DuckDB雑紹介 - Speaker Deck

Slide 1

Slide 1 text

　DuckDB雑紹介

Slide 2

Slide 2 text

祝1.0リリース 2024-06-03 にバージョン1.0がリリースされました DuckDB in Actionでは秋頃と書かれてたが思った以上に早かった 2/57

Slide 3

Slide 3 text

特徴 3/57

Slide 4

Slide 4 text

特徴インメモリ、組み込み、カラムナデータベースそれ自体が静的ライブラリデータ分析のようなOLAPでの使用が主戦場永続化もサポート主だったライバルは、 Snowflake や ClickHouse あたり？ https://duckdb.org 4/57

Slide 5

Slide 5 text

特徴いくつかの言語バインディングが標準で提供 C/C++ Rust JS/TS WASM などなど https://duckdb.org/docs/api/overview 5/57

Slide 6

Slide 6 text

特徴多彩な外部データ取り込み CSV JSON Apache Parquet PostgrSQL (外部スキーマとしてアタッチ) Wired protocolで直結でWriteも可能などなど https://duckdb.org/docs/data/overview 6/57

Slide 7

Slide 7 text

特徴豊富な機能拡張 (プラグイン) 先の外部データ取り込みもビルトインされた機能拡張として提供されている https://duckdb.org/docs/extensions/overview#working-with- extensions 7/57

Slide 8

Slide 8 text

特徴 Live DEMOも公式で提供 (WASM版が) https://duckdb.org/docs/ メニューの一番下のリンクから 8/57

Slide 9

Slide 9 text

構文 9/57

Slide 10

Slide 10 text

構文 PostgreSQL のパーザがベース大体同じことができる標準SQL ( SQL:2008 あたり) 10/57

Slide 11

Slide 11 text

構文データ型だいたい PostgreSQL と同じ Enum や PostgreSQL 式の型キャスト( :: )も使える https://duckdb.org/docs/sql/data_types/overview 11/57

Slide 12

Slide 12 text

構文 CTEの式内materialization たぶん PostgreSQL 独自機能 materialize の強制 or 解除 WITH v AS NOT MATERIALIZE (...) SELECT * v https://www.postgresql.jp/document/16/html/queries- with.html#QUERIES-WITH-CTE-MATERIALIZATION 12/57

Slide 13

Slide 13 text

構文 CTEの式内materialization CTE内で別のCTE使うケース DBエンジンが気を効かせてメモリへのキャッシュを試みる、レコード数に関係なく！キャッシュがファイルストレージにパージされて遅くなる要因 materialize解除で現実的な速度に改善できる 13/57

Slide 14

Slide 14 text

構文 LATERAL JOIN 公式だと直積での説明しかなくて残念相関サブクエリの JOIN もちゃんとできるので安心 SELECT * FROM t1 JOIN LATERAL (...) t2 ON true https://duckdb.org/docs/sql/query_syntax/from#lateral-joins ちなみに LATERAL は飾りでしかなく、相関サブクエリの JOIN はなくてもできる 14/57

Slide 15

Slide 15 text

構文 Window関数もちのろん、Windowフレームも使えるただし、 Row と Range のみ Group は使えない (使えるの PostgreSQL くらいだけど・・・) https://duckdb.org/docs/sql/window_functions#framing 特にWindowフレームの説明がわかりやすいので一見の価値あり 15/57

Slide 16

Slide 16 text

構文配列、リスト、JSONのunnest ずっと独自機能だと思ってたけど、 SQL:2008 に記載あったわ PostgreSQL でも古くからサポートされている (使ったことなかった) 引用: https://docs.vmware.com/en/VMware- Greenplum/7/greenplum-database/ref_guide- SQL2008_support.html 16/57

Slide 17

Slide 17 text

構文配列、リスト、JSONのunnest DuckDBでは、読み込んだ JSON 構造フラット化できる割と使用頻度高め https://duckdb.org/docs/sql/query_syntax/unnest.html 17/57

Slide 18

Slide 18 text

独自機能 18/57

Slide 19

Slide 19 text

SELECT文の独自機能 - JOIN拡張 ASOF JOIN 直近のレコードと結合する Snowflake や ClickHouse でも使える OLAP系だとド定番？ 19/57

Slide 20

Slide 20 text

SELECT文の独自機能 - JOIN拡張 ASOF JOIN 用途としては、指定日以前・以後の直近のログデータをもってくるとか SELECT t1.some_date, ... FROM t1 ASOF JOIN t2 ON t1 t1.some_date > t2.some_date https://duckdb.org/docs/sql/query_syntax/from#as-of-joins 20/57

Slide 21

Slide 21 text

SELECT文の独自機能 - JOIN拡張 ASOF JOIN 標準SQLで書くとスッゲーめんどいやつ SELECT t1.some_date, ... WHERE t1.some_date = ( SELECT max(t2.some_date) FROM t2 WHERE t2.some_date < t1.some_date ) 21/57

Slide 22

Slide 22 text

SELECT文の独自機能 - JOIN拡張 POSITIONAL JOIN 2つのデータソースの同一行で結合する欠損行は NULL に実体は FULL OUTER JOIN なので zip演算を思い浮かべるとわかりやすいかも SELECT * FROM t1 POSITIONAL JOIN t2 https://duckdb.org/docs/sql/query_syntax/from#positional-joins 22/57

Slide 23

Slide 23 text

SELECT文の独自機能 - 集約関数ソート可能な集約関数 CREATE TABLE tbl AS SELECT s FROM range(1, 4) r(s); SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown FROM tbl PostgreSQLの「順序集合集約関数」に似てるけど別物っぽい DuckDBも「順序集合集約関数」をサポートしているわけで https://duckdb.org/docs/sql/aggregates#order-by-clause-in- aggregate-functions 23/57

Slide 24

Slide 24 text

SELECT文の独自機能 - 集約関数配列・リスト型用の関数でラムダ式が使えるさらにメソッドチェインもできる SELECT [1, 2, 3].apply(x -> [4, 5, 6].reduce((a, b) -> a + b) + x) https://duckdb.org/docs/sql/functions/lambda ただしメソッドチェインは、配列・リスト型用の関数のみ TOUPPER とか他の関数はできなかった。残念！ 24/57

Slide 25

Slide 25 text

SELECT文の独自機能 - スター拡張 SELECT句の * と組み合わせて記述する記法 25/57

Slide 26

Slide 26 text

SELECT文の独自機能 - スター拡張 COLUMNS(*) * の指定で、全ての列にまとめて同じ関数を適用できる CREATE TABLE numbers (id INTEGER, number INTEGER); INSERT INTO numbers VALUES (1, 10), (2, 20), (3, NULL); /* 4列帰ってくる */ SELECT min(COLUMNS(*)), count(COLUMNS(*)) FROM numbers; https://duckdb.org/docs/sql/expressions/star#columns-expression 26/57

Slide 27

Slide 27 text

SELECT文の独自機能 - スター拡張 COLUMNS(<パターン>) 正規表現による列名のパターンマッチラムダ式も使える CREATE TABLE t (yobi001 VARCHAR, ..., yobi256 VARCHAR); SELECT COLUMNS( c -> substr(c, 5)::int BETWEEN 10 AND 20) FROM t; https://duckdb.org/docs/sql/expressions/star#columns-expression 27/57

Slide 28

Slide 28 text

SELECT文の独自機能 - スター拡張 EXCLUDE(<列名(複数可)>) 特定列だけ除外不要な1列のために全部列挙しなくてもいいチョー便利 SELECT * EXCLUDE(foo, bar) FROM t; https://duckdb.org/docs/sql/expressions/star#exclude-clause 28/57

Slide 29

Slide 29 text

SELECT文の独自機能 - スター拡張 REPLACE(<式 AS 列名>) 特定列だけ変更できる INSERT-SELECT で大活躍の予感 INSERT INTO t2 SELECT * REPLACE(id*100 AS id) FROM t1 https://duckdb.org/docs/sql/expressions/star 29/57

Slide 30

Slide 30 text

SELECT文の独自機能 - ファイルインポート 30/57

Slide 31

Slide 31 text

SELECT文の独自機能 - ファイルインポート CSV、JSON等をパス指定でそのまま SELECT できるただし拡張子がある場合のみ WEBサーバーからURLでダイレクト取得も SELECT * FROM 'https://example.com/nanchara-kanchara.json' https://duckdb.org/docs/data/json/overview 31/57

Slide 32

Slide 32 text

SELECT文の独自機能 - ファイルインポート拡張子がない場合でも、専用関数を使えばOK SELECT * FROM read_json_auto('https://example.com/nanchara-kanchara') CSV や Parquet も専用関数が用意されている https://duckdb.org/docs/data/overview 32/57

Slide 33

Slide 33 text

SELECT文の独自機能 - ファイルインポート glob で一括指定することもできる SELECT * FROM 'https://example.com/nanchara-kanchara-*.json' 同じフォーマットにする必要はあるだろうけど https://duckdb.org/docs/data/multiple_files/overview 33/57

Slide 34

Slide 34 text

SELECT文の独自機能 - from first syntax FROM句から先に書ける。ただし・・・ FROM + SELECT or FROM + WHERE のみもしくは FROM のみで全列選択 FROM WHERE SELECT は書けない残念な子 /* FROMのみ (SELECT * FROM foo と同等) */ FROM foo; /* FROM WHEREの例 */ SELECT EXISTS (FROM foo WHERE id > 5); https://duckdb.org/docs/sql/query_syntax/from#from-first-syntax 34/57

Slide 35

Slide 35 text

SELECT文の独自機能 - GROUP BY GROUP BY ALL 集約関数を適用した列以外でグループ化 SELECT a, b, max(c), count(c) FROM t GROUP BY ALL https://duckdb.org/docs/sql/query_syntax/groupby#group-by-all 35/57

Slide 36

Slide 36 text

SELECT文の独自機能 - ORDER BY ORDER BY ALL SELECT句のすべてのカラム一括指定 ASC / DESCが揃ってる場合は便利かも SELECT A1, A2, A3 FROM t ORDER BY ALL https://duckdb.org/docs/sql/query_syntax/orderby#order-by-all 36/57

Slide 37

Slide 37 text

SELECT文の独自機能 - SAMPLE サンプリングレートを指定して、データをピックアップ分析界隈では重宝される？ /* 一例。他にもオプションが提供されている */ SELECT * FROM addresses USING SAMPLE 1% https://duckdb.org/docs/sql/query_syntax/sample.html 37/57

Slide 38

Slide 38 text

SELECT文の独自機能 - その他ケツカンマのサポート頭カンマじゃなきゃ順序入れ替えが不便とかの不毛な論争ともおさらば SELECT 10, 'abc', FROM t https://duckdb.org/docs/guides/sql_features/friendly_sql.html#trailin g-commas 38/57

Slide 39

Slide 39 text

INSERT文の独自機能 39/57

Slide 40

Slide 40 text

INSERT文の独自機能 - Upsert INSERT OR REPLACE SQLiteで提供されてるやつなかったら INSERT あったら UPDATE PostgreSQLの ON CONFLICT DO も使えるがより直感的 INSERT OR REPLACE INTO movies VALUES (1, 'The Phantom Menace'); https://duckdb.org/2023/02/13/announcing-duckdb-070.html#new- sql-features 40/57

Slide 41

Slide 41 text

INSERT文の独自機能 - BY NAME INSERT SELECT で、いちいち位置を合わせなくても列名でマッチングしてくれる CREATE TABLE tbl (a INTEGER, b INTEGER); INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a); https://duckdb.org/docs/sql/statements/insert#insert-into--by-name 41/57

Slide 42

Slide 42 text

独自STATEMENT 42/57

Slide 43

Slide 43 text

独自STATEMENT - DESCRIBE TABLEやSELECT結果の型を出力もう information schema を漁らなくても良い DESCRIBE SELECT a1, b1, c1 FROM t https://duckdb.org/docs/sql/statements/describe 43/57

Slide 44

Slide 44 text

独自STATEMENT - SUMMARIZE テーブルや SELECT 結果の最大値や最小値、 NULL 数等をレポート結果が、あっれれ〜おっかし〜なって時の調査に役立つかも SUMMARIZE SELECT a1, b1, c1 FROM t https://duckdb.org/docs/sql/statements/summarize 44/57

Slide 45

Slide 45 text

独自STATEMENT - PIVOT / UNPIVOT 行と列の転置文として提供されている大抵のRDBは関数として提供 PIVOT Cities ON Year USING sum(Population); PIVOT: https://duckdb.org/docs/sql/statements/pivot UNPIVOT: https://duckdb.org/docs/sql/statements/unpivot 45/57

Slide 46

Slide 46 text

DBメンテナンスツール標準パッケージに、CLI版が含まれている JDBCが提供されているので、GUIなツールからもアクセスできる DBeaver については公式でも案内あり https://duckdb.org/docs/guides/sql_editors/dbeaver 46/57

Slide 47

Slide 47 text

できなかったこと 47/57

Slide 48

Slide 48 text

できなかったこと ALTER TABLEで主キーの変更 CTAS で作って、後付けできないキーありにしたければ、 CREATE TABLE + INSERT 48/57

Slide 49

Slide 49 text

できなかったことテーブル別名の付与したときのBY NAMEでのINSERT SELECT 句で別名を付与すると、エイリアス込みの列名を探そうとしてエラー /* a.foo AS fooとすれば回避可能 */ INSERT INTO t (foo) SELECT a.foo FROM table_a a 49/57

Slide 50

Slide 50 text

できなかったことスカラ問い合わせ(サブクエリ)が、うっかり複数行返す場合、先頭行返すエラーにして欲しかった・・・ SELECT (SELECT * FROM generate_series(1,10)) 50/57

Slide 51

Slide 51 text

できなかったことネストした Derived Table で複数回、 unnest するとデータが欠損先頭行のみもってくる SELECT * FROM ( SELECT unnest(bar) FROM ( SELECT unnest(foo) FROM t ) ) 遭遇したのはデータソースとして JSON を使った時テンポラリテーブルで、分けて対処した 51/57

Slide 52

Slide 52 text

できなかったこと - JS/TSバインディング 52/57

Slide 53

Slide 53 text

できなかったこと - JS/TSバインディング SQL実行APIで、 Array や Object をパラメータとして使えない渡せるのは、数値、文字列、 boolean 、 NULL のみ上記以外は、「Invalid column type encountered for argument」ってエラーになる IN句や ANY句に渡す場合は展開する必要あり CLI ではできるのでバインディングの制限か？ 53/57

Slide 54

Slide 54 text

できなかったこと - JS/TSバインディング SQL実行APIで、名前付きパラメータが使えない位置パラメータのみ SQLのプレースホルダの順番変えたら地獄 54/57

Slide 55

Slide 55 text

できなかったこと - JS/TSバインディンインポートAPI使ったら、文字列->Enum自動変換してくれなかった APIによるSQLの直接発行では、自動変換してくれる 0.10.1の時のはなし今はしてくれるかも・・・（未確認） 55/57

Slide 56

Slide 56 text

以上、DuckDBの駆け足紹介でしたみんなも使おう使え！ (豹変) 56/57

Slide 57

Slide 57 text

おわりあばうとみー X(旧ﾄｩｲｯﾀ) - https://x.com/ktz_alias Qiita - https://qiita.com/ktz_alias Zenn - https://zenn.dev/ktz_alias ｷﾞｯﾊﾌﾞ - https://github.com/ritalin 57/57