特殊なケースのjsonをtroccoでBigQueryに転送してパースしてみた

この記事はなに？

ネストされていたり、jsonのkeyに/(スラッシュ)が入っているような特殊なケースのjsonを、ETLサービスのtroccoを利用してBigQueryに転送し、BigQuery側でjsonをパースして扱いやすい形に変換する過程を説明した記事になります。

なお、本記事は2022年3月4日に記載しております。
troccoのアップデートにより、この記事のやり方に沿わなくても実装できる可能性もあることをご了承ください。

想定されるjsonの中身

sample.json

{
  "id":"12345",
  "name":{
    "full":"tanaka|taro",
    "last":"tanaka",
    "first":"taro"
  },
  "log":{
    "event":"page_view",
    "url":"https://xxxx",
    "labels":{
      "a/b_test":"original",
      "conversion":"true"
    }
  }
}

このjsonには

• ネストの中で更にネストされている
• valueの文字列に|(パイプ)が含まれている
• keyの文字列に/(スラッシュ)が含まれている

という3つの特徴があります。
※あくまでサンプルなので、実際のログとは異なります。

前提のお話

jsonファイルはGoogleCloudStorageに保存してあります。
BigQueryにはデータレイク層のデータセット(data_lake)と、データウェアハウス層のデータセット(data_warehouse)が存在しています。
原則、data_lakeには未加工のデータを保存し、それを分析しやすく加工したデータをdata_warehouseに保存しています。

そのためtroccoからBigQueryにデータを転送する際は、jsonのパースを敢えて行わず、生のjson文字列を持つテーブルをdata_lakeに生成します。
その後、data_lakeのテーブルに対してjsonをパースするクエリを実行し、data_warehouseにテーブルを生成していきます。(本記事ではdata_warehouseへのテーブル生成は行わず、パースするところまでとしています)

troccoを利用して、data_lakeにテーブルを生成する

GoogleCloudStorage→BigQueryの転送ジョブを作成します。
細かい設定情報は各社で異なるため、ここではポイントのみ記載します。

POINT1

STEP1 転送元・転送先の設定にて、入力ファイル形式をCSV/TSVにします。
JSON Linesを選択してしまうと、jsonの中身を読み取ってパースされてしまうため、CSV/TSVを選ぶことでパース処理を避けます。

POINT2

STEP2 データプレビュー・詳細設定にて、入力オプションの設定を開き、区切り文字を絶対に存在しない文字列(例の場合アンダースコアを10個)にし、エスケープ文字はブランクにしておきます。

続いて、出力オプションの設定を開き、カラムを追加して出力されるカラムが1つになるようにします。

最後に、データ設定を開き、カラムを1つにします。

ここまでできたら変更をプレビューボタンをクリック→カラム定義を再読み込みボタンをクリック→もう一度変更をプレビューボタンをクリックします。
この際、スキーマプレビューを見るとズレて表示されてしまうことがあるのですが、あくまで表示上ズレているだけであり、実際は1つのカラムに格納されているのでご安心ください。

ここまでできたら、あとは転送ジョブを保存して実行しましょう。

BigQuery上で、jsonをパースして扱いやすい形に変換する

jsonをパースするSELECT文を下記します。

SELECT 
  JSON_EXTRACT(json_text, '$.id') AS id
  , STRUCT(
      JSON_EXTRACT(json_text, '$.name.full') AS full
      , JSON_EXTRACT(json_text, '$.name.last') AS last
      , JSON_EXTRACT(json_text, '$.name.first') AS first
  ) AS name
  , STRUCT(
      JSON_EXTRACT(json_text, '$.log.event') AS event
      , JSON_EXTRACT(json_text, '$.log.url') AS url
      , STRUCT(
          JSON_EXTRACT_SCALAR(json_text, "$.log.labels['a/b_test']") AS ab_test
          ,JSON_EXTRACT(json_text, '$.log.labels.conversion') AS conversion
      ) AS labels
  ) AS log
FROM 
  `project_name.data_lake.json_data`

POINT1

JSON_EXTRACT関数を使って、jsonのvalueの値を出力しています。

POINT2

データ型をSTRUCT型にすることで、jsonのネスト構造を構造化データに変換しています。

POINT3

本来jsonのkeyが受け付けないJSONPathの文字列が含まれていたとしても、JSON_EXTRACT_SCALAR関数を使うことで回避することができます。

おわりに

今回は、特殊なケースのjsonをtroccoを経由してBigQueryに転送し、パースして扱いやすい形に変換するところまでを説明しました。
需要があるかは微妙なところですが、この記事の内容が少しでも誰かの役に立てば幸いです。
ここまで読んでくださりありがとうございました！