Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

MIXI DEVELOPERSAdvent Calendar 2022

Day 13
@m77soin株式会社MIXI

BigQueryのJSON型をSTRUCT型に変換する

Last updated at Posted at 2022-12-12

これは　MIXI DEVELOPERS Advent Calendar 2022 13日目です。

概要

BigQueryのJSON文字列/JSON型を扱う際、構造がよくわからないのでSTRUCT型などに変換したビューを作っておいたほうが便利なことが多い。そこで、JSONを整理してSTRUCT型に変換する手順を整理して紹介する。また、変換は機械的に対応できるので、クエリを自動生成するプログラムの作り方、ソースコード(TypeScript)及び動作確認用のWebアプリを紹介する。

はじめに

JSON型

BigQueryのJSON型がついにGA（一般提供）になりました。【新機能】BigQueryでJSON型が使えます！（BigQuery subscriptionsも対応） - DevelopersIO にもあるように、いつのまにかGAになっています。

今までBigQueryでJSONを扱う際は、JSONデータを文字列として持ち、それに対してJSONにアクセスできる関数を使いました。JSON型では、JSONを半構造化データそのものとして保持します。

JSONの文字列に対してアクセスする例。colはただの文字列です。

SELECT
  JSON_QUERY(col, '$.country'), -- "Canada" 
  JSON_VALUE(col, '$.country'), -- Canada
  JSON_QUERY_ARRAY(col, '$.members'), -- ["Alice", "Bob"]
  JSON_VALUE_ARRAY(col, '$.members') -- [Alice, Bob]
FROM UNNEST([
'{"country": "Canada", "members": ["Alice", "Bob"]}'
]) col

JSON型を使う例。colはJSON型です。

SELECT
  col.country, -- "Canada" JSON型
  STRING(col.country), -- Canada
  col.members, -- ["Alice", "Bob"] JSON型
  JSON_VALUE_ARRAY(col, '$.members') -- [Alice, Bob]
FROM UNNEST([
  JSON'{"country": "Canada", "members": ["Alice", "Bob"]}'
]) col

参照: JSON関数 - BigQueryリファレンス

また、このような半構造化データを扱うもう一つの方法として、STRUCT型やARRAY型を扱う方法があります。事前にデータの構造を定義します。同じ列では同じ構造のデータしか扱えません。

SELECT
  STRUCT<name STRING>("Alice"),
  STRUCT<members ARRAY<STRING>>(["Alice", "Bob"])

JSON型の用途

実際にJSON型を使う際、毎回全く異なる構造のJSONを扱うことはまれで、多くの場合は数種類〜数百種類程度の構造でしょう。たとえば、よくあるログでは以下のようにtypeの値で区別されスキーマが確定します(OpenAPIでいうdiscriminator)。

{"type": "user.create", "body": { "name": "太郎", "year": 2004, "parents": ["一郎", "花子"]}}
{"type": "user.create", "body": { "name": "二郎", "year": 2008, "parents": ["一郎", "花子"]}}
{"type": "item.sell", "body": { "item_id": 12345, "count": 30, "price": 12000, "created_ts": "2022-12-13T12:00:00+09:00"}}

STRUCT型では異なるスキーマを同じ列で扱うことができず、JSONを文字列で持つか、EAVのようなアンチパターンに手を染めるかですが、その点でJSON型は大変便利です。

問題提起

さて、そのようなテーブルに対して分析クエリを投げる際、JSON型をそのまま使うのは不便です。このテーブルに対して、"item_sell"ログの"item_id"ごとの"price"の合計を集計してみましょう。

SELECT
  INT64(col.body.id), SUM(INT64(col.body.price))
FROM 
UNNEST([
  JSON'{"type": "user.create", "body": { "name": "太郎", "year": 2004, "parents": ["一郎", "花子"]}}',
  JSON'{"type": "user.create", "body": { "name": "二郎", "year": 2008, "parents": ["一郎", "花子"]}}',
  JSON'{"type": "item.sell", "body": { "item_id": 12345, "count": 30, "price": 12000, "created_ts": "2022-12-13T12:00:00+09:00"}}'
]) col
WHERE STRING(col.type) = 'item.sell'
GROUP BY 1

INT64(col.body.id), SUM(INT64(col.body.price))なんて手入力しなければいけません。その上、このJSONの構造は別途リファレンスを参照するか、数件データを出力して構造を類推する必要があり大変不便です。

データを入れる際はJSON型は便利ですが、データを使う際は少しわかりにくいです。次のようなSTRUCT型を使ったり、列が展開された構造がわかりやすいViewがあると嬉しいです。

STRUCT型にした例

SELECT
    STRUCT(
      STRING(t.col.type) AS type,
      STRUCT(
        INT64(t.col.body.id) AS id,
        INT64(t.col.body.count) AS count,
        INT64(t.col.body.price) AS price,
        TIMESTAMP(STRING(t.col.body.created_ts)) AS created_ts
      ) AS body
    ) col
FROM t
WHERE STRING(t.col.type) = 'item.sell'

少し展開した例

SELECT
  STRING(t.col.type) type,
  STRUCT(
    INT64(t.col.body.id) AS id,
    INT64(t.col.body.count) AS count,
    INT64(t.col.body.price) AS price,
    TIMESTAMP(STRING(t.col.body.created_ts)) AS created_ts
  ) body
FROM t
WHERE STRING(t.col.type) = 'item.sell'

更に展開した例

SELECT
  INT64(t.col.body.id) id,
  INT64(t.col.body.count) count,
  INT64(t.col.body.price) price,
  TIMESTAMP(STRING(t.col.body.created_ts)) created_ts
FROM t
WHERE STRING(t.col.type) = 'item.sell'

ぱっと変換できますか？このくらい簡単なものならまだしも、もっと複雑なJSONではどうですか？

こういったとき、JSON型をSTRUCT型に変換する方法が必要になります。

変換の考え方

一階層のJSON

JSONと変換例を示します。

{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}

変換例1

SELECT
    STRUCT(
     STRING(col.name) AS name,
     INT64(col.age) AS age,
     TIMESTAMP(STRING(col.updated_ts)) AS updated_ts,
     FLOAT64(col.score) AS score
   ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}'
    ]) col

変換例2

SELECT
    STRUCT<name STRING, age INT64, TIMESTAMP updated_ts, FLOAT64 score>(
     STRING(col.name),
     INT64(col.age),
     TIMESTAMP(STRING(col.updated_ts)),
     FLOAT64(col.score)
   ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "age": 20, "updated_ts": "2006-01-02T15:04:05-07:00", "score": 3.14}'
    ]) col

STRUCT型

STRUCT型はこのように

STRUCT(
  値1 AS フィールド名1,
  値2 AS フィールド名2
)

もしくは

STRUCT<フィールド名1 型1, フィールド名2 型2>(
  値1,
  値2
)

のように表現します。
構造体型 - BigQueryリファレンス

JSON型をプリミティブな型に変換する

JSON型の値はcol.foo.barなど、通常のJSONにアクセスする方法で簡単に参照できます。しかし、その結果はJSON型になります。BigQueryで扱いやすくするにはJSON型をBigQueryのプリミティブな型に変換します。

STRING,INT64,FLOAT64,BOOLの各関数を用いて、JSON型をプリミティブな型に変換します。次のクエリの各列は全てtrueになります。

SELECT
  STRING(JSON'"Alice"') = "Alice", 
  INT64(JSON'64') = 64, 
  FLOAT64(JSON'3.14') = 3.14, 
  BOOL(JSON'true') = true

データの品質が悪い場合は、SAFE.STRINGなどを用いれば、不正な値をNULLにすることができます。

STRING関数 - BigQuery リファレンス

それ以外のDATEやTIMESTAMPといった型を使う際は、一旦STRING型に変換してからめあての型に変換することになります。

SELECT
  TIMESTAMP(STRING(JSON'"2006-01-02T15:04:05-07"'))

二階層以上のJSON

JSONと変換例を示します。

{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}

SELECT
  STRUCT(
    STRING(col.name) AS name,
    STRUCT(
      STRING(col.address.country) AS country,
      STRING(col.address.prefecture) AS prefecture,
      STRING(col.address.city) AS city
    ) AS address
  ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}'
    ]) col

それほど難しくありません。STRUCTの中にSTRUCTを入れればよいだけです。

ただ、型を前置しようとすると省略できず大変なことになります。

SELECT
  STRUCT<name STRING, address STRUCT<country STRING, prefecture STRING, city STRING>>(
    STRING(col.name),
    STRUCT<country STRING, prefecture STRING, city STRING>(
      STRING(col.address.country),
      STRING(col.address.prefecture),
      STRING(col.address.city)
    )
  ) s
FROM
UNNEST([
    JSON'{"name": "太郎", "address": {"country": "日本", "prefecture": "東京都", "city": "渋谷区"}}'
    ]) col

プリミティブ型の配列を含むJSON

JSONと変換例を示します。

{"request_id":9876, "users": [123,456,7890]}

SELECT
  STRUCT(
    INT64(col.request_id) AS request_id,
    ARRAY(
      SELECT
        INT64(users)
      FROM
        UNNEST(
          JSON_QUERY_ARRAY(col.users)
        ) users
        WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
  JSON'{"request_id":9876, "users": [123,456,7890]}'
]) col

SELECT
  STRUCT<request_id INT64, users ARRAY<INT64>>(
    INT64(col.request_id),
    ARRAY(
      SELECT
        INT64(users)
      FROM
        UNNEST(
          JSON_QUERY_ARRAY(col.users)
        ) users
        WITH OFFSET users_index
      ORDER BY users_index
    )
  ) s
FROM
UNNEST([
  JSON'{"request_id":9876, "users": [123,456,7890]}'
]) col

まず、JSON_QUERY_ARRAY(col.users)だけでは、ARRAY<JSON>が返ってきます。
INT64型に変換したいため、ARRAYとUNNESTで変換します。その時、順序を保証するために WITH OFFSET と ORDER BY が必要になります。

以下はこの節で使った機能のBigQueryのリファレンスです。

オブジェクトの配列を含むJSON

JSONと変換例を示します。

{ "users": [
    {"id": 123, "name": "太郎"},
    {"id": 124, "name": "二郎"}
  ]
}

SELECT
  STRUCT(
    ARRAY(
      SELECT AS STRUCT
        INT64(users.id) AS id,
        STRING(users.name) AS name
      FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users
      WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
JSON'{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}']) col

SELECT
  STRUCT<users ARRAY<STRUCT<id INT64, name STRING>>>(
    ARRAY(
      SELECT
        STRUCT<id INT64, name STRING>(
          INT64(users.id),
          STRING(users.name)
        )
      FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users
      WITH OFFSET users_index
      ORDER BY users_index
    ) AS users
  ) s
FROM
UNNEST([
JSON'{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}']) col

プリミティブ型の配列を含むJSONの作り方を理解していれば、同様に列を増やすだけです。SELECT AS STRUCTを使うと多少すっきりしますが、使わなくても作れます。

JSONデータの構造を把握する

さて、どのように変換するかはわかりましたが、JSONのデータはあるものの構造がわからないことがあります。

そんなときは、JSONを読み取ってBigQueryのスキーマに変換する便利なOSSを使います。
https://github.com/bxparks/bigquery-schema-generator
これを用いると、次のように簡単に変換できます。

$ cat sample.json
{"users":[{"id": 123, "name": "太郎"},{"id": 124, "name": "二郎"}]}

$ generate-schema < sample.json 

[
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "id",
        "type": "INTEGER"
      },
      {
        "mode": "NULLABLE",
        "name": "name",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "users",
    "type": "RECORD"
  }
]

機械的にやる

目標

ここまでで、クエリを組み立てる手順を整理し、JSONのフォーマットをわかりやすく記述する方法を手に入れました。次は機械的にクエリを生成したくなります。

次のような関数を実装することが目標です。

次のようなJSONが入ったJSON列colをもつテーブルtを想定します。

{
  "date": "2006-01-02",
  "timestamps": [
    "2006-01-02T15:04:05+0900",
    "2006-01-02T20:00:00+0900"
  ]
  "location": {
    "country": "Japan",
    "ZipCode": "123-4567"
  },
  "users": [
    {"id": 123, "name": "太郎"},
    {"id": 124, "name": "二郎"}
  ]
}

このテーブルに対して次のようなクエリを機械的に作成します。

SELECT
  SAFE.STRING(col.created_at) AS created_at,
  DATE(SAFE.STRING(col.date)) AS date,
  STRUCT(
    SAFE.STRING(col.location.country) AS country,
    SAFE.STRING(col.location.ZipCode) AS ZipCode
  ) AS location,
  ARRAY(
    SELECT
      SAFE_CAST(SAFE.STRING(timestamps) AS TIMESTAMP)
    FROM UNNEST(JSON_QUERY_ARRAY(col.timestamps)) timestamps WITH OFFSET timestamps_index
    ORDER BY timestamps_index
  ) AS timestamps,
  ARRAY(
    SELECT
      STRUCT(
        SAFE.INT64(users.id) AS id,
        SAFE.STRING(users.name) AS name
      )
    FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users WITH OFFSET users_index
    ORDER BY users_index
  ) AS users
FROM t

なお、このJSONに対するスキーマは、bigquery-schema-generatorなどを用いて用意しておきます。

[
  {
    "mode": "NULLABLE",
    "name": "date",
    "type": "DATE"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "country",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "ZipCode",
        "type": "STRING"
      }
    ],
    "mode": "NULLABLE",
    "name": "location",
    "type": "RECORD"
  },
  {
    "mode": "REPEATED",
    "name": "timestamps",
    "type": "STRING"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "id",
        "type": "INTEGER"
      },
      {
        "mode": "NULLABLE",
        "name": "name",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "users",
    "type": "RECORD"
  }
]

実装

ここでは例としてTypeScriptで実装していきます。手順を順に書いていきますので、他言語での実装も簡単ですね！

BigQueryのJSONで表されるSchemaは、次のように表現できます。ポイントは、

  • typeフィールドがRECORDの場合はSTRUCT型です。fieldsフィールドが追加され、再帰的な構造になります。
  • modeフィールドがREPEATEDの場合は配列となります。
type BqJsonSchemaElemBase = {
  name: string,
  mode: "REPEATED" | "NULLABLE" | "REQUIRED",
  description?: string
}
type BqJsonTypes =  "INTEGER" | "STRING" | "DATE" | "TIMESTAMP" // 他にも型があれば足す
type BqJsonSchemaElemDefault = BqJsonSchemaElemBase & {
  type: BqJsonTypes
}
type BqJsonSchemaElemRecord = BqJsonSchemaElemBase & {
  type: "RECORD",
  fields: BqJsonSchemaElem[]
}
type BqJsonSchemaElem = BqJsonSchemaElemDefault | BqJsonSchemaElemRecord
type BqJsonSchema = BqJsonSchemaElem[]

まず、JSON型をそれぞれの型に変換する関数を用意しておきます。

const cast :{[key in BqJsonTypes]:(column :string)=>string} = {
  "TIMESTAMP": (column :string): string => `SAFE_CAST(SAFE.STRING(${column}) AS TIMESTAMP)`,
  "STRING": (column :string): string => `SAFE.STRING(${column})`,
  "DATE": (column :string): string => `DATE(SAFE.STRING(${column}))`,
  "INTEGER": (column :string): string => `SAFE.INT64(${column})`,
  // 他にも型があれば足す
}

一階層

一階層に対応した実装は次のようになります。

const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
  for (let elem of schema) {
    let column = cast[elem.type](`${prefix}${elem.name}`)
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  return columns.join(',\n')
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"}
    ]
    ,'col.'
  )
);

出力は以下のようになります。SELECTとFROM tは最後につけることにして、ここからSELECT句の中身を作っていきます。

DATE(SAFE.STRING(col.date)) AS date

二階層以上

二階層以上に対応した実装は次のようになります。再帰的な構造になっているので再帰で実装します。

const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
  for (let elem of schema) {
    let column = ""
    if (elem.type === "RECORD") {
      column = `STRUCT(
${generateSelect(elem.fields, `${prefix}${elem.name}`)}
)`
    } else {
      column = cast[elem.type](`${prefix}${elem.name}`)
    }
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  return columns.join(',\n')
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "country", "type": "STRING"},
          { "mode": "NULLABLE", "name": "ZipCode", "type": "STRING"},
        ],
        "mode": "NULLABLE",  "name": "location", "type": "RECORD"
      }
    ],
    'col'
  )
);

出力は以下の通りです。

DATE(SAFE.STRING(col.date)) AS date,
STRUCT(
SAFE.STRING(location.country) AS country,
SAFE.STRING(location.ZipCode) AS ZipCode
) AS location

配列を含む構造

配列を実装します。

  • 生成したcolumnをARRAY()で囲みます。
  • ARRAY()の中では、columnはARRAY関数の内側のFROM句を参照するようにします。
  • インデントも整えておきます。本筋から外れるので解説はしません。別途Formatterを使ってもいいと思います。
    • 最後なのでSELECT ... FROM tも付けてしまいます。
const generateSelect = (schema: BqJsonSchema, prefix: string) => {
  const columns: string[] = []
    for (let elem of schema) {
    let column = ""
    let column_prefix = elem.mode === "REPEATED" ? "" : prefix
    if (elem.type === "RECORD") {
      column = `STRUCT(
${generateSelect(elem.fields, `${column_prefix}${elem.name}.`)}
)`
    } else {
      column = cast[elem.type](`${column_prefix}${elem.name}`)
    }
    if (elem.mode === "REPEATED") {
      column = `ARRAY(
SELECT
${column}
FROM UNNEST(JSON_QUERY_ARRAY(${prefix}${elem.name})) ${elem.name}
WITH OFFSET ${elem.name}_index
ORDER BY ${elem.name}_index
)`
    }
    column += ` AS ${elem.name}`
    columns.push(column)
  }
  const query = columns.join(",\n")
  // インデントを整える部分
  const lines = query.split("\n")
  const res_lines: string[] = []
  let indent_cnt = 1 // SELECT句分
  for (let i = 0; i< lines.length; ++i){
    let l = lines[i]
    const heading_close_cnt =  [...l.matchAll(/^([\)>}]|FROM|\s)+/g)].map(v=>v[0]).map(v=>[...v.matchAll(/(FROM|[\)}>])/g)]).flat().length
    indent_cnt -= heading_close_cnt
    l = "  ".repeat(Math.max(0,indent_cnt)) + l
    indent_cnt -= [...l.matchAll(/([>}\)]|FROM)/g)].length - heading_close_cnt
    indent_cnt += [...l.matchAll(/([<{\(]|SELECT)/g)].length
    res_lines.push(l)
  }
  return "SELECT\n" + res_lines.join('\n') + "\nFROM t"
}

console.log(
  generateSelect(
    [
      { "mode": "NULLABLE",  "name": "date", "type": "DATE"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "country", "type": "STRING"},
          { "mode": "NULLABLE", "name": "ZipCode", "type": "STRING"},
        ],
        "mode": "NULLABLE",  "name": "location", "type": "RECORD"
      },
      { "mode": "REPEATED",  "name": "timestamps", "type": "TIMESTAMP"},
      {
        "fields": [
          { "mode": "NULLABLE", "name": "id", "type": "INTEGER"},
          { "mode": "NULLABLE", "name": "name", "type": "STRING"},
        ],
        "mode": "REPEATED",  "name": "users", "type": "RECORD"
      },
    ],
    'col.'
  )
);

出力は以下のようになります。

SELECT
  DATE(SAFE.STRING(col.date)) AS date,
  STRUCT(
    SELECT
        SAFE.STRING(col.location.country) AS country,
        SAFE.STRING(col.location.ZipCode) AS ZipCode
    FROM t
  ) AS location,
  ARRAY(
    SELECT
      SAFE_CAST(SAFE.STRING(timestamps) AS TIMESTAMP)
    FROM UNNEST(JSON_QUERY_ARRAY(col.timestamps)) timestamps WITH OFFSET timestamps_index
    ORDER BY timestamps_index
  ) AS timestamps,
  ARRAY(
    SELECT
      STRUCT(
        SELECT
            SAFE.INT64(users.id) AS id,
            SAFE.STRING(users.name) AS name
        FROM t
      )
    FROM UNNEST(JSON_QUERY_ARRAY(col.users)) users WITH OFFSET users_index
    ORDER BY users_index
  ) AS users
FROM t

動作確認用のWebアプリ

BigQueryのJSONで表現したスキーマからSQLに変換するWebアプリです。元のJSONからスキーマへの変換はあらかじめする必要があります。

私の利用目的では、あらかじめスキーマがわかっていたため、元のJSONからスキーマへの変換は開発が不要で実装していません……。また、DATE, TIMESTAMP, INTEGER, STRING にしか対応していません。

App: https://m77so.github.io/bq-json-struct-conv/
GitHub: https://github.com/m77so/bq-json-struct-conv

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?