BigQuery解説 ~上手にコスト削減しながらBigQueryを活用する~

次世代システム研究室の Y.I です。 Google Cloud Platform (GCP) の BigQueryについてまとめます。今回はBigQueryのコスト節約についてご紹介します。BigQueryはDWHとして非常に優れたマネージドサービスだと思います。しかしデータ量が増えるにつれて、分析クエリを実行するほどに料金が高騰してきます。翌月の請求にびっくりしないために上手に料金を削減しながらBigQueryを便利に利用しましょう。最後にご紹介する 「テーブルサンプリング（tablesample)」 は初期調査時に非常に有効です。ぜひご活用ください。

BigQueryコストルール

BigQueryのコストを削減するために、何にコストがかかるのか把握しましょう。主に3パターンあります。

ストレージ料金

ストレージ料金は、BigQueryに読み込むデータを保存するコストです。保存期間、データ量でコストがかかり、アクティブストレージと長期保存の2種類があります。

アクティブストレージ

1. 過去90日間で変更されたテーブルやパーティションのこと
2. 約 1TB を 1ヶ月 保存すると約 $20 ほどかかります（USリージョンの場合）

長期保存

1. 90日間連続して変更されていないテーブルやパーティションのこと
2. そのテーブルのストレージの料金は自動的に約50%値引きされます
3. 約 1TB を 1ヶ月 保存すると約 $10 ほどかかります（USリージョンの場合）

分析料金

クエリを実行した際にコストがかかります。クエリが参照したデータ量によりコストがかかるオンデマンド料金と他には事前に購入する定額料金があります。

オンデマンド料金

1 TB 参照につき $5.00

ストリーミング処理

BigQueryへのデータ登録(import)および抽出(Export)をストリーミングで行うとコストがかかります。なお、外部リソースからバッチ処理で登録および抽出を行う場合はコストがかかりません。要件が許すならばバッチ処理で登録しましょう。

バッチ処理

1. GCSに置いてあるファイルや外部データソースからバッチ的に登録するような処理
2. 無料

ストリーミング処理

1. 小さいデータをリアルタイムに継続的に登録するような処理
2. リアルタイムで登録したデータを参照できます
3. 登録: $0.010 per 200 MB など
4. 抽出: $1.1 per TB read

BigQueryコスト節約方法

コスト節約方法を以下にまとめます。

保存するデータを少なくする/期間を短くする

保存するデータを少なくする

保存するデータ量に対してコストがかかるので可能な限り登録するデータを少なくしましょう。webサーバーのアクセスログをBigQueryに登録して各種KPIを集計するようなユースケースの場合、アクセス数が多いサーバーだと1つのカラムを削るだけでもコストに差が出てきます。登録したデータを参照することでさらにコストがかかるので不要な情報は削ることを検討しましょう。

可能な限り登録するデータを少なくしましょう

大量なログなどは不要なカラムを1つ削るだけで大きな違いになってくる

保存期間を短くする

保存する期間に対してコストがかかるのでデータ保存期間は短くしましょう。テーブルやパーティションに expire を設定して自動で削除されるようにする、集計した元データは削除するなど。

データ保存期間は短くしましょう

expireを設定しましょう

bq コマンドでの expire 1日(86400sec)の設定例

bq update --expiration 86400 --project_id=xxx dataset.tablename

外部テーブルを利用する

外部テーブルを使用すると、BigQuery外部に保存されたデータに対してクエリを実行することができます。その際、BigQueryよりもより安いStorageに保存したデータの場合、storageコストを削減できます。外部テーブルとして利用できるのは、 Cloud Storage, Bigtable, Google ドライブです。 Cloud Storage に圧縮したファイルで保存するとより安く利用できます。関わったPJでは AVRO でスキーマを定義して、SNAPPY で圧縮した Cloud Storage のファイルを外部テーブルとして使用しています。

BigQuery外部のデータソースを参照できる

Cloud Storage, Bigtable, Google ドライブが対象

AVRO, SNAPPYなどでファイルを作成

Cloud Storageでは、CSV, JSON, AVRO, ORC, Parquet, Datastoreエクスポート, Firestoreエクスポートに対してクエリーを実行できます。

bq コマンドでの外部テーブル作成例

# BigQueryからGCSにexport
bq extract --project_id=xxx --destination_format=AVRO --compression=SNAPPY table gs://path/to/file

# 外部テーブル定義作成
bq mkdef --source_format=AVRO "gs://path/to/file" > table_definition.json

# 外部テーブル作成
bq mk --project_id=xxx --external_table_definition=table_definition.json --expiration=86400 dataset.tablename

テーブル分割/パーティショニング

1つのテーブルを分割してデータを保存することができます。分割されたテーブルパーティション毎に expire を設定できるのでパーティション毎にデータの自動削除が可能です。またクエリでデータを参照する際も必要なパーティションのみ参照するのでクエリコストの削減も可能です。

テーブルを分割するパーティショニングを使いましょう

パーティション毎にexpire設定できる

TIMESTAMPカラムによるパーティショニングテーブルのサンプルです。PARTITION BY で分割ルールを指定できます。

CREATE TABLE IF NOT EXISTS yi_dataset01.yi_table_partition_dt
(
  name STRING NOT NULL
  ,dt TIMESTAMP NOT NULL
)
PARTITION BY TIMESTAMP_TRUNC(dt, DAY)
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
  partition_expiration_days=30,
  description="a table that expires in 2025, with each partition living for 30 days",
  labels=[("org_unit", "development")]
)

クラスタリング

BigQueryのクラスタリングを他のDBMSの機能で言い切ってしまうと、インデックス（Secondary Index）になります。クラスタの指定とクエリ条件（WHERE句など）が合うと、クエリ実行が速くなり参照量が減るためコスト節約になります。最大4つまでの指定したカラムでソートされて保存されます。その際並びが近いデータが近い場所に保存されるため、参照時にデータを探すサーチ量が少なく済みコスト削減につながります。またクエリ実行時間の短縮化にもつながります。

インデックス（Secondary Index）のような機能

クエリ参照データ量が減りコスト節約

クエリ実行速度が速くなる

クラスタリングテーブルのサンプルです。 CLUSTER BY でカラムできます。なおパーティションテーブルもクラスタリングできます。

CREATE TABLE IF NOT EXISTS yi_dataset01.yi_table_partition_dt_cluster
(
  name STRING NOT NULL
  ,quantity INT64 NOT NULL OPTIONS(description="NOT NULL FIELD")
  ,n_value NUMERIC(10,5)
  ,dt TIMESTAMP NOT NULL
)
PARTITION BY TIMESTAMP_TRUNC(dt, DAY)
CLUSTER BY name
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
  partition_expiration_days=30,
  description="a table that expires in 2025, with each partition living for 30 days",
  labels=[("org_unit", "development")]
)

参照するデータを少なくする

GCP Web Consoleのpreviewで参照サイズを確認する

クエリを実行する前に参照サイズを確認することができます。 GCP Web Console や bq コマンドで確認できます。データサイズが大きいテーブルを参照するクエリを実行する前に必ず確認するようにしましょう。

事前にクエリ参照データサイズを確認しましょう

bqコマンドの場合 –dry_run を指定してクエリを実行することで確認可能です。

$ bq query --use_legacy_sql=false --dry_run \
> 'SELECT name
> FROM `yi_dataset01.yi_table_basic`
> WHERE
>   name = "reocrd01"
> '
Query successfully validated. Assuming the tables are not modified, running this query will process 60 bytes of data.

参照するカラムを指定する

BigQueryはカラムナーデータベースなので、参照するカラムを少なくすればするほど参照サイズが減ります。 select * で全てのカラムを指定するのは可能な限り避けましょう。参照するカラムが多い場合に除外するカラムを指定する except という関数もあります。 select * except(col1, col2) のような形で col1, col2 カラムを除いてクエリーを実行できます。

参照するカラムを少なくする

SELECT * は避けてカラムを指定する

SELECT * EXCEPT(col1..) でカラムを除くこともできる

中間テーブルを作成する

解析などの調査時は必要なデータだけを別のテーブルにコピーして利用しましょう。必要なカラム、必要な条件で絞ったデータのみ利用するようにします。解析を行う際は、良い結果を探すために何度もクエリを実行することが多くなると思います。その際に必要なカラム、条件にあったレコードのみがあれば最小限のサーチ量ですみます。

必要なカラム必要な条件で中間テーブルを作成する

キャッシュを有効に使う

BigQueryは初期設定でクエリ実行結果を参照無料な一時テーブルに保存してキャッシュしてくれます。次回に同じクエリーを実行するとキャッシュされた一時テーブルから無料で結果を取得します。ただし、クエリー結果が完全に一致する必要があり、CURRENT_DATETIME など実行時に値が変わるクエリーはキャッシュは利用されず都度データ参照されコストがかかります。

クエリー結果は自動で無料の一時テーブルに保存される

結果が同じクエリーは一時テーブルから無料で参照できる

CURRENT_DATETIMEなど実行毎に値が変わるクエリーは対象外

テーブルサンプリング（tablesample)

サンプリングを使用すると、指定したpercentレコード件数をランダムで取得することができます。その際、サーチ量はpercent分なのでコストを節約することができます。解析時の初期調査として使用すると非常に有効活用できます。なお、limit句はサーチ量の節約にはならないので、limit を使う場合は、tablesample を検討しましょう。

tablesampleで参照レコード数を減らすことができる

limitは参照レコード数を減らすことはできないのでtablesampleを使いましょう

テーブルサンプリング指定方法

SELECT col FROM `dataset.table` tablesample system (10 percent);

publicデータgsodでtablesampleのデータサーチ量を比較

tablesamle percentで参照サイズが減ることを確認できます。

全件参照

select * from `bigquery-public-data.samples.gsod`;
Records read: 114,420,316（1.1億）
Records written: 114,420,316（1.1億）
(検証のため参照サイズが大きくなるようにselect * しています。おすすめしません）

サンプリング10% (tablesample)

select * from `bigquery-public-data.samples.gsod` tablesample system (10 percent);
Records read: 10,941,546（1千万）
Records written: 10,941,546（1千万）

サンプリング10% & rand 1/10000 (tablesample)

select * from `bigquery-public-data.samples.gsod` tablesample system (10 percent) where rand() < 0.00001;
Records read: 10,941,546（1千万）
Records written: 125 (rand()<0.00001により125)

サンプリング1% (tablesample)

select * from `bigquery-public-data.samples.gsod` tablesample system (1 percent);
ecords read: 994,963 (100万)
Records written: 994,963 (100万)

サンプリング1% & rand() 1/1000 (tablesample)

select * from `bigquery-public-data.samples.gsod` tablesample system (1 percent) where rand() < 0.0001;
Records read: 995,224（100万）
Records written: 102

最後に

グループ研究開発本部では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。