高级教程
本教程的预期内容？
在本教程中，您将创建一个表并插入一个大型数据集（纽约出租车数据的 200 万行数据）。然后，您将在数据集上运行查询，包括如何创建字典并使用它执行 JOIN 的示例。
本教程假设您已访问正在运行的 ClickHouse 服务。如果没有，请查看快速入门。
1. 创建新表
纽约市出租车数据包含数百万次出租车行程的详细信息，包括接送时间和地点、费用、小费金额、通行费、支付方式等列。让我们创建一个表来存储这些数据……
连接到 SQL 控制台
SQL 控制台如果您需要 SQL 客户端连接，您的 ClickHouse Cloud 服务具有关联的基于 Web 的 SQL 控制台；展开下面的“连接到 SQL 控制台”以获取详细信息。
连接到 SQL 控制台
在您的 ClickHouse Cloud 服务列表中，选择您将要使用的服务并单击“连接”。在这里，您可以“打开 SQL 控制台”
如果您使用的是自托管 ClickHouse，则可以连接到 https://hostname:8443/play 上的 SQL 控制台（请咨询您的 ClickHouse 管理员以获取详细信息）。
在
default数据库中创建以下trips表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;
2. 插入数据集
现在您已创建了一个表，让我们添加 NYC 出租车数据。它位于 S3 中的 CSV 文件中，您可以从那里加载数据。
以下命令将大约 2,000,000 行插入到您来自 S3 中两个不同文件的
trips表中：trips_1.tsv.gz和trips_2.tsv.gzINSERT 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等待
INSERT完成 - 下载 150 MB 的数据可能需要一段时间。注意s3函数巧妙地知道如何解压缩数据，TabSeparatedWithNames格式告诉 ClickHouse 数据是用制表符分隔的，并且还跳过每个文件的标题行。插入完成后，验证它是否成功
SELECT count() FROM trips您应该看到大约 200 万行（准确地说为 1,999,657 行）。
注意请注意 ClickHouse 必须处理多少行才能确定计数的速度有多快？您只需处理 6 行，即可在 0.001 秒内获得计数。（6 恰好是
trips表当前具有的**分区**数，并且分区知道它们有多少行。）如果您运行需要命中每一行的查询，您会注意到需要处理更多行，但运行时间仍然非常快
SELECT DISTINCT(pickup_ntaname) FROM trips此查询必须处理 200 万行并返回 190 个值，但请注意它在大约 1 秒内完成了此操作。
pickup_ntaname列表示纽约市出租车行程起点的街区名称。
3. 分析数据
让我们运行一些查询来分析 200 万行数据……
我们将从一些简单的计算开始，例如计算平均小费金额
SELECT round(avg(tip_amount), 2) FROM trips响应为
┌─round(avg(tip_amount), 2)─┐
│ 1.68 │
└───────────────────────────┘此查询根据乘客人数计算平均费用
SELECT
passenger_count,
ceil(avg(total_amount),2) AS average_total_amount
FROM trips
GROUP BY passenger_countpassenger_count的范围为 0 到 9┌─passenger_count─┬─average_total_amount─┐
│ 0 │ 22.69 │
│ 1 │ 15.97 │
│ 2 │ 17.15 │
│ 3 │ 16.76 │
│ 4 │ 17.33 │
│ 5 │ 16.35 │
│ 6 │ 16.04 │
│ 7 │ 59.8 │
│ 8 │ 36.41 │
│ 9 │ 9.81 │
└─────────────────┴──────────────────────┘这是一个查询，计算每个街区的每日接送次数
SELECT
pickup_date,
pickup_ntaname,
SUM(1) AS number_of_trips
FROM trips
GROUP BY pickup_date, pickup_ntaname
ORDER BY pickup_date ASC结果如下所示
┌─pickup_date─┬─pickup_ntaname───────────────────────────────────────────┬─number_of_trips─┐
│ 2015-07-01 │ Brooklyn Heights-Cobble Hill │ 13 │
│ 2015-07-01 │ Old Astoria │ 5 │
│ 2015-07-01 │ Flushing │ 1 │
│ 2015-07-01 │ Yorkville │ 378 │
│ 2015-07-01 │ Gramercy │ 344 │
│ 2015-07-01 │ Fordham South │ 2 │
│ 2015-07-01 │ SoHo-TriBeCa-Civic Center-Little Italy │ 621 │
│ 2015-07-01 │ Park Slope-Gowanus │ 29 │
│ 2015-07-01 │ Bushwick South │ 5 │
此查询计算行程的长度，并将结果按该值分组
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结果如下所示
┌──────────────avg_tip─┬───────────avg_fare─┬──────avg_passenger─┬──count─┬─trip_minutes─┐
│ 1.9600000381469727 │ 8 │ 1 │ 1 │ 27511 │
│ 0 │ 12 │ 2 │ 1 │ 27500 │
│ 0.542166673981895 │ 19.716666666666665 │ 1.9166666666666667 │ 60 │ 1439 │
│ 0.902499997522682 │ 11.270625001192093 │ 1.95625 │ 160 │ 1438 │
│ 0.9715789457909146 │ 13.646616541353383 │ 2.0526315789473686 │ 133 │ 1437 │
│ 0.9682692398245518 │ 14.134615384615385 │ 2.076923076923077 │ 104 │ 1436 │
│ 1.1022105210705808 │ 13.778947368421052 │ 2.042105263157895 │ 95 │ 1435 │
此查询显示每个街区的接送次数，按一天中的小时细分
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结果如下所示
┌─pickup_ntaname───────────────────────────────────────────┬─pickup_hour─┬─pickups─┐
│ Airport │ 0 │ 3509 │
│ Airport │ 1 │ 1184 │
│ Airport │ 2 │ 401 │
│ Airport │ 3 │ 152 │
│ Airport │ 4 │ 213 │
│ Airport │ 5 │ 955 │
│ Airport │ 6 │ 2161 │
│ Airport │ 7 │ 3013 │
│ Airport │ 8 │ 3601 │
│ Airport │ 9 │ 3792 │
│ Airport │ 10 │ 4546 │
│ Airport │ 11 │ 4659 │
│ Airport │ 12 │ 4621 │
│ Airport │ 13 │ 5348 │
│ Airport │ 14 │ 5889 │
│ Airport │ 15 │ 6505 │
│ Airport │ 16 │ 6119 │
│ Airport │ 17 │ 6341 │
│ Airport │ 18 │ 6173 │
│ Airport │ 19 │ 6329 │
│ Airport │ 20 │ 6271 │
│ Airport │ 21 │ 6649 │
│ Airport │ 22 │ 6356 │
│ Airport │ 23 │ 6016 │
│ Allerton-Pelham Gardens │ 4 │ 1 │
│ Allerton-Pelham Gardens │ 6 │ 1 │
│ Allerton-Pelham Gardens │ 7 │ 1 │
│ Allerton-Pelham Gardens │ 9 │ 5 │
│ Allerton-Pelham Gardens │ 10 │ 3 │
│ Allerton-Pelham Gardens │ 15 │ 1 │
│ Allerton-Pelham Gardens │ 20 │ 2 │
│ Allerton-Pelham Gardens │ 23 │ 1 │
│ Annadale-Huguenot-Prince's Bay-Eltingville │ 23 │ 1 │
│ Arden Heights │ 11 │ 1 │让我们看看前往拉瓜迪亚或肯尼迪机场的行程
SELECT
pickup_datetime,
dropoff_datetime,
total_amount,
pickup_nyct2010_gid,
dropoff_nyct2010_gid,
CASE
WHEN dropoff_nyct2010_gid = 138 THEN 'LGA'
WHEN dropoff_nyct2010_gid = 132 THEN 'JFK'
END AS airport_code,
EXTRACT(YEAR FROM pickup_datetime) AS year,
EXTRACT(DAY FROM pickup_datetime) AS day,
EXTRACT(HOUR FROM pickup_datetime) AS hour
FROM trips
WHERE dropoff_nyct2010_gid IN (132, 138)
ORDER BY pickup_datetime响应为
┌─────pickup_datetime─┬────dropoff_datetime─┬─total_amount─┬─pickup_nyct2010_gid─┬─dropoff_nyct2010_gid─┬─airport_code─┬─year─┬─day─┬─hour─┐
│ 2015-07-01 00:04:14 │ 2015-07-01 00:15:29 │ 13.3 │ -34 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:09:42 │ 2015-07-01 00:12:55 │ 6.8 │ 50 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:23:04 │ 2015-07-01 00:24:39 │ 4.8 │ -125 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:27:51 │ 2015-07-01 00:39:02 │ 14.72 │ -101 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:32:03 │ 2015-07-01 00:55:39 │ 39.34 │ 48 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:34:12 │ 2015-07-01 00:40:48 │ 9.95 │ -93 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:38:26 │ 2015-07-01 00:49:00 │ 13.3 │ -11 │ 138 │ LGA │ 2015 │ 1 │ 0 │
│ 2015-07-01 00:41:48 │ 2015-07-01 00:44:45 │ 6.3 │ -94 │ 132 │ JFK │ 2015 │ 1 │ 0 │
│ 2015-07-01 01:06:18 │ 2015-07-01 01:14:43 │ 11.76 │ 37 │ 132 │ JFK │ 2015 │ 1 │ 1 │
4. 创建字典
如果您不熟悉 ClickHouse，了解**字典**的工作原理非常重要。简单地理解字典，它是在内存中存储的键->值对的映射。字典的所有详细信息和所有选项都链接在本教程的末尾。
让我们看看如何在您的 ClickHouse 服务中创建与表关联的字典。该表以及字典将基于一个 CSV 文件，该文件包含 265 行，每行代表纽约市的一个街区。街区映射到纽约市区（纽约市有 5 个区：布朗克斯区、布鲁克林区、曼哈顿区、皇后区和史坦顿岛），并且此文件也将纽瓦克机场 (EWR) 算作一个区。
这是 CSV 文件的一部分（为了清晰起见，显示为表格）。文件中的
LocationID列映射到trips表中的pickup_nyct2010_gid和dropoff_nyct2010_gid列LocationID 区 区域 服务区域 1 EWR 纽瓦克机场 EWR 2 皇后区 牙买加湾 区区域 3 布朗克斯区 艾勒顿/佩勒姆花园 区区域 4 曼哈顿区 字母城 黄色区域 5 史坦顿岛 阿登高地 区区域
该文件的 URL 为
https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv。运行以下 SQL，它将创建一个名为taxi_zone_dictionary的字典，并从 S3 中的 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(MIN 1 MAX 10)指定字典在 1 到 10 秒之间的某个随机时间后更新。（在大量服务器上更新字典源时，需要随机时间来分配负载。）
验证它是否成功 - 您应该获得 265 行（每行代表一个街区）
SELECT * FROM taxi_zone_dictionary使用
dictGet函数（或其变体）从字典中检索值。您传入字典的名称、您想要的值和键（在我们的示例中是taxi_zone_dictionary的LocationID列）。例如，以下查询返回
LocationID为 132 的区（如上所述，它是肯尼迪机场）SELECT dictGet('taxi_zone_dictionary', 'Borough', 132)肯尼迪机场位于皇后区，请注意检索值的耗时基本为 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，因为 4567 不是字典中
LocationID的值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此查询总结了在拉瓜迪亚或肯尼迪机场结束的每个区的出租车行程数。结果如下所示，请注意有很多行程的出发街区未知
┌─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.)
5. 执行联接
让我们编写一些将 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响应看起来很熟悉
┌─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函数，但JOIN语法对于 SQL 开发人员来说更熟悉。我们不经常在 ClickHouse 中使用
SELECT *- 您应该只检索您实际需要的列！但是很难找到一个需要很长时间的查询，因此此查询故意选择每一列并返回每一行（除了默认情况下响应中内置了 10,000 行的最大值），并且还执行每一行与字典的右联接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
恭喜！
干得好 - 您已经完成了本教程，希望您对如何使用 ClickHouse 有了更好的了解。以下是一些您可以下一步采取的操作
- 阅读 ClickHouse 中主键的工作原理 - 掌握这些知识将使您在成为 ClickHouse 专家之旅中更进一步
- 集成外部数据源，例如文件、Kafka、PostgreSQL、数据管道或许多其他数据源
- 将您喜欢的 UI/BI 工具 连接到 ClickHouse
- 查看 SQL 参考 并浏览各种函数。ClickHouse 拥有大量用于转换、处理和分析数据的函数
- 详细了解 字典