BigQueryで分析関数を使用して自己結合を回避する

テンペスト

概要

サイズの大きいテーブルを自己結合は一般的にSQLのアンチパターンになる。
そのため、自己結合を回避するための手段としてBigQuery(以下すべてBigQuery上での話)の分析関数を使用する。

背景

業務でユーザに公開しているVIEWを参照するSQLを実行するとそこそこ時間(12分)がかかると問い合わせがあった。
VIEWをSELECT * したテーブルを作成して、VIEWの代わりにそのテーブルを参照してSQLを実行したところ2-3分で実行できるため、VIEWをテーブルにしてもらえないかという依頼に発展した。(このVIEWの参照元テーブルの更新頻度は1日1回のため、元テーブルの更新に合わせて新規のテーブルも更新する)

方針

VIEWのテーブル化はしたくない

多少なりとも開発工数がかかるため、VIEWのテーブル化は最後の手段として検討することを前提とした。

VIEWの実体化(マテリアライズドビュー)は可能か

自己結合はマテリアライズドビューでは使用できず、この後の代替手段である分析関数を使用することもできないため今回は不可とした。

VIEWを参照するSQLの改善は可能か

本題と逸れるので詳しくは触れないが、ユーザがSQL実行時にORDER BYを使用していたため、それを使用しないことは可能かを確認した。(使用しない場合は5分程度で実行が完了した。)

VIEWの定義自体を効率化することは可能か

今回の本題。VIEWの定義内でサイズの大きいテーブル(約730GB)を自己結合していたため、分析関数を使用すれば処理の改善が期待できると判断した。

検証

VIEWの定義の確認

もともとのVIEWの定義は参照元テーブルのキー項目に対して最新の日付のデータのみを取得するような定義。
SQLで書くと以下の通り。

SELECT
    org.KEY1,
    org.KEY2,
    org.COL1,
    org.COL2,
    ・
    ・
    ・
    org.DATE
FROM TABLE org,
(
    SELECT
        KEY1,
        KEY2,
        MAX(DATE) AS MAX_DATE
    FROM org
    GROUP BY KEY1, KEY2
) recent
WHERE org.KEY1 = recent.KEY1
AND org.KEY2 = recent.KEY2
AND org.DATE = recent.MAX_DATE

分析関数を使用して書き換える

BigQueryでは分析関数を使用することができるため、そちらを使用して書き換える。

SQLで書くと以下の通り。

SELECT
    org.KEY1,
    org.KEY2,
    org.COL1,
    org.COL2,
    ・
    ・
    ・
    org.DATE
FROM TABLE org,
-- QUALIFY句を使用する場合、WHERE句・GROUP BY句が必要なので今回はTRUEを入れる。
WHERE TRUE
QUALIFY org.DATE = MAX(org.DATE) OVER (PARTITION BY KEY1, KEY2)

クエリ自体もすっきりし、分析関数になれていればわかりやすい表現になるため、性能面以外でも推奨したい。

上記の2つのVIEWを用いて、ユーザのクエリを実行する

旧定義のVIEWを使用したクエリ

新定義のVIEWを使用したクエリ

実行タイミングによって経過時間やスロット時間はかなりぶれる(検証したタイミングはスロット使用率が高いタイミング(注：業務環境のBigQueryはslot定額制)だったため少し反省)が、新定義のVIEWを使用した場合の方が経過時間は3分短く、消費したslot時間も半分ほどに収まっている。
また、シャッフルされたバイト数というのが今回処理時間の改善に大きく寄与しているのだが、自己結合を回避して分析関数を使用することで、クエリにより生成されるバイト数を削減することができていることが確認できた。

結論

上記検証の結果から、分析関数を使用することで自己結合を回避するとクエリのパフォーマンスの改善につながることが確認できた。
その理由として、自己結合をする場合よりも分析関数を使用する場合の方が、クエリによって生成されるデータのサイズが小さくなるためと確認できた。
以上から、参照元のテーブルサイズが大きいほど、より効果が大きいと言える。

この記事が気に入ったらサポートをしてみませんか？