DuckDB 技巧-三

DuckDB 技巧-三

作者: Andra Ionescu ， Gabor Szarnyas

原文：https://duckdb.org/2024/11/29/duckdb-tricks-part-3.html

excerpt: 在本篇 DuckDB 技巧系列中，我们将分享如何更方便地处理表格，以及针对 Parquet 和 CSV 文件的性能优化技巧。

概述

我们继续之前的 DuckDB 技巧系列文章，
本篇将介绍一些 友好的 SQL 功能^[1] 以及性能优化的实用方法。

数据集

我们使用了 荷兰铁路服务数据集^[2] 的一个子集，该数据集已在荷兰铁路交通数据分析中介绍。
本次分析使用的是 2024 年 1 月到 10 月的数据：services-2024-01-to-10.zip^[3]。
您可以先下载并解压数据集，以便跟随本文示例操作。

从表中排除列

让我们先看看 8 月份的 CSV 数据。我们用 DuckDB 提供的 DESCRIBE 语句^[4] 检查列信息：

DESCRIBEFROM'services-2024-08.csv';

输出结果如下：

为了了解这些列的分布情况，可以使用 SUMMARIZE^[5]：

SUMMARIZE FROM'services-2024-08.csv';

SUMMARIZE 提供了 10 项列统计数据，包括 min、max 和 approx_unique 等。
假如我们不需要某些统计项，比如最小值和分位数，可以用 EXCLUDE 修饰符^[6] 来排除它们：

SELECT* EXCLUDE(min, max, q25, q50, q75) 
FROM (SUMMARIZE FROM'services-2024-08.csv');

或者，我们也可以结合 COLUMNS^[7] 和正则表达式来筛选列：

SELECT COLUMNS(c -> c NOTSIMILARTO'min|max|q.*') 
FROM (SUMMARIZE FROM'services-2024-08.csv');

两种方法都会输出以下剩余列的信息：

使用模式匹配重命名列

查看列名时，可以发现一些列包含空格和特殊符号（如分号 :）。这些符号让查询变得复杂，因为需要用双引号将列名括起来，比如：

SELECTDISTINCT "Service:Company" AS company
FROM'services-2024-08.csv'
ORDERBY company;

为了简化操作，我们可以用 COLUMNS 表达式批量替换列名中的特殊符号。以下查询将列名中的特殊符号替换为下划线 _：

SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
FROM'services-2024-08.csv'
);

重命名后的列名如下：

查询优化

重命名列名后，我们可以直接用小写的列名查询：

SELECTDISTINCT service_company
FROM (
SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
FROM'services-2024-08.csv'
    )
)
ORDERBY service_company;

输出结果显示所有公司名称。

使用通配符加载文件

现在可以将 3 个月的数据加载到一个表中。以下查询利用了通配符 *：

CREATEOR REPLACE TABLE services AS
SELECT COLUMNS('(.*?)_*$') AS "\1" 
FROM (
SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3" 
FROM'services-2024-*.csv'
    );

DuckDB 会自动检测所有文件的架构一致性，并将其合并，最终加载约 2000 万行数据。

重新排序 Parquet 文件

假设我们需要分析 Intercity Direct 列车^[8]（由 NS^[9] 运营）在终点站的平均延误时间。
虽然直接在 .csv 文件上运行查询是可行的，但由于缺乏元数据（例如最小-最大索引），性能较差。
先测量读取 CSV 的时间：

SELECTavg("Stransform: translateY(Arrival delay")
FROM'services-*.csv'
WHERE "Service:Company" ='NS'
AND "Service:Type" ='Intercity direct'
AND "Stop:Departure time" ISNULL;

耗时约 1.8 秒。
相同查询在已

加载的 DuckDB 表上只需约 35 毫秒：

SELECTavg(Stop_Arrival_delay)
FROM services
WHERE Service_Company ='NS'
AND Service_Type ='Intercity direct'
AND Stop_Departure_time ISNULL;

如需使用外部格式，可将数据导出为 Parquet 文件：

EXPORT DATABASE 'railway' (FORMAT PARQUET);

查询 Parquet 文件，运行时间约 90 毫秒：

SELECTavg(Stop_Arrival_delay)
FROM'railway/services.parquet'
WHERE Service_Company ='NS'
AND Service_Type ='Intercity direct'
AND Stop_Departure_time ISNULL;

对 Parquet 文件进行排序优化，查询更快：

COPY
(FROM'railway/services.parquet'ORDERBY Service_Company, Service_Type)
TO'railway/services.parquet';

再次查询，仅耗时 35 毫秒。排序文件使 DuckDB 能跳过更多无关数据。

Hive 分区

更进一步，我们可以用 Hive 分区^[10] 在磁盘上创建分层目录：

COPY services
TO'services-parquet-hive'
(FORMAT PARQUET, PARTITION_BY (Service_Company, Service_Type));

通过 DuckDB CLI 查看目录结构：

.sh tree services-parquet-hive

services-parquet-hive
├── Service_Company=Arriva
│   ├── Service_Type=Intercity
│   │   └── data_0.parquet
│   ├── Service_Type=Snelbus
│   │   └── data_0.parquet
…

查询分区数据集：

SELECTavg(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 ISNULL;

运行时间仅需 20 毫秒。
Hive 分区也支持 CSV 文件：

COPY services
TO'services-csv-hive'
(FORMAT CSV, PARTITION_BY (Service_Company, Service_Type));
SELECTavg(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 ISNULL;

查询时间缩短至约 150 毫秒，比普通 CSV 快 10 倍以上。

格式对比

结果：Intercity Direct 列车的平均延误时间为 3 分钟。

结语

以上就是 DuckDB 技巧系列第三部分。如果有更多技巧想分享，请通过社交媒体或 DuckDB Snippets^[11] 与我们联系。

引用链接

[1] 友好的 SQL 功能:https://duckdb.org/docs/sql/dialect/friendly_sql.md
[2]荷兰铁路服务数据集:https://www.rijdendetreinen.nl/en/open-data/train-archive
[3]services-2024-01-to-10.zip:https://blobs.duckdb.org/data/services-2024-01-to-10.zip
[4]DESCRIBE 语句:https://duckdb.org/docs/guides/meta/describe.md
[5]SUMMARIZE:https://duckdb.org/docs/guides/meta/summarize.md
[6]EXCLUDE 修饰符:https://duckdb.org/docs/sql/expressions/star.md
[7]COLUMNS:https://duckdb.org/docs/sql/expressions/star.md
[8]Intercity Direct 列车:https://en.wikipedia.org/wiki/Intercity_Direct
[9]NS:https://en.wikipedia.org/wiki/Nederlandse_Spoorwegen
[10]Hive 分区:https://duckdb.org/docs/data/partitioning/hive_partitioning.md
[11]DuckDB Snippets: https://duckdbsnippets.com/