BigQuery で無からリレーションを出現させる（StandardSQL 編）

以前、[BigQuery で無からリレーションを出現させる](http://qiita.com/yancya/items/9af89b6f8d7975ef5892) というエントリを書いたのですが、Standard SQL が出たので、Standard SQL 版を書きます

## 基本形

### SQL

```sql
#standardSQL
SELECT * FROM UNNEST([(1, 'taro'), (2, 'jiro')])
```

### 結果

<img width="119" alt="Google_BigQuery.png" src="https://qiita-image-store.s3.amazonaws.com/0/9513/c505f723-af5f-2e29-06c9-de18ba9f423d.png">

UNNEST によって、Array がリレーションに展開されていることがわかります。しかし、このままだと、各列の名前が `f0_` とか `f1_` になってしまって、アクセスできません。アクセスできませんというのは、たとえば

```sql
#standardSQL
  WITH t AS (SELECT *
               FROM UNNEST([(1, 'taro'), (2, 'jiro')]))
SELECT f0_ FROM t
-- Error: Unrecognized name: f0_
```

という感じで、`f0_` という名前は表示上だけのもので、SQL 上で使おうとしても認識されていないのでエラーになります

## 名前付きリレーションの生成

### SQL

```sql
#standardSQL
  WITH t AS (SELECT *
               FROM UNNEST(ARRAY<STRUCT<id INT64, name STRING>>
                      [(1, 'taro'), (2, 'jiro')]))
SELECT name FROM t
```

### 結果

<img width="102" alt="Google_BigQuery.png" src="https://qiita-image-store.s3.amazonaws.com/0/9513/b7f54800-8a27-90f6-b4a1-e0199b9951cd.png">

「Struct の Array」の型定義 `ARRAY<STRUCT<id INT64, name STRING>>` を書いておくと、Struct の要素の名前が UNNEST で展開した際の列名になっていることが分かります

## ネスト構造を持つリレーションの生成

### SQL

```sql
#standardSQL
  WITH t AS (SELECT *
               FROM UNNEST(ARRAY<STRUCT<`id`   INT64
                                      , `name` STRUCT<`first` STRING
                                                    , `last`  STRING>>>
                      [(1, ('taro', 'yamada')), (2, ('jiro', 'tanaka'))]))
SELECT * FROM t
```

単に Struct の要素の型を Struct にするだけです

### [追記] 別の書き方の SQL

```sql
#standardSQL
  WITH t AS (
              SELECT *
                FROM UNNEST([
                       Struct(
                         1 as id,
                         Struct(
                           'taro' as first,
                           'yamada' as last
                         ) as name
                       ),
                       (2, ('jiro', 'tanaka'))
                     ])
            )
SELECT * FROM t
```

* いつの間にか（知らなかっただけで最初からそうだったのかも知れないけど）、`UNNEST` する `Array` の 1 要素目に `AS` で命名するだけで、2 要素目以降は型推論（これを推論っていうのか怪しいけど）してくれるようになっていたので、いちいち複雑な型定義を書きたくない場合には、こっちの記法の法が便利かも知れません

### 結果

<img width="227" alt="Google_BigQuery.png" src="https://qiita-image-store.s3.amazonaws.com/0/9513/9dd922fe-9b87-babf-405a-e2cda79c2dad.png">

ネストされたリレーションができていることが分かります

## 繰り返し構造を持つリレーションの生成

### SQL

```sql
#standardSQL
  WITH t AS (SELECT *
               FROM UNNEST(ARRAY<STRUCT<`id`   INT64
                                      , `tags` ARRAY<STRING>>>
                             [(1, ['java', 'scala']), (2, ['ruby', 'python'])]))
SELECT * FROM t
```

ここまで来れば分かると思いますが、Struct の要素の型に Array を指定すれば、繰り返し構造を持つ列を含むリレーションも生成できます

### 結果

<img width="129" alt="Google_BigQuery.png" src="https://qiita-image-store.s3.amazonaws.com/0/9513/c5d4379f-5157-032e-402a-fefe881fe914.png">

## うれしみ

たとえば、[BigQueryのクエリー料金の些細な話](http://qiita.com/kunit/items/48ed7ad7302762de8cb3)のような状況で

> テストをするために、ものすごく容量が少ないテーブル群に対してクエリーを発行したらそこそこ料金がかかってしまっていた

ということがありがちですが、今回の技を使ってクエリーの前にテストデータ用 CTE(WITH) を作ってやることによって、テーブルアクセスを全く行わずに SQL のロジックだけをテストすることができるようになります。

LegacySQL 時代には[Bigquery上で実行するバッチ処理のテストコードを書く (Ruby編)](http://qiita.com/joker1007/items/c3a3bb3db138b9f6734a)という、感じでテーブルアクセス無しの VIEW を使ったテストという技があって、StandardSQL においても今回の記事の技を使って VIEW を再現可能ですが、CTE(WITH) を使うことで、より軽量な感じでテストできるのではないでしょうか

## すごみ

[BigQuery で無からリレーションを出現させる](http://qiita.com/yancya/items/9af89b6f8d7975ef5892)に「１クエリーにつき SQL は 256KB くらいまで」という話を書きましたが、Standard SQL モードにおいては、なんと、どうやら、この制限が無いっぽいです。今のところ無限にでかいリレーションを使って動作させることができています（すんません。500MB くらいまで試しました）。そのうち制限されるかもしれませんが、UDF もクエリーの中に含められるようになっていますし、Legacy より巨大なクエリーを処理できるようになっていることは間違いないと思いますので、どんどん活用したいところです

### 追記

なんか、今さっき（2016/12/20 15:00）

> invalid: The query is too large. The maximum query length is 256K characters, including comments and white space characters.

とか言われてしまいました。夢の時間は終わりだ

## おまけ

- あ、すごい偶然なんですけど、今日、わしの誕生日です。毎年、みなさまのおかげで生かされております。[毎度どうもありがとうございます](http://yancya.jp/hoshi_wish)