27
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-12-17

以前、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.

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

おまけ

27
16
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
27
16

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?