pg_csv
pg_csvとは
PostgreSQLで、ＳＱＬの結果をＣＳＶ出力させたい事がよくあります。
ところが、通常psqlコマンドを使うと、区切りの文字をカンマに指定する事はできますが、データー中にカンマやダブルクォート、改行が存在する場合は、その都度手動で修正しなければなりませんでした。
それでもデーター量が少なければ問題はないのですが、データーが１０万２０万・・・１００万件となっていくにつれ、手動チェックでは追いつかない事がままあります。実際、１００万件のデーターうちの563,269件目にカンマが入っててエラーになっている事に気付かずそのままｃｓｖファイルを業務で使用していたために、とんでもないトラブルに見舞われたりしました。
そこで、通常はPHPやPerl言語を使ってＣＳＶファイルを出力するプログラムを作るわけですが、その都度同じようなプログラムを作るでは、いささかい手間がかかります。そこで、pg_dumpと同じような感覚で扱えるＣＳＶ出力コマンドを作成しました。
ところが、通常psqlコマンドを使うと、区切りの文字をカンマに指定する事はできますが、データー中にカンマやダブルクォート、改行が存在する場合は、その都度手動で修正しなければなりませんでした。
それでもデーター量が少なければ問題はないのですが、データーが１０万２０万・・・１００万件となっていくにつれ、手動チェックでは追いつかない事がままあります。実際、１００万件のデーターうちの563,269件目にカンマが入っててエラーになっている事に気付かずそのままｃｓｖファイルを業務で使用していたために、とんでもないトラブルに見舞われたりしました。
そこで、通常はPHPやPerl言語を使ってＣＳＶファイルを出力するプログラムを作るわけですが、その都度同じようなプログラムを作るでは、いささかい手間がかかります。そこで、pg_dumpと同じような感覚で扱えるＣＳＶ出力コマンドを作成しました。
更新履歴
・2006-11-23 Version1.01
以下の引数を追加
eucenen
sjisenen
euc2sjis
excel引数をつけた際に、改行コードやカンマ混在時に発生する不具合を若干改善しました。
・2007-12-11 Version1.02
タブコードを\nと変換してしまう不具合を修正しました。（正しくは\t）
・2008-11-29 Version1.03
コンパイル時に#include <string.h>がないために警告が出るのを修正しました。
--postgres_lib指定がうまく効かない（変数名間違いのため）のを修正しました。
make installができるようにしました。
以下の引数を追加
eucenen
sjisenen
euc2sjis
excel引数をつけた際に、改行コードやカンマ混在時に発生する不具合を若干改善しました。
・2007-12-11 Version1.02
タブコードを\nと変換してしまう不具合を修正しました。（正しくは\t）
・2008-11-29 Version1.03
コンパイル時に#include <string.h>がないために警告が出るのを修正しました。
--postgres_lib指定がうまく効かない（変数名間違いのため）のを修正しました。
make installができるようにしました。
作成方法
まず、下記リンクをクリックして、ソース一式をダウンロードした後、サーバーにアップロードします。
pg_csv-1.03.tar.gzをダウンロード
これを、適当なコンパイル用のディレクトリにアップします。
tar xvzf pg_csv-1.03.tar.gz
cd pg_csv-1.03
./configure
make
make install
大抵はこれでコンパイルが通るはずですが、うまくいかない場合は下記を参考に環境変数$POSTGRES_HOMEを設定するか、--postgres_include=や--postgres-lib=の引数を指定してください。
pg_csv-1.03.tar.gzをダウンロード
これを、適当なコンパイル用のディレクトリにアップします。
tar xvzf pg_csv-1.03.tar.gz
cd pg_csv-1.03
./configure
make
make install
大抵はこれでコンパイルが通るはずですが、うまくいかない場合は下記を参考に環境変数$POSTGRES_HOMEを設定するか、--postgres_include=や--postgres-lib=の引数を指定してください。
./configure時にスクリプトが自動的にlibpq-fe.hとlibpq.aを探します。
１．libpq-fe.hを、
1-1)--postgres_include=で指定されたパスから探します。
1-2)$POSTGRES_HOME/includeから探します。
1-3)$PGINCLUDEから探します。
1-4)/usr/includeから探します。
1-5)/usr/local/includeから探します。
２．libpq.aを、
2-1)--postgres_lib=で指定されたパスから探します。
2-2)$POSTGRES_HOME/libから探します。
2-3)$PGLIBから探します。
2-4)/usr/libから探します。
2-5)/usr/local/libから探します。
１．libpq-fe.hを、
1-1)--postgres_include=で指定されたパスから探します。
1-2)$POSTGRES_HOME/includeから探します。
1-3)$PGINCLUDEから探します。
1-4)/usr/includeから探します。
1-5)/usr/local/includeから探します。
２．libpq.aを、
2-1)--postgres_lib=で指定されたパスから探します。
2-2)$POSTGRES_HOME/libから探します。
2-3)$PGLIBから探します。
2-4)/usr/libから探します。
2-5)/usr/local/libから探します。
使い方
ソースが正しくコンパイルされていれば、実行ファイルpg_csvというファイルが作られていると思います。このファイルを実行します。
|
書式 pg_csv [-オプション] ＤＢ名 -q "ステートメント" [--help] |
オプション
-U ユーザー名
PostgreSQLを実行させるユーザー名を指定します。指定しないと現在のユーザー名になります。
-h ホスト名
データーベースが他のサーバーにある時は、このオプションでＩＰアドレスやホスト名を指定します。指定しないとlocalhostになります。
-w パスワード
データーベースにパスワード制限がかかっている場合、このオプションで指定します。スクリプト内で実行させる場合には-Wよりも-wの方が便利だと思います。
-W
パスワード入力のプロンプトを表示します。-wコマンドだとｂａｓｈの履歴にパスワードが残ってしまうのがイヤッという方はこちらの方法で指定してください。こちらはコマンドラインから手入力する時に指定すると良いでしょう。
-p ポート
指定しない場合、ポートはPostgreSQL標準の5432ポートになります。通常は指定する必要ないと思います。
ＤＢ名
データーベース名を指定します。指定がないと、現在のユーザーと同じ名前のデーターベースになります。
-q "ステートメント"
-qに続いてダブルクォートで囲ってＳＱＬステートメントを入力します。必須です。
--tab
結果をカンマ区切りではなく、タブ区切りで出力させます。この場合、ヌルフィールドは\Nに置き換えられます。フィールド名は出力されなくなります。--tabと--eucenen（DBの文字コードがSJISなら--sjisenen）を組み合わせる事で、\copyコマンドでDBに戻せる（多分）データーを作る事ができます。
--nofieldname
結果の１行目にフィールド名を出力しなくします。CSV出力の場合のみ有効です。（--tabオプションを使った場合、もともとフィールド名は出ません。）
--changebool=true
bool型のフィールドは、通常では"t"または"f"と出力しますが、このオプションを指定するとTRUEやFALSEに置き換わります。エクセルに読み込ませたい場合など、TRUE、FALSEの方が都合が良い場合に指定してください。
--changebool=1
bool型のフィールドは、通常では"t"または"f"と出力しますが、このオプションを指定すると1や0に置き換わります。出力したデーターをさらにPHP、Perl等のプログラムで処理したい場合、論理値がTRUE、FALSE（つまり１と０）になった方が可読性が良くなるでしょう。
複数のchangeboolを指定した時は、最後に指定したものが有効になります。
--excel
エクセルでCSVファイルを読み込ませると、date型のフールドを勝手に引き算として計算してしまったり、先頭に０がつく文字列を数値化してゼロを省いてしまったりと、頼みもしないことを色々とやってくれます。 そこで、このオプションを指定することで、=""という風に各フィールドの先頭にイコールをつけて、エクセルに強制的に文字型と認識させます。
ただし、フィールド上に改行コードや、カンマが混在している場合、=""をつけてしまうと、エクセルが改行コードやカンマをフィールドの区切りとして判断してしまいます。 つまり、=をつけることで、せっかくダブルクォートで囲っているのが無効にされてしまうわけです。 そこで、Ver.1.01からは--excelを指定してもフィールドに改行やカンマが混在している場合に限り、フィールドの先頭に=をつけなくなりました。
--eucenen
タブ区切りで出力したテキストファイルを、\copyコマンドで読み込む際に、半角￥マークは\\としないとエスケープキャラにされてしまいます。そこで、--eucenen引数をつけると、フィールドがEUCコードで格納されているとみなし、半角の￥を\\に置き換えます。--tabが指定された場合のみ有効です。
--sjisenen
eucenenと同じく半角の￥を\\に置き換えます。--tabが指定された場合のみ有効です。こちらは、フィールドがシフトＪＩＳで格納されているとみなします。つまり、「ソ」「表」「伝」「刷」等の￥コード（５ｃ）を含む2バイトコードがあった場合、誤って￥をつけてしまわないようにします。
PostgreSQLでは、端末の文字セットをソフトＪＩＳに指定すれば、５ｃのコードを含む漢字でも、後ろに￥をつける必要がありません。
--euc2sjis
大抵の方は、PostgreSQLをデフォルトのＥＵＣコードで使用していると思います。つまり、pg_csvコマンドで出力した結果をそのままエクセルに読み込ませると文字化けして日本語が読めない状態になります。
そこで、バージョン1.0.0では | nkf -s をパイプしていましたが、バージョン1.0.1より--euc2sjisを指定する事で、プログラム内でEUCコードをシフトＪＩＳコードに変換してくれます。
--help
このオプションの説明を出します。最低でも--helpだけ覚えておけば操作方法がわかると思います。
PostgreSQLを実行させるユーザー名を指定します。指定しないと現在のユーザー名になります。
-h ホスト名
データーベースが他のサーバーにある時は、このオプションでＩＰアドレスやホスト名を指定します。指定しないとlocalhostになります。
-w パスワード
データーベースにパスワード制限がかかっている場合、このオプションで指定します。スクリプト内で実行させる場合には-Wよりも-wの方が便利だと思います。
-W
パスワード入力のプロンプトを表示します。-wコマンドだとｂａｓｈの履歴にパスワードが残ってしまうのがイヤッという方はこちらの方法で指定してください。こちらはコマンドラインから手入力する時に指定すると良いでしょう。
-p ポート
指定しない場合、ポートはPostgreSQL標準の5432ポートになります。通常は指定する必要ないと思います。
ＤＢ名
データーベース名を指定します。指定がないと、現在のユーザーと同じ名前のデーターベースになります。
-q "ステートメント"
-qに続いてダブルクォートで囲ってＳＱＬステートメントを入力します。必須です。
--tab
結果をカンマ区切りではなく、タブ区切りで出力させます。この場合、ヌルフィールドは\Nに置き換えられます。フィールド名は出力されなくなります。--tabと--eucenen（DBの文字コードがSJISなら--sjisenen）を組み合わせる事で、\copyコマンドでDBに戻せる（多分）データーを作る事ができます。
--nofieldname
結果の１行目にフィールド名を出力しなくします。CSV出力の場合のみ有効です。（--tabオプションを使った場合、もともとフィールド名は出ません。）
--changebool=true
bool型のフィールドは、通常では"t"または"f"と出力しますが、このオプションを指定するとTRUEやFALSEに置き換わります。エクセルに読み込ませたい場合など、TRUE、FALSEの方が都合が良い場合に指定してください。
--changebool=1
bool型のフィールドは、通常では"t"または"f"と出力しますが、このオプションを指定すると1や0に置き換わります。出力したデーターをさらにPHP、Perl等のプログラムで処理したい場合、論理値がTRUE、FALSE（つまり１と０）になった方が可読性が良くなるでしょう。
複数のchangeboolを指定した時は、最後に指定したものが有効になります。
--excel
エクセルでCSVファイルを読み込ませると、date型のフールドを勝手に引き算として計算してしまったり、先頭に０がつく文字列を数値化してゼロを省いてしまったりと、頼みもしないことを色々とやってくれます。 そこで、このオプションを指定することで、=""という風に各フィールドの先頭にイコールをつけて、エクセルに強制的に文字型と認識させます。
ただし、フィールド上に改行コードや、カンマが混在している場合、=""をつけてしまうと、エクセルが改行コードやカンマをフィールドの区切りとして判断してしまいます。 つまり、=をつけることで、せっかくダブルクォートで囲っているのが無効にされてしまうわけです。 そこで、Ver.1.01からは--excelを指定してもフィールドに改行やカンマが混在している場合に限り、フィールドの先頭に=をつけなくなりました。
--eucenen
タブ区切りで出力したテキストファイルを、\copyコマンドで読み込む際に、半角￥マークは\\としないとエスケープキャラにされてしまいます。そこで、--eucenen引数をつけると、フィールドがEUCコードで格納されているとみなし、半角の￥を\\に置き換えます。--tabが指定された場合のみ有効です。
--sjisenen
eucenenと同じく半角の￥を\\に置き換えます。--tabが指定された場合のみ有効です。こちらは、フィールドがシフトＪＩＳで格納されているとみなします。つまり、「ソ」「表」「伝」「刷」等の￥コード（５ｃ）を含む2バイトコードがあった場合、誤って￥をつけてしまわないようにします。
PostgreSQLでは、端末の文字セットをソフトＪＩＳに指定すれば、５ｃのコードを含む漢字でも、後ろに￥をつける必要がありません。
--euc2sjis
大抵の方は、PostgreSQLをデフォルトのＥＵＣコードで使用していると思います。つまり、pg_csvコマンドで出力した結果をそのままエクセルに読み込ませると文字化けして日本語が読めない状態になります。
そこで、バージョン1.0.0では | nkf -s をパイプしていましたが、バージョン1.0.1より--euc2sjisを指定する事で、プログラム内でEUCコードをシフトＪＩＳコードに変換してくれます。
--help
このオプションの説明を出します。最低でも--helpだけ覚えておけば操作方法がわかると思います。
例
pg_csv -U postgres database1 -q "select * from user_m" > user_m.csv
select * from user_mというステートメントを実行し、結果をuser_m.csvというファイルに入れます。
pg_csv -U postgres database1 -q "select * from user_m" --tab
> user_m.dmp
select * from user_mというステートメントを実行し、結果をuser_m.dmpというファイルに入れます。結果はタブ区切りになります。タブ区切りの場合、ヌルな列は「\N」になります。また、改行コードは「\r」「\n」、タブコードは「\t」になります。
pg_csv -U postgres database1 -q "select * from user_m" --excel
--euc2sjis > user_m.dmp
select * from user_mというステートメントを実行し、結果をuser_m.dmpというファイルに入れます。各フィールドの先頭に＝をつけます。また、ＥＵＣコードをシフトＪＩＳに変換します。出力結果はすぐにエクセルで読み込む事ができます。
pg_csv -U postgres -h 192.168.1.4 -p 5000 -w pass database1 --nofieldname
-q "select * from user_m" > user_m.csv
パスワードpass、ホスト192.168.1.4 ポート5000 ユーザーpostgresで、データーベースdatabase1に接続し、select
* from user_mの結果を、フィールド名を除いてuser_m.dmpというファイルに入れます。
pg_csv -U postgres --tab --eucenen -q "select user_name, tel, fax,
address from user_m where tel like '048%' order by tel" > user_m.csv
whereで指定された条件を満たすユーザーの情報を、タブ区切りで出力します。フィールドに￥が含まれていたら、\\にエスケープします。
制限事項
ＳＱＬ文は１行だけ
ある程度複雑なＳＱＬ文でも受け付けるはずですが、必ず１行のＳＱＬ文にしてください。途中でセミコロンで区切って複数のＳＱＬ文を読ませたりはできません。
LEFT JOINや、INNER JOIN、サブクエリ等を駆使してなんとか１行に収めてください。
LEFT JOINや、INNER JOIN、サブクエリ等を駆使してなんとか１行に収めてください。
結果データーの量
結果が大量になる場合、where句やoffset、limit句を使ってなるべく必要な分だけを出すようにしてください。pg_dump感覚で全データー一気に引き出そうとするとサーバーのメモリリソースが不足してしまう場合があります。
特に現在業務で稼動中のサーバーに対して行う際は、くれぐれも慎重に行ってください。
特に現在業務で稼動中のサーバーに対して行う際は、くれぐれも慎重に行ってください。
'や\をクォートしてください
where条件として'や\を含む場合、''とか\\という風に自力でクォートしてください。ただし、PostgreSQL8.2以降でbackslash_quote offの場合は、バックスラッシュ（￥マークね）をエスケープする必要はありません。
select文専用です
このコマンドで、q=で指定できるＳＱＬ文は、select文専用です。insert、update、delete文等データーの書き込み（消去）を行うSQL文を指定した場合の動作保障はいたしかねます。
予想ＦＡＱ
しばしば尋ねられるんじゃないかと予想される質問
Ｑ：出力結果をエクセルで読み込ませたら、日本語が全部文字化けしていた
Ａ：telnetで処理する場合、端末の文字コードが、RedHatLinux９ならＥＵＣコード、RHELやCentOSならＵＴＦ－８になっている事だと思います。エクセルでは基本的にシフトＪＩＳコード以外は文字化けをしてしまうので、 --euc2sjis を指定してシフトＪＩＳコードで出力させてください。
Ｑ：出力結果をエクセルで読み込ませたら、ＤＡＴＥ型が小数になってしまいました
Ａ：エクセルでは、2006-08-16というセルを、2006ひく8ひく16だと思ってしまいます。同様に、12:30:50を、勝手に６０進法から１０進法に直してくれちゃったりと、余計な事を色々とやってくれます。
--excelオプションをつけて結果を、=""形式にすれば、エクセルに、強制的に文字列として読み込ませることができます。
Ｑ：３桁ごとにカンマで区切られたフィールドを、文字型として読ませたいんだけど？
Ａ：残念。今のところカンマの混在したフィールドを、エクセルに文字型とみなさせる方法がわかっていません。
Ｑ：SQL文の途中に\や"があるとエラーになるんですけど
基本的にＳＱＬ文の\はエスケープコードとして使われます。また、"はLinuxが引数の区切りとして認識します。なので、\は\\、"は\"という風に直前に半角の\をつけてエスケープしてください。
Ｑ：例にあるようにselect * from user_mと入れたらサーバーがダウンしてしまいました
Ａ：select文は*ではなく、セレクトするフィールドを指定するようにしてください。また、whereやlimitを使って本当に必要な箇所だけをセレクトするようにしてください。でないと、もし結果が100万件あったりするとサーバーのメモリーが持ちません。
これは当ソフトだけの問題ではなく、セレクト文を実行する全ての行為にいえることです。
Ｑ：PostgreSQLのインストールのしかたがわかりません。データーの入れ方もわかりません。
A：「Linux情報」「プログラム講座」のコーナーを読んで勉強してください。
Ｑ：コマンドってどうやって入れるんですか？
Ｑ：telnetって何ですか？
Ｑ：出力したデーターをどうやって端末のパソコンに読み込ませるんですか？
Ａ：申し訳ありませんが、そのような方はユーザーとして想定しておりません。Linux入門書等で基本を学習してからお使いください。
Ｑ：こんなの使わなくても、psql -c 'ステートメント' -A -F, を使えば一発では？
Ａ：このpg_csvのようなコマンドが長年にわたって存在していなかった理由はそこにあります。しかし、selectした結果にカンマやダブルクォートが混在している場合、手動でのエスケープが必要となります。データーが１０万件ともなると、とても手動で見つけていられる状態ではないと思います。
Ｑ：こんなの使わなくても、ＯＤＢＣを使えば一発では？
Ａ：それはそうですが、サーバーの5432ポートが開いている必要がありますし、pg_hba.confの設定も必要になるし、端末側のＯＤＢＣのインストール・・などなど、目的を達するまでにかなりのハードルがあると思います。
Ｑ：こんなの使わなくても、タブ区切りで\copyで出したものをエクセルに読ませればいいのでは？
Ａ：それはそうですが、\copyではwhereやorder by 、limit等の条件を指定する事ができませんし、エクセル内でさらにＣＳＶに変換させるといった二度手間が必要になります。また、強制文字型指定もできません。
Ｑ：出力結果をエクセルで読み込ませたら、日本語が全部文字化けしていた
Ａ：telnetで処理する場合、端末の文字コードが、RedHatLinux９ならＥＵＣコード、RHELやCentOSならＵＴＦ－８になっている事だと思います。エクセルでは基本的にシフトＪＩＳコード以外は文字化けをしてしまうので、 --euc2sjis を指定してシフトＪＩＳコードで出力させてください。
Ｑ：出力結果をエクセルで読み込ませたら、ＤＡＴＥ型が小数になってしまいました
Ａ：エクセルでは、2006-08-16というセルを、2006ひく8ひく16だと思ってしまいます。同様に、12:30:50を、勝手に６０進法から１０進法に直してくれちゃったりと、余計な事を色々とやってくれます。
--excelオプションをつけて結果を、=""形式にすれば、エクセルに、強制的に文字列として読み込ませることができます。
Ｑ：３桁ごとにカンマで区切られたフィールドを、文字型として読ませたいんだけど？
Ａ：残念。今のところカンマの混在したフィールドを、エクセルに文字型とみなさせる方法がわかっていません。
Ｑ：SQL文の途中に\や"があるとエラーになるんですけど
基本的にＳＱＬ文の\はエスケープコードとして使われます。また、"はLinuxが引数の区切りとして認識します。なので、\は\\、"は\"という風に直前に半角の\をつけてエスケープしてください。
Ｑ：例にあるようにselect * from user_mと入れたらサーバーがダウンしてしまいました
Ａ：select文は*ではなく、セレクトするフィールドを指定するようにしてください。また、whereやlimitを使って本当に必要な箇所だけをセレクトするようにしてください。でないと、もし結果が100万件あったりするとサーバーのメモリーが持ちません。
これは当ソフトだけの問題ではなく、セレクト文を実行する全ての行為にいえることです。
Ｑ：PostgreSQLのインストールのしかたがわかりません。データーの入れ方もわかりません。
A：「Linux情報」「プログラム講座」のコーナーを読んで勉強してください。
Ｑ：コマンドってどうやって入れるんですか？
Ｑ：telnetって何ですか？
Ｑ：出力したデーターをどうやって端末のパソコンに読み込ませるんですか？
Ａ：申し訳ありませんが、そのような方はユーザーとして想定しておりません。Linux入門書等で基本を学習してからお使いください。
Ｑ：こんなの使わなくても、psql -c 'ステートメント' -A -F, を使えば一発では？
Ａ：このpg_csvのようなコマンドが長年にわたって存在していなかった理由はそこにあります。しかし、selectした結果にカンマやダブルクォートが混在している場合、手動でのエスケープが必要となります。データーが１０万件ともなると、とても手動で見つけていられる状態ではないと思います。
Ｑ：こんなの使わなくても、ＯＤＢＣを使えば一発では？
Ａ：それはそうですが、サーバーの5432ポートが開いている必要がありますし、pg_hba.confの設定も必要になるし、端末側のＯＤＢＣのインストール・・などなど、目的を達するまでにかなりのハードルがあると思います。
Ｑ：こんなの使わなくても、タブ区切りで\copyで出したものをエクセルに読ませればいいのでは？
Ａ：それはそうですが、\copyではwhereやorder by 、limit等の条件を指定する事ができませんし、エクセル内でさらにＣＳＶに変換させるといった二度手間が必要になります。また、強制文字型指定もできません。
免責事項
このプログラムを使った結果発生した、データー消失や損害について、作者は一切責任を負いません。あくまで自己責任でお願いします。少なくとも、大事なデーターを扱う前には、dumpをとっておいた方が良いと思います。
このページの先頭へ
広告