読者です 読者をやめる 読者になる 読者になる

日々之迷歩

世の中わからんことだらけ

ITが複雑で難しくなっていく様に翻弄される日々です

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

まだまだ問題点がありそうだが、とりあえずこれでいいんじゃ?ユニケージも試してみる準備が出来た・・・かな?