BigQuery

どのようなものか

BigQueryは、Google
Cloudの管理型SQL分析サービスです。利用者がサーバーを用意する必要はな
く、テーブルは列ごとに保存されます。クエリの料金は、読み取ったバイト
数に応じて支払うか、事前に予約した計算能力からまかないます。Google
BigQueryという名前でも提供され、その名前でも検索されます。SQLには
GoogleSQLを使います。ANSI標準に従い、独自の関数を加えた方言です。

設計の基本は、ストレージと計算を分離することです。テーブルはGoogleの
ストレージ層に置き、クエリは実行している間だけ計算資源を借ります。各
列を個別に保存するため、五十列のテーブルから三列を指定するクエリは、
その三列だけを読み、残りはディスク上に置いたままにします。したがっ
て、調整の仕方はPostgreSQLサーバーと異なります。B-treeインデックスを
設計したり、メモリー容量を決めたりする代わりに、テーブルの構成を整え
ます。日付・タイムスタンプの列、取り込み時刻、整数の範囲でパーティ
ション分割でき、最大四列でクラスタリングして、関連する行を同じスト
レージブロックにまとめられます。

BigQueryはデータウェアハウスの一種で、通常はSnowflakeやAmazon
Redshift、または会社ですでに運用しているトランザクション用データベー
スで分析を続ける選択肢と比較します。数百万、数十億の行を走査し、少数
の集計値を返すクエリ向けに設計されています。アプリケーションが要求の
たびに行う、一行ずつの検索や更新は、PostgreSQLやSQL
Serverなどのデータベースが担う仕事です。両方を運用し、パイプラインで
一方から他方へデータを送るのが、よくある構成です。

なぜ重要なのか

BigQueryを検討するチームが決めるのは、分析にどう料金を支払いたいかで
す。請求額は、誰かが一度選んだマシンの大きさではなく、人が書くクエリ
で決まります。それは利点にも、不利益にもなります。

オンデマンド料金では、各クエリが処理するバイト数で課金します。不定期
の作業に適しており、週に数回、重いレポートを実行するチームは、そのレ
ポートの分だけ支払い、実行しない間の料金はかかりません。夜間に待機す
るサーバーもありません。一方、不注意なクエリにも費用が生じます。一年
分のセンサー測定値に対する`SELECT
i*`は、分析担当者が一行だけ欲しかった場合も、全行が欲しかった場合も、		engineer.company	70
i同じ費用がかかります。容量ベースの料金では、代わりにスロットを使いま		engineer.company	70
iす。予約を通じて購入する計算能力の単位で、請求額を事前に分かる数値に		engineer.company	70
iし、負荷の高い利用が安定して続く場合に、費用を抑えた計画を立てやすく		engineer.company	70
iします。プロジェクトは二つの料金体系を切り替えられるため、実際の利用		engineer.company	70
i状況が見えてから、最初の選択を見直せます。		engineer.company	70

ストレージには別のルールがあります。テーブルやパーティションを連続90
日間変更せずに置くと、移動の操作をしなくても、50パーセント低い長期保
存料金へ移ります。各データセットは、非圧縮の論理サイズか、圧縮済みの
物理サイズのいずれかで課金されます。よく圧縮できるデータでは、後者の
ほうが安くなる場合があります。

契約前には、三つの点を重視すべきです。データセットの配置場所は、単一
リージョン、またはUS・EUのマルチリージョンから作成時に選び、その後は
固定されます。初日に行う、法令順守に関わる判断です。また、SQLの方
言、`bq`コマンドラインツール、クライアントライブラリーはいずれも
Google独自のため、クエリを別のデータウェアハウスへ移すには、そのまま
ではなく修正が必要です。一方、テーブルをCSV、Avro、Parquetへ出力でき
るため、データ自体は容易に取り出せます。人材確保の難しさは、見かけほ
どではありません。SQLを知る分析担当者は、数日で実務に対応できます。
むしろ希少なのは、クエリを行数だけでなく、バイト数でも読み解く能力で
す。

では、BigQueryが適切なのは、どのような場合でしょうか。イベント、ロ
グ、メーターの測定値など、主に追記する大量のデータが入り、尋ねる内容
が週ごとに変わり、チームが分析用データベースサーバーの運用を避けたい
場合です。一台のPostgreSQLサーバーに余裕を持って収まるデータなら、
データウェアハウスは、二つ目のシステム、二つ目の請求、その間を結ぶパ
イプラインを増やします。導入の意義は、データ量と、データに問いかける
人の数が増えるほど大きくなります。

実際の活用例

PostgreSQL、Microsoft SQL Server、Google
BigQueryに分かれた100を超えるデータベースの環境では、座標、センサー
測定値、ログ、リアルタイム指標など、主に地理データと時系列データを保
持していました。BigQueryは大規模な時系列データを担当し、テーブルを時
間でパーティション分割し、クラスタリングしました。圧縮と列指向の保存
を使い、保存費用を抑えて走査を高速にしました。BigQueryのパーティショ
ン分割・クラスタリングした走査と、環境全体でのインデックス、マテリア
ライズドビュー、キャッシュにより、GISと時系列クエリの応答時間を40–
60%短縮しました。

遅くなっていた地理データのパイプラインでは、まずPostgreSQL、MS SQL、
Google Cloud
BigQueryのSQLとデータモデルを分析し、不十分なインデックス、不効率な
クエリ、足りない制約を特定しました。そのうえで、各エンジンに合った手
段を使いました。PostgreSQLにはB-treeとGiSTインデックス、MS
SQLにはフィルター選択されたインデックス、BigQueryにはクラスタリング
を適用し、モデルの正規化、パーティション分割、クエリの書き直しも行い
ました。創業者の実績記録では、パイプライン全体が50倍高速に動作するよ
うになったと報告されています。ただし、ベンチマークに用いたデータセッ
ト、ハードウェア、比較手順は明記されていません。

複数の地域のスマートメーターデータでは、PostgreSQLとBigQueryに対し、
500を超える分析レポートを純粋なSQLで作成しました。メーターID、タイム
スタンプ、座標、環境メタデータに、CTE、ウィンドウ関数、サブクエリ、
地理空間結合を使いました。各レポートには、同僚が再実行・監査できるよ
う注釈付きのSQLと、GPS座標の欠落やメーター値の破損など、既知のデータ
品質上の問題に関する説明を添えました。レポートは部署をまたいで参照さ
れ、業務計画の効率を10–15%改善することに寄与しました。

三つの事例すべてで、PostgreSQLをBigQueryに置き換えず、両者を併用し、
それぞれのエンジンに最も適した仕事を割り当てました。

問題が起きるところ

`LIMIT 10`のクエリで、テーブル全体の料金を請求されます。
クラスタリングしていないテーブルでは、`LIMIT`が減らすのは結果であ
り、読み取るバイト数ではありません。`SELECT
i*`は、存在するすべての列を読みます。少しデータを見るつもりが、全体の		engineer.company	70
i走査になります。問いに必要な列を指定し、行のサンプルを眺めるだけな		engineer.company	70
iら、無料のコンソールのテーブルプレビューや`bq		engineer.company	70
head`を使います。

パーティション分割しても、テーブル全体を走査します。
パーティションを読み飛ばすには、分割に使う列のフィルターが、読み取り
前にエンジンで評価できる定数である必要があります。その列をサブクエリ
の結果と比較したり、関数に渡したり、列に対して計算したりすると、クエ
リはすべてのパーティションを走査します。対策は二つあります。テーブル
でパーティションフィルターを必須にすれば、使えるフィルターのないクエ
リを、実行せずエラーで止められます。また、試行実行は、クエリを実行せ
ず、料金も発生させずに、処理する予定のバイト数を報告します。

```sh
bq query --use_legacy_sql=false --dry_run \
  'SELECT meter_id, kwh
   FROM energy.readings
   WHERE reading_date = DATE "2026-10-01"'
```

クラスタリングを加えても、読み取るバイト数がほとんど変わりません。
クラスタリングは、最大四列を使い、その順序でストレージブロックを並べ
ます。順序は重要で、注文日、国、状態の順にクラスタリングしたテーブル
は、国と状態だけのフィルターに適した配置ではありません。64
MB未満のテーブルでは効果が小さく、クラスタリングしたテーブルで実際に
走査したバイト数は、クエリ終了後にしか分かりません。クエリで絞り込む
順序に合わせて、クラスタリングの列を並べます。実行前に費用の上限を確
定する必要がある場合は、試行実行の時点で読み飛ばす部分を見積もれる
パーティション分割を使い、課金対象バイト数の上限も設定して、超過する
クエリが課金前に止まるようにします。

二つのデータセットを結合するクエリが拒否されます。
各データセットは一つの場所に置かれ、クエリも一つの場所で動くため、参
照するすべてのデータセットが同じ場所にある必要があります。プロジェク
トごとに既定の場所を選ばせると、一方を別の場所へコピーするまで結合で
きないテーブルが生まれます。法令順守とデータの発生場所を考えて、一
度、配置場所を決め、すべてのデータセットを意識的にそこへ作成します。

参考資料

  *
BigQueryの概要：サービス、分離されたストレージ層と計算層、利用者に代
わって管理する範囲についてのGoogle自身の説明です。

  * パーティション分割テーブルの概要：分割の種類、粒度、制限を説明し
ます。

  * パーティション分割テーブルへのクエリ：パーティションを読み飛ばせ
るフィルターと、すべてを走査するフィルターを説明します。

  * クラスタ化テーブルの概要：列の順序とブロックの読み飛ばしの関係、
それが費用見積もりに与える影響を説明します。

  * 費用の見積もりと管理：試行実行、課金対象バイト数の上限、テーブル
を無料でプレビューする方法を説明します。

  * BigQueryのロケーション：リージョン、マルチリージョン、複数データ
セットを参照するクエリのルールを説明します。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/bigquery/	engineer.company	70
