DuckDB 小技巧 – 第三部分
TL;DR: 在 DuckDB 小技巧系列的新一期中，我们将介绍便捷的表处理功能以及针对 Parquet 和 CSV 文件的性能优化技巧。
概览
我们继续我们的 DuckDB 小技巧 系列，带来第三部分，其中展示了 便捷的 SQL 功能 和性能优化。
| 操作 | SQL 指令 |
|---|---|
| 从表中排除列 | EXCLUDE/COLUMNS(...), NOT SIMILAR TO |
| 使用模式匹配重命名列 | COLUMNS(...) AS ... |
| 使用 globbing 加载 | FROM '*.csv' |
| Parquet 文件重排序 | COPY (FROM ... ORDER BY ...) TO ... |
| Hive 分区 | hive_partitioning = true |
数据集
我们将使用 荷兰铁路服务数据集 的一个子集，该数据集曾出现在 今年早些时候的一篇博客文章 中。这次，我们将使用 2024 年 1 月至 10 月的 CSV 文件：services-2024-01-to-10.zip。如果您想跟着示例操作，请在继续之前下载并解压数据集。
从表中排除列
首先，让我们看看 CSV 文件中的数据。我们选取八月份的 CSV 文件，并使用 DESCRIBE 语句对其进行检查。
DESCRIBE FROM 'services-2024-08.csv';
结果是一个包含列名和列类型的表。
| 列名 (column_name) | 列类型 (column_type) | 空 | 键 | 默认值 (default) | 额外信息 (extra) |
|---|---|---|---|---|---|
| Service:RDT-ID | BIGINT | 是 (YES) | NULL | NULL | NULL |
| 服务：日期 | DATE | 是 (YES) | NULL | NULL | NULL |
| Service:Type | VARCHAR | 是 (YES) | NULL | NULL | NULL |
| Service:Company | VARCHAR | 是 (YES) | NULL | NULL | NULL |
| Service:Train number | BIGINT | 是 (YES) | NULL | NULL | NULL |
| … | … | … | … | … | … |
现在，让我们使用 SUMMARIZE 检查关于列的一些统计信息。
SUMMARIZE FROM 'services-2024-08.csv';
使用 SUMMARIZE，我们可以得到关于数据的 10 项统计数据（min、max、approx_unique 等）。如果我们想从结果中移除其中一些，可以使用 EXCLUDE 修饰符。例如，要排除 min、max 以及分位数 q25、q50、q75，我们可以发出以下命令：
SELECT * EXCLUDE(min, max, q25, q50, q75)
FROM (SUMMARIZE FROM 'services-2024-08.csv');
另一种方法是，我们可以使用 COLUMNS 表达式配合 NOT SIMILAR TO 运算符。这适用于正则表达式：
SELECT COLUMNS(c -> c NOT SIMILAR TO 'min|max|q.*')
FROM (SUMMARIZE FROM 'services-2024-08.csv');
在两种情况下，结果表都将包含剩余的 5 个统计列：
| 列名 (column_name) | 列类型 (column_type) | approx_unique (近似唯一值) | avg | std | count | null_percentage |
|---|---|---|---|---|---|---|
| Service:RDT-ID | BIGINT | 259022 | 14200071.03736433 | 59022.836209662266 | 1846574 | 0.00 |
| 服务：日期 | DATE | 32 | NULL | NULL | 1846574 | 0.00 |
| Service:Type | VARCHAR | 20 | NULL | NULL | 1846574 | 0.00 |
| Service:Company | VARCHAR | 12 | NULL | NULL | 1846574 | 0.00 |
| Service:Train number | BIGINT | 17264 | 57781.81688196628 | 186353.76365744913 | 1846574 | 0.00 |
| … | … | … | … | … | … | … |
使用模式匹配重命名列
检查列后，我们发现它们的名称包含空格和分号（:）。这些特殊字符使得编写查询有些繁琐，因为它们需要用双引号引用列名。例如，我们必须在以下查询中写入 "Service:Company"：
SELECT DISTINCT "Service:Company" AS company,
FROM 'services-2024-08.csv'
ORDER BY company;
让我们看看如何使用 COLUMNS 表达式重命名列。要替换特殊字符（最多 2 个），我们可以编写以下查询：
SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
FROM 'services-2024-08.csv'
);
在查询开头添加 DESCRIBE，即可看到重命名后的列：
| 列名 (column_name) | 列类型 (column_type) | 空 | 键 | 默认值 (default) | 额外信息 (extra) |
|---|---|---|---|---|---|
| Service_RDT_ID | BIGINT | 是 (YES) | NULL | NULL | NULL |
| Service_Date | DATE | 是 (YES) | NULL | NULL | NULL |
| Service_Type | VARCHAR | 是 (YES) | NULL | NULL | NULL |
| Service_Company | VARCHAR | 是 (YES) | NULL | NULL | NULL |
| Service_Train_number | BIGINT | 是 (YES) | NULL | NULL | NULL |
| … | … | … | … | … | … |
让我们从第一个 COLUMNS 表达式开始分解一下这个查询：
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
在这里，我们使用正则表达式，其中 (\w*) 组捕获 0 到 n 个单词字符（[0-9A-Za-z_]）。同时，表达式 \W* 捕获 0 到 n 个非单词字符（[^0-9A-Za-z_]）。在别名部分，我们使用 \i 来引用捕获组 i，因此 "\1_\2_\3" 意味着我们只保留单词字符并用下划线（_）分隔它们的组。然而，因为有些列名包含由空格分隔的单词，而有些则不包含，所以在此 SELECT 语句后，我们得到带有尾随下划线（_）的列名，例如 Service_Date_。因此，我们需要一个额外的处理步骤：
SELECT COLUMNS('(.*?)_*$') AS "\1"
在这里，我们捕获不带尾随下划线的字符组，并将列重命名为 \1，从而删除尾随下划线。
为了让查询编写更方便，我们可以利用 标识符的大小写不敏感性，以小写形式查询列名：
SELECT DISTINCT service_company
FROM (
SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
FROM 'services-2024-08.csv'
)
)
ORDER BY service_company;
| Service_Company |
|---|
| Arriva |
| Blauwnet |
| Breng |
| DB |
| Eu Sleeper |
| … |
即使我们在查询中使用了小写字母，返回的列名仍保留其原始大小写。
使用 Globbing 加载
既然我们可以简化列名了，让我们将所有 3 个月的数据摄取到一个表中：
CREATE OR REPLACE TABLE services AS
SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
FROM 'services-2024-*.csv'
);
在内部的 FROM 子句中，我们使用 * glob 语法来匹配所有文件。DuckDB 自动检测到所有文件具有相同的模式，并将它们合并在一起。我们现在有一个包含从 1 月到 10 月所有数据的表，总计近 2000 万行。
Parquet 文件重排序
假设我们想分析由 Nederlandse Spoorwegen (NS) 运营的 Intercity Direct 列车在服务最终目的地测量的平均延迟。尽管我们可以直接在 .csv 文件上运行此分析，但元数据（例如模式和最小-最大索引）的缺乏将限制性能。让我们通过在 CLI 客户端中打开 计时器 来测量这一点：
.timer on
SELECT avg("Stop:Arrival delay")
FROM 'services-*.csv'
WHERE "Service:Company" = 'NS'
AND "Service:Type" = 'Intercity direct'
AND "Stop:Departure time" IS NULL;
此查询大约需要 1.8 秒。现在，如果我们在已加载到 DuckDB 的 services 表上运行相同的查询，查询会快得多：
SELECT avg(Stop_Arrival_delay)
FROM services
WHERE Service_Company = 'NS'
AND Service_Type = 'Intercity direct'
AND Stop_Departure_time IS NULL;
运行时间约为 35 毫秒。
如果我们想使用外部二进制文件格式，我们还可以将数据库导出为单个 Parquet 文件：
EXPORT DATABASE 'railway' (FORMAT parquet);
然后我们可以直接查询它，如下所示：
SELECT avg(Stop_Arrival_delay)
FROM 'railway/services.parquet'
WHERE Service_Company = 'NS'
AND Service_Type = 'Intercity direct'
AND Stop_Departure_time IS NULL;
这种格式的运行时间约为 90 毫秒——比 DuckDB 自己的文件格式稍慢，但比读取原始 CSV 文件快约 20 倍。
如果我们对查询过滤的字段有先验知识，我们可以对 Parquet 文件进行重排序以提高查询性能。
COPY
(FROM 'railway/services.parquet' ORDER BY Service_Company, Service_Type)
TO 'railway/services.parquet';
如果我们再次运行查询，它会明显更快，只需 35 毫秒。这归功于 部分读取，它使用 zonemap（最小-最大索引）来限制需要扫描的数据量。对文件进行重排序可以使 DuckDB 跳过更多数据，从而加快查询时间。
Hive 分区
为了进一步加快查询速度，我们可以使用 Hive 分区 来创建与查询中使用的过滤条件匹配的磁盘目录布局。
COPY services
TO 'services-parquet-hive'
(FORMAT parquet, PARTITION_BY (Service_Company, Service_Type));
让我们从 DuckDB 的 CLI 中使用 .sh 点命令查看目录：
.sh tree services-parquet-hive
services-parquet-hive
├── Service_Company=Arriva
│ ├── Service_Type=Extra%20trein
│ │ └── data_0.parquet
│ ├── Service_Type=Nachttrein
│ │ └── data_0.parquet
│ ├── Service_Type=Snelbus%20ipv%20trein
│ │ └── data_0.parquet
│ ├── Service_Type=Sneltrein
│ │ └── data_0.parquet
│ ├── Service_Type=Stopbus%20ipv%20trein
│ │ └── data_0.parquet
│ ├── Service_Type=Stoptrein
│ │ └── data_0.parquet
│ └── Service_Type=Taxibus%20ipv%20trein
│ └── data_0.parquet
├── Service_Company=Blauwnet
│ ├── Service_Type=Intercity
│ │ └── data_0.parquet
...
我们现在可以通过传递 hive_partitioning = true 标志，在 Hive 分区数据集上运行查询：
SELECT avg(Stop_Arrival_delay)
FROM read_parquet(
'services-parquet-hive/**/*.parquet',
hive_partitioning = true
)
WHERE Service_Company = 'NS'
AND Service_Type = 'Intercity direct'
AND Stop_Departure_time IS NULL;
此查询现在大约需要 20 毫秒，因为 DuckDB 可以使用目录结构进一步限制读取。Hive 分区的一个巧妙之处在于它甚至适用于 CSV 文件！
COPY services
TO 'services-csv-hive'
(FORMAT csv, PARTITION_BY (Service_Company, Service_Type));
SELECT avg(Stop_Arrival_delay)
FROM read_csv('services-csv-hive/**/*.csv', hive_partitioning = true)
WHERE Service_Company = 'NS'
AND Service_Type = 'Intercity direct'
AND Stop_Departure_time IS NULL;
尽管 CSV 文件缺乏任何形式的元数据，但 DuckDB 可以依靠目录结构将扫描限制在相关目录，从而使执行时间约为 150 毫秒，比读取所有 CSV 文件快 10 倍以上。
如果所有这些格式和结果让您感到头晕目眩，别担心。我们为您准备了这张汇总表：
| 格式 | 查询运行时间 (ms) |
|---|---|
| DuckDB 文件格式 | 35 |
| CSV（原始） | 1800 |
| CSV（Hive 分区） | 150 |
| Parquet（原始） | 90 |
| Parquet（重排序） | 35 |
| Parquet（Hive 分区） | 20 |
哦，我们忘了报告结果。Intercity Direct 列车的平均延迟是 3 分钟！
结束语
DuckDB 小技巧的第三部分就到这里。如果您有想分享的小技巧，请在我们的社交媒体网站上与 DuckDB 团队分享，或者提交到 DuckDB Snippets 网站（由 MotherDuck 的朋友维护）。
