BigqueryでUNNESTを使いこなせ!クエリ効率100%!!最強!!

Mizuki Kobayashi
Dec 6, 2019 · 9 min read


どうも!

BIチームの小林です!

今回は、

BigqueryでUNNESTをうまく使えば、
見やすくてしかも効率が良いクエリを書けるんです!

ということをやっていきたいと思います!

はい。

私の好きなものは Fortnite、RainbowSixSiege、ゲーム配信 です。
当記事は、ゲーム配信だと思って読んでください。

ちなみになんですが、前回2018年のアドベントカレンダーでは、
BigqueryでStandardSQL書くときに使えるTipsをいくつか紹介したので、

「Bigqueryは記法に癖があって難しいよ〜」
「すたんだーどすぃーくえるってなんですか?」

という人は、是非見てください!! ↓↓


では、UNNEST を使いこなす方法、やっていきましょう。

そもそもUNNESTとは?

簡単に言ってしまえば、
UNNEST を使うと、ARRAYなどの配列や、REPEATEDなカラムを、
開くことができちゃうんです!

例えば、 REPEATEDなカラムをもつテーブル

REPEATEDなカラムをもつテーブル

配列を含むテーブル
(BQ上では実際REPEATEDと配列型(ARRAY)は同じ扱われ方をするので、表示上はREPEATEDのものと同様になる)

配列を含むテーブル

これらに対して、下のクエリのようにUNNESTを使えば

一般的なUNNESTの使い方
一般的なUNNESTの使い方をした結果

上記のように、配列やREPEATEDの中身がテーブルに展開されます。
このとき、UNNESTで指定されたカラムが空の配列やNULLだった場合は、該当レコードは消えて無くなります。

他にも IN句 で使う方法もあって、

のように使うこともできます。これは結構便利なので是非覚えて帰ってください。

以上がUNNESTでググったときに出てくる超初歩的な用法です。

UNNESTの基礎についてもっと詳しく知りたい方は、
Bigqueryのリファレンス(標準 SQL での配列の操作)や、
グーグル デベロッパー アドボケートの人が書いた記事(BigQuery 活用術: UNNEST 関数)を読むと良いと思います。


table, UNNEST(column) って気持ち悪くない?

気持ち悪いですね。

1キモポイント !!
まず、カンマの前後で粒度が違うものが並んでいる。
2キモポイント !!
そして、そもそもこの場所でのカンマはCROSS JOINのはずなのに、UNNESTした結果をカンマで並べたとき、 CROSS JOINとは少し違う動作をしていること。
(CROSS JOINっぽいけど違うこの結合は、相関クロス結合と呼ばれるものです。)

そもそも、塊としては、1レコードでひとかたまりになっているので、

SELECT~FROMの間に記述する方が直感的に分かりやすいんじゃないの?

と思ったので、新しく綺麗な書き方を提唱します。

です!

日本語では全く伝わらないと思うので、
以下から、よくある二つのテーブル構造を想定して説明します。


① 1つのレコードに対して構造体(game)を複数持ってるパターン
(sample_tableを作成するクエリはgistに記載しています。)

構造体(game)を複数持ってるパターン

例 :

  • 2018年の仕様変更前にFirebase → Bigquery連携で生成されるテーブルで、user_dimに複数のevent_dimがついている
  • publicデータのgithub_repos.commitsテーブルで、1つのcommitレコードに複数のdiff構造体がついている

このテーブルに対し、
type別で、最小のstart_dateを出したいとします。
そのとき、書くべきクエリはこうです。

game構造体の中のstart_dateが最小のものをとってくる

同一のtypeで複数レコードある場合は、
サブクエリの上でもう一度MIN()で囲んであげても良いでしょう。

ここで、type別で、最小のstart_dateをもつゲームのタイトルを取りたい時は以下のように書きます

type別で最小のstart_dateをもつタイトル

SELECT AS STRUCT して、* で全てを取り出します。

これは分かりやすいですね。

覚えて帰ってください。

② 複数ある構造体(game.param)の中でkey, valueを持ってるパターン
(sample_tableを作成するクエリはgistに記載しています。)

複数ある構造体(game.param)の中でkey, valueを持ってるパターン

例 :

  • 2018年の仕様変更後にFirebase → Bigquery連携で生成されるテーブルで、一つのeventに複数のevent_paramsがついていてkey, valueのカラムがある
  • publicデータのgithub_repos.commitsテーブルで、1つのcommitレコードに複数のtrailerがついていて、中にkey, valueのカラムがある

こちらでも同様にtype別で、最小のstart_dateを出したいとします。

key,value の中身の値を取り出してから集計

①より多少面倒臭い感じになりますが、こう書くことができます。

最小のstart_dateをもつゲームのタイトルを取りたい時は、今回のテーマとあんまり関係ないので割愛させていただきます。

最上段でWINDOW関数のFIRST_VALUEを使えば解決します。


サブクエリと言うワードにはトラウマがある人も多いので

でもサブクエリとか書いちゃうとパフォーマンス落ちるんじゃないの〜?

とか言ってくる人もいると思いますが、

違うんですよ!

そんなことありません!

検証

読者のみんなも検証できるように、

publicデータのgithub_repos.commitsテーブルを用います。

このテーブルはcommitsレコード1つに対して、複数のtrailer構造体がついていて、その中では、keyカラムとvalueカラムで値を持っています。

Firebase → Bigqueryの連携をしている人ならお馴染みのデータの持ち方ですね。

[検証1] keyが特定の値の時のvalueを取り出して集計してみましょう。

以下のようなクエリになります。

trailerのkeyがTravis-Build-IDの時のValueのユニーク数をカウントする

上記クエリは結果が全く一緒になるのですが、

実行の詳細は変わってきます。それがこちら

用語は以下のように説明されています。

消費したスロット時間 :
スロットとは、SQL クエリの実行に必要な演算能力の単位です。

シャッフルされたバイト数 :
BigQuery は、大規模なコンピューティングに対応するためにデータをシャッフルします。

この結果自体は実行するたびに変わりますが、傾向は変わりません。
経過時間とスロット時間 は 私の提唱する新しい書き方に軍配が上がったようですね!

でもこんなことを言うとまた、

「よく見ると下のクエリ、SELECT AS STRUCTじゃないじゃん!」

とか言われそうなので、
次はSELECT AS STRUCTがある問題で検証しました。

[検証2] keyが特定の値の時のvalueを取り出して並べてみましょう。

クエリは以下になります。

trailerのkeyがTravis-Build-IDの時のValueを並べる

keyが特定の値を持たないtrailerをもつ行を除く必要がある分、
新しい書き方の方が不利な戦いになりそうです。

では、
実行の詳細はこちら

はい。

結果は実行するたびに変わるので、負けたり勝ったりしますが、
少なくとも、めちゃくちゃパフォーマンスが落ちる、ということはなさそうです。


Eureka Engineering

Learn about Eureka’s engineering efforts, product developments and more.

Mizuki Kobayashi

Written by

eureka,Inc. BI team / 分析 / 機械学習

Eureka Engineering

Learn about Eureka’s engineering efforts, product developments and more.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade