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

以前、BigQuery で無からリレーションを出現させる というエントリを書いたのですが、Standard SQL が出たので、Standard SQL 版を書きます

基本形

SQL

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

結果

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

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

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

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

SQL

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

結果

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

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

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

#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 要素目以降は型推論（これを推論っていうのか怪しいけど）してくれるようになっていたので、いちいち複雑な型定義を書きたくない場合には、こっちの記法の法が便利かも知れません

結果

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

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

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 を指定すれば、繰り返し構造を持つ列を含むリレーションも生成できます

結果

うれしみ

たとえば、BigQueryのクエリー料金の些細な話のような状況で

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

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

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

すごみ

BigQuery で無からリレーションを出現させるに「１クエリーにつき 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.

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

おまけ

• あ、すごい偶然なんですけど、今日、わしの誕生日です。毎年、みなさまのおかげで生かされております。毎度どうもありがとうございます