Amazon Redshift におけるストアドプロシージャの拡張

Amazon Redshift は、クラウドにおけるフルマネージドでペタバイト規模のデータウェアハウスサービスです。Amazon Redshift を使えば、すべてのデータを分析して、ビジネスと顧客に関する総合的な洞察を導き出すことができます。ストアドプロシージャをサポートしており、準備された SQL コードが保存され、コードを何度も再利用することができます。

ストアドプロシージャは、データ変換、データ検証、ビジネス固有のロジックをカプセル化するために一般的に使用されます。複数の SQL ステップをストアドプロシージャにまとめることで、再利用可能なコードブロックを作成し、単一のトランザクションまたは複数の個別のトランザクションとしてまとめて実行することができます。Amazon Redshift 上のデータ処理を自動化するために、ストアドプロシージャをスケジュールすることもできます。詳細については、Amazon Redshift にストアドプロシージャを導入するを参照してください。

Redshift ストアドプロシージャのデフォルトのアトミックトランザクションモードでは、Redshift ストアドプロシージャの呼び出しは、呼び出されたときに独自のトランザクションを作成するか、ストアドプロシージャが呼び出される前に明示的なトランザクションが開始されている場合は、既存のトランザクションの一部となります。プロシージャ内のすべてのステートメントは、ストアドプロシージャの呼び出しが終了したときに終了する単一のトランザクションブロック内にあるかのように動作します。他のプロシージャへの入れ子になった呼び出しは、他の SQL 文と同様に扱われ、呼び出し元と同じトランザクションのコンテキスト内で動作します。TRUNCATE、COMMIT、ROLLBACK ステートメントと、任意の SQL 文による例外処理ブロックは、現在のトランザクションを終了し、暗黙的に新しいトランザクションを開始します。この動作は、Teradata のような他のシステムから Amazon Redshift への移行において問題を引き起こす可能性があります。

この投稿では、非アトミックトランザクションモードのための Amazon Redshift ストアドプロシージャの拡張について説明します。このモードは、ストアドプロシージャ内のステートメントを自動的にコミットすることを可能にする、強化されたトランザクション制御を提供します。

非アトミックトランザクションモード

新しい非アトミックトランザクションモード機能は、Amazon Redshift のストアドプロシージャに 3 つの強化された機能を提供します。

DML または DDL ステートメントが明示的にオープンされたトランザクションの一部でない限り、ストアドプロシージャ内の各ステートメントは、それらステートメント自身が暗黙的に開始するトランザクションで実行され、次のステートメントではまた新しいトランザクションが開始されます。明示的にトランザクションが開始された場合、後続のステートメントはすべて実行され、明示的なトランザクション制御コマンド（ COMMIT または ROLLBACK ）がトランザクションを終了するために実行されるまで、コミットされないままになります。
Amazon Redshift は例外処理ステートメントが完了した後、例外を再送出しません。そのため、例外処理ブロックによってキャッチされた例外を再送出するために、INFO や EXCEPTION を持たない新しい RAISE ステートメントが提供されています。INFO や EXCEPTION を持たない、この RAISE ステートメントは、例外処理ブロックでのみ許可されています。
また、新しい START TRANSACTION ステートメントは、非アトミックトランザクションモードのストアドプロシージャ内で明示的なトランザクションを開始します。明示的に開始されたトランザクションを終了するには、既存のトランザクション制御コマンド ( COMMIT または ROLLBACK ) を使用します。
- Amazon Redshift はサブトランザクションをサポートしないため、既に開始されたトランザクションが存在する場合、このステートメントを再度呼び出しても何も行われず、エラーは発生しません。
- 非アトミックトランザクションモードのストアドプロシージャ呼び出しが終了したときに、明示的トランザクションがまだ実行中の場合、セッション内でトランザクション制御コマンドが実行されるまで、明示的トランザクションは実行中のままになります。
- トランザクション制御コマンドを実行する前にセッションが切断されると、トランザクション全体が自動的にロールバックされます。

その他の制限

Redshift ストアドプロシージャにはいくつかの制限があります。

ストアドプロシージャの入れ子呼び出しについては、アトミックトランザクションモード（デフォルトのモード）であろうと、新しい非アトミックトランザクションモードであろうと、すべてのプロシージャは同じトランザクションモードで作成されなければなりません。
2 つのトランザクションモード（アトミックと非アトミック）にまたがってストアドプロシージャを入れ子にすることはできません。
非アトミックトランザクションモードのストアドプロシージャに SECURITY DEFINER オプションや SET configuration_parameter オプションを設定することはできません。

カーソルへの影響

非アトミックトランザクションモードのストアドプロシージャのカーソルは、デフォルトのアトミックトランザクションモードと比較して異なる動作をします。

カーソルステートメントは、カーソルループの各イテレーションが自動コミットされないように、カーソルを開始する前に明示的なトランザクションブロックが必要になります。
非アトミックトランザクションモードのストアドプロシージャからカーソルを返すには、カーソルを開始する前に明示的なトランザクションブロックが必要です。そうでなければ、ループ内の SQL ステートメントが自動的にコミットされる時に、カーソルはクローズされます。

利点

ユーザーの視点から見たこの機能の主な利点は、以下の通りです。

Teradata のセッションモードで実行できる Teradata のストアドプロシージャをリフト＆シフトする機能を提供します。これは、Teradata や SQL Server のようなデータウェアハウスからのシームレスな移行に役立ちます。
Amazon Redshift がエラーや例外に遭遇した際に、ストアドプロシージャ内部でより柔軟な操作を提供できるようになります。Amazon Redshift は、例外に到達する前に以前のアクションの状態を保持できるようになりました。

構文

次のコードに示すように、新しいオプションのキーワード NONATOMIC がストアドプロシージャ定義の構文に追加されました。

<code class="lang-sql">CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name
 ( [ [ argname ] [ argmode ] argtype [, ...] ] ) 
[ NONATOMIC ]
AS $$ 
procedure_body 
$$ LANGUAGE plpgsql</code>

このオプションのキーワードは、ストアドプロシージャを非アトミックトランザクションモードで作成します。このキーワードを指定しない場合は、ストアドプロシージャの作成時にデフォルトのアトミックモードがトランザクションモードとなります。

NONATOMIC は、プロシージャ内の各 DML と DDL ステートメントが暗黙的にコミットされることを意味します。

非アトミックモードでない場合、プロシージャは呼び出し開始時に独自のトランザクションを作成するか、呼び出し前に明示的なトランザクションが開始されていれば既存のトランザクションの一部となります。ストアドプロシージャ内の全てのステートメントは、この 1 つのトランザクションに属することになります。

NONATOMIC モードの例

顧客の主連絡先と副連絡先の電話番号を格納する顧客連絡先テーブル custcontacts を考えてみましょう。

CREATE table custcontacts( 
custid int4 not null, 
primaryphone char(10), 
secondaryphone char(10));

連絡先番号のない 3 つのサンプル顧客レコードを挿入します。

INSERT INTO custcontacts VALUES (101, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (102, 'xxxxxxxxxx', 'xxxxxxxxxx');
INSERT INTO custcontacts VALUES (103, 'xxxxxxxxxx', 'xxxxxxxxxx');

主と副の電話番号を更新するストアドプロシージャを作成する必要があります。要件は、副連絡先番号への更新が何らかの理由で失敗した場合に、主連絡先番号への更新をロールバックしないことです。

これは、NONATOMIC キーワードを使用してストアドプロシージャを作成することで実現できます。NONATOMIC キーワードは、ストアドプロシージャ内の各ステートメントが独自の暗黙的なトランザクションブロックで実行されるようにします。したがって、副電話番号に対する UPDATE ステートメントが失敗しても、主電話番号に対するデータ更新がロールバックされることはありません。以下のコードを参照してください。

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
END;
$$
LANGUAGE plpgsql;

それでは、10 桁以上の secondaryphone を渡すストアドプロシージャを呼び出してみましょう。副電話番号の長さが正しくないため UPDATE ステートメントは失敗します。

call sp_update_custcontacts(101,'1234567890','345443345324');

前述のプロシージャ呼び出しは、主電話番号の更新には成功しますが、副電話番号の更新には失敗します。ただし、primaryphone の更新は、ストアドプロシージャ定義の NONATOMIC 節により、独自の暗黙のトランザクションブロックで実行されたため、ロールバックされません。

select * from custcontacts; 

custcontacts | primaryphone | secondaryphone 
-------------+---------------+---------------
101 | 1234567890 | XXXXXXXXXX 
102 | XXXXXXXXXX | XXXXXXXXXX 
103 | XXXXXXXXXX | XXXXXXXXXX

NONATOMICモードでの例外処理

ストアドプロシージャにおける例外の取り扱いは、アトミックモードか非アトミックモードかで異なります。

アトミック (デフォルト) – 例外は常に再送出されます。
非アトミック – 例外は処理され、再送出するかどうかを選択できます。
先ほどの例に続き、非アトミックモードでの例外処理を確認していきましょう。

ストアドプロシージャによって発生した例外を記録するために、以下のテーブルを作成します。

CREATE TABLE procedure_log
(log_timestamp timestamp, procedure_name varchar(100), error_message varchar(255));

次に、例外を処理するために sp_update_custcontacts() プロシージャを更新します。プロシージャ定義に EXCEPTION ブロックを追加していることに注意してください。これは、例外が発生した場合に procedure_log テーブルにレコードを挿入します。

CREATE OR REPLACE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
END;
$$
LANGUAGE plpgsql;

もう 1 つストアドプロシージャを作成します。このプロシージャは前述のプロシージャを呼び出します。このプロシージャも EXCEPTION ブロックを持ち、例外が発生した場合に procedure_log テーブルにレコードを挿入します。

CREATE PROCEDURE sp_update_customer() NONATOMIC AS
$$
BEGIN
-- Let us assume you have additional staments here to update other fields. For this example, ommitted them for simplifiction.
-- Nested call to update contacts
call sp_update_custcontacts(101,'1234567890','345443345324');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_customer', sqlerrm);
END;
$$
LANGUAGE plpgsql;

作成した親プロシージャを呼び出してみましょう。

call sp_update_customer();

これにより、sp_update_custcontacts() プロシージャが呼び出されます。副電話番号を無効な値で更新しているため、内部で呼び出されているプロシージャ sp_update_custcontacts() は失敗します。コントロールは sp_update_custcontacts() プロシージャの EXCEPTION ブロックに入り、procedure_log テーブルに挿入します。

しかし、非アトミックモードでは例外を再送出しません。したがって、親プロシージャ sp_update_customer() は、sp_update_custcontacts() プロシージャから渡された例外を取得しません。コントロールは sp_update_customer() プロシージャの EXCEPTION ブロックに入りません。

procedure_log テーブルをクエリすると、sp_update_custcontacts() プロシージャで処理されたエラーのエントリのみが表示されます。

select * from procedure_log;

次に、RAISE ステートメントを使用して sp_update_custcontacts() プロシージャを再定義します。

CREATE PROCEDURE sp_update_custcontacts(cid int4,pphone char(15),sphone char(15)) NONATOMIC AS
$$
BEGIN
UPDATE custcontacts SET primaryphone=pphone WHERE custid=cid;
UPDATE custcontacts SET secondaryphone=sphone WHERE custid=cid;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO procedure_log VALUES (getdate(), 'sp_update_custcontacts', sqlerrm);
RAISE;
END;
$$
LANGUAGE plpgsql;

親ストアドプロシージャ sp_update_customer() をもう一度呼び出してみましょう。

call sp_update_customer();

ここで、内部で呼び出されているプロシージャ sp_update_custcontacts() は、自身の EXCEPTION ブロックで例外を処理した後、親プロシージャ sp_update_customer() に例外を再送出します。その後、コントロールは親プロシージャの EXCEPTION ブロックに到達し、procedure_log テーブルに別のレコードを挿入します。

procedre_log テーブルにクエリを実行すると、2 つのエントリーが表示されます。1 つは内部で呼び出されているプロシージャ sp_update_custcontacts() によるもので、もう 1 つは親プロシージャ sp_update_customer() によるものです。これは、内部で呼び出されているプロシージャの RAISE ステートメントが例外を再送出したことを示しています。

select * from procedure_log;

非アトミック・モードでの明示的なSTART TRANSACTION文

START TRANSACTION ステートメントを発行して、ストアドプロシージャ内でトランザクションブロックを開始することができます。これは、ストアドプロシージャ内で新しいトランザクションを開始します。参考例については、非アトミックモードのストアドプロシージャのトランザクション管理を参照してください。

まとめ

この投稿では、Redshift ストアドプロシージャの非アトミックトランザクションモードの機能強化について説明しました。これは、ストアドプロシージャ内のステートメントを自動的にコミットできるようにトランザクション制御を拡張したものです。このモードは、Teradata のような他のシステムから Amazon Redshift への移行も容易にします。

原文はこちらです。

Amazon Web Services ブログ