Edited at

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


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


おまけ