Redshift Serverless でストアドプロシージャーを動かしてみた (psql, Data API)

初めに

Redshift Serverless でストアドプロシージャーを動かしたことがなかったので、どのように作成・起動するのか検証していきます。以下の内容を実施する手順を紹介していきます

• psql で Redshift Serverless に接続
• psql で ストアドプロシージャーを作成
• psql でストアドプロシージャーを呼びだし
• Data API でストアドプロシージャーを呼びだし

Redshift Serverless に接続

Redshift Serverless は既に作成している環境を使います。Workgroup の詳細を開き、Endpoint を確認します。

psql コマンドで接続します

psql \
-h default.xxxxxxxxxxxx.ap-northeast-1.redshift-serverless.amazonaws.com \
-p 5439 \
-U awsuser \
-d dev

ストアドプロシージャー作成

実際にストアドプロシージャーを作成していきましょう。ストアドプロシージャーの一覧確認です。

SELECT
    routine_schema,
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE';

実行例 : 何も表示されません。

dev=# SELECT
dev-#     routine_schema,
dev-#     routine_name
chema.rodev-# FROM
dev-#     information_schema.routines
dev-# WHERE
dev-#     routine_type = 'PROCEDURE';
 routine_schema | routine_name
----------------+--------------
(0 rows)

test_sp1 という名前のストアドプロシージャーを作成します。public スキーマに tmp_tblテーブルを作成するプロシージャーです。

CREATE PROCEDURE test_sp1()
AS $$
BEGIN
  DROP TABLE IF EXISTS public.tmp_tbl;
  CREATE TABLE public.tmp_tbl(id int);
  INSERT INTO public.tmp_tbl values (10001),(10002);
END;
$$ LANGUAGE plpgsql;

ストアドプロシージャーの一覧コマンドで、表示されることがわかりました

dev=# SELECT
dev-#     routine_schema,
dev-#     routine_name
dev-# FROM
dev-#     information_schema.routines
dev-# WHERE
dev-#     routine_type = 'PROCEDURE';
 routine_schema | routine_name
----------------+--------------
 public         | test_sp1
(1 row)

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

ストアドプロシージャーを呼びだします。

CALL test_sp1();

実行例です。

dev=# CALL test_sp1();
CALL

ストアドプロシージャーは、public.tem_tbl を作成するものでした。この中身を確認して、正常に実行されたか見てみます。

SELECT * FROM public.tmp_tbl;

実行例 : 正常にデータが存在しています。

dev=# SELECT * FROM public.tmp_tbl;
  id
-------
 10001
 10002
(2 rows)

DROP TABLE

DROP TABLE tmp_tbl;

付録1 : Data API 経由で呼び出し

Redshift の Data API 経由でもストアドプロシージャーを利用できるか確認します。

まず、Data API 経由で利用される Redshift (PostgreSQL) 上の user 名を確認します。AWS CLI で Data API を実行し、SELECT current_user; という SQL 文を投げます。

aws redshift-data execute-statement \
--workgroup-name default \
--database dev \
--sql "SELECT current_user;"

実行例

> aws redshift-data execute-statement \
      --workgroup-name default \
      --database dev \
      --sql "SELECT current_user;"
{
    "CreatedAt": "2023-04-01T11:42:25.870000+09:00",
    "Database": "dev",
    "DbUser": "IAM:cliuser:AdminGroup",
    "Id": "a2b5d2e8-ed44-41fd-86e9-caaef25e2bd4",
    "WorkgroupName": "default"
}

上の手順で表示された id を使って、結果を取得します。

aws redshift-data get-statement-result --id a2b5d2e8-ed44-41fd-86e9-caaef25e2bd4

実行例

• IAM:cliuser というユーザーで、Data API が実行されていることがわかりました。

> aws redshift-data get-statement-result --id a2b5d2e8-ed44-41fd-86e9-caaef25e2bd4
{
    "Records": [
        [
            {
                "stringValue": "IAM:cliuser"
            }
        ]
    ],
    "ColumnMetadata": [
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "current_user",
            "length": 0,
            "name": "current_user",
            "nullable": 1,
            "precision": 63,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "bpchar"
        }
    ],
    "TotalNumRows": 1
}

Redshift に psql コマンドで接続し、IAM:cliuser ユーザーに対して、対象のスキーマやプロシージャーの権限を付与します。

GRANT ALL PRIVILEGES on ALL TABLES IN SCHEMA public to "IAM:cliuser";
GRANT ALL PRIVILEGES on ALL PROCEDURES IN SCHEMA public to "IAM:cliuser";

Data API でストアドプロシージャーを呼びだしてみます。

aws redshift-data execute-statement \
--workgroup-name default \
--database dev \
--sql "CALL test_sp1();"

実行例

> aws redshift-data execute-statement \
      --workgroup-name default \
      --database dev \
      --sql "CALL test_sp1();"
{
    "CreatedAt": "2023-04-01T11:56:04.162000+09:00",
    "Database": "dev",
    "DbUser": "IAM:cliuser:AdminGroup",
    "Id": "c6d264cd-f501-4545-970f-d394e65df91e",
    "WorkgroupName": "default"
}

describe-statement で Data API を実行した結果が確認できます。Status : FINISHED となっており、正常に呼び出されていそうなことがわかります。

> aws redshift-data describe-statement --id c6d264cd-f501-4545-970f-d394e65df91e
{
    "CreatedAt": "2023-04-01T11:56:04.162000+09:00",
    "Duration": 1408677108,
    "HasResultSet": false,
    "Id": "c6d264cd-f501-4545-970f-d394e65df91e",
    "QueryString": "CALL test_sp1();",
    "RedshiftPid": 1073774953,
    "RedshiftQueryId": 3200216,
    "ResultRows": 0,
    "ResultSize": 0,
    "Status": "FINISHED",
    "UpdatedAt": "2023-04-01T11:56:06.677000+09:00",
    "WorkgroupName": "default"
}

確認のため、テーブルの中身を Data API 経由で確認してみましょう。

aws redshift-data execute-statement \
--workgroup-name default \
--database dev \
--sql "SELECT * FROM public.tmp_tbl;"

実行例

> aws redshift-data execute-statement \
      --workgroup-name default \
      --database dev \
      --sql "SELECT * FROM public.tmp_tbl;"
{
    "CreatedAt": "2023-04-01T11:57:46.293000+09:00",
    "Database": "dev",
    "DbUser": "IAM:cliuser:AdminGroup",
    "Id": "a70848ec-657a-4218-8f82-126544268581",
    "WorkgroupName": "default"
}

Data API の実行結果を取得します。

aws redshift-data get-statement-result --id a70848ec-657a-4218-8f82-126544268581

実行例 : 正常にストアドプロシージャーが呼び出されていることがわかりました。

> aws redshift-data get-statement-result --id a70848ec-657a-4218-8f82-126544268581
{
    "Records": [
        [
            {
                "longValue": 10001
            }
        ],
        [
            {
                "longValue": 10002
            }
        ]
    ],
    "ColumnMetadata": [
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "id",
            "length": 0,
            "name": "id",
            "nullable": 1,
            "precision": 10,
            "scale": 0,
            "schemaName": "public",
            "tableName": "tmp_tbl",
            "typeName": "int4"
        }
    ],
    "TotalNumRows": 2
}

付録2 : Procedure の削除

削除コマンド

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

参考 URL