Upgrade to Pro — share decks privately, control downloads, hide ads and more …

datatech-jp Casual Talks #1 「BigQueryのネイティブJSON...

Shuichi Ohsawa
February 07, 2022

datatech-jp Casual Talks #1 「BigQueryのネイティブJSON型がサポートされたので触ってみた」 / support-bigquery-native-json-and-try-it

2022/02/07 datatech-jp Casual Talks #1 の資料です。
https://datatech-jp.connpass.com/event/236698/

Shuichi Ohsawa

February 07, 2022
Tweet

More Decks by Shuichi Ohsawa

Other Decks in Technology

Transcript

  1. BigQueryにおけるJSONの扱いについて 4 • すでにJSON関数が提供されている • JSON_QUERY、JSON_VALUE、JSON_QUERY_ARRAY etc • 簡単な構造なら抽出に問題ないが、入れ子になっていたり 配列が入るとパースするのが大変

    • 今まではSQLやUDF(ユーザー定義関数)を使って スキーマを定義することでパースしていた CREATE TEMPORARY FUNCTION parse_json_data(json_data STRING) RETURNS STRUCT<id INT64, name STRING, person_info ARRAY<STRUCT<key STRING, value STRING >>> LANGUAGE js AS "return JSON.parse(json_data);"; WITH input_data AS ( SELECT '...JSON String...' AS json_data ), parsed AS ( SELECT parse_json_data(json_data).* FROM input_data ) SELECT id, name, person_info.key AS key, person_info.value AS value FROM parsed LEFT JOIN UNNEST(parsed.person_info) AS person_info;
  2. BigQueryネイティブJSON型がサポート 5 • 2022年2月時点でPublic Preview • あらかじめスキーマを決めておく必要がない • PARSE_JSON関数で文字列をパースするとJSON型になる •

    SAFE.PARSE_JSONはパースできない場合NULLを返す • ドット表記でアクセス可能 • 空白などドット表記できない場合は “[ ]” で指定可能 • 例:person.info[“User Name”] WITH input_data AS ( SELECT SAFE.PARSE_JSON('...JSON String...') AS json_data ) SELECT json_data.id, JSON_VALUE(json_data.name) AS name, JSON_VALUE(person_info.key) AS key, JSON_VALUE(person_info.value) AS value FROM input_data LEFT JOIN UNNEST(JSON_QUERY_ARRAY(json_data.person_info)) AS person_info;
  3. BigQueryに取り込むときにファイルフォーマットに注意 6 • JSONファイルをJSON型として読み込む場合 • 外部テーブル(GCS)、bq load • JSONファイルだからといって、JSONLを指定すると エラーになる

    • NEWLINE_DELIMITED_JSON does not support JSON columns • JSONL以外のフォーマット(CSVなど)を指定する • filed_delimiter はTABなどJSONファイル内に存在しない 文字を指定 ※ JSONL = Newline delimited JSON $ bq load ¥ --source_format=CSV ¥ --field_delimiter="¥t" ¥ json_example.example_json ¥ example_json.json ¥ json_data:JSON CREATE OR REPLACE EXTERNAL TABLE json_example.example_json( json_data JSON, ) OPTIONS ( format='CSV', field_delimiter="¥t", allow_jagged_rows=false, allow_quoted_newlines=false, skip_leading_rows=0, ignore_unknown_values=false, max_bad_records=0, uris=['gs://example-bucket/example_json.json'] );
  4. PARSE_JSON関数における数値丸めについて 7 • PARSE_JSONはデフォルトでは数値を丸めない • 小数点桁数が多い場合にエラーになってしまう • Invalid input to

    PARSE_JSON: Input number: xxxxx cannot round- trip through string representation • `wide_number_mode=“round”` にすると丸められるので エラーにならない • 精度を求められる場合はJSON文字列として入れたほうがいい -- エラーになる SELECT PARSE_JSON('{"key":64.09999999999999}') -- エラーにならない(64.1が返る) SELECT PARSE_JSON('{"key":64.09999999999999}’, wide_number_mode=>"round") -- エラーにならない SELECT PARSE_JSON('{"key":"64.09999999999999"}')
  5. パフォーマンス 8 • 約1TBのJSONデータをSTRING型、JSON型それぞれでロード&抽出して比較してみた • JSON型でロードする場合、パースするのでSTRING型より遅い • テーブルサイズはSTRING型より約45%少ない • 構造化されているから無駄なサイズが削減されている?

    • JSON_QUERYで取り出すときはJSON型の方が早い ロード(Load) Duration テーブルサイズ STRING型 1 min 4 sec 985.35 GB JSON型 2 min 28 sec 436.16 GB 抽出(Extract) Duration Bytes billed STRING型 1 min 22 sec 973.82 GB JSON型 34.5 sec 424.62 GB
  6. まとめ 9 • BigQueryのネイティブJSON型は柔軟な構造に対してデータ操作がしやすい • 特に構造が複雑なJSONデータだと恩恵を受けやすい • PARSE_JSON関数で文字列をパースするだけでJSON型になる • ファイルフォーマットには注意

    • 数字の丸めには注意 • データ取り込み時は時間かかるが、テーブルサイズが縮小され、SELECTの処理時間が早い • Public Previewなので利用したい方は登録フォームまで