TimescaleDB
TimescaleDB is a category-defining relational database for time-series data. Packaged as a PostgreSQL extension, TimescaleDB is designed to be easy to use, easy to get started, and easy to maintain.
Download this sample dataset by Timescale
Docker Compose
1mkdir /tmp/prest+timescaledb
2cd /tmp/prest+timescaledb
3cat <<YML > docker-compose.yml
4---
5version: "3"
6services:
7 timescaledb:
8 image: timescale/timescaledb:latest-pg12
9 volumes:
10 - "./data:/var/lib/postgresql/data"
11 - "/tmp:/var/tmp"
12 environment:
13 - POSTGRES_USER=prest
14 - POSTGRES_DB=prest
15 - POSTGRES_PASSWORD=prest
16 ports:
17 - "5432:5432"
18 prest:
19 image: prest/prest:latest
20 links:
21 - "timescaledb:timescaledb"
22 environment:
23 - PREST_DEBUG=true # remove comment for enable DEBUG mode (disable JWT)
24 - PREST_PG_HOST=timescaledb
25 - PREST_PG_USER=prest
26 - PREST_PG_PASS=prest
27 - PREST_PG_DATABASE=prest
28 - PREST_PG_PORT=5432
29 - PREST_JWT_DEFAULT=false # remove if need jwt
30 - PREST_SSL_MODE=disable
31 depends_on:
32 - timescaledb
33 ports:
34 - "3000:3000"
35YML
Starting up the containers
1docker-compose pull
2docker-compose up -d
Creating database structure
1curl https://timescaledata.blob.core.windows.net/datasets/devices_small.tar.gz -o /tmp/devices_small.tar.gz
2tar -C /tmp -xzvf /tmp/devices_small.tar.gz
3docker-compose exec -T timescaledb psql -U prest -f /var/tmp/devices.sql
Loading data
1docker-compose exec -T timescaledb psql -U prest <<SQL
2COPY device_info FROM '/var/tmp/devices_small_device_info.csv' WITH (FORMAT CSV);
3COPY readings FROM '/var/tmp/devices_small_readings.csv' WITH (FORMAT CSV);
4SQL
Simple Query
SQL execution:
1docker-compose exec -T timescaledb psql -U prest <<SQL
2SELECT
3 time, device_id, battery_temperature
4FROM
5 readings
6WHERE
7 battery_status = 'charging'
8ORDER BY
9 time DESC
10LIMIT
11 10;
12SQL
prestd execution:
1curl -G http://localhost:3000/prest/public/readings \
2 -d battery_status='$eq.charging' \
3 -d _select=time,device_id,battery_temperature \
4 -d _order=-time \
5 -d _page=1 \
6 -d _page_size=10
Joining tables
SQL execution:
1docker-compose exec -T timescaledb psql -U prest <<SQL
2SELECT
3 time, readings.device_id, cpu_avg_1min,
4 battery_level, battery_status, device_info.model
5FROM
6 readings
7 JOIN device_info ON readings.device_id = device_info.device_id
8WHERE
9 battery_level < 33
10 AND battery_status = 'discharging'
11ORDER BY
12 cpu_avg_1min DESC, time DESC
13LIMIT
14 5;
15SQL
prestd execution:
1curl -G http://localhost:3000/prest/public/readings \
2 -d battery_level='$lt.33' \
3 -d battery_status='$eq.discharging' \
4 -d _select='time,readings.device_id,cpu_avg_1min,battery_level,battery_status,device_info.model' \
5 -d _join='inner:device_info:readings.device_id:$eq:device_info.device_id' \
6 -d _order='-cpu_avg_1min,-time' \
7 -d _page=1 \
8 -d _page_size=5
Using VIEWs
Creating VIEW named battery_level_by_hour:
1docker-compose exec -T timescaledb psql -U prest <<SQL
2CREATE VIEW battery_level_by_hour AS
3SELECT
4 time_bucket('1 hour', time) AS "hour",
5 model,
6 min(battery_level) AS min_battery_level,
7 max(battery_level) AS max_battery_level
8FROM
9 readings
10 JOIN device_info ON readings.device_id = device_info.device_id
11GROUP BY
12 "hour", model;
13SQL
Aggregating data over battery_level_by_hour view:
1docker-compose exec -T timescaledb psql -U prest <<SQL
2SELECT
3 hour, min(min_battery_level), max(max_battery_level)
4FROM
5 battery_level_by_hour
6WHERE
7 model IN ('pinto', 'focus')
8GROUP BY
9 hour
10ORDER BY
11 hour ASC
12LIMIT
13 12;
14SQL
prestd execution:
1curl -G http://localhost:3000/prest/public/battery_level_by_hour \
2 -d model='$in.pinto,focus' \
3 -d _select='hour,min:min_battery_level,max:max_battery_level' \
4 -d _groupby='hour' \
5 -d _order='hour'
Simple SELECT over battery_level_by_hour view:
1docker-compose exec -T timescaledb psql -U prest <<SQL
2SELECT
3 hour, min_battery_level, max_battery_level
4FROM
5 battery_level_by_hour
6WHERE
7 model = 'mustang'
8LIMIT
9 5;
10SQL
**prestd execution over battery_level_by_hour view:
1curl -G http://localhost:3000/prest/public/battery_level_by_hour \
2 -d model='$eq.mustang' \
3 -d _select='hour,min_battery_level,max_battery_level' \
4 -d _order='hour' \
5 -d _page='1' \
6 -d _page_size='5'
Batch Insert data
Using default INSERT statement WITH returning inserted data:
1curl http://localhost:3000/batch/prest/public/readings \
2 -H "Content-Type: application/json" \
3 -d @- << JSON
4 [
5 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000000", "battery_level": 43, "battery_status": "discharging", "battery_temperature": 89.8, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 28.84, "cpu_avg_5min": 16.9047812612903, "cpu_avg_15min": 10.8993036332756, "mem_free": 420023054, "mem_used": 579976946, "rssi": -40, "ssid": "demo-net"},
6 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000001", "battery_level": 27, "battery_status": "discharging", "battery_temperature": 89.3, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 4.89, "cpu_avg_5min": 6.63334573320236, "cpu_avg_15min": 9.25968056754939, "mem_free": 717784757, "mem_used": 282215243, "rssi": -41, "ssid": "stealth-net"},
7 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000002", "battery_level": 29, "battery_status": "discharging", "battery_temperature": 93.7, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 8.29, "cpu_avg_5min": 6.78591150918263, "cpu_avg_15min": 7.37546420066158, "mem_free": 634081377, "mem_used": 365918623, "rssi": -54, "ssid": "stealth-net"},
8 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000003", "battery_level": 14, "battery_status": "discharging", "battery_temperature": 93.1, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 8.83, "cpu_avg_5min": 8.18492270691781, "cpu_avg_15min": 11.3986054360923, "mem_free": 563352328, "mem_used": 436647672, "rssi": -30, "ssid": "demo-net"},
9 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000004", "battery_level": 58, "battery_status": "discharging", "battery_temperature": 93.2, "bssid": "22:32:A2:B3:05:98", "cpu_avg_1min": 8.79, "cpu_avg_5min": 10.3900175308572, "cpu_avg_15min": 13.5103326842724, "mem_free": 642162250, "mem_used": 357837750, "rssi": -62, "ssid": "demo-5ghz"}
10 ]
11JSON
Using COPY statement WITHOUT returning inserted data:
1curl http://localhost:3000/batch/prest/public/readings \
2 -H "Content-Type: application/json" \
3 -H "Prest-Batch-Method: copy" \
4 -d @- << JSON
5 [
6 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000000", "battery_level": 43, "battery_status": "discharging", "battery_temperature": 89.8, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 28.84, "cpu_avg_5min": 16.9047812612903, "cpu_avg_15min": 10.8993036332756, "mem_free": 420023054, "mem_used": 579976946, "rssi": -40, "ssid": "demo-net"},
7 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000001", "battery_level": 27, "battery_status": "discharging", "battery_temperature": 89.3, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 4.89, "cpu_avg_5min": 6.63334573320236, "cpu_avg_15min": 9.25968056754939, "mem_free": 717784757, "mem_used": 282215243, "rssi": -41, "ssid": "stealth-net"},
8 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000002", "battery_level": 29, "battery_status": "discharging", "battery_temperature": 93.7, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 8.29, "cpu_avg_5min": 6.78591150918263, "cpu_avg_15min": 7.37546420066158, "mem_free": 634081377, "mem_used": 365918623, "rssi": -54, "ssid": "stealth-net"},
9 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000003", "battery_level": 14, "battery_status": "discharging", "battery_temperature": 93.1, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 8.83, "cpu_avg_5min": 8.18492270691781, "cpu_avg_15min": 11.3986054360923, "mem_free": 563352328, "mem_used": 436647672, "rssi": -30, "ssid": "demo-net"},
10 {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000004", "battery_level": 58, "battery_status": "discharging", "battery_temperature": 93.2, "bssid": "22:32:A2:B3:05:98", "cpu_avg_1min": 8.79, "cpu_avg_5min": 10.3900175308572, "cpu_avg_15min": 13.5103326842724, "mem_free": 642162250, "mem_used": 357837750, "rssi": -62, "ssid": "demo-5ghz"}
11 ]
12JSON