timescale使用例子 - 纽约出租车

一、资料使用

在timescale的官方网站的“教程”菜单中，有几个不同业务场景的例子，其中就有运输行业的例子。我访问中文站点的时候，关于教程的几个步骤内容刷不出来，所以还是建议访问英文站点。

https://docs.timescale.com/tutorials/latest/nyc-taxi-cab/

对于出租车的数据，不用去 NYC TLC官网去下载，他们现在都是parquet格式数据，还得了解怎么去用，没必要。如果想了解关于这些数据的背景信息，可以看看他们官方站点https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page

二、操作

2.1 安装 TimescaleDB

本机安装就用docker方式，参考

https://docs.timescaledb.cn/self-hosted/latest/install/installation-docker/

拿到镜像

docker pull timescale/timescaledb-ha:pg16

目前无法通过docker pull直接下载，需要有代理的地方提前准备好镜像

运行容器

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb-ha:pg16

我本机已经有一个pg库用了5432:5432，这里可以改为端口 -p 54329:5432

在 timescaledb-ha:pg16 镜像中，默认用户和数据库都是 postgres。在运行镜像时，您在 POSTGRES_PASSWORD 中设置密码，这里就是password。

连接

通过工具 DBeaver可以方便的连接数据库。

也可以通过psql命令行工具连接，如下图，直接进入运行timescaledb的容器，然后通过psql连接，通过\dx 检查是否安装了TimescaleDB。

2.2 准备基础表

所有内容都在官方文档中，照着操作就好，就是创建表，我们把这些SQL都直接粘贴到 DBeaver工具直接运行就行。

https://docs.timescale.com/tutorials/latest/nyc-taxi-cab/dataset-nyc/#set-up-the-database

创建超表之前，先是创建一个PostgreSQL表，用于存储出租车的行程信息

CREATE TABLE "rides"(vendor_id TEXT,pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,passenger_count NUMERIC,trip_distance NUMERIC,pickup_longitude  NUMERIC,pickup_latitude   NUMERIC,rate_code         INTEGER,dropoff_longitude NUMERIC,dropoff_latitude  NUMERIC,payment_type INTEGER,fare_amount NUMERIC,extra NUMERIC,mta_tax NUMERIC,tip_amount NUMERIC,tolls_amount NUMERIC,improvement_surcharge NUMERIC,total_amount NUMERIC
);

然后把标准版转换为超表，

SELECT create_hypertable('rides', by_range('pickup_datetime'), create_default_indexes=>FALSE);
SELECT add_dimension('rides', by_hash('payment_type', 2));

为了高效查询，创建索引

CREATE INDEX ON rides (vendor_id, pickup_datetime DESC);
CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
CREATE INDEX ON rides (passenger_count, pickup_datetime DESC);

创建两个普通表，用于做关联数据

① 存储支付类型

CREATE TABLE IF NOT EXISTS "payment_types"(payment_type INTEGER,description TEXT
);
INSERT INTO payment_types(payment_type, description) VALUES
(1, 'credit card'),
(2, 'cash'),
(3, 'no charge'),
(4, 'dispute'),
(5, 'unknown'),
(6, 'voided trip');

② 存储费率数据


CREATE TABLE IF NOT EXISTS "rates"(rate_code   INTEGER,description TEXT
);
INSERT INTO rates(rate_code, description) VALUES
(1, 'standard rate'),
(2, 'JFK'),
(3, 'Newark'),
(4, 'Nassau or Westchester'),
(5, 'negotiated fare'),
(6, 'group ride');