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

# 初めに

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

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



# Redshift Serverless に接続

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

![image-20230401101504932.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b7b9041d-20fd-2428-e730-423b3d602ab4.png)



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

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/stored-procedure-overview.html

https://dev.classmethod.jp/articles/redshift_stored_procedure/

https://zenn.dev/pontsuyo/articles/cd2beb381896fc