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

Written by @dr_taka_n at 2011/01/03 14:27 [, ]

意外と嵌ったのでメモ。

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

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

最初はアプリでデータを加工しつつ、ローディングしようとしたのだが、あまりに時間がかかり過ぎるのが判明。。 先に加工したデータを 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分かかるかかからないかでローディングした。なかなか速い。
blog comments powered by Disqus