PostgreSQL の COPY コマンドと SQL だけで様々なデータをインポートする
2008年 8月 22日 19:01PostgreSQL の COPY コマンドと SQL だけを使って、いろいろなデータをテーブルにインポートする方法についてまとめてみました。
プログラミングが得意で、データベースはあんまり得意じゃないっていう人だと、データをインポートする際に、何でもかんでもゴリゴリとプログラムを書いて済ませてしまうことが多いかと思いますが、COPY コマンドと SQL だけでも結構複雑なデータをインポートすることができたりしますので、簡単に紹介してみます。
シーケンスをインクリメントしながらインポートする
例えば、次のような感じのテーブルと user_no_seq というシーケンスがあったとします。
user_no | user_name | total_score
---------+-----------+-------------
1 | A | 120
2 | B | 130
3 | C | 95
4 | D | 110
(4 rows)
このテーブルに、
user.csv
E,125 F,88 G,115
のような CSV ファイルの内容を追加したいとします。
単純に COPY コマンドが使えないので、プログラムで user.csv ファイルをオープンして、1行づつファイルを読み込みながら、INSERT する。みたいな処理を書きたくなってしまいますが、こういう場合は、一度、別のテーブルにインポートして、その内容を SQL を使って、INSERT する方法が使えます。
具体的には、まず、次のようにテンポラリなテーブルを作ります。このテーブルは CSV ファイルに合わせた内容にします。
CREATE TABLE temp_user (
name text,
score int
);
このテーブルに COPY コマンドを使って、CSV ファイルの内容をそのまま読み込みます。
COPY temp_user FROM '/home/postgres/user.csv' USING DELIMITERS ',';
すると、こんな感じで読み込まれたと思います。
name | score ------+------- E | 125 F | 88 G | 115 (3 rows)
後は、このテーブルの内容を INSERT SELECT で user_info テーブルに INSERT してあげればOKです。具体的には、
INSERT INTO user_info (user_no, user_name, total_score) SELECT nextval('user_no_seq'), name, score FROM temp_user;
みたいに書いて実行すると、
user_no | user_name | total_score
---------+-----------+-------------
1 | A | 120
2 | B | 130
3 | C | 95
4 | D | 110
5 | E | 125
6 | F | 88
7 | G | 115
(7 rows)
という感じで、シーケンスがきちんと付加された E, F, G のデータがインポートできます。
計算しながらインポートする
次は、計算しながらインポートする場合です。
次のように3つの得点が記録された CSV ファイルがあったとして、 user_info テーブルには、この3つの得点の合計点が80点以上のデータを INSERT したいとします。
user2.csv
H,30,40,40 I,45,50,20 J,10,20,40
この場合も先ほどと同じようにテンポラリテーブルを用意します。
CREATE TABLE temp_user2 (
name text,
score1 int,
score2 int,
score3 int
);
テンポラリテーブルに user2.csv の内容をそのままインポート。
COPY temp_user2 FROM '/home/postgres/user2.csv' USING DELIMITERS ',';
こな感じでインポートできます。
name | score1 | score2 | score3 ------+--------+--------+-------- H | 30 | 40 | 40 I | 45 | 50 | 20 J | 10 | 20 | 40 (3 rows)
この内容を先ほどのように INSERT SELECT で user_info に INSERT します。ただし、今回は条件式が付くので、それを WHERE 句に書いておきます。
INSERT INTO user_info (user_no, user_name, total_score) SELECT nextval('user_no_seq'), name, score1 + score2 + score3 FROM temp_user2 WHERE score1 + score2 + score3 >= 80;
そうすると、こんな感じで INSERT できました。
user_no | user_name | total_score
---------+-----------+-------------
1 | A | 120
2 | B | 130
3 | C | 95
4 | D | 110
5 | E | 125
6 | F | 88
7 | G | 115
8 | H | 110
9 | I | 115
(9 rows)
３つの得点の合計が80点以上のみ INSERT という条件のため、J のデータだけインポートされていません。
データ内容によってインポートと削除を行う
さらに、SQL を2回流すことで、インポートと削除も行えたりします。
例えば、次のような CSV ファイル user3.csv があったとします。
user3.csv
K,100,add L,105,add B,130,delete
このファイル中で、3番目の項目が add になっているものは追加し、delete になっているものは削除するようにしてみます。
まずはお約束のテンポラリテーブルの作成です。
CREATE TABLE temp_user3 (
name text,
score int,
mode text
);
データをテンポラリテーブルにそのままインポート。
COPY temp_user3 FROM '/home/postgres/user3.csv' USING DELIMITERS ',';
こんな感じでデータが入ります。
name | score | mode ------+-------+-------- K | 100 | add L | 105 | add B | 130 | delete (3 rows)
あとは、このテンポラリテーブルのデータを使って、INSERT の処理と DELETE の処理を書いて見ます。
まず、INSERT の処理。
INSERT INTO user_info (user_no, user_name, total_score) SELECT nextval('user_no_seq'), name, score FROM temp_user3 WHERE mode = 'add';
次に DELETE の処理。
DELETE FROM user_info WHERE user_name IN (SELECT name FROM temp_user3 WHERE mode = 'delete');
上記2つの SQL を流すと、次のような感じで、INSERT と DELETE ができます。
user_no | user_name | total_score
---------+-----------+-------------
1 | A | 120
3 | C | 95
4 | D | 110
5 | E | 125
6 | F | 88
7 | G | 115
8 | H | 110
9 | I | 115
10 | K | 100
11 | L | 105
(10 rows)
K, L のデータが追加されて、B のデータは削除されているのが分かるかと思います。
テンポラリテーブルを多段にして、INSERT SELECT を駆使すれば、もっと複雑なこともできますので、ぜひ、いろいろ試してみてください。
よほど複雑な処理を行わない限り、たいていのインポート処理は COPY コマンドと SQL だけでサクっとできてしまう場合が多いです。プログラムをゴリゴリ書くより早く済むのでおすすめですね。
