【アップデート】BigQueryで再帰クエリのRECURSIVEがGAになりました
2023.03.04
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
初めに
データアナリティクス事業本部でGoogle Cloudのデータエンジニアをしています、はんざわです。
2023年3月2日にBigQueryのWITH RECURSIVEがGAになりました。
https://cloud.google.com/bigquery/docs/release-notes#March_02_2023
WITH RECURSIVEを使うことでWITH句で再帰することが可能になりました。
試してみる
WITH RECURSIVE T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) ) SELECT n FROM T1 +---+ | n | +---+ | 2 | | 1 | | 3 | +---+
上のクエリは公式ドキュメントに記載されている再帰クエリの例です。
この例では初期値1のnが3になるまでnに1ずつ加算されます。
現在、再帰回数は500回が上限になっており、それを超える場合はクエリが失敗します。
WITH RECURSIVE T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 600) ) SELECT n FROM T1 ORDER BY n ASC
A recursive CTE has reached the maximum number of iterations: 500.
他にも以下のようなケースには対応していないようです。
- FROM句外で再帰クエリを参照できない
- 再帰クエリ内で
ORDER BYとLIMITは使えない - 再帰クエリ内で集計関数は使えない
- 再帰クエリ内でウィンドウ関数は使えない
- 再帰クエリ内で
DISTINCTとGROUP BYは使えない
詳しくは以下のドキュメントを参考にしてください。
おまけ
再帰関数といえばフィボナッチ数列ということでSQLでフィボナッチ数列を書いてみました。
DECLARE num INT64;
set num = 10
;
WITH RECURSIVE TMP AS (
SELECT
0 AS a, 1 AS b, 1 AS cnt
UNION ALL
SELECT
b AS a, a + b AS b, cnt + 1 AS cnt
FROM TMP WHERE cnt < num
)
SELECT b AS Fibonacci_Number
FROM TMP WHERE cnt = num
ORDER BY cnt ASC
+------------------+
| Fibonacci_Number |
+------------------+
| 55 |
+------------------+
set num = 10の部分で何番目のフィボナッチ数を求めたいか指定します。
再帰句で上の画像のようなテーブルを作成し、サブクエリを呼び出す際にWHERE句で対象のレコードを呼び出しています。
DECLARE num INT64;
set num = 100
;
WITH RECURSIVE TMP AS (
SELECT
0 AS a, 1 AS b, 1 AS cnt
UNION ALL
SELECT
b AS a, a + b AS b, cnt + 1 AS cnt
FROM TMP WHERE cnt < num
)
SELECT b AS Fibonacci_Number
FROM TMP WHERE cnt = num
ORDER BY cnt ASC
int64 overflow: 4660046610375530309 + 7540113804746346429
数値が大きくなりすぎるとオーバーフローするので気をつけましょう
まとめ
新しくGAになったRECURSIVEを紹介しました。
これによりデータ処理の幅がさらに広がると思いますので是非試してみてください。
