2014/07/04
PostgreSQL >  COPY + DO で複数ファイル一括インポート
昨日の最後に書いた件。昨日は PostgreSQL 9.3 の COPY … FROM PROGRAM から Windows の DIR コマンドを起動して、ファイル一覧を PostgreSQL のテーブルにした。これ自体が目的ということは余りなく、この一覧を元に、COPY コマンドを DO 構文の中で連続実行して多数のファイルを一括インポートすることが多い。その簡単な例を紹介。実行環境は 2014/04/17 を、COPY と DO の詳細は下記マニュアルを参照。

■ PostgreSQL 9.3.2 文書 : COPY -- ファイルとテーブルの間でデータをコピーする
http://www.postgresql.jp/document/9.3/html/sql-copy.html

■ PostgreSQL 9.3.2 文書 : DO -- 無名コードブロックを実行します
http://www.postgresql.jp/document/9.3/html/sql-do.html

↓ サンプル用に作成したテキストファイル群。今回は話を単純にするため中身・ファイル名ともに全て ASCII 文字とし、日本語はない。タブ区切りで、何かの一時間値が二系列一年分あり、月別のファイルに分かれている想定。



↓ 同じファイル群を ZIP にして置いておく。

https://kenpg.up.seesaa.net/image/20140704_files_sample.zip


上記ファイル群に PostgreSQL からのアクセス権限を適宜設定したら、昨日と同様にファイル一覧テーブルを作る。最初に一時テーブルを作って COPY + DIR の出力を一行ずつ格納し、続いてファイル情報の行だけ抽出して別テーブルにする。昨日はファイル名だけだったが今日はフルパスにした。その方が COPY でインポートする時に一手間減る。以上で一時テーブルは不要なので消す。↓ 以上のクエリ。
CREATE TABLE "201407"."04_files_temp" (rid serial, str text ) ;
COPY "201407"."04_files_temp" (str)
	FROM PROGRAM 'DIR R:\TEMP_20140704' (ENCODING 'SJIS') ;

CREATE TABLE "201407"."04_files_list" AS
WITH a AS (
	SELECT regexp_matches(str
		, '^(\d{4}/\d{2}/\d{2} +\d{2}:\d{2}) +([\d,]+) (.+)$') reg
	FROM "201407"."04_files_temp"
	WHERE str ~* '\.txt$'
)
SELECT 'R:/TEMP_20140704/' || reg[3] fpath
	, replace(reg[2], ',', '') :: int fsize
	, reg[1] ftime
FROM a ;

DROP TABLE "201407"."04_files_temp" ;

↓ 作成されたファイル一覧テーブルの中身。


↓ 続いてインポート先の一時テーブルを作る。元データが四列あるのでその列数分、ただし全て文字型で。というのも元データにヘッダ行があり、PostgreSQL の COPY コマンドがタブ区切りファイルに対して「ヘッダ行を除く」ことができず（CSV に対しては可能）、とりあえずヘッダ・データを一括して文字型でインポートするから。
CREATE TABLE "201407"."04_import_temp"
	(ymd text, h24 text, val1 text, val2 text) ;

↓ 今日の中心部分。DO 構文の中でファイル一覧テーブルを一行ずつ呼び出し、COPY コマンドの SQL を動的に生成して実行する。その際、クォーテーションが煩雑にならないよう format 関数を使う。PL/pgSQL でストアド関数を作っても同様のことが可能。
DO $D$
DECLARE fph text ;
	sql text ;
	scn text = '201407' ; -- 投入先テーブルのスキーマ名
	tbn text = '04_import_temp' ; -- テーブル名
BEGIN
	FOR fph IN
		SELECT fpath FROM "201407"."04_files_list"
	LOOP
		sql = format('COPY %I.%I FROM %L', scn, tbn, fph) ;
		RAISE INFO '%', sql ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;

↓ クエリ実行の様子。一年分の時間値は約8700行で、ほぼ一瞬。今回は確認のため、動的に生成した SQL を一行ずつ RAISE INFO している。


↓ 作成された一時テーブル。前述したように全ての列が文字型で、各ファイルのヘッダとデータが両方入っている。


↓ 一時テーブルからヘッダ行を除き、本来のデータ型にキャストして別テーブルに保存する。これで正式なインポート先テーブルになる。一時テーブルはもう使わないので消す。
CREATE TABLE "201407"."04_import_sample" AS
SELECT ymd :: date, h24 :: int, val1 :: float, val2 :: float
FROM "201407"."04_import_temp"
WHERE ymd <> 'ymd' ;

DROP TABLE "201407"."04_import_temp" ;

↓ 最終形のテーブルと、行数・日数を確認したところ。



同様の一括インポートは、psql の \copy コマンドをループさせるなど様々な方法でできるが、今日の方法は pgAdmin のクエリツール上で完結し、手順を一連の SQL にまとめられるので記録・再実行・使い回しが楽。ファイルの中身に非 ASCII 文字があっても COPY コマンドの ENCODING オプションを指定すれば基本的に同様にインポートできる。

一方デメリットとして、ファイル名に非 ASCII 文字を含む場合、ファイルシステムとサーバの文字コードが同じでない限り今日の方法は使えない。COPY コマンドのマニュアル（URL は冒頭）に下記の記述があるとおり。
指定したファイルは必ずサーバからアクセスできる必要があります。また、ファイル名はサーバから見たように指定されなければなりません。

もしファイル名に日本語を含むデータがある場合、いったん ASCII 文字だけの一時ファイルにコピーすれば何とかなる。実は PL/Python を使えば、その作業も SQL で可能になって今日のクエリに組み込める。紹介はまたいずれ。
<< テスト用ダミーファイル群を作る（1）
COPY + DIR コマンドでファイル一覧表 >>