Amazon Redshift PL/pgSQLのストアドプロシージャ開発入門

はじめに

今朝、PL/pgSQLのストアドプロシージャのリリースについては以下のブログにてご紹介しました。さらにPL/pgSQLのストアドプロシージャの開発を具体例を用いて解説します。

Amazon Redshift 待望の PL/pgSQL のストアドプロシージャをサポートしました

• PL/pgSQLとは
  • 特長
• ストアドプロシージャとは
  • 特長
  • アクセス制御
• ストアドプロシージャの作成（CREATE PROCEDURE）
  • 構文
  • ストアドプロシージャ名
  • 引数の指定
  • プロシージャ本体の指定
  • プロシージャ本体
  • 例．2つの入力パラメータを持つプロシージャ
  • 例．1つのINパラメータ、1つのOUTパラメータ、および1つのINOUTパラメータを持つプロシージャ
  • SECURITY属性指定
• ストアドプロシージャの削除（DROP PROCEDURE）
  • 構文
  • 例．ストアドプロシージャtest_sp1の削除
• ストアドプロシージャの参照（SHOW PROCEDURE）
  • 構文
  • 例．ストアドプロシージャtest_spl2の定義の参照
  • 補足：その他の参照方法
• ストアドプロシージャの呼び出し（CALL）
  • 構文
  • 例．ストアドプロシージャの作成と呼び出し
  • ネストされた呼び出しと再帰的な呼び出しのサポート
• ユースケース
  • 有効なユースケース
  • 不向きなユースケース

PL/pgSQLとは

PostgreSQLの手続き型言語PL/pgSQLという言語でSQLクエリとロジックを記述します。

特長

PL/pgSQLの特長は以下のとおりです。

• 条件判断（IF-ELSE）やループ（LOOP）などの制御文をサポート
• 動的なSQLの実行（EXECUTE文）をサポート
• カーソルのサポート
• 例外処理のサポート

ストアドプロシージャとは

ストアドプロシージャは、SQLクエリとロジックを登録したものです。データベースに登録したストアドプロシージャは、実行に必要な十分な権限を持つすべてのユーザーがCALLコマンドを使用して実行できます。ストアドプロシージャは、PostgreSQLの手続き型言語PL/pgSQLという言語でSQLクエリとロジックを記述します。

ユーザー定義関数（UDF）とは異なり、ストアドプロシージャはSELECTクエリに加えてデータ定義言語（DDL）およびデータ操作言語（DML）を組み込むことができます。ストアドプロシージャは値を返す必要はありません。ループや条件式などの手続き型言語を使用して、論理フローを制御できます。

特長

ストアドプロシージャは、一般にデータ変換、データ検証、およびビジネス固有のロジックのカプセル化するために用いられます。複数のSQLステップをストアドプロシージャに結合することで、アプリケーションとデータベース間のラウンドトリップを減らすことができます。

アクセス制御

きめ細かいアクセス制御の場合は、基になるテーブルへのアクセスをユーザーに許可せずに関数を実行するストアドプロシージャを作成できます。たとえば、オブジェクトオーナーまたはスーパーユーザだけがテーブルをTRUNCATEすることができ、その他の一般ユーザーはテーブルにデータを挿入するための書き込み権限を必要とします。基になるテーブルに対するアクセス許可をユーザーに付与せずに、タスクを実行するストアドプロシージャを作成できます。その後、ストアドプロシージャを実行する権限をユーザーに与えます。

SECURITY DEFINER属性を持つストアドプロシージャは、ストアドプロシージャの所有者の権限で実行されます。デフォルトでは、ストアドプロシージャにはSECURITY INVOKER属性が設定されています。このプロシージャはこのプロシージャを呼び出すユーザーのアクセス許可を使用します。

ストアドプロシージャの作成（CREATE PROCEDURE）

構文

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name
( [ [ argname ] [ argmode ] argtype [, ...] ] )
AS $$
procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

ストアドプロシージャ名

プロシージャ名.スキーマ名（例．myschema.myprocedure）を指定した場合 、プロシージャは指定したスキーマ内に作成されます。それ以外の場合、プロシージャは現在のスキーマ内に作成されます。

AWSのマニュアルでは、ストアドプロシージャ名の先頭にsp_を付けることを推奨しています。 Amazon Redshiftはストアドプロシージャ名の先頭にsp_を予約しています。sp_プレフィックスを使用することで、ストアドプロシージャ名が既存または将来のAmazon Redshift組み込みストアドプロシージャまたは関数名と競合しないようにします。詳細については、ストアドプロシージャの命名を参照してください。

シグネチャ（入力引数の数と引数のデータ型）の指定

( [ [ argname ] [ argmode ] argtype [, ...] ] )

シグネチャは、引数名、引数モード、データ型は、順に列挙します。データ型のみが必須です。名前とモードはオプションであり、それらの位置は切り替えることができます。最大32個の入力引数と32個の出力引数を指定できます。

• argname：引数名（任意）
• argmode：引数モード（任意）
• IN IN引数は、イミュータブルな入力用の引数です。引数モードをを省略した場合のデフォルトは、IN引数になります。
• OUT OUT引数は、出力用の引数です。OUT引数はCALLステートメントの一部として指定されていません。ストアード・プロシージャーのCALLステートメントにINOUT引数を指定してください。
• INOUT INOUT引数は、入力かつ出力用の引数のことです。ネストした呼び出しから値を渡したり返すときや、refcursorを返すときにも役立ちます。
• argtype：データ型（必須） 引数のデータ型は、標準のAmazon Redshiftデータ型を指定します。さらに、引数のデータ型はrefcursorにすることができます。

プロシージャ呼び出しから1つ以上の値を返すために、OUT引数とINOUT引数を使用できます。 OUT引数またはINOUT引数がある場合、プロシージャー呼び出しはn列を含む1つの結果行を返します。ここで、nはOUT引数またはINOUT引数の合計数です。

INOUT引数は同時に入力引数と出力引数です。入力引数にはIN引数とINOUT引数の両方が含まれ、出力引数にはOUT引数とINOUT引数の両方が含まれます。

プロシージャ本体の指定

AS $$ procedure_body $$

実行するプロシージャは、リテラルキーワードAS $$と$$によってプロシージャ本体を囲みます。$記号の間に、文を識別しやすくするために文字列を指定できます。例えば、$$のかわりに$test$を指定することも可能です。

プロシージャ本体

有効なPL/pgSQLステートメントのセット。 PL/pgSQLステートメントは、論理フローを制御するために、ループや条件式を含む手続き構造を使ってSQLコマンドを定義します。 COPY、UNLOAD、INSERTなどのデータ操作言語（DML）、およびCREATE TABLEなどのデータ定義言語（DDL）を含む、ほとんどのSQLコマンドはプロシージャ本体で使用できます。LANGUAGEは、plpgsqlを指定してください。

例．2つの入力パラメータを持つプロシージャ

このストアドプロシージャは、2つのINパラメータを持ちます。引数モードは指定していないのでデフォルトのINになります。プロシジャの最後のRAISE INFOでメッセージ出力しています。

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
min_val int;
BEGIN
DROP TABLE IF EXISTS tmp_tbl;
CREATE TEMP TABLE tmp_tbl(id int);
INSERT INTO tmp_tbl values (f1),(10001),(10002);
SELECT INTO min_val MIN(id) FROM tmp_tbl;
RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

補足： RedshiftのRAISEによるメッセージ出力は、INFO、NOTICE、WARNINGのいずれかのようです。

CREATE OR REPLACE PROCEDURE raise_sample()
AS $$
DECLARE
foo text := 'foo';
bar int := 123;
BEGIN
RAISE DEBUG 'DEBUG RAISE MESSAGE';
RAISE LOG 'LOG RAISE MESSAGE';
RAISE INFO 'INFO RAISE MESSAGE %', foo;
RAISE NOTICE 'NOTICE RAISE MESSAGE %,%', foo, bar;
RAISE WARNING 'WARNING RAISE MESSAGE';
END
$$ LANGUAGE plpgsql
;

CALL raise_sample();
INFO: INFO RAISE MESSAGE foo
NOTICE: NOTICE RAISE MESSAGE foo,123
WARNING: WARNING RAISE MESSAGE
CALL

例．1つのINパラメータ、1つのOUTパラメータ、および1つのINOUTパラメータを持つプロシージャ

このストアドプロシージャは、1つのINパラメータ、1つのOUTパラメータ、および1つのINOUTパラメータを持ちます。入力パラメタ（INとINOUT）の入力チェックした後、一時テーブルmy_etlを作成して結果を格納します。ストアドプロシジャの結果は出力パラメータ（OUTとINOUT）の結果を返します。例外は、RAISE EXCEPTION '<メッセージ>'にてメッセージ出力しています。

CREATE OR REPLACE PROCEDURE test_sp2(f1 int, f2 INOUT varchar, OUT varchar)
AS $$
DECLARE
out_var alias for $3;
loop_var int;
BEGIN
IF f1 is null OR f2 is null THEN
RAISE EXCEPTION 'input cannot be null';
END IF;
DROP TABLE if exists my_etl;
CREATE TEMP TABLE my_etl(a int, b varchar);
FOR loop_var IN 1..f1 LOOP
insert into my_etl values (loop_var, f2);
f2 := f2 || '+' || f2;
END LOOP;
SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;

SECURITY属性指定

プロシージャには、基となるデータベースオブジェクトにアクセスするための権限が必要です。プロシージャのSECURITY属性によって、実行時のプロシージャのアクセス権限が決めることができます。

• SECURITY INVOKERの場合 プロシージャーはそのプロシージャーを呼び出すユーザーの権限を使用します。ユーザーは、基となるデータベースオブジェクトに対する明示的な権限を持っている必要があります。デフォルトはSECURITY INVOKERです。
• SECURITY DEFINERの場合 SECURITY DEFINERを指定した場合、プロシージャーはそのプロシージャーの所有者の権限を使用します。プロシージャを呼び出すユーザーには、そのプロシージャに対する実行権限が必要ですが、基となるオブジェクトに対する権限は必要ありません。

ストアドプロシージャの削除（DROP PROCEDURE）

構文

DROP PROCEDURE sp_name ( [ [ argname ] [ argmode ] argtype [, ...] ] )

ストアドプロシージャ名とシグネチャ（入力引数の数と引数のデータ型）を指定して削除します。引数を指定が必須なのは、PostgreSQLのDROP PROCEDUREと異なります。シグネチャが必要な理由は、シグネチャが異なる同じ名前の（オーバーロードされている）プロシージャを複数定義できるからと考えられます。

例．ストアドプロシージャtest_sp1の削除

ストアドプロシージャ名とシグネチャ（入力引数の数と引数のデータ型）を指定して削除します。

DROP PROCEDURE test_sp1(f1 int, f2 varchar(20));

一方、関数test_sp2の削除は、作成する際に指定した引数は３つですが、削除する際には引数名がある２つの引数のみを指定します。

DROP PROCEDURE test_sp2(f1 int, f2 varchar);

ストアドプロシージャの参照（SHOW PROCEDURE）

構文

SHOW PROCEDURE sp_name [( [ [ argname ] [ argmode ] argtype [, ...] ] )]

例．ストアドプロシージャtest_spl2の定義の参照

SHOW PROCEDURE test_sp2(int, varchar);
Stored Procedure Definition
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE public.test_sp2(f1 integer, INOUT f2 character varying, OUT character varying)
LANGUAGE plpgsql
AS $_$
DECLARE
out_var alias for $3;
loop_var int;
BEGIN
IF f1 is null OR f2 is null THEN
RAISE EXCEPTION 'input cannot be null';
END IF;
CREATE TEMP TABLE etl(a int, b varchar);
FOR loop_var IN 1..f1 LOOP
insert into etl values (loop_var, f2);
f2 := f2 || '+' || f2;
END LOOP;
SELECT INTO out_var count(*) from etl;
END;
$_$

(1 row)

補足：その他の参照方法

PostgreSQL標準のクライアントpsqlは、以下のように簡単にストアドプロシージャの一覧や定義を確認できます。

-- 定義されたファンクション一覧を確認する
\df
\df [プロシージャ名]

-- 作成済みプロシージャ一覧の取得方法
\df+ [プロシージャ名]

実行例は、以下のとおりです。

cmdb=> -- 定義されたファンクション一覧を確認する
cmdb=> \df
List of functions
schema | name | result data type | argument data types | type
--------------------+--------------+-----------------------------+----------------------------+------
cm_ishikawa_satoru | test_sp1 | - | integer, character varying | func
cm_ishikawa_satoru | test_sp2 | - | integer, character varying | func
(2 rows)

cmdb=> \df test_sp1
List of functions
schema | name | result data type | argument data types | type
--------------------+----------+------------------+----------------------------+------
cm_ishikawa_satoru | test_sp1 | - | integer, character varying | func
(1 row)

cmdb=> -- 作成済みプロシージャ定義の取得方法
cmdb=> \df+ test_sp1
List of functions
schema | name | result data type | argument data types | type | volatility | owner | security | access privileges | language | source code | description
--------------------+----------+------------------+----------------------------+------+------------+--------------------+----------+-------------------+----------+----------------------------------------------------+-------------
cm_ishikawa_satoru | test_sp1 | - | integer, character varying | func | volatile | cm_ishikawa_satoru | invoker | | plpgsql | +|
| | | | | | | | | | DECLARE +|
| | | | | | | | | | min_val int; +|
| | | | | | | | | | BEGIN +|
| | | | | | | | | | DROP TABLE IF EXISTS tmp_tbl; +|
| | | | | | | | | | CREATE TEMP TABLE tmp_tbl(id int); +|
| | | | | | | | | | INSERT INTO tmp_tbl values (f1),(10001),(10002);+|
| | | | | | | | | | SELECT INTO min_val MIN(id) FROM tmp_tbl; +|
| | | | | | | | | | RAISE INFO 'min_val = %, f2 = %', min_val, f2; +|
| | | | | | | | | | END; +|
| | | | | | | | | | |
(1 row)

ストアドプロシージャの呼び出し（CALL）

ストアドプロシージャを作成するには、CREATE PROCEDUREコマンドを使用します。プロシージャを実行するには、CALLコマンドを使用します。

構文

CALL sp_name ( [ argument ] [, ...] )

引数に関数を指定することができますが、クエリは指定できません。

例．ストアドプロシージャの作成と呼び出し

-- ストアドプロシージャの作成
CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

-- ストアドプロシージャの呼び出し
CALL test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

ネストされた呼び出しと再帰的な呼び出しのサポート

ストアドプロシージャは、ネストされた呼び出しと再帰的な呼び出しをサポートしています。許容されるネストレベルの最大数は16です。ネストされた呼び出しはビジネスロジックを小さなプロシージャにカプセル化することができます。これは複数の呼び出し元で共有できます。

出力パラメーターを持つネストしたプロシージャーを呼び出す場合、内部プロシージャーはINOUT引数を定義しなければなりません。この場合、内部手続きは定数でない変数で渡されます。OUT引数は許可されていません。この理由は内部呼び出しの出力を保持するために変数が必要なために発生します。

内部と外部のプロシージャの関係は、SVL_STORED_PROC_CALLテーブルのfrom_sp_callカラムに記録されます。次の例は、INOUT引数を使用してネストしたプロシージャー呼び出しに変数を渡す方法を示しています。

CREATE OR REPLACE PROCEDURE inner_proc(INOUT a int, b int, INOUT c int) LANGUAGE plpgsql
AS $$
BEGIN
a := b * a;
c := b * c;
END;
$$;
CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE outer_proc(multiplier int) LANGUAGE plpgsql
AS $$
DECLARE
x int := 3;
y int := 4;
BEGIN
DROP TABLE IF EXISTS test_tbl;
CREATE TEMP TABLE test_tbl(a int, b varchar(256));
CALL inner_proc(x, multiplier, y);
insert into test_tbl values (x, y::varchar);
END;
$$;
CREATE PROCEDURE

CALL outer_proc(5);
INFO: Table "test_tbl" does not exist and will be skipped
CALL

SELECT * from test_tbl;
a | b
----+----
15 | 20
(1 row)

ユースケース

有効なユースケース

• データ変換やデータ検証におけるクライアントアプリケーションとRedshift間のラウンドトリップの削減
• オブジェクトへの権限を持たないユーザーが、プロシージャの所有者の権限でデータを操作したり、アクセスしたい場合
• 専門的なビジネス固有のロジックのカプセル化
• プロシージャからプロシージャ呼び出しによるモジュール化、プロシージャの再利用

不向きなユースケース

• 大規模データに対するカーソル使用など、リーダーノードに負荷が集中する場合
• カーソルを用いたループにおいて結合がコロケーションにならない場合
• CPU負荷の高い処理プロシージャの多用する場合

最後に

ストアドプロシージャを利用することで、Redshiftのみではこれまでまでできなかった変換やクライアントアプリケーションとRedshift間のラウンドトリップの削減効果は大きいはずです。私が比較した限りでは、一部を除きPostgreSQLのストアドプロシージャの仕様に限りなく近いという印象です。

Oracleのストアドプロシージャしか作成したことなない筆者でも、ほとんど違和感なくRedshiftのストアドプロシージャを作成できましたので、他のDBにてストアドプロシージャの開発経験のある方であれば、解説と例を参考に新規開発やマイグレーションのイメージが掴めたと思います。

個人的な見解ですが、Amazon Redshiftは、Windows関数や他のAWS のビッグデータソリューションも充実しており、ストアドプロシージャがなくても私なりのワークアラウンドがあったのでそれほど不自由は感じておりませんでした。しかし、レガシーな行志向のDBからのマイグレーションではストアドプロシージャの移行がないため、常にどのように移行するかが課題であったのも事実です。マイグレーションにおいて、安易なリフトアンドシフトは、既存のバックログをそのまま継承するという懸念がありますが、既存のフローを変えることなく移行したい場合に、ストアドプロシージャのサポートは心強いはずです。

いずれにしても、有効なユースケースにピンポイントでご利用していただけることをおすすめします。