Databricksランタイム17.0における再起的クエリーのサポート

こちらのアップデートです。

再帰的共通テーブル式(rCTE)のサポート
Databricksでは、再起的な共通テーブル式(rCTE)を用いた階層型データのナビゲーションがサポートされました。再帰的なリレーションシップを辿るにはUNION ALLと自己参照CTEを使います。

Databricksランタイム17.0を使います。

2地点間のルートを表現する一時ビューを作成します。

-- 一時ビュー 'routes' を作成
CREATE TEMPORARY VIEW routes(origin, destination) AS VALUES
  ('New York', 'Washington'),
  ('New York', 'Boston'),
  ('Boston', 'New York'),
  ('Washington', 'Boston'),
  ('Washington', 'Raleigh');

WITH RECURSIVEを用いることで、再帰的なクエリーを定義できます。以下では、SELECT...FROM routes...JOIN destinations_from_new_yorkのように自己参照しています。

-- 再帰的に 'New York' から到達可能な目的地を計算
WITH RECURSIVE destinations_from_new_york AS (
    -- 初期状態として 'New York' を目的地とし、経路に 'New York' を含め、長さを 0 とする
    SELECT 'New York' AS destination, ARRAY('New York') AS path, 0 AS length
    UNION ALL
    -- ルートテーブルと再帰的に結合し、新しい目的地、経路、長さを計算
    SELECT r.destination, CONCAT(d.path, ARRAY(r.destination)), d.length + 1
      FROM routes AS r
      JOIN destinations_from_new_york AS d
        -- 現在の目的地が出発地と一致し、経路に含まれていない場合に結合
        ON d.destination = r.origin AND NOT ARRAY_CONTAINS(d.path, r.destination)
  )
  -- 再帰的に計算された結果を選択
  SELECT * FROM destinations_from_new_york;

New Yorkを起点としてルートを辿ってpathを構成してます。

destination	path	length
New York	["New York"]	0
Washington	["New York","Washington"]	1
Boston	["New York","Boston"]	1
Boston	["New York","Washington","Boston"]	2
Raleigh	["New York","Washington","Raleigh"]	2

なお、ストアドプロシージャでも使用できます。

-- ストアドプロシージャ 'numbers' を作成
CREATE PROCEDURE takaakiyayoi_catalog.default.numbers(end INT) 
-- SQL 言語を使用
LANGUAGE SQL
-- セキュリティコンテキストを INVOKER に設定
SQL SECURITY INVOKER
-- コメントを追加
COMMENT 'Returns numbers from 1 to end'
-- プロシージャの開始
AS BEGIN
  -- 再帰的な CTE を定義
  WITH RECURSIVE rec(num) AS
    -- 初期値を設定
    (VALUES (1) 
    -- 再帰的に値を生成
    UNION ALL SELECT num + 1 FROM rec WHERE num < end)
    -- 結果を選択
    SELECT * FROM rec;
-- プロシージャの終了
END;

CALL takaakiyayoi_catalog.default.numbers(5);

num
1
2
3
4
5

はじめてのDatabricks

はじめてのDatabricks

Databricks無料トライアル

Databricks無料トライアル