PostgreSQLのデータを抜き取る
シェル芸の世界に本格的に取り込むきっかけになったこの本。最後の付録にWordPressのデータを抜き取る処理が書かれている。付録というよりはこの本の集大成みたいな感じもする。MySQLに入っているWordPressのデータをぶっこ抜いて、BashCMS用のテキストファイルにしている。
この記事を参考に、PostreSQLに入っているデータをぶっこ抜いてテキストファイルにしてみた。シェル芸で扱いやすいデータにするには、ぶっこ抜いた結果が下記のようになっていると良いかな？
- カラム間が空白区切り
- データの空白や改行は別の文字へ置き換え
- 1行1レコード
この形にするには、下記のようなデータがあるとメンドくさい。NULL値はなんらかの文字に置き換え、空白は@s@p@という文字に置き換える方針にした。
- NULL値や空文字を表示しないとカラム数がおかしくなる
- データに空白があるとカラム区切りがおかしくなる
- 改行入りデータ（text形式のカラム）があると・・・
動作環境は、Mac OSX Yosemite。gsed(GNU sed)とpsqlコマンドはHomebrewにてインストール。psqlの接続先やユーザ名のオプションは、適宜追加で指定すること。
最初の案
\pset null 'NULL'の指定psql --no-align --field-separator '@f@s@' --tuples-onlyのオプションで邪魔なのを消す
カラム間の区切りは@f@s@という文字列にしてみた。上記のアイデアでこんなワンライナーを考えた。（長いので改行）
$ ( echo "\pset null 'NULL'"; echo 'SELECT * from TABLE名;' ) |\
> psql --no-align --field-separator '@f@s@' --tuples-only DB名 | tail -n +2 |\
> sed 's/ /@s@p@/g' |\
> sed 's/@f@s@@f@s@/@f@s@""@f@s@/g' |\
> sed 's/@f@s@/ /g' > TABLE名.txt
2行目のtailでヘッダを取り除き、3行目のsedで空白を@s@p@へ変換、4行目のsedで空白文字を""へ変換（この処理は怪しい？）、5行目のsedでカラム区切りを空白へ。
このワンライナーの問題は、改行の含まれたtext形式のカラムがあると、1行1レコードに出来ないのであった・・・
次の案
そこで再度戦略を練る。そういえばpsqlコマンドでCOPYってのが使えたな・・・こっちのが簡単じゃ？text形式なカラムの改行は\r\nになるので、1行1レコードに簡単に出来そう。
COPYコマンドの出力は、カラム区切り文字はタブになる。NULL値は\Nと表示される。ということでCOPYコマンドを使ったワンライナーがこちら。（長いので改行）
$ echo 'COPY TABLE名 TO stdout;' |\
> psql --host=localhost --user=developer gw |\
> gsed 's/\t\t/\t""\t/g' |\
> sed 's/ /@s@p@/g' |\
> gsed 's/\t/ /g' > TABLE名.txt
タブ文字の指定をする時はGNU sed (gsed)を使う。3行目のgsedで空白文字を""へ変換（この処理は怪しい？）、4行目のsedで空白を@s@p@へ変換、5行目のgsedでカラム区切りをタブから空白へ。
念のため各行のフィールド数が等しいかを確認。フィールド数が1つだけ出て来ればOK。
$ awk '{print NF}' TABLE名.txt | sort -n | uniq
まだまだ問題点がありそうだが、とりあえずこれでいいんじゃ？ユニケージも試してみる準備が出来た・・・かな？
