こんにちは、エンジニアリンググループ、データ基盤チームの木田です。
最近我が家では手作りピザがブームになっており、週末になると度々生地をこねては家庭内ピザパーティーを開催しております。
さて、エムスリーではBigQueryをメインのデータウェアハウスとして活用していますが、費用最適化の取り組みの 1つとして一部のデータマートでクラスタ化テーブルの活用を始めました。本日はその導入効果をご紹介できればと思います。
この記事は【データ基盤チーム ブログリレー4日目】です。データ基盤チーム設立の経緯についてはブログリレー1日目の鳥山の記事をぜひご覧ください。
はじめに
エムスリーでは2018年頃からBigQueryの運用を始めており、蓄積されるデータ量、利用者数、ユースケース共に順調に増加して今日に至っています。そしてそれに比例する形で費用も増加しており、これをいかに大きな工数や利便性低下をもたらすことなく適正化するかもまたデータ基盤担当としては重要な課題と捉えています。
2023年7月5日 より BigQuery Editionsをはじめとした新料金プランが適用開始となり、オンデマンド料金の値上げもあった昨今、関心事がスキャンデータ量だけではなく、消費スロットにも向くようになったのではないでしょうか。エムスリーでは、性能の安定や運用の簡易さというメリットを考慮して固定料金やFlex Slotではなくオンデマンド料金で運用してきました。当座はオンデマンドでの利用を継続しようと考えていましたが、いくつかのワークロードについてはEditionsに移行することで費用面での効率化余地があることが試算で分かり、現在Editions併用もチーム内で検討中です。この辺りについても機会があればまた記事化してご紹介できればと思います。
費用最適化のアプローチ
費用最適化のためのアプローチとして、オンデマンドであればスキャンするデータ量の削減、Editionsであれば消費slot量の適正化が費用対策として有効であることは論を俟たないかと思います。 私が兼務で所属している製薬プラットフォームチームでは、データエンジニアがBigQuery上でデータマートを提供し、アナリストやビジネスサイドがそれを利用してSQLやTableau, Lookerのようなダッシュボードツールでデータを活用するという運用形態がよくとられています。
そのような環境で費用最適化を進めようとした時、 大きく分けるとデータマートを活用してクエリを書いたり実行したりするエンジニアやアナリスト各々が実行するSQLを改善する方法と、データマート等の形でテーブルに適切な設定を行なって提供する方法があろうかと思います。
前者についてはGoogle Cloudの公式ドキュメントとしてクエリの最適化のためのガイドやベストプラクティスが複数紹介されており、クエリやダッシュボードのレビューや各種ガイドの整備などを通じて組織内に広めていくことが有効な打ち手と考えています。
後者の対応としてできることの中に、提供するテーブルたちをパーティション分割テーブルやクラスタ化テーブルにすることや、TableauやLookerのようなBIツールからのアクセスに対しては BIEngineのようなキャッシュ機構の活用が挙げられます(もちろんデータマート構築時においてもクエリの最適化のベストプラクティスが有効です) 。
弊社のデータマートは、一定規模以上のファクト表を中心にほぼパーティション分割テーブルで提供できていましたがクラスタ化までは踏み込めていませんでした。しかし、最近一部データマートでクラスタ化を使い始めて実際に効果を上げ始めています。データマート提供時にできる効率化施策は、データマート利用者数が多ければレバレッジが効きやすく、個々のクエリ実行者側での工夫が前提となるクエリの最適化に比べて限られた工数内での対応であっても効果を享受することが期待できます。
クラスタ化テーブルとは
Google Cloudのドキュメントから引用ですが、データの物理配置に関する制約をあらかじめ指定しておくことでパフォーマンス向上及びクエリ費用の削減効果が期待されるものです。
BigQuery のクラスタ化テーブルは、クラスタ化列を使用したユーザー定義の列並べ替え順序があるテーブルです。クラスタ化テーブルを使用すると、クエリのパフォーマンスが向上し、クエリ費用を削減できます。
クラスタ化テーブルの概要 | BigQuery | Google Cloud
クラスタ化テーブルの作成方法
1) テーブルの新規作成や洗い替え(CTAS)で作成する場合
create table 文に cluster by のキーワードでクラスタキーとなる列を設定できます。式などではなく実際にテーブル存在する列であることが必要です。
create or replace table dataset_name.table_name ( /* カラム定義*/ ) cluster by cluster_key_column as /* select文*/
2) 既存のテーブルにクラスタキーを追加 (変更)する場合
bq updateコマンドで設定が可能です。 (即時反映されるわけではなく、バックグラウンドで自動再クラスタ化が動くため反映にはしばらく時間がかかります)
bq update –clustering_fields cluster_key_column dataset_name.table_name
実際に速く・安くなるのか
クラスタ化するテーブルのデータ量・クラスタキーの選択、実行するクエリの構造などの前提条件によって結果が変わりうるという断り書きをさせていただいた上での報告になりますが、弊社のデータマートでは実際に顕著な改善効果が見られました。具体的にはそのテーブルをスキャンするクエリのスキャンデータ量が1日あたり約7.5TBあったところを600GBまで減らすことができ (90%以上削減) 、これは想像以上の効果でした。スキャン量の削減と同様に消費スロットに関しても抑えられたという結果になりました。
スキャン量や消費スロット時間のようなKPIは、BigQueryのコンソールでクエリ実行結果とともに確認できます。また、INFORMATION_SCHEMA.JOBS_BY_PROJECTビューなどを活用すれば直近のクエリ実行履歴として確認できます(以下はクエリの一例) 。
select creation_time, user_email, job_id, statement_type, total_bytes_processed, total_slot_ms, from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT where creation_time between @start_time and @end_time
複合キーによるクラスタリング
cluster by a, b,... のような形式で複数の列をクラスタキーに指定できます (最大4列) 。その際はRDBの複合インデックスのように、WHERE句でもa, bの順で絞り込むことで最大の効果を発揮します。例えば a, bの順でクラスタキーを指定したテーブルに対して、キーbのみで絞り込もうとするとテーブル全体をスキャンしたり、あるいは部分的スキャンになったとしても本来必要とされる以上のスキャン量になってしまいます(消費スロットもスキャン量に比例する形で増えてしまいます) 。
クラスタ化の隠れた効用
クラスタ化テーブルの隠れたもう１つの効用として物理バイト数の削減にも効いていそうだということがわかりました(※)。同じテーブルでクラスタ化あり・なしを比較したところ論理バイト数が同じでも物理バイト数はクラスタ化したテーブルの方が半分程度となっておりました。似たようなデータが近く配置された結果、圧縮効率が上がったのではないかと勝手に想像しています。直近のBigQueryの料金改定で、物理ストレージに応じた課金体系への移行もアナウンスされていましたので、物理ストレージベースの課金に移行する際は、その点でもアドバンテージになりそうです。
※ 公式にそのような効果がうたわれているわけではなく、弊社のいくつかのテーブルの容量を確認した結果であることにご注意ください
クラスタ化列の選定
上記で紹介した通りクラスタ化の改善効果が大きかったことから他のテーブルへの横展開も進めています。実際にクラスタ化を行うにあたり、クラスタ化列の選択が悩ましい問題になる可能性があるかと思います。カーディナリティが高く(ユニークな値数が多く、選択性の高い列) かつクエリのWHERE句に指定されやすい列を選ぶのが定石となりますが、テーブル数が多いと個別のテーブルごとに最適なキーを選んでいくのは一仕事です。
そんな悩みに応えるかのように、直近のクエリ履歴などからクラスタ化のキーを提案してくれる機能もpreviewで公開されており、まずは推奨された設定を元に始めてみるのも一案でしょう。
まとめ
BigQueryのクラスタ化テーブルを活用することで、スキャン量・使用スロット共に削減しパフォーマンス向上と費用圧縮を達成できた事例を紹介しました。クラスタ化テーブルの導入はオンデマンド料金・Editions料金のどちらの料金プランであっても効果が期待できるので是非ご検討いただければと思います。
We are Hiring!!
エムスリーではBigQueryを活用してビジネスインパクトを創出するとともに、費用の最適化にも取り組んでいます。 少しでもご興味を持った方は、以下ページよりカジュアル面談等にお申し込みいただければ幸いです！
