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 を使用 --- CSV の代わりに Parquet を使用します（1 GB 超の場合に推奨）
• 入力テーブルのデータ型を推定 --- 入力テーブルのデータ型を推定します（型が設定されていない Storage で便利です）
• デバッグモード --- デバッグログを有効にします

ブロックベースのオーケストレーション

• ブロックは順次実行されます（1 つずつ）
• ブロック内のスクリプトは（依存関係がない場合）並列実行されます
• SQL の自動分析に SQLGlot を使用します
• 依存関係の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 など）を推定するため、手動で型変換しなくても集計関数や型固有の操作を実行できます。

推定なしの場合：amount は VARCHAR のため、SUM("amount") は失敗します。 推定ありの場合：DuckDB が amount を数値型として検出するため、SUM("amount") は動作します。

同期アクション

変換全体を実行せずに利用できる、デバッグ用アクションが 4 つあります。

1. syntax_check --- SQL 構文を実行せずに検証します
2. lineage_visualization --- データ依存関係の Markdown 図を生成します
3. execution_plan_visualization --- ブロックとバッチを含む実行予定順序を表示します
4. expected_input_tables --- SQL 分析に基づいて、入力テーブルの一覧を表示します

SQL の記述ルール

ステートメント間のセミコロン

各 SQL ステートメントの末尾には、必ずセミコロン（;）を付けてください。1 つのスクリプトに複数のステートメントを記述する場合は、適切に区切る必要があります。

-- 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 を使ったサブクエリ
NULL の処理	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 は Snowflake SQL の約 85% を 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. **複雑な変換は小さなステップに分割し、**各ステップで 1 つの出力テーブルを生成します
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 ドキュメント