SELECT文のFROM句にストアドプロシージャを指定してみた

はじめに

タイトル通りですが、SnowflakeではSELECT文のFROM句にストアドプロシージャを指定することができるようになりました。

参考 : Selecting from a stored procedure

SQLに組み込む処理といえばUDFですが、ストアドプロシージャも通常のSQL(特にSELECT文)の中に組み込んでCALLできるようになった、ということです。

今まではストアドプロシージャはCALLで呼び出していましたが、さらに用途が広がりますね。

この機能を試してみました。

まずはお試し前の準備です。

準備

USE ROLE SYSADMIN;

CREATE OR REPLACE WAREHOUSE my_wh WITH WAREHOUSE_SIZE='X-SMALL';
USE WAREHOUSE my_wh;

CREATE DATABASE my_db;
CREATE SCHEMA my_db.my_schema;
USE SCHEMA my_db.my_schema;

CREATE OR REPLACE TABLE persona_data (
    person_id NUMBER,      -- 個人を一意に識別するID
    name VARCHAR,          -- 名前
    age NUMBER,            -- 年齢
    gender VARCHAR,        -- 性別
    hobby VARCHAR,         -- 趣味
    purchase_amount NUMBER -- 購入履歴の金額
);

INSERT INTO persona_data (person_id, name, age, gender, hobby, purchase_amount) VALUES
    (1, 'Alice', 30, 'Female', 'Hiking', 120.00),
    (2, 'Bob', 45, 'Male', 'Fishing', 230.00),
    (3, 'Charlie', 25, 'Male', 'Hiking', 150.00),
    (4, 'Diana', 35, 'Female', 'Fishing', 320.00),
    (5, 'Eve', 40, 'Female', 'Reading', 110.00),
    (6, 'Frank', 55, 'Male', 'Hiking', 90.00)
;

試してみた

では本題のお試し用にそれっぽいストアドプロシージャを作成してみます。

CREATE OR REPLACE PROCEDURE calculate_hobby_statistics(
    k NUMBER DEFAULT 2
)
RETURNS TABLE (
    hobby VARCHAR,
    total_spent NUMBER,
    num_people NUMBER
)
LANGUAGE SQL
AS
DECLARE
  query STRING DEFAULT 'SELECT hobby, SUM(purchase_amount) AS total_spent, COUNT(person_id) AS num_people ' ||
                       'FROM persona_data ' ||
                       'GROUP BY hobby ' ||
                       'HAVING COUNT(person_id) >= ?';
  res RESULTSET;
  invalid_filter_exception EXCEPTION (-20001, 'Input parameter \'k\' must be 2 or greater.');
BEGIN
  IF (k < 2) THEN
    RAISE invalid_filter_exception;
  END IF;

  res := (
    EXECUTE IMMEDIATE query
    USING (k)
  );
  
  RETURN TABLE(res);
END;
;

これを実行するには通常はcallします。

call calculate_hobby_statistics();

これが今では何とSELECT文とセットで使えます。

select * from table(
    calculate_hobby_statistics()
)
;

ストアドプロシージャの戻り値(テーブル型)を、通常のテーブル対して扱うことができるので、where句で条件を指定できます。

select * from table(
    calculate_hobby_statistics(3)
)
where hobby in ('Fishing', 'Hiking')
;

ただし、あらゆるストアドプロシージャを組み込むことができるわけではなく、制約があります。
特に戻り値の型をテーブル型として明示的に指定しないといけない点があります。

Limitations for selecting from a stored procedure

Only stored procedures that return tabular data with a static output schema can be placed in the FROM clause of a SELECT statement.

前述のSQLでいうとこの部分ですね。

...
RETURNS TABLE (
    hobby VARCHAR,
    total_spent NUMBER,
    num_people NUMBER
)
...

テーブル型の値を返却するストアドプロシージャを、TABLE関数を介して実行結果(戻り値)を受け取ることで、通常のテーブルと同じように扱うことができるようになります。

...
table(
    calculate_hobby_statistics()
)
...

ストアドプロシージャ側でできそうな工夫点

ここから+αの小ネタに入ります。

ストアドプロシージャ側から何かしらのメッセージをRETURNする際もテーブル形式にしないといけないのか？という疑問が出てきました。

例えば条件分岐でエラーメッセージを返したい時は、Exceptionを仕掛けることで実現できます。

前述のストアドプロシージャのDDLに記載していますが、

DECLARE
  ...
  invalid_filter_exception EXCEPTION (-20001, 'Input parameter \'k\' must be 2 or greater.');
BEGIN
...
  IF (k < 2) THEN
    RAISE invalid_filter_exception;
  END IF;
...

こちらの記述部分ですね。

この定義によって、call経由でもselect経由でも同じようにエラーメッセージが表示されます。

callしたときは...

call calculate_hobby_statistics(1);

以下のようなエラーが返却されます。

-20001 (P0001): Uncaught exception of type 'INVALID_FILTER_EXCEPTION' on line 10 at position 4 : Input parameter 'k' must be 2 or greater.

一方で、selectの中で呼び出してみても...

select * from table(
    calculate_hobby_statistics(1)
)
where hobby in ('Fishing', 'Hiking')
;

以下のようなエラーが返却されます。

-20001 (P0001): Stored procedure execution error on line 2 position 4: Uncaught exception of type 'INVALID_FILTER_EXCEPTION' on line 10 at position 4 : Input parameter 'k' must be 2 or greater.

エラーメッセージはこのように返却できますね。
基本的にはストアドプロシージャから正常な結果(テーブル)ではなく、何らかのメッセージを返すのはエラーぐらいだと思います。

もしエラーの原因を隠したい、ストアドプロシージャの実行条件を満たしていない時はとりあえず空のテーブルを返したい、みたいなケースもあるかと思います。
その場合は、以下のように変数のデフォルト値として空テーブルを返却するSQL文を定義しておけば使いまわせて便利かと思います。

なお、空テーブルを返す際もRETURN TABLE();のような横着はできません。

RETURNSに指定しているテーブルと同じ形式(列名、型)を明示的に指定しないとNGである点に気をつける必要があります。

CREATE OR REPLACE PROCEDURE calculate_hobby_statistics(
    k NUMBER DEFAULT 2
)
RETURNS TABLE (
    hobby VARCHAR,
    total_spent NUMBER,
    num_people NUMBER
)
LANGUAGE SQL
AS
DECLARE
  query STRING DEFAULT 'SELECT hobby, SUM(purchase_amount) AS total_spent, COUNT(person_id) AS num_people ' ||
                       'FROM persona_data ' ||
                       'GROUP BY hobby ' ||
                       'HAVING COUNT(person_id) >= ?';
  -- 空のテーブルの定義を追加
  empty_table_query STRING DEFAULT 'SELECT NULL::VARCHAR AS hobby, NULL::NUMBER AS total_spent, NULL::NUMBER AS num_people WHERE FALSE';
  res RESULTSET;
BEGIN
  IF (k < 2) THEN
    -- 空のテーブルを返却
    res := (
        EXECUTE IMMEDIATE empty_table_query
    );
    RETURN TABLE(res);
  END IF;

  res := (
    EXECUTE IMMEDIATE query
    USING (k)
  );
  
  RETURN TABLE(res);
END;
;

空テーブルの返却を確認してみます。

select * from table(
    calculate_hobby_statistics(1)
)
where hobby in ('Fishing', 'Hiking')
;

ちゃんと空テーブルが返却されました。

おわりに

これはなかなか面白い、ありがたい機能ですね。

これまでテーブルやビューの権限や、行・列レベルのアクセスポリシー等での閲覧制御はできていましたが、ストアドプロシージャを介してのみ何らかのテーブルが得られる、といった仕組みも作れますので、よりきめ細やかな制御が実現でき、Snowflakeの用途もさらに広がりそうですね。

以上です。