〜基礎から発展まで〜 BigQuery×Looker Studio のクエリコスト削減方法！

Transcript

1. 〜基礎から発展まで〜 BigQuery×Looker Studio のクエリコスト削減方法！ 株式会社キュービック テクノロジーエキスパートセンター Tech Lead/データエンジニア 尾﨑勇太 2025.04.25 開示範囲:公開ドキュメント 
 1
2. 自己紹介 2 株式会社キュービック 役割：Tech Lead/データエンジニア 名前： 尾﨑 勇太（おざき ゆうた） 覚え方：尾崎豊(おざきゆたか)と一字違い 業務領域 「データに関わる全て」
3. 3 可視化したは良いものの、 クエリコストが課題。。。。
4. LT内容 4 1.基礎編：BigQueryのベストプラクティス対応 2.応用編：クエリストリング +Looker Studioのフィルタ 3.発展編：Looker Studioのクエリコストのモニタリング
5. BigQueryのベストプラクティス対応_パーティション 5 パーティション 日付単位で分割することで、指定期間のデータしかスキャンしなくなる ▷未設定の場合はWHERE句でBETWEEN句などを指定したとしてもフルスキャン SELECT * FROM `project.dataset.table` WHERE event_date = '2024-04-01' ❌ パーティションなし ✅ 日付パーティションあり 全データ (例:100GB)がスキャン対象 （WHERE句は後から適用） 1日分のデータのみスキャン （例：300MB）ス キャン量が大幅に削減 共通のクエリを実行した場合
6. BigQueryのベストプラクティス対応_クラスタリング 6 クラスタリング ブロック単位で設定することでテーブルを効果的にソートする ▷RDBでいうところのINDEXの設定に感覚としては近い。 SELECT * FROM `project.dataset.events` WHERE user_id = 'abc123' ❌ クラスタリングなし ✅ クラスタリングあり 全データがスキャン対象となりスキャン量が 多く、速度劣化に user_idの値がまとまって格納されている の で、該当ブロックのみをスキャンして高速＆低コ ストに 共通のクエリを実行した場合
7. BigQueryのベストプラクティス対応_集計テーブルの作成 7 アーキテクチャ データ収集 データ可視化 BigQueryに蓄積されたローデータをサーバレスアーキテクチャなどで整形し データを再蓄積&軽量化し、Looker Studioから参照する データ蓄積 ローデータ 整形データ 同期
8. 8 Looker Studioは運用者数に比 例してアクション数が増えるの で付け焼き刃感はある
9. LT内容 9 1.基礎編：BigQueryのベストプラクティス対応 2.応用編：クエリストリング +Looker Studioのフィルタ 3.発展編：Looker Studioのクエリコストのモニタリング
10. 当たり前だけど忘れがち 10 期間フィルタあり BigQuery 2025/04/01-2025/04/20 フィルタ結果 データスキャン量を最小化 　　 意図しない 　　 長期範囲の参照を軽減
11. Looker Studioを仮想的にAPI化 11 アーキテクチャ ページ内表示 パラメータをクエリストリングで連携することで、Looker Studioのカスタムクエリの集計条件 を描画前に調整し、ユーザーがみたい閾値で可視化が可能 BigQuery ページ参照 https://lookerstudio.google.com/u/0/reporting/54f a38e1-b459-4606-9a20-d86359802901/page/p_mnpnn34y nd?params={"ds76.roas_times_param":"0.5"} 表示要素演算 ユーザー フィルタ結果
12. イメージ 12 https://lookerstudio.google.com/u/0/r eporting/54fa38e1-b459-4606-9a20-d863 59802901/page/p_mnpnn34ynd?params={"d s76.organization_param":"組織B"} SELECT campaign_id, campaign_name, ad_group_id, ad_group_name, impressions, clicks, cost, revenue, revenue / NULLIF(cost, 0) AS roas FROM your_dataset.your_table WHERE organization= @organization_param ORDER BY roas DESC 組織はデフォルト値 クエリストリングでデ フォルト値を更新 データソースを再集計 組織A 組織B
13. 効果 13 クエリコストの削減、ユーザビリティの向上 ▷組織や事業所毎に分割をしなくて良くなり、サイロ化を低減 ▷最小限のアクションで見たいものを見れる状態が作れる 見たいものに絞り込んだ状態で提供する 運用統制、データ管理 お気に入り登録すれば、テンプレート分割は不要に※ ※データボリュームや情報規制とトレードオフになる可能性もあります
14. 14 でも実際どれぐらい使われてい るかは発生ベースになるので事 前に検知したり、改善提案をで きないか？
15. LT内容 15 1.基礎編：BigQueryのベストプラクティス対応 2.応用編：クエリストリング +Looker Studioのフィルタ 3.発展編：Looker Studioのクエリコストのモニタリング
16. INFORMATION SCHEMAから取得する 16 BigQuery フィルタ結果 SELECT DATETIME(creation_time, "Asia/Tokyo") AS creation_time_jst, -- クエリ作成日時 referenced_tables, -- 参照されたテーブル job_id, -- BigQueryのジョブ ID job_type, statement_type, (SELECT l.value FROM UNNEST(labels) l WHERE l.key = "looker_studio_report_id") AS report_id, -- Looker Studioのレポート ID (SELECT l.value FROM UNNEST(labels) l WHERE l.key = "looker_studio_datasource_id") AS datasource_id, -- データソース ID user_email, -- 実行ユーザー cache_hit, -- キャッシュ使用の有無 query, -- 実行クエリ total_bytes_billed / (1024*1024*1024) AS total_gb_billed, -- 請求データ量 (GB) total_slot_ms / 1000 AS total_slot_seconds -- スロット使用時間 (秒) FROM `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS WHERE DATE(creation_time, "Asia/Tokyo") BETWEEN DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY) AND DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 0 DAY) -- 昨日分のクエリ AND (SELECT l.value FROM UNNEST(labels) l WHERE l.key = "requestor") = "looker_studio" -- Looker Studioのクエリに限定 ORDER BY creation_time_jst DESC;
17. Looker Studioで可視化する 17 テンプレート単位での浸透率とデータ量の割合を可視化 利用率とスキャンデータ量の相関を把握 利用者にヒアリング(アタック)をかける モニタリング環境の改善とコスト削減が可能に EX.効果の薄いデータソースをクローズ より精緻にどこで?誰が?※何を？を可視化 ※オーナー認証やサービスアカウント認証では同一メールアドレスになります これは是非持ち帰って欲しい
18. 18 データエンジニアリングでここ まではできる！！
19. 19 ご清聴、ありがとうございました