BigQuery に JSON を読み込ませて Query 投げようとしてどうしてもうまくいかなくて泣いていたんだけど、何を勘違いしていたか分かったのでまとめておく。¹

一応全部

Google Cloud Storage から JSON データを読み込む  |  BigQuery  |  Google Cloud

に書いてある。分かりにくいけど。

まとめ

• BigQuery は SQL
• SQL はテーブル、言い方を変えると行指向

もともと1行1レコードのデータ（例えばログ）に向いている。ドキュメント指向な複雑な構造の JSON データはそもそも向いていない。

ndjson(NEWLINE_DELIMITED_JSON)にしろ

• ndjson
• Newline delimited JSON is awesome – Jaga Santagostino – Medium

BigQuery は読み込める JSON 形式を Newline Delimited JSON と名乗っている。これは複数レコードに渡る JSON データを [] と , で区切るのではなく、改行で区切ってしまおうというもの。

NG

[
  {"key1": "val1", "key2": "val2"},
  {"key1": "val2", "key2": "val2"},
  ..
]

OK

{"key1": "val1", "key2": "val2"}
{"key1": "val2", "key2": "val2"}

まぁここはそんなに難しくないと思う。

nested and repeated JSONの意味

BigQuery は 1レコード内に object をそのまま含むことはできない。これをやりたい場合は nested and repeated JSON にするとよい。

ここは日本語訳が恐らく間違っているので英語の方を載せておくと

Loading nested and repeated JSON data

BigQuery supports loading nested and repeated data from source formats that support object-based schemas, such as JSON, Avro, Cloud Firestore, and Cloud Datastore.

Loading JSON Data from Google Cloud Storage  |  BigQuery  |  Google Cloud

どういうことかというと、以下のような話。

NG

{
  "key1": "val1",
  "parent": {
    "childkey1": "val1",
    "chidlkey2": "val2"
  }
}

OK

{
  "key1": "val1",
  "children": [
    {"childkey1": "val1"},
    {"childkey2": "val2"}
  ]
}

or

{
  "key1": "val1",
  "children": [
    {"childkey1": "val1"},
    {"childkey1": "val2"}
  ]
}

これは JSON だけでなく、複雑な構造を扱えるデータソースならすべて同様になるらしい。

ただしこれがクセモノで、ややこしいのは、この NG のケースのデータ、実は data load もできるし、レコードが一つしかない場合は query 実行もできる。レコードが複数になった途端、dataset が not found と言われたり、JSON table encountered too many errors と言われる。これらの現象から原因を推測するのは正直厳しい。

それでも以下のおまけにあるような形式にするよりはややこしくなさそうなので、nested and repeated JSON にする、という方針がよさそうだと思っている。

ということで API のレスポンスなんかの JSON の形式とは別に、分析用のデータ（ログとか）に関しては nested and repeated つまり

{
  key: val,
  key2: val2,
  items: [
    {},
    {}
  ]
}

みたいな形式を採用しよう！と思いましたとさ。

おまけ

制限事項には以下のように書いてあって、

制限事項

Cloud Storage から BigQuery に JSON データを読み込む際は、以下の点に注意してください。

BigQuery では JSON のマップや辞書がサポートされません。たとえば、"product_categories": {"my_product": 40.0} は無効ですが、"product_categories": {"column1": "my_product" , "column2": 40.0} は有効です。

同じことやんけ！という気がするが、column1, column2, … という名前は予約されている、と解釈すると BigQuery 向けに column1, column2 と key を名付けて構造を repeated and nested にしない、という選択もあり得るが、ややこしすぎるし、BigQuery 経由ではなく生でデータを見たときに意味が分からないのでこの案は捨てた方がよいように思う。