Redshift auto copy from s3 (Preview) 試してみた

「Redshift auto copy from s3」はまだPreviewなので、2022/12/05現在の動作としてご覧ください
[What's Newの記事](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/)

※作業はすべて東京リージョンで実施


## 事前セットアップ

* S3バケット：demo-redshift-s3copy で作成
* IAMロール：[こちら](https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/authorizing-redshift-service.html)に沿ってdemo-redshift-s3copyjobで作成
* Redshift公式ページにある[TICKITというサンプルデータ](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_sampledb.html)をダウンロードする。その中のListingテーブル(ファクトテーブル)をテストデータとして使う。s3からRedshiftへのロードを段階的に確認したいため、ダウンロードしたlisting.txtファイルを以下のように分割しておく。
  * listing_1.txtは最初の3行だけのファイル
  * listing_2.txtはその次の5行だけのファイル
  * listing_3.txtはその次の7行だけのファイル

## Previw用のRedshift cluster作成

本機能をためすにはPreview用のRedshift Clusterを作る必要があります。クラスタは以下の公式ドキュメントの冒頭の流れで特にハマることなく作成できます
[Continuous file ingestion from Amazon S3 (preview)](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/loading-data-copy-job.html) 

#### クラスタ作成手順

2022/12/5 時点での手順は以下の流れでした。

マネコンからRedshiftのページに行き、メニューで「プロビジョニングされたクラスタダッシュボード」をクリックすると、右上に[Create preview cluster]というボタンがあります。それをクリックします。

![スクリーンショット 0004-12-05 13.10.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/5ee2f5c6-a643-7b61-149c-ee8e63f803fd.png)

クラスタ作成時の選択項目は以下です

* 名前: preview(任意)
* preview track: preview_autocopy_2022
* 管理者パスワード: (任意)
* 関連付けられたIAMロール: demo-redshift-s3copyjob (事前に作成したロール)

これくらいでサクッと作れます

## auto copy from s3の説明

[Continuous file ingestion from Amazon S3 (preview)](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/loading-data-copy-job.html)
Redshiftは、COPY コマンドで指定されたS3パスに新しいファイルが追加されたことを検出し、COPY コマンドが自動的に実行されます。
Redshift は、ロードされたファイルを追跡します。 Redshift は、COPY コマンドごとにバッチ処理されるファイルの数を決定します。結果の COPY コマンドは、システム ビューで確認できます

[COPY JOB (preview)](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY-JOB.html)

#### 構文

"job-name"は、COPY ジョブの名前。
[AUTO ON | OFF] は、自動的にロードされるかどうかを示す句でAutoがデフォ。OFFの場合は以下の「COPY JOB RUN」で手動実行できる(未確認)

```sql:
    --コピー ジョブを作成します。 COPY コマンドのパラメーターは、コピー ジョブと共に保存されます。
    COPY copy-command JOB CREATE job-name
    [AUTO ON | OFF]
    --コピー ジョブの構成を変更します。
    COPY JOB ALTER job-name
    [AUTO ON | OFF]
    --コピー ジョブを実行します。保存された COPY コマンド パラメータが使用されます。
    COPY JOB RUN job-name
    --リスト
    COPY JOB LIST
    --コピージョブの詳細
    COPY JOB SHOW job-name
    --コピージョブの削除
    COPY JOB DROP job-name
```

#### 状態確認

```sql:
--(Preview)現在定義されている各 COPY JOB の行が含まれています。
select * from sys_copy_job;
--エラー
select * from stl_load_errors;
--トラシュー時の調査データ
select * from stl_load_commits;
--コマンド詳細
select * from sys_load_history;
--コマンドエラー詳細
select * from sys_load_error_detail;

--COPY JOBによってロードされたファイルのリストを取得。<job_id>を置き換えて実行
SELECT job_id, job_name, data_source, copy_query,filename,status, 
curtime FROM sys_copy_job copyjob JOIN stl_load_commits loadcommit 
ON copyjob.job_id = loadcommit.copy_job_id WHERE job_id = <job_id>;
```

### 注意点

* COPY コマンドのオプションは、実行時まで検証されません。たとえば、IAM_ROLE または Amazon S3 データ ソースが無効な場合、COPY JOB の開始時にランタイム エラーが発生します。
* COPY JOB は、クラスターに関連付けられたデフォルトの IAM ロールをサポートしていません。 COPY コマンドで IAM_ROLE を指定する必要があります。
* クラスターが一時停止している場合、COPY JOBS は実行されません。
* ロードされた COPY コマンド ファイルとロード エラーを照会するには、STL_LOAD_COMMITS、STL_LOAD_ERRORS、STL_LOADERROR_DETAIL を参照する。

## やってみる

以下のコマンド実行は全てRedshift Query Editor v2で実施

### 1つ目のファイルアップロード確認

Redshiftにlistingテーブルを作成

```sql:
create table listing( listid integer not null distkey, sellerid integer not null
, eventid integer not null, dateid smallint not null sortkey, numtickets 
smallint not null, priceperticket decimal(8,2), totalprice decimal(8,2), 
listtime timestamp);
```

Redshiftにコピージョブを作成。これがメインで、これによってS3からデータロードするジョブが作られる。

```sql:
COPY dev.public.listing
FROM 's3://demo-redshift-s3copy/tmp'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/demo-redshift-s3copyjob'
JOB CREATE demo_copy_job
AUTO ON;
```

![スクリーンショット 0004-12-04 21.04.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/78f43ac3-bc39-b67c-b2aa-a8eeacf9f092.png)

S3にファイルアップロードする前なので、テーブルは空っぽです

![スクリーンショット 0004-12-04 21.05.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/21a07651-5145-d54f-1e22-56fd496bc271.png)

3行だけに分割しておいたlisting_1.txtファイルをアップロード。

![スクリーンショット 0004-12-04 21.05.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/1e179305-881e-c119-b9b8-507a14d4b7cb.png)

1分後くらいにRedshift側でSelectクエリすることでデータロードが確認出来ました。 **これで自動でS3からRedshiftにロードされることが確認できました。**

![スクリーンショット 0004-12-04 21.06.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/b695f5ca-750d-41a1-d013-84bb289ad7e3.png)

### システムビューを確認

コピージョブ確認。前述のコピージョブ確認コマンドの実行結果を貼っておきます。適宜ジョブの状態確認に活用できます。

![68747470733a2f2f71696974612d696d6167652d73746f72652e73332e61702d6e6f727468656173742d312e616d617a6f6e6177732e636f6d2f302f32373933322f35343937396337622d343165362d373637652d346365302d3564383264356330343639632e706e67.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/39df30df-c871-c70a-98b3-ca698dcc6c91.png)


![68747470733a2f2f71696974612d696d6167652d73746f72652e73332e61702d6e6f727468656173742d312e616d617a6f6e6177732e636f6d2f302f32373933322f36333634643361622d373963632d346438352d356266322d6430633436656461376537312e706e67.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/47375901-ec39-6478-2d00-a50bd678b57b.png)

ここでjobidを取得できる

![スクリーンショット 0004-12-04 21.11.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/6b65854a-6854-b4f2-d0c5-3d696545fb4c.png)

jobidに106340を指定する

![スクリーンショット 0004-12-04 21.19.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/5bd8ddd6-bda1-3618-2265-a2681bccb6ee.png)

### 2つ目のファイルアップロード

2つ目のlisting_2.txtファイルは5行だけのデータ
![スクリーンショット 0004-12-04 21.23.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/0f4129ad-b96c-ac99-44a0-8258015c69d4.png)

1分くらいで反映された

![スクリーンショット 0004-12-04 21.23.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/30829849-d63c-d27a-4752-501c97f04ce7.png)


### 1つ目のファイルを削除してみる

期待値としては、COPY JOBがs3 copyしてるだけだろうから、Redshiftのデータは消えない
まさか同期は取ってくれないだろう

↓S3からlisting_1.txtファイルを削除してみた。

![スクリーンショット 0004-12-04 21.25.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/5d5073e6-534b-ea1d-a466-5b75957c4ec1.png)

うん。そりゃそうだよねという結果ですが、Redshiftのデータはそのままです

![スクリーンショット 0004-12-04 21.25.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/e1269b07-0849-9ddc-c4f6-7af268b14374.png)

### 同じファイルが2回アップされたらどうなるか？

再度1つ目のファイルをアップロード

![スクリーンショット 0004-12-04 21.27.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/28454c38-4b7b-7fce-8734-67a52cce8cf0.png)

Redshiftにロードされない。これは公式ドキュメントにある「Redshift は、ロードされたファイルを追跡します」のためだと思われる

![スクリーンショット 0004-12-04 21.29.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/6989e402-5617-47f7-a95a-db513b04f4f4.png)

### 同じファイルだが、ファイル名を変更してアップしたらどうなるか？

期待値としては、Redshiftはロードしたデータをファイル名で追跡してそうなので、ファイル名が異なれば同じデータでもロードされる。
1つ目のファイル名を変更してアップしてみた。

![スクリーンショット 0004-12-04 21.30.58 (1).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/2078b6fb-70c2-9bef-eb7b-ec00a79f63b6.png)


ロードされた

![スクリーンショット 0004-12-04 21.31.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/8f15670c-d510-2fa8-07db-10b11a9b0101.png)


sys_copy_jobからCOPY JOBが保持しているファイル名が確認出来る

![スクリーンショット 0004-12-04 21.33.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/27932/9ea4ad23-7ff5-aa6a-9317-e6deb4d7a425.png)


### 大きめのファイルで通常のS3 CopyとCopy Jobでロードの時間の差を確認する

やろうかやるまいか考え中

## 感想

* 簡単に出来るので、今までs3からredshiftへのロードを手組みしてた人には置き換えることで簡潔になる
* エラーなどはうまいことclondwatchと連携してくれてるとよかった。でも便利！