先日読んだ記事で dbt に DuckDB 用のプラグインがあると知り、dbt と DuckDB の学習も兼ねて試してみることにしました。とても簡単な実験ですが、何かの参考になれば幸いです。
今回実際に試してみて感じたのが、dbt と DuckDB の相性の良さです。手元で動く DWH に対して dbt のデータ変換処理の開発を手早く行うことができ、外部連携部分は DuckDB が吸収してくれるため、dbt は素直に SQL を利用したデータ変換に専念できそうです。以前読んだ記事は、データが巨大でなければ dbt + DuckDB で本番データに対してもデータ変換処理をしてしまおうという趣旨の記事で、面白い内容でした。併せてご覧ください:
概要
アーキテクチャ
アーキテクチャというほどのものではないですが、S3 の CSV ファイルを dbt-duckdb プラグインで読み込んで集計し、結果を Parquet ファイルにして S3 へ転送してみます。非常に簡単な実験ですが、上の記事に倣って dbt + DuckDB を ETL 処理として使うなら、外部からデータを読み込み、集計結果をまたどこか別の場所に保存する似たようなアーキテクチャになるかなと思います。
①のオープンデータのS3への保存は手動で行なったのですが、次で理由を説明します。
使ったデータ
使うオープンデータはこちらにしました。CSV形式で提供されていたのと、市区町村別の人口総数という分かりやすい指標だったからです。
出典：e-Govポータル （https://www.e-gov.go.jp）
ただこの CSV が SJIS で提供されており、DuckDB の read_csv メソッドがどうやら UTF-8 にしか対応していないようで、一度文字コード変換をする必要があります。今回は手元にCSVをダウンロードして、iconv コマンドで文字コード変換をして S3 へアップロードしましたが、日々追加されていくファイルに対して手動オペレーションは挟めないので、どこかで自動化する必要があります（要調査。。）
iconv -f sjis -t utf8 ~/Downloads/sjis_file.csv > ~/Downloads/utf8_file.csv
環境準備
DuckDB インストール
dbt インストール
Python のライブラリを独立させるため、virtualenv を使います:
dbt-duckdb のインストール・セットアップは dbtの公式HPに倣います。pip install dbt-duckdb で dbt Core と dbt-duckdb プラグインをまとめてインストールできます。
ディレクトリ構成
普段使っていないのが丸わかりですが…とりあえず動くことを目標にしたので dbt のディレクトリ構成は最小限に留めました。dbt init コマンドで population_census プロジェクトを生成します。
% dbt init population_census 17:02:29 Running with dbt=1.4.6 Which database would you like to use? [1] duckdb (Don't see the one you want? https://docs.getdbt.com/docs/available-adapters) Enter a number: 1 ... Happy modeling!
~/.dbt/profiles.yml
population_census/dbt_project.yml
population_census/models/population_census/opendata.yml
/population_by_city.sql
/population_by_prefecture.sql
profiles.yml は dbt-duckdb の Github README に倣い、AWS へのアクセスキーとシークレットキーの設定をしています。dbt-duckdb の Github README だけ読むと分からないですが、プロジェクト配下ではなくホーム配下の .dbt/ フォルダに設置する必要があります。
dbt_project.yml
このファイルも大幅に簡略化しています。models: の指定だけディレクトリ構成と合っていれば大丈夫かと思います。
name: 'population_census'
version: '1.0.0'
config-version: 2
profile: 'population_census'
model-paths: ["models"]
test-paths: ["tests"]
target-path: "target" # directory which will store compiled SQL files
clean-targets: # directories to be removed by `dbt clean`
- "target"
- "dbt_packages"
models:
population_census:
+materialized: table
opendata.yml - 外部ソース(S3) の指定
DuckDB の read_csv を利用し、S3 の CSV ファイルの設定をします。今回読み込むCSVには不要なヘッダが7行あるためこれをスキップし、型推論は auto_detect=True で自動にしました。
version: 2
sources:
- name: external_source
tables:
- name: population_census_jp_2014
meta:
external_location: "read_csv('s3://<BUCKET_NAME>/{name}.csv', delim=',', auto_detect=True, header=True, skip=7)"
population_by_city.sql - 外部ソースの読み込み
外部ソースからデータを読み込み、 population_by_city というテーブル名で DuckDB に保存します。データ内の数値が3桁区切りごとにカンマが入った文字列型だったため、クレンジング処理を加えています。
北海道 札幌市,11002,"1,010,177 ","1,240,613 ","1,401,757","1,542,979","1,671,742","1,757,025","1,822,368","1,880,863","1,913,545"
WITH original_data AS ( SELECT "市区町村名" AS CityName, "市区町村コード" AS CityCode, "1970年" AS Population1970, "1975年" AS Population1975, ... "2010年" AS Population2010 FROM {{ source('external_source', 'population_census_jp_2014') }} ) SELECT CityName, CityCode, CAST(REPLACE(Population1970, ',', '') AS INT) AS Population1970, CAST(REPLACE(Population1975, ',', '') AS INT) AS Population1975, ... CAST(REPLACE(Population2010, ',', '') AS INT) AS Population2010 FROM original_data
population_by_prefecture.sql - DuckDB 上での集計＆S3へ保存
population_by_city テーブルを都道府県のレベルで集約し、これを Parquet 形式で S3に保存します。この指定はファイル先頭に config の指定をするだけで実現できます。
{{ config(materialized='external', location='s3://dbt-duckdb-test-hitoshi-tsuda/population_by_prefecture.parquet') }}
SELECT STRING_SPLIT(CityName, ' ')[1] AS Prefecture,
SUM(Population1970) AS Population1970,
SUM(Population1975) AS Population1975,
...
SUM(Population2010) AS Population2010
FROM {{ref('population_by_city')}}
GROUP BY 1
実行
dbt run で実行します。エラーが出た場合は dbt -d run でデバッグ出力をしてエラー内容を確認します。
% dbt run 17:03:36 Running with dbt=1.4.6 17:03:36 Found 2 models, 0 tests, 0 snapshots, 0 analyses, 297 macros, 0 operations, 0 seed files, 1 source, 0 exposures, 0 metrics 17:03:36 17:03:36 Concurrency: 1 threads (target='dev') 17:03:36 17:03:36 1 of 2 START sql table model main.population_by_city ........................... [RUN] 17:03:39 1 of 2 OK created sql table model main.population_by_city ...................... [OK in 2.80s] 17:03:39 2 of 2 START sql external model main.population_by_prefecture .................. [RUN] 17:03:43 2 of 2 OK created sql external model main.population_by_prefecture ............. [OK in 4.21s] 17:03:44 17:03:44 Finished running 1 table model, 1 external model in 0 hours 0 minutes and 7.36 seconds (7.36s). 17:03:44 17:03:44 Completed successfully 17:03:44 17:03:44 Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2
出力ファイルの確認
S3に生成された Parquet ファイルをローカルにダウンロードし、DuckDB で読み込んでみます:
% ./duckdb /tmp/dbt.duckdb D SELECT Prefecture, Population1970, Population2010 FROM '~/Downloads/population_by_prefecture.parquet' LIMIT 10 ; ┌────────────┬────────────────┬────────────────┐ │ Prefecture │ Population1970 │ Population2010 │ │ varchar │ double │ double │ ├────────────┼────────────────┼────────────────┤ │ 北海道 │ 5184287.0 │ 5506419.0 │ │ 青森県 │ 1427520.0 │ 1373339.0 │ │ 岩手県 │ 1371383.0 │ 1330147.0 │ │ 宮城県 │ 1819223.0 │ 2348165.0 │ │ 秋田県 │ 1241376.0 │ 1085997.0 │ │ 山形県 │ 1225618.0 │ 1168924.0 │ │ 福島県 │ 1946077.0 │ 2029064.0 │ │ 茨城県 │ 2143551.0 │ 2969770.0 │ │ 栃木県 │ 1580021.0 │ 2007683.0 │ │ 群馬県 │ 1658909.0 │ 2008068.0 │ ├────────────┴────────────────┴────────────────┤ │ 10 rows 3 columns │ └──────────────────────────────────────────────┘
参考ページ
notrogue さんの DuckDB の解説がとても分かりやすいです: zenn.dev
GitHub - jwills/dbt-duckdb: dbt (http://getdbt.com) adapter for DuckDB (http://duckdb.org)
DuckDB setup | dbt Developer Hub
