【新機能】BigQueryでJSON型が使えます！（BigQuery subscriptionsも対応）
データアナリティクス事業本部、池田です。
BigQueryのJSON型は申請が必要な プレビュー
という認識だったのですが、いつの間にか普通に使えるようになっていました！
（某所にも確認しましたが、GA（一般提供）という扱いで良いそうです。
以下の公式ガイドにもプレビューの記述はありません！）
【 Working with JSON data in Google Standard SQL 】
Amazon Redshift だと SUPER タイプ 、 Snowflake だと VARIANT などが近いかと思います。
STRUCT型 に比べて、スキーマオンリード的に、より柔軟に値を格納できます。
使ってみる
軽く使ってみます。
JSONリテラル
は以下のような構文です。
JSON ＜JSONの文字列＞
リファレンスの例を元に、簡単なSELECT文を動かしてみます。
-- CREATE TABLE my_json.verify AS
WITH sample_table AS (
SELECT JSON """{
"id": 10,
"type": "fruit",
"name": "apple",
"on_menu": true,
"recipes":
{
"salads":
[
{ "id": 2001, "type": "Walnut Apple Salad" },
{ "id": 2002, "type": "Apple Spinach Salad" }
],
"desserts":
[
{ "id": 3001, "type": "Apple Pie" },
{ "id": 3002, "type": "Apple Scones" },
{ "id": 3003, "type": "Apple Crumble" }
]
}
}""" AS json_col
)
SELECT json_col, json_col.id, json_col.recipes.salads FROM sample_table;
ドットで簡単に各要素へアクセスできていますね。
ちなみに、前述のSELECT文をCTASでテーブルにしてみると…
全てJSON型になっていました。
適切に扱うには適宜キャストが必要そうです。
… SELECT json_col, INT64(json_col.id), json_col.recipes.salads FROM sample_table;
nullが特殊
冒頭の公式ガイド で気になった記述が以下です。
The JSON type has a special null value that is different from the SQL NULL. A JSON null is not treated as a SQL NULL value, …
SELECT
JSON 'null' IS NULL AS a,
JSON_VALUE(JSON 'null') IS NULL AS b;
-- a b
-- false true
null の扱いには注意が必要そうです。
BigQuery subscriptions
以前ブログにしたBigQuery subscriptions（
Cloud Pub/Sub
からBigQueryへ直接データを投入する機能）でもメッセージをJSON型として格納できるようになりました。
【 Pub/Sub release notes > November 15, 2022 】
簡単に動かしてみます。 詳細は以前のブログをご覧下さい。
以前は（ トピック スキーマ
を使わずに）メッセージを受け取るのは data というSTRING型のカラムである必要がありました。
CREATE TABLE subscriptions.sample_with_meta (
subscription_name STRING,
message_id STRING,
publish_time TIMESTAMP,
data STRING,
attributes STRING
)
PARTITION BY DATE(publish_time);
↓↓↓↓↓↓↓↓
JSON型で受け取れます！
CREATE TABLE subscriptions.sample_with_meta_json (
subscription_name STRING,
message_id STRING,
publish_time TIMESTAMP,
data JSON,
attributes STRING
)
PARTITION BY DATE(publish_time);
※↑では直し忘れたのですが、 attributes カラムもJSON型で受け取れます。
一般に公開されているPub/Subのトピック をサブスクライブ登録します。
gcloud pubsub subscriptions create sample-with-meta-json-sub \ --topic=projects/pubsub-public-data/topics/taxirides-realtime \ --bigquery-table=my-project.subscriptions.sample_with_meta_json \ --write-metadata
データが格納されます。（↓画像は左が以前のもの、右が今回のもの。）
見た目ではSTRING型と区別がつかないです…
要素へのアクセス
以前は以下のように要素にアクセスして、ビューにしていました。
CREATE VIEW subscriptions.sample_view AS
SELECT
JSON_VALUE(data, '$.ride_id') AS ride_id,
SAFE_CAST(JSON_VALUE(data, '$.point_idx') AS INT64) AS point_idx,
SAFE_CAST(JSON_VALUE(data, '$.latitude') AS BIGNUMERIC) AS latitude,
SAFE_CAST(JSON_VALUE(data, '$.longitude') AS BIGNUMERIC) AS longitude,
SAFE_CAST(JSON_VALUE(data, '$.timestamp') AS TIMESTAMP) AS timestamp,
SAFE_CAST(JSON_VALUE(data, '$.meter_reading') AS NUMERIC) AS meter_reading,
SAFE_CAST(JSON_VALUE(data, '$.meter_increment') AS NUMERIC) AS meter_increment,
JSON_VALUE(data, '$.ride_status') AS ride_status,
SAFE_CAST(JSON_VALUE(data, '$.passenger_count') AS INT64) AS passenger_count
FROM
subscriptions.sample_with_meta
WHERE
DATE(publish_time) >= CURRENT_DATE()
;
※ JSON_VALUE() はSTRING型もJSON型も受け取れるので、実はこのアクセス方法もそのまま使えます。
↓↓↓↓↓↓↓↓
CREATE VIEW subscriptions.sample_view_json AS
SELECT
data.ride_id,
data.point_idx,
data.latitude,
data.longitude,
data.timestamp,
data.meter_reading,
data.meter_increment,
data.ride_status,
data.passenger_count
FROM
subscriptions.sample_with_meta_json
WHERE
DATE(publish_time) >= CURRENT_DATE()
;
だいぶシンプルに要素にアクセスできるようになりました。 （分かりやすくするためキャストは省略しました。）
おわりに
ずっと待っていました。
