timesacledb 測試demo資料運行

標籤：href   install   post   tutorial   ase   alter   postgre   nbsp   explain   

timesacledb 的安裝還是使用docker，對於測試資料需要提前下載

啟動timesacledb

使用支援gis 的鏡像，後邊需要使用

docker run -d --name timescaledb -p 5432:5432 timescale/timescaledb-postgis

預備環境

• 下載測試資料

https://timescaledata.blob.core.windows.net/datasets/nyc_data.tar.gz

• 建立資料庫&&擴充載入timesacledb

CREATE DATABASE nyc_data;\c nyc_dataCREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

載入資料

• 匯入schema

psql -U postgres -d nyc_data -h localhost < nyc_data.sql

• 匯入資料

psql -U postgres -d nyc_data -h localhost -c "\COPY rides FROM nyc_data_rides.csv CSV"

執行查詢

• 基本查詢

SELECT date_trunc(‘day‘, pickup_datetime) as day, avg(fare_amount) FROM rides WHERE passenger_count > 1 AND pickup_datetime < ‘2016-01-08‘ GROUP BY day ORDER BY day;SELECT date_trunc(‘day‘, pickup_datetime) as day, COUNT(*) FROM rides GROUP BY day ORDER BY day LIMIT 5;

• 時序查詢

SELECT time_bucket(‘5 minute‘, pickup_datetime) AS five_min, count(*) FROM rides WHERE pickup_datetime < ‘2016-01-01 02:00‘ GROUP BY five_min ORDER BY five_min;

系統資料

查詢細節

• 通過explain 查看timesacledb 的工作

EXPLAIN SELECT * FROM rides;

gis 查詢

• 讓nyc_data 支援gis

CREATE EXTENSION postgis;ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);

• 產生geo 資料（有點慢，需要花點時間）

UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163);UPDATE rides SET dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

• gis 查詢

SELECT time_bucket(‘30 minutes‘, pickup_datetime) AS thirty_min, COUNT(*) AS near_times_sq  FROM rides  WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400    AND pickup_datetime < ‘2016-01-01 14:00‘  GROUP BY thirty_min ORDER BY thirty_min;

• 系統產生資料
  

說明

總的來說簡單，同時具有時序資料庫的特點，對於我們來說不需要關注資料多的時候效能的問題，還是很不錯的，對於ha 以及叢集功能還有待研究

參考資料

https://docs.timescale.com/v0.9/tutorials/tutorial-hello-nyc
https://docs.timescale.com/v0.9/getting-started/installation/mac/installation-homebrew

    

timesacledb 測試demo資料運行