背景・目的

先日、「Amazon Redshift now supports auto-copy from 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バケットとデータの準備

S3バケットを作成します。
以下のようなテストデータを作成します。
- ３つのファイル
- それぞれ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ロールの作成

IAM permissions for COPY, UNLOAD, and CREATE LIBRARYの要件に基づき、IAMポリシーとロールを作成します。

動作確認

クラスタの作成

Redshiftのプロビジョニングされたクラスターダッシュボードで「Create preview cluster」をクリックします。
Cluster preview clusterの画面で以下を入力し「クラスターを作成」をクリックします。
- Preview trackに「preview_autocopy_2022」
- 上記以外はデフォルト
しばらくすると、起動されます。
IAMロールの管理で作成したIAMロールを関連付けます。

Redshiftのスキーマとテーブルを作成

クエリエディタを開きます。
スキーマを作成します。（必須ではありません。）
```
create schema auto_copy;
```

テーブルを作成します。

CREATE TABLE "auto_copy"."id_table"(
id INT
,value VARCHAR(64)
)
DISTSTYLE AUTO
;

テーブル定義を確認します。

select * from SVV_ALL_COLUMNS where schema_name='auto_copy' and table_name ='id_table';

ジョブの作成〜動作確認

以下のコマンドを実行します。

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;

ジョブを確認します。登録されました。

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

ファイルを配置する前にテーブルに登録されているレコードを確認します。未だ登録されていません。
```
select * from auto_copy.id_table;
```
S3にファイルを配置します。先程作成した1.jsonを配置します。
ファイル配置後にレコードを確認します。登録されました。

ジョブのロード結果を確認します。

select * from stl_load_commits where filename like '%1.json%'

ファイルの追加〜動作確認

配置前のS3とテーブルの状態を確認します。
ファイルを配置します。(2.jsonを配置します。)
配置後のテーブルの状態を確認します。COPYが成功しレコードが追加されています。

AUTO COPYをOFF〜動作確認

AUTO COPYをOFFに変更し、自動でコピーされないか確認します。

以下のSQLでOFFに変更します。

COPY JOB ALTER id_json_copy_job 
AUTO OFF

ジョブを確認します。OFFになりました。

select job_name,is_auto from SYS_COPY_JOB

===
job_name is_auto
id_json_copy_job	false

ファイルを配置します。(3.jsonを配置します。)
配置後のテーブルの状態を確認します。OFFにしているので、想定通りレコードは登録されていません。（ID=3のレコードは見当たりません。）
```
select * from auto_copy.id_table;
```

ジョブのマニュアル実行

上記の「AUTO COPYをOFF〜動作確認」でOFFに変更した状態で、ジョブを実行しレコードが登録されるか確認します。

以下のコマンドでジョブを実行します。
```
COPY JOB RUN id_json_copy_job
```
配置後のテーブルの状態を確認します。レコードが登録されました。
```
select * from auto_copy.id_table;
```

ジョブのDDLを確認

以下のコマンドで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;

ジョブの削除

以下のコマンドでジョブを削除します。
```
COPY JOB DROP id_json_copy_job
```
削除されたことを確認します。
```
select * from SYS_COPY_JOB
```

ファイルがある状態でジョブを作成

ファイルが配置されたままで、ジョブを作成した場合、自動でロードされるか確認します。

レコードを確認します。(事前にTruncateしているので0件です。)
```
select * from auto_copy.id_table;
```
ジョブを確認します。登録されていません。
```
select * from SYS_COPY_JOB
```
S3を確認します。

ジョブを作成します。

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;

ジョブを確認します。

    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

レコードを確認します。COPYされているようです。(3件増えています。)
```
select * from auto_copy.id_table;
```

考察

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

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