🦆

🦆🦆🦆DuckDBとSQLite・PostgreSQL連携🦆🦆

2022/12/21に公開

Database

SQLite

DuckDB

tech

気にはなってるけど触ってないビッグデータ系のツール・サービスを触る Advent Calendar 2022の#21です。

DuckDBには他のデータベースと連携する機能があるので、いくつか試してみました。

tl;dr

DuckDBからSQLiteとPostgreSQLのデータが読み込めるよ
（多分）PostgreSQLからDuckDBのデータも読み込めるよ

準備

DuckDB（CLI）をダウンロードし、パスが通っている場所に置いておきます。
（私は~/.local/bin/duckdbに置きました）

Ubuntu 20.04 (Windows10のWSL2上)、DuckDBv0.6.1で試しました。

DuckDBとSQLite

SQLite Scannerを使うと、SQLiteのデータベースを読み込むことができます。

準備

まずは、SQLiteのデータベースを作成します。

sudo apt install sqlite

sqlite3 sqlite3.db
sqlite> CREATE TABLE t1(key varchar, not_key varchar);
sqlite> INSERT INTO t1 VALUES ('sqlite_key', 'made in sqlite3');

sqlite3.dbを作成したディレクトリでDuckDBを起動し、SQLite Scannerプラグインのインストール・有効化を行います。

duckdb hoge.db
D INSTALL sqlite;
D LOAD sqlite;

DuckDBからSQLiteのデータを読んでみる

SQLiteのデータベースを読んでみます。

D SELECT * FROM sqlite_scan('sqlite3.db', 't1');
┌────────────┬─────────────────┐
│    key     │     not_key     │
│  varchar   │     varchar     │
├────────────┼─────────────────┤
│ sqlite_key │ made in sqlite3 │
└────────────┴─────────────────┘

読めていますね。SQLiteのデータをDuckDBに保存したり、DuckDBのデータとSQLiteのデータを組み合わせたりも出来ます。

# SQLiteのデータをDucKDBに保存
D CREATE TABLE t1_from_sqlite AS SELECT * FROM sqlite_scan('sqlite3.db', 't1');
# SQLiteのデータとDuckDBのデータを同時に使う
D SELECT * FROM t1_from_sqlite UNION ALL SELECT * FROM sqlite_scan('sqlite3.db', 't1');
┌────────────┬─────────────────┐
│    key     │     not_key     │
│  varchar   │     varchar     │
├────────────┼─────────────────┤
│ sqlite_key │ made in sqlite3 │
│ sqlite_key │ made in sqlite3 │
└────────────┴─────────────────┘

トランザクション

SQLite側でトランザクション中のデータはDuckDB側からは見えません。

sqlite> BEGIN;
sqlite> INSERT INTO t1 VALUES ('sqlite_ke2', 'made in sqlite3');
sqlite>

D SELECT * FROM sqlite_scan('sqlite3.db', 't1');
┌────────────┬─────────────────┐
│    key     │     not_key     │
│  varchar   │     varchar     │
├────────────┼─────────────────┤
│ sqlite_key │ made in sqlite3 │
└────────────┴─────────────────┘

もちろんCommitすると見えるようになります。

sqlite> COMMIT;
sqlite> SELECT * FROM t1;
sqlite_key|made in sqlite3
sqlite_ke2|made in sqlite3

D SELECT * FROM sqlite_scan('sqlite3.db', 't1');
┌────────────┬─────────────────┐
│    key     │     not_key     │
│  varchar   │     varchar     │
├────────────┼─────────────────┤
│ sqlite_key │ made in sqlite3 │
│ sqlite_ke2 │ made in sqlite3 │
└────────────┴─────────────────┘

（多分）出来ないこと

以下の二つは試したらエラーになりました。多分対応していない？

SQLite Scannerと名前にあることからも推測できますが、SQLiteのデータの読みとりのみで、データの変更はできないようです
SQLite2系のファイルには対応していないようです

D UPDATE sqlite_scan('sqlite3.db', 't1') set not_key = 'hogehoge' WHERE key = 'sqlite_key';
Error: Parser Error: syntax error at or near "("
LINE 1: UPDATE sqlite_scan('sqlite3.db', 't1') set not_key = 'hog...

# sqlite3ではなく、sqlite2系のデータベースを作ってみる
sqlite sqlite.db
CREATE TABLE t1(key varchar, not_key varchar);
INSERT INTO t1 VALUES ('common_key','common');

# DuckDBからのアクセスが失敗
D SELECT * FROM sqlite_scan('sqlite.db', 't1');
Error: Invalid Error: file is not a database

DuckDBとPostgreSQL

SQLiteと同様に、Postgres Scannerを使うとPostgreSQLのテーブルを読み込むことができます。

準備

PostgreSQLのデータベースを用意します。今回はコンテナイメージを使いますので、docker-composeファイルを記載します。

version: '3.1'

services:
  db:
    image: postgres:15.1
    restart: always
    environment:
      POSTGRES_PASSWORD: admin
    ports:
      - 5433:5432
    volumes:
      - ./postgresql:/var/lib/postgresql/data
    command: ["postgres", "-c", "log_statement=all"]

  # 動作確認用のツール。無くても動くはず
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

後々動作確認しやすくするために、log_statement=Trueしています。

コンテナを起動し、SQLiteと同様にテーブルを作成します。

docker-compose up -d
psql -h 127.0.0.1 -p 5433 -U postgres
postgres=# CREATE TABLE t1(key varchar, not_key varchar);
postgres=# INSERT INTO t1 VALUES ('common_key', 'common'), ('changed_key', 'changed'), ('inserted_key', 'inserted');

DuckDB側でPostgres Scannerプラグインのインストール・有効化を行っておきます。

D INSTALL postgres_scanner;
D LOAD postgres_scanner;

DuckDBからPostgreSQLのデータを読んでみる

PostgreSQLのデータベースを読んでみます。

D SELECT * FROM postgres_scan_pushdown('postgresql://postgres:admin@localhost:5433/postgres', 'public', 't1');
┌──────────────┬──────────┐
│     key      │ not_key  │
│   varchar    │ varchar  │
├──────────────┼──────────┤
│ common_key   │ common   │
│ changed_key  │ changed  │
│ inserted_key │ inserted │
└──────────────┴──────────┘

（docker logsで）PostgreSQLのログを見ると、リクエストが来ていることを確認できます。

2022-12-18 13:12:13.268 UTC [76] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY
2022-12-18 13:12:13.269 UTC [76] LOG:  statement: SELECT pg_is_in_recovery()
2022-12-18 13:12:13.269 UTC [76] LOG:  statement:
        SELECT pg_class.oid, GREATEST(relpages, 1)
        FROM pg_class JOIN pg_namespace ON relnamespace = pg_namespace.oid
        WHERE nspname='public' AND relname='t1'

2022-12-18 13:12:13.270 UTC [76] LOG:  statement:
        SELECT
            attname, atttypmod,
            pg_type.typname, pg_type.typlen, pg_type.typtype, pg_type.typelem,
            pg_type_elem.typname elem_typname, pg_type_elem.typlen elem_typlen, pg_type_elem.typtype elem_typtype
        FROM pg_attribute
            JOIN pg_type ON atttypid=pg_type.oid
            LEFT JOIN pg_type pg_type_elem ON pg_type.typelem=pg_type_elem.oid
        WHERE attrelid=16389 AND attnum > 0
        ORDER BY attnum;

2022-12-18 13:12:13.276 UTC [77] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY
2022-12-18 13:12:13.276 UTC [77] LOG:  statement:
        COPY (SELECT "key", "not_key" FROM "public"."t1" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid ) TO STDOUT (FORMAT binary);

なお、DuckDBのPostgreSQL連携ではBinary Transfer Modeというのを使っているらしいです（PostgreSQLのCOPY）。詳しくDuckDBのブログを参照してください。

pushdown・projection

SELECT文の、カラム指定・WHERE文はDuckDB側ではなく、PostgreSQL側で動作させる（pushdown）ことが可能です。

D SELECT key FROM postgres_scan_pushdown('postgresql://postgres:admin@localhost:5433/postgres', 'public', 't1') WHERE key = 'common_key';
┌────────────┐
│    key     │
│  varchar   │
├────────────┤
│ common_key │
└────────────┘

        COPY (SELECT "key", "not_key" FROM "public"."t1" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid  AND ("key" = 'common_key' AND "key" IS NOT NULL)) TO STDOUT (FORMAT binary);

なお、WHEREのpushdownは行わない（＝DuckDB側で絞り込み）も可能です。

D SELECT key FROM postgres_scan('postgresql://postgres:admin@localhost:5433/postgres', 'public', 't1') WHERE key = 'common_key';
┌────────────┐
│    key     │
│  varchar   │
├────────────┤
│ common_key │
└────────────┘

先ほどと違い、PostgreSQL側のログにWHERE文がありませんね。

        COPY (SELECT "key" FROM "public"."t1" WHERE ctid BETWEEN '(0,0)'::tid AND '(4294967295,0)'::tid ) TO STDOUT (FORMAT binary);

PostgreSQLからDuckDB

duckdb_fdwというPostgreSQLの拡張で可能そうです。

（力尽きました）