SQLite3 のテーブルに CSV でデータを読み込む

メモリに乗り切らない程度のちょっとした集計をするのに SQLite3 のデータベースを使うのが意外と便利だなーと思っている今日このごろ。サポートされている SQL の構文や関数が少ないするのはネックだけど、手軽さには変えられないという感じ。今回は SQLite3 のテーブルに CSV のデータをインポートする方法について書いておく。

使った環境は以下の通り。

$ sw_vers          
ProductName:    macOS
ProductVersion: 12.1
BuildVersion:   21C52
$ uname -rm
21.2.0 arm64
$ sqlite3 --version
3.36.0 2021-06-18 18:58:49 d24547a13b6b119c43ca2ede05fecaa707068f18c7430d47fc95fb5a2232aapl

下準備

データとして Seaborn の Taxis データセットを使うので、あらかじめダウンロードしておく。このとき、先頭行のヘッダは読み飛ばす。これは、後ほど登場する SQLite3 の .import 命令が、ヘッダを読み飛ばすのに対応していないため。

$ brew install wget
$ wget -O - https://raw.githubusercontent.com/mwaskom/seaborn-data/master/taxis.csv | sed -e "1d" > /tmp/taxis.csv
$ head -n 5 /tmp/taxis.csv
2019-03-23 20:21:09,2019-03-23 20:27:24,1,1.6,7.0,2.15,0.0,12.95,yellow,credit card,Lenox Hill West,UN/Turtle Bay South,Manhattan,Manhattan
2019-03-04 16:11:55,2019-03-04 16:19:00,1,0.79,5.0,0.0,0.0,9.3,yellow,cash,Upper West Side South,Upper West Side South,Manhattan,Manhattan
2019-03-27 17:53:01,2019-03-27 18:00:25,1,1.37,7.5,2.36,0.0,14.16,yellow,credit card,Alphabet City,West Village,Manhattan,Manhattan
2019-03-10 01:23:59,2019-03-10 01:49:51,1,7.7,27.0,6.15,0.0,36.95,yellow,credit card,Hudson Sq,Yorkville West,Manhattan,Manhattan
2019-03-30 13:27:42,2019-03-30 13:37:14,3,2.16,9.0,1.1,0.0,13.4,yellow,credit card,Midtown East,Yorkville West,Manhattan,Manhattan

CSV ファイルにあわせてテーブルの定義を用意する。データベースは taxis.db という名前で作成する。

$ cat << 'EOF' | sqlite3 taxis.db
CREATE TABLE IF NOT EXISTS taxis (
  pickup DATETIME,
  dropoff DATETIME,
  passengers INT,
  distance FLOAT,
  fare FLOAT,
  tip FLOAT,
  tolls FLOAT,
  total FLOAT,
  color TEXT,
  payment TEXT,
  pickup_zone TEXT,
  dropoff_zone TEXT,
  pickup_borough TEXT,
  dropoff_borough TEXT
);
EOF

データの区切り文字をカンマ (,) にしたら .import 命令を使って CSV ファイルをテーブルに読み込む。

$ cat << 'EOF' | sqlite3 taxis.db
.separator ,
.import /tmp/taxis.csv taxis
EOF

ちゃんと読み込まれているか確認しよう。

$ cat << 'EOF' | sqlite3 taxis.db
.headers on
SELECT * FROM taxis LIMIT 5
EOF
pickup|dropoff|passengers|distance|fare|tip|tolls|total|color|payment|pickup_zone|dropoff_zone|pickup_borough|dropoff_borough
2019-03-23 20:21:09|2019-03-23 20:27:24|1|1.6|7.0|2.15|0.0|12.95|yellow|credit card|Lenox Hill West|UN/Turtle Bay South|Manhattan|Manhattan
2019-03-04 16:11:55|2019-03-04 16:19:00|1|0.79|5.0|0.0|0.0|9.3|yellow|cash|Upper West Side South|Upper West Side South|Manhattan|Manhattan
2019-03-27 17:53:01|2019-03-27 18:00:25|1|1.37|7.5|2.36|0.0|14.16|yellow|credit card|Alphabet City|West Village|Manhattan|Manhattan
2019-03-10 01:23:59|2019-03-10 01:49:51|1|7.7|27.0|6.15|0.0|36.95|yellow|credit card|Hudson Sq|Yorkville West|Manhattan|Manhattan
2019-03-30 13:27:42|2019-03-30 13:37:14|3|2.16|9.0|1.1|0.0|13.4|yellow|credit card|Midtown East|Yorkville West|Manhattan|Manhattan