DuckDB v1.1 发布, 带来多项重大更新

背景

原文: https://duckdb.org/2024/09/09/announcing-duckdb-110.html

1.1.0 中的新功能

变化实在太多，无法一一详细讨论，但我们想强调几个特别令人兴奋的功能！

下面是这些新功能的摘要和示例

• 重大 SQL 更改
• 社区插件扩展
• 灵活的SQL
• 反向解columns表达式(类似指针和从指针反向获取数据?)
• query和query_table功能
• 性能提升
• JOIN自动生成过滤器(filter)并下推
• 自动 CTE 物化
• 并行流式查询提速返回大量结果集场景
• 并行task调度, 提升read csv , read parquet接口读取大量小文件性能
• 嵌套 ART索引结构重设计（外键插入、删除、修改加速）
• 窗口函数改进
• 空间特征
• GeoParquet 格式
• R树
• 最后的想法

重大 SQL 更改

IEEE-754 : 除以零的语义。IEEE-754 浮点标准规定除以零返回inf
(无限大)。以前，DuckDB 在除以零时会返回NULL ，浮点除法也是如此。从此版本开始，DuckDB 将改为返回inf
。

SELECT 1 / 0 AS division_by_zero;  
  
┌──────────────────┐  
│ division_by_zero │  
│      double      │  
├──────────────────┤  
│              inf │  
└──────────────────┘  

ieee_floating_point_ops
可以设置为false
恢复此行为：

SET ieee_floating_point_ops = false;  
SELECT 1 / 0 AS division_by_zero;  
  
┌──────────────────┐  
│ division_by_zero │  
│      double      │  
├──────────────────┤  
│             NULL │  
└──────────────────┘  

标量子查询返回多个值时出错。标量子查询每个输入行只能返回一个值。以前，DuckDB 会匹配 SQLite 的行为，并在返回多个行时选择任意行返回。实际上，这种行为经常导致混淆。从此版本开始，将返回错误，与 Postgres 的行为一致。可以使用ARRAY包装子查询以将子查询的所有结果收集到列表中。

SELECT (SELECT unnest(range(10)));  

Invalid Input Error: More than one row returned by a subquery used as an expression - scalar subqueries can only return a single row.

SELECT ARRAY(SELECT unnest(range(10))) AS subquery_result;  
  
┌────────────────────────────────┐  
│        subquery_result         │  
│            int64[]             │  
├────────────────────────────────┤  
│ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] │  
└────────────────────────────────┘  

可以设定scalar_subquery_error_on_multiple_rows为false来恢复此行为。

SET scalar_subquery_error_on_multiple_rows = false;  
SELECT (SELECT unnest(range(10))) AS result;  
  
┌────────┐  
│ result │  
│ int64  │  
├────────┤  
│      0 │  
└────────┘  

社区插件扩展

https://community-extensions.duckdb.org/list_of_extensions.html

最近，我们推出了社区插件扩展。社区插件扩展允许任何人为 DuckDB 构建扩展插件，然后由我们构建和分发。从那时起，社区扩展列表一直在增长。

在此版本中，我们一直致力于使社区扩展更易于构建和生产。此版本包括一种使用 C API注册扩展的新方法，此外还有许多 C API 扩展，允许定义标量函数、聚合函数和自定义类型。这些更改将允许针对稳定的 API 构建扩展，这些扩展的大小更小，并且可以在不同版本的 DuckDB 中使用。此外，这些更改将允许将来使用其他编程语言构建扩展。

灵活的 SQL

1、直方图函数。此版本引入了histogram可用于计算数据集列直方图的函数。直方图函数适用于任何类型的列，并允许使用各种不同的分箱策略和自定义的分箱数量。

FROM histogram(  
    'https://blobs.duckdb.org/data/ontime.parquet',  
    UniqueCarrier,  
    bin_count := 5  
);  
  
┌────────────────┬────────┬──────────────────────────────────────────────────────────────────────────────────┐  
│      bin       │ count  │                                       bar                                        │  
│    varchar     │ uint64 │                                     varchar                                      │  
├────────────────┼────────┼──────────────────────────────────────────────────────────────────────────────────┤  
│ AA             │ 677215 │ ██████████████████████████████████████████████████████▏                          │  
│ DL             │ 696931 │ ███████████████████████████████████████████████████████▊                         │  
│ OO             │ 521956 │ █████████████████████████████████████████▊                                       │  
│ UA             │ 435757 │ ██████████████████████████████████▉                                              │  
│ WN             │ 999114 │ ████████████████████████████████████████████████████████████████████████████████ │  
│ (other values) │ 945484 │ ███████████████████████████████████████████████████████████████████████████▋     │  
└────────────────┴────────┴──────────────────────────────────────────────────────────────────────────────────┘  

2、SQL 变量。此版本引入了对可在 SQL 中定义的变量的支持。变量可以保存任何类型的单个值 - 包括列表或结构等嵌套类型。变量可以设置为文字，也可以从标量子查询中设置。

变量中存储的值可以使用getvariable读取。在query中使用时，getvariable在查询规划和优化期间被解析为 SQL文 进行替换。这允许在通常无法从表中读取值的地方使用变量，例如，在指定要读取哪些 CSV 文件时：

SET VARIABLE list_of_files = (SELECT LIST(file) FROM csv_files);  
SELECT * FROM read_csv(getvariable('list_of_files'), filename := true);  
  
┌───────┬───────────┐  
│   a   │ filename  │  
│ int64 │  varchar  │  
├───────┼───────────┤  
│    42 │ test.csv  │  
│    84 │ test2.csv │  
└───────┴───────────┘  

3、反向解columns表达式*COLUMNS(...)
 (类似指针 和 从指针反向获取数据?)

https://duckdb.org/docs/sql/expressions/star.html#columns-expression

该COLUMNS表达式允许用户针对一组列编写动态 SQL，而无需在 SQL文 中明确列出列。相反，可以通过正则表达式选择列，或使用lambda 函数计算列。

此版本扩展了此功能，允许将COLUMNS 表达式解包后作为函数的输入。当与嵌套函数（ 如struct_pack或list_value ）结合使用时，此功能特别有用。

CREATE TABLE many_measurements(  
    id INTEGER, m1 INTEGER, m2 INTEGER, m3 INTEGER  
);  
INSERT INTO many_measurements VALUES (1, 10, 100, 20);  
  
SELECT id, struct_pack(*COLUMNS('m\d')) AS measurements  
FROM many_measurements;  
  
┌───────┬────────────────────────────────────────────┐  
│  id   │                measurements                │  
│ int32 │ struct(m1 integer, m2 integer, m3 integer) │  
├───────┼────────────────────────────────────────────┤  
│     1 │ {'m1': 10, 'm2': 100, 'm3': 20}            │  
└───────┴────────────────────────────────────────────┘  

COLUMNS
behavior

select COALESCE(COLUMNS(*)) from (select NULL, 2, 3) t(a, b, c)  

When the COLUMNS
expression is expanded, the resulting query becomes:

select COALESCE(a) a, COALESCE(b) b, COALESCE(c) c from (select NULL, 2, 3) t(a, b, c)  

The result of this query is:

┌───────┬───────┬───────┐  
│   a   │   b   │   c   │  
│ int32 │ int32 │ int32 │  
├───────┼───────┼───────┤  
│       │     2 │     3 │  
└───────┴───────┴───────┘  

*COLUMNS
behavior

select COALESCE(*COLUMNS(*)) from (select NULL, 2, 3) t(a, b, c)  

When the *COLUMNS
expression is expanded, the resulting query becomes:

select COALESCE(a, b, c) from (select NULL, 2, 3) t(a, b, c)  

The result of this query is:

┌─────────────────────────┐  
│ COALESCE(t.a, t.b, t.c) │  
│          int32          │  
├─────────────────────────┤  
│                       2 │  
└─────────────────────────┘  

4、query和query_table功能

动态参数变量很常见, 那么 动态列名、动态表名 呢? 它来了.

query和query_table函数接受字符串文字，并将其转换为子查询或表引用。请注意，这些函数SELECT只能接受文字字符串。因此，它们不如通用的eval那么强大（或危险）。

这些函数在概念上很简单，但可以实现功能强大且更动态的 SQL。例如，它们允许将表名作为prepared statement参数传递：

CREATE TABLE my_table(i INT);  
INSERT INTO my_table VALUES (42);  
  
PREPARE select_from_table AS SELECT * FROM query_table($1);  
EXECUTE select_from_table('my_table');  
  
┌───────┐  
│   i   │  
│ int32 │  
├───────┤  
│    42 │  
└───────┘  

与COLUMNS表达式结合使用时，我们可以编写非常通用的 SQL 专用宏。例如，下面是一个自定义版本，用于SUMMARIZE计算表中每一列的min和max：

CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLE  
SELECT  
    unnest([*COLUMNS('alias_.*')]) AS column_name,  
    unnest([*COLUMNS('min_.*')]) AS min_value,  
    unnest([*COLUMNS('max_.*')]) AS max_value  
FROM (  
    SELECT  
        any_value(alias(COLUMNS(*))) AS "alias_\0",  
        min(COLUMNS(*))::VARCHAR AS "min_\0",  
        max(COLUMNS(*))::VARCHAR AS "max_\0"  
    FROM query_table(table_name::VARCHAR)  
);  
  
SELECT *  
FROM my_summarize('https://blobs.duckdb.org/data/ontime.parquet')  
LIMIT 3;  
  
┌─────────────┬───────────┬───────────┐  
│ column_name │ min_value │ max_value │  
│   varchar   │  varchar  │  varchar  │  
├─────────────┼───────────┼───────────┤  
│ year        │ 2017      │ 2017      │  
│ quarter     │ 1         │ 3         │  
│ month       │ 1         │ 9         │  
└─────────────┴───────────┴───────────┘  

性能提升

1、JOIN自动生成过滤器(filter)并下推

此版本为JOIN添加了非常酷的优化：DuckDB 现在会在执行过程中自动为JOIN中较大的表创建过滤器。假设我们正在JOIN两个表A和B。A有 100 行，B有一百万行。我们正在使用i键进行连接。如果有任何i过滤，DuckDB 会将该filter推送到扫描中，从而大大降低完成查询的成本。但我们现在正在过滤来自A的另一列，即j：

CREATE TABLE A AS SELECT range i, range j FROM range(100);  
CREATE TABLE B AS SELECT a.range i FROM range(100) a, range(10_000) b;  
SELECT count(*) FROM A JOIN B USING (i) WHERE j > 90;   

DuckDB 将通过在较小的表 A 上构建哈希表来执行此JOIN，然后使用 B 的内容探测该哈希表。DuckDB 现在将在 A 上构建哈希表期间观察 i 的值。然后，它将创建 i 的这些值的最小-最大范围filter，然后自动将该filter应用于 B 中的 i 值！这样，我们在查看哈希表之前就从大表中删除了（在本例中）90% 的数据。在此示例中，这导致查询性能提高了大约 10 倍。在 EXPLAIN ANALYZE的输出中也可以观察到优化。

2、自动 CTE 物化

通用表表达式 (CTE) 是一种将复杂查询分解为可管理部分且无需无休止地嵌套子查询的便捷方法。以下是 CTE 的一个小示例：

WITH my_cte AS (SELECT range AS i FROM range(10))  
SELECT i FROM my_cte WHERE i > 5;  

有时，同一个 CTE 会在同一个查询中被多次引用。以前，CTE 会在它出现的任何位置被“复制”。这会产生潜在的性能问题：如果计算 CTE 的计算成本很高，那么最好将其结果缓存（“具体化/物化”），而不是在同一查询的不同位置多次计算结果。这可以大大降低其计算成本。

此版本添加了一项功能，即 DuckDB使用启发式方法自动决定是否应实现 CTE 结果。目前的启发式方法是，如果 CTE 执行聚合并且被查询多次，则应物化它。我们计划在未来扩展该启发式方法。

3、并行流式查询提速返回大量结果集场景

DuckDB 有两种不同的获取query result的方法：物化和流式。物化一次性获取结果中存在的所有数据并返回。流式则允许以增量步骤迭代数据。处理大型结果集时，流式至关重要，因为它们不需要将整个结果集放入内存中。然而，在以前的版本中，最后的流式阶段仅限于单个线程。

并行性对于在现代硬件上获得良好的查询性能至关重要，此版本增加了并行流式获取查询结果的支持。系统将使用所有可用线程来填充有限大小（几兆字节）的查询结果缓冲区。当结果缓冲区被完全取出时，线程将重新启动并开始再次填充缓冲区。缓冲区的大小可以通过参数streaming_buffer_size配置。

下面是一个小的基准测试，用ontime.parquet说明使用 Python 流式结果接口可以获得的性能优势：

import duckdb  
duckdb.sql("SELECT * FROM 'ontime.parquet' WHERE flightnum = 6805;").fetchone()  

4、并行task调度, 提升read csv , read parquet接口读取大量小文件性能

In this PR we use the TaskExecutor to provide a parallel implementation of the union_by_name file scanning. Since we perform auto-detection on all files, this is trivial to parallelize, and can provide substantial speedups when running read_csv or read_parquet with union_by_name enabled over many small files. The union_by_name is also a good showcase for how easy the parallelism is to add using the TaskExecutor, e.g.:

TaskExecutor executor(context);  
// schedule tasks for all files  
for (auto &file : files) {  
 auto task = make_uniq<UnionByReaderTask>(....);  
 executor.ScheduleTask(std::move(task));  
}  
// complete all tasks  
executor.WorkOnTasks();  

Below are some timings of reading 1000 small CSV files. Source:

CREATE TABLE t1 AS select i,i,i,i,i,i,i,i,i,i from range(0,2048) tbl(i);  
COPY t1 TO 'small_csv.csv' (FORMAT CSV, HEADER 0);  
SELECT * from read_csv(repeat(['small_csv.csv'], 1000),delim= ',',  header = 0, union_by_name=true);  

5、嵌套 ART索引结构重设计（外键插入、删除、修改加速）

https://github.com/duckdb/duckdb/pull/13373

我们大大提高了外键的索引插入和删除性能。我们现在实际上为每个键创建另一个索引条目，该索引条目本身就是另一个“递归”索引树。这样，我们可以在索引条目内实现良好的插入和删除性能。请考虑以下示例，其中 a 有 100 行，b 有 100 万行，b reference a：

CREATE TABLE a (i INTEGER, PRIMARY KEY (i));  
CREATE TABLE b (i INTEGER, FOREIGN KEY (i) REFERENCES a(i));  
  
INSERT INTO a FROM range(100);  
INSERT INTO b SELECT a.range FROM range(100) a, range(10_000) b;  

这种变化的性能结果是巨大的，在之前的版本中，这在 MacBook 上需要大约 10 秒才能完成。由于采用了新的索引结构，现在只需 0.2 秒，速度提高了约 50 倍！

6、窗口函数改进

窗口函数在 DuckDB 中得到广泛使用，这就是我们不断提高在大型数据集上执行窗口函数的性能的原因。最近PG 18也有一个分组特别多的情况下的性能改进 《PostgreSQL 18 preview - 窗口查询性能提升40%(100万个窗口时)》

DISTINCT和窗口函数修饰符FILTER现在可以在流模式下执行。流模式意味着运算符的输入数据不需要在运算符执行之前完全收集和缓冲。对于大型中间结果，这可能会对性能产生非常大的影响。例如，以下查询现在将使用流窗口运算符：

SELECT  
    sum(DISTINCT i)  
        FILTER (i % 3 = 0)  
        OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  
FROM range(10) tbl(i);  

我们还实现了lead窗口函数的流模式。

现在，我们可以通过按同一列分区的窗口函数对列推送filter。例如，考虑以下场景：

CREATE TABLE tbl2 AS SELECT range i FROM range(10);  
SELECT i  
FROM (SELECT i, SUM(i) OVER (PARTITION BY i) FROM tbl)    
WHERE i > 5;  -- 会推送到上面的tbl中     

以前，无法将i上的filter推入tbl的扫描中。但我们现在认识到，将此过滤器“推过”窗口是安全的，优化器也会这样做。这可以通过以下方法验证EXPLAIN：

┌─────────────────────────────┐  
│┌───────────────────────────┐│  
││       Physical Plan       ││  
│└───────────────────────────┘│  
└─────────────────────────────┘  
              …  
┌─────────────┴─────────────┐  
│           WINDOW          │  
│    ────────────────────   │  
│        Projections:       │  
│ sum(i) OVER (PARTITION BY │  
│             i)            │  
└─────────────┬─────────────┘  
┌─────────────┴─────────────┐  
│         SEQ_SCAN          │  
│    ────────────────────   │  
│            tbl            │  
│                           │  
│       Projections: i      │  
│                           │  
│          Filters:         │  
│   i>5 AND i IS NOT NULL   │  
│                           │  
│          ~2 Rows          │  
└───────────────────────────┘  

窗口运算符的阻塞（非流式）版本现在可以并行处理输入数据。这大大减少了窗口运算符的占用空间。 https://github.com/duckdb/duckdb/pull/12907

另请参阅理查德几周前在西雅图DuckCon#5
上关于该主题的演讲。 https://duckdb.org/2024/08/15/duckcon5.html

空间特征

1、GeoParquet 格式

GeoParquet 是普遍存在的 Parquet 格式的扩展格式，它标准化了如何在 Parquet 文件中对矢量几何图形及其元数据进行编码。这可用于高效地将地理数据集存储在 Parquet 文件中。安装并加载spatial扩展后，通过 DuckDB 的常规 Parquet 读取器从 GeoParquet 文件读取数据时会自动将几何列转换为GEOMETRY类型，例如：

INSTALL spatial;  
LOAD spatial;  
  
FROM 'https://blobs.duckdb.org/data/geoparquet-example.parquet'  
SELECT GEOMETRY g  
LIMIT 10;  
  
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐  
│                                                       g                                                        │  
│                                                    geometry                                                    │  
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤  
│ MULTIPOLYGON (((180 -16.067132663642447, 180 -16.555216566639196, 179.36414266196414 -16.801354076946883, 17…  │  
│ POLYGON ((33.90371119710453 -0.95, 34.07261999999997 -1.059819999999945, 37.69868999999994 -3.09698999999994…  │  
│ POLYGON ((-8.665589565454809 27.656425889592356, -8.665124477564191 27.589479071558227, -8.684399786809053 2…  │  
│ MULTIPOLYGON (((-122.84000000000003 49.000000000000114, -122.97421000000001 49.00253777777778, -124.91024 49…  │  
│ MULTIPOLYGON (((-122.84000000000003 49.000000000000114, -120 49.000000000000114, -117.03121 49, -116.04818 4…  │  
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘  

2、R树

此版本附带的空间扩展还实现了对创建“R-Tree”空间索引的初始支持。R-Tree 索引将列中每个几何图形的近似边界框存储在分层树状数据结构中，其中每个“node”都包含一个覆盖其所有子node的边界框。这使得检查哪些几何图形与特定感兴趣区域相交变得非常快，因为您可以通过递归向下移动树来快速修剪大量候选对象。

对空间索引的支持一直是空间扩展路线图上长期要求的功能，现在我们有了它，大量新的用例和进一步开发的方向正在开放。但是，截至目前，它们仅用于加速简单查询，这些查询使用应用于索引几何列和常量几何的一组硬编码空间谓词函数之一从表中进行选择，并使用filter。当您有一个非常大的几何表并且需要反复查询时，但当您只对几何相交或适合某个区域的行感兴趣时，您不想执行全表扫描这使得 R-Tree 索引非常有用。下面是一个我们可以看到运算符被RTREE_INDEX_SCAN使用的示例：

INSTALL spatial;  
LOAD spatial;  
  
-- Create a table with 10_000_000 random points  
CREATE TABLE t1 AS SELECT point::GEOMETRY AS geom  
FROM st_generatepoints(  
        {min_x: 0, min_y: 0, max_x: 10_000, max_y: 10_000}::BOX_2D,  
        10_000_000,  
        1337  
    );  
  
-- Create an index on the table  
CREATE INDEX my_idx ON t1 USING RTREE (geom);  
  
-- Perform a query with a "spatial predicate" on the indexed geometry  
-- column. Note how the second argument in this case,  
-- the ST_MakeEnvelope call is a "constant"  
SELECT count(*)  
FROM t1  
WHERE ST_Within(geom, ST_MakeEnvelope(450, 450, 650, 650));  

3986  

R-Tree 索引与 DuckDB 的内置 ART 索引具有大部分相同的功能集。它们被shared buffer管理、可以持久化存储、支持从磁盘延迟加载，并且支持基表的插入、更新和删除。但它们不能用于强制实施约束。

最后的想法

以上只是几个亮点，但此版本中还有更多功能和改进。完整的发布说明可在 GitHub 上找到。https://github.com/duckdb/duckdb/releases/tag/v1.1.0

用好周边工具, 数据库管理水平战胜90%老司机

1、管控软件

云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.

• https://github.com/apecloud/kubeblocks

乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

通过信创并且开源的数据库:

PolarDB for PostgreSQL

• https://github.com/ApsaraDB/PolarDB-for-PostgreSQL