LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@huntersai(プロフィール 商品検索　中野哲平)

BigQueryマスターへの道: パフォーマンスと費用対効果を高める20のTips

Posted at

GoogleのBigQueryはクラウドネイティブなデータウェアハウスとして、多くの企業がビッグデータ分析の基盤として活用しています。しかし、その真の力を引き出し、コストを最適化するには、単なる基本操作を超えたテクニックが必要です。このブログでは、BigQueryを日常的に使用するデータエンジニアやアナリスト向けに、実践的なTipsをご紹介します。

クエリパフォーマンスの最適化

1. PARTITIONを活用する

テーブルをパーティション化することで、クエリが処理するデータ量を大幅に削減できます。特に時系列データを扱う場合は必須のテクニックです。

-- 日付でパーティション化されたテーブルの作成
CREATE TABLE mydataset.partitioned_table
(
  timestamp TIMESTAMP,
  user_id STRING,
  event_name STRING,
  value FLOAT64
)
PARTITION BY DATE(timestamp);

-- 特定のパーティションだけをクエリ
SELECT * FROM mydataset.partitioned_table
WHERE DATE(timestamp) BETWEEN '2023-01-01' AND '2023-01-07';

パーティションの状態を確認するには：

SELECT
  table_name,
  partition_id,
  total_rows,
  total_logical_bytes / POWER(1024, 3) AS size_gb
FROM
  `mydataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  table_name = 'partitioned_table'
ORDER BY
  partition_id DESC;

2. CLUSTERINGで関連データをグループ化

頻繁にフィルタリングや集計に使用される列でテーブルをクラスタリングすると、より効率的なクエリが可能になります。

-- パーティション化とクラスタリングを組み合わせたテーブル
CREATE TABLE mydataset.partitioned_clustered_table
(
  timestamp TIMESTAMP,
  country STRING,
  device_type STRING,
  user_id STRING,
  revenue FLOAT64
)
PARTITION BY DATE(timestamp)
CLUSTER BY country, device_type;

クラスタリングは最大4つのカラムまで指定でき、指定順序が重要です（最初に指定した列ほど影響が大きい）。

3. SELECT *を避ける

必要なカラムだけを選択することで、処理するデータ量とコストを削減できます。

-- 避けるべき例
SELECT * FROM `bigquery-public-data.samples.wikipedia`;

-- 良い例
SELECT title, language, wp_namespace
FROM `bigquery-public-data.samples.wikipedia`
WHERE wp_namespace = 0
LIMIT 1000;

4. 効率的なJOINの書き方

大きなテーブル同士のJOINは処理コストが高くなります。可能であれば、小さいテーブルを先に記述し、必要なデータのみをフィルタリングしましょう。

-- より効率的なJOIN
SELECT
  a.user_id,
  a.event_name,
  b.user_name
FROM
  -- 先に条件でフィルタリングして結果を小さくする
  (SELECT user_id, event_name 
   FROM `mydataset.events` 
   WHERE DATE(timestamp) = '2023-04-01') a
JOIN
  `mydataset.users` b
ON
  a.user_id = b.user_id;

5. 近似集計関数を使用する

正確な値が必須でない場合は、近似関数を使ってパフォーマンスを向上させましょう。

-- 正確なカウント（処理が重い）
SELECT COUNT(DISTINCT user_id) AS exact_count
FROM `mydataset.large_events_table`;

-- 近似カウント（高速だが若干の誤差あり）
SELECT APPROX_COUNT_DISTINCT(user_id) AS approximate_count
FROM `mydataset.large_events_table`;

近似関数はHyperLogLog++アルゴリズムを使用し、通常は誤差率が0.5%程度です。

コスト最適化のテクニック

6. クエリ検証にDRY RUNを使用

実際にクエリを実行する前に、どれだけのデータが処理されるかを確認できます。

-- クエリを実行せずに処理バイト数を確認
#dry_run
SELECT *
FROM `mydataset.huge_table`
WHERE DATE(timestamp) BETWEEN '2022-01-01' AND '2023-01-01';

または、APIやbqコマンドラインツールを使用して：

bq query --use_legacy_sql=false --dry_run 'SELECT * FROM `mydataset.huge_table`'

7. マテリアライズドビューの活用

頻繁に実行される複雑なクエリはマテリアライズドビューに変換することで、処理時間とコストを削減できます。

-- マテリアライズドビューの作成
CREATE MATERIALIZED VIEW mydataset.mv_daily_stats
AS
SELECT
  DATE(timestamp) AS day,
  country,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS user_count,
  SUM(revenue) AS total_revenue
FROM
  `mydataset.events`
GROUP BY
  day, country;

マテリアライズドビューは増分更新が可能で、元のテーブルが更新されると自動的に更新されます。

8. ストレージ最適化のためのTABLE EXPIRATIONの設定

一時的なデータや分析結果には有効期限を設定しましょう。

-- 作成から7日後に自動削除されるテーブル
CREATE TABLE mydataset.temp_analysis_results
(
  analysis_date DATE,
  metric_name STRING,
  metric_value FLOAT64
)
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
);

9. パーティションレベルでのTTL設定

時系列データの古いパーティションを自動的に削除するには：

-- パーティションの有効期限を設定（90日後）
CREATE TABLE mydataset.events_with_ttl
(
  timestamp TIMESTAMP,
  user_id STRING,
  event_name STRING
)
PARTITION BY DATE(timestamp)
OPTIONS(
  partition_expiration_days=90
);

10. キャッシュを賢く使う

同じクエリを30分以内に再実行する場合、キャッシュが使用され料金は発生しませんが、以下の点に注意が必要です：

  • テーブルが更新されるとキャッシュは無効になります
  • --no_cacheフラグまたはCREATE TABLE ... AS SELECTを使用するとキャッシュはバイパスされます
  • プロジェクト間でキャッシュは共有されません

高度なSQLテクニック

11. ARRAY_AGGとSTRUCTで結果を効率的に構造化

複数行を1行にまとめることで、結果セットを効率的に構造化できます。

-- ユーザーごとのイベント履歴を配列として取得
SELECT
  user_id,
  ARRAY_AGG(
    STRUCT(
      timestamp,
      event_name,
      device_type,
      revenue
    )
    ORDER BY timestamp
  ) AS event_history
FROM
  `mydataset.events`
WHERE
  DATE(timestamp) = '2023-04-01'
GROUP BY
  user_id;

この方法はクライアント側での後処理が減り、特にネストされたJSONデータの生成に便利です。

12. ウィンドウ関数を活用した傾向分析

時系列データの傾向を分析するにはウィンドウ関数が強力です。

-- 7日間の移動平均を計算
SELECT
  date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7day_ma
FROM (
  SELECT
    DATE(timestamp) AS date,
    SUM(revenue) AS daily_revenue
  FROM
    `mydataset.sales`
  GROUP BY
    date
  ORDER BY
    date
);

13. WITH句で複雑なクエリを整理

複雑なクエリは共通テーブル式（CTE）を使って整理すると読みやすく、メンテナンスしやすくなります。

-- ステップごとに処理を分解して可読性を高める
WITH daily_metrics AS (
  SELECT
    DATE(timestamp) AS date,
    country,
    COUNT(DISTINCT user_id) AS users,
    SUM(revenue) AS revenue
  FROM
    `mydataset.events`
  WHERE
    DATE(timestamp) BETWEEN '2023-01-01' AND '2023-03-31'
  GROUP BY
    date, country
),
country_rankings AS (
  SELECT
    date,
    country,
    revenue,
    RANK() OVER (PARTITION BY date ORDER BY revenue DESC) AS revenue_rank
  FROM
    daily_metrics
)
SELECT
  date,
  country,
  revenue
FROM
  country_rankings
WHERE
  revenue_rank <= 5
ORDER BY
  date, revenue_rank;

14. 日付・時間関数を効果的に使う

BigQueryには便利な日付・時間関数が多数あります。

-- 様々な時間単位での集計
SELECT
  DATE_TRUNC(timestamp, MONTH) AS month,
  DATE_TRUNC(timestamp, WEEK) AS week,
  DATE_TRUNC(timestamp, DAY) AS day,
  EXTRACT(HOUR FROM timestamp) AS hour,
  COUNT(*) AS event_count
FROM
  `mydataset.events`
WHERE
  timestamp BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY
  month, week, day, hour
ORDER BY
  month, week, day, hour;

また、タイムゾーン変換も簡単に行えます：

-- タイムゾーン変換
SELECT
  timestamp AS utc_time,
  DATETIME(timestamp, 'Asia/Tokyo') AS jst_time,
  DATETIME(timestamp, 'America/Los_Angeles') AS pst_time
FROM
  `mydataset.events`
LIMIT 10;

15. UDFを活用して処理をカプセル化

JavaScript UDF（ユーザー定義関数）を使うと、複雑なロジックをカプセル化できます。

-- 郵便番号から地域コードを抽出するUDF
CREATE TEMPORARY FUNCTION extractRegionCode(zipcode STRING)
RETURNS STRING
LANGUAGE js
AS """
  if (!zipcode || zipcode.length < 3) return null;
  return zipcode.substring(0, 3);
""";

-- UDFを使用したクエリ
SELECT
  user_id,
  address_zipcode,
  extractRegionCode(address_zipcode) AS region_code
FROM
  `mydataset.users`;

チーム開発とベストプラクティス

16. テーブル命名規則を統一する

一貫性のある命名規則を採用することで、データ管理が容易になります。

[環境]_[データドメイン]_[エンティティ]_[バリエーション]

例：

  • prod_sales_transactions_daily
  • dev_marketing_campaigns_raw
  • stg_users_profile_enriched

17. テーブル説明とスキーマ注釈を活用

テーブルとカラムにはわかりやすい説明を追加しましょう。

-- テーブルとカラムの説明を追加
CREATE TABLE mydataset.user_activity
(
  user_id STRING OPTIONS(description="ユーザーの一意識別子"),
  event_date DATE OPTIONS(description="イベントが発生した日（UTC）"),
  event_type STRING OPTIONS(description="イベントの種類（例：click, purchase, signup）"),
  session_id STRING OPTIONS(description="セッションの一意識別子")
)
OPTIONS(
  description="ユーザーアクティビティの追跡データ。1日1回ETLパイプラインで更新"
);

これにより、チームメンバーがデータカタログでテーブルを検索したときに詳細情報を確認できます。

18. クエリ結果の可視化をLooker Studioと連携

分析結果を可視化するためにBigQueryとLooker Studio（旧Data Studio）を直接連携させると便利です。

-- Looker Studioに最適化されたサマリーテーブル
CREATE OR REPLACE TABLE mydataset.dashboard_daily_metrics AS
SELECT
  DATE(timestamp) AS date,
  device_category,
  country,
  COUNT(DISTINCT user_id) AS unique_users,
  COUNT(*) AS total_events,
  SUM(IF(event_name = 'purchase', revenue, 0)) AS revenue
FROM
  `mydataset.events`
WHERE
  DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY
  date, device_category, country;

このテーブルをLooker Studioのデータソースとして設定すれば、リアルタイムに近いダッシュボードが構築できます。

19. 再利用可能なSQLモジュールの作成

共通のロジックはテーブル関数として定義することで再利用できます。

-- ユーザーコホート分析用のテーブル関数
CREATE OR REPLACE TABLE FUNCTION mydataset.fn_user_cohort(start_date DATE, end_date DATE)
AS
WITH first_purchase AS (
  SELECT
    user_id,
    DATE(MIN(timestamp)) AS cohort_date
  FROM
    `mydataset.purchases`
  GROUP BY
    user_id
),
purchase_by_month AS (
  SELECT
    user_id,
    DATE_TRUNC(DATE(timestamp), MONTH) AS purchase_month,
    SUM(revenue) AS revenue
  FROM
    `mydataset.purchases`
  WHERE
    DATE(timestamp) BETWEEN start_date AND end_date
  GROUP BY
    user_id, purchase_month
)
SELECT
  fp.cohort_date,
  pb.purchase_month,
  DATE_DIFF(pb.purchase_month, DATE_TRUNC(fp.cohort_date, MONTH), MONTH) AS month_number,
  COUNT(DISTINCT pb.user_id) AS user_count,
  SUM(pb.revenue) AS total_revenue
FROM
  first_purchase fp
JOIN
  purchase_by_month pb
ON
  fp.user_id = pb.user_id
GROUP BY
  cohort_date, purchase_month, month_number
ORDER BY
  cohort_date, month_number;

-- 使用例
SELECT * FROM mydataset.fn_user_cohort('2022-01-01', '2023-03-31');

20. ワークフローの自動化とスケジューリング

定期的なデータ処理はBigQuery Scheduled Queriesを使って自動化できます。

-- 毎日実行されるスケジュールクエリの例
-- GUIからスケジュール設定も可能
CREATE OR REPLACE TABLE mydataset.daily_summary
AS
SELECT
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS report_date,
  country,
  platform,
  COUNT(DISTINCT user_id) AS unique_users,
  SUM(revenue) AS total_revenue
FROM
  `mydataset.events`
WHERE
  DATE(timestamp) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
  country, platform;

まとめ

BigQueryの真の力を引き出すには、基本的なSQLの知識だけでなく、パフォーマンスとコスト最適化のためのベストプラクティスを理解することが重要です。今回ご紹介した20のTipsを活用して、より効率的なデータ分析環境を構築していただければ幸いです。

日々のクエリ作成から大規模データパイプラインの設計まで、これらのテクニックを応用することで、BigQueryのパフォーマンスを最大限に引き出し、コストも削減できるはずです。

このブログはBigQueryの2023年4月時点の機能に基づいています。Google Cloudのサービスは常に進化しているため、最新の機能や制限については公式ドキュメントを参照してください。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?