お世話になっております。FLINTERSでデータエンジニアをしている堀と申します。今回の投稿はFLINTERSブログ祭りの記事です。テーマは #データ分析 #SQL #技術 です。
本日は爆速分析SQLエンジンのDuckDBを紹介したいです。
そもそも
brew install duckdb
ポイント
- インストールかんたん
- パッと起動して、さまざまなデータソースにSQL分析ができる
- 豊富なインプット方法、アウトプット方法
- ローカルのデータファイル（CSV, JSON, parquet, Excel）
- http(s)でアクセスできるデータファイル
- S3/GCS/Azure Blob Storage上のデータファイル
- PostgreSQL
- MySQL
- また、DuckDBは各言語のクライアントライブラリでもある。いろいろあるよ
- 豊富なインプット方法、アウトプット方法
- SQLの実行が爆速
なんでDuckDBという名前なの？
Ducks are amazing animals. They can fly, walk and swim. They can also live off pretty much everything. They are quite resilient to environmental challenges. A duck’s song will bring people back from the dead and inspires database research. They are thus the perfect mascot for a versatile and resilient data management system. Also the logo designs itself. https://duckdb.org/faq#why-call-it-duckdb
アヒルは以下の特徴を備えるため、多目的&強靭なデータ管理システムのパーフェクトマスコットなんだそう
- 飛べる
- 歩ける
- 泳げる
- いろんなものを食べて生きていくことができる
- 環境変化に強い
Cyberduckとの関係は、ないと思います
まずは--help
❯ duckdb --help Usage: duckdb [OPTIONS] FILENAME [SQL] FILENAME is the name of an DuckDB database. A new database is created if the file does not previously exist. OPTIONS include: -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -c COMMAND run "COMMAND" and exit -csv set output mode to 'csv' -echo print commands before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -markdown set output mode to 'markdown' -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -no-stdin exit after processing options instead of reading stdin -nullvalue TEXT set text string for NULL values. Default '' -quote set output mode to 'quote' -readonly open the database read-only -s COMMAND run "COMMAND" and exit -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -unredacted allow printing unredacted secrets -unsigned allow loading of unsigned extensions -version show DuckDB version
- 引数なければ、対話IFが起動する
- mysqlとかpsqlとかと同じ
- -cで対話IFを起動せずにクエリ実行できるね
- -json, -csvでシェ芸に持ち込めるね
ローカルファイルでやってみた
起動したらすぐ！起動したディレクトリにあるCSVファイルやparquetファイルやjsonファイルにSQL実行できる！
❯ duckdb v0.10.3 70fd6a8a24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. D select * from 'yellow_tripdata_2024-01.parquet'; ┌──────────┬──────────────────────┬──────────────────────┬─────────────────┬───────────────┬───┬────────────┬──────────────┬──────────────────────┬──────────────┬──────────────────────┬─────────────┐ │ VendorID │ tpep_pickup_datetime │ tpep_dropoff_datet… │ passenger_count │ trip_distance │ … │ tip_amount │ tolls_amount │ improvement_surcha… │ total_amount │ congestion_surcharge │ Airport_fee │ │ int32 │ timestamp │ timestamp │ int64 │ double │ │ double │ double │ double │ double │ double │ double │ ├──────────┼──────────────────────┼──────────────────────┼─────────────────┼───────────────┼───┼────────────┼──────────────┼──────────────────────┼──────────────┼──────────────────────┼─────────────┤ │ 2 │ 2024-01-01 00:57:55 │ 2024-01-01 01:17:43 │ 1 │ 1.72 │ … │ 0.0 │ 0.0 │ 1.0 │ 22.7 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:03:00 │ 2024-01-01 00:09:36 │ 1 │ 1.8 │ … │ 3.75 │ 0.0 │ 1.0 │ 18.75 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:17:06 │ 2024-01-01 00:35:01 │ 1 │ 4.7 │ … │ 3.0 │ 0.0 │ 1.0 │ 31.3 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:36:38 │ 2024-01-01 00:44:56 │ 1 │ 1.4 │ … │ 2.0 │ 0.0 │ 1.0 │ 17.0 │ 2.5 │ 0.0 │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ 1 │ 2024-01-31 23:13:07 │ 2024-01-31 23:27:52 │ │ 4.0 │ … │ 2.34 │ 0.0 │ 1.0 │ 25.74 │ │ │ │ 2 │ 2024-01-31 23:19:00 │ 2024-01-31 23:38:00 │ │ 3.33 │ … │ 0.0 │ 0.0 │ 1.0 │ 23.97 │ │ │ │ 2 │ 2024-01-31 23:07:23 │ 2024-01-31 23:25:14 │ │ 3.06 │ … │ 5.58 │ 0.0 │ 1.0 │ 33.46 │ │ │ │ 1 │ 2024-01-31 23:58:25 │ 2024-02-01 00:13:30 │ │ 8.1 │ … │ 7.29 │ 6.94 │ 1.0 │ 55.88 │ │ │ ├──────────┴──────────────────────┴──────────────────────┴─────────────────┴───────────────┴───┴────────────┴──────────────┴──────────────────────┴──────────────┴──────────────────────┴─────────────┤ │ 2964624 rows (8 shown) 19 columns (11 shown) │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ D
CSVに対してSQLでクエリできるツールは見たことあるけど、CSV専門じゃないのがいい感じ。
リモートファイルでやってみた
というか、データファイルをDLする必要すらない。FROM句に直接URLを与えられる。
D select * from 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet'; 100% ▕████████████████████████████████████████████████████████████▏ ┌──────────┬──────────────────────┬──────────────────────┬─────────────────┬───────────────┬───┬────────────┬──────────────┬──────────────────────┬──────────────┬──────────────────────┬─────────────┐ │ VendorID │ tpep_pickup_datetime │ tpep_dropoff_datet… │ passenger_count │ trip_distance │ … │ tip_amount │ tolls_amount │ improvement_surcha… │ total_amount │ congestion_surcharge │ Airport_fee │ │ int32 │ timestamp │ timestamp │ int64 │ double │ │ double │ double │ double │ double │ double │ double │ ├──────────┼──────────────────────┼──────────────────────┼─────────────────┼───────────────┼───┼────────────┼──────────────┼──────────────────────┼──────────────┼──────────────────────┼─────────────┤ │ 2 │ 2024-01-01 00:57:55 │ 2024-01-01 01:17:43 │ 1 │ 1.72 │ … │ 0.0 │ 0.0 │ 1.0 │ 22.7 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:03:00 │ 2024-01-01 00:09:36 │ 1 │ 1.8 │ … │ 3.75 │ 0.0 │ 1.0 │ 18.75 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:17:06 │ 2024-01-01 00:35:01 │ 1 │ 4.7 │ … │ 3.0 │ 0.0 │ 1.0 │ 31.3 │ 2.5 │ 0.0 │ │ 1 │ 2024-01-01 00:36:38 │ 2024-01-01 00:44:56 │ 1 │ 1.4 │ … │ 2.0 │ 0.0 │ 1.0 │ 17.0 │ 2.5 │ 0.0 │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ │ 1 │ 2024-01-31 23:13:07 │ 2024-01-31 23:27:52 │ │ 4.0 │ … │ 2.34 │ 0.0 │ 1.0 │ 25.74 │ │ │ │ 2 │ 2024-01-31 23:19:00 │ 2024-01-31 23:38:00 │ │ 3.33 │ … │ 0.0 │ 0.0 │ 1.0 │ 23.97 │ │ │ │ 2 │ 2024-01-31 23:07:23 │ 2024-01-31 23:25:14 │ │ 3.06 │ … │ 5.58 │ 0.0 │ 1.0 │ 33.46 │ │ │ │ 1 │ 2024-01-31 23:58:25 │ 2024-02-01 00:13:30 │ │ 8.1 │ … │ 7.29 │ 6.94 │ 1.0 │ 55.88 │ │ │ ├──────────┴──────────────────────┴──────────────────────┴─────────────────┴───────────────┴───┴────────────┴──────────────┴──────────────────────┴──────────────┴──────────────────────┴─────────────┤ │ 2964624 rows (8 shown) 19 columns (11 shown) │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ D
PostgreSQL つないでみた
ほぼこれ https://duckdb.org/docs/extensions/postgres
- 初回のみextensionのinstallが必要っぽい
- ATTACH文を使って接続
ATTACH 'dbname=my_database user=hori_de_gozaimasu host=111.111.111.111 port=5432 password=qwertyuiop' AS db (TYPE POSTGRES, READ_ONLY);
ATTACHしたらクエリできた
select * from db.public.table_name;
- DBクライアントとして使う場合は
-cmdオプションを使って、対話IFに入る前にATTACH文を実行しておいてもらうとよさそうだ-
-cmdオプションとは:-cmd COMMAND run "COMMAND" before reading stdin duckdb -cmd "ATTACH 'dbname=my_database user=hori_de_gozaimasu host=111.111.111.111 port=5432 password=qwertyuiop' AS db (TYPE POSTGRES, READ_ONLY);"として起動すれば、dbにすぐクエリできる、みたいなこと
-
フィルタコマンド的に使ってみた
- ラーメンAPIってものがあるらしいので、これのレスポンスJSONをパースして遊んでみる https://yusukebe.com/posts/2022/ramen-api/
- DuckDBで、標準入力からデータを読み取るには https://duckdb.org/docs/api/cli/overview#reading-from-stdin-and-writing-to-stdout
ガチャガチャやってみて、できたのがこちら
❯ curl --silent https://ramen-api.dev/shops | duckdb -list -noheader -c "with ramen_shops as (select unnest(shops) as shop from read_json_auto('/dev/stdin')) select shop.name from ramen_shops"
吉村家
杉田家
たかさご家
上々家
とらきち家
らすた
六角家 戸塚店
維新商店
札幌ラーメン こぐま
らーめん まつや
- unnest関数でリストを行に分割
- struct（構造体）のフィールドにアクセスする場合は
.を使う。shop.name- このへんはBigQueryでおなじみですかね
ちなみに同様のことをjqでやると、次のようになる
curl --silent https://ramen-api.dev/shops | jq --raw-output .shops[].name
この項はほんとにjqやawkを統合していこう！という気概を感じた
いろんなオプションを試してみた
-nullvalue TEXT
デフォルトだと、nullと空文字の見分けがつかない。
❯ duckdb -c "select '' as a, null as b;" ┌─────────┬───────┐ │ a │ b │ │ varchar │ int32 │ ├─────────┼───────┤ │ │ │ └─────────┴───────┘
この挙動を変えるのが-nullvalue TEXT
❯ duckdb -nullvalue '(null)' -c "select '' as a, null as b;" ┌─────────┬────────┐ │ a │ b │ │ varchar │ int32 │ ├─────────┼────────┤ │ │ (null) │ └─────────┴────────┘
output形式を変更するオプション全部試す
- 題材クエリは
select 'a' as a, null as b, 123 as c"; -nullvalue '(null)'付与
-ascii
abcabc(null)123
-box
┌─────┬────────┬─────┐ │ a │ b │ c │ ├─────┼────────┼─────┤ │ abc │ (null) │ 123 │ └─────┴────────┴─────┘
-column
a b c --- ------ --- abc (null) 123
-csv
a,b,c abc,(null),123
お、ちゃんと-nullvalue '(null)'の指定が反映されてる。いいじゃん
-html
<TR><TH>a</TH> <TH>b</TH> <TH>c</TH> </TR> <TR><TD>abc</TD> <TD>(null)</TD> <TD>123</TD> </TR>
-json
[{"a":"abc","b":null,"c":123}]
お、ちゃんと-nullvalue '(null)'の指定が反映されていない。いいじゃん
-line
a = abc
b = (null)
c = 123
-list
a|b|c abc|(null)|123
-markdown
| a | b | c | |-----|--------|----:| | abc | (null) | 123 |
markdownのコンテキストに貼れば、表になる
| a | b | c |
|---|---|---|
| abc | (null) | 123 |
-quote
'a'|'b'|'c' 'abc'|NULL|123
-table
+-----+--------+-----+ | a | b | c | +-----+--------+-----+ | abc | (null) | 123 | +-----+--------+-----+
おわりに
私の場合、これまでアドホックなデータファイル分析において、
- JSON -> jq
- CSV -> awk
- parquet -> parquet-cli
という感じで、データ種ごとのツールを使い分けていた。またjq, awkではある程度のツールへの習熟が必要であり、分析ソースコードは誰も読めないものになりがちだった。
社内的にも話者の多いSQLであれば、分析ソースコードの共同作業（相互レビュー、共同開発）が容易になりそうで、そんなところも期待しています。
