命令行数据处理：使用 DuckDB 作为 Unix 工具

Gabor Szarnyas

2024-06-20 · 19 分钟

在这篇博客文章中，我们将深入终端，比较 DuckDB 与 Unix shell (Bash, Zsh 等) 中使用的传统工具。我们解决了一些需要投影和过滤等操作的问题，以演示在 DuckDB 中使用 SQL 查询与专用命令行工具之间的差异。在此过程中，我们将展示一些很酷的功能，例如 DuckDB 强大的 CSV 读取器和位置连接操作符。让我们开始吧！

Unix 哲学

为了奠定基础，让我们回顾一下Unix 哲学。它指出程序应该：

• 只做一件事，并把它做好，
• 协同工作，以及
• 处理文本流。

macOS、Linux 和 Windows 中的 WSL 等类 Unix 系统都采纳了这一哲学。grep、sed 和 sort 等工具无处不在，并广泛用于shell 脚本中。

作为一个专门构建的数据处理工具，DuckDB 非常符合 Unix 哲学。首先，它被设计为一个快速的进程内分析型 SQL 数据库系统（只做一件事，并把它做好）。其次，它有一个独立的命令行客户端，可以消费和生成 CSV 文件（协同工作），也支持读写文本流（处理文本流）。由于这些特性，DuckDB 在 Unix CLI 工具生态系统中表现良好，如多篇文章所示。

可移植性和可用性

虽然 Unix CLI 工具快速、健壮且可在所有主要平台上使用，但它们的语法通常繁琐且难以记住。更糟糕的是，这些工具在系统之间常常存在细微差异——想想 GNU sed 和 macOS 的 sed 之间的差异，或者程序之间正则表达式语法的差异，这被 Donald Knuth 的名言恰当地概括为“我将 Unix 定义为在同一屋檐下生活的 30 种正则表达式定义。”

虽然有一些专门用于数据帧处理的 shell，例如 Nushell 项目，但旧的 Unix shell（例如 Bourne shell sh 和 Bash）仍然是最广泛使用的，尤其是在服务器上。

与此同时，我们有 DuckDB，一个极其便携的数据库系统，在所有平台上使用相同的 SQL 语法。随着版本 1.0.0 最近发布，DuckDB 的语法——基于经过验证和广泛使用的 PostgreSQL 方言——现在处于稳定状态。DuckDB 的另一个吸引人的特点是它提供了一个交互式 shell，这有助于快速调试。此外，DuckDB 还提供多种宿主语言，并通过 WebAssembly 在浏览器中提供（https://shell.duckdb.org/），因此如果您决定在 shell 之外使用 SQL 脚本，DuckDB SQL 脚本可以移植到各种环境中而无需任何更改。

使用 Unix 工具和 DuckDB 进行数据处理

接下来，我们将举例说明如何使用大多数 Unix shell 中提供的 CLI 工具和 DuckDB SQL 查询来执行简单的数据处理任务。我们使用 DuckDB v1.0.0 并在内存模式下运行。这种模式对于我们正在处理的问题是合适的，因为我们不创建任何表，并且操作不是内存密集型的，因此没有数据需要持久化或溢出到磁盘。

数据集

我们使用四个输入文件，它们捕获了荷兰城市和机场的信息。

city,province,population
Amsterdam,North Holland,905234
Rotterdam,South Holland,656050
The Hague,South Holland,552995
Utrecht,Utrecht,361924
Eindhoven,North Brabant,238478
Groningen,Groningen,234649
Tilburg,North Brabant,224702
Almere,Flevoland,218096
Breda,North Brabant,184716
Nijmegen,Gelderland,179073

city,area
Amsterdam,219.32
Rotterdam,324.14
The Hague,98.13
Utrecht,99.21
Eindhoven,88.92
Groningen,197.96
Tilburg,118.13
Almere,248.77
Breda,128.68
Nijmegen,57.63

city,IATA
Amsterdam,AMS
Haarlemmermeer,AMS
Eindhoven,EIN
Groningen,GRQ
Eelde,GRQ
Maastricht,MST
Beek,MST
Rotterdam,RTM
The Hague,RTM

IATA,airport name
AMS,Amsterdam Airport Schiphol
EIN,Eindhoven Airport
GRQ,Groningen Airport Eelde
MST,Maastricht Aachen Airport
RTM,Rotterdam The Hague Airport

您可以将所有输入文件下载为单个 zip 文件。

投影列

投影列是非常常见的数据处理步骤。我们以 pop.csv 文件为例，投影第一列 city 和最后一列 population。

Unix Shell: cut

在 Unix shell 中，我们使用 cut 命令并指定文件分隔符 (-d) 和要投影的列 (-f)。

cut -d , -f 1,3 pop.csv

这将产生以下输出

city,population
Amsterdam,905234
Rotterdam,656050
The Hague,552995
Utrecht,361924
Eindhoven,238478
Groningen,234649
Tilburg,224702
Almere,218096
Breda,184716
Nijmegen,179073

DuckDB: SELECT

在 DuckDB 中，我们可以使用 CSV 阅读器加载数据，然后使用 SELECT 子句和列索引 (#i) 来指定要投影的列

SELECT #1, #3 FROM 'pop.csv';

请注意，我们不必定义任何模式或将数据加载到表中。相反，我们只是在 FROM 子句中使用了 'pop.csv'，就像我们使用常规表一样。DuckDB 检测到这是一个 CSV 文件并调用 read_csv 函数，该函数自动推断 CSV 文件的方言（分隔符、引号是否存在等）以及表的模式。这允许我们简单地使用 SELECT #1, #3 来投影列。我们也可以使用更具可读性的语法 SELECT city, population。

为了使使用 Unix 工具和 DuckDB 解决方案的输出等效，我们将查询封装在 COPY ... TO 语句中

COPY (
    SELECT #1, #3 FROM 'pop.csv'
  ) TO '/dev/stdout/';

此查询产生与上面所示的 Unix 命令输出相同的结果。

要将其转换为独立的 CLI 命令，我们可以使用 -c query 参数调用 DuckDB 命令行客户端，该参数将运行 SQL 查询并在完成后退出。使用此技术，上述查询可以转换为以下一行命令

duckdb -c "COPY (SELECT #1, #3 FROM 'pop.csv') TO '/dev/stdout/'"

在下文中，我们将省略使用独立 duckdb 命令的代码块：所有解决方案都可以在 duckdb -c query 模板中执行，并产生与使用 Unix 工具的解决方案相同的结果。

文件排序

另一个常见的任务是根据给定列对文件进行排序。让我们根据人口对省份内的城市进行排名。为此，我们需要首先根据 pop.csv 文件的 province 名称按升序排序，然后根据 population 按降序排序。然后我们首先返回 province 列，然后是 city 和 population 列。

Unix Shell: sort

在 Unix shell 中，我们依赖 sort 工具。我们使用 -t 参数指定 CSV 文件的分隔符，并使用 -k 参数设置排序键。我们首先使用 -k 2,2 对第二列 (province) 进行排序。然后，我们对第三列 (population) 进行排序，使用 -k 3rn 将顺序设置为反向 (r) 和数字 (n)。请注意，我们需要单独处理文件头：我们使用 head -n 1 获取第一行，使用 tail -n +2 获取其余行，对后者进行排序，然后将它们与文件头连接起来。最后，我们执行投影以重新排列列。不幸的是，cut 命令无法重新排列列，所以我们使用 awk 代替

(head -n 1 pop.csv; tail -n +2 pop.csv \
    | sort -t , -k 2,2 -k 3rn) \
    | awk -F , '{ print $2 "," $1 "," $3 }'

结果如下

province,city,population
Flevoland,Almere,218096
Gelderland,Nijmegen,179073
Groningen,Groningen,234649
North Brabant,Eindhoven,238478
North Brabant,Tilburg,224702
North Brabant,Breda,184716
North Holland,Amsterdam,905234
South Holland,Rotterdam,656050
South Holland,The Hague,552995
Utrecht,Utrecht,361924

DuckDB: ORDER BY

在 DuckDB 中，我们只需加载 CSV 并通过 SELECT province, city, population 指定列顺序，然后设置选定列的排序条件 (province ASC 和 population DESC)。CSV 读取器自动检测类型，因此排序默认为数字。最后，我们将查询用 COPY 语句包围，以将结果打印到标准输出。

COPY (
    SELECT province, city, population
    FROM 'pop.csv'
    ORDER BY province ASC, population DESC
  ) TO '/dev/stdout/';

列相交

一个常见的任务是计算两列的交集，即查找同时存在于两列中的实体。让我们找出既在人口最多的前 10 个城市中，又拥有自己机场的城市。

Unix Shell: comm

Unix 解决方案的交集使用 comm 工具，旨在逐行比较两个已排序的文件。我们首先从两个文件中 cut 出相关列。由于排序要求，我们在执行交集之前对两个输入都应用 sort。交集是使用 comm -12 执行的，其中参数 -12 意味着我们只想保留同时存在于两个文件中的行。我们再次依赖 head 和 tail 来在处理过程中分别处理文件头和文件的其余部分，并在最后将它们连接起来。

head -n 1 pop.csv | cut -d , -f 1; \
    comm -12 \
        <(tail -n +2 pop.csv | cut -d , -f 1 | sort) \
        <(tail -n +2 cities-airports.csv | cut -d , -f 1 | sort) 

脚本产生以下输出

city
Amsterdam
Eindhoven
Groningen
Rotterdam
The Hague

DuckDB: INTERSECT ALL

DuckDB 解决方案读取 CSV 文件，投影 city 字段并应用 INTERSECT ALL 子句来计算交集

COPY (
    SELECT city FROM 'pop.csv'
    INTERSECT ALL
    SELECT city FROM 'cities-airports.csv'
  ) TO '/dev/stdout/';

拼接行

逐行拼接行是一项常见的任务。在我们的示例中，我们知道 pop.csv 和 area.csv 文件具有相同数量的行，因此我们可以生成一个包含数据集中每个城市的人口和面积的单个文件。

Unix Shell: paste

在 Unix shell 中，我们使用 paste 命令，并使用 cut 删除重复的 city 字段

paste -d , pop.csv area.csv | cut -d , -f 1,2,3,5

输出如下

city,province,population,area
Amsterdam,North Holland,905234,219.32
Rotterdam,South Holland,656050,324.14
The Hague,South Holland,552995,98.13
Utrecht,Utrecht,361924,99.21
Eindhoven,North Brabant,238478,88.92
Groningen,Groningen,234649,197.96
Tilburg,North Brabant,224702,118.13
Almere,Flevoland,218096,248.77
Breda,North Brabant,184716,128.68
Nijmegen,Gelderland,179073,57.63

DuckDB: POSITIONAL JOIN

在 DuckDB 中，我们可以使用 POSITIONAL JOIN。这种连接类型是 DuckDB 的SQL 扩展之一，它提供了一种简洁的语法，可以根据表中每一行的位置逐行组合表。使用 POSITIONAL JOIN 将两个表连接在一起会产生两个 city 列——我们使用 EXCLUDE 子句来删除重复的列

COPY (
    SELECT pop.*, area.* EXCLUDE city
    FROM 'pop.csv'
    POSITIONAL JOIN 'area.csv'
  ) TO '/dev/stdout/';

过滤

过滤是另一个非常常见的操作。为此，我们将使用cities-airports.csv 文件。对于每个机场，此文件包含其 IATA 代码以及它服务的主要城市

city,IATA
Amsterdam,AMS
Haarlemmermeer,AMS
Eindhoven,EIN
...

让我们尝试制定两个查询

1. 查找所有名称以 dam 结尾的城市。

2. 查找所有 IATA 代码等同于所服务城市名称前三个字母的机场，但该城市名称不以 dam 结尾。

Unix Shell: grep

为了在 Unix shell 中回答第一个问题，我们使用 grep 和正则表达式 ^[^,]*dam,

grep "^[^,]*dam," cities-airports.csv

在此表达式中，^ 表示行首，[^,]* 搜索不包含逗号字符（分隔符）的字符串。表达式 dam, 确保第一个字段的字符串末尾是 dam。输出是

Amsterdam,AMS
Rotterdam,RTM

让我们尝试回答第二个问题。为此，我们需要将 city 字段中的前三个字符与 IATA 字段进行匹配，但我们需要以不区分大小写的方式进行。我们还需要使用一个否定条件来排除城市名称以 dam 结尾的行。这两个要求都很难通过单个 grep 或 egrep 命令实现，因为它们缺乏对两个功能的支持。首先，它们不支持使用反向引用的不区分大小写匹配（仅 grep -i 不足以确保这一点）。其次，它们不支持负向回顾断言。因此，我们使用 pcregrep，并按如下方式制定问题

pcregrep -i '^([a-z]{3}).*?(?<!dam),\1$' cities-airports.csv

这里，我们用不区分大小写标志 (-i) 调用 pcregrep，这在 pcregrep 中也影响反向引用，例如 \1。我们用 ([a-z]{3}) 捕获前三个字母（例如 Ams），并用反向引用将其与第二个字段匹配：,\1$。我们使用非贪婪的 .*? 来找到第一个字段的末尾，然后用 (?<!dam) 表达式应用负向回顾断言，以确保该字段不以 dam 结尾。结果是一行

Eindhoven,EIN

DuckDB: WHERE ... LIKE

现在在 DuckDB 中回答这些问题。为了回答第一个问题，我们可以使用LIKE 进行模式匹配。标题不应包含在输出中，因此我们使用 HEADER false 禁用它。完整的查询如下所示

COPY (
    FROM 'cities-airports.csv'
    WHERE city LIKE '%dam'
  ) TO '/dev/stdout/' (HEADER false);

对于第二个问题，我们使用字符串切片提取前三个字符，upper 确保不区分大小写，并使用 NOT LIKE 作为否定条件

COPY (
    FROM 'cities-airports.csv'
    WHERE upper(city[1:3]) = IATA
      AND city NOT LIKE '%dam'
  ) TO '/dev/stdout/' (HEADER false);

这些查询返回的结果与使用 grep 和 pcregrep 的解决方案完全相同。

在这两个查询中，我们都使用了FROM 优先语法。如果省略 SELECT 子句，则查询的执行方式与使用 SELECT * 相同，即它返回所有列。

文件连接

连接表是数据处理中的一项基本任务。我们的下一个例子将使用连接来返回城市名称-机场名称的组合。这是通过将 cities-airports.csv 和 airport-names.csv 文件基于它们的 IATA 代码字段进行连接来实现的。

Unix Shell: join

Unix 工具通过 join 命令支持文件连接，该命令在公共字段上连接两个已排序输入的行。为了实现这一点，我们根据它们的 IATA 字段对文件进行排序，然后对第一个文件的第 2 列 (-1 2) 和第二个文件的第 1 列 (-2 1) 执行连接。我们必须省略标题才能使 join 命令工作，所以我们这样做，并使用 echo 命令构造一个新的标题

echo "IATA,city,airport name"; \
    join -t , -1 2 -2 1 \
        <(tail -n +2 cities-airports.csv | sort -t , -k 2,2) \
        <(tail -n +2 airport-names.csv   | sort -t , -k 1,1)

结果如下

IATA,city,airport name
AMS,Amsterdam,Amsterdam Airport Schiphol
AMS,Haarlemmermeer,Amsterdam Airport Schiphol
EIN,Eindhoven,Eindhoven Airport
GRQ,Eelde,Groningen Airport Eelde
GRQ,Groningen,Groningen Airport Eelde
MST,Beek,Maastricht Aachen Airport
MST,Maastricht,Maastricht Aachen Airport
RTM,Rotterdam,Rotterdam The Hague Airport
RTM,The Hague,Rotterdam The Hague Airport

DuckDB

在 DuckDB 中，我们加载 CSV 文件并使用NATURAL JOIN 子句连接它们，该子句在同名列上进行连接。为了确保结果与 Unix 解决方案的结果匹配，我们使用ORDER BY ALL 子句，它对所有列进行排序，从第一列开始，然后依次检查它们以进行平局处理，直到最后一列。

COPY (
    SELECT "IATA", "city", "airport name"
    FROM 'cities-airports.csv'
    NATURAL JOIN 'airport-names.csv'
    ORDER BY ALL
  ) TO '/dev/stdout/';

替换字符串

您可能已经注意到我们正在使用非常干净的数据集。这当然非常不现实，所以邪恶地扭转一下，让我们稍微降低数据质量

• 将省份名称中的空格替换为下划线，例如将 North Holland 变为 North_Holland。
• 添加千位分隔逗号，例如将 905234 变为 905,234。
• 将 CSV 的分隔符更改为分号字符 (;)。

顺便说一下，这次也通过 HTTPS 获取数据集，使用 URL https://duckdb.net.cn/data/cli/pop.csv。

Unix Shell: curl 和 sed

在 Unix 中，远程数据集通常通过 curl 获取。curl 的输出被管道传输到后续的处理步骤，在本例中是一堆 sed 命令。

curl -s https://duckdb.net.cn/data/cli/pop.csv \
    | sed 's/\([^,]*,.*\) \(.*,[^,]*\)/\1_\2/g' \
    | sed 's/,/;/g' \
    | sed 's/\([0-9][0-9][0-9]\)$/,\1/'

这导致以下输出

city;province;population
Amsterdam;North_Holland;905,234
Rotterdam;South_Holland;656,050
The Hague;South_Holland;552,995
Utrecht;Utrecht;361,924
Eindhoven;North_Brabant;238,478
Groningen;Groningen;234,649
Tilburg;North_Brabant;224,702
Almere;Flevoland;218,096
Breda;North_Brabant;184,716
Nijmegen;Gelderland;179,073

DuckDB: httpfs 和 regexp_replace

在 DuckDB 中，我们使用以下查询

COPY (
    SELECT
        city,
        replace(province, ' ', '_') AS province,
        regexp_replace(population::VARCHAR, '([0-9][0-9][0-9])$', ',\1')
            AS population
    FROM 'https://duckdb.net.cn/data/cli/pop.csv'
  ) TO '/dev/stdout/' (DELIMITER ';');

请注意，FROM 子句现在是 HTTPS URL，而不是简单的 CSV 文件。 https:// 前缀的存在会触发 DuckDB 加载httpfs 扩展并使用它来获取 JSON 文档。我们使用replace 函数将空格替换为下划线，并使用regexp_replace 函数进行正则表达式替换。（我们也可以使用字符串格式化函数，例如format和printf）。为了将分隔符更改为分号，我们使用带有 DELIMITER ';' 选项的 COPY 语句序列化文件。

读取 JSON

作为最后的练习，让我们查询 GitHub 上的 duckdb/duckdb 仓库获得的星数。

Unix Shell: curl 和 jq

在 Unix 工具中，我们可以使用 curl 从 https://api.github.com 获取 JSON 文件，并将其输出管道传输到 jq 来查询 JSON 对象。

curl -s https://api.github.com/repos/duckdb/duckdb \
    | jq ".stargazers_count"

DuckDB: read_json

在 DuckDB 中，我们使用 read_json 函数，通过远程 HTTPS 终结点的 URL 调用它。JSON 文件的模式会自动检测，因此我们只需使用 SELECT 返回所需的字段。

SELECT stargazers_count
  FROM read_json('https://api.github.com/repos/duckdb/duckdb');

输出

这两个命令都返回仓库当前的星数。

性能

此时，您可能想知道 DuckDB 解决方案的性能。毕竟，我们之前的例子都只有几行代码，所以相互基准测试不会产生任何可衡量的性能差异。因此，让我们切换到我们在上一篇博客文章中使用的荷兰铁路服务数据集，并提出一个不同的问题。

我们将使用2023 年铁路服务文件 (services-2023.csv.gz)，并统计该年运营的城际列车服务数量。

在 Unix 中，我们可以使用 gzcat 命令将 csv.gz 文件解压缩到管道中。然后，我们可以使用 grep 或 pcregrep（性能更好），最后使用 wc 命令统计行数 (-l)。在 DuckDB 中，内置的 CSV 阅读器也支持压缩 CSV 文件，因此我们可以直接使用，无需额外配置。

gzcat services-2023.csv.gz | grep '^[^,]*,[^,]*,Intercity,' | wc -l
gzcat services-2023.csv.gz | pcregrep '^[^,]*,[^,]*,Intercity,' | wc -l
duckdb -c "SELECT count(*) FROM 'services-2023.csv.gz' WHERE \"Service:Type\" = 'Intercity';"

我们还在未压缩的输入上测试了这些工具

gunzip -k services-2023.csv.gz
grep '^[^,]*,[^,]*,Intercity,' services-2023.csv | wc -l
pcregrep '^[^,]*,[^,]*,Intercity,' services-2023.csv | wc -l
duckdb -c "SELECT count(*) FROM 'services-2023.csv' WHERE \"Service:Type\" = 'Intercity';"

为了减少测量中的噪声，我们使用了 hyperfine 基准测试工具，并取了 10 次运行的平均执行时间。实验在配备 12 核 M2 Pro CPU 和 32 GB RAM 的 MacBook Pro 上进行，运行 macOS Sonoma 14.5。要复现它们，请运行 grep-vs-duckdb-microbenchmark.sh 脚本。下表显示了解决方案在压缩和未压缩输入上的运行时间

结果显示，在压缩输入上，grep 是最慢的，而 DuckDB 略逊于 gzcat+pcregrep，后者运行时间为 3.1 秒，而 DuckDB 为 4.2 秒。在未压缩输入上，DuckDB 可以从一开始就利用所有 CPU 内核（而不是从单线程解压缩步骤开始），使其性能显著优于 grep 和 pcregrep：比 pcregrep 快 2.5 倍，比 grep 快 15 倍以上。

虽然这个例子相当简单，但随着查询变得越来越复杂，优化机会也越来越多，并且可能会生成更大的中间数据集。虽然这两者都可以在 shell 脚本中解决（通过手动实现优化并将中间数据集写入磁盘），但这些可能不如 DBMS 能想出的效率高。实现复杂管道的 shell 脚本也可能非常脆弱，即使是微小的更改也需要重新考虑，这使得对于更复杂的问题，使用数据库的性能优势更为显著。

总结

在这篇文章中，我们将 DuckDB 作为独立的 CLI 应用程序，并探讨了它补充或替代现有命令行工具（sort、grep、comm、join 等）的能力。虽然我们显然非常喜欢 DuckDB，并在许多情况下更喜欢使用它，但我们也相信 Unix 工具也有其独特之处：在大多数系统上，它们已经预装，并且精心选择的 Unix 命令工具链可以是快速、高效和可移植的（感谢POSIX 兼容性）。此外，它们对于某些问题可能非常简洁。然而，要利用它们的优势，您需要学习每个工具的语法和怪癖，例如 grep 变体、awk 以及更高级的工具，例如 xargs 和 parallel。与此同时，DuckDB 的 SQL 易于学习（您可能已经知道很多了），并且 DuckDB 会为您处理大部分优化。

如果您有 DuckDB 在 CLI 中的最喜欢用例，请在社交媒体上告诉我们，或提交到 DuckDB snippets。祝您玩得开心！