BigQueryでスキーマレス対応（動的テーブル作成）をしてみるテスト

#概要
対象データはログではなくマスタ系のデータで日次で更新すれば良いくらいの頻度＋数十万件くらいデータ量を想定

BigQueryでスキーマレスなデータを扱いたい場合、JSONでデータを突っ込んだ後、JSON 関数で参照すればよいが、非エンジニアがBIツールから使用したい場合などはつらい。
参考記事：BigQuery をスキーマレスっぽく運用する方法

また、EAV(エンティティ・アトリビュート・バリュー)はSQLアンチパターンといわれているが、分析で使用するマスタなどで項目が可変（というか追加がある）ものをRDBで実現しようとするとEAVで持たざるを得ない。
参考記事：5章 EAV (エンティティ・アトリビュート・バリュー)

上記の解決案として、一旦EAV(縦持ちテーブル)を作成し、項目横持ちテーブルを動的に作成する事を考える。

例）
もともとの商品マスタ

コード	名称	色	長さ(mm)	金額
1	くぎ	銀	120	100
に

コード	名称	色	重さ(Kg)	金額
2	ブロック	白	5	900

というデータが加わった場合、

コード	名称	色	長さ(mm)	重さ(Kg)	金額
1	くぎ	銀	120		100
2	ブロック	白		5	900

となるテーブルを半自動で作成する。

#注意点
・Legacy SQL
・もともとExcelなんかで管理されているマスタのようなものを想定
・リアルタイムなログ分析には厳しい
・型は入っているデータより自動で判断しているが、ざっくり判断
・項目名の管理（同音異義語がないか）は厳密に行っておく必要がある

#手順
1.JSONデータ準備
2.縦持ちテーブルにロード
3.横持ちテーブルに変換

#手順詳細
※BigQueryのデータセット"temp"を使用するものと想定

1.1 JSONデータ準備

data.json

{"syohin_cd":"1","syohin_nm":"くぎ","attr":[{"name":"color","value":"銀"},{"name":"nagasa","value":"120"},{"name":"kingaku","value":"100"}]}
{"syohin_cd":"2","syohin_nm":"ブロック","attr":[{"name":"color","value":"白"},{"name":"omosa","value":"5"},{"name":"kingaku","value":"900"}]}

2.1 縦持ちテーブル定義

syohin.json

[
   {
      "name": "syohin_cd" 
     ,"type": "string"
     ,"mode": "nullable"
     ,"description": "コード"
   }
  ,{
      "name": "syohin_nm"
     ,"type": "string"
     ,"mode": "nullable"
     ,"description": "名称"
   }
  ,{
      "name": "attr"
     ,"type": "record"
     ,"mode": "repeated"
     ,"description": "属性データ"
     ,"fields":[
       {
           "name": "name"
          ,"type": "string"
          ,"description": "属性名称"
       }
      ,{
           "name": "value"
          ,"type": "string"
          ,"description": "属性値"
       }
     ]
   }
]

2.2 縦持ちテーブルデータロード

bq load --source_format=NEWLINE_DELIMITED_JSON temp.syohin_eav data.json syohin.json

結果(レイアウト)

結果(データ)

3.1 横持ちテーブル作成SQLを作成するSQL実行

下記SQLを実行するとSELECT SQLが返される。

pivot_eav.sql

SELECT
  f1_ as createsql
FROM
 (SELECT 1 ,'SELECT')
,(SELECT 2,'   syohin_cd')
,(SELECT 3,'  ,syohin_nm')
,(
  SELECT
     4
    ,CONCAT(
          '  ,CAST(MAX(CASE WHEN attr.name='
         ,"'",attr.name
         ,"'"
         ,' THEN attr.value '
         ,' END) AS '
         ,CASE
            WHEN max_attrtype = 1 THEN 'INTEGER'
            WHEN max_attrtype = 2 THEN 'FLOAT'
            WHEN min_attrtype = 3 AND max_attrtype = 3 THEN 'DATE'
            ELSE 'STRING'
          END
         ,') AS '
         ,attr.name
       )
  FROM
   (
    SELECT
       attr.name
      ,MIN(
         CASE
           WHEN REGEXP_MATCH(attr.value,'^(0|-{0,1}[1-9]{1}[0-9]*)$') THEN
              1 -- INTEGER
           WHEN REGEXP_MATCH(attr.value,'^-{0,1}(0|[1-9]+[0-9]*)\\.[0-9]+$') THEN
              2 -- FLOAT
           WHEN REGEXP_MATCH(attr.value,'[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}') THEN
              3 -- DATE
           ELSE
              4 -- STRING
         END
       ) as min_attrtype
      ,MAX(
         CASE
           WHEN REGEXP_MATCH(attr.value,'^(0|-{0,1}[1-9]{1}[0-9]*)$') THEN
              1 -- INTEGER
           WHEN REGEXP_MATCH(attr.value,'^-{0,1}(0|[1-9]+[0-9]*)\\.[0-9]+$') THEN
              2 -- FLOAT
           WHEN REGEXP_MATCH(attr.value,'[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}') THEN
              3 -- DATE
           ELSE
              4 -- STRING
         END
       ) as max_attrtype
    FROM
      temp.syohin_eav
    WHERE
      attr.value != ''
    GROUP BY
      1
   )
 )
,(SELECT  5,'FROM')
,(SELECT  6,'  temp.syohin_eav')
,(SELECT  7,'WHERE')
,(SELECT  8,'  attr.value != ' + "''")
,(SELECT  9,'GROUP BY')
,(SELECT 10,'  1,2')
ORDER BY
  f0_
;

SQL実行結果

3.2 上記(3.1)で得られたSQLをDestination Table指定して実行

自動作成されたテーブルレイアウト

動的にテーブルを作成する事に成功！

#まとめ

• プログラムを書けばテーブルの自動生成は全自動化が可能
• 一旦横持ちにしてしまえば、コストもおさえられる
• 項目の並び順を揃えたい場合は工夫が必要
• 項目名の命名に気をつける（例：サイズでも大中小とmmは別の名前をつける等）
• 複数の商品マスタを統合するなど用途によっては使えそう
  • NOT NULL制約や厳密な型定義が必要なものは不可