DuckDBに入門する

最近よく見かけるので気になってきたため、ちょっと触ってみた

その際のメモ

DuckDB

OLAP+インプロセスのRDB

分析用途で用いる、さらっと読んだ感じだとローカルでいろんな形式のデータを組み合わせて分析などを行う際に便利というイメージだった

データ基盤などで、どこにどんなデータがあるからこうしようみたいな調査にも使えそう

インストール

DuckDB Installation – DuckDB

ドキュメント見るとさまざまなパターンでのインストール方法がある

今回はWSLなのでLinux

$ curl -LO https://github.com/duckdb/duckdb/releases/download/v1.0.0/duckdb_cli-linux-amd64.zip

$ unzip duckdb_cli-linux-amd64.zip
Archive: duckdb_cli-linux-amd64.zip  
inflating: duckdb

$ sudo mv duckdb /usr/local/bin/

$ duckdb sample.duckdb  
v1.0.0 1f98600c2c  
Enter ".help" for usage hints.  
D
D .database
sample: sample.duckdb

SQLiteと同様データファイルを指定してコンソールを起動させてみた

$ 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.  
D .database  
memory:

何もファイルを指定しない場合はmemory上で処理してくれるっぽい

インポート色々

CSVはもちろん他にもさまざまな種類のデータを読み込める

さらに、アクセスキーを登録するコマンドも用意されていてあらかじめアクセスキーを用意すればGCSやS3のデータも読める(今回はやってないので次回以降で…)

いくつか試してみた

JSON (Over HTTP)

OVER Httpで試してみた、ローカルファイルももちろん読み込める

ネストしている場合はstructとして読み込んでくれて気が利く

D SELECT id,name,username,company FROM read_json_auto('https://jsonplaceholder.typicode.com/users') LIMIT 10;
┌───────┬──────────────────────────┬──────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  id   │           name           │     username     │                                                           company                                                            │
│ int64 │         varchar          │     varchar      │                                   struct("name" varchar, catchphrase varchar, bs varchar)                                    │
├───────┼──────────────────────────┼──────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│     1 │ Leanne Graham            │ Bret             │ {'name': Romaguera-Crona, 'catchPhrase': Multi-layered client-server neural-net, 'bs': harness real-time e-markets}          │
│     2 │ Ervin Howell             │ Antonette        │ {'name': Deckow-Crist, 'catchPhrase': Proactive didactic contingency, 'bs': synergize scalable supply-chains}                │
│     3 │ Clementine Bauch         │ Samantha         │ {'name': Romaguera-Jacobson, 'catchPhrase': Face to face bifurcated interface, 'bs': e-enable strategic applications}        │
│     4 │ Patricia Lebsack         │ Karianne         │ {'name': Robel-Corkery, 'catchPhrase': Multi-tiered zero tolerance productivity, 'bs': transition cutting-edge web services} │
│     5 │ Chelsey Dietrich         │ Kamren           │ {'name': Keebler LLC, 'catchPhrase': User-centric fault-tolerant solution, 'bs': revolutionize end-to-end systems}           │
│     6 │ Mrs. Dennis Schulist     │ Leopoldo_Corkery │ {'name': Considine-Lockman, 'catchPhrase': Synchronised bottom-line interface, 'bs': e-enable innovative applications}       │
│     7 │ Kurtis Weissnat          │ Elwyn.Skiles     │ {'name': Johns Group, 'catchPhrase': Configurable multimedia task-force, 'bs': generate enterprise e-tailers}                │
│     8 │ Nicholas Runolfsdottir V │ Maxime_Nienow    │ {'name': Abernathy Group, 'catchPhrase': Implemented secondary concept, 'bs': e-enable extensible e-tailers}                 │
│     9 │ Glenna Reichert          │ Delphine         │ {'name': Yost and Sons, 'catchPhrase': Switchable contextually-based project, 'bs': aggregate real-time technologies}        │
│    10 │ Clementina DuBuque       │ Moriah.Stanton   │ {'name': Hoeger LLC, 'catchPhrase': Centralized empowering task-force, 'bs': target end-to-end models}                       │
├───────┴──────────────────────────┴──────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 10 rows                                                                                                                                                                  4 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

ndjson

さらに、ndjsonでも配列の状態でもよしなに読み込んでくれるよう

ndjson

{"a": 1}
{"a": 2}
{"a": 3}

json array

[
  {"a": 1},
  {"a": 2},
  {"a": 3}
]

両方結果は下記

D SELECT *
  FROM read_json_auto('/home/user/sample.json');
┌───────┐
│   a   │
│ int64 │
├───────┤
│     1 │
│     2 │
│     3 │
└───────┘

複数データを読み込んでJOINしてみる

CTASでusers,postsテーブルを作成

CREATE TABLE users AS SELECT * FROM read_json_auto('https://jsonplaceholder.typicode.com/users');
CREATE TABLE posts AS SELECT * FROM read_json_auto('https://jsonplaceholder.typicode.com/posts');

D select p.userId,u.id,p.id,u.name,p.title from users u left join posts p on(u.id = p.userId) LIMIT 20;
┌────────┬───────┬───────┬───────────────┬────────────────────────────────────────────────────────────────────────────┐
│ userId │  id   │  id   │     name      │                                   title                                    │
│ int64  │ int64 │ int64 │    varchar    │                                  varchar                                   │
├────────┼───────┼───────┼───────────────┼────────────────────────────────────────────────────────────────────────────┤
│      1 │     1 │     1 │ Leanne Graham │ sunt aut facere repellat provident occaecati excepturi optio reprehenderit │
│      1 │     1 │     2 │ Leanne Graham │ qui est esse                                                               │
│      1 │     1 │     3 │ Leanne Graham │ ea molestias quasi exercitationem repellat qui ipsa sit aut                │
│      1 │     1 │     4 │ Leanne Graham │ eum et est occaecati                                                       │
│      1 │     1 │     5 │ Leanne Graham │ nesciunt quas odio                                                         │
│      1 │     1 │     6 │ Leanne Graham │ dolorem eum magni eos aperiam quia                                         │
│      1 │     1 │     7 │ Leanne Graham │ magnam facilis autem                                                       │
│      1 │     1 │     8 │ Leanne Graham │ dolorem dolore est ipsam                                                   │
│      1 │     1 │     9 │ Leanne Graham │ nesciunt iure omnis dolorem tempora et accusantium                         │
│      1 │     1 │    10 │ Leanne Graham │ optio molestias id quia eum                                                │
│      2 │     2 │    11 │ Ervin Howell  │ et ea vero quia laudantium autem                                           │
│      2 │     2 │    12 │ Ervin Howell  │ in quibusdam tempore odit est dolorem                                      │
│      2 │     2 │    13 │ Ervin Howell  │ dolorum ut in voluptas mollitia et saepe quo animi                         │
│      2 │     2 │    14 │ Ervin Howell  │ voluptatem eligendi optio                                                  │
│      2 │     2 │    15 │ Ervin Howell  │ eveniet quod temporibus                                                    │
│      2 │     2 │    16 │ Ervin Howell  │ sint suscipit perspiciatis velit dolorum rerum ipsa laboriosam odio        │
│      2 │     2 │    17 │ Ervin Howell  │ fugit voluptas sed molestias voluptatem provident                          │
│      2 │     2 │    18 │ Ervin Howell  │ voluptate et itaque vero tempora molestiae                                 │
│      2 │     2 │    19 │ Ervin Howell  │ adipisci placeat illum aut reiciendis qui                                  │
│      2 │     2 │    20 │ Ervin Howell  │ doloribus ad provident suscipit at                                         │
├────────┴───────┴───────┴───────────────┴────────────────────────────────────────────────────────────────────────────┤
│ 20 rows                                                                                                   5 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

EXCEL

Excel Import – DuckDB

SELECT * FROM st_read('/mnt/c/Users/user/sample.xlsx', layer='sheet1');  
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;

EXCELファイルの読み込みはプラグインが必要

インストールしたら読み込めた

Parquet

サンプル用にCSV→Parquetの簡単な変換用コードを用意して試した

csv2parquet.py

import pandas as pd
import sys

def csv_to_parquet(input_file, output_file):
    # CSVファイルの読み込み
    df = pd.read_csv(input_file)
    # Parquetファイルへの書き込み
    df.to_parquet(output_file, engine='pyarrow')

def main():
    if len(sys.argv) != 3:
        print("Usage: python convert.py <input_file> <output_file>")
        sys.exit(1)
    
    input_file = sys.argv[1]
    output_file = sys.argv[2]
    
    csv_to_parquet(input_file, output_file)

if __name__ == "__main__":
    main()

jsonからCSV、CSVからParquetへ

$ python csv2parquet.py users_1.csv users_1.parquet

読み込み

D SELECT * FROM read_parquet('/home/user/users_1.parquet');  
┌───────┬─────────┬───────┬─────────────────────┐  
│ id │ name │ age │ email │  
│ int64 │ varchar │ int64 │ varchar │  
├───────┼─────────┼───────┼─────────────────────┤  
│ 1 │ Alice │ 30 │ alice@example.com │  
│ 2 │ Bob │ 25 │ bob@example.com │  
│ 3 │ Charlie │ 35 │ charlie@example.com │  
└───────┴─────────┴───────┴─────────────────────┘

複数ファイル指定

*で複数の複数のファイルを対象に読み込むことも可能

D SELECT * FROM read_json('/home/user/users_1.json');
┌───────┬─────────┬───────┬─────────────────────┐
│  id   │  name   │  age  │        email        │
│ int64 │ varchar │ int64 │       varchar       │
├───────┼─────────┼───────┼─────────────────────┤
│     1 │ Alice   │    30 │ alice@example.com   │
│     2 │ Bob     │    25 │ bob@example.com     │
│     3 │ Charlie │    35 │ charlie@example.com │
└───────┴─────────┴───────┴─────────────────────┘
D SELECT * FROM read_json('/home/user/users_2.json');
┌───────┬─────────┬───────┬───────────────────┐
│  id   │  name   │  age  │       email       │
│ int64 │ varchar │ int64 │      varchar      │
├───────┼─────────┼───────┼───────────────────┤
│   101 │ David   │    40 │ david@example.com │
│   102 │ Eva     │    28 │ eva@example.com   │
│   103 │ Frank   │    33 │ frank@example.com │
└───────┴─────────┴───────┴───────────────────┘
D SELECT * FROM read_json('/home/user/users*.json');
┌───────┬─────────┬───────┬─────────────────────┐
│  id   │  name   │  age  │        email        │
│ int64 │ varchar │ int64 │       varchar       │
├───────┼─────────┼───────┼─────────────────────┤
│     1 │ Alice   │    30 │ alice@example.com   │
│     2 │ Bob     │    25 │ bob@example.com     │
│     3 │ Charlie │    35 │ charlie@example.com │
│   101 │ David   │    40 │ david@example.com   │
│   102 │ Eva     │    28 │ eva@example.com     │
│   103 │ Frank   │    33 │ frank@example.com   │
└───────┴─────────┴───────┴─────────────────────┘

これかなり便利だよなー

CLIでの利用

$ cat users_1.json | duckdb -json -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true) WHERE age >= 30"  
[{"id":1,"name":"Alice","age":30,"email":"alice@example.com"},  
{"id":3,"name":"Charlie","age":35,"email":"charlie@example.com"}]

-json で出力フォーマットにJSONを指定

/dev/stdinで標準入力から読み込むことを指示

あとは普通のSQL

ちょっと凝ったフィルタとかは完全にこの手法が楽、jqでやりきるのは結構時間掛かる…

環境変数の値をクエリに含めてみた

$ export AGE=30
$ cat users_1.json | duckdb -echo -line -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true) WHERE age >= $AGE+1"  
SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true) WHERE age >= 30+1  
id = 3  
name = Charlie  
age = 35  
email = charlie@example.com

-echoで、どのようなSQLを実行しているのかっていうのがログに残せる

色々気遣いがあって良き

出力フォーマット

結構種類あった

$ cat users_1.json | duckdb  -ascii -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
idnameageemail1Alice30alice@example.com2Bob25bob@example.com3Charlie35charlie@example.com

$  cat users_1.json | duckdb  -box -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
┌────┬─────────┬─────┬─────────────────────┐
│ id │  name   │ age │        email        │
├────┼─────────┼─────┼─────────────────────┤
│ 1  │ Alice   │ 30  │ alice@example.com   │
│ 2  │ Bob     │ 25  │ bob@example.com     │
│ 3  │ Charlie │ 35  │ charlie@example.com │
└────┴─────────┴─────┴─────────────────────┘

$ cat users_1.json | duckdb  -column -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
id  name     age  email
--  -------  ---  -------------------
1   Alice    30   alice@example.com
2   Bob      25   bob@example.com
3   Charlie  35   charlie@example.com

$ cat users_1.json | duckdb  -csv -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
id,name,age,email
1,Alice,30,alice@example.com
2,Bob,25,bob@example.com
3,Charlie,35,charlie@example.com

$ cat users_1.json | duckdb  -json -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
[{"id":1,"name":"Alice","age":30,"email":"alice@example.com"},
{"id":2,"name":"Bob","age":25,"email":"bob@example.com"},
{"id":3,"name":"Charlie","age":35,"email":"charlie@example.com"}]

$ cat users_1.json | duckdb  -line -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
   id = 1
 name = Alice
  age = 30
email = alice@example.com

   id = 2
 name = Bob
  age = 25
email = bob@example.com

   id = 3
 name = Charlie
  age = 35
email = charlie@example.com

$ cat users_1.json | duckdb  -list -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
id|name|age|email
1|Alice|30|alice@example.com
2|Bob|25|bob@example.com
3|Charlie|35|charlie@example.com

$ cat users_1.json | duckdb  -markdown -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
| id |  name   | age |        email        |
|---:|---------|----:|---------------------|
| 1  | Alice   | 30  | alice@example.com   |
| 2  | Bob     | 25  | bob@example.com     |
| 3  | Charlie | 35  | charlie@example.com |

$ cat users_1.json | duckdb  -quote -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
'id'|'name'|'age'|'email'
1|'Alice'|30|'alice@example.com'
2|'Bob'|25|'bob@example.com'
3|'Charlie'|35|'charlie@example.com'

$ cat users_1.json | duckdb  -html -c "SELECT * FROM read_json_auto('/dev/stdin', ignore_errors=true)"
<TR><TH>id</TH>
<TH>name</TH>
<TH>age</TH>
<TH>email</TH>
</TR>
<TR><TD>1</TD>
<TD>Alice</TD>
<TD>30</TD>
<TD>alice@example.com</TD>
</TR>
<TR><TD>2</TD>
<TD>Bob</TD>
<TD>25</TD>
<TD>bob@example.com</TD>
</TR>
<TR><TD>3</TD>
<TD>Charlie</TD>
<TD>35</TD>
<TD>charlie@example.com</TD>
</TR>