SELECT結果をCSV出力する方法
SELECT文の結果をCSVファイルへ出力する方法をご紹介します。CSVファイル出力は、OS上でvsqlユーティリティとawkコマンドを組み合わせて行います。
構文
|
1 |
$ vsql -w パスワード -F '囲み文字＋区切り文字＋囲み文字' -P pager=off -AXtnqc "SELECT文" | awk '{print "行頭の囲み文字"$0"行末の囲み文字"}' > CSVファイル名 |
実行例
|
1 2 3 4 5 6 7 8 |
$ vsql -w password -F '","' -P pager=off -AXtnqc "SELECT * FROM ssbm.date1 LIMIT 5;" | awk '{print "\""$0"\""}' > /tmp/date1.csv $ $ cat /tmp/date1.csv "19920101","January 1, 1992 ","Thursday ","January ","1992","199201.000000000000000","Jan1992","5.000000000000000","1.000000000000000","1.000000000000000","1.000000000000000","1.000000000000000","Winter ","0","1","1","1" "19920102","January 2, 1992 ","Friday ","January ","1992","199201.000000000000000","Jan1992","6.000000000000000","2.000000000000000","2.000000000000000","1.000000000000000","1.000000000000000","Winter ","0","1","0","1" "19920103","January 3, 1992 ","Saturday ","January ","1992","199201.000000000000000","Jan1992","7.000000000000000","3.000000000000000","3.000000000000000","1.000000000000000","1.000000000000000","Winter ","1","1","0","0" "19920104","January 4, 1992 ","Sunday ","January ","1992","199201.000000000000000","Jan1992","1.000000000000000","4.000000000000000","4.000000000000000","1.000000000000000","1.000000000000000","Winter ","0","1","0","0" "19920105","January 5, 1992 ","Monday ","January ","1992","199201.000000000000000","Jan1992","2.000000000000000","5.000000000000000","5.000000000000000","1.000000000000000","1.000000000000000","Winter ","0","1","0","1" |
補足
vsqlには出力ファイルの区切り文字を指定するオプション(-F)は用意されていますが、囲み文字を指定するオプションが用意されていません。
そのため、vsqlの-Fオプションで「”,”」のように区切り文字の前後に囲み文字も付与し、且つ、
awkコマンドを用いてvsql出力行の行頭および行末に「”」を付与しています。
なお、区切り文字にタブを使用する場合は、-Fオプションに「-F $'”\t”‘」と指定します。
「$」は、ログインシェルがbashの場合に、バックスラッシュをエスケープするフォーマットです。
参考情報
Verticaで既存環境のテーブルのDDLとデータを一括で抽出する方法http://vertica-tech.ashisuto.co.jp/export-all-data/
SELECT結果をCSV出力する方法(その2)
http://vertica-tech.ashisuto.co.jp/csv-output2/
