duckdb-transformation

DuckDB 转换知识

提供有关在 Keboola Connection 中编写、配置和优化 DuckDB 转换的专业知识。

概述

DuckDB（keboola.duckdb-transformation）是一款进程内分析数据库（OLAP），采用列式存储，专为快速 SQL 分析而设计。对于中小型数据集，它是 Snowflake 的高性价比替代方案。

状态： BETA —— 可能会发生破坏性变更。

主要优势：

• 进程内执行（无需外部数据库服务）
• 列式存储，针对分析型查询进行了优化
• 基于区块的编排，并自动分析依赖关系
• 并行执行互不依赖的脚本
• 丰富的 SQL 方言，包含现代易用性扩展

配置

组件 ID

keboola.duckdb-transformation

配置结构

{
  "parameters": {
    "blocks": [
      {
        "name": "Block Name",
        "codes": [
          {
            "name": "Script Name",
            "script": [
              "CREATE TABLE \"output\" AS",
              "SELECT * FROM \"input_table\";"
            ]
          }
        ]
      }
    ],
    "threads": 4,
    "max_memory_mb": 2048,
    "image_tag": "parquet",
    "dtypes_infer": false
  },
  "storage": {
    "input": {
      "tables": [
        {
          "source": "in.c-bucket.table",
          "destination": "input_table"
        }
      ]
    },
    "output": {
      "tables": [
        {
          "source": "output",
          "destination": "out.c-bucket.result"
        }
      ]
    }
  }
}

设置

• 超时 —— 最长执行时间（默认：1 小时）
• 后端大小 —— 内存分配（参见动态后端）
• DuckDB 版本 —— latest（默认）或固定版本（例如 1.5.2、1.4.4）
• 自动数据类型 —— 自动为输出表分配类型
• 输入表使用 parquet —— 使用 Parquet 而非 CSV（建议用于大于 1 GB 的数据集）
• 推断输入表数据类型 —— 推断输入表的数据类型（适用于非类型化 Storage）
• 调试模式 —— 启用调试日志

基于区块的编排

• 区块按顺序执行（依次执行）
• 区块内的脚本会并行执行（无依赖关系时）
• 使用 SQLGlot 自动分析 SQL
• 构建依赖关系的DAG（有向无环图）
• 自动优化执行顺序

动态后端

后端大小	内存	建议用途
XSmall	8 GB	小型数据集、测试
Small （默认）	16 GB	大多数使用场景
Medium	32 GB	大型数据集（5 GB 以上）
Large	113.6 GB	超大型数据集（10 GB 以上）

从 Small 开始，根据需要扩容。Free/Pay As You Go 套餐不提供动态后端。

DuckDB 会自动检测可用的 CPU 和内存。可通过 threads 和 max_memory_mb 参数手动覆盖。

Parquet 格式

启用 Parquet 可显著提升大型数据集的处理性能：

• 比 CSV 处理速度更快
• 内存占用更低
• 列式存储，针对分析进行了优化

建议： 对于大于 1 GB 的数据集，始终使用 Parquet。

配置方法：设置 "image_tag": "parquet"，或在 UI 中启用 输入表使用 parquet。

推断输入表数据类型

Keboola Storage 表可以是非类型化的（所有列均为 VARCHAR）。启用此选项后，DuckDB 会推断实际类型（INTEGER、FLOAT、DATE 等），因此聚合函数和特定类型的操作无需手动转换类型即可正常工作。

不进行推断时：SUM("amount") 会失败，因为 amount 是 VARCHAR。 进行推断后：SUM("amount") 可以正常运行，因为 DuckDB 会将其识别为数值类型。

同步操作

无需运行完整转换即可使用四种调试操作：

1. syntax_check —— 验证 SQL 语法，不执行 SQL
2. lineage_visualization —— 以 Markdown 图表展示数据依赖关系
3. execution_plan_visualization —— 展示计划的执行顺序，包括区块和批次
4. expected_input_tables —— 根据 SQL 分析列出预期的输入表

SQL 编写规则

语句之间的分号

每条 SQL 语句都必须以分号（;）结束。单个脚本中有多条语句时，必须使用分号正确分隔：

-- Correct: each statement ends with a semicolon
CREATE TABLE "output_a" AS SELECT * FROM "input_a";

CREATE TABLE "output_b" AS SELECT * FROM "input_b";

缺少分号会导致语法错误。

大小写敏感性（重要）

表名：

• 不加引号 → 小写（MyTable → mytable）
• 加引号 → 区分大小写（"MyTable" 保持为 MyTable）

列名：

• 始终区分大小写，无论是否加引号
• SELECT columnName 和 SELECT ColumnName 指的是不同的列

这与 Snowflake 不同（不加引号 → 大写，加引号的列名不区分大小写）。

最佳实践： 使用统一的小写命名，或始终为标识符加引号。

类型转换模式

对于非类型化的 Storage 表，请使用安全的类型转换模式：

-- Safe: handles empty strings without errors
TRY_CAST(NULLIF("column", '') AS DATE)
TRY_CAST(NULLIF("column", '') AS DOUBLE)
TRY_CAST(NULLIF("column", '') AS INT)

-- Shorthand type cast
"column"::BOOLEAN
"column"::INTEGER

-- Regular cast
CAST("column" AS DECIMAL)

模式： TRY_CAST(NULLIF("col", '') AS TYPE) --- 在类型转换前将空字符串转换为 NULL，以避免空值导致错误。

DuckDB SQL 扩展

并非所有 SQL 方言都提供的实用扩展：

-- GROUP BY ALL: auto-groups by all non-aggregated columns
SELECT product, category, SUM(sales) FROM orders GROUP BY ALL;

-- EXCLUDE: select all columns except specific ones
SELECT * EXCLUDE (password, ssn) FROM users;

-- ASOF JOIN: time-series data with non-matching timestamps
SELECT s.player_id, s.score, w.temperature
FROM scores s ASOF JOIN weather w ON s.score_time >= w.timestamp;

-- SUMMARIZE: quick data profiling
SUMMARIZE SELECT * FROM my_table;

大型数据集的内存管理

对于超过 10 GB 的数据集，请配置磁盘处理：

PRAGMA memory_limit='8GB';
PRAGMA temp_directory='/tmp/duckdb_temp';
PRAGMA threads=4;
PRAGMA enable_object_cache;

从 Snowflake 迁移到 DuckDB

数据类型映射

Snowflake	DuckDB	说明
VARIANT	JSON 或 VARCHAR	半结构化数据
ARRAY	LIST	原生数组
OBJECT	STRUCT 或 JSON	嵌套对象
INTEGER	INTEGER	相同
VARCHAR	VARCHAR	相同
TIMESTAMP	TIMESTAMP	相同

SQL 函数差异

类别	Snowflake	DuckDB
窗口函数	QUALIFY ROW_NUMBER() OVER (...) = 1	使用带 WHERE rn = 1 的子查询
空值处理	NVL(a, b)	COALESCE(a, b)
条件表达式	IFF(cond, a, b)	CASE WHEN cond THEN a ELSE b END
日期加法	DATEADD(day, 7, date)	date + INTERVAL '7 days'
日期差值	DATEDIFF(day, d1, d2)	date_diff('day', d1, d2)
字符串聚合	LISTAGG(...) WITHIN GROUP (ORDER BY ...)	STRING_AGG(...)
临时表	CREATE TRANSIENT TABLE	不支持（请使用普通表或 TEMP）

大小写敏感性迁移

Snowflake：

• 不加引号 → 大写（MyTable → MYTABLE）
• 加引号 → 区分大小写（"MyTable" ≠ "MYTABLE"）

DuckDB：

• 不加引号 → 小写（MyTable → mytable）
• 加引号的表名 → 区分大小写
• 列名 → 始终区分大小写，无论是否加引号

迁移提示： 使用统一的小写命名：CREATE TABLE mytable ...

窗口函数迁移

-- Snowflake
SELECT * FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) = 1;

-- DuckDB
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM table
) WHERE rn = 1;

临时表差异

• Snowflake： 临时表可以位于任何架构中
• DuckDB： 临时表始终位于 temp.main 架构中（不能指定其他架构）
• 两者： 会话结束后自动删除
• DuckDB 名称冲突： 临时表的优先级高于同名普通表。可通过 memory.main.table_name 访问普通表

自动迁移

SQLGlot 可自动将约 85% 的 Snowflake SQL 转换为 DuckDB 语法。不过，复杂查询、Snowflake 专属函数和边缘情况可能无法正确转换。

Transformation Migration Component（keboola.app-transformation-migration）：

• 用于自动化 Snowflake → DuckDB 迁移的实验性组件
• 通过 SQLGlot 处理 SQL 方言转换
• 保留输入／输出表映射和运行时设置
• 如果某个代码块转换失败，则会将原始 SQL 保留为注释

重要： 自动迁移并非 100% 准确。大约 25% 的迁移后转换需要手动调整，通常涉及：

• 大小写敏感性问题
• 不支持的 Snowflake 专属函数
• 数据类型差异
• GROUP BY 中的列别名处理

在生产环境中使用迁移后的配置之前，务必先检查并测试。

工作方式相同的函数

• SUBSTRING(str, start, len)
• POSITION('x' IN str)
• CONCAT(a, b)（两者都能妥善处理 NULL）
• a || b（任意输入为 NULL 时，两者都会返回 NULL）
• LIMIT n
• FETCH FIRST n ROWS
• CREATE TEMP TABLE / CREATE TEMPORARY TABLE
• CREATE OR REPLACE TABLE
• CREATE TABLE IF NOT EXISTS
• CREATE TABLE AS SELECT（CTAS）

真实示例：CRM 数据转换

典型的 CRM 数据转换处理（例如 HubSpot）：

/* companies */
CREATE TABLE "out_companies" AS
SELECT
  "companyId",
  "name",
  "website",
  TRY_CAST(NULLIF("createdate", '') AS DATE) AS "createdate",
  "isDeleted"::BOOLEAN AS "isDeleted"
FROM "companies";

/* contacts */
CREATE TABLE "out_contacts" AS
SELECT
  "canonical_vid",
  "firstname",
  "lastname",
  "email",
  TRY_CAST(NULLIF("createdate", '') AS DATE) AS "createdate",
  "hs_analytics_source" AS "email_source",
  "associatedcompanyid",
  "lifecyclestage"
FROM "contacts";

/* deals */
CREATE TABLE "out_deals" AS
SELECT
  "dealId",
  "isDeleted"::BOOLEAN AS "isDeleted",
  "dealname",
  TRY_CAST(NULLIF("createdate", '') AS DATE) AS "createdate",
  TRY_CAST(NULLIF("closedate", '') AS DATE) AS "closedate",
  "dealtype",
  TRY_CAST(NULLIF("amount", '') AS DOUBLE) AS "amount",
  "pipeline",
  "dealstage",
  "hubspot_owner_id",
  "hs_analytics_source"
FROM "deals";

/* pipeline stages */
CREATE TABLE "out_stages" AS
SELECT
  "stageId",
  "label",
  TRY_CAST(NULLIF("displayOrder", '') AS INT) AS "displayOrder",
  TRY_CAST(NULLIF("probability", '') AS DOUBLE) AS "probability",
  "closedWon"::BOOLEAN AS "closedWon"
FROM "pipeline_stages";

/* deals-contacts association */
CREATE TABLE "out_deals_contacts" AS
SELECT
  "contact_vid",
  "dealId"
FROM "deals_contacts_list";

/* activities */
CREATE TABLE "out_activities" AS
SELECT
  "engagement_id",
  "metadata_subject",
  TRY_CAST(NULLIF("engagement_createdAt", '') AS DATE) AS "engagement_createdAt",
  "metadata_durationMilliseconds",
  "associations_contactIds",
  "associations_dealIds",
  "associations_ownerIds"
FROM "activities";

最佳实践

1. 从小型后端开始，并根据需要扩展
2. 数据集超过 1 GB 时，使用 Parquet
3. 脚本中包含多条语句时，每条语句都以 ; 结尾
4. 统一标识符的大小写（建议使用小写）
5. 为混合大小写或包含特殊字符的标识符加引号
6. 尽早筛选和投影 --- 在靠近数据源的位置应用 WHERE，并且只选择所需列
7. 使用 EXPLAIN 分析开销较大的查询的执行计划
8. 将复杂转换拆分为更小的步骤，每个步骤生成一张输出表
9. 统一输出表的命名方式（例如使用 stg_、fact_、dim_ 前缀）
10. 使用同步操作进行调试（syntax_check、lineage_visualization、execution_plan）
11. 从 Snowflake 迁移时，仔细测试迁移
12. 对可能为空的值进行安全类型转换时，使用 TRY_CAST(NULLIF(...) AS TYPE) 模式

何时选择 DuckDB，而非 Snowflake

以下情况选择 DuckDB：

• 临时分析和中小型数据集（最大约数 TB）
• 快速构建转换原型
• 预算有限的项目
• 开发和测试

以下情况选择 Snowflake：

• 超大型数据集（TB 级以上）
• 复杂的企业工作负载
• 在多个进程间共享数据仓库
• 需要最高可扩展性
• 需要 Snowflake 特有的高级功能

DuckDB 不适用于事务型工作负载。 它是针对 SELECT 语句优化的 OLAP 数据库。 避免频繁执行 INSERT/UPDATE 操作。

参考链接

• DuckDB 官方网站
• SQLGlot --- SQL 转译
• DuckDB Snowflake 扩展 --- 用于本地开发
• 组件 README
• 迁移组件 README
• Keboola DuckDB 文档