BigQueryのネイティブJSON型が サポートされたので触ってみた 大澤 秀一 @ohsawa0515

2 @ohsawa0515 https://blog.jicoman.info/about @ohsawa0515

アジェンダ 3 • BigQueryにおけるJSONの扱いについて • BigQueryのネイティブJSON型について • ネイティブJSON型を触ってみた 詳細についてはZennに書いているのでご参考までに https://zenn.dev/ohsawa0515/articles/support-bigquery-native-json

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>> 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;

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;

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'] );

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"}')

パフォーマンス 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

まとめ 9 • BigQueryのネイティブJSON型は柔軟な構造に対してデータ操作がしやすい • 特に構造が複雑なJSONデータだと恩恵を受けやすい • PARSE_JSON関数で文字列をパースするだけでJSON型になる • ファイルフォーマットには注意 • 数字の丸めには注意 • データ取り込み時は時間かかるが、テーブルサイズが縮小され、SELECTの処理時間が早い • Public Previewなので利用したい方は登録フォームまで