2014/07/05
PostgreSQL >  テスト用ダミーファイル群を作る（1）
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

本来ダミーファイル群の作成は他のプログラミング言語の方が向くが、既に PostgreSQL 内にあるデータを使ったり日付/時刻関数を使う場合、多かれ少なかれクエリを検討する。それなら、pgAdmin でクエリ検討とファイル出力を一括して行えば楽という考え。SQL を保存して後で使い回すこともできる。

今日は出力ファイル名が ASCII 文字だけの例。自分の環境では、日本語など非 ASCII を使うと文字化けする。そこで PL/Python で対処する方法を見つけたが、それは明日書く予定。

↓ 例えばこんな都道府県コード・名の一覧テーブルがあるとして、47都道府県別のダミーデータファイルを出力する場合。


↓ クエリ例。全体を DO 文で作り、都道府県のテーブルを一行ずつループして COPY を実行。その際のクエリで generate_series と random を使ってダミーデータを発生させる。出力先フォルダは事前に作成し、PostgreSQL からの書き込み権限を与えておく。
DO $D$
DECLARE r record ;
	dir text = 'R:/Test 20140705_1/' ; -- 出力先パス
	enc text = 'SJIS' ; -- 出力文字コード
	fln text ;
	fmt text = 'text' ; -- TSV or CSV
	sql text ;
BEGIN
	FOR r IN
		SELECT * FROM "201407"."05_tb_sample_1"
	LOOP
		fln = concat(dir, r.pcode, '.tsv') ;
		sql = format('COPY (
			SELECT text %L, sub, random()
			FROM generate_series(1, (random() * 1000) :: int) sub
		) TO %L (ENCODING %L, FORMAT %L)', r.pname, fln, enc, fmt) ;
		RAISE INFO '%', sql ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;


↑ 実行結果。確認用に COPY コマンドを表示。パスに半角空白があっても大丈夫だった。各ファイルの行数は random() * 1000、おおむね数百行になる。下が出力後のフォルダと、参考まで同じファイル群を ZIP に圧縮したもの。。

https://kenpg.up.seesaa.net/image/20140705_test_1.zip


↓ 各ファイルの中身はこんな感じ。COPY コマンドの区切り文字はデフォルトのタブ。この場合ヘッダ行を付けられず、データ本体だけになる。


↓ 同じテーブルを元に、今度は CSV で出力する例。ついでにデータの文字コードも EUC-JP に変えた。ヘッダ行は付け忘れたので、次の次の例で。
DO $D$
DECLARE r record ;
	dir text = 'R:/Test 20140705_2/' ;
	enc text = 'EUC-JP' ; -- 変えてみた
	fln text ;
	fmt text = 'csv' ; -- 変えてみた
	sql text ;
BEGIN
	FOR r IN
		SELECT * FROM "201407"."05_tb_sample_1"
	LOOP
		fln = concat(dir, r.pcode, '.csv') ; -- 拡張子も変更
		sql = format('COPY (
			SELECT text %L, sub, random()
			FROM generate_series(1, (random() * 1000) :: int) sub
		) TO %L (ENCODING %L, FORMAT %L)', r.pname, fln, enc, fmt) ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;



↑ 実行結果。確かに EUC-JP で出力できた。先ほどと同様 ZIP を置いておく。

https://kenpg.up.seesaa.net/image/20140705_test_2.zip

↓ もう少し多いファイルを一度に作る例として、全国の市（854行、現存しないのも含む）のテーブルを使う例。文字コードを UTF-8 に変えた以外はほぼ同じ。

DO $D$
DECLARE r record ;
	dir text = 'R:/Test 20140705_3/' ;
	enc text = 'UTF-8' ; -- 変えてみた
	fln text ;
	fmt text = 'text' ;
	sql text ;
BEGIN
	FOR r IN
		SELECT * FROM "201407"."05_tb_sample_2"
	LOOP
		fln = concat(dir, r.jcode, '.tsv') ;
		sql = format('COPY (
			SELECT text %L, sub, random()
			FROM generate_series(1, (random() * 1000) :: int) sub
		) TO %L (ENCODING %L, FORMAT %L)', r.jname, fln, enc, fmt) ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;



↑ 実行結果。確かに UTF-8 で出力できた。先ほどと同様 ZIP を置いておく。

https://kenpg.up.seesaa.net/image/20140705_test_3.zip

↓ 今度は、既存のテーブルではなく日付/時刻型を使い、任意の年月に対して日別ファイルを出力する例。ファイルの中身は1分ごとのカウントデータという想定。
DO $D$
DECLARE r record ;
	dir text = 'R:/Test 20140705_4/' ;
	enc text = 'SJIS' ;
	fln text ;
	fmt text = 'csv' ;
	sql text ;
BEGIN
	FOR r IN
		WITH a AS (
			SELECT 2014 "year", 2 "month"
		), b AS (
			SELECT concat_ws('-', "year", "month", 1) :: timestamp ymd
			FROM a
		)
		SELECT generate_series(
			ymd, ymd + interval '1 month' - interval '1 day'
			, interval '1 day') :: date ymd
		FROM b
	LOOP
		fln = concat(dir, r.ymd, '.csv') ;
		sql = format('COPY (
			SELECT left(concat_ws(%L, "hour", "minute")
					 :: time :: text, 5) "time"
				, (random() * 1000) :: int "count"
			FROM generate_series(0, 23) "hour"
				, generate_series(0, 59) "minute"
		) TO %L (ENCODING %L, FORMAT %L, HEADER)' -- ヘッダ追加
			, ':', fln, enc, fmt) ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;



↑ 実行結果。COPY コマンドのオプションに HEADER を追加して、列名を各ファイルの先頭に入れた。先ほどと同様 ZIP を置いておく。

https://kenpg.up.seesaa.net/image/20140705_test_4.zip

↓ 最後に、昨日インポートに使ったファイル群の作成クエリと、中身の再掲。月別のファイルに、一日ごとの24時間別データが二系列ある想定。クエリを工夫して TSV でもヘッダ行を入れた。ZIP は昨日の記事の前半にある。
DO $D$
DECLARE r record ;
	fln text ;
	dt1 date ;
	dt2 date ;
	sql text ;
BEGIN
	FOR r IN
		SELECT 2014 wyear, generate_series(1, 12) "month"
	LOOP
		fln = 'R:/TEMP_20140704/'
			|| r.wyear || to_char(r.month, 'FM00') || '.txt' ;
		dt1 = concat_ws('-', r.wyear, r.month, 1) :: date ;
		dt2 = dt1 + interval '1 month' - interval '1 day' ;
		
		sql = format('COPY (
			SELECT text %L
				, text %L
				, text %L
				, text %L
			UNION ALL
			SELECT ymd :: date :: text
				, h24 :: text
				, random() :: text
				, random() :: text
			FROM generate_series(%L :: timestamp
					, %L :: timestamp
					, %L :: interval) ymd
				, generate_series(0, 23) h24
		) TO %L', 'ymd'
			, 'h24'
			, 'val1'
			, 'val2'
			, dt1
			, dt2
			, '1 day'
			, fln) ;
		EXECUTE sql ;
	END LOOP ;
END $D$ ;



この方法でできないのは、出力先のパスに非 ASCII 文字を使うこと。それを補うため PL/Python を使う。というわけで（2）に続く。
<< テスト用ダミーファイル群を作る（2）
COPY + DO で複数ファイル一括イン… >>