1. はじめに
株式会社 Hogetic Lab の兼子と申します。
皆さん、業務で BigQuery を使用してクエリを書くことがあると思います。 BigQuery を使用する際、同じ結果を得るためのクエリは複数の書き方が存在します。しかし、クエリの書き方によっては、処理の途中経過やパフォーマンスに大きな差が生じることがあります。クエリの処理方法を意識することで、より効率的で処理負荷の少ないクエリを作成することが可能です。
本記事では、全テーブルの洗い替えを行うために作成したクエリがタイムアウトエラーで失敗する事例をもとに、効率的なクエリの書き方とその重要性について説明します。
2. 事例の説明
今回取り上げるクエリは、下記のような INNER JOIN 句や LEFT JOIN 句を使用したものでした（クエリは例となります）。
SELECT DISTINCT
i.columnA,
i.columnB,
o.columnC,
o.columnD,
a.columnE,
a.columnF,
i.columnG
FROM
`datasetA.tableA` AS i
INNER JOIN
`datasetA.tableB` o
ON
i.columnA = o.columnA
LEFT JOIN
`datasetA.tableC` a
ON
i.columnB = a.columnB
特に、INNER JOIN 句で結合しているテーブル（ tableA 、 tableB ）はレコード数が非常に多く、巨大なデータセットとなっていました。
| テーブル名 | レコード数 |
|---|---|
| tableA | 約 150 億件 |
| tableB | 約 200 万件 |
その結果、クエリを実行すると処理に時間がかかり、最終的にタイムアウトエラーが発生してしまいました。
Operation timed out after 6.0 hours. Consider reducing the amount of work performed by your operation so that it can complete within this limit.;
3. 今回のクエリの改善ポイント
今回のクエリの改善ポイントは以下の2点です。
1. JOIN 句は処理負荷が高いため、結合前に不要なデータを削減する
JOIN 句はデータベースにおける処理負荷の高い操作です。そのため、なるべくレコード数が少ない状態で JOIN を行うことで、処理負荷を下げ、クエリの実行時間を短縮できます。一般的な SQL の処理順序では、 JOIN 句が SELECT 句より先に実行されます。そのため、 JOIN 句の前に不要なデータを削減しておくことが重要です（ BigQuery の公式ドキュメントではクエリの処理順序は明言されていませんが、クエリのパフォーマンスを最適化する際にはこのアプローチは有効です）。
2. SELECT DISTINCT は JOIN 句を実行する前に行っても、結果は変わらない
クエリ内で DISTINCT を JOIN 後に適用しても、 JOIN 前に適用しても、得られる結果は同じです。したがって、先に SELECT DISTINCT でデータ量を減らしてから JOIN を行うことで、クエリの処理負荷を大幅に軽減できます。これは、巨大なテーブルを扱う場合に特に有効であり、クエリ全体の効率を向上させることが可能です。
注意点:
データの特性によっては、
SELECT DISTINCTを JOIN 句前に適用することで結果が変わる場合があります。そのため、後述するデータの比較クエリを実行し、改善後のクエリが期待通りの結果を返すことを確認する事をおすすめします。
- 参考記事: BigQuery 特集: 結合データ、繰り返しおよびネストされたデータの処理
- 参考記事: SQLのクエリ内実行順序を理解する
- 参考資料: クエリ計算を最適化する - JOINを使用する前にデータを削減する
4. 改善後のクエリ
前節の内容を踏まえ、改善したクエリが下記になります。巨大なテーブルに対して、事前に SELECT DISTINCT を適用して重複を排除し、その後 JOIN 句でテーブルの結合を行っています。
WITH distinct_tableA AS
(
SELECT DISTINCT
columnA,
columnB,
columnG
FROM
`datasetA.tableA`
),
distinct_tableB AS
(
SELECT DISTINCT
columnA,
columnC,
columnD
FROM
`datasetA.tableB`
)
SELECT DISTINCT
i.columnA,
i.columnB,
o.columnC,
o.columnD,
a.columnE,
a.columnF,
i.columnG
FROM
distinct_tableA AS i
INNER JOIN
distinct_tableB AS o
ON
i.columnA = o.columnA
LEFT JOIN
`datasetA.tableC` AS a
ON
i.columnB = a.columnB
ポイント:
-
tableAとtableBに対して、SELECT DISTINCTを用いて重複行を削除し、中間テーブルとしてdistinct_tableAとdistinct_tableBを作成しています
→ これにより、 JOIN 時のデータ量が削減され、クエリのパフォーマンスが向上します
なお、改善したクエリが改善前のクエリと同じ結果を返すかどうかを確認するために、以下のクエリを実行すると良いでしょう。
- 参考資料: BigQueryでテーブルの差分を確認する
(
SELECT
*
FROM
before_improvement
EXCEPT DISTINCT
SELECT
*
FROM
after_improvement
)
UNION ALL
( SELECT
*
FROM
after_improvement
EXCEPT DISTINCT
SELECT
*
FROM
before_improvement
)
このクエリを実行することで、改善前と改善後のクエリ結果の差分を確認できます。結果が空であれば、両者の結果が一致していることを意味します。結果にレコードが存在する場合、それらが差分となりますので、データの整合性を確認し、必要に応じてクエリを修正してください。
5. まとめ
今回紹介した改善ポイントを踏まえることで、クエリのパフォーマンスを最適化し、タイムアウトエラーを防ぐことができました。特に大規模なデータセットを扱う場合、事前にデータを絞り込むことで、処理効率を大幅に向上させることができます。
今回の事例では、上記のクエリに修正する事で 10 分程度でクエリを実行する事ができました。
一般的な SQL の知識や BigQuery の公式ドキュメントにあるベストプラクティスを活用することで、効率的なデータ処理が可能となります。
Discussion