Techtouch Developers Blog

テックタッチ株式会社の開発チームによるテックブログです

DuckDBでお手軽！データフェデレーション

データエンジニア・分析


tl;dr

  • DuckDB 便利だよ。分析以外でも使えるよ
  • 色々な場所のデータを閲覧・結合できるよ。標準SQLも使えるよ
  • ただし、細かい落とし穴は色々あるので気をつけてね

はじめに

2023年4月にデータエンジニアとして入社したmin（@not_rogue）です。暖かくなるにつれ、YouTube で見た南伊豆ロングトレイル | 松崎町に行く機運が日々高まっています！

弊社では

  • 各種マスター情報は Amazon Aurora に
  • 各種ログを Amazon S3 に

保存しています。業務の中で、こういった各所のデータを見たいことがたまにあります。例えば、

  • 障害があった時の原因調査のために S3 のデータを確認したい
  • さらに、その時、S3 上のログと Aurora 上のマスターと紐づけて見たい

といった場合です。
こういった場合に、DuckDB というツールが便利では？ということで、その使い方をご紹介します。

DuckDB とは

シングルバイナリで動作するデータベース（いわゆる Embedded database）の一つです。同じカテゴリのプロダクトでは SQLite3 が有名ですが、DuckDB は分析用途よりのプロダクトです。
DuckDB のその他特徴や SQLite3 との違いについては

などをご参照ください。

DuckDB では何が読めるのか

色々あります！ファイルフォーマットとしては

  • CSV
  • JSON
  • Parquet
  • Excel

ファイルの置き場所としては

  • ローカル
  • AWS/Azure/Google Cloud のオブジェクトストレージ
  • HTTP（HTTPS）

他のデータベースとしては

  • MySQL
  • PostgreSQL
  • SQLite3

のデータを読み込むことができます。（書き込みもできます）
本記事ではそのうち、

  • S3 上の JSON
  • MySQL上のテーブル

をDuckDBで読み込みました。

使ってみる

インストール（DuckDB Installation - DuckDB）は済んでいる前提です。macOS Ventura・DuckDB 0.10 で試しました。

S3 上のJSON を読んでみる

手順としては、

  1. （この記事では省略）AWS の認証情報を設定（アクセスキーやSSO 等）
  2. AWS クレデンシャルを読み込み
  3. read_json_auto で JSON ファイルを読み込み
    1. スキーマを推測して自動でカラムにしてくれます（明示的にカラムを指定することも可能）
    2. GZIP 圧縮にも対応してくれます

するだけです。

S3 上の JSON ファイルの例として、AWS Open Data Sponsorship Program で公開されているAWS Marketplace: Open Observatory of Network Interference (OONI)のデータを見てみます。

OONI は各種インターネットサイトへの検閲・ブロック情報を収集するプロジェクトで、gzip圧縮された JSON（NDJSONと かJSON Lines と呼ばれるやつ）が S3 に公開されています。
どんなデータか AWS cli と jq で見てみます。

aws s3 cp  --no-sign-request s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz - | gzcat | head -n1 | jq ''

{

"resolver_network_name": "Microsoft Corporation",

"software_name": "miniooni",

"software_version": "0.18.0-alpha",

"test_runtime": 1.000464311,

"test_start_time": "2020-10-05 02:19:55",

"resolver_asn": "AS8075",

"probe_network_name": "Microsoft Corporation",

"test_keys": {

"success": true

},

"data_format_version": "0.2.0",

"measurement_start_time": "2020-10-05 02:19:55",

"test_version": "0.0.1",

"test_name": "example",

"resolver_ip": "13.89.169.76",

"input": null,

"probe_ip": "127.0.0.1",

"report_id": "20201005T021955Z_AS8075_9SU2fyJnElS2ENUND7jDTdAMS0RhchSsFWblxFV8VoROEny7O6",

"probe_asn": "AS8075",

"annotations": {

"platform": "linux",

"assets_version": "20200929203018",

"engine_version": "0.18.0-alpha",

"engine_name": "ooniprobe-engine"

},

"probe_cc": "US",

"measurement_uid": "012020100514b4aa5fed8e67e3970e7b24c7601f"

}
DuckDB でファイル単位で見てみます。
$ duckdb

# AWSクレデンシャルを読み込み

D call load_aws_credentials();

# S3上のJSONを

D select * from read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz') limit 1;

┌──────────────────────┬───────────────┬──────────────────┬──────────────┬─────────────────────┬──────────────┬───┬──────────────────────┬───────────┬──────────────────────┬──────────┬──────────────────────┐

│ resolver_network_n…  │ software_name │ software_version │ test_runtime │   test_start_time   │ resolver_asn │ … │      report_id       │ probe_asn │     annotations      │ probe_cc │   measurement_uid    │

│       varchar        │    varchar    │     varchar      │    double    │      timestamp      │   varchar    │   │       varchar        │  varchar  │ struct(platform va…  │ varchar  │       varchar        │

├──────────────────────┼───────────────┼──────────────────┼──────────────┼─────────────────────┼──────────────┼───┼──────────────────────┼───────────┼──────────────────────┼──────────┼──────────────────────┤

│ Microsoft Corporat…  │ miniooni      │ 0.18.0-alpha     │  1.000464311 │ 2020-10-05 02:19:55 │ AS8075       │ … │ 20201005T021955Z_A…  │ AS8075    │ {'platform': linux…  │ US       │ 012020100514b4aa5f…  │

├──────────────────────┴───────────────┴──────────────────┴──────────────┴─────────────────────┴──────────────┴───┴──────────────────────┴───────────┴──────────────────────┴──────────┴──────────────────────┤

│ 1 rows                                                                                                                                                                                20 columns (11 shown) │

└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

途中カラムが省略されています。表示フォーマットを変えると確認できます。
（デフォルトの duckbox というフォーマットではウィンドウ幅に合わせて省略してくれるようです）

D .mode box

D select * from read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz') limit 1;

┌───────────────────────┬───────────────┬──────────────────┬──────────────┬─────────────────────┬──────────────┬───────────────────────┬───────────────────┬─────────────────────┬────────────────────────┬──────────────┬───────────┬──────────────┬───────┬───────────┬────────────────────────────────────────────────────────────────────────────┬───────────┬────────────────────────────────────────────────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐

│ resolver_network_name │ software_name │ software_version │ test_runtime │   test_start_time   │ resolver_asn │  probe_network_name   │     test_keys     │ data_format_version │ measurement_start_time │ test_version │ test_name │ resolver_ip  │ input │ probe_ip  │                                 report_id                                  │ probe_asn │                                    annotations                                     │ probe_cc │             measurement_uid              │

├───────────────────────┼───────────────┼──────────────────┼──────────────┼─────────────────────┼──────────────┼───────────────────────┼───────────────────┼─────────────────────┼────────────────────────┼──────────────┼───────────┼──────────────┼───────┼───────────┼────────────────────────────────────────────────────────────────────────────┼───────────┼────────────────────────────────────────────────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤

│ Microsoft Corporation │ miniooni      │ 0.18.0-alpha     │ 1.000464311  │ 2020-10-05 02:19:55 │ AS8075       │ Microsoft Corporation │ {'success': true} │ 0.2.0               │ 2020-10-05 02:19:55    │ 0.0.1        │ example   │ 13.89.169.76 │       │ 127.0.0.1 │ 20201005T021955Z_AS8075_9SU2fyJnElS2ENUND7jDTdAMS0RhchSsFWblxFV8VoROEny7O6 │ AS8075    │ {'platform': linux, 'assets_version': 20200929203018, 'engine_version': 0.18.0-... │ US       │ 012020100514b4aa5fed8e67e3970e7b24c7601f │

└───────────────────────┴───────────────┴──────────────────┴──────────────┴─────────────────────┴──────────────┴───────────────────────┴───────────────────┴─────────────────────┴────────────────────────┴──────────────┴───────────┴──────────────┴───────┴───────────┴────────────────────────────────────────────────────────────────────────────┴───────────┴────────────────────────────────────────────────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘
desc コマンドで、read_json_auto が推測したスキーマを確認できます。
（見やすさのためdescコマンドの結果を、limitで制限しています）
D select * from (desc select * from read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz') ) order by column_name limit 3;

┌─────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐

│     column_name     │                                          column_type                                          │  null   │   key   │ default │  extra  │

│       varchar       │                                            varchar                                            │ varchar │ varchar │ varchar │ varchar │

├─────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤

│ annotations         │ STRUCT(platform VARCHAR, assets_version VARCHAR, engine_version VARCHAR, engine_name VARCHAR) │ YES     │         │         │         │

│ data_format_version │ VARCHAR                                                                                       │ YES     │         │         │         │

│ input               │ JSON                                                                                          │ YES     │         │         │         │

└─────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┘
もちろん、普通のテーブル同様に集計することもできます。
D select count(1), max(test_start_time), min(test_start_time) from read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz') limit 1;

┌──────────┬──────────────────────┬──────────────────────┐

│ count(1) │ max(test_start_time) │ min(test_start_time) │

│  int64   │      timestamp       │      timestamp       │

├──────────┼──────────────────────┼──────────────────────┤

│      108 │ 2020-10-05 17:41:44  │ 2020-10-05 02:19:55  │

└──────────┴──────────────────────┴──────────────────────┘
create table as select（CTAS ）で DuckDB のフォーマットで保存もできます。一度作成すると JSON を直接読むより、クエリが早くなります。
D create table hoge as select * from  read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz');

D .timer on

D select count(1), max(test_start_time), min(test_start_time) from hoge limit 1;

┌──────────┬──────────────────────┬──────────────────────┐

│ count(1) │ max(test_start_time) │ min(test_start_time) │

│  int64   │      timestamp       │      timestamp       │

├──────────┼──────────────────────┼──────────────────────┤

│      108 │ 2020-10-05 17:41:44  │ 2020-10-05 02:19:55  │

└──────────┴──────────────────────┴──────────────────────┘

Run Time (s): real 0.002 user 0.001139 sys 0.000752

# （json直接クエリする時はreal 2.529でした）
ワイルドカード・Glob で複数ファイルを指定することもできます。
D select count(1) from read_json_auto('s3://ooni-data-eu-fra/jsonl/whatsapp/JP/202001*/**') limit 1;

┌──────────┐

│ count(1) │

│  int64   │

├──────────┤

│       62 │

└──────────┘

# filenameオプションつけると対応するファイルがわかります

D select filename  from read_json_auto('s3://ooni-data-eu-fra/jsonl/whatsapp/JP/202001*/**', filename=true)  limit 2;;

┌───────────────────────────────────────────────────────────────────────────────────────┐

│                                       filename                                        │

│                                        varchar                                        │

├───────────────────────────────────────────────────────────────────────────────────────┤

│ s3://ooni-data-eu-fra/jsonl/whatsapp/JP/20200102/00/20200102_JP_whatsapp.l.0.jsonl.gz │

│ s3://ooni-data-eu-fra/jsonl/whatsapp/JP/20200102/00/20200102_JP_whatsapp.l.0.json

リレーショナルデータベース

DuckDB では MySQL/PostgreSQL/SQLite3 上のテーブルに対して、DuckDB を経由してクエリを実行することができます。ここでは、MySQL 上のテーブルに対してクエリを実行してみます。

MySQL の例として、Docker 上に構築した MySQL サーバーに、ASN 番号を持つテーブルを用意したものを使います。用意方法については付録をご覧ください。
DuckDB から MySQL 上のデータにアクセスするには、拡張をインストールして、ATTACH コマンドを呼ぶだけです。

$ duckdb

D INSTALL mysql;

D ATTACH 'host=127.0.0.1 password=example user=root port=3308 database=test' AS mysqldb (TYPE mysql);
DuckDB 上の mysqldb というデータベースに、MySQL 上のテーブルが同期され、

  • MySQL 上のテーブルの読み書き
  • MYSQL 上のテーブルと、DuckDB 上の他のテーブルとの組み合わせ

ができるようになります。

# MySQLのテーブルが見えます

D .tables

asn

# MySQLのテーブルが閲覧できます

D select * from mysqldb.asn limit 1;

┌─────────┬─────────┬────────────────────────┐

│   asn   │ country │          name          │

│ varchar │ varchar │        varchar         │

├─────────┼─────────┼────────────────────────┤
│ AS23456 │ UN      │ 4Byte ASN Place Holder │
└─────────┴─────────┴────────────────────────┘

# MySQLのデータとMySQL以外のテーブルを組み合わせることもできます

D select * from (select test_start_time, probe_asn from read_json_auto('s3://ooni-data-eu-fra/jsonl/example/US/20201005/00/20201005_US_example.x.f7d82996d07a0e96.jsonl.gz') limit 1) s left join mysqldb.asn on asn.asn = s.probe_asn;

┌─────────────────────┬───────────┬─────────┬─────────┬─────────────────────────────────────────────────────┐

│   test_start_time   │ probe_asn │   asn   │ country │                        name                         │

│      timestamp      │  varchar  │ varchar │ varchar │                       varchar                       │

├─────────────────────┼───────────┼─────────┼─────────┼─────────────────────────────────────────────────────┤/

│ 2020-10-05 02:19:55 │ AS8075    │ AS8075  │ US      │ MICROSOFT-CORP-MSN-AS-BLOCK - Microsoft Corporation │

└─────────────────────┴───────────┴─────────┴─────────┴─────────────────────────────────────────────────────┘
ただし、MySQL 上のテーブルにクエリすると、一度全部ローカルに持って来る可能性があるので注意が必要です。
試しに、where があるクエリを DuckDB から実行してみます。
D select * from mysqldb.asn  where asn = 'AS560' limit 1;

┌─────────┬─────────┬───────────────────────────────────┐

│   asn   │ country │               name                │

│ varchar │ varchar │              varchar              │

├─────────┼─────────┼───────────────────────────────────┤

│ AS560   │ US      │ LVLT-560 - Level 3 Communications │

└─────────┴─────────┴───────────────────────────────────┘

MySQL 側のログを見ると、where がないクエリが実行されています。

docker container exec  some-mysql sh -c 'tail -n3  /var/lib/mysql/*.log'

2024-04-02T06:45:09.484894Z        25 Query     SELECT `asn`, `country`, `name` FROM `test`.`asn`

2024-04-02T06:45:09.510722Z        25 Query     COMMIT

2024-04-02T06:45:09.511091Z        25 Quit

pushdown（データソース側で絞り込みを行う機能）を有効にすると、where が MySQL の方で実行されます。

# DuckDB側

D set mysql_experimental_filter_pushdown = true;

D select * from mysqldb.asn  where asn = 'AS560' limit 1;

# MySQLのログを見るとwhereが付記されています

docker container exec  some-mysql sh -c 'tail -n3  /var/lib/mysql/*.log'

2024-04-02T06:52:00.941808Z        27 Query     SELECT `asn`, `country`, `name` FROM `test`.`asn` WHERE (`asn` = 'AS560' AND `asn` IS NOT NULL) LIMIT 1

2024-04-02T06:52:00.952084Z        27 Query     COMMIT

2024-04-02T06:52:00.952604Z        27 Quit

ただし、実質的に同じような where 文でも、pushdown が効かない場合もあります。実行前にクエリプランを確認するのが吉なようです。

# （pushdownを有効にして）DuckDB側で=ではなく、inでselectしてみる

D select * from mysqldb.asn  where asn in ('AS560') limit 1;

# MySQL側のログにはwhereがないです

docker container exec  some-mysql sh -c 'tail -n3  /var/lib/mysql/*.log'

2024-04-02T06:55:53.505738Z        29 Query     SELECT `asn`, `country`, `name` FROM `test`.`asn`

2024-04-02T06:55:53.589020Z        29 Query     COMMIT

2024-04-02T06:55:53.590234Z        29 Quit

他ツールではなく DuckDB を使うメリット

今回記載した

  • S3 上の JSON 読み込み
  • Aurora のデータの読み込み

をそれぞれ閲覧するだけであれば、

  • Amazon Athena や一度ダウンロードしてjq
  • 各種データベースのクライアント（MySQL Workbench や DBeaver）

などでも対応可能です。DuckDB で閲覧するメリットとしては、

  • 違う置き場所にあるデータを、組み合わせて閲覧できる
  • Athena のGlue Data Catalog のような事前設定が不要
  • JSON やCSV のスキーマを推測してくれる
  • 一般的な SQL が使える
  • ある程度の大きさのファイルも（数100MB〜GB）処理できる

などがあります。

しくじりポイント

ここまでは「DuckDB 便利だねー」というお話ですが、いくつか躓いたポイントがあったので共有します。

（特にリリースされたばかりの）バージョンには気をつける

SQLite3 は20年以上歴史のある成熟したツールですが、DuckDB はまだまだ新しいツールでバグを踏むこともあります。
例えば、バージョン0.8系ではgzipを読めない（こともある）バグがありました。この件に関しては迅速に（2週間？）バグ修正いただいていますが、活発に機能追加・変更されていることを考えると、

  • バージョンを上げる時は様子をみて行う
  • 代替案も用意しておく
  • ミッションクリティカルな箇所では使わない

を頭に入れておいた方が良さそうです。

S3 のオブジェクト数が多い場合不都合がありがち

体感的には、データサイズが大きい場合（〜数GB・数十GB）では、私の端末（macbook pro 2023）でもリーズナブルな時間（単純なクエリでは数秒・数分）で処理できる場合が多いです。
しかし、S3 上にあり、かつ、処理対象のオブジェクト数が多い場合は、処理に時間がかかる、もしくは処理がエラーで終わる場合もあります。

一度S3のオブジェクトを（AWS CLI などで）ローカルに保存し、そのディレクトリに対してクエリを投げてあげると、S3 上のデータを直接閲覧するよりも時間が短くなる場合がありそうです（ダウンロードの時間を含めたとしても）。

# 約千個のオブジェクトのあるフォルダ

> aws s3 ls  --recursive --no-sign-request 's3://ooni-data-eu-fra/jsonl/whatsapp/JP/' | grep -c jsonl

1229

# 10分待っても終わりませんでした

D select count(1) from read_json_auto('s3://ooni-data-eu-fra/jsonl/whatsapp/JP/**') limit 1;

# ローカルに一度ダウンロード(下コマンド)した同じデータにクエリ

# aws s3 cp --recursive s3://ooni-data-eu-fra/jsonl/whatsapp/JP/ .//ooni

# ダウンロードは43秒かかりました

D select count(1) from read_json_auto('./ooni/**' ) limit1;

┌──────────┐

│ count(1) │

│  int64   │

├──────────┤

│     2842 │

└──────────┘

Run Time (s): real 1.168 user 1.116827 sys 0.191204

スレッドの調整が必要な場合も

ファイル数が多い場合と関係して、一つ一つのファイルが小さい場合でも OOM が発生する時があります。
その場合、同時に実行するスレッド数（Configuration Reference の threads オプション）を小さくしてあげると解決できる場合があります。

Redshift には未対応

PostgreSQL 拡張を使うことで DuckDB を経由して PostgreSQL に接続することができますが、2024/3時点でPostgreSQL 拡張には対応していません（Redshift がサポートしていない PostgreSQL の構文を使用しているため）。

終わりに

今回のブログでは、DuckDB をファイルビューアーとして使えそうという！と紹介させていただきました。Pandas や SQLite3 の代替として、分析用途で紹介されることが多い DuckDB ですが、このような使い道もあることがご参考になれば幸いです。

付録 MySQL のデータを読み込む例の MySQL 側の準備

適当に Docker コンテナで MySQL サーバーを起動します。

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=example  -d -p 3308:3306 mysql:8.3

サンプルデータを持つテーブルとしてvvuksan さんがGitHub で公開しているasnを取り込んだテーブルを作ります。

mysql -u root  -h 127.0.0.1 -P 3308 -pexample  --local-infile=1

mysql> create database test;

mysql> use test;

mysql> create table asn (asn varchar(255), country varchar(2), name varchar(255) );

mysql> load data local infile 'ダウンロードしたasn.csv' into table asn fields terminated by ',';

mysql> set global general_log = on;

We're Hiring!!

テックタッチは、「Maximize the power of tech 〜テックの力を最大化する〜」というミッションを掲げています。誰もがシステムを使いこなせる世界を、私たちと一緒につくりませんか。

採用情報はこちら Techtouch Developers Blog 読者になる
#duckdb #database #infra

toukoudo 読者になる