tokadev です。最近よく目にする DuckDB について触れつつ簡単なサンプルを動かしたので、その備忘録代わりに書きました。
DuckDB
DuckDB は分析データベースシステムです。基本的なSQLはもちろん、相関サブクエリやウィンドウ関数などをサポートしている他、ジオメトリを扱う際に便利な拡張機能も持っているようです。
導入
Homebrew でインストールします。 Scoop でもインストール出来ますがそちらはバージョンが少し古い模様。
% brew install duckdb;
% duckdb -version;
> v1.0.0 1f98600c2c
% duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
終了する場合は Ctr+D.
いろいろ読ませてみる
csv
gz 形式に圧縮されている場合はそのまま読み込めます。
D SELECT * FROM test.csv.gz;
┌───────┬─────────┐
│ id │ name │
│ int64 │ varchar │
├───────┼─────────┤
│ 1 │ aaa │
│ 2 │ bbb │
└───────┴─────────┘
Excel
シートを指定して読み込みます。
D SELECT * FROM st_read('test.xlsx',
layer = 'シート1',
open_options = ['HEADERS=FORCE', 'FIELD_TYPES=AUTO']
);
Catalog Error: Table Function with name "st_read" is not in the catalog, but it exists in the spatial extension.
Please try installing and loading the spatial extension:
INSTALL spatial;
LOAD spatial;
拡張機能が必要と言われたのでメッセージの通りにインストール
D INSTALL spatial;
D LOAD spatial;
D SELECT * FROM st_read('test.xlsx',
layer = 'シート1',
open_options = ['HEADERS=FORCE', 'FIELD_TYPES=AUTO']
);
┌────────┬─────────┬─────────┐
│ id │ name │ note │
│ double │ varchar │ varchar │
├────────┼─────────┼─────────┤
│ 1.0 │ aaa │ test a │
│ 2.0 │ bbb │ test b │
│ 3.0 │ ccc │ test c │
└────────┴─────────┴─────────┘
スプレッドシート
共有URLを発行する必要がありますが、csv フォーマットすることでスプレッドシートもURLから直接読み込めました。
D SELECT * FROM read_csv_auto('https://docs.google.com/spreadsheets/export?format=csv&id=1osm7XXWr70p3rf4Muq5dbfBV_EUXWU15nz05Ij__06Y', normalize_names=True);
┌───────┬───────┬─────────┐
│ id │ _name │ note │
│ int64 │ int64 │ varchar │
├───────┼───────┼─────────┤
│ 1 │ 111 │ test 1 │
│ 2 │ 222 │ test 2 │
│ 3 │ 333 │ test 3 │
└───────┴───────┴─────────┘
json
ドキュメントを参考にバケットにおいたサンプル用の json ファイルを読ませてみます。
- S3
事前準備として、サンプル用にバケットを作成してファイルをアップロードします。
アクセスキーとシークレットが必要なので、オブジェクト閲覧の権限を付与したサービスアカウントを追加してキーを発行します。
D CREATE SECRET secret_aws (
TYPE S3,
KEY_ID '{AccessKey}',
SECRET '{Secret}',
REGION '{Region}'
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘
Suite URL を直接FROM句に指定できるようです。便利ですね。
D SELECT * FROM read_json_auto('s3://{bucketName}/test1.json');
┌───────┬─────────┐
│ id │ name │
│ int64 │ varchar │
├───────┼─────────┤
│ 1 │ fizz │
│ 2 │ buzz │
└───────┴─────────┘
- Cloud Storage
S3 と同じくアクセスキーとシークレットを設定します。
D CREATE SECRET secret_gcs (
TYPE GCS,
KEY_ID '{AccessKey}',
SECRET '{Secret}'
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘
なお json も直接 gz 圧縮されていればそのまま読み込み出来ます。ついでに JOIN。
D SELECT * FROM read_json_auto('gs://{bucketName}/test2.json');
┌───────┬─────────┐
│ id │ name │
│ int64 │ varchar │
├───────┼─────────┤
│ 1 │ hoge │
│ 2 │ fuga │
└───────┴─────────┘
D SELECT * FROM read_json_auto('gs://{bucketName}/test3.json.gz');
┌───────┬─────────┐
│ id │ note │
│ int64 │ varchar │
├───────┼─────────┤
│ 1 │ hello │
│ 2 │ world │
└───────┴─────────┘
D SELECT
*
FROM read_json_auto('gs://{bucketName}/test2.json') AS t2
INNER JOIN read_json_auto('gs://{bucketName}/test3.json.gz') AS t3
USING(id)
;
┌───────┬─────────┬─────────┐
│ id │ name │ note │
│ int64 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│ 1 │ hoge │ hello │
│ 2 │ fuga │ world │
└───────┴─────────┴─────────┘
FROM 句のオブジェクト指定をワイルドカードにも出来るようです。ログをまとめて読ませたい時に便利そう。
D SELECT * FROM read_json_auto('gs://duckdb-bucket/*.json');
┌───────┬─────────┐
│ id │ name │
│ int64 │ varchar │
├───────┼─────────┤
│ 1 │ hoge │
│ 2 │ fuga │
└───────┴─────────┘
D SELECT * FROM read_json_auto('gs://duckdb-bucket/*');
┌───────┬─────────┬─────────┐
│ id │ name │ note │
│ int64 │ varchar │ varchar │
├───────┼─────────┼─────────┤
│ 1 │ hoge │ │
│ 2 │ fuga │ │
│ 1 │ │ hello │
│ 2 │ │ world │
└───────┴─────────┴─────────┘
おわりに
バケットに貯めた構造化ログは Athena や BigQuery で解析するというイメージが強かったので、これらを DuckDB に置き換えることが出来れば運用コストを下げることが出来そうというのがパッと思いつきました。
バケットのオブジェクトを参照する場合はダウンロードする時間がかかりますが、すでに読み込んでいれば大容量のファイルの解析も高速で行えるとのこと。
拡張機能のページを眺めると ST_Within など PostGIS ではおなじみの空間関係関数もあるので、GISとの相性も良さそうです。
おまけ
なんで "Duck" なんだろうと思ったらFAQで言及されてました 🦆
Discussion