跳到主要内容
跳到主要内容

高级教程

概述​

了解如何使用纽约市出租车示例数据集在 ClickHouse 中摄取和查询数据。

先决条件​

您需要访问正在运行的 ClickHouse 服务才能完成本教程。有关说明，请参见 快速入门 指南。

创建新表​

纽约市出租车数据集包含有关数百万次出租车乘车的详细信息，包括小费金额、过路费、支付类型等列。创建一个表以存储这些数据。

  1. 连接到 SQL 控制台：
  • 对于 ClickHouse Cloud，从下拉菜单中选择服务，然后从左侧导航菜单中选择 SQL 控制台。
  • 对于自管理的 ClickHouse，连接到 SQL 控制台，地址为 https://_hostname_:8443/play。请与您的 ClickHouse 管理员确认详细信息。
  1. 在 default 数据库中创建以下 trips 表：

添加数据集​

现在您已创建一个表，请从 S3 中的 CSV 文件添加纽约市出租车数据。

  1. 以下命令从 S3 中的两个不同文件 trips_1.tsv.gz 和 trips_2.tsv.gz 向您的 trips 表插入约 2,000,000 行：

  1. 等待 INSERT 完成。下载 150 MB 的数据可能需要一段时间。

  2. 插入完成后，验证其成功：

此查询应返回 1,999,657 行。

分析数据​

运行一些查询以分析数据。探索以下示例或尝试您自己的 SQL 查询。

  • 计算平均小费金额：
预期输出

  • 根据乘客人数计算平均费用：
预期输出

passenger_count 的范围从 0 到 9：

  • 计算每个社区每天的接客数量：
预期输出

  • 计算每次行程的时长（以分钟为单位），然后按行程时长对结果进行分组：
预期输出

  • 显示每个社区每小时的接客数量：
预期输出

  1. 检索前往拉瓜地亚或约翰·F·肯尼迪机场的行程：
预期输出

创建字典​

字典是存储在内存中的键值对映射。有关详细信息，请参见 字典

创建一个与您的 ClickHouse 服务中的表关联的字典。 该表和字典基于一个包含纽约市每个社区行的 CSV 文件。

这些社区与五个纽约市区（布朗克斯、布鲁克林、曼哈顿、皇后区和斯塔滕岛）以及纽瓦克机场（EWR）的名称对应。

以下是您正在使用的以表格格式展示的 CSV 文件摘录。文件中的 LocationID 列映射到您 trips 表中的 pickup_nyct2010_gid 和 dropoff_nyct2010_gid 列：

LocationIDBoroughZoneservice_zone
1EWR纽瓦克机场EWR
2皇后区牙买加湾区域
3布朗克斯阿勒顿/佩勒姆花园区域
4曼哈顿字母城市黄色区域
5斯塔滕岛阿登高地区域
  1. 运行以下 SQL 命令，创建名为 taxi_zone_dictionary 的字典，并从 S3 中的 CSV 文件填充该字典。文件的 URL 为 https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv。
备注

将 LIFETIME 设置为 0 禁用自动更新，以避免对我们 S3 存储桶产生不必要的流量。在其他情况下，您可能会以不同的方式进行配置。有关详细信息，请参见 使用 LIFETIME 刷新字典数据。

  1. 验证其成功。以下查询应返回 265 行，或每个社区一行：

  1. 使用 dictGet 函数 (或其变体) 从字典中检索值。您需要输入字典的名称、所需的值和键（在我们的示例中是 taxi_zone_dictionary 的 LocationID 列）。

    例如，以下查询返回 LocationID 为 132 的 Borough，对应于 JFK 机场：

JFK 位于皇后区。注意检索值的时间几乎为 0：

  1. 使用 dictHas 函数查看字典中是否存在键。例如，以下查询返回 1（在 ClickHouse 中表示 "true"）：
  1. 以下查询返回 0，因为 4567 不是字典中 LocationID 的值：
  1. 使用 dictGet 函数在查询中检索区域名称。例如：

此查询计算结束于拉瓜地亚或 JFK 机场的每个区的出租车行程数量。结果如下所示，请注意有相当多的行程的接客社区未知：

执行连接​

编写一些将 taxi_zone_dictionary 与您的 trips 表连接的查询。

  1. 从一个简单的 JOIN 开始，其行为类似于上面的机场查询：

响应与使用 dictGet 查询的结果相同：

备注

请注意，上述 JOIN 查询的输出与使用 dictGetOrDefault 的查询具有相同的结果（只是 "未知" 值未包含）。在后台，ClickHouse 实际上为 taxi_zone_dictionary 字典调用 dictGet 函数，但 JOIN 语法对于 SQL 开发人员更加熟悉。

  1. 此查询返回 1000 次最高小费的行程的行，然后将每一行与字典进行内连接：
备注

一般来说，我们在 ClickHouse 中较少使用 SELECT *。您应仅检索实际需要的列。然而，对于本示例而言，该查询的速度较慢。

后续步骤​

通过以下文档了解更多有关 ClickHouse 的信息：