BigQueryにJSONを読み込ませてQuery実行しようとしてハマっていた

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

一応全部

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

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

まとめ

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

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

ndjson(NEWLINE_DELIMITED_JSON)にしろ

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 経由ではなく生でデータを見たときに意味が分からないのでこの案は捨てた方がよいように思う。

  1. JSON で BigQuery にデータを投げる際、自分の中には使ったこともない BigTable に対する憧れのようなものがあって、なんか適当な構造のデータを投げても大丈夫だと思い込んでいたように思う。 

More

Categories

Tool 日々 Web Biz Net Apple MS ことば News Unix howto Food PHP Movie Edu Community Book Security Text TV Perl Ruby Music Pdoc 生き方 RDoc ViewCVS CVS Rsync Disk Mail FreeBSD Cygwin PDF Photo Zebedee Debian OSX Comic Cron Sysadmin Font Analog iCal Sunbird DNS Linux Wiki Emacs Thunderbird Sitecopy Terminal Drawing tDiary AppleScript Life Money Omni PukiWiki Xen XREA Zsh Screen CASL Firefox Fink zsh haXe Ecmascript PATH_INFO SQLite PEAR Lighttpd FastCGI Subversion au prototype.js jsUnit Apache Trac Template Java Rhino Mochikit Feed Bloglines CSS del.icio.us SBS qwikWeb gettext Ajax JSDoc Rails HTML CHM EPWING NDTP EB IE CLI ck ThinkPad Toy WSH RFC readline rlwrap ImageMagick epeg Frenzy sysprep Ubuntu MeCab DTP ERD DBMS eclipse Eclipse Awk RD Diigo XAMPP RubyGems PHPDoc iCab DOM YAML Camino Geekmonkey w3m Scheme Gauche Lisp JSAN Google VMware DSL SLAX Safari Markdown Textile IRC Jabber Fastladder MacPorts LLSpirit CPAN Mozilla Twitter OpenFL Rswatch ITS NTP GUI Pragger Yapra XML Mobile Git Study JSON VirtualBox Samba Pear Growl Mercurial Rack Capistrano Rake Win RSS Mechanize Sitemaps Android JavaScript Python RTM OOo iPod Yahoo Unicode Github iTunes God SBM friendfeed Friendfeed HokuUn Sinatra TDD Test Project Evernote iPad Geohash Location Map Search Simplenote Image WebKit RSpec Phone CSV WiMAX USB Chrome RubyKaigi RubyKaigi2011 Space CoffeeScript Nokogiri Hpricot Rubygems jQuery Node GTD CI UX Design VCS Kanazawa.rb Kindle Amazon Agile Vagrant Chef Windows Composer Dotenv PaaS Itamae SaaS Docker Swagger Grape WebAPI Microservices OmniAuth HTTP 分析基盤 CDN Terraform IaaS HCL Webpack Vue.js BigQuery Middleman CMS AWS PNG Laravel Selenium OAuth OpenAPI GitHub UML GCP TypeScript SQL Hanami Develop Document Jekyll