はじめに

株式会社ONE COMPATHのmaseです。
弊社ではBigQueryを主にオンデマンド課金で利用中です。
（オンデマンド課金：クエリ量に応じた従量課金）

新たにBigQueryに触れる人も増えてきているため、
BigQuery初心者の方でもオンデマンド料金に怖がらず利用ができるよう、簡単な節約のコツをまとめました。

ここでは記載しないこと

BigQueryの説明
BigQueryの料金体系の説明
スロット課金の場合の節約方法

オンデマンド料金節約のコツ

クエリ量の見積もりを常にチェックする

基礎的なことですが、クエリの実行前に必ずクエリ量の見積もりをチェックしましょう。
Webのコンソールだとクエリ欄の右上に表示されます。

コマンドやライブラリから実行する場合はdry runオプションを活用してください。

クエリ量をお金に換算できるようにする

クエリ量を見てもMBやGBではいくらかかっているか実感が湧きません。
ご利用のリージョンの単価を調べればお金換算できますので、クエリ量から大体の金額に換算できるようにしましょう。
レートは日々変わるので大まかな計算で大丈夫です。

usリージョンで1GiBにつき日本円でいくらか概算してみる

usリージョン $6.25 / TiB (2024/10現在の価格)
1TiB = 1024GiB だが簡易化のため1000とする
$1 = 150円とする

$6.25 / 1000 * 150 = 0.9375円 ≒ 1円

というわけで、1GiB = 大体1円と覚えておくと試算がしやすいです。

レートの変動やご利用のリージョンの単価により計算が異なりますのでご注意ください。

クエリを書く時のコツ

使うカラムを絞る

オンデマンド料金ではスキャンするカラムの容量によって課金されます。
select * するとテーブルをフルスキャンしてしまうため、本当に必要なケースを除いて使用しないほうが良いです。

-- select * は（本当に必要な時以外）使用しないこと！
select * from sample;

selectやwhereなどに出てきたカラム全てがスキャン対象になりますので、余分なカラムは書かないようにしましょう。

-- select句とwhere句に出てきたカラムがスキャン対象となる
select id, name from sample where id = 1; -- id, name列が対象
select id, name from sample where age >= 20; -- id, name, age列が対象

必要なカラムが多すぎる時はselect * exceptを使用しましょう。
こう書くことでid,name以外の列を全て表示できます。

select * except (id, name) from sample;

なるべく検索する「列」を最小限にするのがコツです。

検索範囲を絞る

カラム方向に絞るのも重要ですが、レコード方向にも絞る様にしましょう。
limit句はフルスキャン後に表示を絞るだけなので、意味がありません。

-- limit句は容量削減にならない
select id, name from sample limit 10;

テーブルの中身を少し確認したいという用途ならテーブルのプレビュー機能を使いましょう。

検索対象がパーティションテーブル（後述）の場合、必ずパーティション対象列を条件に入れて範囲を絞りましょう。

-- パーティション対象列はwhere句で指定する
select id, name from sample where birthday between '1990-01-01' and '1999-12-31';

なるべく検索する「行」を最小限にするのがコツです。

大きいクエリなら中間テーブルを作る

大きなデータ元テーブルから複数のサマリテーブルを作るケースなど、特定の状況においては中間テーブルを作成することでクエリ量を削減できます。

例）GoogleAnalyticsのデータから2つのサマリテーブルを作る場合

上記は弊社の実例ですが、削減量は5GBとはいえ毎日動く処理のためかなりのコスト削減となっています。

テーブル設計のコツ

クラスタリング

BigQueryのクラスタリング（クラスタ化）では特定の列を指定するとその列の値で内部的に並び替えされます。
クラスタ化した列で絞り込みを行うと、その値の範囲のみしかスキャンされなくなるためクエリ量の削減になるほか、パフォーマンスも向上します。

公式ドキュメントより引用

上記の例ではwhere句でCountryを絞り込むことでクエリ料を4分の1に削減できます。
また、右の表のようにクラスタリングは複数カラムでも可能です。

パーティショニング

クラスタリングでは内部的な並び替えでしたが、パーティショニングではパーティションと呼ばれるセグメントにテーブル自体を分割します。
パーティション対象列を条件に入れることで検索対象を絞ることができ、クエリ量の節約になります。
使用できる列が整数型または時系列型と限られますが、パーティション自体に保存期間を設定でき、○○日経ったら自動削除といった処理が可能です。
パーティションの単位は年単位〜時間単位まで選べますが、1テーブルにつき作れるパーティションの上限が決まっていますのでご注意ください。
（現在は1テーブル最大4000）

詳しくは公式ドキュメントを参照ください。

料金を可視化しよう

いくら気をつけてもお金はかかるものです。
かかった料金を簡単に確認できるよう、可視化の手法をいくつか紹介します。

ご自身の権限によって見られるものが異なります。

Google Cloud＞お支払い＞レポート

クエリ個別の金額ではなく、日付単位の合算でよければGoogle Cloud全体のお支払いページにてレポートを確認できます。
「グループ条件」欄で「SKU」を選択した際に「Analytics」と表示される項目がクエリ料金です。（※オンデマンド課金のみの場合）
利用サービスが多くて表示項目が多すぎる場合は、「サービス」欄で「BigQuery」を選択して絞り込むと見やすくなります。

BigQuery＞モニタリング

1回のクエリ実行単位（ジョブ単位）で確認したい場合はBigQueryの「モニタリング」機能で確認可能です。
「リソースの活用」タブにて直近30日間のジョブの処理バイト数が確認できます。
実行ユーザーでフィルターをかけたりできるので、より細かい確認が可能です。
ただし金額換算されてはいないため、課金額を把握したい場合は自身で計算する必要があります。

INFORMATION.SCHEMA

可視化の仕組みを自作するという手もあります。
INFORMATION.SCHEMAというビューから様々な情報が取れるようになっています。

データセット一覧とその情報
テーブル一覧とその情報
ジョブの情報
etc

このビューを利用して「どのクエリがいくら使ったか」を保存するクエリ料金履歴テーブルを作成し、毎日更新するようにスケジュール設定をします。
Looker Studio等を利用してクエリ料金履歴テーブルを可視化すればお好みのダッシュボードを作ることが出来ます。

こちらに関しては以下の記事を参考にさせて頂きました。
BigQueryのコストと利用状況を可視化してみた

おわりに

以上、簡単にですがオンデマンド料金節約のコツをお伝えしました。

クエリ利用料見積もりのチェックを欠かさない
クエリ作成時、検索範囲をなるべく絞り込む
テーブル作成時に可能であればクラスタリングとパーティショニングを利用する
利用料金を可視化して日頃から気にしておく

上記4点を意識して課金に怯えない快適なBigQueryライフをお送りください。