【新機能】BigQueryでJSON型が使えます!(BigQuery subscriptionsも対応)

BigQueryのJSON型(JSON type)が一般提供。 BigQuery subscriptionsのメッセージ格納先でもJSON型が使えるようになった。
2022.11.22

データアナリティクス事業本部、池田です。
BigQueryJSON型は申請が必要な プレビュー という認識だったのですが、いつの間にか普通に使えるようになっていました! (某所にも確認しましたが、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 subscriptionsCloud Pub/Sub からBigQueryへ直接データを投入する機能)でもメッセージをJSON型として格納できるようになりました。
Pub/Sub release notes > November 15, 2022

簡単に動かしてみます。 詳細は以前のブログをご覧下さい。

BigQuery subscriptionsを使ってPub/Subと直接連携する

以前は( トピック スキーマ を使わずに)メッセージを受け取るのは 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型と区別がつかないです…

要素へのアクセス

以前は以下のように要素にアクセスして、ビューにしていました。

STRING型のdataカラム

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型も受け取れるので、実はこのアクセス方法もそのまま使えます。

↓↓↓↓↓↓↓↓

JSON型のdataカラム

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

だいぶシンプルに要素にアクセスできるようになりました。 (分かりやすくするためキャストは省略しました。)

おわりに

ずっと待っていました。

関連情報/参考にさせていただいたページ