🙄

BigQuery初心者を脱出したい２（パーティション分割テーブル）

2021/09/05に公開

BigQuery初心者を脱出しようと記事を書いていきます。その２。

今回は費用を節約するためのパーティション分割テーブルの使い方について学んでいきます。

BigQueryの費用について（再掲）2021/8/7時点

オペレーション	料金(東京リージョン)	詳細
アクティブストレージ	$0.023 per GB	毎月 10 GB まで無料。
長期保存	$0.016 per GB	毎月 10 GB まで無料。

オペレーション	料金(東京リージョン)	詳細
クエリ（オンデマンド）	$6.00 per TB	毎月 1 TB まで無料です。

そもそも何故パーティション分割を行うのか

BigQueryはスキャンするデータ量によって分析料金が変わる。
一回のスキャン量が10TBの場合と1TBの場合で費用が10倍違ってくる。
普通にテーブルを作って普通にスキャンをしてしまうと毎回テーブルをフルスキャンすることになる。
一回のスキャン量を減らす為にパーティション分割テーブルというものが存在する。適切にパーティション分割テーブルを作り適切なクエリを発行すると、分割されたテーブルの内の必要なテーブルのみをスキャンするのでスキャン費用が節約できる。

以下の通り、パーティション分割テーブルの作成には何種類かやり方があるみたい。

→例として、一番分かりやすそうなので今回は「時間単位の列」を作成していく。

分割テーブルの概念・使い方が分かれば良いので今回も簡易なデータを用いて検証していく
users.csv

ID,Name,favorite,create_datetime
1001,田中 太郎,サッカー,2020-12-02 10:05:04.971
1002,鈴木 一郎,野球,2021-02-03 14:45:44.811
1003,山田 隆史,テニス,2021-05-04 21:35:11.071

作成を押すと以下のようなテーブルが作成される。

無事にパーティション分割テーブルとして作成されたよう

クエリウインドウから検索をかけていく。
まずフィルタをかけずに全量スキャンをかけようとしたらエラーになった。

``SELECT * FROM `avid-booster-xxxxx.dataset_test_01.users```


「パーティションフィルタを要求」のオプションが効いているよう。

次に全データが対象となるフィルタをかけて検索してみる。
SELECT * FROM `avid-booster-xxxxx.dataset_test_01.users` WHERE DATE(create_datetime) < "2021-12-31"

実行計画のスキャン量が120Bとなり、テーブル全体の大きさと一致したので想定通りフルスキャンになる結果となった。

次に三件中、二件だけ一致するようなフィルタをかけて検索してみる。
SELECT * FROM `avid-booster-xxxxx.dataset_test_01.users` WHERE DATE(create_datetime) < "2021-3-31"

実行計画のスキャン量が80Bとなり、パーティション分割テーブルをうまく利用してスキャン量を減らすことができた。

最後に、三件中、一件だけ一致するようなフィルタをかけて検索してみる。
SELECT * FROM `avid-booster-xxxxx.dataset_test_01.users` WHERE DATE(create_datetime) = "2021-2-3"

実行計画のスキャン量が37Bとなり、こちらも想定通りパーティション分割テーブルを利用してスキャン量を更に減らすことができた。

これでパーティション分割テーブルの３種の内の一つについて理解出来るようになりました。
取り込み時間、整数範囲、についても基本的な考え方は同じだと思うのでどのようなデータを扱うか、どのような検索を行いたいのか、によって分割方法を決めればいいのかなと思います。