SQLite でのデータローディング

意外と嵌ったのでメモ。

SQLite でデータローディングを行おうとした。対象となるデータとテーブルの条件は以下の通り。

1. データ件数 200 万ちょっと
2. ローディングするデータは、タブ区切りフォーマット
3. ローディング対象のテーブルはプリイマリキーがオートインクリメントとなっている構造

最初はアプリでデータを加工しつつ、ローディングしようとしたのだが、あまりに時間がかかり過ぎるのが判明。。 先に加工したデータを SQLite のデータローディング機能を使ってローディングすることにした。
そこで、上記の 2 と 3 でちょっと嵌ってしまった。

使用した SQLite は以下のバージョン。

$ sqlite3 --version
3.6.22

Table of Contents

ローディングデータにタブ区切りフォーマットを使用する方法

コマンドラインでローディングする際の構文は、以下の通りになる。

$ sqlite3 -separator <セパレータ> <DB ファイル名> ".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 分かかるかかからないかでローディングした。なかなか速い。