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

# この記事はなに？

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

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

# 想定されるjsonの中身

``` 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`を選ぶことでパース処理を避けます。

![スクリーンショット 2022-03-04 0.01.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311026/2789b9dc-566b-5fba-20c6-8f348d583a99.png)


## POINT2

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

![スクリーンショット 2022-03-04 0.14.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311026/391c95c4-d2a5-e225-411d-485633552296.png)


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

![スクリーンショット 2022-03-04 0.14.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311026/4571a0af-cb63-3df0-762c-ff3adc6b3a93.png)

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

![スクリーンショット 2022-03-04 0.23.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311026/8935f1a0-e19b-7609-8a14-8a7e12db419f.png)


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

![スクリーンショット 2022-03-04 0.27.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311026/63e7cbe9-02ca-3259-4399-6e218cd07695.png)

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

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

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

```sql
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関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract)を使って、jsonのvalueの値を出力しています。

## POINT2

データ型を[STRUCT型](https://dev.classmethod.jp/articles/bigquery-advent-calendar-2020-array-type-and-struct-type/#toc-2)にすることで、jsonのネスト構造を構造化データに変換しています。

## POINT3

本来jsonのkeyが受け付けないJSONPathの文字列が含まれていたとしても、[JSON_EXTRACT_SCALAR関数](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract_scalar)を使うことで回避することができます。

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