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

高级教程

本教程的期望内容是什么？​

在本教程中，您将创建一个表并插入一个大型数据集（包含两百万行的 纽约出租车数据）。然后，您将对数据集执行查询，包括如何创建字典并使用它执行 JOIN 的示例。

备注

本教程假设您可以访问正在运行的 ClickHouse 服务。如果没有，请查看 快速入门。

1. 创建新表​

纽约市出租车数据包含数百万次出租车行程的详细信息，列包括接送时间和地点、费用、小费金额、过路费、付款类型等。让我们创建一个表来存储这些数据...

  1. 连接到 SQL 控制台
SQL 控制台

如果您需要 SQL 客户端连接，您的 ClickHouse Cloud 服务具有一个关联的基于网页的 SQL 控制台；请展开 连接到 SQL 控制台 以获取详细信息。

连接到 SQL 控制台

从您的 ClickHouse Cloud 服务列表中，点击一个服务。

连接到 SQL 控制台

这将会重定向您到 SQL 控制台。

SQL 控制台

如果您使用的是自管理的 ClickHouse，您可以通过 https://hostname:8443/play 连接到 SQL 控制台（请向您的 ClickHouse 管理员确认详细信息）。

  1. 在 default 数据库中创建以下 trips 表：

2. 插入数据集​

现在您已经创建了一个表，让我们添加纽约出租车数据。它存储在 S3 的 CSV 文件中，您可以从那里加载数据。

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

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

    备注

    s3 函数聪明地知道如何解压数据，TabSeparatedWithNames 格式告诉 ClickHouse 数据是以制表符分隔，并且跳过每个文件的标题行。

  3. 插入完成后，验证是否成功：

    您应该看到大约 2M 行（确切地说是 1,999,657 行）。

    备注

    请注意，ClickHouse 是多么迅速以及处理了多么少的行以确定计数？您可以在 0.001 秒内获得计数，仅处理了 6 行。

  4. 如果您运行一个需要遍历每一行的查询，您会注意到需要处理的行数显著增加，但运行时间仍然非常快：

    这个查询需要处理 2M 行并返回 190 个值，但请注意，它在大约 1 秒内完成。pickup_ntaname 列表示纽约市出租车行程起始的邻里名称。

3. 分析数据​

让我们运行一些查询来分析这 2M 行的数据...

  1. 我们将从一些简单的计算开始，比如计算平均小费金额：

    响应为：

  2. 该查询根据乘客数量计算平均费用：

    passenger_count 的范围为 0 到 9：

  3. 这是一个计算每个邻里每日接送次数的查询：

    结果如下所示：

  4. 该查询计算行程长度并按该值分组结果：

    结果如下所示：

  5. 该查询显示每个邻里按小时分解的接送次数：

    结果如下所示：

  6. 让我们查看前往拉瓜迪亚或 JFK 机场的行程：

    响应为：

4. 创建字典​

如果您是 ClickHouse 新手，理解 字典 的工作原理非常重要。简单地说，字典可以被视为存储在内存中的键值对映射。字典的详细信息和所有选项将在教程的最后链接。

  1. 让我们看一下如何创建与 ClickHouse 服务中的表关联的字典。该表以及字典将基于一个包含 265 行的 CSV 文件，每一行对应纽约市的一个邻里。这些邻里映射到纽约市的区名（纽约市有 5 个区：布朗克斯、布鲁克林、曼哈顿、皇后区和斯塔顿岛），并且该文件还将纽瓦克机场 (EWR) 算作一个区。

这是 CSV 文件的一部分（为了清晰以表格形式显示）。该文件中的 LocationID 列映射到您 trips 表中的 pickup_nyct2010_gid 和 dropoff_nyct2010_gid 列：

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

将 LIFETIME 设置为 0 意味着这个字典将永远不会与其源更新。这样做是为了不对我们的 S3 存储桶发送不必要的流量，但一般来说，您可以根据需要指定任何生命周期值。

例如：

指定字典在 1 到 10 秒之间的随机时间后更新。（随机时间是必要的，以便在大量服务器上更新时分散字典源的负担。）

  1. 验证是否成功 - 你应该获得 265 行（每个邻里一行）：

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

    例如，以下查询返回 LocationID 为 132 的 Borough（如上面所见为 JFK 机场）：

    查尔斯在皇后区，注意检索值的时间基本上为 0：

  3. 使用 dictHas 函数查看字典中是否存在键。比如，以下查询返回 1（在 ClickHouse 中为“真”）：

  4. 以下查询返回 0，因为 4567 不是字典中的 LocationID 的值：

  5. 使用 dictGet 函数在查询中检索一个区的名称。例如：

    该查询汇总了在拉瓜迪亚或 JFK 机场结束的每个区的出租车行程数量。结果如下所示，并且请注意，有相当多的行程的接送邻里是未知的：

5. 执行 JOIN​

让我们编写一些查询，将 taxi_zone_dictionary 与您的 trips 表进行连接。

  1. 我们可以从一个简单的 JOIN 开始，它在功能上类似于上面之前的机场查询：

    响应类似于：

    备注

    请注意，上面的 JOIN 查询的输出与之前使用 dictGetOrDefault 的查询是相同的（只是 Unknown 值不包含在内）。在后台，ClickHouse 实际上调用的是 taxi_zone_dictionary 字典的 dictGet 函数，但 JOIN 语法对 SQL 开发人员来说更为熟悉。

  2. 我们在 ClickHouse 中不常使用 SELECT * - 您应该仅检索您实际需要的列！但要找到一个花费很长时间的查询很困难，因此此查询故意选择每一列并返回每一行（除了默认情况下内置的 10,000 行响应最大限制），并且也与字典进行每一行的右连接：

恭喜！​

做得好 - 您完成了本教程，并且希望您对如何使用 ClickHouse 有了更好的理解。以下是您可以做的下一步：