Zenn
👏

BigQuery で WITH RECURSIVE 句が GA になりました！

2023/04/18に公開
GCP
SQL
Google Cloud
BigQuery
tech

はじめに

こんにちは、クラウドエース データ ML ディビジョン所属の池上です。
クラウドエースの IT エンジニアリングを担うシステム開発部の中で、特にデータ基盤構築・分析基盤構築からデータ分析までを含む一貫したデータ課題の解決を専門とするのがデータ ML ディビジョンになります。

データ ML ディビジョンでは活動の一環として、毎週 Google Cloud の新規リリースを調査・発表し、データ領域のプロダクトのキャッチアップをしています。その中でも重要と考えるリリースを本ページ含め記事として公開しています。

今回紹介するリリースは、BigQuery の「WITH RECURSIVE 句」が GA (Generally Available; 一般提供) になったことについてです。
WITH RECURSIVE 句を用いることにより、WITH 句で再帰処理を行えるようになりました。そのため、複数のテーブルから取得した結果を繰り返し (再帰的に) 処理したい場合、結合したい処理内容 (テーブル数等) が可変である場合の処理に対応できるようになりました。

BigQuery の概要

BigQuery は、サーバーレス アーキテクチャにより、SQL クエリを使用して、インフラストラクチャ管理なしで大きな課題に対応できるフルマネージドのエンタープライズ データ ウェアハウスです。

BigQuery の「WITH RECURSIVE 句」の説明

今回ご紹介するリリースは、2023年3月2日付に一般提供 (GA) となった BigQuery における WITH RECURSIVE 句というクエリについてになります。
URL：BigQuery release notes

リリース内容

WITH RECURSIVE 句では、WITH 句で再帰処理を行うことが可能です。
以下で、より詳しく説明していきたいと思います。

通常の共通テーブル式 (CTE) と再帰共通テーブル式

BigQuery の Google SQL では、WITH 句を使用して、1つ以上の共通テーブル式 (Common Table Expressions; CTEs) を含むクエリを作成することができます。CTEは、非再帰的、再帰的になります。WITH句にRECURSIVEキーワードを含めることで、再帰的なCTEを作成することができます。

再帰CTEは、自身のCTE、前のCTE、または後続のCTEを参照することができます。一方、非再帰CTEは、以前のCTEのみを参照することができて、自身のCTEを参照することはできません。再帰CTEは、新しい結果が見つかるまで継続的に実行されますが、非再帰CTEは1回だけ実行されます。そのため、階層的なデータやグラフデータのクエリでは、再帰CTEが一般的に使用されます。

再帰 CTE の例

以下は、WITH 句で RECURSIVE を使ったクエリの例です。この例では、iterationの回数を再帰的にカウントアップするクエリを実行しています。

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1)
    UNION ALL
    SELECT iteration + 1 FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 1         |
 | 2         |
 | 2         |
 | 3         |
 | 3         |
 *-----------*/

このクエリでは、WITH 句で RECURSIVE を使用して、CTE_1 という名前の一時的なテーブルを作成しています。最初の SELECT ステートメントは、iterationという変数に 1 という数値を定義しています。UNION ALL により、2 番目の SELECT ステートメントが実行され、最初の SELECT ステートメントで取得した iteration に 1 を加算します。このプロセスは、WHERE ステートメントで iteration が 3 になるまで続きます。

続いて WITH RECURSIVE を使ってフィボナッチ数を求める例です。

WITH RECURSIVE fibonacci AS (
  SELECT 1 AS n, 0 AS value1, 1 AS value2
  UNION ALL
  SELECT n + 1, value2, value1 + value2 FROM fibonacci WHERE n < 10
)
SELECT value2
FROM fibonacci
ORDER BY 1 ASC;

/*--------*
 | value2 |
 +--------+
 | 1      |
 | 1      |
 | 2      |
 | 3      |
 | 5      |
 | 13     |
 | 21     |
 | 34     |
 | 55     |
 *--------*/

このように、RECURSIVE を使用することで、再帰的にデータを取得することができます。

反復処理の制限

再帰 CTE を使用する際には、終了条件を満たさないまま再帰用語が継続的に実行されると、無限再帰が発生する可能性があります。無限再帰を終了するために、クエリエンジンは、再帰 CTE ごとに反復処理の制限を適用します。BigQuery の反復処理の上限は500回です。再帰 CTE が反復処理の最大数に達すると、CTE の実行は中止され、エラーが発生します。

この制限がある理由は、再帰 CTE の計算が高コストになるためです。大量の反復処理で CTE を実行すると、多数のシステムリソースが消費され、完了までにはるかに時間がかかる可能性があります。

ただし、Google Cloudのカスタマーケアに連絡することで再帰的な上限を引き上げることも可能です。

詳細については、以下ドキュメントをご参照ください。
Google Cloudドキュメント：再帰 CTE を操作する

ユースケース

WITH RECURSIVE句のユースケース例を以下に示します。

  • リンクされたグラフの探索
    • ホップの最大数がわからない状態で、特定の開始ノードから到達可能なすべてのノードを探索する場合、再帰CTEを使用します。再帰CTEは開始ノードから始まり、各ステップにて、前のステップで探索したすべてのノードから到達できる新しい未知のノードが計算されます。新しいノードが見つからない場合、クエリは終了します。
  • 階層的なデータの探索
    • 例えば、社員の上司と部下の関係を表すテーブルがある場合、ある社員の部下全員の名前と彼らの部下の数を取得するクエリを実行することができます。
  • テーブルの分割
    • 大きなテーブルを小さなテーブルに分割することができます。例えば、年度別の売上データを分割して、各年度の売上を個別のテーブルに格納することができます。

まとめ

今回の記事では、以下の内容について紹介しました。

  • BigQuery で、WITH RECURSIVE 句の一般提供が開始。
  • WITH RECURSIVE 句によって再帰的なクエリの実行が可能になった。

WITH RECURSIVE 句が一般提供開始になったことで、WITH 句で再帰処理が可能となった反面、再帰 CTE は計算コストが高くなることや、無限再帰が発生しないよう BigQuery の反復処理の上限のデフォルトは 500 回であることも念頭に置く必要があります。

関連記事

Discussion