DuckDB 技巧 – 第 1 部分

作者: Gabor Szarnyas

原文：https://duckdb.org/2024/08/19/duckdb-tricks-part-1.html

我们通过一个简单的数据集展示在使用 DuckDB 时非常实用的一些小技巧

本文，我们将分享了五个在交互式使用 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” 语句^[1]。在这里，我们结合了 VALUES
 语句^[2] 和 FROM
 优先语法^[3]，这使得 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
^[4] 或 format
^[5] 函数，例如：

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  

这个命令将返回表的 SQL 定义，例如：

CREATE TABLE example(s VARCHAR, x DOUBLE);  

修改表名后（例如，将 example
 改为 tbl
），我们就可以创建一个具有相同结构的新表。

打乱数据顺序

有时候，我们需要随机打乱数据的顺序。要实现非确定性的打乱，可以使用 random()
 函数^[6] 按随机值排序：

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

如果需要确定性打乱数据顺序，可以使用 rowid
 伪列^[7] 的哈希值。需要注意的是，该列仅在物理表中可用，因此首先我们需要加载 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 加载器会自动从一个简短的类型列表^[8]中检测数据类型，包括 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 中，可以通过 COPY
 命令方便地更新 CSV 文件。我们可以从一个新的查询结果导出到同一个 CSV 文件，实现原地更新。例如，假设我们希望将 s
 列写回原文件，忽略掉 x
 列：

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

通过 WITH (HEADER TRUE)
 保持 CSV 文件的列名一致，执行上述命令后，CSV 文件将被更新为：

s  
foo  
bar  
qux  

这是 COPY
 命令的另一种常见用途，尤其适用于无需复杂数据操作的场景。

通过上述几个 DuckDB 操作，你可以更高效地处理数据，并根据自己的需求调整表的结构和内容。]()

引用链接

[1]
 “友好 SQL” 语句: https://duckdb.org/docs/sql/dialect/friendly_sql.html
[2]
 VALUES
 语句: https://duckdb.org/docs/sql/query_syntax/values.md
[3]
 FROM
 优先语法: https://duckdb.org/docs/sql/query_syntax/from.md#from-first-syntax
[4]
 printf
: https://duckdb.org/docs/sql/functions/char.md#printf-syntax
[5]
 format
: https://duckdb.org/docs/sql/functions/char.md#fmt-syntax
[6]
 random()
 函数: https://duckdb.org/docs/sql/functions/numeric.md#random
[7]
 rowid
 伪列: https://duckdb.org/docs/sql/statements/select.md#row-ids
[8]
 简短的类型列表: https://duckdb.org/docs/data/csv/auto_detection.md#type-detection