bigdata

q - Text as Data でどこまでCSVがパースできるのか

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

というのが基本。(なげー!)