RDBにしてもBigQueryにしても、インターフェースファイルとして登場するのはCSV。まだまだCSVを使えないとダメ。
CSVをみるのはExcelをつかうのがいいのだけれど、大きなCSVを手元のPCに持ってくるのはしんどい。サーバーサイドでCLIでCSVを読み取りたい。そこで、q コマンド。
q コマンドはCSVに対してSQLを発行できる。
でもCSVって言っても、いろいろ機能がある。
- ダブルクォートで囲むとフィールド内にカンマが入っても大丈夫だよね
- フィールド内のダブルクォートはどうやってエスケープする？（Excelだとダブルクォートでエスケープ）
- ダブルクォートで値をくくると、改行もありになるよね
このあたり、一回テストしてみよう。答えが知りたい人には先にお知らせします。以上のCSVの機能は全部カバーされている。
まずは最低限のオプション
- ヘッダー行をフィールド名にするには -H オプションを付ける
- デリミタをカンマにするには -d, をつける
ということで、最低限のサンプルはこちら
$ q -H -d, "select * from filename"
LF改行のCSV
$ cat lf.csv
id,name,age
1,john,30
2,paul,31
$ q -H -d, "select name from ./lf.csv"
john
paul
CRLF改行のCSV
cat crlf.csv
id,name,age
1,john,30
2,paul,31
$ q -H -d, "select name from ./crlf.csv"
john
paul
フィールドの中にカンマがあるケース
cat quote_comma.csv
id,name,age
"1","jo,hn","30"
"2","paul","31"
$ q -H -d, "select name from ./quote_comma.csv"
"jo,hn"
paul
一応、フィールドの中のカンマはフィールド区切りではなく値として扱ってくれている。
フィールドの中にクォーテーションが有るケース（エスケープ済み）
cat quote_escape.csv
id,name,age
"1","jo""hn","30"
"2","paul","31"
$ q -H -d, "select name from ./quote_escape.csv"
"jo""hn"
paul
フィールドの中に改行があるケース
cat quote_newline.csv
id,name,age
"1","jo
hn","30"
"2","paul","31"
$ q -H -d, "select name from ./quote_newline.csv"
jo
hn
paul
SJISのデータをみてみよう
$ cat sjis.csv
id,name,age
1,?W????,30
2,?|?[??,31
2,?W???`?W,29
$ nkf -u sjis.csv
id,name,age
1,ジョン,30
2,ポール,31
2,ジョ〜ジ,29
$ q -H -d, -eSJIS "select name from ./sjis.csv"
ジョン
ポール
ジョ〜ジ
$ q -H -d, -eShift-JIS "select name from ./sjis.csv"
ジョン
ポール
ジョ〜ジ
見える見える。SJISとShift-JISの両方でいける。
BOMがあっても大丈夫？
$ more bom.csv
<U+FEFF>id,name,age
1,john,30
2,paul,31
q -H -d, "select id from ./bom.csv"
query error: no such column: id
Warning - There seems to be a "no such column" error, and -H (header line) exists. Please make sure that you are using the column names from the header line and not the default (cXX) column names
ダメ。一番左のカラム名をBOMつきで指定しないとダメ。そして入力しずらいので、運用には乗らない。ここは成約として諦める。
ちなみに、BOMつきのUTF8は、Excel日本語版でも最初からUTF8として認識してくれるので、便利なのです。BOMつきUTF8のCSVならダブルクリックでExcelで開いても化けない。
バックスラッシュが混じっても大丈夫？
$ cat backslash.csv
id,name,age
1,"jo\""hn",30
2,paul,31
$ q -H -d, "select name from ./backslash.csv"
"jo""hn"""
paul
これ期待と違う、本当は jo\"hn であって欲しい。Excel方式では、バックスラッシュは意味を持たないが、ものによってはバックスラッシュをエスケープ文字扱いにしているのもある。これを防ぐには、「バックスラッシュをエスケープ文字扱いにしないでね」ってオプションが必要。
q -H -d, --disable-escaped-double-quoting "select name from ./backslash.csv"
"jo\""hn"
paul
これで、期待通り。つまりExcelベースのCSVをパースしたかったら、
q -H -d, --disable-escaped-double-quoting SQL
というのが基本。（なげー！）