コマンドラインツールのインストール
$ easy_install bigquery
$ bq init
ロード(テーブル作成・データ投入; project と dataset までは作成済みという前提)
$ bq load --source_format=NEWLINE_DELIMITED_JSON mydataset.nested01 nested01.data.json nested01.fields.json
Waiting on job_d7ebcad03b8247fc8b6f8313f0689a28 ... (26s) Current status: DONE
$ bq query "SELECT note, fullName, age, gender, citiesLived.place FROM mydataset.nested01"
Waiting on job_2079eaaf09ec4b5081f4ed1b037137a2 ... (0s) Current status: DONE
+------------------------------------+-------------+------+--------+--------------------+
| note | fullName | age | gender | citiesLived_place |
+------------------------------------+-------------+------+--------+--------------------+
| NULL | John Doe | 22 | Male | Seattle |
| NULL | John Doe | 22 | Male | Stockholm |
| NULL | Jane Austen | 24 | Female | Los Angeles |
| NULL | Jane Austen | 24 | Female | Tokyo |
| with newline (\n) | newline | 30 | Male | Shinjuku-ku, |
| | | | | Tokyo |
| with tab (\t) | tab | 30 | NULL | Shinjuku-ku, Tokyo |
| japanese / unicode escape sequence | 横浜 花子 (1) | NULL | Female | 横浜市 |
| japanese / raw utf-8 | 横浜 花子 (2) | NULL | Female | 横浜市 |
| double-quoted string | "quoted" | NULL | NULL | NULL |
+------------------------------------+-------------+------+--------+--------------------+
- コマンド実行時に
とかやるとリクエストとレスポンスの内容がみれる。bq --apilog=- load ... - NEWLINE_DELIMITED_JSON 形式でのインポート
- https://developers.google.com/bigquery/docs/import#jsonformat
- インポートするデータのフォーマットとして JSON を使える。
-
ファイルの1行1行が行データを表現するJSON の object で、行末を改行で区切る。
``` {"field1": "value1", "field2": "value2", ...}[LF] {"field1": "value1", "field2": "value2", ...}[LF] {"field1": "value1", "field2": "value2", ...} ``` -
行ごとに改行区切りという仕様だが、行単位で valid な JSON になってればOK
- もちろんフィールドの順番も関係ない
- ユニコードエスケープシーケンスでも元のUTF-8文字列が登録される(日本語の検索もできる)
- Python の場合、json.dumps() でよい
- 生データのサイズ的には ensure_ascii=False するほうがよいか?
-
- メリット
- 構造化された(入れ子になった) データを1行にもつことができる
- GAE の RequestLog と AppLog の関係 (one-to-many) のような
- 生データ(BigQueryにインポートするためにCloud Storage に保管するデータ) がCSVよりは説明的になる
- カラムの追加には強い (後述)
- 投入先テーブルが1つになるので、データの投入プログラムはすっきりしそう
- 構造化された(入れ子になった) データを1行にもつことができる
- ロードすると、"あらかじめ JOIN されたテーブル" とでもいうようなイメージになる
- BigQuery での 2つのテーブルの JOIN にはサイズ制限があるので、使い途はある
- Big JOIN がサポートされたのでこの点のメリットは薄くなった
- ただし1行あたりのサイズ制限(<= 64KB)には注意
- BigQuery での 2つのテーブルの JOIN にはサイズ制限があるので、使い途はある
- value が null の場合はフィールドそのもの(key-value のペア)を落とすとよい
- => 行のデータが、BigQuery テーブルの行のサブセットでさえあれば投入できる
- => カラムを追加した新しいテーブルに、古いデータをそのままインポートできる
- 未使用の予備のカラムの分まで生データ(Google Cloud Storage 上のオブジェクト) に書き込んでおく必要はない
-
(あとからスキーマ変更はできないので、予備の列を多めにつくっとけ、という話がある)
I need to add another column to my schema. But BigQuery schema are immutable. Solution: Add generic columns for future use at table creation time and populate with nulls.
BigQuery can efficiently store and process empty columns in your data, so you can add unused columns to your schema upon table creation without affecting storage or performance. Adding new columns to a schema would require creating a new table and reimporting all of your data. As your database grows to billions of rows, this reimport becomes more expensive in time and effort, so you can avoid this by adding any columns you anticipate needing before you add your data.
A spare string, integer, or float column could come in handy in the future!
https://developers.google.com/bigquery/articles/ingestionbestpractices
-
カラムの追加もサポートされたので予備カラムは不要になった? (http://googledevelopers.blogspot.jp/2013/03/bigquery-gets-big-new-features-to-make.html)
-
- GAE のログ転送に使う場合
- appengine-mapreduce の LogInputReader で読み込んで行ごとに json.dumps してGoogle Cloud Storage 上のファイルに追記していく
- BigQuery 行のサイズが64KBまでなので
- AppLog まで含める場合は念のためサイズのチェックしたほうが良い
- ちなみに AppLog のメッセージ最大長は 1件あたり 8192 bytes ぽい(GAEデプロイ環境のみ)
- 1000jobs/day per table という import の quota がある
- 転送job を2分に1回まで頻度増やしたとしてもQuota的には十分 (パフォーマンス的には追いつかないかも)