2023.07.6

【BigQuery】TABLESAMPLE SYSTEMを日本一詳しく解説する

1. はじめに

こんにちは、エクスチュアの大崎です。

皆さんはBigQueryのTABLESAMPLE SYSTEM句をご存知でしょうか？

今回はBigQueryを使う上で便利だけど、あまり知られていない（個人の感想です）、TABLESAMPLE SYSTEMについて詳しく解説していきたいと思います。

それでは、いってみましょう！

2. TABLESAMPLE SYSTEMとは？

TABLESAMPLE SYSTEMは、BigQueryで使用できるSQLクエリの一部で、テーブルの一部分をランダムに抽出するために使用します。

使い方は以下のようにFROM句の末尾にTABLESAMPLE SYSTEM(x PERCENT)を追加するだけです。

※サンプルサイズを調整したい場合はxを調整してください

SELECT *
FROM `google_analytics_sample.ga_sessions_*`
TABLESAMPLE SYSTEM(10 PERCENT)

簡単ですね。

次からはこのTABLESAMPLE SYSTEMを使うメリットについてみていきましょう。

3. 何が嬉しい？

TABLESAMPLE SYSTEMを使うメリットを一言で表すとスキャンサイズを減らす＝課金を減らすことができます！

BigQueryでクエリを書いている時にスキャンサイズが数テラバイトになることがあります。

特に実行結果が合っているかもわからない時にクエリを実行して結果を確かめるのは無駄に課金を発生させる原因になってしまいます。

こんな時、力を発揮するのがTABLESAMPLE SYSTEM句です。

TABLESAMPLE SYSTEMは主に以下のような時に力を発揮します。

【前提】クエリ対象のテーブルがGoogleAnalyticsの行動ログデータなど大きい（スキャンサイズが大きい）とき
正規表現や複雑なクエリなどを書きながら実行し、調整したいとき
カラムに入っている値の一覧を取得したいとき
ランダムな行（レコード）を取得したいとき
その他、全行を見る必要がないとき

前置きが長くなりましたが、次からはTABLESAMPLE SYSTEM書くと実際にどうなるかをみていきます。

4. TABLESAMPLE SYSTEMを使ってみた

以下はTABLESAMPLE SYSTEMを使わない場合のスキャンサイズです。

続いて、TABLESAMPLE SYSTEMを追加してみます。

他の部分には手を加えていないのに、スキャンサイズが1/10になりました！！

ちなみに同じように行数を減らす方法として`LIMIT句`を使うことがありますが、LIMITにはスキャンサイズを減らす効果はありません。（ここテストに出ます！）

実際にクエリを実行してみましょう。

GoogleAnalyticsのログからブラウザごとの行数を取得しています。

SELECT device.browser, count(*) as count
FROM `google_analytics_sample.ga_sessions_*`
TABLESAMPLE SYSTEM(10 PERCENT)
GROUP BY device.browser

browser	count
Chrome	60,261
Safari	18,522
Firefox	3,655
Internet Explorer	1,794

実行結果はこのようになりました。

全てを対象にして正確な数値を取得することもできますが、全体の傾向値をざっくり知りたい場合はこれだけでも十分ですよね。

5. 注意点

TABLESAMPLE SYSTEMは非常に便利ですが、ランダムサンプルを取得するという性質上、必ずしも全ての行が均等に選ばれるわけではありません。

また、上でも示したようにサンプルがデータセット全体を代表するわけではないため、日々のレポーティングや正確な数値が求められる場面では向かないことには注意が必要です。

また、シード値を固定できないため、実行するたびに結果が変わってしまうことにも注意が必要です。

6. 最後に

今回はBigQueryのTABLESAMPLE SYSTEMについて解説しました。

非常に便利な機能ですが、中々使っている人もいないのではないでしょうか？

つけておいて損はないので、大規模テーブルに対するクエリを開発する際は是非使ってみてください！（クエリ完成後の取り忘れにはくれぐれもご注意くださいね…）

エクスチュアはGoogle Cloud Platformのサービスパートナーです。

Cloud WorkflowsやGA4を含むGoogle Cloud Platform、Adobe Experience Cloud、Tableau、Lookerなどに精通したスタッフによるデータ活用サポート、各マーケティングツールの導入実装・活用支援のコンサルティングサービスや、GCP/AWSなどのパブリッククラウドを使ったデータ分析基盤構築コンサルティングサービスを提供しております。

デジタルマーケティングに関するお悩みや活用支援、他分析ツールなどについてお困りの方は
お気軽にご質問・ご相談ください。