基于纽约出租车数据集的地理空间分析

在本教程中，您将探索如何使用 ClickHouse 对海量数据运行分析查询。此外，您还将学习如何使用字典扩充数据，以及如何编写连接查询。

前置条件

完成本教程需要准备：

一个 ClickHouse Cloud 账户 (注册即可获赠 300 美元免费额度)
一个 ClickHouse Cloud 服务

创建表

本教程使用的数据集是纽约市出租车数据集，其中包含数百万次出租车行程的详细信息，涵盖小费金额、通行费、支付方式等列。

在左侧菜单中选择 SQL 控制台
点击主页图标旁的 + 选项卡，新建一个查询
在 SQL 编辑器中输入以下查询，然后点击运行：

Expandable

CREATE TABLE trips
(
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;

插入数据

创建好表之后，接下来从 S3 中的 CSV 文件导入纽约市出租车数据。以下命令会从 S3 中的两个文件 trips_1.tsv.gz 和 trips_2.tsv.gz 向 trips 表插入约 2,000,000 行数据：

Expandable

INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    `trip_id` UInt32,
    `vendor_id` Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    `pickup_date` Date,
    `pickup_datetime` DateTime,
    `dropoff_date` Date,
    `dropoff_datetime` DateTime,
    `store_and_fwd_flag` UInt8,
    `rate_code_id` UInt8,
    `pickup_longitude` Float64,
    `pickup_latitude` Float64,
    `dropoff_longitude` Float64,
    `dropoff_latitude` Float64,
    `passenger_count` UInt8,
    `trip_distance` Float64,
    `fare_amount` Float32,
    `extra` Float32,
    `mta_tax` Float32,
    `tip_amount` Float32,
    `tolls_amount` Float32,
    `ehail_fee` Float32,
    `improvement_surcharge` Float32,
    `total_amount` Float32,
    `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    `trip_type` UInt8,
    `pickup` FixedString(25),
    `dropoff` FixedString(25),
    `cab_type` Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    `pickup_nyct2010_gid` Int8,
    `pickup_ctlabel` Float32,
    `pickup_borocode` Int8,
    `pickup_ct2010` String,
    `pickup_boroct2010` String,
    `pickup_cdeligibil` String,
    `pickup_ntacode` FixedString(4),
    `pickup_ntaname` String,
    `pickup_puma` UInt16,
    `dropoff_nyct2010_gid` UInt8,
    `dropoff_ctlabel` Float32,
    `dropoff_borocode` UInt8,
    `dropoff_ct2010` String,
    `dropoff_boroct2010` String,
    `dropoff_cdeligibil` String,
    `dropoff_ntacode` FixedString(4),
    `dropoff_ntaname` String,
    `dropoff_puma` UInt16
") SETTINGS input_format_try_infer_datetimes = 0

等待数据插入完成，此过程大约会下载 150MB 数据。数据插入完成后，查看 trips 表中的行数：

SELECT count() FROM trips

您应该会得到 1,999,657 行结果

分析数据

数据加载完成后，即可运行一些查询来分析数据。

计算平均小费金额：

SELECT round(avg(tip_amount), 2) FROM trips

按乘客人数计算平均费用：

SELECT
    passenger_count,
    ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_count

计算每个社区每天的上车次数：

SELECT
  pickup_date,
  pickup_ntaname,
  SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC

计算每次行程的时长 (以分钟计) ，然后按行程时长对结果分组：

SELECT
  avg(tip_amount) AS avg_tip,
  avg(fare_amount) AS avg_fare,
  avg(passenger_count) AS avg_passenger,
  count() AS count,
  truncate(date_diff('second', pickup_datetime, dropoff_datetime)/60) as trip_minutes
FROM trips
WHERE trip_minutes > 0
GROUP BY trip_minutes
ORDER BY trip_minutes DESC

按小时细分显示每个社区在一天中各时段的上车次数：

SELECT
    pickup_ntaname,
    toHour(pickup_datetime) as pickup_hour,
    SUM(1) AS pickups
FROM trips
WHERE pickup_ntaname != ''
GROUP BY pickup_ntaname, pickup_hour
ORDER BY pickup_ntaname, pickup_hour

创建字典

接下来，您将创建一个名为 taxi_zone_dictionary 的字典 (即存储在内存中的键值对映射) ，用于建立位置 ID 与纽约市行政区名称之间的映射关系，其数据来源于一个包含纽约市所有社区的 CSV 文件。这些位置 ID 对应 trips 表中的 pickup_nyct2010_gid 和 dropoff_nyct2010_gid 列。下表摘录了您将使用的 CSV 文件的部分内容。文件中的 LocationID 列对应 trips 表中的 pickup_nyct2010_gid 和 dropoff_nyct2010_gid 列：

LocationID	Borough	Zone	service_zone
1	EWR	Newark Airport	EWR
2	Queens	Jamaica Bay	Boro Zone
3	Bronx	Allerton/Pelham Gardens	Boro Zone
4	Manhattan	Alphabet City	Yellow Zone
5	Staten Island	Arden Heights	Boro Zone

运行以下 SQL 命令，创建名为 taxi_zone_dictionary 的字典，并使用 S3 中的 CSV 文件填充该字典。该文件的 URL 为 https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv。

CREATE DICTIONARY taxi_zone_dictionary
(
  `LocationID` UInt16 DEFAULT 0,
  `Borough` String,
  `Zone` String,
  `service_zone` String
)
PRIMARY KEY LocationID
SOURCE(HTTP(URL 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv' FORMAT 'CSVWithNames'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(HASHED_ARRAY())

将 LIFETIME 设置为 0 会禁用自动更新，以避免向我们的 S3 桶发送不必要的请求流量。在其他场景下，您可以按需进行不同的配置。详情请参见使用 LIFETIME 刷新字典数据。

验证操作是否成功。以下查询应返回 265 行，即每个社区一行：

SELECT * FROM taxi_zone_dictionary

使用字典执行查询

你可以使用 dictGet 函数 (或其变体) 从字典中获取值。只需传入字典名称、要获取的值以及键 (在本示例中为 taxi_zone_dictionary 的 LocationID 列) ，即可得到对应的值。例如，以下查询返回 LocationID 为 132 (即 JFK 机场) 的 Borough：

SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)

JFK 位于 Queens (皇后区) 。请注意，检索该值所用的时间几乎为 0：

┌─dictGet('taxi_zone_dictionary', 'Borough', 132)─┐
│ Queens                                          │
└─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

使用 dictHas 函数可以检查字典中是否存在某个键。例如，以下查询会返回 1 (在 ClickHouse 中表示 “true”) ：

SELECT dictHas('taxi_zone_dictionary', 132)

以下查询返回 0，因为字典中的 LocationID 不包含值 4567：

SELECT dictHas('taxi_zone_dictionary', 4567)

在查询中使用 dictGet 函数获取行政区的名称。例如：

SELECT
  count(1) AS total,
  dictGetOrDefault('taxi_zone_dictionary','Borough', toUInt64(pickup_nyct2010_gid), 'Unknown') AS borough_name
FROM trips
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY borough_name
ORDER BY total DESC

此查询按行政区统计了终点为 LaGuardia 或 JFK 机场的出租车行程数。结果如下所示，可以看到有相当多行程的上车社区是未知的：

┌─total─┬─borough_name──┐
│ 23683 │ Unknown       │
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

7 rows in set. Elapsed: 0.019 sec. Processed 2.00 million rows, 4.00 MB (105.70 million rows/s., 211.40 MB/s.)

执行连接

最后，编写一些查询，将 taxi_zone_dictionary 与你的 trips 表进行连接。先从一个简单的 JOIN 开始，其作用与上文的机场查询类似：

SELECT
    count(1) AS total,
    Borough
FROM trips
JOIN taxi_zone_dictionary ON toUInt64(trips.pickup_nyct2010_gid) = taxi_zone_dictionary.LocationID
WHERE dropoff_nyct2010_gid = 132 OR dropoff_nyct2010_gid = 138
GROUP BY Borough
ORDER BY total DESC

返回的结果与 dictGet 查询的结果完全相同：

┌─total─┬─Borough───────┐
│  7053 │ Manhattan     │
│  6828 │ Brooklyn      │
│  4458 │ Queens        │
│  2670 │ Bronx         │
│   554 │ Staten Island │
│    53 │ EWR           │
└───────┴───────────────┘

6 rows in set. Elapsed: 0.034 sec. Processed 2.00 million rows, 4.00 MB (59.14 million rows/s., 118.29 MB/s.)

请注意，上述 JOIN 查询的输出与前面使用 dictGetOrDefault 的查询结果相同 (只是不包含 Unknown 值) 。实际上，ClickHouse 在底层是对 taxi_zone_dictionary 字典调用 dictGet 函数，只不过 SQL 开发人员对 JOIN 语法更为熟悉。

此查询返回小费金额最高的 1000 条行程记录，然后将每一行与该字典进行内连接：

SELECT *
FROM trips
JOIN taxi_zone_dictionary
ON trips.dropoff_nyct2010_gid = taxi_zone_dictionary.LocationID
WHERE tip_amount > 0
ORDER BY tip_amount DESC
LIMIT 1000

建议只列出查询真正需要的列，避免使用 SELECT *。由于 ClickHouse 按列存储数据，查询的列越少，需要读取、解压和处理的数据量就越少。

后续步骤

如需进一步了解 ClickHouse，请参阅以下文档：

ClickHouse 主索引简介：了解 ClickHouse 如何在查询时利用稀疏主索引高效定位相关数据。
集成外部数据源：了解各类数据源集成方案，包括文件、Kafka、PostgreSQL、数据管道等。
在 ClickHouse 中可视化数据：将您常用的 UI/BI 工具连接到 ClickHouse。
SQL 参考：浏览 ClickHouse 中可用于转换、处理和分析数据的 SQL 函数。

入门

部署

示例数据集

迁移

基于纽约出租车数据集的地理空间分析

前置条件

创建表

插入数据

分析数据

创建字典

使用字典执行查询

执行连接

后续步骤

​前置条件

创建表

插入数据

分析数据

创建字典

使用字典执行查询

执行连接

​后续步骤

前置条件

后续步骤