SQLite でのデータローディング
意外と嵌ったのでメモ。
SQLite でデータローディングを行おうとした。対象となるデータとテーブルの条件は以下の通り。
- データ件数 200万ちょっと
- ローディングするデータは、タブ区切りフォーマット
- ローディング対象のテーブルはプリイマリキーがオートインクリメントとなっている構造
最初はアプリでデータを加工しつつ、ローディングしようとしたのだが、あまりに時間がかかり過ぎるのが判明。。
先に加工したデータを SQLite のデータローディング機能を使ってローディングすることにした。
そこで、上記の 2 と 3 でちょっと嵌ってしまった。
使用した SQLite は以下のバージョン。
$ sqlite3 --version
3.6.22
ローディングデータにタブ区切りフォーマットを使用する方法
コマンドラインでローディングする際の構文は、以下の通りになる。
$ sqlite3 -separator <セパレータ> ".import <ローディング対象データ> <ローディング対象テーブル>"
SQLite3 のデフォルトのセパレータは、**"`|`"** になっているので、セパレータに **"`\t`"** を指定してあげればスンナリいくものだと思ったが、この指定では正しく解釈してくれない。
では、と、直接タブ文字を入れ込もうとしたが、Shell の補完機能が効いてしまい、うまくいかない。
最終的には、Shell スクリプトにして直接タブ文字を入力することでうまくいった。
**`load.sh`:**
#!/bin/bash
sqlite3 -separator " " test.db.sqlite3 ".import test_data.tsv t1"
**【2011/01/30 追記】:**
と、書いたのだが、コマンドラインでもちゃんといけた。上記の例であれば、
$ sqlite3 -separator $'\t' test.db.sqlite3 ".import test_data.tsv t1"
### 対象テーブルのプリイマリキーがオートインクリメントになっている場合のローディング方法 ###
結局テンポラリテーブルを使う方法で対処した。
対象テーブルの構造は以下の通りとなっている。
sqlite> .schema t1
CREATE TABLE "t1" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "word" text, "content" text);
CREATE INDEX "index_t1_on_word" ON "t1" ("word");
インポートするデータには、上記の word と content の2つの項目しかない。
一旦、ローディング用のテンポラリテーブルを用意し、そのテーブルにデータをインポートする。
sqlite> create table t1_tmp(word TEXT, content TEXT)
先ほどの Shell スクリプトを使って、t1_tmp にデータをローディング。
**`load.sh`:**
#!/bin/bash
sqlite3 -separator " " test.db.sqlite3 ".import test_data.tsv t1_tmp"
$ ./load.sh
ローディングが完了したら、テンポラリテーブルから実際にローディングを行いたかったテーブルにデータを移してあげる。以下の insert 文を走らせる。
sqlite> insert into t1(word, content) select word, content from t1_tmp;
これで、`t1.id` にもちゃんとオートインクリメントされた id がふられている。
テンポラリテーブルはもう必要ないので削除しておく。
sqlite> drop table t1_tmp;
若干バッドノウハウ的な感じだが、もっとスマートな方法が見つかったら、今度からはそちらを利用しよう。
しかし、200 MByte で 200 万ちょっとのデータ件数のデータを1分かかるかかからないかでローディングした。なかなか速い。