アヒルとビーバーと遊ぼう
なんとなくいいたくなっただけです
DuckDB
2024.6 に1.0.0 リリース ホットで軽快なデータベース
ちなみに 最初のリリース 0.1.0 は 2019.06
- 分散型なので SQLite っぽい。
- 書き込みよりも分析につよい。（現在書き込みはマルチユーザー非対応）
- Mac / Win / Linux どこでもうごかせる
- 開発言語もメジャーなのにはほぼほぼ対応
- WASM で ブラウザの中で 軽快に動く！
- spatial に対応
といいとこたくさんです。
この週末は、国土交通省の Project LINKS : DATA Hackathon にメンターとしてお手伝いに来ていて、なにかさくっとデータをあれこれできる方法を整理整頓です。
Foursquare さんからヤバイ1億件超のオープンデータが
2024.11.19
すごいのでました。これをアヒルくん (DuckDB) にたくさんたべてもらいます。
DuckDB
CLI (コマンドラインツール) をインストール
Mac
$ brew install duckdb
Windows
$ winget install DuckDB.cli
もしかしたらインストール後に、シェルの再起動など必要になるかもしれない。
そのあとは、Mac でも Windows でも
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
で、duckdb が起動し、duckdb cli のプロンプト 'D' が表示されます。
上記の場合は、インメモリーな（データが永続的に保存されない）データベースとして動き
$ duckdb ファイル名
$ duckdb test.duckdb
としたら、そのファイルにデータが保存されます。
プロンプト環境をおわらせるには、
D .q
が、quit になります。
なお、わたしの環境は Mac なので Windows 環境の方はパスの指定方法とかすこし異なりますがご注意を。
とにかく読み込もう
FSQ (Foursquare) が公開されているデータは AWS S3 にあります。
ダウンロードするために、適当に AWS CLI もインストールしてください。
Mac は
$ brew install awscli
とかでもさくっとインストールされるかもしれません
$ aws s3 ls s3://fsq-os-places-us-east-1/release/dt=2024-11-19/places/parquet/ --no-sign-request
2024-11-19 07:39:15 454930222 places-00000.snappy.parquet
2024-11-19 07:39:15 454946591 places-00001.snappy.parquet
2024-11-19 07:39:15 454994932 places-00002.snappy.parquet
2024-11-19 07:39:15 455300825 places-00003.snappy.parquet
2024-11-19 07:39:15 455040084 places-00004.snappy.parquet
2024-11-19 07:39:25 455183369 places-00005.snappy.parquet
2024-11-19 07:39:27 455185872 places-00006.snappy.parquet
2024-11-19 07:39:27 455039163 places-00007.snappy.parquet
2024-11-19 07:39:27 454742309 places-00008.snappy.parquet
2024-11-19 07:39:28 454955652 places-00009.snappy.parquet
2024-11-19 07:39:33 454754113 places-00010.snappy.parquet
2024-11-19 07:39:41 455093317 places-00011.snappy.parquet
2024-11-19 07:39:42 455188841 places-00012.snappy.parquet
2024-11-19 07:39:42 455088907 places-00013.snappy.parquet
2024-11-19 07:39:44 455145229 places-00014.snappy.parquet
2024-11-19 07:39:46 455363068 places-00015.snappy.parquet
2024-11-19 07:39:53 455010209 places-00016.snappy.parquet
2024-11-19 07:39:55 455174648 places-00017.snappy.parquet
2024-11-19 07:39:55 455149050 places-00018.snappy.parquet
2024-11-19 07:39:57 454879910 places-00019.snappy.parquet
2024-11-19 07:39:59 455336909 places-00020.snappy.parquet
2024-11-19 07:40:05 455164747 places-00021.snappy.parquet
2024-11-19 07:40:08 454988566 places-00022.snappy.parquet
2024-11-19 07:40:08 454895725 places-00023.snappy.parquet
2024-11-19 07:40:09 454771357 places-00024.snappy.parquet
約1億件のデータが、450MB くらいずつの 25ファイルにわかれて公開されています。
データ形式はこれも最近ぐっとくる Apache Parquet 形式ですね。
※ アパッチ・パーキー？ パーケット？ パルケ？ 読み方は諸説あるようです。
S3 から直接 DuckDB に import することもできるのですが、大きなデータで時間もかかってしまうので、ダウンロードしておきましょう
$ aws s3 cp s3://fsq-os-places-us-east-1/release/dt=2024-11-19/places/parquet/ ./ --no-sign-request --recursive
# aws s3 で コピーします s3にあるfsqのファイルを ローカルのカレントディレクトリに 認証
ネット接続環境によりますが、約10GBのダウンロードなので、そこそこ時間はかかるかと思います。
それでは DuckDB をたちあげてすすめましょう
$ duckdb # とりあえずインメモリー型でいいかな
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D -- ダウンロードしたファイルに含まれれるデータ件数を確認
D --ファイル名の指定には ワイルドカード (*) を使えます
D SELECT count(*) from './*.parquet';
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 104511073 │
└──────────────┘
1億件ありますね。
中身を見てみましょう
D SELECT * from './*.parquet'; --hoge
100% ▕████████████████████████████████████████████████████████████▏
┌──────────────────────┬──────────────────────┬─────────────────────┬───┬────────────────┬──────────────────────┬──────────────────────┐
│ fsq_place_id │ name │ latitude │ … │ twitter │ fsq_category_ids │ fsq_category_labels │
│ varchar │ varchar │ double │ │ varchar │ varchar[] │ varchar[] │
├──────────────────────┼──────────────────────┼─────────────────────┼───┼────────────────┼──────────────────────┼──────────────────────┤
│ 4aee4d4688a04abe82… │ Canada House │ 41.78115297385013 │ … │ │ [4bf58dd8d48988d10… │ [Retail > Fashion … │
│ cb57d89eed29405b90… │ Fotos. Zakład foto… │ 52.19266718928583 │ … │ │ [4d4b7105d754a0637… │ [Retail] │
│ 59a4553d112c6c2b6c… │ CoHo │ 40.774559 │ … │ │ [4bf58dd8d48988d11… │ [Dining and Drinki… │
│ 4bea3677415e20a110… │ Bisma lounge │ -6.134261741465453 │ … │ │ │ │
│ dca3aeba404e4b6006… │ Grace Motorworks │ 34.18099230709591 │ … │ │ [52f2ab2ebcbc57f10… │ [Business and Prof… │
│ 505a6a8be4b066ff88… │ 沖縄海邦銀行 国頭… │ 26.73299018147453 │ … │ │ [4bf58dd8d48988d10… │ [Business and Prof… │
│ 542609ee498e58df08… │ Dungklepu │ -8.078449137837312 │ … │ │ │ │
│ 57bd5be5498e99415b… │ на яхте in dubai │ 25.076424 │ … │ │ [4d4b7104d754a0637… │ [Arts and Entertai… │
│ 59455dc74ce06616d2… │ Коло │ 50.46606497526524 │ … │ │ [4bf58dd8d48988d1d… │ [Dining and Drinki… │
│ 51b4638c498e87bc8e… │ Sinar Minang │ -6.1448666216760435 │ … │ │ [4bf58dd8d48988d14… │ [Dining and Drinki… │
│ 1da2248395a84f39e8… │ TAAJ Portugal │ 41.17091125775253 │ … │ │ │ │
│ 5384f5e9498ec1985a… │ Raisbeck │ 4.698453389997753 │ … │ │ [52e81612bcbc57f10… │ [Community and Gov… │
│ 575d5903498e64452b… │ Jake's Fireworks │ 33.941390120350626 │ … │ jakesfireworks │ [52f2ab2ebcbc57f10… │ [Retail > Firework… │
│ 5ea07e93d49aa10008… │ SERVICIO DE INGENI… │ -34.2985556 │ … │ │ [5454144b498ec1f09… │ [Retail > Construc… │
│ 50755c76e4b07ef70e… │ Lunchroom Mepavex │ 51.49698189963533 │ … │ │ [4eb1d5724b900d56c… │ [Dining and Drinki… │
│ 4d573caac6edf04d01… │ Royal Ballroom │ -6.6024520915641824 │ … │ │ │ │
│ 5139b302e4b0de6b85… │ Bighome │ 13.864113 │ … │ │ [4bf58dd8d48988d12… │ [Business and Prof… │
│ 4e9cd13982310f9e5f… │ Ruang 2 PKn SMA 1,… │ -3.696803108008636 │ … │ │ [4bf58dd8d48988d13… │ [Community and Gov… │
│ 4b72c7fcf964a5204e… │ brodies │ 55.863021 │ … │ │ │ │
│ 91ea56566a4f45bb3b… │ University of Illi… │ 39.73429648873096 │ … │ │ [4d4b7105d754a0637… │ [Community and Gov… │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ 5b59e3df464d65002c… │ R & T Asesores │ 42.46605370104558 │ … │ │ [5453de49498eade8a… │ [Business and Prof… │
│ 50c3875de4b025007f… │ Mini tienda Marthita │ -2.21447372436523 │ … │ │ [4bf58dd8d48988d11… │ [Retail > Bookstore] │
│ 7ae737001e18012f48… │ Ted's Family Hair … │ 42.37935306404939 │ … │ │ [4bf58dd8d48988d11… │ [Business and Prof… │
│ 53bc8c9c498e8b213a… │ TPS 38 │ -8.662266214811599 │ … │ │ │ │
│ 56ec7f46498e49e34a… │ Insomni'hack 2016 │ 46.23495276496341 │ … │ │ [5267e4d9e4b0ec794… │ [Event > Conference] │
│ d29929553b1249954a… │ Skeppsdockans Vand… │ 58.491869 │ … │ │ [4bf58dd8d48988d1e… │ [Travel and Transp… │
│ 5cdc43da58002c002c… │ Rhea Silvia Wine &… │ 42.655194 │ … │ │ [4bf58dd8d48988d1d… │ [Dining and Drinki… │
│ 5e3a01fd38effb0008… │ Outback Steakhouse │ -1.4463438086263094 │ … │ │ [4bf58dd8d48988d1c… │ [Dining and Drinki… │
│ 4bd78db35cf276b0bf… │ Bank Street Manor │ 40.737236907201115 │ … │ │ [4d4b7104d754a0637… │ [Arts and Entertai… │
│ 529b2bc3498eee0be9… │ Loveat Restaurant │ 3.6855415661926685 │ … │ │ [4bf58dd8d48988d16… │ [Dining and Drinki… │
│ 4da5d616cda1c55f75… │ Delícias do Sertão │ -3.727898625917249 │ … │ │ [4bf58dd8d48988d16… │ [Dining and Drinki… │
│ 6276bde4f205340a81… │ Erda Rooms │ 41.067098 │ … │ │ [4bf58dd8d48988d1f… │ [Travel and Transp… │
│ 6329be3d57f7a9429a… │ Kuwaiti Design │ 29.264052 │ … │ │ [5fac002599ce226e2… │ [Business and Prof… │
│ 52a3619e498e4e88e0… │ Waroeng Surabaya │ 1.210887 │ … │ │ │ │
│ 4b81bc6ff964a520ee… │ Cash America │ 29.416439056396484 │ … │ cashamerica │ [4bf58dd8d48988d1f… │ [Retail > Miscella… │
│ 6061cf00e6971d1ebb… │ Vodafone Laleli │ 41.011815 │ … │ │ [4f04afc02fb6e1c99… │ [Retail > Computer… │
│ 4f282b4ce4b052a5c8… │ Fussballplatz Walc… │ 47.66120302115233 │ … │ │ [4cce455aebf7b749d… │ [Sports and Recrea… │
│ 4bef2c712ff520a18f… │ 어부의집 │ 37.665378 │ … │ │ [4bf58dd8d48988d11… │ [Dining and Drinki… │
│ 50347318e4b0514bf4… │ The Salt Mines │ 3.1569516671837183 │ … │ │ [4bf58dd8d48988d12… │ [Business and Prof… │
│ 4df9332462e1e9a243… │ Skaneateles Lake │ 42.88264583421452 │ … │ │ [4bf58dd8d48988d16… │ [Landmarks and Out… │
├──────────────────────┴──────────────────────┴─────────────────────┴───┴────────────────┴──────────────────────┴──────────────────────┤
│ 104511073 rows (40 shown) 23 columns (6 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
表形式で表示されます。
ここまで、まだデータファイルは parquet 形式のままで、duckdb の inmemory dbにキープされていません。
CREATE TABLE テーブル名 as することで、キープされます。
-- places テーブルにインポートするような操作
D CREATE TABLE places as SELECT * from './*.parquet';
31% ▕██████████████████▌ ▏
100% ▕████████████████████████████████████████████████████████████▏
-- テーブル構造を確認
D DESCRIBE places;
┌─────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ fsq_place_id │ VARCHAR │ YES │ │ │ │
│ name │ VARCHAR │ YES │ │ │ │
│ latitude │ DOUBLE │ YES │ │ │ │
│ longitude │ DOUBLE │ YES │ │ │ │
│ address │ VARCHAR │ YES │ │ │ │
│ locality │ VARCHAR │ YES │ │ │ │
│ region │ VARCHAR │ YES │ │ │ │
│ postcode │ VARCHAR │ YES │ │ │ │
│ admin_region │ VARCHAR │ YES │ │ │ │
│ post_town │ VARCHAR │ YES │ │ │ │
│ po_box │ VARCHAR │ YES │ │ │ │
│ country │ VARCHAR │ YES │ │ │ │
│ date_created │ VARCHAR │ YES │ │ │ │
│ date_refreshed │ VARCHAR │ YES │ │ │ │
│ date_closed │ VARCHAR │ YES │ │ │ │
│ tel │ VARCHAR │ YES │ │ │ │
│ website │ VARCHAR │ YES │ │ │ │
│ email │ VARCHAR │ YES │ │ │ │
│ facebook_id │ BIGINT │ YES │ │ │ │
│ instagram │ VARCHAR │ YES │ │ │ │
│ twitter │ VARCHAR │ YES │ │ │ │
│ fsq_category_ids │ VARCHAR[] │ YES │ │ │ │
│ fsq_category_labels │ VARCHAR[] │ YES │ │ │ │
├─────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 23 rows 6 columns │
└───────────────────────────────────────────────────────────────────────────┘
-- 10件だけデータ内容を確認
D D select * from places limit 10;
┌──────────────────────┬──────────────────────┬─────────────────────┬───┬─────────┬──────────────────────┬──────────────────────┐
│ fsq_place_id │ name │ latitude │ … │ twitter │ fsq_category_ids │ fsq_category_labels │
│ varchar │ varchar │ double │ │ varchar │ varchar[] │ varchar[] │
├──────────────────────┼──────────────────────┼─────────────────────┼───┼─────────┼──────────────────────┼──────────────────────┤
│ 4aee4d4688a04abe82… │ Canada House │ 41.78115297385013 │ … │ │ [4bf58dd8d48988d10… │ [Retail > Fashion … │
│ cb57d89eed29405b90… │ Fotos. Zakład foto… │ 52.19266718928583 │ … │ │ [4d4b7105d754a0637… │ [Retail] │
│ 59a4553d112c6c2b6c… │ CoHo │ 40.774559 │ … │ │ [4bf58dd8d48988d11… │ [Dining and Drinki… │
│ 4bea3677415e20a110… │ Bisma lounge │ -6.134261741465453 │ … │ │ │ │
│ dca3aeba404e4b6006… │ Grace Motorworks │ 34.18099230709591 │ … │ │ [52f2ab2ebcbc57f10… │ [Business and Prof… │
│ 505a6a8be4b066ff88… │ 沖縄海邦銀行 国頭… │ 26.73299018147453 │ … │ │ [4bf58dd8d48988d10… │ [Business and Prof… │
│ 542609ee498e58df08… │ Dungklepu │ -8.078449137837312 │ … │ │ │ │
│ 57bd5be5498e99415b… │ на яхте in dubai │ 25.076424 │ … │ │ [4d4b7104d754a0637… │ [Arts and Entertai… │
│ 59455dc74ce06616d2… │ Коло │ 50.46606497526524 │ … │ │ [4bf58dd8d48988d1d… │ [Dining and Drinki… │
│ 51b4638c498e87bc8e… │ Sinar Minang │ -6.1448666216760435 │ … │ │ [4bf58dd8d48988d14… │ [Dining and Drinki… │
├──────────────────────┴──────────────────────┴─────────────────────┴───┴─────────┴──────────────────────┴──────────────────────┤
│ 10 rows 23 columns (6 shown) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- 表示が省略された列項目を簡単に確認したいときは
D .col
D select * from places limit 10;
┌─────────────────────┬───────────┬──────────────────────┬───┬──────────────────────┬──────────────────────┬──────────────────────┐
│ Column │ Type │ Row 1 │ … │ Row 8 │ Row 9 │ Row 10 │
├─────────────────────┼───────────┼──────────────────────┼───┼──────────────────────┼──────────────────────┼──────────────────────┤
│ fsq_place_id │ varchar │ 4aee4d4688a04abe82… │ … │ 57bd5be5498e99415b… │ 59455dc74ce06616d2… │ 51b4638c498e87bc8e… │
│ name │ varchar │ Canada House │ … │ на яхте in dubai │ Коло │ Sinar Minang │
│ latitude │ double │ 41.78115297385013 │ … │ 25.076424 │ 50.46606497526524 │ -6.1448666216760435 │
│ longitude │ double │ 3.029574057012216 │ … │ 55.141186 │ 30.52368012458723 │ 106.83436302671524 │
│ address │ varchar │ Anselm Clavé, 16 B… │ … │ │ вул. Іллінська, 20 │ │
│ locality │ varchar │ Sant Feliu de Guíx… │ … │ │ Kiev │ │
│ region │ varchar │ Gerona │ … │ │ Kyiv city │ │
│ postcode │ varchar │ 17220 │ … │ │ 04070 │ │
│ admin_region │ varchar │ Cataluña │ … │ │ │ │
│ post_town │ varchar │ │ … │ │ │ │
│ po_box │ varchar │ │ … │ │ │ │
│ country │ varchar │ ES │ … │ AE │ UA │ ID │
│ date_created │ varchar │ 2010-05-09 │ … │ 2016-08-24 │ 2017-06-17 │ 2013-06-09 │
│ date_refreshed │ varchar │ 2024-06-14 │ … │ 2023-06-15 │ 2021-08-14 │ 2024-07-16 │
│ date_closed │ varchar │ │ … │ 2017-08-01 │ 2020-09-19 │ │
│ tel │ varchar │ 972 32 45 54 │ … │ │ 098 756 4513 │ │
│ website │ varchar │ http://www.canadah… │ … │ │ │ │
│ email │ varchar │ │ … │ │ │ │
│ facebook_id │ int64 │ │ … │ │ 1742175026098149 │ │
│ instagram │ varchar │ │ … │ │ │ │
│ twitter │ varchar │ │ … │ │ │ │
│ fsq_category_ids │ varchar[] │ [4bf58dd8d48988d10… │ … │ [4d4b7104d754a0637… │ [4bf58dd8d48988d1d… │ [4bf58dd8d48988d14… │
│ fsq_category_labels │ varchar[] │ [Retail > Fashion … │ … │ [Arts and Entertai… │ [Dining and Drinki… │ [Dining and Drinki… │
├─────────────────────┴───────────┴──────────────────────┴───┴──────────────────────┴──────────────────────┴──────────────────────┤
│ 10 rows (4 shown) 23 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
D -- 表示を元に戻したい時は
D .row
概ねこんな感じですね。あとは SQL の使い方をいろいろ調べましょう。
RDB かわいいよ
DBeaver
コマンドラインでがんばるのもいいけど、GUI があるとちょっとべんりです。
オープンソースの DBeaver が DuckDB に対応しているのでそれをインストールします。
DuckDB の新規作成
新しい接続を選ぼう
DuckDB を選ぼう
新規作成をえらんで、任意の位置に適当なファイル名をつけよう
なんかできた
まだ DuckDB のデータベースはからっぽ
Foursquare のデータを import してみよう
Script を新規作成して、Script を右側にコピペして 実行ボタンをクリック
-- 空間情報対応を on にする
install spatial;
load spatial;
-- places テーブルをつくるのですが、1億件ではなく、JP (日本) のデータだけを取り込む
-- longitude, latigude 列から、geom 型のデータに変換しながら取り込む
create table places as select *, st_point(Longitude, Latitude) AS geom
from '~/Desktop/fsq_dt=2024-11-19/places/parquet/*.parquet' -- パスは環境によって丁寧に
where country='JP';
-- 東京駅の中心ぽい緯度経度から約50mのデータを抽出 50000 / 111000.0 は緯度経度をメートルに雑に換算
select fsq_place_id, name, geom from places
WHERE ST_DWithin(ST_Point(139.7671, 35.6812), geom , 5 / 111000.0);
左側の Tree 型の Panel に places テーブルができていることが確認できます
表示されないときは F5 やマウス右クリックしたりで、「最新の情報に更新」してください
右側の「データ」タブをえらぶと表形式で places テーブル内容を確認できます
表形式の一番右に geom 列があるので、そこをクリックしてみてください
地図上にポイントが表示されます。便利だ！？
geom 列をマウス操作などで複数選択すると、地図上にも複数点が表示されます。便利だ！？
ちなみに・・・
こんな Script にすると、S3 から直接 import することもできます。
時間はそれなりにかかりますが。。。
create table places as
select * from 's3://fsq-os-places-us-east-1/release/dt=2024-11-19/categories/parquet/*.parquet';
せっかくなので位置情報で条件指定した Query もつかってみよう
Script を実行してみましょう
-- 東京駅の中心ぽい緯度経度から約50mのデータを抽出 50000 / 111000.0 は緯度経度をメートルに雑に換算
select fsq_place_id, name, geom from places
WHERE ST_DWithin(ST_Point(139.7671, 35.6812), geom , 5 / 111000.0);
東京駅の中心あたりのデータだけが選択して表示されます。
GeoJSON もためそう
G空間情報センター で公開されている「登記所備付地図データ」の GeoJSON をつかってみましょう
ここから適当な市町村を選ぼう
たとえばここ
ダウンロードしたデータを import しましょう。
ST_READ という関数が、geojson 形式を上手に変換してくれます。
select * from ST_READ('~/Downloads/20362__8_r.geojson');
これもとっても便利です。
追記：空間関連エクステンション ON にするのを自動化
DBeaver 起動して DuckDB に接続してから毎回
install spatial;
LOAD spatial;
するのが面倒です。
接続の設定で、bootstrap (ブートストラップ) のクエリ的なところで次のように設定しておくと自動的に上記スクリプトが実行されるので少し幸せになれます。
まとめ
アヒルとビーバー、とっても便利だ
1億件の処理も軽快
これは command line tool や DBeaver をつかっていますが、duckdb は wasm でも動かせるので、めちゃ楽しいですよ〜〜〜
おまけ QGIS
QDuckDB という Plugin をいれよう
注意点2つあります
- コマンドライン や DBeaver で DuckDB を開いてたら、マルチユーザー対応していないので、QDuckDB でひらけない
- テーブル名に英数字以外の日本語が含まれていたりしたら、現バージョンのは不具合？動かないっぽい
日本のデータ 500万点ほど、描写されました
