垂直堆叠，如关系模型所愿：UNION ALL BY NAME

Alex Monahan

2025-01-10 · 15 分钟

概览

听说过 SQL 的 CORRESPONDING 关键字吗？我也没听说过！好吧，它至少从 1992 年起就出现在 SQL 标准中了，但几乎没人实现它！CORRESPONDING 曾试图修复 SQL 中的一个缺陷——但它失败了。是时候让 SQL 在堆叠数据时回归关系模型的根源了。让我们把时间倒回到 1969 年……

你刚刚开上了自己的 福特野马 Boss 302，在每个街角漂移着赶往图书馆，只为阅读 IBM 出版的 Edgar Codd 的最新 研究报告。（我们需要一部关于数据库的 Netflix 特辑吗？）阅读那份报告时，你身穿格子衫，获得了一个关键的洞察：数据应该被视为无序集！（严格来说是 多重集——重复项无处不在……）行应该被视为无序的，列也应该如此。关系模型是 正道。任何建立在关系模型之上的语言都应绝对遵循这些核心原则。

几年后，你了解了 SQL，它看起来是个相当酷的主意。声明式、关系型——完全没有维护顺序的麻烦。毕竟，你不想被顺序束缚。如果你改变了查询数据的方式怎么办？集合是思考这些问题的最佳方式。

更多时间过去了，然后，你需要在 SQL 中堆叠一些数据。这应该很容易——我只需取出两个表并将它们堆叠起来，相应的属性就会映射在一起。无需担心顺序，当然也无需确保关系具有完全相同的宽度。

等等。这不对劲。

我必须确保列的顺序完全正确？而且两个关系中列的数量必须完全相同？这些 SQL 开发者是不是忘了 Codd 了？

快进几十年，DuckDB 正在让 SQL 中的堆叠再次变得很酷。

再次让垂直堆叠变得很酷

除了传统的 UNION 和 UNION ALL 运算符之外，DuckDB 还添加了 UNION BY NAME 和 UNION ALL BY NAME。这些操作符将通过匹配列名而非其顺序，垂直堆叠多个关系（例如，SELECT 语句）。举个例子，我们提供了乱序的列 a 和 b，甚至引入了全新的列 c，堆叠仍然会成功。

SELECT
    42 AS a,
    'woot' AS b

UNION ALL BY NAME

SELECT
    'woot2' AS b,
    9001 AS a,
    'more wooting' AS c;

任何未在所有关系中出现的列，在缺失的地方将填充为 NULL。

此功能解锁了多种有用的模式，可增加灵活性并节省时间。例如：

• 堆叠列顺序不同的数据集
• 为分析添加新列，但仅针对部分行
• 将完全不相关的数据集组合成一个结果集
  • 如果您的 IDE、BI 工具或 API 每次只能返回一个结果集，但您需要查看多个数据集，这会非常有用。

DuckDB 自 2022 年 8 月以来就具备此功能，但最近此功能的性能和可扩展性得到了大幅提升！请参阅文章末尾的一些微基准测试。

UNION 与 UNION ALL

如果只使用关键字 UNION，堆叠时会移除重复项。而使用 UNION ALL，允许重复项，并且堆叠无需额外处理。

不幸的是，我们要感谢 Codd 带来了这个令人困惑的部分！要是 UNION ALL 是默认值就好了……通常，UNION ALL（以及它的新对应项 UNION ALL BY NAME！）是期望的行为，因为它们忠实地复制输入关系，只是将它们堆叠在一起。这也带来了更高的性能，因为 UNION 进行的去重操作在处理大型数据集时可能非常耗时。最后，UNION ALL 保留了原始行顺序。

读取多个文件

当从具有不同模式的多个文件中查询数据时，这种列匹配功能变得特别有用。DuckDB 在用于拉取外部平面文件的表函数中提供了布尔参数 union_by_name。

• read_csv
• read_json
• read_parquet

要读取多个文件，DuckDB 可以在文件路径参数中使用 glob 模式（或文件列表，或 glob 模式列表！）。如果这些文件可能具有不同的模式，添加 union_by_name=True 将允许它们被读取和堆叠！任何未在特定文件中出现的列将填充 NULL 值。例如：

COPY (SELECT 'Star' AS col1) TO 'star.parquet';
COPY (SELECT 'Wars' AS col2) TO 'wars.parquet';

FROM read_parquet(
    ['star.parquet', 'wars.parquet'],
    union_by_name = true);

如果您的文件模式不同，并且您没有预料到，DuckDB 友好的错误消息会建议使用 union_by_name 参数！无需记忆。

如果您正在尝试读取具有不同模式的文件，请尝试设置 union_by_name=True

数据湖

数据湖中随时间推移出现模式变化非常常见，因此这为 DuckDB 在这些环境中解锁了许多额外用途。此功能的次要效果是，您现在可以随意更改数据湖模式！现在，随着时间的推移向数据湖添加更多属性变得轻而易举——DuckDB 将准备好处理分析！

DuckDB 读取湖仓表格式（如 Delta 和 Iceberg）的扩展会处理格式自身元数据中的模式演变，因此不需要 union_by_name。

按名称插入数据

垂直堆叠数据的另一个用例是在插入现有表时。DuckDB 的语法 INSERT INTO my_table BY NAME 提供了相同的灵活性，即通过名称而不是位置引用列。这允许您以任何列顺序提供要插入的数据，甚至只包含列的子集。例如：

CREATE TABLE year_info (year INTEGER, status VARCHAR);

INSERT INTO year_info BY NAME 
    SELECT 
        'The planet made it through' AS status,
        2024 AS year;

INSERT INTO year_info BY NAME 
    SELECT 
        2025 AS year;

FROM year_info;

预先存在的替代方法是提供一个额外的子句，指定要添加的列列表，其顺序与数据集相同。然而，这要求列的顺序和数量必须预先知道，而不是动态确定。在许多情况下，它还需要在两个位置指定列：INSERT 语句和生成数据的 SELECT 语句。忽视 “不要重复你自己” 的明智建议，在我的代码中导致了不少意想不到的后果……拥有一个单一的编辑位置，而不是必须保持同步，总是更好！

UNION ALL BY NAME 的灵感来源

其他系统和社区多年来一直致力于解决堆叠混乱数据的挑战。DuckDB 从它们那里汲取灵感，并将其改进带回 SQL！

最直接的灵感来自 Pandas 的 concat 函数。它于 2012 年 1 月添加，从一开始就支持添加新列。Pandas 被极其广泛地使用，是当今 Python 普及的重要贡献者。将此功能引入 SQL 可以将其影响范围从 Python 扩展到 DuckDB 支持的其他语言（Java、Node.js、Go、Rust 等）。数据库应该向数据帧学习！

PySpark 于 2018 年添加了函数 unionByName，并于 2021 年 3 月的 3.1 版本中添加了处理新列添加的能力。这是 Python 用户 的另一个选择，但它带来了对 Spark 集群及其开销的要求。

SQL 的 UNION 子句最晚从 1992 年 (!) 起就有了 CORRESPONDING 关键字，但关键在于它缺乏处理新列或缺失列的能力。因此，它在处理模式演变方面毫无用处。

我们希望我们能激励其他 SQL 引擎变得“更友好”，并允许这种灵活性！

DuckDB 1.1 中的性能改进

DuckDB 自 2022 年起就支持 UNION ALL BY NAME，但 1.1 版本 带来了显著的可扩展性和性能改进。此功能过去是一种“不得不”的方法，但现在可以更广泛地使用了！

第一个变化是 在使用 union_by_name 通过网络读取多个文件时减少了内存使用。这在从 S3 等云对象存储查询时提供了可扩展性优势，特别是当文件相对于可用内存较大时。

第二个变化是 在使用 union_by_name 时跨文件并行读取。这如预期般带来了显著的性能提升（在 PR 中的微基准测试中约为 6 倍）。

微基准测试

此微基准测试是 Daniel Beach (@DataEngDude) 在 这篇文章 中所做工作的重现。感谢 Daniel 允许我们在本文中重复使用他的基准测试！

该基准测试需要在具有 4 GB 内存的云实例上读取存储在 S3 上的 16 GB 具有变化模式的 CSV 文件。其目的是在小型商品硬件上处理大型数据集（这是我们希望看到 DuckDB 有所帮助的用例！）。原始文章使用 Linode，但本文我们选择了具有相同内存量的最相似的 AWS 实例（c5d.large）。

我们使用了来自 Backblaze 数据集 的两个季度的 CSV 文件（2023 年第二季度 和 2023 年第三季度），这些文件都放置在一个 S3 存储桶中。

我对 这里 的查询进行了非常细微的修改，以移除 ignore_errors = true 选项。基准测试继续使用 Python，但我在这里只展示 SQL 以获得更好的语法高亮。

CREATE OR REPLACE VIEW metrics AS 
    SELECT 
        date,
        sum(failure) AS failures
    FROM read_csv_auto('s3_path/*.csv', union_by_name = true)
    GROUP BY date;

COPY metrics TO 's3_path/results/results.csv';

当使用 4 GB 实例和旧版本 DuckDB (1.0.0) 时，我能够重现 Daniel 遇到的内存不足错误。如果我升级到 DuckDB 1.1.3，查询就能成功运行！然而，它们需要大约 5.8 分钟才能完成。

当我更深入地研究数据集时，我发现基准查询中选择的列存在于每个文件中。在早期版本的 DuckDB 中，即使不使用不一致或新列，只要文件具有不同的列集，就需要 union_by_name = True 标志。然而，在原始文章和 1.1.3 版本之间，DuckDB 添加了将投影下推到 CSV 文件的功能！这意味着实际上只从 CSV 读取查询中使用的列，而不是所有列。因此，我们可以为基准查询实际移除 union_by_name = true 并成功运行。这需要更少的开销（因为我们不需要花费时间检查所有模式是否匹配——我们可以依赖读取的第一个模式）。简化后的查询只需 4 分钟即可运行，但它未能充分展示我们讨论过的能力——处理模式演变！

为了演示 BY NAME 功能，我们在 SQL 查询中添加了一个仅存在于部分文件中的列。

CREATE OR REPLACE VIEW metrics AS 
    SELECT 
        date,
        count(DISTINCT datacenter) AS datacenters,
        sum(failure) AS failures
    FROM read_csv_auto('s3_path/*.csv', union_by_name = true)
    GROUP BY date;

COPY metrics TO 's3_path/results/results.csv';

这个查询的运行时间与原始查询大致相同（5.6 分钟），因此它是原始查询的一个很好的替代品，同时展示了 DuckDB 如何处理模式演变！

随后我进行了一些调整以提高性能。第一个改变是跳过视图的创建，并一次性完成所有操作。这样做能提高性能的原因是，DuckDB 在创建视图时会通过绑定来尝试确保视图定义正确。通常，这带来的开销可以忽略不计（视图是很棒的抽象！），然而当从云对象存储读取并使用 UNION ALL BY NAME 时，这会触发对每个文件模式的检查，这可能需要时间。在这种情况下，大约是 2 分钟！更新后的 SQL 语句如下：

COPY (
    SELECT 
        date, 
        count(DISTINCT datacenter) AS datacenters,
        sum(failure) AS failures
    FROM read_csv_auto('s3_path/*.csv', union_by_name = true)
    GROUP BY date
) TO 's3_path/results/results.csv';

经过此更改，性能提高到约 4.1 分钟，并且还将测试缩减为单个查询。

如果我们保留改进后的子查询语法，但再次移除 datacenter 列和 union_by_name 标志，我们就可以量化 UNION ALL BY NAME 提供的灵活性的开销。

COPY (
    SELECT 
        date, 
        sum(failure) AS failures
    FROM read_csv_auto('s3_path/*.csv')
    GROUP BY date
) TO 's3_path/results/results.csv';

这个查询在 3.7 分钟内运行完成，因此处理模式演变的开销只有大约 10%！为了灵活性和易用性，这是一个很小的代价。

然而，我们还可以进一步提高性能。下一个改变是增加 DuckDB 使用的线程数。默认情况下，DuckDB 每个核心使用一个线程。然而，这是一个 I/O 密集型查询（由于从 S3 读取然后写入的网络跳数），而不是 CPU 密集型查询。DuckDB 使用同步 I/O，因此在默认线程数下，如果一个线程正在进行 I/O，那么该 CPU 核心将处于空闲状态。因此，使用更多线程可能更有可能充分利用网络资源，而这正是本次测试中的瓶颈。在这里，我只是根据经验猜测这会有所帮助，但监控 CPU 利用率是更好的方法。

使用 4 个线程，而不是默认的 2 个，性能提升到 3 分钟！

增加更多线程并未进一步显著提高性能。额外的线程确实会占用更多内存，但随着 1.1 版本的改进，这不再是一个显著问题（我测试了多达 16 个线程，仅使用了 2.2 GB 内存）。

下表总结了在 c5d.large 实例（具有 2 个 vCPU 和 4 GB RAM）上获得的结果。我们报告了每个查询的总运行时间和最大内存使用量。

结束语

在堆叠数据时，DuckDB 将关系模型的精髓带回了 SQL！毕竟，堆叠数据不应该要求列顺序匹配……BY NAME 关键字可以简化常见操作，例如组合具有不同顺序或列集的关系、将查询结果插入表中，或查询具有不断变化的模式的数据湖。截至 DuckDB 1.1 版本，这现在是一种高性能且可扩展的方法！

祝您分析愉快！