BigqueryでUNNESTを使いこなせ！クエリ効率１００%！！最強！！
どうも！
BIチームの小林です！
今回は、
BigqueryでUNNESTをうまく使えば、
見やすくてしかも効率が良いクエリを書けるんです！
ということをやっていきたいと思います！
はい。
私の好きなものは Fortnite、RainbowSixSiege、ゲーム配信 です。
当記事は、ゲーム配信だと思って読んでください。
ちなみになんですが、前回2018年のアドベントカレンダーでは、
BigqueryでStandardSQL書くときに使えるTipsをいくつか紹介したので、
「Bigqueryは記法に癖があって難しいよ〜」
「すたんだーどすぃーくえるってなんですか？」
という人は、是非見てください！！ ↓↓
では、UNNEST を使いこなす方法、やっていきましょう。
そもそもUNNESTとは？
簡単に言ってしまえば、
UNNEST を使うと、ARRAYなどの配列や、REPEATEDなカラムを、
開くことができちゃうんです！
例えば、 REPEATEDなカラムをもつテーブル
配列を含むテーブル
(BQ上では実際REPEATEDと配列型(ARRAY)は同じ扱われ方をするので、表示上はREPEATEDのものと同様になる)
これらに対して、下のクエリのようにUNNESTを使えば
上記のように、配列やREPEATEDの中身がテーブルに展開されます。
このとき、UNNESTで指定されたカラムが空の配列やNULLだった場合は、該当レコードは消えて無くなります。
他にも IN句 で使う方法もあって、
1 IN UNNEST([1,2,3,4])
のように使うこともできます。これは結構便利なので是非覚えて帰ってください。
以上がUNNESTでググったときに出てくる超初歩的な用法です。
UNNESTの基礎についてもっと詳しく知りたい方は、
Bigqueryのリファレンス(標準 SQL での配列の操作)や、
グーグル デベロッパー アドボケートの人が書いた記事(BigQuery 活用術: UNNEST 関数)を読むと良いと思います。
table, UNNEST(column) って気持ち悪くない？
気持ち悪いですね。
１ｷﾓﾎﾟｲﾝﾄ !!
まず、カンマの前後で粒度が違うものが並んでいる。
２ｷﾓﾎﾟｲﾝﾄ !!
そして、そもそもこの場所でのカンマはCROSS JOINのはずなのに、UNNESTした結果をカンマで並べたとき、 CROSS JOINとは少し違う動作をしていること。
(CROSS JOINっぽいけど違うこの結合は、相関クロス結合と呼ばれるものです。)
そもそも、塊としては、１レコードでひとかたまりになっているので、
SELECT~FROMの間に記述する方が直感的に分かりやすいんじゃないの？
と思ったので、新しく綺麗な書き方を提唱します。
SELECT以下にサブクエリで構造体として値を取り出す形で書いて その後 * で全カラムを取り出す
です！
日本語では全く伝わらないと思うので、
以下から、よくある二つのテーブル構造を想定して説明します。
① 1つのレコードに対して構造体(game)を複数持ってるパターン
(sample_tableを作成するクエリはgistに記載しています。)
例 :
- 2018年の仕様変更前にFirebase → Bigquery連携で生成されるテーブルで、user_dimに複数のevent_dimがついている
- publicデータのgithub_repos.commitsテーブルで、1つのcommitレコードに複数のdiff構造体がついている
このテーブルに対し、
type別で、最小のstart_dateを出したいとします。
そのとき、書くべきクエリはこうです。
同一のtypeで複数レコードある場合は、
サブクエリの上でもう一度MIN()で囲んであげても良いでしょう。
ここで、type別で、最小のstart_dateをもつゲームのタイトルを取りたい時は以下のように書きます
SELECT AS STRUCT して、* で全てを取り出します。
これは分かりやすいですね。
SELECT AS STRUCT
覚えて帰ってください。
② 複数ある構造体(game.param)の中でkey, valueを持ってるパターン
(sample_tableを作成するクエリはgistに記載しています。)
例 :
- 2018年の仕様変更後にFirebase → Bigquery連携で生成されるテーブルで、一つのeventに複数のevent_paramsがついていてkey, valueのカラムがある
- publicデータのgithub_repos.commitsテーブルで、1つのcommitレコードに複数のtrailerがついていて、中にkey, valueのカラムがある
こちらでも同様にtype別で、最小のstart_dateを出したいとします。
①より多少面倒臭い感じになりますが、こう書くことができます。
最小のstart_dateをもつゲームのタイトルを取りたい時は、今回のテーマとあんまり関係ないので割愛させていただきます。
最上段でWINDOW関数のFIRST_VALUEを使えば解決します。
サブクエリと言うワードにはトラウマがある人も多いので
でもサブクエリとか書いちゃうとパフォーマンス落ちるんじゃないの〜？
とか言ってくる人もいると思いますが、
違うんですよ！
そんなことありません！
検証
読者のみんなも検証できるように、
publicデータのgithub_repos.commitsテーブルを用います。
このテーブルはcommitsレコード1つに対して、複数のtrailer構造体がついていて、その中では、keyカラムとvalueカラムで値を持っています。
Firebase → Bigqueryの連携をしている人ならお馴染みのデータの持ち方ですね。
[検証1] keyが特定の値の時のvalueを取り出して集計してみましょう。
以下のようなクエリになります。
上記クエリは結果が全く一緒になるのですが、
実行の詳細は変わってきます。それがこちら
用語は以下のように説明されています。
消費したスロット時間 :
スロットとは、SQL クエリの実行に必要な演算能力の単位です。シャッフルされたバイト数 :
BigQuery は、大規模なコンピューティングに対応するためにデータをシャッフルします。
この結果自体は実行するたびに変わりますが、傾向は変わりません。
経過時間とスロット時間 は 私の提唱する新しい書き方に軍配が上がったようですね！
でもこんなことを言うとまた、
「よく見ると下のクエリ、SELECT AS STRUCTじゃないじゃん！」
とか言われそうなので、
次はSELECT AS STRUCTがある問題で検証しました。
[検証2] keyが特定の値の時のvalueを取り出して並べてみましょう。
クエリは以下になります。
keyが特定の値を持たないtrailerをもつ行を除く必要がある分、
新しい書き方の方が不利な戦いになりそうです。
では、
実行の詳細はこちら
はい。
いかがでしたか？
結果は実行するたびに変わるので、負けたり勝ったりしますが、
少なくとも、めちゃくちゃパフォーマンスが落ちる、ということはなさそうです。
UNNESTをCROSS JOIN的な書き方するより、サブクエリでやった方がいい感じになることが理解いただけたかと思います。
我々BIチームは、美しく効率が良いクエリをかける方を募集中です。
もっと美しく書けるゾ！と言う方は、
僕にもっと美しいクエリを教えてください！！
