DuckDB数据分析实战:读写Excel文件

在企业数据分析场景中，Excel 几乎无处不在。为此，DuckDB 提供了专门的插件，让数据分析师和开发人员可以像查询数据库表一样直接查询 Excel 文件，同时支持将分析结果直接导出为 Excel 文件。

安装插件

DuckDB 官方提供的 Excel 插件（excel）在第一次使用时可以自动安装并加载，也可以手动执行以下命令：

INSTALL excel;LOAD excel;

然后我们准备一个 Excel 示例文件（fruits.xlsx），内容如下：

excel 插件支持 .xlsx 格式文件，但不支持 .xls 格式文件。

读取Excel

DuckDB 可以将 Excel 文件当作一个数据源进行查询，例如：

SELECT *FROM 'fruits.xlsx';┌─────────┬────────┬────────┐│  name   │ price  │  num   ││ varchar │ double │ double │├─────────┼────────┼────────┤│ 苹果    │    9.9 │   20.0 ││ 西瓜    │    2.5 │   15.0 ││ 梨      │    6.6 │    6.0 │└─────────┴────────┴────────┘

DuckDB 能够基于文件后缀调用相应的处理函数，对于 XLSX 文件实际调用的是 read_xlsx 函数。因此，以上查询也可以写成下面的语句：

SELECT *FROM read_xlsx('fruits.xlsx', header = true);

其中，header = true 表示读取的内容中包含标题（第一行数据）。

read_xlsx 函数的完整参数如下：

参数	类型	默认值	描述
`header`	`BOOLEAN`	自动推断	是否将第一行当作标题行。
`sheet`	`VARCHAR`	自动推断	要读取的 sheet 名称。默认读取第一个 sheet。
`all_varchar`	`BOOLEAN`	`false`	是否将所有单元格当作 VARCHAR 类型读取。
`ignore_errors`	`BOOLEAN`	`false`	是否忽略错误并且将无法进行类型转换的数据使用 NULL 替代。
`range`	`VARCHAR`	自动推断	读取的单元格区域。例如，`A1:B2`表示读取从 A1 到 B2 之间的单元格。如果没有指定该参数，从第一行连续非空单元格开始向下查找，直到遇到第一个空行；列范围由该数据区域决定。
`stop_at_empty`	`BOOLEAN`	自动推断	遇到空行时是否停止读取。如果指定了`range`，该参数默认为`false`，否则默认为`true`。
`empty_as_varchar`	`BOOLEAN`	`false`	自动推断类型时，是否将空单元格当作`VARCHAR`而不是`DOUBLE`。

例如，以下查询只读取了 fruits.xlsx 文件中的第 2 行数据：

SELECT *FROM read_xlsx('fruits.xlsx', range='A2:C2');┌─────────┬────────┬────────┐│    A    │   B    │   C    ││ varchar │ double │ double │├─────────┼────────┼────────┤│ 苹果    │    9.9 │   20.0 │└─────────┴────────┴────────┘

DuckDB 自动推断出数据中没有标题行，因此生成了默认的标题。

另外，导入 Excel 文件时，DuckDB 会基于单元格内容以及/或者格式自动推断列的类型。主要规则包括：

• 包含日期格式的数据推断为 DATE；
• 包含时间格式的数据推断为 TIME；
• 包含时间戳格式的数据推断为 TIMESTAMP；
• 包含文本 TRUE/FALSE的数据推断为 BOOLEAN；
• 空单元格默认推断为 DOUBLE，除非使用 empty_as_varchar 参数进行指定；
• 数值列推断为 DOUBLE；
• 文本列推断为 VARCHAR。

导入数据

如果需要在 DuckDB 中存储数据，可以将读取的 Excel 内容写入表中。

第一种方法是使用 CREATE TABLE ... AS 语句创建一个新表，例如：

CREATE TABLE fruits ASSELECT *FROM read_xlsx('fruits.xlsx');SELECT *FROM fruits;┌─────────┬────────┬────────┐│  name   │ price  │  num   ││ varchar │ double │ double │├─────────┼────────┼────────┤│ 苹果    │    9.9 │   20.0 ││ 西瓜    │    2.5 │   15.0 ││ 梨      │    6.6 │    6.0 │└─────────┴────────┴────────┘

第二种方法是使用 INSERT INTO 语句将数据导入已有的表，例如：

CREATE TABLE fruits2(name VARCHAR, price VARCHAR, cnt DOUBLE);INSERT INTO fruits2SELECT *FROM read_xlsx('fruits.xlsx');SELECT * FROM fruits2;┌─────────┬─────────┬────────┐│  name   │  price  │  cnt   ││ varchar │ varchar │ double │├─────────┼─────────┼────────┤│ 苹果    │ 9.9     │   20.0 ││ 西瓜    │ 2.5     │   15.0 ││ 梨      │ 6.6     │    6.0 │└─────────┴─────────┴────────┘

需要注意，fruits2 的字段类型和 Excel 文件不完全相同，此时 DuckDB 会按照目标表字段类型进行转换。

还有一种导入 Excel 文件的方法是使用 COPY 语句，例如：

COPY fruits2 FROM 'fruits.xlsx' (FORMAT xlsx, HEADER);

导出Excel

COPY 语句支持将数据表或者查询结果导出为 Excel 文件（.xlsx），例如：

COPY employee TO 'employee.xlsx' (FORMAT xlsx, HEADER true);

以上示例将 employee 中的数据写入名为 employee.xlsx 的文件，写入的第一行为标题。

COPY 导出数据时，还可以指定生成的工作表（sheet）名称。例如：

COPY (SELECT * FROM employee WHERE dept_id=5)TO 'employee.xlsx' (FORMAT xlsx, HEADER true, SHEET 'sales');

由于 Excel 底层只支持数字和字符串两种类型，DuckDB 导出到 Excel 文件时会自动执行类型转换：

• 数字类型转换为 DOUBLE；
• 时间类型（TIMESTAMP、DATE、TIME）转化为 Excel 序列号数字，并且赋予对应的显示格式；
• TIMESTAMP_TZ 以及 TIME_TZ 转换为 UTC 时间并且丢失时区信息；
• BOOLEAN 类型转化为 1 或者 0，并且设置显示为 TRUE 或者 FALSE 的特殊格式；
• 其他类型转化为文本格式。

最后，我们看一个结合 Excel 导入与导出的示例。假设每个月的销售数据分别存储在一个 Excel 文件中，名称为 sales_202601.xlsx、sales_202602.xlsx、sales_202603.xlsx 等。

以下语句可以完成联合读取多个文件、执行数据分析、写入结果文件的多个操作：

COPY ( -- 导出结果文件    WITH sales AS ( -- 联合读取文件        SELECT * FROM 'sales_202601.xlsx'        UNION ALL        SELECT * FROM 'sales_202602.xlsx'        UNION ALL        SELECT * FROM 'sales_202603.xlsx'    )    SELECT region, SUM(amount) -- 执行数据分析    FROM sales    GROUP BY region)TO 'region_sales.xlsx' (FORMAT xlsx, HEADER true);

其中，WITH 相当于定义了一个临时表 sales，包含了多个月份的销售数据；然后基于 sales 进行数据分析；COPY 语句用于导出结果。