DuckDB 技巧 – 第 1 部分

Gabor Szarnyas

2024-08-19 · 5 分钟

在这篇博客文章中，我们将介绍五种简单但我们认为在交互式使用场景中特别有用的 DuckDB 操作。这些操作总结在下表中

创建示例数据集

我们首先创建一个将在本博客文章其余部分中使用的数据集。为此，我们定义一个表，用一些数据填充它，然后将其导出到 CSV 文件。

CREATE TABLE example (s STRING, x DOUBLE);
INSERT INTO example VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4);
COPY example TO 'example.csv';

等等，这太冗长了！DuckDB 的语法有多种 SQL 简写形式，包括“友好 SQL”子句。在这里，我们将VALUES子句与FROM优先语法结合起来，这使得SELECT子句变为可选。通过这些，我们可以将数据创建脚本压缩到原始大小的约 60%。新的写法省略了模式定义，并使用单个命令创建 CSV

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

无论我们运行哪个脚本，生成的 CSV 文件都将如下所示

s,x
foo,1.1111111111111112
bar,7.142857142857143
qux,2.25

让我们继续查看代码片段及其解释。

美化打印浮点数

当将浮点数打印到输出时，其小数部分可能难以阅读和比较。例如，以下查询返回介于 1 到 8 之间的三个数字，但由于其小数部分，它们的打印宽度差异很大。

SELECT x
FROM 'example.csv';

┌────────────────────┐
│         x          │
│       double       │
├────────────────────┤
│ 1.1111111111111112 │
│  7.142857142857143 │
│               2.25 │
└────────────────────┘

通过将列转换为小数点后具有固定位数​​的DECIMAL类型，我们可以如下美化打印它

SELECT x::DECIMAL(15, 3) AS x
FROM 'example.csv';

┌───────────────┐
│       x       │
│ decimal(15,3) │
├───────────────┤
│         1.111 │
│         7.143 │
│         2.250 │
└───────────────┘

一个典型的替代解决方案是使用printf或format函数，例如

SELECT printf('%.3f', x)
FROM 'example.csv';

然而，这些方法要求我们指定一个容易忘记的格式化字符串。更糟糕的是，上述语句返回的是字符串值，这使得后续操作（例如排序）更加困难。因此，除非需要保留浮点数的完整精度，否则对于大多数用例来说，转换为DECIMAL值应该是首选解决方案。

复制表的模式

要在不复制数据的情况下从表中复制模式，我们可以使用LIMIT 0。

CREATE TABLE example AS
    FROM 'example.csv';
CREATE TABLE tbl AS
    FROM example
    LIMIT 0;

这将创建一个与源表具有相同模式的空表

DESCRIBE tbl;

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ s           │ VARCHAR     │ YES     │         │         │         │
│ x           │ DOUBLE      │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

或者，在 CLI 客户端中，我们可以运行.schema点命令

.schema

这将返回表的模式。

CREATE TABLE example (s VARCHAR, x DOUBLE);

编辑表名（例如，将example改为tbl）后，此查询可用于创建具有相同模式的新表。

打乱数据

有时，我们需要通过打乱数据来引入一些无序性。要非确定性地打乱数据，我们只需通过random()函数对随机值进行排序即可

FROM 'example.csv' ORDER BY random();

确定性地打乱数据则稍微复杂一些。为此，我们可以根据哈希值，对rowid伪列进行排序。请注意，此列仅在物理表中可用，因此我们必须首先将 CSV 加载到表中，然后按如下方式执行打乱操作

CREATE OR REPLACE TABLE example AS FROM 'example.csv';
FROM example ORDER BY hash(rowid + 42);

这种打乱操作的结果是确定性的——如果重复运行脚本，它将始终返回下表

┌─────────┬────────────────────┐
│    s    │         x          │
│ varchar │       double       │
├─────────┼────────────────────┤
│ bar     │  7.142857142857143 │
│ qux     │               2.25 │
│ foo     │ 1.1111111111111112 │
└─────────┴────────────────────┘

请注意，+ 42仅用于将第一行从其位置移开——因为hash(0)返回0，这是最小可能的值，将其用于排序会将第一行留在原位。

在 CSV 加载器中指定类型

DuckDB 的 CSV 加载器会自动从一个短列表中检测类型，包括BOOLEAN、BIGINT、DOUBLE、TIME、DATE、TIMESTAMP和VARCHAR。在某些情况下，需要用此列表之外的类型覆盖给定列的检测类型。例如，我们可能希望从一开始就将列x视为DECIMAL值。我们可以通过read_csv函数的types参数，按列进行此操作

CREATE OR REPLACE TABLE example AS
    FROM read_csv('example.csv', types = {'x': 'DECIMAL(15, 3)'});

然后，我们只需查询该表即可查看结果

FROM example;

┌─────────┬───────────────┐
│    s    │       x       │
│ varchar │ decimal(15,3) │
├─────────┼───────────────┤
│ foo     │         1.111 │
│ bar     │         7.143 │
│ qux     │         2.250 │
└─────────┴───────────────┘

原地更新 CSV 文件

在 DuckDB 中，可以原地读取、处理和写入 CSV 文件。例如，要将列s投影到同一个文件中，我们只需运行

COPY (SELECT s FROM 'example.csv') TO 'example.csv';

生成的example.csv文件将包含以下内容

s
foo
bar
qux

请注意，在 Unix shell 中，如果没有变通方法，此技巧是不可行的。人们可能会尝试在example.csv文件上运行以下命令，并期望得到相同的结果

cut -d, -f1 example.csv > example.csv

然而，由于 Unix 管道的复杂性，执行此命令将导致example.csv文件为空。解决方案是使用不同的文件名，然后执行重命名操作

cut -d, -f1 example.csv > tmp.csv && mv tmp.csv example.csv

结束语

今天的内容就到这里。本文中展示的技巧可在duckdbsnippets.com上找到。如果您有想分享的技巧，请在那里提交，或通过社交媒体或Discord发送给我们。祝您编码愉快！