设置数据库

本教程使用一个数据集，其中包含纽约黄色出租车网络的历史数据，存储在一个名为 rides 的超表中。它还包括单独的支付类型和费率表，存储在名为 payment_types 和 rates 的常规 PostgreSQL 表中。

注意

如果您在您的 Timescale 服务上已预加载了数据集，请直接转到查询部分。

Timescale 中的服务是一个云实例，其中包含您的数据库。每个服务包含一个名为 tsdb 的数据库。您可以使用 psql 命令行实用程序从本地系统连接到服务。如果您以前使用过 PostgreSQL，您可能已经安装了 psql。如果未安装，请查看安装 psql 部分。

创建 Timescale 服务并连接到服务

在Timescale 门户中，单击 Create service。
单击 Download the cheatsheet 下载一个 SQL 文件，其中包含您的新服务的登录详细信息。您也可以直接从此页面复制详细信息。复制密码后，单击页面底部的 I stored my password, go to service overview。
当您的服务准备好使用时，它会在 Service Overview 中显示绿色的 Running 标签。您还会收到一封电子邮件，确认您的服务已准备好使用。
在您的本地系统上，在命令提示符下，使用您下载的 SQL 文件中的 Service URL 连接到服务。当系统提示时，输入密码
```
psql -x "<SERVICE_URL>"
Password for user tsdbadmin:
```
如果连接成功，您将看到类似这样的消息，然后是 psql 提示符
```
psql (13.3, server 12.8 (Ubuntu 12.8-1.pgdg21.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
tsdb=>
```

本教程使用纽约黄色出租车网络的历史数据，由纽约市出租车和豪华轿车委员会 NYC TLC 提供。

创建超表

超表是 Timescale 的核心。超表使 Timescale 能够高效地处理时序数据。由于 Timescale 是 PostgreSQL，因此所有标准的 PostgreSQL 表、索引、存储过程和其他对象都可以与您的 Timescale 超表一起创建。这使得创建和使用 Timescale 表类似于标准 PostgreSQL。

创建超表

使用 CREATE TABLE 创建一个标准的 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
);

使用 Timescale 提供的 create_hypertable() 函数将标准表转换为在 time 列上分区的超表。您必须提供表的名称以及表中用于分区的时间戳数据列
```
SELECT create_hypertable('rides', by_range('pickup_datetime'), create_default_indexes=>FALSE);
SELECT add_dimension('rides', by_hash('payment_type', 2));
```
注意
by_range 和 by_hash 维度构建器是 TimescaleDB 2.13 的新增功能。

创建一个索引以支持按供应商、费率代码和乘客人数进行高效查询


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);

为关系数据创建标准 PostgreSQL 表

当您有其他关系数据来增强您的时序数据时，您可以像平常一样创建标准 PostgreSQL 表。对于此数据集，还有两个其他数据表，分别名为 payment_types 和 rates。

创建标准 PostgreSQL 表

添加一个表来存储支付类型数据


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');

您可以通过在 psql 命令行中运行 \dt 命令来确认脚本是否成功。您应该看到这个


List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | payment_types | table | tsdbadmin
 public | rates         | table | tsdbadmin
 public | rides         | table | tsdbadmin
(3 rows)

加载行程数据

设置好数据库后，您可以将出租车行程数据加载到 rides 超表中。

加载行程数据

重要提示

这是一个大型数据集，因此可能需要很长时间，具体取决于您的网络连接。

下载数据集
nyc_data.tar.gz
使用文件管理器解压缩下载的数据集，并记下 nyc_data_rides.csv 文件的路径。
在 psql 提示符下，将数据从 nyc_data_rides.csv 文件复制到您的超表中。如果该文件不在您当前的工作目录中，请确保指向正确的路径
```
\COPY rides FROM nyc_data_rides.csv CSV;
```

您可以使用以下命令检查数据是否已成功复制


SELECT * FROM rides LIMIT 5;

您应该得到五个看起来像这样的记录


-[ RECORD 1 ]---------+--------------------
vendor_id             | 1
pickup_datetime       | 2016-01-01 00:00:01
dropoff_datetime      | 2016-01-01 00:11:55
passenger_count       | 1
trip_distance         | 1.20
pickup_longitude      | -73.979423522949219
pickup_latitude       | 40.744613647460938
rate_code             | 1
dropoff_longitude     | -73.992034912109375
dropoff_latitude      | 40.753944396972656
payment_type          | 2
fare_amount           | 9
extra                 | 0.5
mta_tax               | 0.5
tip_amount            | 0
tolls_amount          | 0
improvement_surcharge | 0.3
total_amount          | 10.3

注意

创建 Timescale 服务并连接到您的服务

数据集