CSVファイルをPostgreSQLに取り込みたい

Question

【PostgreSQL】8.3

10万行位のCSVファイルをPostgreSQLのテーブルに取り込みたいです。
このファイルには重複したデータがあることを考慮した作りにしたいです。重複しなければ普通にインサートして、重複時はアップデートをしたいです。
データが多いので高速の処理をしたいです。

Answer 1

COPY FROMで一時テーブル（TEMPORARY）に取り込んで、そのテーブルからINSERT ON CONFLICTでUPSERTするのが高速だと思われます。

PostgreSQLでCSVからUpdateをかける
PostgreSQLのUPSERT方式の簡易性能比較

追記

サンプル追記

-- Create tmp table
create temp table tmp (id int, day date,value varchar(20)) on commit drop;

-- Import data from csv:
copy tmp from 'C:/test.csv' delimiter ',' csv header;

-- IDが重複しているもののうち最新の日付以外を削除
delete from tmp as target where day < (select max(day) from tmp where id=target.id);

-- Update table from tmp
update import_test_tbl set
  id = tmp.id
, date = tmp.day
, value = tmp.value
from tmp
where import_test_tbl.id = tmp.id
;
-- Insert table from tmp
insert into import_test_tbl
select id, day, value
from tmp
where not exists(	--キーが存在していないもの
       select 1 from import_test_tbl where id = tmp.id
      )
;

Comments

[silversink]

＞sazi様
現状報告です。
お時間ありましたらご教授お願い致します。

①
INSERT ON CONFLICT　は【PostgreSQL】8.3 は非対応でしょうか？
INSERTが出来なくで調べている最中です。
②
updateは「PostgreSQLでCSVからUpdateをかける」の通りに
「create temp table～」で確認出来ました。
➂
1つのcsvファイルに複数のIDがある場合、上書きは可能でしょうか？
(テーブルで既にあるIDをcsvファイルからupdateは出来ました。)

[sazi]

①9.5からの機能なので、8.3ではできません
②ON COMMIT DROP しておくとDROPする必要がありません
③CSVファイルではキー重複しているということですか？
8.3ならINSERTとUPDATEは別に行う必要があるので、集約も若しくは最終のデータを選別してから行った方が簡潔になるかと。

[silversink]

③CSVファイルではキー重複しているということですか？
→はい。キー重複した処理を想定しております。

8.3ならINSERTとUPDATEは別に行う必要があるので、
集約も若しくは最終のデータを選別してから行った方が簡潔になるかと。

下記がUPDATEを実際に行っているものなのですが、
これにINSERT処理を入れるとなると可能でしょうか？
可能であればどのようにするかサンプルを頂けないでしょうか？
宜しくお願い致します。

/-------------------------------------------------------------/

-- Create tmp table
create temp table tmp (id int, day date,value varchar(20) );

-- Import data from csv:
copy tmp from 'C:/test.csv' delimiter ',' csv header;

-- Update table from tmp
update
import_test_tbl
set
id = tmp.id
,date = tmp.day
,value = tmp.value
from
tmp
where
import_test_tbl.id = tmp.id;

--自動で削除
drop table tmp;

[sazi]

サンプルを追記しました。
重複しているものの削除部分は、あくまで思いつく処理ですので、実態に合わせてください。
tmpにIDの重複が無くなった状態で、updateとinsertを行います。

[akirafudo6]

csvの段階で、重複しているレコードは集約しておくこと。あと、ｉｎｓｅｒｔするテーブルにインデックスを張っている場合には、ｄｒｏｐ　ｉｎｄｅｘでインデックスを削除し、ｉｎｓｅｒｔが終わってから、ｃｒｅａｔｅ　ｉｎｄｅｘでインデックスを作成したほうが高速になると思います。
他、注意することとして、１０万件ということですが、一度にできるかは疑問です。
データベースのロックリストとか割り当てているメモリなど資源がパンクする可能性があると思うので、
いくつか分割して、ｉｎｓｅｒｔしたほうが良いと思います。

[sazi]

>akirafudo6さん
１０万件程度なら資源的には1Gb未満でしょう（ひょっとすると１Mbも行かないかも）
ノートPCでも数百万件のCSVを取込めたりしますし。

[akirafudo6]

そのような環境であれば問題ないと思います。
家の環境では、２５０件ずつinsertしました。数千件やろうとしたらメモリ不足で落ちた！（たぶんpostgres.confの設定）

[sazi]

>akirafudo6さん
そういう環境なら設定の方を見直しですね。
大量件数で分割COMMITは有効ですけど、数万件単位というのが多いですね。

[akirafudo6]

ですです。ただ、以前DB　insertの処理時間って、1recあたり、23-25msかかったけど、ご質問者さんの環境ではどれぐらいなんでしょうかね？

[silversink]

今insert処理でサンプルデータ3000件を入れたら0.6秒で済みました。
現在ノートで開発しており、本番はwindows serverにクライアントからファイルを取り込み予定です。重複データの判断基準がIDの他に日付とかも加わってしまいちょっとupdateが苦戦中です。

色々有益なコメントを頂いてありがとうございます。
明日も進展がありましたら報告させて頂きますので
ご迷惑でなければご意見頂ければと思います。

[silversink]

＞sazi様

以前書いてもらった下記サンプルで、

-- IDが重複しているもののうち最新の日付以外を削除
delete from tmp as target where day < (select max(day) from tmp where id=target.id);

これを「-- IDが重複しているもの全てを削除」する時はどうすれば宜しいでしょうか？

[sazi]

重複してたら１件も残らず削除ですか？
残すなら残す条件が必要です。

[silversink]

はい。
IDが重複していれば削除を実施したいです。

[sazi]

IDが重複しているデータを残らず削除するには、IDが複数あるものを条件にします。
delete from tmp where id in (select id from tmp group by id having count(*)>1)

[silversink]

ありがとうございます。
想定通りの処理が出来ました。

[silversink]

＞sazi様
再度すみません。
例えばCSVの任意の行の『day』の列が空のデータの時にその列のDBへの取り込みをスキップして空の『day』の行を別のファイルでダウンロードとかは可能でしょうか？
可能の時はやはり一時テーブルを使う事になるのでしょうか？

[sazi]

>『day』の行を別のファイルでダウンロードとかは可能でしょうか？
複数のファイルを１行に纏めるという意味なら、それぞれのファイル用の一時テーブルに取り込んで、後は普通のSQLでjoinするのと同じようにselectしたものをinsertすれば良いと思います。

[silversink]

特に複数という訳ではなく1つを想定していますが
やることは一緒でしょうか？

test.csvの取り込み処理
↓
『day』がある行はDBへ取込
↓
test.csvの取り込み処理が終わった後に
『day』が空の行は他のcsvファイルへ自動で出力

[sazi]

copy (select * from tmp where day is null) to xxx.csv
みたいな感じでtmpが消える前（同トランザクション内）出力すれば良いかと

[silversink]

ありがとうございます。
クライアント側から出来ました。
何度も聞いて申し訳ございませんが、、

ちなみにこの『day』のないcsvファイルを
サーバ、クライアント両方からDLする方法はありますでしょうか？

[sazi]

PGADMINなどのDBツールでは、サーバー上で実行されますから、COPYに関するパスはPostgresのインストールされているサーバーからアクセスできる範囲である必要があります。
ですので、サーバーとクライアント双方から参照できる場所を指定する必要があります。
psqlのコンソールだったら、psqlが介在してファイルをやり取りしてくれるのでローカルフォルダだけ意識すれば良いですけど。

[sazi]

元質問からは離れてきているので、別質問の方が良いと思いますけど。

[silversink]

ご回答ありがとうございます。

また、確かに本題からそれておりました。
失礼しました。

Answer 2

早速のご回答ありがとうございます。
明日試してみます。