Closed24
DuckDBでセマンティック検索を試す
kun432公式
ポイントだけDeepL日本語訳
DuckDB は、高速な分析用/インプロセス/オープンソース/ポータブルなデータベースシステムです。
DuckDB の機能豊富な SQL 方言を使用して、どこからでもデータをクエリおよび変換できます。
DuckDB の概要
- シンプル
DuckDB はインストールと導入が簡単です。外部依存関係がなく、ホストアプリケーション内でプロセスとして実行するか、単一のバイナリとして実行できます。- ポータブル
DuckDB は、Linux、macOS、Windows、Android、iOS、およびすべての主要なハードウェアアーキテクチャで動作します。主要なプログラミング言語用のイディオムに準拠したクライアント API を提供しています。- 機能豊富
DuckDB は豊富な SQL 方言を提供します。CSV、Parquet、JSON などのファイル形式を、ローカルファイルシステムや S3 バケットなどのリモートエンドポイントとの間で読み書きできます。- 高速
DuckDB は、並列実行をサポートし、メモリを超えるワークロードを処理できるカラム型エンジンにより、分析クエリを高速に実行します。- 拡張性
DuckDB は、新しいデータ型、関数、ファイル形式、新しい SQL 構文などのサードパーティ機能によって拡張可能です。ユーザーによる貢献は、コミュニティ拡張機能として利用可能です。- 自由
DuckDB およびそのコア拡張機能は、寛容な MIT ライセンスの下でオープンソースとして提供されています。プロジェクトの知的財産権は DuckDB 財団が保有しています。
詳細は以下にある
データベース管理システム（DBMS）は数多く存在します。しかし、すべての用途に適合する万能のデータベースシステムは存在しません。すべてのシステムは、特定の用途に最適化するために異なるトレードオフを採用しています。DuckDBも例外ではありません。ここでは、DuckDBが目指す目標とは何か、なぜその目標を追求するのか、そして技術的な手段を通じてその目標を達成しようとする方法を説明します。まず、DuckDBは構造化クエリ言語（SQL）をサポートするリレーショナル（テーブル指向）なDBMSです。
GitHubレポジトリ
kun432今回はDuckDBを使ったセマンティック検索について知ることが目的だが、一通りQuick Start的なところは試してからセマンティック検索を試す予定。
kun432インストール
利用形態は概ね以下の通り
- CLI
- 各言語のパッケージ
- ODBCドライバ経由
今回は、自分がよく利用する、CLIとPythonパッケージで試してみようと思う。
kun432CLI
インストール
手元のMacで試す。Macの場合は、
- Homebrew
- curlでワンライナー
- 実行ファイルのダウンロード
の3つがあるが、今回は実行ファイルのダウンロードで。
作業ディレクトリ作成
mkdir duckdb-work && cd duckdb-work
アーカイブをダウンロードして展開
wget https://github.com/duckdb/duckdb/releases/download/v1.3.1/duckdb_cli-osx-universal.zip
unzip duckdb_cli-osx-universal.zip
CLIの実行ファイルが展開される
ls -l duckdb
出力
-rwxr-xr-x@ 1 kun432 staff 105196016 6 16 17:36 duckdb
file duckdb
出力
duckdb: Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
duckdb (for architecture x86_64): Mach-O 64-bit executable x86_64
duckdb (for architecture arm64): Mach-O 64-bit executable arm64
アーカイブは削除
rm duckdb_cli-osx-universal.zip
バージョン確認
./duckdb --version
出力
v1.3.1 (Ossivalis) 2063dda3e6
Usage確認
./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:
-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
-f FILENAME read/process named file and exit
-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'
-no-stdin exit after processing options instead of reading stdin
-nullvalue TEXT set text string for NULL values. Default 'NULL'
-quote set output mode to 'quote'
-readonly open the database read-only
-s COMMAND run "COMMAND" and exit
-safe enable safe-mode
-separator SEP set output column separator. Default: '|'
-table set output mode to 'table'
-ui launches a web interface using the ui extension (configurable with .ui_command)
-unredacted allow printing unredacted secrets
-unsigned allow loading of unsigned extensions
-version show DuckDB version
CLIチュートリアル
CLIのドキュメントは以下にあるのだけど
公式ブログにCLIのチュートリアルがあるみたい。これをやってみる。
準備
チュートリアル用のファイルがGitHubレポジトリに用意してあるので、それをクローン。
git clone https://github.com/mehd-io/duckdb-tutorial-for-beginner
# 中味だけほしいので移動して元のディレクトリは削除
mv duckdb-tutorial-for-beginner/* .
rm -rn duckdb-tutorial-for-beginner
ディレクトリ構成はこんな感じになっているはず
tree
出力
.
├── README.md
├── data
│ ├── netflix_daily_top_10.csv
│ ├── netflix_daily_top_10.parquet
│ └── sales.json
├── duckdb
├── duckdb_for_beginner.sql
└── examples
├── extensions.sql
├── json_parsing.sql
└── netflix_playground.sql
3 directories, 9 files
VSCodeを使った設定なども紹介されているが今回はスキップ。
データベースの作成
duckdbの起動はシンプルにコマンドを実行するだけ。
./duckdb
プロンプトの先頭がD なのね。
出力
DuckDB v1.3.1 (Ossivalis) 2063dda3e6
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
ただし、デフォルトではDuckDBはインメモリで動作し、データは永続化されない。試しにテーブルを作成するSQLを実行してみる。
CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;
テーブルを見てみる。FROM テーブル名みたいなショートカットが用意されている。
FROM ducks;
出力
┌───────┬──────────┐
│ age │ breed │
│ int32 │ varchar │
├───────┼──────────┤
│ 3 │ mandarin │
└───────┴──────────┘
CLIを終了してみる。終了はctrl+c x 2回 / ctrl+d、もしくは .exit と入力すれば終了する
.quit
再度CLIを開いてテーブルを見てみる。
./duckdb
FROM ducks;
テーブルが存在しないというエラーになる。
出力
Catalog Error:
Table with name ducks does not exist!
Did you mean "duckdb_schemas"?
LINE 1: FROM ducks;
^
データベースを永続化する場合は以下の2つ
- データベースファイルパスを指定してduckdbを起動
-
attachコマンドでファイルパスを指定してデータベースに接続する。
まずデータベースファイルパスを指定する方法。指定したパスにデータベースファイルが存在しない場合は新たに作成される様子。
./duckdb ./mydatabase.db
先ほどのSQLを実行
CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;
一旦終了して再度起動し、テーブルを確認
.quit
./duckdb ./mydatabase.db
FROM ducks;
先ほど作成したテーブルとデータが確認できる。
出力
┌───────┬──────────┐
│ age │ breed │
│ int32 │ varchar │
├───────┼──────────┤
│ 3 │ mandarin │
└───────┴──────────┘
またシェルからも以下のようにデータベースファイルが作成されているのがわかる。
ls -lt
出力
-rw-r--r--@ 1 kun432 staff 536576 6 20 19:26 mydatabase.db
もう一つの方法。まずは普通に起動。
./duckdb
データベースファイルパスを指定してアタッチする。
ATTACH DATABASE './mydatabase2.db' AS mydb;
SQLを実行、この時、上で指定したデータベースのエイリアスを指定しておく必要がある。
CREATE TABLE mydb.ducks AS SELECT 3 AS age, 'mandarin' AS breed;
一旦終了。先ほど作成したデータベースパスを指定して再度duckdbを起動。
.quit
./duckdb ./mydatabase2.db
先ほど作成したテーブルを見てみる。
FROM ducks;
出力
┌───────┬──────────┐
│ age │ breed │
│ int32 │ varchar │
├───────┼──────────┤
│ 3 │ mandarin │
└───────┴──────────┘
もちろんファイルも作成されている。
ls -lt
出力
-rw-r--r--@ 1 kun432 staff 536576 6 20 19:40 mydatabase2.db
-rw-r--r--@ 1 kun432 staff 536576 6 20 19:26 mydatabase.db
ATTACHコマンドは、データベースの永続化とかってことよりも、オンデマンドにデータベースに接続できるみたいな感じのコマンドだよね。
kun432データの読み込み・表示
DuckDBでは、CSVやParquetなどのデータを直接読み込むことができる。チュートリアルのレポジトリ内のデータを読み込んでみる。
新しいデータベースファイルを指定してduckdb起動
./duckdb sample.db
CSVの場合はread_csv_auto、またはread_csvを使う。後者はスキーマやセパレータを指定できるみたい。
SELECT * FROM read_csv_auto('./data/netflix_daily_top_10.csv');
出力
┌────────────┬───────┬───────────────────┬───┬──────────────────────┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ … │ Netflix Release Date │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ │ varchar │ int64 │ int64 │
├────────────┼───────┼───────────────────┼───┼──────────────────────┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ … │ Mar 20, 2020 │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ … │ Jul 21, 2017 │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ … │ Mar 28, 2019 │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ … │ Mar 26, 2020 │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ … │ Mar 20, 2020 │ 9 │ 55 │
│ 2020-04-01 │ 6 │ 6 │ … │ Sep 14, 2018 │ 4 │ 14 │
│ 2020-04-01 │ 7 │ 10 │ … │ Mar 26, 2020 │ 2 │ 5 │
│ 2020-04-01 │ 8 │ 7 │ … │ Feb 13, 2020 │ 9 │ 40 │
│ 2020-04-01 │ 9 │ 8 │ … │ Mar 26, 2020 │ 4 │ 11 │
│ 2020-04-01 │ 10 │ 9 │ … │ Mar 27, 2020 │ 4 │ 15 │
│ 2020-04-02 │ 1 │ 1 │ … │ Mar 20, 2020 │ 10 │ 100 │
│ 2020-04-02 │ 2 │ 2 │ … │ Jul 21, 2017 │ 6 │ 54 │
│ 2020-04-02 │ 3 │ 3 │ … │ Mar 28, 2019 │ 10 │ 84 │
│ 2020-04-02 │ 4 │ - │ … │ Mar 9, 2018 │ 1 │ 7 │
│ 2020-04-02 │ 5 │ - │ … │ Apr 1, 2020 │ 1 │ 6 │
│ 2020-04-02 │ 6 │ 4 │ … │ Mar 26, 2020 │ 6 │ 35 │
│ 2020-04-02 │ 7 │ - │ … │ Apr 1, 2020 │ 1 │ 4 │
│ 2020-04-02 │ 8 │ 7 │ … │ Mar 26, 2020 │ 3 │ 8 │
│ 2020-04-02 │ 9 │ 5 │ … │ Mar 20, 2020 │ 10 │ 57 │
│ 2020-04-02 │ 10 │ 6 │ … │ Sep 14, 2018 │ 5 │ 15 │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ 2022-03-10 │ 1 │ 1 │ … │ Mar 4, 2022 │ 6 │ 59 │
│ 2022-03-10 │ 2 │ - │ … │ Jul 10, 2016 │ 12 │ 52 │
│ 2022-03-10 │ 3 │ 2 │ … │ Jan 1, 2019 │ 25 │ 141 │
│ 2022-03-10 │ 4 │ 3 │ … │ Feb 11, 2022 │ 27 │ 243 │
│ 2022-03-10 │ 5 │ 4 │ … │ Mar 1, 2022 │ 9 │ 76 │
│ 2022-03-10 │ 6 │ 5 │ … │ Feb 13, 2020 │ 45 │ 306 │
│ 2022-03-10 │ 7 │ 6 │ … │ Feb 25, 2022 │ 13 │ 96 │
│ 2022-03-10 │ 8 │ 10 │ … │ Aug 1, 2014 │ 2 │ 4 │
│ 2022-03-10 │ 9 │ 7 │ … │ Mar 1, 2022 │ 9 │ 31 │
│ 2022-03-10 │ 10 │ 8 │ … │ May 1, 2018 │ 6 │ 11 │
│ 2022-03-11 │ 1 │ 2 │ … │ Jul 10, 2016 │ 13 │ 62 │
│ 2022-03-11 │ 2 │ 1 │ … │ Mar 4, 2022 │ 7 │ 68 │
│ 2022-03-11 │ 3 │ 3 │ … │ Jan 1, 2019 │ 26 │ 149 │
│ 2022-03-11 │ 4 │ 4 │ … │ Feb 11, 2022 │ 28 │ 250 │
│ 2022-03-11 │ 5 │ 6 │ … │ Feb 13, 2020 │ 46 │ 312 │
│ 2022-03-11 │ 6 │ 5 │ … │ Mar 1, 2022 │ 10 │ 81 │
│ 2022-03-11 │ 7 │ 7 │ … │ Feb 25, 2022 │ 14 │ 100 │
│ 2022-03-11 │ 8 │ 8 │ … │ Aug 1, 2014 │ 3 │ 7 │
│ 2022-03-11 │ 9 │ 9 │ … │ Mar 1, 2022 │ 10 │ 33 │
│ 2022-03-11 │ 10 │ 10 │ … │ May 1, 2018 │ 7 │ 12 │
├────────────┴───────┴───────────────────┴───┴──────────────────────┴────────────────┴──────────────────┤
│ 7100 rows (40 shown) 10 columns (6 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
この状態は、あくまでもCSVデータを読み込んだだけでデータベースには保存されていない。これをデータベースに保存する場合は以下のようにCREATE TABLE x ASを使用する
CREATE TABLE netflix_top10 AS SELECT * FROM read_csv_auto('./data/netflix_daily_top_10.csv');
一旦終了して確認
.quit
./duckdb sample.db
FROM netflix_top10;
出力
┌────────────┬───────┬───────────────────┬───┬──────────────────────┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ … │ Netflix Release Date │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ │ varchar │ int64 │ int64 │
├────────────┼───────┼───────────────────┼───┼──────────────────────┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ … │ Mar 20, 2020 │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ … │ Jul 21, 2017 │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ … │ Mar 28, 2019 │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ … │ Mar 26, 2020 │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ … │ Mar 20, 2020 │ 9 │ 55 │
(snip)
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ 2022-03-11 │ 6 │ 5 │ … │ Mar 1, 2022 │ 10 │ 81 │
│ 2022-03-11 │ 7 │ 7 │ … │ Feb 25, 2022 │ 14 │ 100 │
│ 2022-03-11 │ 8 │ 8 │ … │ Aug 1, 2014 │ 3 │ 7 │
│ 2022-03-11 │ 9 │ 9 │ … │ Mar 1, 2022 │ 10 │ 33 │
│ 2022-03-11 │ 10 │ 10 │ … │ May 1, 2018 │ 7 │ 12 │
├────────────┴───────┴───────────────────┴───┴──────────────────────┴────────────────┴──────────────────┤
│ 7100 rows (40 shown) 10 columns (6 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
これをファイルに出力するにはCOPYコマンドでファイルフォーマットを指定すれば良い。CSVの場合はセパレータも指定する。
COPY netflix_top10 TO './file.csv' WITH (FORMAT 'CSV', DELIMITER ',');
COPY netflix_top10 TO './file.tsv' WITH (FORMAT 'CSV', DELIMITER '\t');
COPY netflix_top10 TO './file.parquet' WITH (FORMAT 'PARQUET');
ファイルを直接指定することもできるので、これフォーマット変換にも使える。
COPY './data/netflix_daily_top_10.csv' TO './file2.parquet' WITH (FORMAT 'PARQUET');
COPY './data/netflix_daily_top_10.parquet' TO './file2.csv' WITH (FORMAT 'CSV', DELIMITER ',');
Parquetファイルの場合は read_parquetを使う
SELECT * FROM read_parquet('./data/netflix_daily_top_10.parquet');
出力
┌────────────┬───────┬───────────────────┬───┬──────────────────────┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ … │ Netflix Release Date │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ │ varchar │ int64 │ int64 │
├────────────┼───────┼───────────────────┼───┼──────────────────────┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ … │ Mar 20, 2020 │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ … │ Jul 21, 2017 │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ … │ Mar 28, 2019 │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ … │ Mar 26, 2020 │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ … │ Mar 20, 2020 │ 9 │ 55 │
(snip)
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
(snip)
│ 2022-03-11 │ 6 │ 5 │ … │ Mar 1, 2022 │ 10 │ 81 │
│ 2022-03-11 │ 7 │ 7 │ … │ Feb 25, 2022 │ 14 │ 100 │
│ 2022-03-11 │ 8 │ 8 │ … │ Aug 1, 2014 │ 3 │ 7 │
│ 2022-03-11 │ 9 │ 9 │ … │ Mar 1, 2022 │ 10 │ 33 │
│ 2022-03-11 │ 10 │ 10 │ … │ May 1, 2018 │ 7 │ 12 │
├────────────┴───────┴───────────────────┴───┴──────────────────────┴────────────────┴──────────────────┤
│ 7100 rows (40 shown) 10 columns (6 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘
CSVやParquet以外にはJSONなども直接読み込めて、また、読み込み元もローカルのファイルシステムだけでなく、HTTP経由、エンドポイント、またはAWS・Google Cloud．Azure・Cloudflareなどのストレージから読み込むことができるらしい。
kun432表示モードと出力オプション
まず普通にJSONファイルを読み込んでみる。
SELECT * FROM './data/sales.json';
以下のようなテーブルフォーマットで表示される。おそらくこれがデフォルトで、よく見ると、データの中身はターミナルの幅にあわせて省略されている。
出力
┌──────────┬──────────────────────┬──────────────────────────────────────────────────────────────────────┬────────────┐
│ order_id │ customer │ items │ date │
│ int64 │ struct(id bigint, … │ struct(product_id bigint, product_name varchar, quantity bigint, p… │ date │
├──────────┼──────────────────────┼──────────────────────────────────────────────────────────────────────┼────────────┤
│ 1 │ {'id': 101, 'name'… │ [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price… │ 2023-03-24 │
│ 2 │ {'id': 102, 'name'… │ [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'pri… │ 2023-03-25 │
└──────────┴──────────────────────┴──────────────────────────────────────────────────────────────────────┴────────────┘
.modeコマンドを使用すると表示モードを切り替えることができる。まずlineモード。
.mode line
SELECT * FROM './data/sales.json';
出力
order_id = 1
customer = {'id': 101, 'name': John Doe, 'email': john.doe@example.com}
items = [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}]
date = 2023-03-24
order_id = 2
customer = {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com}
items = [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}]
date = 2023-03-25
jsonモード
.mode json
SELECT * FROM './data/sales.json';
出力
[{"order_id":1,"customer":"{'id': 101, 'name': John Doe, 'email': john.doe@example.com}","items":"[{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}]","date":"2023-03-24"},
{"order_id":2,"customer":"{'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com}","items":"[{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}]","date":"2023-03-25"}]
markdownモード
.mode markdown
SELECT * FROM './data/sales.json';
出力
| order_id | customer | items | date |
|---------:|------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| 1 | {'id': 101, 'name': John Doe, 'email': john.doe@example.com} | [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}] | 2023-03-24 |
| 2 | {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com} | [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}] | 2023-03-25 |
その他使えるモードは以下で確認できる
.help mode
出力
.mode MODE ?TABLE? Set output mode
MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
box Tables using unicode box-drawing characters
csv Comma-separated values
column Output in columns. (See .width)
duckbox Tables with extensive features
html HTML <table> code
insert SQL insert statements for TABLE
json Results in a JSON array
jsonlines Results in a NDJSON
latex LaTeX tabular environment code
line One value per line
list Values delimited by "|"
markdown Markdown table format
quote Escape answers as for SQL
table ASCII-art table
tabs Tab-separated values
tcl TCL list elements
trash No output
さらに出力をファイルに行うことができる。markdownモードにして、.outputコマンドを使う。
.mode markdown
.output myfile.md
このあとでSQLを実行する。というか普通にJSONをそのまま読み込んでるね。
SELECT * FROM './data/sales.json';
結果は出力されないが、別ターミナルで確認するとファイルが作成されて結果が出力されているのがわかる。
ls -lt
出力
-rw-r--r--@ 1 kun432 staff 984 6 20 21:54 myfile.md
cat myfile.md
出力
| order_id | customer | items | date |
|---------:|------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| 1 | {'id': 101, 'name': John Doe, 'email': john.doe@example.com} | [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}] | 2023-03-24 |
| 2 | {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com} | [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}] | 2023-03-25 |
更に続けてコマンドを実行してみる。
SELECT * FROM read_csv_auto('./data/netflix_daily_top_10.csv') LIMIT 5;
再度ファイルを見てみると追記されているのがわかる。つまり、.outputは一度使用するとずっとファイルに出力結果が追記されていく様子。
出力
| order_id | customer | items | date |
|---------:|------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| 1 | {'id': 101, 'name': John Doe, 'email': john.doe@example.com} | [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}] | 2023-03-24 |
| 2 | {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com} | [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}] | 2023-03-25 |
| As of | Rank | Year to Date Rank | Last Week Rank | Title | Type | Netflix Exclusive | Netflix Release Date | Days In Top 10 | Viewership Score |
|------------|-----:|-------------------|----------------|------------------------------|---------|------------------:|----------------------|---------------:|-----------------:|
| 2020-04-01 | 1 | 1 | 1 | Tiger King: Murder, Mayhem … | TV Show | true | Mar 20, 2020 | 9 | 90 |
| 2020-04-01 | 2 | 2 | - | Ozark | TV Show | true | Jul 21, 2017 | 5 | 45 |
| 2020-04-01 | 3 | 3 | 2 | All American | TV Show | NULL | Mar 28, 2019 | 9 | 76 |
| 2020-04-01 | 4 | 4 | - | Blood Father | Movie | NULL | Mar 26, 2020 | 5 | 30 |
| 2020-04-01 | 5 | 5 | 4 | The Platform | Movie | true | Mar 20, 2020 | 9 | 55 |
再度duckdbを開き直して、今度はonceでファイルを出力してみる。
.mode markdown
.once myfile2.md
そしてSQLを2回実行してみる。
SELECT * FROM './data/sales.json';
SELECT * FROM read_csv_auto('./data/netflix_daily_top_10.csv') LIMIT 5;
2回目のSQLの出力だけが画面には出力されている。
出力
| As of | Rank | Year to Date Rank | Last Week Rank | Title | Type | Netflix Exclusive | Netflix Release Date | Days In Top 10 | Viewership Score |
|------------|-----:|-------------------|----------------|------------------------------|---------|------------------:|----------------------|---------------:|-----------------:|
| 2020-04-01 | 1 | 1 | 1 | Tiger King: Murder, Mayhem … | TV Show | true | Mar 20, 2020 | 9 | 90 |
| 2020-04-01 | 2 | 2 | - | Ozark | TV Show | true | Jul 21, 2017 | 5 | 45 |
| 2020-04-01 | 3 | 3 | 2 | All American | TV Show | NULL | Mar 28, 2019 | 9 | 76 |
| 2020-04-01 | 4 | 4 | - | Blood Father | Movie | NULL | Mar 26, 2020 | 5 | 30 |
| 2020-04-01 | 5 | 5 | 4 | The Platform | Movie | true | Mar 20, 2020 | 9 | 55 |
.onceで指定したファイルを見ると、最初のSQLの結果のみが出力されている。つまり、.onceはその直後に実行したSQLだけをファイルに出力するということ。
cat myfile2.md
出力
| order_id | customer | items | date |
|---------:|------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| 1 | {'id': 101, 'name': John Doe, 'email': john.doe@example.com} | [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}] | 2023-03-24 |
| 2 | {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com} | [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}] | 2023-03-25 |
コマンドラインからのSQLの実行
-cを使えばコマンドラインからCLIを実行できる。
./duckdb -c "SELECT * FROM read_parquet('data/netflix_daily_top_10.parquet');"
出力
┌────────────┬───────┬───────────────────┬───┬──────────────────────┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ … │ Netflix Release Date │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ │ varchar │ int64 │ int64 │
├────────────┼───────┼───────────────────┼───┼──────────────────────┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ … │ Mar 20, 2020 │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ … │ Jul 21, 2017 │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ … │ Mar 28, 2019 │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ … │ Mar 26, 2020 │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ … │ Mar 20, 2020 │ 9 │ 55 │
(snip)
これを使えばカスタムなbashの関数などが作成できる。以下は紹介されていたCSVをParquetに変換するスニペット。
#!/bin/bash
function csv_to_parquet() {
file_path="$1"
duckdb -c "COPY (SELECT * FROM read_csv_auto('$file_path')) TO '${file_path%.*}.parquet' (FORMAT PARQUET);" }
こういうスニペットが以下で多く紹介されている
kun432設定のカスタマイズ
スレッド数、メモリ制限、NULL 値の順序など、細かい設定をカスタマイズすることができる。設定可能なオプションのリストと現在の値は以下で確認できる。
FROM duckdb_settings();
出力
┌──────────────────────┬──────────────────────┬──────────────────────────┬────────────┬─────────┐
│ name │ value │ description │ input_type │ scope │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼──────────────────────┼──────────────────────────┼────────────┼─────────┤
│ access_mode │ automatic │ Access mode of the dat… │ VARCHAR │ GLOBAL │
│ allocator_backgrou… │ false │ Whether to enable the … │ BOOLEAN │ GLOBAL │
│ allocator_bulk_dea… │ 512.0 MiB │ If a bulk deallocation… │ VARCHAR │ GLOBAL │
│ allocator_flush_th… │ 128.0 MiB │ Peak allocation thresh… │ VARCHAR │ GLOBAL │
│ allow_community_ex… │ true │ Allow to load communit… │ BOOLEAN │ GLOBAL │
│ allow_extensions_m… │ false │ Allow to load extensio… │ BOOLEAN │ GLOBAL │
│ allow_persistent_s… │ true │ Allow the creation of … │ BOOLEAN │ GLOBAL │
│ allow_unredacted_s… │ false │ Allow printing unredac… │ BOOLEAN │ GLOBAL │
│ allow_unsigned_ext… │ false │ Allow to load extensio… │ BOOLEAN │ GLOBAL │
│ allowed_directories │ [] │ List of directories/pr… │ VARCHAR[] │ GLOBAL │
│ allowed_paths │ [] │ List of files that are… │ VARCHAR[] │ GLOBAL │
│ arrow_large_buffer… │ false │ Whether Arrow buffers … │ BOOLEAN │ GLOBAL │
│ arrow_lossless_con… │ false │ Whenever a DuckDB type… │ BOOLEAN │ GLOBAL │
│ arrow_output_list_… │ false │ Whether export to Arro… │ BOOLEAN │ GLOBAL │
│ arrow_output_version │ 1.0 │ Whether strings should… │ VARCHAR │ GLOBAL │
│ asof_loop_join_thr… │ 64 │ The maximum number of … │ UBIGINT │ LOCAL │
│ autoinstall_extens… │ │ Overrides the custom e… │ VARCHAR │ GLOBAL │
│ autoinstall_known_… │ true │ Whether known extensio… │ BOOLEAN │ GLOBAL │
│ autoload_known_ext… │ true │ Whether known extensio… │ BOOLEAN │ GLOBAL │
│ catalog_error_max_… │ 100 │ The maximum number of … │ UBIGINT │ GLOBAL │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ scalar_subquery_er… │ true │ When a scalar subquery… │ BOOLEAN │ LOCAL │
│ scheduler_process_… │ false │ Partially process task… │ BOOLEAN │ GLOBAL │
│ schema │ main │ Sets the default searc… │ VARCHAR │ LOCAL │
│ search_path │ │ Sets the default catal… │ VARCHAR │ LOCAL │
│ secret_directory │ /Users/kun432/.duc… │ Set the directory to w… │ VARCHAR │ GLOBAL │
│ storage_compatibil… │ v0.10.2 │ Serialize on checkpoin… │ VARCHAR │ GLOBAL │
│ streaming_buffer_s… │ 976.5 KiB │ The maximum memory to … │ VARCHAR │ LOCAL │
│ temp_directory │ .tmp │ Set the directory to w… │ VARCHAR │ GLOBAL │
│ threads │ 12 │ The number of total th… │ BIGINT │ GLOBAL │
│ worker_threads │ 12 │ The number of total th… │ BIGINT │ GLOBAL │
│ username │ NULL │ The username to use. I… │ VARCHAR │ GLOBAL │
│ user │ NULL │ The username to use. I… │ VARCHAR │ GLOBAL │
│ zstd_min_string_le… │ 4096 │ The (average) length a… │ UBIGINT │ GLOBAL │
│ TimeZone │ Asia/Tokyo │ The current time zone │ VARCHAR │ GLOBAL │
│ enable_geoparquet_… │ true │ Attempt to decode/enco… │ BOOLEAN │ GLOBAL │
│ Calendar │ gregorian │ The current calendar │ VARCHAR │ GLOBAL │
│ parquet_metadata_c… │ false │ Cache Parquet metadata… │ BOOLEAN │ GLOBAL │
│ prefetch_all_parqu… │ false │ Use the prefetching me… │ BOOLEAN │ GLOBAL │
│ disable_parquet_pr… │ false │ Disable the prefetchin… │ BOOLEAN │ GLOBAL │
│ binary_as_string │ │ In Parquet files, inte… │ BOOLEAN │ GLOBAL │
├──────────────────────┴──────────────────────┴──────────────────────────┴────────────┴─────────┤
│ 126 rows (40 shown) 5 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────┘
ドキュメントだとこの辺りに色々載っている。
kun432拡張機能を使う
DuckDBには多くの拡張機能がある。
利用可能な拡張機能を確認するには以下を実行。
FROM duckdb_extensions();
出力
┌──────────────────┬─────────┬───┬───────────────────┬───────────────────┬────────────────┐
│ extension_name │ loaded │ … │ extension_version │ install_mode │ installed_from │
│ varchar │ boolean │ │ varchar │ varchar │ varchar │
├──────────────────┼─────────┼───┼───────────────────┼───────────────────┼────────────────┤
│ autocomplete │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ aws │ false │ … │ │ NOT_INSTALLED │ │
│ azure │ false │ … │ │ NOT_INSTALLED │ │
│ core_functions │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ delta │ false │ … │ │ NOT_INSTALLED │ │
│ ducklake │ false │ … │ │ NOT_INSTALLED │ │
│ encodings │ false │ … │ │ NOT_INSTALLED │ │
│ excel │ false │ … │ │ NOT_INSTALLED │ │
│ fts │ false │ … │ │ NOT_INSTALLED │ │
│ httpfs │ false │ … │ │ NOT_INSTALLED │ │
│ iceberg │ false │ … │ │ NOT_INSTALLED │ │
│ icu │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ inet │ false │ … │ │ NOT_INSTALLED │ │
│ jemalloc │ false │ … │ │ NOT_INSTALLED │ │
│ json │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ motherduck │ false │ … │ │ NOT_INSTALLED │ │
│ mysql_scanner │ false │ … │ │ NOT_INSTALLED │ │
│ parquet │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ postgres_scanner │ false │ … │ │ NOT_INSTALLED │ │
│ shell │ true │ … │ v1.3.1 │ STATICALLY_LINKED │ │
│ spatial │ false │ … │ │ NOT_INSTALLED │ │
│ sqlite_scanner │ false │ … │ │ NOT_INSTALLED │ │
│ tpcds │ false │ … │ │ NOT_INSTALLED │ │
│ tpch │ false │ … │ │ NOT_INSTALLED │ │
│ ui │ false │ … │ │ NOT_INSTALLED │ │
│ vss │ false │ … │ │ NOT_INSTALLED │ │
├──────────────────┴─────────┴───┴───────────────────┴───────────────────┴────────────────┤
│ 26 rows 9 columns (5 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────┘
拡張機能をインストールしてみる。今回はHTTPSやS3経由でリモートのファイルを読み込めるhttpfsをインストール。
INSTALL httpfs;
出力
100% ▕████████████████████████████████████████████████████████████▏
再度確認してみる
FROM duckdb_extensions();
出力
┌──────────────────┬─────────┬───┬───────────────────┬───────────────────┬────────────────┐
│ extension_name │ loaded │ … │ extension_version │ install_mode │ installed_from │
│ varchar │ boolean │ │ varchar │ varchar │ varchar │
├──────────────────┼─────────┼───┼───────────────────┼───────────────────┼────────────────┤
(snip)
│ httpfs │ false │ … │ 39779f8 │ REPOSITORY │ core │
(snip)
拡張機能をロードする。ただし、今回のhttpfsのような拡張の場合はhttps://で始まっているファイルを読み込もうとすると自動的にロードされるらしい。今回はとりあえず明示的にやってみる。
LOAD httpfs;
再度FROM duckdb_extensions();を実行してみると、以下のようにロードされているのがわかる。
出力
┌──────────────────┬─────────┬───┬───────────────────┬───────────────────┬────────────────┐
│ extension_name │ loaded │ … │ extension_version │ install_mode │ installed_from │
│ varchar │ boolean │ │ varchar │ varchar │ varchar │
├──────────────────┼─────────┼───┼───────────────────┼───────────────────┼────────────────┤
(snip)
│ httpfs │ true │ … │ 39779f8 │ REPOSITORY │ core │
(snip)
これでhttpfs拡張が使えるようになったので、早速使ってみる。DuckDB公式が用意しているデータを使う。
SELECT * FROM 'https://raw.githubusercontent.com/duckdb/duckdb-web/main/data/weather.csv';
出力
┌───────────────┬─────────┬─────────┬─────────┬────────────┐
│ column0 │ column1 │ column2 │ column3 │ column4 │
│ varchar │ int64 │ int64 │ double │ date │
├───────────────┼─────────┼─────────┼─────────┼────────────┤
│ San Francisco │ 46 │ 50 │ 0.25 │ 1994-11-27 │
│ San Francisco │ 43 │ 57 │ 0.0 │ 1994-11-29 │
│ Hayward │ 37 │ 54 │ NULL │ 1994-11-29 │
└───────────────┴─────────┴─────────┴─────────┴────────────┘
なお、インストールできる拡張にはコアとコミュニティのレポジトリがあり、FROM duckdb_extensions(); で表示されるのはコアレポジトリらしい。コアレポジトリで提供されている拡張の一覧は以下にある。
コミュニティレポジトリで利用可能な拡張は以下にリストがある。
コミュニティレポジトリの拡張はどうやらデフォルトではセキュリティを考慮して許可されていない様子。これらの拡張をインストールする場合は allow_unsigned_extensionsフラグを有効にする or -unsignedオプションを付けてduckdbを起動する必要がある様子。
拡張は自分で作成することもでき、いかにそのテンプレートがある。
kun432チュートリアルの最後に、S3のパプリックレポジトリで公開されているデータを使ったものがあるので、サラッとやっておく。
duckdbを起動
./duckdb
まずS3のデータを取得する。httpfs拡張を使うので、これを有効にしておく。
INSTALL httpfs;
LOAD httpfs;
S3のファイルを扱うには、最低でもリージョンの設定が必要になる。これはパブリックバケットの場合で、プライベートバケットの場合はその他認証の設定が必要になる。HTTPSでアクセスできる場合でも認証が必要な場合は同様に追加設定が必要になる様子。
SET s3_region='us-east-1';
ではデータセットを読み込んで表示する。
CREATE TABLE netflix AS SELECT * FROM read_parquet('s3://us-prd-motherduck-open-datasets/netflix/netflix_daily_top_10.parquet');
FROM netflix;
出力
┌────────────┬───────┬───────────────────┬───┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ … │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ │ int64 │ int64 │
├────────────┼───────┼───────────────────┼───┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ … │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ … │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ … │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ … │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ … │ 9 │ 55 │
(snip)
カラムなどもターミナル幅にあわせて省略されているのだけど、実際には以下のようなカラムがある。
DESC netflix;
出力
┌──────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ As of │ DATE │ YES │ NULL │ NULL │ NULL │
│ Rank │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ Year to Date Rank │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Last Week Rank │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Title │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Type │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Netflix Exclusive │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Netflix Release Date │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ Days In Top 10 │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ Viewership Score │ BIGINT │ YES │ NULL │ NULL │ NULL │
├──────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 10 rows 6 columns │
└────────────────────────────────────────────────────────────────────────────┘
最も人気のあるTV番組の上位5件を抽出する。
SELECT Title, max("Days In Top 10") from netflix
where Type='Movie'
GROUP BY Title
ORDER BY max("Days In Top 10") desc
limit 5;
出力
┌────────────────────────────────┬───────────────────────┐
│ Title │ max("Days In Top 10") │
│ varchar │ int64 │
├────────────────────────────────┼───────────────────────┤
│ The Mitchells vs. The Machines │ 31 │
│ Vivo │ 29 │
│ How the Grinch Stole Christmas │ 29 │
│ 365 Days │ 28 │
│ Despicable Me 2 │ 27 │
└────────────────────────────────┴───────────────────────┘
結果をCSVに出力する。
COPY (
SELECT Title, max("Days In Top 10") from netflix
where Type='TV Show'
GROUP BY Title
ORDER BY max("Days In Top 10") desc
limit 5
) TO 'output.csv' (HEADER, DELIMITER ',');
出力されたファイルを見てみるとこんな感じ。
cat output.csv
出力
Title,"max(""Days In Top 10"")"
Cocomelon,428
Ozark,89
Cobra Kai,81
Manifest,80
The Queenâs Gambit,73
Python
Python APIのページを見つつ進めていく。
インストール
Colaboratoryでやる。まず、パッケージインストール。
!pip install -U duckdb
!pip freeze | grep -i duckdb
出力
duckdb==1.3.1
基本的な使い方
SQLの実行はduckdb.sql()を使う。
import duckdb
duckdb.sql("SELECT 42").show()
出力
┌───────┐
│ 42 │
│ int32 │
├───────┤
│ 42 │
└───────┘
この状態はCLIと同様にインメモリで行われる。以下のような説明がある。
これは、Pythonモジュール内にグローバルに保存されているインメモリデータベースを使ってクエリを実行します。 クエリの結果はリレーションとして返されます。 リレーションはクエリの記号表現です。 クエリは、結果がフェッチされるか、画面にプリントされるように要求されるまでは実行されません。
つまりこういうことかな
import duckdb
r = duckdb.sql("SELECT 42")
r.show() # ここで実行される
結果は同じなので割愛。
また、以下のようにインクリメンタルにクエリを構築することができる。
import duckdb
r1 = duckdb.sql("SELECT 42 AS i")
r2 = duckdb.sql("SELECT i * 2 AS k FROM r1")
r2.show()
出力
┌───────┐
│ k │
│ int32 │
├───────┤
│ 84 │
└───────┘
データの入力
DuckDBはいろいろなファイルフォーマットを読み込むことができる。
CLIのチュートリアル記事にあったレポジトリを使用させてもらう。
!git clone https://github.com/mehd-io/duckdb-tutorial-for-beginner
%%cd duckdb-tutorial-for-beginner
レポジトリ内のディレクトリ構成
.
├── README.md
├── data
│ ├── netflix_daily_top_10.csv
│ ├── netflix_daily_top_10.parquet
│ └── sales.json
├── duckdb_for_beginner.sql
└── examples
├── extensions.sql
├── json_parsing.sql
└── netflix_playground.sql
ではまずCSV
import duckdb
duckdb.read_csv("./data/netflix_daily_top_10.csv")
出力
┌────────────┬───────┬───────────────────┬────────────────┬──────────────────────────────┬─────────┬───────────────────┬──────────────────────┬────────────────┬──────────────────┐
│ As of │ Rank │ Year to Date Rank │ Last Week Rank │ Title │ Type │ Netflix Exclusive │ Netflix Release Date │ Days In Top 10 │ Viewership Score │
│ date │ int64 │ varchar │ varchar │ varchar │ varchar │ boolean │ varchar │ int64 │ int64 │
├────────────┼───────┼───────────────────┼────────────────┼──────────────────────────────┼─────────┼───────────────────┼──────────────────────┼────────────────┼──────────────────┤
│ 2020-04-01 │ 1 │ 1 │ 1 │ Tiger King: Murder, Mayhem … │ TV Show │ true │ Mar 20, 2020 │ 9 │ 90 │
│ 2020-04-01 │ 2 │ 2 │ - │ Ozark │ TV Show │ true │ Jul 21, 2017 │ 5 │ 45 │
│ 2020-04-01 │ 3 │ 3 │ 2 │ All American │ TV Show │ NULL │ Mar 28, 2019 │ 9 │ 76 │
│ 2020-04-01 │ 4 │ 4 │ - │ Blood Father │ Movie │ NULL │ Mar 26, 2020 │ 5 │ 30 │
│ 2020-04-01 │ 5 │ 5 │ 4 │ The Platform │ Movie │ true │ Mar 20, 2020 │ 9 │ 55 │
│ 2020-04-01 │ 6 │ 6 │ - │ Car Masters: Rust to Riches │ TV Show │ true │ Sep 14, 2018 │ 4 │ 14 │
│ 2020-04-01 │ 7 │ 10 │ - │ Unorthodox │ TV Show │ true │ Mar 26, 2020 │ 2 │ 5 │
│ 2020-04-01 │ 8 │ 7 │ 5 │ Love is Blind │ TV Show │ true │ Feb 13, 2020 │ 9 │ 40 │
│ 2020-04-01 │ 9 │ 8 │ - │ Badland │ Movie │ NULL │ Mar 26, 2020 │ 4 │ 11 │
│ 2020-04-01 │ 10 │ 9 │ - │ Uncorked │ Movie │ true │ Mar 27, 2020 │ 4 │ 15 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 2022-03-11 │ 1 │ 2 │ - │ The Last Kingdom │ TV Show │ NULL │ Jul 10, 2016 │ 13 │ 62 │
│ 2022-03-11 │ 2 │ 1 │ - │ Pieces of Her │ TV Show │ true │ Mar 4, 2022 │ 7 │ 68 │
│ 2022-03-11 │ 3 │ 3 │ - │ Good Girls │ TV Show │ NULL │ Jan 1, 2019 │ 26 │ 149 │
│ 2022-03-11 │ 4 │ 4 │ 3 │ Inventing Anna │ TV Show │ true │ Feb 11, 2022 │ 28 │ 250 │
│ 2022-03-11 │ 5 │ 6 │ 6 │ Love is Blind │ TV Show │ true │ Feb 13, 2020 │ 46 │ 312 │
│ 2022-03-11 │ 6 │ 5 │ 1 │ Worst Roommate Ever │ TV Show │ true │ Mar 1, 2022 │ 10 │ 81 │
│ 2022-03-11 │ 7 │ 7 │ 2 │ Vikings: Valhalla │ TV Show │ true │ Feb 25, 2022 │ 14 │ 100 │
│ 2022-03-11 │ 8 │ 8 │ - │ Shooter │ Movie │ NULL │ Aug 1, 2014 │ 3 │ 7 │
│ 2022-03-11 │ 9 │ 9 │ 7 │ Shrek 2 │ Movie │ NULL │ Mar 1, 2022 │ 10 │ 33 │
│ 2022-03-11 │ 10 │ 10 │ - │ Shrek │ Movie │ NULL │ May 1, 2018 │ 7 │ 12 │
├────────────┴───────┴───────────────────┴────────────────┴──────────────────────────────┴─────────┴───────────────────┴──────────────────────┴────────────────┴──────────────────┤
│ 7100 rows (20 shown) 10 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
直接SQLを実行
duckdb.sql("SELECT COUNT(*) FROM './data/netflix_daily_top_10.csv'")
出力
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 7100 │
└──────────────┘
read_csvを使うこともできる。
duckdb.sql("SELECT COUNT(*) FROM read_csv('./data/netflix_daily_top_10.csv')")
結果は同じなので割愛。
Parquetも同様。上と同じことをParquetファイルに対してやる場合はこう。
duckdb.read_parquet("./data/netflix_daily_top_10.parquet")
duckdb.sql("SELECT COUNT(*) FROM './data/netflix_daily_top_10.parquet'")
duckdb.sql("SELECT COUNT(*) FROM read_parquet('./data/netflix_daily_top_10.parquet')")
JSONも。
duckdb.read_json("./data/sales.json")
出力
┌──────────┬──────────────────────────────────────────────────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────────┐
│ order_id │ customer │ items │ date │
│ int64 │ struct(id bigint, "name" varchar, email varchar) │ struct(product_id bigint, product_name varchar, quantity bigint, price bigint)[] │ date │
├──────────┼──────────────────────────────────────────────────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────────┤
│ 1 │ {'id': 101, 'name': John Doe, 'email': john.doe@example.com} │ [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}] │ 2023-03-24 │
│ 2 │ {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com} │ [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}] │ 2023-03-25 │
└──────────┴──────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────┘
duckdb.sql("SELECT COUNT(*) FROM './data/sales.json'")
出力
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 2 │
└──────────────┘
duckdb.sql("SELECT COUNT(*) FROM read_json_auto('./data/sales.json')")
出力
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 2 │
└──────────────┘
データフレーム
Pandas/Polarsのデータフレーム、Arrowテーブルには直接問い合わせることができる。ただし、この場合はINSERTやUPDATEなどの編集はできない。
とりあえずPandasで試してみる。Pandasの公式チュートリアルのデータフレームを拝借。
import duckdb
import pandas as pd
pandas_df = pd.DataFrame(
{
"Name": [
"Braund, Mr. Owen Harris",
"Allen, Mr. William Henry",
"Bonnell, Miss. Elizabeth",
],
"Age": [22, 35, 58],
"Sex": ["male", "male", "female"],
}
)
duckdb.sql("SELECT * FROM pandas_df")
出力
┌──────────────────────────┬───────┬─────────┐
│ Name │ Age │ Sex │
│ varchar │ int64 │ varchar │
├──────────────────────────┼───────┼─────────┤
│ Braund, Mr. Owen Harris │ 22 │ male │
│ Allen, Mr. William Henry │ 35 │ male │
│ Bonnell, Miss. Elizabeth │ 58 │ female │
└──────────────────────────┴───────┴─────────┘
より細かい説明は以下にもある
kun432結果の変換・ディスクへの書き込み
CLIと同様にフォーマットを変換できる。
Pythonオブジェクトへの変換
duckdb.sql("SELECT * FROM './data/sales.json'").fetchall()
出力
[(1,
{'id': 101, 'name': 'John Doe', 'email': 'john.doe@example.com'},
[{'product_id': 301, 'product_name': 'Laptop', 'quantity': 1, 'price': 1200},
{'product_id': 302, 'product_name': 'Mouse', 'quantity': 2, 'price': 25}],
datetime.date(2023, 3, 24)),
(2,
{'id': 102, 'name': 'Jane Smith', 'email': 'jane.smith@example.com'},
[{'product_id': 303, 'product_name': 'Keyboard', 'quantity': 1, 'price': 50},
{'product_id': 304,
'product_name': 'Monitor',
'quantity': 3,
'price': 200}],
datetime.date(2023, 3, 25))]
Panadasデータフレームに変換。（なおゆえあってここからはJupyterLabを使っているが、Colaboratoryでも同じような出力になる）。PolarsのデータフレームやArrowのテーブルにも同様に変換できる。
r.df()
NumPyの配列に変換
duckdb.sql("SELECT * FROM './data/sales.json'").fetchnumpy()
出力
{'order_id': array([1, 2]),
'customer': array([{'id': 101, 'name': 'John Doe', 'email': 'john.doe@example.com'},
{'id': 102, 'name': 'Jane Smith', 'email': 'jane.smith@example.com'}],
dtype=object),
'items': array([array([{'product_id': 301, 'product_name': 'Laptop', 'quantity': 1, 'price': 1200},
{'product_id': 302, 'product_name': 'Mouse', 'quantity': 2, 'price': 25}],
dtype=object) ,
array([{'product_id': 303, 'product_name': 'Keyboard', 'quantity': 1, 'price': 50},
{'product_id': 304, 'product_name': 'Monitor', 'quantity': 3, 'price': 200}],
dtype=object) ],
dtype=object),
'date': array(['2023-03-24T00:00:00.000000', '2023-03-25T00:00:00.000000'],
dtype='datetime64[us]')}
またディスクへの書き込みも可能。CSVやParquetなどはこれを使う。
duckdb.sql("SELECT * FROM './data/sales.json'").write_csv("output.csv")
!cat output.csv
出力
order_id,customer,items,date
1,"{'id': 101, 'name': John Doe, 'email': john.doe@example.com}","[{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}]",2023-03-24
2,"{'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com}","[{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}]",2023-03-25
duckdb.sql("SELECT * FROM './data/sales.json'").write_parquet("output.parquet")
!ls -lt output.parquet
!file output.parquet
出力
-rw-r--r-- 1 jovyan users 2353 Jun 20 18:26 output.parquet
output.parquet: Apache Parquet
COPY文を使うこともできる。
duckdb.sql("COPY (SELECT * FROM './data/sales.json') TO 'copy.csv'")
!cat copy.csv
出力
order_id,customer,items,date
1,"{'id': 101, 'name': John Doe, 'email': john.doe@example.com}","[{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 2, 'price': 25}]",2023-03-24
2,"{'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com}","[{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 3, 'price': 200}]",2023-03-25
データベースへの接続
duckdb.connect()を使用する。
引数無しで使用した場合はインメモリデータベースへの接続となる。
import duckdb
con = duckdb.connect()
con.sql("CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;")
con.sql("FROM ducks;").show()
出力
┌───────┬──────────┐
│ age │ breed │
│ int32 │ varchar │
├───────┼──────────┤
│ 3 │ mandarin │
└───────┴──────────┘
永続データベースの場合はduckdb.connect("DBファイルパス")を使う。
import duckdb
# "file.db" への接続を作成
con = duckdb.connect("file.db")
# テーブルを作成してデータを登録
con.sql("CREATE TABLE ducks (age INTEGER, breed VARCHAR)")
con.sql("INSERT INTO ducks VALUES (3, 'mandarin')")
# 検索
con.table("ducks").show()
# 明示的に接続を閉じる
# 注: スコープ外になると、接続も暗黙的に閉じられる。
con.close()
コンテキストマネージャを使う場合
import duckdb
with duckdb.connect("file.db2") as con:
con.sql("CREATE TABLE ducks (age INTEGER, breed VARCHAR)")
con.sql("INSERT INTO ducks VALUES (3, 'mandarin')")
con.table("ducks").show()
# コンテキストマネージャが接続を自動で閉じる
どちらも以下のようになる
出力
┌───────┬──────────┐
│ age │ breed │
│ int32 │ varchar │
├───────┼──────────┤
│ 3 │ mandarin │
└───────┴──────────┘
DBファイルも作成されている。
ls -lt file*.db
出力
-rw-r--r-- 1 jovyan users 536576 Jun 20 18:42 file.db2
-rw-r--r-- 1 jovyan users 536576 Jun 20 18:41 file.db
また接続時にconfigで設定オプションを設定できる。
import duckdb
con = duckdb.connect(config = {'threads': 1})
その他
- 接続オブジェクト経由で操作する場合と、duckdbモジュールで直接操作する場合、どちらも同じメソッドが使えるが、duckdbモジュールの場合は共有のグローバルなインメモリデータベースとなる。
- アプリケーション等で共有する場合は接続オブジェクト経由で操作すべき
- 接続オブジェクトはスレッドセーフではない。複数のスレッドから同じデータベースに書き込む場合には、
DuckDBPyConnection.cursor()でスレッドごとにカーソルを作成する必要がある
基本的には複数から同時アクセスするものではないということかな。
kun432拡張機能
Pythonからも拡張機能のロード・インストールが行える。
import duckdb
con = duckdb.connect()
con.install_extension("httpfs")
con.load_extension("httpfs")
コミュニティレポジトリの場合はインストールにrepository="community"オプションを付与する。
import duckdb
con = duckdb.connect()
con.install_extension("h3", repository="community")
con.load_extension("h3")
署名されていない拡張機能の場合はデータベース接続時にconfig = {"allow_unsigned_extensions": "true"}を指定する。
kun432セマンティック検索
ということでやっと本題
DuckDBではVSS拡張を使うことでセマンティック検索・ベクトル検索が可能となる。いろいろ日本語記事も多い。
実はまさにこの組み合わせをやりたくてDuckDB入門していたのだった、なんというタイムリー・・・
とはいえ一通り自分で動かしてみたいので、公式ドキュメント読みながら進める。
まずはCLIで。
./duckdb
vss拡張を有効化する。
INSTALL vss;
LOAD vss;
テーブルを作成してデータを登録。ここでは3次元の浮動小数点のARRAYカラムを持つテーブルを作成して、ランダムなデータを登録している。
CREATE TABLE my_vector_table (vec FLOAT[3]);
INSERT INTO my_vector_table
SELECT array_value(a, b, c)
FROM range(1, 10) ra(a), range(1, 10) rb(b), range(1, 10) rc(c);
FROM my_vector_table;
出力
┌─────────────────┐
│ vec │
│ float[3] │
├─────────────────┤
│ [1.0, 1.0, 1.0] │
│ [1.0, 2.0, 1.0] │
│ [1.0, 3.0, 1.0] │
│ [1.0, 4.0, 1.0] │
│ [1.0, 5.0, 1.0] │
│ [1.0, 6.0, 1.0] │
│ [1.0, 7.0, 1.0] │
│ [1.0, 8.0, 1.0] │
│ [1.0, 9.0, 1.0] │
│ [1.0, 1.0, 2.0] │
│ [1.0, 2.0, 2.0] │
│ [1.0, 3.0, 2.0] │
│ [1.0, 4.0, 2.0] │
│ [1.0, 5.0, 2.0] │
│ [1.0, 6.0, 2.0] │
│ [1.0, 7.0, 2.0] │
│ [1.0, 8.0, 2.0] │
│ [1.0, 9.0, 2.0] │
│ [1.0, 1.0, 3.0] │
│ [1.0, 2.0, 3.0] │
│ · │
│ · │
│ · │
│ [9.0, 8.0, 7.0] │
│ [9.0, 9.0, 7.0] │
│ [9.0, 1.0, 8.0] │
│ [9.0, 2.0, 8.0] │
│ [9.0, 3.0, 8.0] │
│ [9.0, 4.0, 8.0] │
│ [9.0, 5.0, 8.0] │
│ [9.0, 6.0, 8.0] │
│ [9.0, 7.0, 8.0] │
│ [9.0, 8.0, 8.0] │
│ [9.0, 9.0, 8.0] │
│ [9.0, 1.0, 9.0] │
│ [9.0, 2.0, 9.0] │
│ [9.0, 3.0, 9.0] │
│ [9.0, 4.0, 9.0] │
│ [9.0, 5.0, 9.0] │
│ [9.0, 6.0, 9.0] │
│ [9.0, 7.0, 9.0] │
│ [9.0, 8.0, 9.0] │
│ [9.0, 9.0, 9.0] │
├─────────────────┤
│ 729 rows │
│ (40 shown) │
└─────────────────┘
このテーブルにHNSWインデックスを作成する。
CREATE INDEX my_hnsw_index ON my_vector_table USING HNSW (vec);
では検索してみる。ORDER BYで距離メトリクス関数（以下の場合はarray_distance）を指定してLIMITで件数を指定する。
SELECT *
FROM my_vector_table
ORDER BY array_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
出力
┌─────────────────┐
│ vec │
│ float[3] │
├─────────────────┤
│ [1.0, 2.0, 3.0] │
│ [2.0, 2.0, 3.0] │
│ [1.0, 2.0, 4.0] │
└─────────────────┘
めちゃめちゃ直感的だなぁ・・・
こういう書き方もできる。
SELECT min_by(my_vector_table, array_distance(vec, [1, 2, 3]::FLOAT[3]), 3 ORDER BY vec) AS result
FROM my_vector_table;
出力
┌────────────────────────────────────────────────────────────────────────────────┐
│ result │
│ struct(vec float[3])[] │
├────────────────────────────────────────────────────────────────────────────────┤
│ [{'vec': [1.0, 2.0, 3.0]}, {'vec': [2.0, 2.0, 3.0]}, {'vec': [1.0, 2.0, 4.0]}] │
└────────────────────────────────────────────────────────────────────────────────┘
内容は同じだけど、返されるデータの形式はちょっと異なる。後続の処理次第で選べば良さそう。
EXPLAINで確認すると、HNSWインデックスが使用されていることがわかる。
EXPLAIN
SELECT *
FROM my_vector_table
ORDER BY array_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
出力
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ #0 │
│ │
│ ~3 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ vec │
│ NULL │
│ │
│ ~729 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HNSW_INDEX_SCAN │
│ ──────────────────── │
│ Table: │
│ my_vector_table │
│ │
│ HSNW Index: │
│ my_hnsw_index │
│ │
│ Projections: vec │
│ │
│ ~729 Rows │
└───────────────────────────┘
DuckDBでは、インデックス作成時にmetricパラメータで使用する距離メトリクスを指定でき、どのメトリクスを指定するかで、検索時の距離メトリクス関数もそれに応じたものを指定する必要がある。
| 距離メトリクス | メトリクス関数 | 説明 |
|---|---|---|
l2sq |
array_distance |
ユークリッド距離。metric未指定時のデフォルト。 |
cosine |
array_cosine_distance |
コサイン類似度距離 |
ip |
array_negative_inner_product |
負の内積 |
なお、
HNSWインデックスは 1 列にのみ適用されますが、同じテーブル上で異なる列を対象に複数のHNSWインデックスを作成することもできます。また、同一列に対して距離指標を変えて複数のHNSWインデックスを作成することも可能です。
とあるが、後半部分はちょっとややこしくなるのでは？ではという気もする。コサイン類似度距離を指定したインデックスを追加作成。
CREATE INDEX my_hnsw_cosine_index
ON my_vector_table
USING HNSW (vec)
WITH (metric = 'cosine');
インデックスを見てみる
.indexes my_vector_table
2つのインデックスが作成されているのがわかる。
出力
my_hnsw_cosine_index my_hnsw_index
コサイン類似度距離を使用しているインデックス検索してみる。array_cosine_distanceに変えている。
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
異なる結果になっているのがわかる
出力
┌─────────────────┐
│ vec │
│ float[3] │
├─────────────────┤
│ [3.0, 6.0, 9.0] │
│ [2.0, 4.0, 6.0] │
│ [1.0, 2.0, 3.0] │
└─────────────────┘
EXPLAINしてみる。
EXPLAIN
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
出力
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ #0 │
│ │
│ ~3 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ vec │
│ NULL │
│ │
│ ~729 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HNSW_INDEX_SCAN │
│ ──────────────────── │
│ Table: │
│ my_vector_table │
│ │
│ HSNW Index: │
│ my_hnsw_cosine_index │
│ │
│ Projections: vec │
│ │
│ ~729 Rows │
└───────────────────────────┘
array_distanceの場合でもEXPLAINしてみる。
EXPLAIN
SELECT *
FROM my_vector_table
ORDER BY array_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
出力
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ #0 │
│ │
│ ~3 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ vec │
│ NULL │
│ │
│ ~729 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HNSW_INDEX_SCAN │
│ ──────────────────── │
│ Table: │
│ my_vector_table │
│ │
│ HSNW Index: │
│ my_hnsw_index │
│ │
│ Projections: vec │
│ │
│ ~729 Rows │
└───────────────────────────┘
一応使い分けはできてるみたい。
ちょっとひねったことをやってみる。一旦コサイン類似度距離を使っているインデックスを削除
DROP INDEX my_hnsw_cosine_index;
.indexes my_vector_table
出力
my_hnsw_index
array_cosine_distanceを使って検索
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
一応検索は返ってくるのだけど・・・
出力
┌─────────────────┐
│ vec │
│ float[3] │
├─────────────────┤
│ [2.0, 4.0, 6.0] │
│ [1.0, 2.0, 3.0] │
│ [3.0, 6.0, 9.0] │
└─────────────────┘
EXPLAINしてみる
EXPLAIN
SELECT *
FROM my_vector_table
ORDER BY array_cosine_distance(vec, [1, 2, 3]::FLOAT[3])
LIMIT 3;
出力
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ #0 │
│ │
│ ~3 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ TOP_N │
│ ──────────────────── │
│ Top: 3 │
│ Order By: #1 ASC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ vec │
│ array_cosine_distance(vec,│
│ [1.0, 2.0, 3.0]) │
│ │
│ ~729 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ ──────────────────── │
│ Table: │
│ my_vector_table │
│ │
│ Type: Sequential Scan │
│ Projections: vec │
│ │
│ ~729 Rows │
└───────────────────────────┘
インデックスが使用されていないことがわかる。
まあこういうことがあるので、あんまり凝ったこと・複雑なことはしないほうがいいかなという気がする。
kun432インデックスオプション
metric 以外にも、インデックス構築・検索のハイパーパラメータを制御するオプションがある。
オプション デフォルト 説明 ef_construction128 インデックス構築時に検討する候補頂点数。値を大きくすると精度が向上するが作成時間も増加する。 ef_search64 検索時に検討する候補頂点数。値を大きくすると精度が向上するが検索時間も増加する。 M16 各頂点が保持する最大近傍数。値を大きくすると精度が向上するが作成時間も増加する。 M02 * Mグラフの 0 階層で保持する近傍数。値を大きくすると精度が向上するが作成時間も増加する。 実行時に
SET hnsw_ef_search = intを設定すると、インデックス作成時のef_searchを上書きできます。精度と速度のトレードオフを接続単位で調整したい場合に便利です。解除はRESET hnsw_ef_searchで行えます。
kun432永続化
ここはちょっとドキュメント読んで気になったところ。
カスタム拡張インデックスの永続化に関する既知の問題により、
HNSWインデックスは既定でインメモリデータベースにのみ作成可能です。オンディスク環境で使用するにはSET hnsw_enable_experimental_persistence = trueを設定してください。この機能が実験的フラグで保護されている理由は、WAL リカバリが未実装のため、クラッシュや予期せぬシャットダウンが発生すると インデックスのデータ損失または破損 が起こり得るためです。
予期しないシャットダウン後は、DuckDB を別プロセスで起動し
vss拡張をロードしたうえでデータベースをATTACHし、WAL 再生中にHNSW機能を有効化してリカバリを試みてください。ただし本番環境での使用は推奨しません。
hnsw_enable_experimental_persistenceを有効にすると、インデックスがデータベースファイルに保存されるため、再起動後に再構築せずともメモリにロードできます。ただしチェックポイントごとにインデックス全体が上書き保存され、再起動後は最初のアクセス時に一括逆直列化されます。インデックスが大きい場合、逆直列化に時間を要することがありますが、再作成よりは高速です。
んー、書き込みに関しては現状はまだ信頼性が厳しい感じか。インデックスを作成してきちんとチェックポイントを実行しておけば、読み取り専用・検索のみのポータブルなデータベースとして配布とかは可能かな。
kun432ということでEmbeddingモデルと組み合わせてみる。以下を使用する。
以前軽く試した記事
こんな感じで。
!pip install "sentence-transformers>=3.3.1" duckdb pandas
インデックスの作成
import pandas as pd
from sentence_transformers import SentenceTransformer
model_name = "hotchpotch/static-embedding-japanese"
model = SentenceTransformer(model_name, device="cpu")
docs = [
"木の温もりあふれるブックカフェで、自家焙煎の深煎りコーヒーと季節のタルトを味わいながら、窓辺から路面電車をのんびり眺められるんだ。",
"庭にハーブが茂るガーデンカフェでは、ハンドドリップの浅煎りとフレッシュハーブティーが選べて、小鳥のさえずりが BGM 代わりになるよ。",
"港直送の鯖を炙りしめ鯖にしてくれる専門店、皮目の香ばしさと酢のきりりとした酸味が口いっぱいに広がるんだ。",
"カウンター割烹の金目鯛の煮付けは、甘辛ダレが骨の隅々まで染みていて、白ご飯が思わずおかわり必至だよね。",
"昔ながらの屋台ラーメンは鶏ガラの澄んだ醤油スープと細ちぢれ麺が相性抜群で、深夜の胃袋にしみるんだ。",
"真っ白な豚骨スープに焦がしニンニク油をひと垂らしした濃厚ラーメン、替え玉が無料でつい無限ループしてしまうよ。",
"スリランカ式の混ぜて食べるプレートカレーでは、15種類のスパイスが複雑に重なって食べ進めるほど香りが花開くんだ。",
"野菜がごろごろ入った欧風ビーフカレーは、赤ワインとバターのコクが効いたシャバっとルウで後を引くよ。",
"薪窯ナポリピッツァのマルゲリータは、モッツァレラがびよーんと伸びて焼き立てを頬張る瞬間がたまらない。",
"４種のチーズをのせたクアトロフォルマッジに蜂蜜を垂らすスタイルが人気で、塩気と甘さのコントラストがクセになるんだ。",
"しゅわっととろけるバスクチーズケーキ専門店、表面の香ばしい焦げと濃厚クリーミーな中身のギャップが病みつきになるよ。",
"パリパリの薄皮たい焼きは羽根つきで端っこまで香ばしく、黒あんか白あんか毎回真剣に迷っちゃうんだよね。",
]
df = pd.DataFrame({
"id": range(1, len(docs) + 1),
"text": docs,
"text_vector": list(model.encode(docs))
})
import duckdb
con = duckdb.connect(
"docs.db",
config={"hnsw_enable_experimental_persistence": True},
)
con.install_extension("vss")
con.load_extension("vss")
con.register("df", df)
con.sql("""
CREATE OR REPLACE TABLE docs AS
SELECT
id,
text,
text_vector::FLOAT[1024] AS text_vector,
FROM df
""")
con.sql("""
CREATE INDEX my_docs_hnsw
ON docs
USING HNSW (text_vector)
WITH (metric = 'cosine');
""")
で検索
query = "中華そばが食べたい"
query_vector = model.encode(query)
sql = """\
SELECT
id,
text,
array_cosine_distance(?::FLOAT[1024], text_vector) AS distance
FROM docs
ORDER BY distance
LIMIT 5;
"""
result = con.execute(sql, [query_vector]).fetchall()
result
出力
[(5, '昔ながらの屋台ラーメンは鶏ガラの澄んだ醤油スープと細ちぢれ麺が相性抜群で、深夜の胃袋にしみるんだ。', 0.6416981220245361),
(6,
'真っ白な豚骨スープに焦がしニンニク油をひと垂らしした濃厚ラーメン、替え玉が無料でつい無限ループしてしまうよ。',
0.7051510810852051),
(7,
'スリランカ式の混ぜて食べるプレートカレーでは、15種類のスパイスが複雑に重なって食べ進めるほど香りが花開くんだ。',
0.7992098331451416),
(3,
'港直送の鯖を炙りしめ鯖にしてくれる専門店、皮目の香ばしさと酢のきりりとした酸味が口いっぱいに広がるんだ。',
0.8405280113220215),
(12,
'パリパリの薄皮たい焼きは羽根つきで端っこまで香ばしく、黒あんか白あんか毎回真剣に迷っちゃうんだよね。',
0.8705376982688904)]
ドキュメントを見ると、array_cosine_similarity()とかもある。
array_cosine_similarity()だとこう。
query = "中華そばが食べたい"
query_vector = model.encode(query)
sql = """\
SELECT
id,
text,
array_cosine_similarity(?::FLOAT[1024], text_vector) AS distance
FROM docs
ORDER BY distance DESC
LIMIT 5;
"""
result = con.execute(sql, [query_vector]).fetchall()
result
出力
[(5, '昔ながらの屋台ラーメンは鶏ガラの澄んだ醤油スープと細ちぢれ麺が相性抜群で、深夜の胃袋にしみるんだ。', 0.3583018481731415),
(6,
'真っ白な豚骨スープに焦がしニンニク油をひと垂らしした濃厚ラーメン、替え玉が無料でつい無限ループしてしまうよ。',
0.2948489189147949),
(7,
'スリランカ式の混ぜて食べるプレートカレーでは、15種類のスパイスが複雑に重なって食べ進めるほど香りが花開くんだ。',
0.2007901668548584),
(3,
'港直送の鯖を炙りしめ鯖にしてくれる専門店、皮目の香ばしさと酢のきりりとした酸味が口いっぱいに広がるんだ。',
0.15947197377681732),
(12,
'パリパリの薄皮たい焼きは羽根つきで端っこまで香ばしく、黒あんか白あんか毎回真剣に迷っちゃうんだよね。',
0.12946228682994843)]
他にも全文検索や、全文検索・ベクトル検索のハイブリッドなんかもできるみたい
参考
kun432まとめ
自分はSQLを過去それほど深く使ってこなかったのだけど、それでもDuckDBはかなり便利で使いやすいと感じたし、ベクトル検索については非常に直感的に使えて好印象。個人的に体験がとても良かったので、今後積極的に使っていきたいと考えている。
このスクラップは2ヶ月前にクローズされました