BigQuery でデータ追加が意外と難しかったのでメモ #BigQuery

結論

× オペレーションが難しい
⚪︎ ドキュメントが難しい

きっかけ

それまでログに対して SELECT による集計等はやっていましたが, 直接 BigQuery のコマンドを使用してのデータ追加はやったことがありませんでした.
今回追加作業をするにあたって「MySQL とかみたいに INSERT バッチとかですぐ終わるでしょ」と高を括っていました.

Google Cloud SDK インストール

SDK をインストールすることによりコマンドライン上から BigQuery コマンドを実行できます.
https://cloud.google.com/sdk/
これは普通にダウンロード & インストールなので大丈夫.

bq コマンドのドキュメントを読む

https://cloud.google.com/bigquery/bq-command-line-tool?hl=ja
SDK インストールにより使用可能となった bq コマンドを使ってデータ追加すべくドキュメントを読みます.
プロジェクトの指定, データセット作成..., 等々読み進めて, テーブルの作成項目を見つけて次くらいかな? と思ったら,
「クエリの実行」
ああそうだよね. テーブル作ったら中身の確認をしないとね. じゃあ次だろう...
「このページは役立ちましたか？評価をお願いいたします。」
あれれ〜おかしいぞ〜

データ追加方法

こいつはアホじゃないかと思われた諸氏もいるかと思います,実際ググってみると、本当にやり方がわからない！のでやってみるといいと思います.
(BigQuery知識0の方に限る)

実は bq help で見れるコマンド一覧で大体わかるのですが, データ追加には

insert
load

の2つの方法があります. insert は少量のデータを行単位で追加, load はデータファイルを使用して大量のファイルを追加, ということができます.
ちなみに load は前項目ドキュメントにおいて, 「データからテーブル作成」の中で使用されているのでデータインポートっぽく見えて普通に追加に使えることがわかりづらいです.

insert を使う場合

dataset.table があるとして
echo '{"a":1, "b":2}' | bq insert dataset.table
または
bq insert dataset.table /tmp/mydata.json
このとき /tmp/mydata.json は "newline delimited JSON" つまり行区切りJSONです.
行単位で完結している JSON ファイルです.
例:

{"a":1, "b":2}
{"c":3, "d":4}
{"e":5, "f":6}

拡張子JSONですが行単位でしかJSONとして成立していない特別なデータ形式です.

load を使う場合

データファイルとして使えるのは先ほどの "newline delimited JSON", csv, avro になります.さらにデータスキーマの JSON も必要になります.

example_schema.json

[
  {
    "name": "dataA",
    "type": "STRING"
  },
  {
    "name": "dataB",
    "type": "INTEGER"
  }
]

load のデータファイルはデフォルトでは csv を使うようになっているので他形式を使う場合はオプションで指定する必要があります.

bq load --source_format=NEWLINE_DELIMITED_JSON dataset.table /tmp/mydata.json /tmp/example_schema.json

オプション, データファイル, スキーマの順で指定します.
ちなみにデータファイルはローカルより Google cloud storage からの方が速いです.
またテーブルは, あれば追記なければ作成, となります.
(あっても上書き新規作成オプションもあるという噂を聞きましたが調べた限り見つかりませんでした.ブラウザ上での操作ならできましたが)

BigQuery のデータ追加説明は以上になります.

おまけ

BigQuery は1カラム内に JSON形式で格納されている場合, JSON_EXTRACT 関数で取り出せるため STRING 型カラムに JSON を押し込むことがあります.
ただしこの形式のデータをデータファイルとして作成する場合一手間必要です.

例:

{"created": "201701010000", "log": "{"action": "post", "title": "Happy New Year!"}"}

みたいなデータをそのまま load しようとすると, スキーマに対して行多すぎエラーになります. なのでダブルクォーテーションをエスケープして

{"created": "201701010000", "log": "{\"action\": \"post\", \"title\": \"Happy New Year!\"}"}

とする必要があります. (カンマはエスケープ不要)