Amazon Redshift auto-copy from Amazon S3を試してみた

# 背景・目的
先日、「[Amazon Redshift now supports auto-copy from Amazon S3](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/)」が発表されましたので、ドキュメントの確認と実際に動かしてみたいと思います。

# まとめ
- 2022年12月16日時点でプレビューです。今後仕様が変わる可能性があります。
    - 以下のリージョンで使用ができます。
        - US East (Ohio) Region (us-east-2)
        - US East (N. Virginia) Region (us-east-1)
        - US West (Oregon) Region (us-west-2)
        - Asia Pacific (Tokyo) Region (ap-northeast-1)
        - Europe (Stockholm) Region (eu-north-1)
        - Europe (Ireland) Region (eu-west-1)
- COPYコマンドをジョブとして登録することで、S3に配置したファイルを自動的にコピーすることができます。

# 概要
- COPYステートメントをコピージョブに保存できるようになり、S3パスで検出された新しいファイルが自動的にRedshiftにCOPYできるようになりました。
- Redshiftは、ロードされたファイルを追跡し、COPYコマンドごとにバッチ処理されるファイルの数を決定します。
- COPYコマンドの状況は、以下のシステム ビューで確認できます。
    - SYS_COPY_JOB (preview)
        - 現在定義されている各 COPY JOB の行が含まれています。
    - STL_LOAD_ERRORS
        - COPY コマンドからのエラーが含まれます。
    - STL_LOAD_COMMITS
        - COPY コマンドのデータ ロードのトラブルシューティングに使用される情報が含まれています。
    - SYS_LOAD_HISTORY
        - COPY コマンドの詳細が含まれています。
    - SYS_LOAD_ERROR_DETAIL
        - COPY コマンド エラーの詳細が含まれます。

## COPY JOB (preview)
- COPY JOB コマンドは COPY コマンドの拡張であり、S3バケットからのデータのロードを自動化します。
- COPYジョブを作成すると、Redshiftは、新しいS3ファイルが指定されたパスに作成されたことを検出し、自動的にロードします。
- データのロード時には、元のCOPYコマンドで使用されたものと同じパラメーターが使用されます。 Redshift は、ロードされたファイルを追跡して、ロードが1回だけであることを確認します。

# 実践
## 事前準備
### S3バケットとデータの準備
1. S3バケットを作成します。
1. 以下のようなテストデータを作成します。
    - ３つのファイル
    - それぞれidとvalueを持つJSONフォーマット
```
$ ls -ltr                                  
total 24
-rw-r--r--  1 azumaha  staff  28 12 15 22:48 1.json
-rw-r--r--  1 azumaha  staff  28 12 15 22:49 2.json
-rw-r--r--  1 azumaha  staff  28 12 15 22:49 3.json
$ for nm in `ls`;do echo $nm ; cat $nm;done
1.json
{"id": 1,"value": "111111"}
2.json
{"id": 2,"value": "222222"}
3.json
{"id": 3,"value": "333333"}
$
```

### IAMロールの作成
1. [IAM permissions for COPY, UNLOAD, and CREATE LIBRARY](https://docs.aws.amazon.com/redshift/latest/dg/copy-usage_notes-access-permissions.html#copy-usage_notes-iam-permissions)の要件に基づき、IAMポリシーとロールを作成します。

## 動作確認
### クラスタの作成
1. Redshiftのプロビジョニングされたクラスターダッシュボードで「Create preview cluster」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/948e349a-6f49-c273-c1d9-d4c424e8f8d7.png)

1. Cluster preview clusterの画面で以下を入力し「クラスターを作成」をクリックします。
    - Preview trackに「preview_autocopy_2022」
    - 上記以外はデフォルト
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/b5c45e52-e97f-8123-1dbb-cfcb42f4fa82.png)

1. しばらくすると、起動されます。
1. IAMロールの管理で作成したIAMロールを関連付けます。

### Redshiftのスキーマとテーブルを作成
1. クエリエディタを開きます。
1. スキーマを作成します。（必須ではありません。）
    ```
    create schema auto_copy;
    ```

1. テーブルを作成します。
    ```
    CREATE TABLE "auto_copy"."id_table"(
    id INT
    ,value VARCHAR(64)
    )
    DISTSTYLE AUTO
    ;
    ```
1. テーブル定義を確認します。
    ```
    select * from SVV_ALL_COLUMNS where schema_name='auto_copy' and table_name ='id_table';
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/a28cb946-8dd5-5b01-df9c-cdc51d2cc8b9.png)

### ジョブの作成〜動作確認
1. 以下のコマンドを実行します。
    ```
    COPY auto_copy.id_table
    FROM 's3://{バケット名}/{フォルダ名}'
    IAM_ROLE 'arn:aws:iam::{AWSアカウント}:role/{IAMロール}' 
    FORMAT AS JSON 'auto'
    JOB CREATE id_json_copy_job
    AUTO ON;
    ```

1. ジョブを確認します。登録されました。
    ```
    select * from SYS_COPY_JOB

    ===
    job_id,job_name,job_owner,table_id,data_source,iam_role,copy_query,is_auto,on_error_suspend
    105725,id_json_copy_job,100,105715,s3://{バケット名}/{フォルダ名},arn:aws:iam::{AWSアカウント}:role/{IAMロール},COPY auto_copy.id_table\nFROM 's3://{バケット名}/{フォルダ名}'\nIAM_ROLE '' \nFORMAT AS JSON 'auto'\nJOB CREATE id_json_copy_job\nAUTO ON;,true,true
    ```

1. ファイルを配置する前にテーブルに登録されているレコードを確認します。未だ登録されていません。
    ```
    select * from auto_copy.id_table;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/8150d6a4-5529-dad4-5a2f-3cb3ddc42446.png)

1. S3にファイルを配置します。先程作成した1.jsonを配置します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/91d877bc-8bf9-4e99-f822-6a37e9fbbd9c.png)

1. ファイル配置後にレコードを確認します。登録されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/e1f2a496-c97d-b5e9-d130-d5b978b26217.png)

1. ジョブのロード結果を確認します。
    ```
    select * from stl_load_commits where filename like '%1.json%' 
    ```
### ファイルの追加〜動作確認
1. 配置前のS3とテーブルの状態を確認します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/725dd317-bf94-4c03-d08c-73c6f45d267f.png)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/5758f4c7-c7fd-734e-44ad-b118f5f27105.png)

1. ファイルを配置します。(2.jsonを配置します。)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/58d1b9c8-1c4d-039b-af04-f3dc0362f745.png)

1. 配置後のテーブルの状態を確認します。COPYが成功しレコードが追加されています。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/45778fda-fce3-f7bf-4cd2-e495f41ad4b3.png)


### AUTO COPYをOFF〜動作確認
AUTO COPYをOFFに変更し、自動でコピーされないか確認します。
1. 以下のSQLでOFFに変更します。
    ```
    COPY JOB ALTER id_json_copy_job 
    AUTO OFF
    ```
1. ジョブを確認します。OFFになりました。
    ```
    select job_name,is_auto from SYS_COPY_JOB

    ===
    job_name is_auto
    id_json_copy_job	false
    ```

1. ファイルを配置します。(3.jsonを配置します。)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/bbaf499f-72d4-a779-d8c1-e05878706c65.png)


1. 配置後のテーブルの状態を確認します。OFFにしているので、想定通りレコードは登録されていません。（ID=3のレコードは見当たりません。）
    ```
    select * from auto_copy.id_table;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/e69c086d-2caa-d4e0-93b2-9e7f2e943306.png)


### ジョブのマニュアル実行
上記の「AUTO COPYをOFF〜動作確認」でOFFに変更した状態で、ジョブを実行しレコードが登録されるか確認します。
1. 以下のコマンドでジョブを実行します。
    ```
    COPY JOB RUN id_json_copy_job
    ```
1. 配置後のテーブルの状態を確認します。レコードが登録されました。
    ```
    select * from auto_copy.id_table;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/25bf6005-daab-0cc8-a59c-02e814eedf10.png)

### ジョブのDDLを確認
1. 以下のコマンドでDDLが確認できます。
    ```
    COPY JOB SHOW id_json_copy_job
    ===
    COPY auto_copy.id_table FROM 's3://{バケット名}/{フォルダ名}' IAM_ROLE 'arn:aws:iam::{AWSアカウント}:role/{IAMロール}' FORMAT AS JSON 'auto' JOB CREATE id_json_copy_job AUTO ON;
    ```

### ジョブの削除
1. 以下のコマンドでジョブを削除します。
    ```
    COPY JOB DROP id_json_copy_job
    ```
1. 削除されたことを確認します。
    ```
    select * from SYS_COPY_JOB
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/c34d7d1e-42fc-b51f-b4d4-f93a9786a941.png)


### ファイルがある状態でジョブを作成
ファイルが配置されたままで、ジョブを作成した場合、自動でロードされるか確認します。
1. レコードを確認します。(事前にTruncateしているので0件です。)
    ```
    select * from auto_copy.id_table;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/0e831d75-babd-4b5e-69dc-b98f8bf0fff5.png)

1. ジョブを確認します。登録されていません。
    ```
    select * from SYS_COPY_JOB
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/682833bc-7b66-3e63-9105-300ac8722e7f.png)

1. S3を確認します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/8220eea5-4070-77c7-6825-4a7550febfad.png)

1. ジョブを作成します。
    ```
    COPY auto_copy.id_table
    FROM 's3://{バケット名}/{フォルダ名}'
    IAM_ROLE 'arn:aws:iam::{AWSアカウント}:role/{IAMロール}' 
    FORMAT AS JSON 'auto'
    JOB CREATE id_json_copy_job
    AUTO ON;
    ```

1. ジョブを確認します。
    ```
        select * from SYS_COPY_JOB
    
        ===
        job_id,job_name,job_owner,table_id,data_source,iam_role,copy_query,is_auto,on_error_suspend
        105737,id_json_copy_job,100,105715,s3://{バケット名}/{フォルダ名},arn:aws:iam::{AWSアカウント}:role/{IAMロール},COPY auto_copy.id_table\nFROM 's3://{バケット名}/{フォルダ名}'\nIAM_ROLE '' \nFORMAT AS JSON 'auto'\nJOB CREATE id_json_copy_job\nAUTO ON;,true,true
    ```
1. レコードを確認します。COPYされているようです。(3件増えています。)
    ```
    select * from auto_copy.id_table;
    ```
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/ad15e59f-7827-0b7a-0c57-635a51cc5f2b.png)

# 考察
今まで、COPYにはジョブなどでRedshift外から実行する必要がありました。AUTO COPYによりそのような手間が減り運用が楽になりました。
GA後に、大量データをコピーしパフォーマンスを計測しようと思います。

# 参考
https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/

https://docs.aws.amazon.com/redshift/latest/dg/loading-data-copy-job.html

https://docs.aws.amazon.com/redshift/latest/dg/r_COPY-JOB.html