BigQueryでSaaSのjsonデータを処理するSQLサンプル集

この記事の概要

SaaSのデータをBigQueryに統合することで業務改善を促進できる。 しかし、SaaSのデータの中身を見ると、BigQueryの関数では対応しにくい形式になっていることがある。 そこで、本稿では「こういうデータ形式だったらこういうSQLを書く」というサンプル集を掲載する。

目次

宣伝

• 本稿は風音屋の技術アドバイザー3名にレビューを受けてブラッシュアップしています。
• 風音屋では SQLとBIツールが得意な人材を募集中 です。

実現したいこと

SaaSのデータをBigQueryに集約することで、データドリブンな業務改善を実現したい。

例えば、営業ツール（SalesforceやHubSpot）とカスタマーサポートツール（Zendesk）のデータを統合してみよう。 営業ツール単体では「スタッフごとの契約件数」しか集計できない。 この場合、以下のような弊害が起きる。

	Aさん	Bさん
ボーナス	500万円	250万円
契約件数	100件	50件
契約後の苦情・解約	80件	10件
継続契約してくれた顧客	20件	40件

契約を獲得するごとに5万円のインセンティブ（ボーナス）がもらえるとしよう。 Bさんに比べるとAさんのほうが高いボーナスを受け取ることになる。 だが、Aさんの担当顧客のうち80%が苦情・解約に繋がった。残っているのは20件だけだ。 一方で、Bさんの契約は50件だけだが、苦情・解約は少なく、40件も残っている。

明らかにAさんよりもBさんのほうが高いボーナスをもらうべきだろう。 しかし、営業ツールで分かるのは契約件数だけで、苦情・解約までは評価できない。 そのため「Aさんのやり方が成功」「Bさんのやり方は失敗」という前提のもとで、誤った営業方法が社内に定着してしまう。 LTV（売上）とCAC（費用）が連動しないため、巨額の機会損失が生じてしまうだろう。

このような事態を避けるためには、営業ツールとカスタマーサポートツールのデータを統合しなければいけない。 契約だけではなく、クレームや解約のデータを紐付けて、部署や工程を横断した成果指標をトラッキングする必要がある。 つまり複数のSaaSからデータを一箇所に連携することになる。

データ統合に適したデータベースは「データウェアハウス」と呼ばれる。 本稿で取り上げる「BigQuery」は代表的なデータウェアハウスの1つである。

筆者はGoogle社が主催する「Google Cloud Day: Digital '21」というカンファレンスに登壇して、上記についてプレゼンテーションを行った。 上記で掲載している2枚の画像は登壇スライドを転載したものだ。

speakerdeck.com

上記の事例では「trocco」と呼ばれるツールを使ってSaaSのデータを統合した。 troccoを運営するprimeNumber社はシリーズBで13億円を調達し、スタートアップ業界で注目を集めている。 筆者もprimeNumber社に記事を寄稿している。

blog.trocco.io

ちなみに、Salesforceなどの営業ツールは、機能面だけを見るとカスタマーサポート部門でも利用できる。 しかし、部署が分かれてしまうとツールも分かれてしまうことが多い。 ITシステムは組織構造に準拠する形で管理される傾向がある。 これはIT業界では「コンウェイの法則」として知られている。 残念ながらコンウェイの法則に対抗するには負担や時間がかかる。 「複数の部署に1つのツールを徹底すること」や「複数の部署を統合すること」よりも「1つの部署が独自にツールを導入すること」のほうが圧倒的に簡単だからだ。 短期視点ではツールの散在を前提にしたほうが建設的だろう。

SaaSデータの処理方法

SaaSのデータを処理するときには「jsonデータ」の存在を無視できない。

一般的なデータベースでは、以下のような形式でデータを管理する。

id	username	email
1	ゆずたそ	xxx@yuzutas0.com
2	柚子太郎	yyy@yuzutas0.com
3	名前	zzz@yuzutas0.com

「id = 1」に該当するレコード（行）は「username = ゆずたそ」「email = xxx@yuzutas0.com」として判断できる。 これをSQLで抽出する場合、以下のようなクエリを書く。

SELECT
    username,
    email,
FROM
    データセット.顧客テーブル
WHERE
    id = 1

一方でSaaSのデータは以下のようになっていることが多い。 利用者がパラメータを柔軟に追加できるように、データ構造をjsonという形式で管理している。

custom_field_1	custom_field_2	custom_field_3
{'name': 'id', 'value': 1}	{'value': 'ゆずたそ'}	[{'email': 'xxx@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'},{'email': 'xxx-2@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'}]
{'name': 'id', 'value': 2}	{'value': '柚子太郎'}	[{'email': 'yyy@yuzutas0.com', 'tel': 'yyy-yyyy-yyyy'}]
{'name': 'id', 'value': 3}	{'value': '名前'}	[{'email': 'zzz@yuzutas0.com', 'tel': ''}]

「id = 1」に該当するレコード（行）として「username = ゆずたそ」「email = xxx@yuzutas0.com」を抽出するには、以下のようなクエリを書かなければいけない。

SELECT
    JSON_VALUE(t1.custom_field_2, '$.value') AS username,
    JSON_VALUE(contact, '$.email') AS email,
FROM
    データセット.顧客テーブル AS t1
LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY(t1.custom_field_3)) AS contact
WHERE
    JSON_VALUE(t1.custom_field_1, '$.value') = 1

先ほどの例に比べると、複雑になっているのが分かるだろう。

jsonは {} で囲うことが望ましい。 BigQueryではjsonデータを処理（パース）するために JSON_VALUE() という関数を用意している。 この関数は {} でデータが囲われていることを前提としている。 例えば、上記SQLのWHERE句が該当する。 {'name': 'id', 'value': 1} から 1 を抽出するには JSON_VALUE(t1.custom_field_1, '$.value') と書けば良い。

一方で、SaaSのデータは [] で始まっているケースが見受けられる。 [] で始まっているケースとは、具体的には以下のようなデータ構造を指す。

[
  {
    'email': 'xxx@yuzutas0.com',
    'tel': 'xxx-xxxx-xxxx'
  },
  {
    'email': 'xxx-2@yuzutas0.com',
    'tel': 'xxx-xxxx-xxxx'
  }
]

この形式のデータを扱うには前処理が必要となる。

1. JSON_QUERY_ARRAY() という関数で文字列から配列型に変換する。
2. UNNEST() でレコードを分割する。

SQLで表現すると以下のようになる。

UNNEST(JSON_QUERY_ARRAY(t1.custom_field_3)) AS contact

この処理をLEFT JOIN句の中で実施する。 対象となる列（カラム）が複数ある場合は、複数のLEFT JOINを書く。 ここでは、INNER JOINやCROSS JOINではなくLEFT JOINを使うことに注意してほしい。 ネット上でBiqQueryのUNNESTのサンプルクエリを探すと、暗黙的にCROSS JOINを用いている例が散見される。 しかし、INNER JOINやCROSS JOINだと該当カラムがNULLだった場合にレコードが欠損してしまう。

結果として以下の2つのレコードが出来る。

{
  'email': 'xxx@yuzutas0.com',
  'tel': 'xxx-xxxx-xxxx'
}

{
  'email': 'xxx-2@yuzutas0.com',
  'tel': 'xxx-xxxx-xxxx'
}

これらは {} で囲まれているので、先ほど紹介した JSON_VALUE() 関数で処理できる。 メールアドレスを取得したい場合は以下のクエリで抽出できる。

SELECT JSON_VALUE(対象データ, '$.email')

以上が基本的なアプローチとなる。 一連の処理を行っているのが、先ほど紹介したSQLである。

SELECT
    JSON_VALUE(t1.custom_field_2, '$.value') AS username,
    JSON_VALUE(contact, '$.email') AS email,
FROM
    データセット.顧客テーブル AS t1
        LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY(t1.custom_field_3)) AS contact
WHERE
    JSON_VALUE(t1.custom_field_1, '$.value') = 1

ここから先はケース別のSQLサンプルを見ていこう。

SQLサンプル1: 純粋な配列だけのケース

id	custom_field
1	["ゆずたそ","柚子太郎"]
2	[]

データ形式が上記の場合、SQLは以下のようになる。

SELECT
    id,
    JSON_VALUE(custom_field) AS username,
FROM
    データセット.顧客テーブル AS t1
LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY(t1.custom_field)) AS custom_field

• JSON_QUERY_ARRAY() と UNNEST() で配列を複数のレコードに分割する。
• JSON_VALUE() で中身を取得する。

以下の結果を得られる。

id	username
1	ゆずたそ
1	柚子太郎
2	NULL

SQLサンプル2: 配列内にハッシュマップがあるケース

id	custom_field
1	[{'email': 'xxx@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'},{'email': 'xxx-2@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'}]
2	[]

データ形式が上記の場合、SQLは以下のようになる。

SELECT
    id,
    JSON_VALUE(contact, '$.email') AS email,
FROM
    データセット.顧客テーブル AS t1
LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY(t1.custom_field)) AS contact

このデータ形式は冒頭の例と同じだ。

• JSON_QUERY_ARRAY() と UNNEST() で配列を複数のレコードに分割する。
• JSON_VALUE() で中身を取得する。取得する値（email）を明示する。

以下の結果を得られる。

id	email
1	xxx@yuzutas0.com
1	xxx-2@yuzutas0.com
2	NULL

SQLサンプル3: 配列宣言ナシでカンマ区切りのハッシュマップが突如始まるケース

id	custom_field
1	{'email': 'xxx@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'},{'email': 'xxx-2@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'}
2	NULL

データ形式が上記の場合、SQLは以下のようになる。

SELECT
    id,
    JSON_VALUE(contact, '$.email') AS email,
FROM
    データセット.顧客テーブル AS t1
LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY("[" || t1.custom_field || "]")) AS contact

• 文字列の前後を [] で囲んで、サンプル2と同じ形式にする。
• そこから先はサンプル2と同じ処理を実施する。

以下の結果を得られる。

id	email
1	yyy@yuzutas0.com
1	xxx-2@yuzutas0.com
2	NULL

SQLサンプル4: 配列とハッシュが入り乱れるケース

id	custom_field
1	{'email': 'xxx@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'},{'email': 'xxx-2@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'}
2	[{'email': 'xxx-3@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'},{'email': 'xxx-4@yuzutas0.com', 'tel': 'xxx-xxxx-xxxx'}]
3	NULL

柔軟な設定ができるSaaSで、途中からデータ形式を変更した場合に、上記のようなレコードが生成されるケースがある。 差分更新でDWHにデータを投入している場合は、バックフィリング（Backfilling、過去データの再取得のこと）で修正されることもあるので試してほしい。

データ形式が上記の場合、SQLは以下のようになる。

WITH
cleansed AS (
    SELECT
        id, -- SELECT * EXCEPT(custom_field), にするとカラムが増えても対応可能
        IF(
            SUBSTRING(custom_field, 0, 1) = "{",
            "[" || t1.custom_field || "]",
            custom_field
        ) AS contact
    FROM
        データセット.顧客テーブル AS t1
)
SELECT
    id,
    JSON_VALUE(contact, '$.email') AS email,
FROM
    cleansed
LEFT JOIN
    UNNEST(JSON_QUERY_ARRAY(cleansed.contact)) AS contact

• データクレンジングの前処理をWITH句で実施する
• 最初の1文字が { の場合（サンプル3と同じ形式）は、前後に [ と ] を付け足して [] の形式（つまりサンプル2と同じ形式）に寄せる
• サンプル2と同じ形式になっているので、そこから先はサンプル2と同じ処理を行う。

以下の結果を得られる。

id	email
1	xxx@yuzutas0.com
1	xxx-2@yuzutas0.com
2	xxx-3@yuzutas0.com
2	xxx-4@yuzutas0.com
3	NULL

SQLサンプル5: JSONの中に親子構造があるケース

id	custom_field
1	{'key': 'tag', 'value': '飲み物', 'child': {'key': 'tag', 'value': 'ソフトドリンク'}}
2	{'key': 'tag', 'value': '飲み物', 'child': {'key': 'tag', 'value': 'お酒'}}
3	{'key': 'tag', 'value': 'デザート'}
4	NULL

レコードによって階層が異なっている。 「飲み物」については、さらに細分化して「ソフトドリンク」や「お酒」というタグがある。 一方で「デザート」については1階層だけで完結している。

データ形式が上記の場合、SQLは以下のようになる。

SELECT
    id,
    JSON_VALUE(custom_field, '$.value') AS parent_tag,
    JSON_VALUE(custom_field, '$.child.value') AS child_tag,
    IF(
        JSON_VALUE(custom_field, '$.child.value') IS NOT NULL,
        JSON_VALUE(custom_field, '$.value') || "__" || JSON_VALUE(custom_field, '$.child.value'),
        JSON_VALUE(custom_field, '$.value')
    ) AS tag,
FROM
    データセット.カテゴリテーブル AS t1

• 親タグや子タグ単体は JSON_VALUE() でシンプルに抽出できる。
• フィルター用のカラムは $.child.value が存在するかどうかを確認し、TRUEであれば「親__子」、FALSEであれば「親」でデータを保存する。

以下の結果を得られる。

id	parent_tag	child_tag	tag
1	飲み物	ソフトドリンク	飲み物__ソフトドリンク
2	飲み物	お酒	飲み物__お酒
3	デザート	NULL	デザート
4	NULL	NULL	NULL

「親タグ」や「子タグ」とは別に「親タグ__子タグ」を組み合わせたカラムを用意した。 LookerなどのBIツールでフィルターを作ってデータを絞り込むことを想定している。 データ利用者はこの1つのカラムに対して検索をかければ、欲しいデータに辿り着ける。 「飲み物」という内容が親と子のどちらかに属しているかわざわざ覚えなくても済む。

SQLサンプル6: Objectを定義したくなるケース

custom_field
[{"id":"1","comments":["id":"1","content":"テストコメント1","userId":"100","userName":"テストユーザー100","createdAt":"2022-01-01 12:00:00","id":"2","content":"テストコメント2","userId":"200","userName":"テストユーザー200","createdAt":"2022-01-01 12:30:00"],"info":{"roomName":"チャット機能のテスト部屋1","roomPhotoUrl":"https://example.com"}}, {"id":"2","comments":["id":"1","content":"テストコメント3","userId":"100","userName":"テストユーザー100","createdAt":"2022-01-01 18:00:00","id":"2","content":"テストコメント4","userId":"200","userName":"テストユーザー200","createdAt":"2022-01-01 20:30:00"],"info":{"roomName":"チャット機能のテスト部屋2","roomPhotoUrl":"https://example.com"}}]
NULL

実は箇条書きだとシンプルに表現できるのだが、1カラムやSQLで素直に表現しようとすると、複雑に感じる。 データ形式が上記の場合、SQLは以下のようになる。

CREATE TEMPORARY FUNCTION parse_chatroom_json(s string)
RETURNS ARRAY<STRUCT<
    id string,
    comments ARRAY<STRUCT<
        id string,
        content string,
        userId string,
        userName string,
        createdAt string
    >>,
    info STRUCT<
        roomName string,
        roomPhotoUrl string
    >
>>
LANGUAGE js AS "return JSON.parse(s);";

WITH
parsed1 AS (
    SELECT
        parse_chatroom_json(custom_field) AS chatrooms,
    FROM
        データセット.チャットテーブル
),

parsed2 AS (
    SELECT
        CAST(chatroom.id AS INT64) AS id,
        chatroom.comments          AS comments,
        chatroom.info.roomName     AS info_roomname,
        chatroom.info.roomPhotoUrl AS info_roomphotourl,
    FROM
        parsed1
    LEFT JOIN
        UNNEST(chatrooms) AS chatroom
)
SELECT
    parsed2.* EXCEPT(comments),
    CAST(comment.id AS INT64)     AS comment_id,
    comment.content               AS comment_content,
    CAST(comment.userId AS INT64) AS comment_userid,
    comment.userName              AS comment_username,
    TIMESTAMP(comment.createdAt)  AS comment_createdat,
FROM
    parsed2
LEFT JOIN
    UNNEST(comments) AS comment

• BigQueryのJavaScriptUDF関数で ARRAY と Struct を組み合わせてObjectの型を定義する。
• 該当の関数を呼び出してjsonをパースする。
• 他のサンプルと同じように [] で囲われている箇所はLEFT JOINと UNNEST() でレコードを分ける。
• パース結果をテーブル形式で返すようにSQLを調整する。
• 必要に応じて数値型やタイムスタンプ型にキャスティングする。

以下の結果を得られる。

id	info_roomname	info_roomphotourl	comments_id	comments_content	comments_userid	comments_username	comments_createdat
1	チャット機能のテスト部屋1	https://example.com	1	テストコメント1	100	テストユーザー100	2022-01-01 12:00:00 UTC
1	チャット機能のテスト部屋1	https://example.com	2	テストコメント2	200	テストユーザー200	2022-01-01 12:30:00 UTC
2	チャット機能のテスト部屋2	https://example.com	1	テストコメント3	100	テストユーザー100	2022-01-01 18:00:00 UTC
2	チャット機能のテスト部屋2	https://example.com	2	テストコメント4	200	テストユーザー200	2022-01-01 20:30:00 UTC
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

フラットなテーブルにしたが、必要に応じて正規化しても良いだろう。

最強のJSONパースの関数は作れるか

本稿では複数のSQLサンプルを提示してきた。 これらを読んで真っ先に考えることは「汎用的なパース処理を作れないか？」だろう。 もし「手元に最強のパース処理がある」という方がいたらぜひ共有してほしい。

関数を作ることで給料が上がる立場ならぜひ挑戦するのが良いと思う。 ただ、データ活用の促進がミッションなら、TODOリストに追加するのは後回しでも良いかもしれない。 人によって考え方が分かれるところだが、管理職の立場で短期的な費用対効果を考えると、 テーブルごとに個別に処理を作っても良いのではないか？と思ったりはする。

いざ汎用的な処理を作ろうとしても、イレギュラーなパターンが次々に出てきたり、 スクリプトが複雑になるとUDFsのクオータ（容量制限）に引っかかったり、 データ量が多いテーブルだとタイムアウトエラーが発生したり、 なんだかんだで個々の処理をチューニングすることになったりする。 色々と試行錯誤した結果、 「そもそも簡単に作れるならGoogleのエンジニアが既にBigQueryの関数として提供しているのではないか」 「そのような汎用的な関数が提供されていないということはつまり……」 という考えが脳内をよぎったりする。

jsonデータの処理をどこで行うか

上記のような処理をシステム全体のどこで行うべきだろうか。 多くの現場ではSaaSからデータを取得するときに直接データを加工している。 担当者の言い分は「このほうがデータを使いやすいから」だ。 気持ちは分かる。 しかし、残念だが、これはアンチパターンだ。

原則的には「元データのコピーは加工せずにそのまま持っておく」ことを勧めたい。 よかれと思ってデータ加工をしても、後から「やっぱり別の加工方法に変えたい」と考えが変わることがある。 ひとまずは元データのコピーを残しておいて、そのコピーとは別に集計処理を実施しよう。 以下のブログでは「データレイク層」という概念で説明している。

yuzutas0.hatenablog.com

同じ内容は、私の書籍『実践的データ基盤への処方箋』（技術評論社）でも解説している。 まだ読んでいないのであればぜひ読んでほしい。

yuzutas0.hatenablog.com

以上を踏まえるとデータの流れは以下のようになる。

1. SaaSからデータをDWH（今回はBigQuery）にjson形式のままコピーしてデータレイク層として管理する。ここでは加工処理は行わない。
2. データレイク層のjsonデータを加工して、別の中間テーブルとして保存する。ここで上記のSQLサンプル集を使う。
3. 中間テーブルを参照してデータを活用する。

もし集計方法を変更したくなったら、2のSQLを修正して中間テーブルを作り直そう。

最後に

今後ますますSaaSの利用は活発になるだろう。 自社開発やインストールが不要で、課金するだけですぐにツールを使えるというのは魅力的だ。 ありとあらゆる部門でSaaSを活用した業務改善が進むように思う。

だからこそ部門横断のデータ統合、ならびに部門横断のKPIモニタリングは重要性を増していくはずだ。 データ（Data）をもとに業務（Ops）を改善していく人たちにとって、本稿が一助になると幸いである。

もしSaaSデータの統合について相談したいという場合は、問い合わせを送ってほしい。 技術顧問としてサポートやアドバイスを提供できると思う。

prtimes.jp