Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

基本形

SQL

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

結果

Google_BigQuery.png

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

結果

Google_BigQuery.png

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

結果

Google_BigQuery.png

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

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

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

結果

Google_BigQuery.png

うれしみ

たとえば、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.

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

おまけ

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした