One-liner for running queries against CSV files with SQLite という記事で紹介されていた、ワンライナーを使って CSV ファイルに対して SQL クエリを実行する方法。
$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
ソース元に記述されてる通り、taxi.csv を GitHub からダウンロードして試すことができる。7z で圧縮されていて解凍する必要がある。
SQLite は通常ストレージディスク（SSD とか）上に保存して利用するのが一般的だが、SQLite 側で用意されている特別なファイル名 :memory: を指定して開くことでデータをインメモリで扱うことができる。これについては SQLite の In-Memory Databases
[1] のページで紹介されている。
taxi.csv の中身はこんな感じの CSV ファイル。
{/_ more _/}
VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount,congestion_surcharge
1,2021-04-01 00:00:18,2021-04-01 00:21:54,1,8.40,1,N,79,116,1,25.5,3,0.5,5.85,0,0.3,35.15,2.5
1,2021-04-01 00:42:37,2021-04-01 00:46:23,1,.90,1,N,75,236,2,5,3,0.5,0,0,0.3,8.8,2.5
1,2021-04-01 00:57:56,2021-04-01 01:08:22,1,3.40,1,N,236,168,2,11.5,3,0.5,0,0,0.3,15.3,2.5
1,2021-04-01 00:01:58,2021-04-01 00:54:27,1,.00,1,N,47,61,1,44.2,0,0.5,0,0,0.3,45,0
始めの -cmd '.mode csv' -cmd '.import taxi.csv taxi' を指定することで taxi というテーブルを作成し、CSV 形式としてファイルの中身をテーブルにインポートするらしい。実際にテーブルスキーマを確認すると taxi テーブルが作成されていることがわかる。
$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' '.schema taxi'
CREATE TABLE IF NOT EXISTS "taxi"(
"VendorID" TEXT,
"tpep_pickup_datetime" TEXT,
"tpep_dropoff_datetime" TEXT,
"passenger_count" TEXT,
"trip_distance" TEXT,
"RatecodeID" TEXT,
"store_and_fwd_flag" TEXT,
"PULocationID" TEXT,
"DOLocationID" TEXT,
"payment_type" TEXT,
"fare_amount" TEXT,
"extra" TEXT,
"mta_tax" TEXT,
"tip_amount" TEXT,
"tolls_amount" TEXT,
"improvement_surcharge" TEXT,
"total_amount" TEXT,
"congestion_surcharge" TEXT
);
ソース記事にも記述されているが、そのまま実行するとカラムの情報が表示されず CSV フォーマットとして結果のみ出力される。
"",128020,32.2371511482553
0,42228,17.0214016766151
1,1533197,17.6418833067999
2,286461,18.0975870711456
3,72852,17.9153958710923
4,25510,18.452774990196
5,50291,17.2709248175672
6,32623,17.6002964166367
7,2,87.17
8,2,95.705
9,1,113.6
上記コマンドに -cmd '.mode column' を指定するとカラム名と一緒に SQL の見慣れたフォーマットとして出力される。
$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode column' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
passenger_count COUNT(*) AVG(total_amount)
--------------- -------- -----------------
128020 32.2371511482553
0 42228 17.0214016766151
1 1533197 17.6418833067999
2 286461 18.0975870711456
3 72852 17.9153958710923
4 25510 18.452774990196
5 50291 17.2709248175672
6 32623 17.6002964166367
7 2 87.17
8 2 95.705
9 1 113.6
また、-cmd '.mode json' とすることで JSON 形式で出力することもできる。
$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' -cmd '.mode json' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
[{"passenger_count":"","COUNT(*)":128020,"AVG(total_amount)":32.23715114825532968},
{"passenger_count":"0","COUNT(*)":42228,"AVG(total_amount)":17.021401676615067088},
{"passenger_count":"1","COUNT(*)":1533197,"AVG(total_amount)":17.641883306799908126},
{"passenger_count":"2","COUNT(*)":286461,"AVG(total_amount)":18.097587071145646575},
{"passenger_count":"3","COUNT(*)":72852,"AVG(total_amount)":17.915395871092314905},
{"passenger_count":"4","COUNT(*)":25510,"AVG(total_amount)":18.452774990196012083},
{"passenger_count":"5","COUNT(*)":50291,"AVG(total_amount)":17.270924817567234299},
{"passenger_count":"6","COUNT(*)":32623,"AVG(total_amount)":17.600296416636713736},
{"passenger_count":"7","COUNT(*)":2,"AVG(total_amount)":87.170000000000005258},
{"passenger_count":"8","COUNT(*)":2,"AVG(total_amount)":95.704999999999991189},
{"passenger_count":"9","COUNT(*)":1,"AVG(total_amount)":113.59999999999998987}]
面白い。出力できる mode はこれだけある。markdown フォーマットとしても出力可能とのこと。
$ sqlite3 -cmd '.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)
html HTML <table> code
insert SQL insert statements for TABLE
json Results in a JSON array
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
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
これは virtual table API を利用して実現されているらしい。この API は全文検索でも利用されているっぽい。 ↩︎