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

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

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

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

上記の解決案として、一旦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
```
結果(レイアウト)
![schema_eav.png](https://qiita-image-store.s3.amazonaws.com/0/106673/9fdc433e-8a29-98fd-6dcf-69e23557fb59.png)

結果(データ)
![data_eav.png](https://qiita-image-store.s3.amazonaws.com/0/106673/300dc39f-07fb-a771-2632-0f985d5d1d0d.png)

#### 3.1 横持ちテーブル作成SQLを作成するSQL実行
下記SQLを実行するとSELECT SQLが返される。

```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実行結果
![selectcreate_sql.png](https://qiita-image-store.s3.amazonaws.com/0/106673/dd6bee57-11eb-35a4-e619-eebcf8d875d7.png)



#### 3.2 上記(3.1)で得られたSQLをDestination Table指定して実行
![result.png](https://qiita-image-store.s3.amazonaws.com/0/106673/67efee4b-6f35-2c17-27e5-13e507283e5b.png)

自動作成されたテーブルレイアウト
![tableresult.png](https://qiita-image-store.s3.amazonaws.com/0/106673/97268820-cd7a-78d7-58d6-e7068dc9e8df.png)

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

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