Looker StudioとBigQuery連携で予想外のコスト増加の原因となる2つの挙動

G-gen の min です。Looker Studio から BigQuery をデータソースとして利用する際、意図しない挙動により BigQuery の利用料金が高額になるケースがあります。本記事では、特に CURRENT_DATE() のような非決定性関数を使用した場合のキャッシュの扱いや、プルダウンリストのコントロールによるクエリ発行時に注意が必要な挙動がありました。その共有と対策を解説します。

コスト増の原因になる挙動

Looker Studio レポートから BigQuery テーブルをデータソースとして使用しているとき、BigQuery の利用料金が想定を超えて高くなる原因として、2つの挙動を例示します。

これらの挙動は、予期せぬスキャン量の増加やコスト発生につながることがあります。

1. CURRENT_DATE() 使用によるキャッシュ無効化

BigQuery に対して実行されるクエリで CURRENT_DATE() のような非決定性関数（実行されるたび出力が変わる関数）を使用していると、BigQuery のクエリキャッシュが適用されません。

• 参考 : クエリのキャッシュの例外

例えば、以下のケースでは、クエリが毎回再実行されます。

• CURRENT_DATE() を使用して「今日から過去〇日間」のデータを取得するテーブルを作成
• そのテーブルを Looker Studio のデータソースとして使用

こういったケースでは、レポートの再表示やフィルタ操作を行うたびにスキャンが発生し、キャッシュが利用されないため、BigQuery 利用料金の増加につながります。

2. プルダウンリストのコントロールによるクエリ増加

Looker Studio のレポートに配置するプルダウンリストのコントロールは、以下のタイミングで BigQuery にクエリが発行されます。

• フィルタの選択肢を表示するためのデータ取得時
• ユーザーがフィルタの値を変更したとき

例えば、表グラフが 1 つ、プルダウンリストのコントロールが 4 つある構成のレポートについて考えます。レポートを開いたりフィルタを操作したりするたびに、合計 5 件以上のクエリが一度に実行されます。

このケースでは、グラフやプルダウンリストのコントロールが参照しているデータリソースに対するデータスキャン量とそれに伴うコストが増加します。

具体的な対策

BigQuery のスキャン量とコストを削減するための具体的な対策を以下に説明します。

対策 1-1 : 非決定性関数の除外

最も直接的な対策は、クエリから CURRENT_DATE() のような非決定性関数を削除することです。ただし、この方法ではデータの取得期間が固定されるため、動的な期間指定には別の工夫が必要です。

対策 1-2 : テーブル関数の使用

ビューを使用する場合、BigQuery のテーブル関数（Table Valued Function、TVF）を検討します。テーブル関数はパラメータを受け取ることができ、ビューのように振る舞います。さらに、動的な条件に基づいてデータをフィルタリングできるため、動的な期間指定が行えます。

Looker Studio のカスタムクエリからこのテーブル関数を呼び出し、その際に @DS_START_DATE と @DS_END_DATE といった Looker Studio の標準パラメータを関数に渡すことで、動的にパーティションテーブルへクエリすることで、 BigQuery のスキャン料金を削減することができます。

詳細は、後述の検証結果を参照してください。

• 参考 : テーブル関数
• 参考 : カスタムクエリでパラメータを使用する

対策2 : コントロール用データソースの分離とクロスフィルタリング

プルダウンリストのコントロール専用の軽量なデータソースを作成し、Looker Studio のクロスフィルタリング機能を使用します。ユーザーがプルダウンリストのコントロールで値を選択すると、クロスフィルタリング機能によって、その選択がメインのグラフに適用され、その際に必要なクエリが発行されます。

こちらの対策も、検証結果を後述します。

• 参考 : グラフのクロスフィルタリング

対策の検証

具体的な挙動と対策の効果を確認するため、検証環境を構築します。以降の SQL サンプルコードでは、プロジェクト ID とデータセット名を your-project.your-dataset と記述します。ご自身の環境の値に置き換えてご使用ください。

検証環境の構築

テーブル定義

まず、以下のスキーマで 3 つのテーブルを作成します。

売上ファクトテーブル (sales_fact) sales_date でパーティション分割します。

CREATE OR REPLACE TABLE `your-project.your-dataset.sales_fact` (
    sales_date DATE, -- 売上日
    store_id INT64, -- 店舗ID
    product_id INT64, -- 商品ID
    amount INT64 -- 売上金額
)
PARTITION BY sales_date;

商品マスタ (product_master)

CREATE OR REPLACE TABLE `your-project.your-dataset.product_master` (
    product_id INT64,
    product_name STRING,
    category STRING -- 商品カテゴリ
);

店舗マスタ (store_master)

CREATE OR REPLACE TABLE `your-project.your-dataset.store_master` (
    store_id INT64,
    store_name STRING, -- 店舗名
    region STRING -- 地域
);

サンプルデータ

各マスタテーブルに 10 件程度のマスタデータを投入します。sales_fact テーブルには、過去 3 年分、約 250 万件のトランザクションデータ（約75MB）を投入します。データ投入の具体的な SQL は長くなるため、ここでは割愛します。

分析用ビュー（問題のあるケース）

次に、CURRENT_DATE() を使用して直近 2 年間のデータを集計するビューを作成します。これが問題を引き起こす可能性のあるビューです。

CREATE OR REPLACE VIEW `your-project.your-dataset.sales_summary_problematic` AS
SELECT
    FORMAT_DATE('%Y-%m', f.sales_date) AS sales_month,
    sm.store_name,
    sm.region,
    pm.category,
    COUNT(DISTINCT f.product_id) AS product_count,
    SUM(f.amount) AS total_sales
FROM
    `your-project.your-dataset.sales_fact` f
JOIN
    `your-project.your-dataset.product_master` pm ON f.product_id = pm.product_id
JOIN
    `your-project.your-dataset.store_master` sm ON f.store_id = sm.store_id
WHERE
    f.sales_date BETWEEN DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 YEAR) AND CURRENT_DATE('Asia/Tokyo')
GROUP BY
    sales_month, sm.store_name, sm.region, pm.category;

挙動確認

上記で作成した sales_summary_problematic をデータソースとして Looker Studio レポートを作成し、年月や地域を選択するプルダウンリストのコントロールを配置します。

このレポートを表示すると、1回の表示操作で、BigQuery のジョブ履歴には以下のようなクエリが4件記録されました。 このクエリのスキャン量（課金されるバイト数）は、1件ごとに、約 50 MB でした。

SELECT
  clmn0_
FROM (
  SELECT
    t0.region AS clmn0_  -- region, sales_month, store_name, category に対して1件ずつ
  FROM (
    SELECT
      *
    FROM
      `your-project.your-dataset.sales_summary_problematic` ) AS t0 )
GROUP BY
  clmn0_
ORDER BY
  clmn0_ DESC
LIMIT
  2000001;

さらに、1回の表示操作で、BigQuery のジョブ履歴には以下のようなクエリが1件記録されました。 このクエリのスキャン量（課金されるバイト数）は、約 65 MB でした。

SELECT
  clmn0_,
  SUM(clmn4_) AS t0_qt_xxxxxxxxid,
  clmn1_,
  clmn2_,
  clmn3_
FROM (
  SELECT
    t0.category AS clmn0_,
    t0.region AS clmn1_,
    t0.sales_month AS clmn2_,
    t0.store_name AS clmn3_,
    t0.total_sales AS clmn4_
  FROM (
    SELECT
      *
    FROM
      `your-project.your-dataset.sales_summary_problematic` ) AS t0 )
GROUP BY
  clmn0_,
  clmn1_,
  clmn2_,
  clmn3_
ORDER BY
  t0_qt_xxxxxxxxid DESC
LIMIT
  2000001;

この5件のクエリは、ブラウザのリロードにより、レポートを再表示するたびに、同じスキャン量で同じ数のクエリが発行されました。 このように、ビュー内で CURRENT_DATE() を使用し、かつそのビューをプルダウンリストのコントロールで参照すると、一定期間内に同一ユーザーによる同一フィルタ条件の操作であっても、キャッシュが利用されないクエリが発行され、スキャン量が増加します。

対策 1-1 の適用

ビュー定義から CURRENT_DATE() のような非決定性関数を削除してみます。

CREATE OR REPLACE VIEW `your-project.your-dataset.sales_summary_cached` AS
SELECT
    FORMAT_DATE('%Y-%m', f.sales_date) AS sales_month,
    sm.store_name,
    sm.region,
    pm.category,
    COUNT(DISTINCT f.product_id) AS product_count,
    SUM(f.amount) AS total_sales
FROM
    `your-project.your-dataset.sales_fact` f
JOIN
    `your-project.your-dataset.product_master` pm ON f.product_id = pm.product_id
JOIN
    `your-project.your-dataset.store_master` sm ON f.store_id = sm.store_id
-- WHERE
--     f.sales_date BETWEEN DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 YEAR) AND CURRENT_DATE('Asia/Tokyo')
GROUP BY
    sales_month, sm.store_name, sm.region, pm.category;

このビュー (sales_summary_cached) を使用すると、同じレポート表示条件（ブラウザのリロード）でのクエリ結果はキャッシュされました。 ただし、ビュー側では全期間のデータを集計しているため、Looker Studio 側でフィルタを適用するまでのクエリは全期間のデータに対して行われ、表テーブルの初回スキャン量は約 65 MB でした。

対策 1-2 の適用

BigQuery のテーブル関数を使用してみます。

CREATE OR REPLACE TABLE FUNCTION `your-project.your-dataset.sales_summary_tf`(
    target_date_from DATE,
    target_date_to DATE
) AS (
    SELECT
        FORMAT_DATE('%Y-%m', f.sales_date) AS sales_month,
        sm.store_name,
        sm.region,
        pm.category,
        COUNT(DISTINCT f.product_id) AS product_count,
        SUM(f.amount) AS total_sales
    FROM
        `your-project.your-dataset.sales_fact` f
    JOIN
        `your-project.your-dataset.product_master` pm ON f.product_id = pm.product_id
    JOIN
        `your-project.your-dataset.store_master` sm ON f.store_id = sm.store_id
    WHERE
    -- パラメータで期間を指定することでパーティションのプルーニングが適用されます
        f.sales_date BETWEEN target_date_from AND target_date_to
    GROUP BY
        sales_month, sm.store_name, sm.region, pm.category
);

Looker Studio でこのテーブル関数をデータソースとして使用するには、データソースの接続設定で「カスタムクエリ」を選択し、以下のように記述します。

SELECT *
FROM `your-project.your-dataset.sales_summary_tf`(PARSE_DATE('%Y%m%d', @DS_START_DATE), PARSE_DATE('%Y%m%d', @DS_END_DATE))

Looker Studio の標準の期間パラメータである @DS_START_DATE と @DS_END_DATE をテーブル関数の引数として渡します。 これにより、Looker Studio の期間コントロールで指定された期間がテーブル関数に渡され、sales_fact テーブルの sales_date パーティションを効果的に利用できます（パーティションのプルーニングが適用されます）。

• 参考 : 日付パラメータを使用して、基になるクエリに期間を渡す

Looker Studio の「年月」のプルダウンリストのコントロールを「期間設定」のコントロールに変えます。

このレポートを表示すると、1回の表示操作で、BigQuery のジョブ履歴には以下のようなクエリが3件記録されました。（「期間設定」のコントロールに対してのクエリは発行されていませんでした。）

SELECT
  clmn0_
FROM (
  SELECT
    t0.region AS clmn0_
  FROM (
    SELECT
      *
    FROM
      `your-project.your-dataset.sales_summary_tf`(PARSE_DATE('%Y%m%d', @DS_START_DATE),
        PARSE_DATE('%Y%m%d', @DS_END_DATE)) ) AS t0 )
GROUP BY
  clmn0_
ORDER BY
  clmn0_ DESC
LIMIT
  2000001;

さらに、1回の表示操作で、BigQuery のジョブ履歴には以下のようなクエリが1件記録されました。

SELECT
  clmn0_,
  SUM(clmn4_) AS t0_qt_xxxxxxxxid,
  clmn1_,
  clmn2_,
  clmn3_
FROM (
  SELECT
    t0.category AS clmn0_,
    t0.region AS clmn1_,
    t0.sales_month AS clmn2_,
    t0.store_name AS clmn3_,
    t0.total_sales AS clmn4_
  FROM (
    SELECT
      *
    FROM
      `your-project.your-dataset.sales_summary_tf`(PARSE_DATE('%Y%m%d', @DS_START_DATE),
        PARSE_DATE('%Y%m%d', @DS_END_DATE)) ) AS t0 )
GROUP BY
  clmn0_,
  clmn1_,
  clmn2_,
  clmn3_
ORDER BY
  t0_qt_xxxxxxxxid DESC
LIMIT
  2000001;

そしてこの4件のクエリは、対策1-1 と同じ条件（同じレポート表示条件（ブラウザのリロード）であっても、2 回目以降はスキャン量が0Bでした。

対策2 の適用

プルダウンリストのコントロール用のデータソースを作成し、Looker Studio のクロスフィルタリング機能を使用してみます。

• プルダウンリストのコントロール用データソースの追加

  • 「地域」「店舗名」フィルタ用データソースは your-project.your-dataset.store_master テーブルを参照します。
  • 「商品カテゴリ」フィルタ用データソースは your-project.your-dataset.product_master テーブルを参照します。

• レポート設定

  • Looker Studio レポートで、メインの表は対策 1-2 で作成したテーブル関数 (sales_summary_tf) を参照するデータソースを使用します。
  • 「地域」「店舗名」プルダウンリストのコントロールは、上記で作成した store_master を参照するデータソースを使用するように変更します。
  • 同様に「商品カテゴリ」プルダウンリストのコントロールも product_master を参照するデータソースを使用するように変更します。
  • メインの表を選択し、[表のプロパティ] > 下部にある [グラフ インタラクション] セクション >「クロスフィルタリング」のトグルがオンとなっていることを確認します。

これにより、ユーザーがプルダウンリストのコントロールで値を選択すると、クロスフィルタリング機能によって、その選択がメインのグラフ（テーブル関数を参照しているデータソース）に適用され、その際に必要なクエリが発行されます。

このレポートを表示すると、1回の表示操作で、BigQuery のジョブ履歴には以下のようなクエリが1件記録されました。 このクエリのスキャン量（課金されるバイト数）は、10 MB でした。

SELECT
  clmn0_
FROM (
  SELECT
    t0.category AS clmn0_
  FROM
    `your-project.your-dataset.product_master` AS t0 )
GROUP BY
  clmn0_
ORDER BY
  clmn0_ DESC
LIMIT
  2000001;

さらに、BigQuery のジョブ履歴には以下のようなクエリが2件記録されました。 このクエリのスキャン量（課金されるバイト数）は、1件ごとに、10 MB でした。

SELECT
  clmn0_
FROM (
  SELECT
    t0.region AS clmn0_ --region, store_name ごとに1件ずつ
  FROM
    `your-project.your-dataset.store_master` AS t0 )
GROUP BY
  clmn0_
ORDER BY
  clmn0_ DESC
LIMIT
  2000001;

BigQuery の費用と追加のコスト最適化策

BigQuery のオンデマンド料金モデルは、クエリによってスキャンされたバイト数に基づいて課金されますが、留意点があります。

BigQuery のオンデマンド料金には、最小データ処理量という概念があります。公式ドキュメントには、最小データ処理容量について次のように記載されています。

料金は MB 単位のデータ処理容量（端数は切り上げ）で決まります。クエリが参照するテーブルあたりのデータ最小処理容量は 10 MB、クエリあたりのデータ最小処理容量は 10 MB とします。

• 参考 : オンデマンド コンピューティングの料金

つまり、非常に小さなテーブルを参照するクエリや、結果データが小さいクエリであっても、最低 10 MB 分の料金が発生します。 したがって、クエリのスキャン量を減らすことと同時に、不要なクエリの発行回数を削減することがコスト最適化につながります。

INFORMATION_SCHEMA.JOBS を使用したクエリ調査方法や、その他のコスト最適化策については、以下の記事も参照してください。

blog.g-gen.co.jp

blog.g-gen.co.jp

佐々木 愛美 (min) (記事一覧)

クラウドソリューション部 データアナリティクス課。2024年7月 G-gen にジョイン。G-gen 最南端、沖縄県在住。最近覚えた島言葉は、「マヤー（猫）」。

関連記事

• Looker Studioでフィルタを適用したレポートを共有する方法

  G-genの min です。Looker Studio には、フィルタや…
• Looker Studioの「レポートの公開設定」でバージョンを管理する方法

  G-gen の min です。Looker Studio のレポートの公開…
• Looker Studioで閲覧者メールアドレスに基づき表示データを動的に制限

  G-gen の min です。Looker Studio レポートに表示す…

2025-07-02 09:00

問い合わせ

開催予定・受付中のセミナー一覧はこちら

G-genではGoogle Cloud / Google Workspace の活用に役立つセミナーを開催しております。気になるテーマがありましたら、ぜひご参加ください。

Google Cloud請求代行サービス

Google Cloud (旧称GCP) が5%オフで利用可能 + 無償の技術サポート窓口。

既に Google Cloud をご利用中の方でも切り替え可能です。

採用ページ

エンジニア・営業ともに、積極採用中！