S3からRedshiftへ自動コピー（preview）の動作確認

2022年11月にAmazon S3 から Amazon Redshift へのデータの読み込みを簡素化する自動コピー機能のプレビューの提供が開始されました。
https://aws.amazon.com/jp/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/

すでに使ってみた等の簡単な動作確認は他の記事でも紹介されていますが、振る舞いについて細かく確認していきます。



**注意**
プレビュー版の終了時期が設定されているのでご注意ください。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY-JOB.html
>パブリックプレビューは 2023 年 10 月 15 日に終了します。プレビュークラスターは、プレビュー終了 2 週間後に自動的に削除されます。




# 概要
自動コピー機能は[COPYコマンド](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY.html)を拡張したものです。

COPYコマンドを発行すると、COPYジョブとしてテーブル名などのパラメータと共に保存されます。

従来はコピーのたびにジョブの実行が必要でしたが、今回のプレビュー機能により自動で継続的に取り込むことが可能になります。

詳細はドキュメントをご確認ください。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY-JOB.html

# 準備
すでに実行できる環境があれば飛ばしてください。

### S3バケットの作成
任意のバケット、フォルダを作成します。
### IAMロール作成
COPY コマンドを実行するためにロール・ポリシーが必要です。
・Redshiftテーブルへロードするための INSERT権限
・S3バケットへアクセスする権限

## プレビュー版のクラスター構築
### クラスタの作成
	• IAMロールを関連付ける
1. Redshiftのクラスター作成画面の上部に表示される「Create preview cluster」を選択します。
ここで作成したIAMロールを紐づけます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/3812ce19-930c-3771-3e66-01608cc0389b.png)

2. クラスター設定で「Preview track」から「preview_2022」を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/a7e19868-9a8d-c77c-635a-d0839a47fe21.png)

3. クラスターを起動します。
クラスターの起動には数分かかります。

### テーブルの作成
クラスターが起動できたら、テーブルを作成します。
```テーブル作成.sql
CREATE TABLE "public"."table-sample" (
    id INTEGER
  , value VARCHAR(100) 
);
```

### テストデータ作成
2カラムでヘッダーを含む1行のCSVを3種類用意しています。
```1.csv
id,value
1,aaa
```
```2.csv
id,value
2,bbb
```
```3.csv
id,value
3,ccc
```

## 基本
### ①自動コピーのCOPYコマンドを実行

自動コピー機能を実行するCOPYコマンドです。
パラメータは必要に応じて変更してください。参考：[COPY 構文概要](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY.html#r_COPY-syntax)

```COPY JOB (プレビュー).sql
COPY "スキーマ名"."テーブル名"
FROM 'S3バケットディレクトリ'
IAM_ROLE 'IAMロール'
IGNOREHEADER 1
JOB CREATE "ジョブ名"
AUTO ON;
```
### ②基本動作
今回の例では以下の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/66ee5ba2-74c0-79b7-1184-915a5bf14f6d.png)

作成したテーブルは空の状態です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/d331295c-33e1-fb09-e657-8d090a4b2872.png)

COPYジョブに指定したS3のフォルダにテストファイルを置きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/d66d44a8-0c33-de02-0de4-250ff6697a52.png)

その後、テーブルを確認するとCSVファイルの中身が取り込まれています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/ce37daff-c5cc-eb6e-ba94-eee2b05e462c.png)


### ③JOBのステータスの確認方法
COPYジョブの内容や実行履歴は以下のコマンドで確認できます。
|コマンド|内容|
|:--|:--|
|SYS_COPY_JOB|現在定義されている各 COPY JOB の一覧（プレビュー）|
|SYS_LOAD_HISTORY|ロード履歴|
|STL_LOAD_COMMIT|ロード履歴の詳細|
|STL_LOAD_ERRORS|ロードエラー概要|
|SYS_LOAD_ERROR_DETAIL|ロードエラーの詳細|

### ④対応するファイル形式
COPYコマンドで使用できる取り込めるファイル形式は以下の通りです。
- FORMAT
- CSV
- DELIMITER
- FIXEDWIDTH
- SHAPEFILE
- AVRO
- JSON
- ENCRYPTED
- BZIP2
- GZIP
- LZOP
- PARQUET
- ORC
- ZSTD

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-format.html
## 詳細
### ①バケットとジョブの状態による動作
色々なパターンでの動作を確認します。

- 複数ファイルをまとめて追加する
2.csvと3.csvを追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/95ea80e6-cdee-4de5-a2d5-4a2da0366ec5.png)
追加した行が増えていますが、登録順はランダムになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/ed3dbeca-f17e-1aad-0c0a-6659c706bc70.png)

- ファイルを上書きする
3.csvの内容を「valueをddd」として書き換えた状態で上書きしてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/440ceac6-3ca2-8583-1f47-6de803aedad5.png)
valueがcccのままとなり、更新されません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/baa75bab-0f74-e8db-3260-98938dc8d141.png)

- ファイルを削除する
2.csvのファイルを削除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/df872fcc-daee-31d7-fc76-0d2b413020cb.png)
こちらも変化は置きません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/7a5a0688-da2a-f5b1-4ba3-ec31f2a300fd.png)

確認したパターンをまとめると以下の通りでした。
||ジョブON|ジョブOFF→ON|
|:--|:--|:--|
|ファイルが既に置かれている場合|〇|〇|
|ファイルを上書きした場合|×|×|
|ファイルを削除した場合、削除されるか|×|×|

OFFにした後、一度読み取ったものを削除し、再度ONにしても読み取りません。
すでに読み取ったファイルパスは除外されるため、ジョブの初期化が必要となります。

### ②ファイルの形式やカラムが変わった場合


- ファイル形式が変わった場合

基本的にはないと思われますが、誤って置かれた場合に読み取られないかどうか、ファイル形式が変わった場合の動作を確認します。

JSONファイルを追加してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/5d5a84b2-4de8-b124-5345-5d5eb35bff5b.png)
テーブルに変化はありません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/0e2e09b7-a176-b179-a1bd-9ca9b6892308.png)
HISTRYを確認すると、4.jsonは認識していますがフォーマットはCSVであるため取り込まれません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/e721877e-00b3-be7d-a8ca-204550487dab.png)

- カラムの並び順を入れ替える

カラムの並び順を変えてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/f925d873-826a-af35-3fcd-cab4674ec613.png)
テーブルに変化がなくERRORSを確認すると、
ヘッダーは考慮されずvalue列をid列として認識したためTypeエラーとなっていました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/c6bab59e-891b-5600-d906-f3b21c9a65c4.png)

- カラムを追加する
カラムを新たに追加してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/567846d0-fa58-3c3a-5244-8d42af46ad85.png)

Extra column(s) found のエラーとなり取り込まれません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/14d8dbe0-522a-8312-f7ed-eb54b3765e06.png)

これらの動作はCOPYコマンド自体によるものであり、
列名を指定する設定を行うことで取り込むことも可能です。

https://dev.classmethod.jp/articles/amazon-redshift-specify-column-names-when-copying/

### ③同一バケット内の別フォルダのファイルで同一テーブルを更新させる場合
3カラムでヘッダーを含む5行のCSVを2種類用意しています。

```sample1.csv
ID,Name,Category
0001,test0001,01
0002,test0002,04
0003,test0003,04
0004,test0004,03
0005,test0005,03
```
```sample2.csv
ID,Name,Category
0006,test0006,04
0007,test0007,01
0008,test0008,02
0009,test0009,04
0010,test0010,04
```
#### テーブルに制約を付けない場合
- コピー先のテーブルを作成
s3のファイルの内容をコピーするテーブルを作成します。
```CreateTable.sql
CREATE TABLE "AutoCopyTest_08"(
			    "ID" CHAR(4) NULL,
			    "Name" VARCHAR(10) NULL,
			    "Category" CHAR(3) NULL
) ;
```
データは何も挿入しない状態でcreateしました。
- S3の同一バケット内にフォルダを2つ作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/1d3bfd10-2f10-c0d1-36d0-0f3f4bbbf00b.png)
- copyジョブを作成
それぞれのフォルダから同一のテーブルを更新するcopyジョブを作成します。
```CopyJob01.sql
COPY "autocopytest_08"
FROM 's3のバケット/auto-copy-01-01/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-01"
AUTO ON
```
```CopyJob02.sql
COPY "autocopytest_08"
FROM 's3のバケット/auto-copy-01-02/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-02"
AUTO ON
```
- それぞれのフォルダに別々のファイルを配置
作成した2つのs3のフォルダに、それぞれデータが異なるファイルを配置します。
「auto-copy-01-01」フォルダには sample1.csv、
「auto-copy-01-02」フォルダには sample2.csv を配置しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/352e129c-9c25-db4c-544d-f273cd8b0144.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/0616e853-4bbb-8d5c-2338-680a44cc8ab1.png)
テーブルを見ると、2ファイル分のデータが同一テーブルにinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_08"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/a9e74eb5-0903-2721-138c-7a1a65473cfe.png)
「auto-copy-01-01」フォルダに、「auto-copy-01-02」フォルダに配置したファイル sample2.csv と全く同一のファイルを配置します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/eae9440d-3849-742d-1b77-fe87ec2de38d.png)
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_08"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/71e5bfbf-036d-06e9-66b5-f74954b9cb56.png)
結果として、同じファイルの内容が2度重複してテーブルにinsertされたことになります。

#### テーブルに一意キー制約を付けた場合
- コピー先のテーブルを作成
s3のファイルの内容をコピーするテーブルを作成します。
```CreateTable.sql
CREATE TABLE "AutoCopyTest_08_unique"(
			    "ID" CHAR(4) NULL,
			    "Name" VARCHAR(10) NULL,
			    "Category" CHAR(3) NULL
			    UNIQUE ("ID")
) ;
```
データは何も挿入しない状態でcreateしました。
- S3の同一バケット内にフォルダを2つ作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/4aa19531-d2ff-e46f-ddfb-d09f38477a57.png)
- copyジョブを作成
それぞれのフォルダから同一のテーブルを更新するcopyジョブを作成します。
```CopyJob01_unique.sql
COPY "autocopytest_08_unique"
FROM 's3のバケット/auto-copy-01-01/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-01-unique"
AUTO ON
```
```CopyJob02_unique.sql
COPY "autocopytest"."autocopytest_08_unique"
FROM 's3のバケット/auto-copy-01-02/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-02-unique"
AUTO ON
```
- それぞれのフォルダに別々のファイルを配置
作成した2つのs3のフォルダに、それぞれデータが異なるファイルを配置します。
「auto-copy-01-01」フォルダには sample1.csv、
「auto-copy-01-02」フォルダには sample2.csv を配置しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/b98a03a7-1775-342e-3613-d8f91f7674c1.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/6322a74d-b06d-a5eb-f710-81b8b0a37b9d.png)
テーブルを見ると、2ファイル分のデータが同一テーブルにinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_08_unique"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/62986370-fd21-ff27-1dfe-916c325926b0.png)
「auto-copy-01-01」フォルダに、「auto-copy-01-02」フォルダに配置したファイル sample2.csv と全く同一のファイルを配置します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/7491db86-85cc-e14f-34a9-d55167a97e07.png)
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_08_unique"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/0dde74cf-1bb8-50e8-accd-774b1cc5729e.png)
結果として、同じファイルの内容が2度重複してテーブルにinsertされたことになります。
RedShift上での一意キー制約は意味をなさないので、重複したファイルの登録を避けることはできませんでした。

### ④別バケットのファイルで同一テーブルを更新させる場合
3カラムでヘッダーを含む5行のCSVを2種類用意しています。
```sample1.csv
ID,Name,Category
0001,test0001,01
0002,test0002,04
0003,test0003,04
0004,test0004,03
0005,test0005,03
```
```sample3.csv
ID,Name,Category
0011,test0011,01
0012,test0012,04
0013,test0013,04
0014,test0014,03
0015,test0015,04
```
#### テーブルに制約を付けない場合
- コピー先のテーブルを作成
s3のファイルの内容をコピーするテーブルを作成します。
```CreateTable.sql
CREATE TABLE "AutoCopyTest_09"(
			    "ID" CHAR(4) NULL,
			    "Name" VARCHAR(10) NULL,
			    "Category" CHAR(3) NULL
) ;
```
データは何も挿入しない状態でcreateしました。
- S3にバケットを2つ作成
今回は赤枠内の2バケットを使用します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/41cfd445-0579-d48e-5486-829e991f45a0.png)
- copyジョブを作成
それぞれのバケットから同一のテーブルを更新するcopyジョブを作成します。
```CopyJob03.sql
COPY "autocopytest_09"
FROM 's3://staging-auto-copy-01/バケット内のフォルダ/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-03"
AUTO ON
```
```CopyJob04.sql
COPY "autocopytest_09"
FROM 's3://staging-auto-copy-02/バケット内のフォルダ/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-04"
AUTO ON
```
- それぞれのフォルダに別々のファイルを配置
作成した2つのs3のフォルダに、それぞれデータが異なるファイルを配置します。
「staging-auto-copy-01」バケットには sample1.csv、
「staging-auto-copy-02」バケットには sample3.csv を配置しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/15ca596c-6038-366e-04e7-877ceab1c712.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/03b90877-fde2-ae68-5c22-ced146fdea5b.png)
テーブルを見ると、2バケット分のデータが同一テーブルにinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_09"
``` 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/734068ba-5d25-bb6d-8ead-76772b72f81c.png)

「staging-auto-copy-01」バケットに、「staging-auto-copy-02」バケットに配置したファイル sample3.csv と全く同一のファイルを配置します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/a4aa62bf-3761-2c53-5b4d-11e0e7b01a8c.png)
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_09"
``` 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/542b88f3-ba6b-3fb7-5df7-e0d51e513507.png)
結果として、同じファイルの内容が2度重複してテーブルにinsertされたことになります。
④と同じ結果となりました。

#### テーブルに一意キー制約を付けた場合
- コピー先のテーブルを作成
s3のファイルの内容をコピーするテーブルを作成します。
```CreateTable.sql
CREATE TABLE "AutoCopyTest_09_unique"(
			    "ID" CHAR(4) NULL,
			    "Name" VARCHAR(10) NULL,
			    "Category" CHAR(3) NULL
			    UNIQUE ("ID")
) ;
``` 
データは何も挿入しない状態でcreateしました。
- S3にバケットを2つ作成
今回は赤枠内の2バケットを使用します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/117d97d8-7f16-3e28-9c0a-a460c2b81d29.png)
- copyジョブを作成
それぞれのバケットから同一のテーブルを更新するcopyジョブを作成します。
```CopyJob03_unique.sql
COPY "autocopytest_09_unique"
FROM 's3://staging-auto-copy-01/バケット内のフォルダ/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-03-unique"
AUTO ON
``` 
```CopyJob04_unique.sql
COPY "autocopytest_09_unique"
FROM 's3://staging-auto-copy-02/バケット内のフォルダ/'
IAM_ROLE 'IAMロール'
FORMAT AS CSV 
DELIMITER ',' 
QUOTE '"'
IGNOREHEADER 1
JOB CREATE "staging-auto-copy-job-08-04-unique"
AUTO ON
```
- それぞれのフォルダに別々のファイルを配置
作成した2つのs3のフォルダに、それぞれデータが異なるファイルを配置します。
「staging-auto-copy-01」バケットには sample1.csv、
「staging-auto-copy-02」バケットには sample3.csv を配置しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/3891088a-fd8d-78d8-f1d3-71c1c85adbf1.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/309798fc-bbbd-9a3a-f4b1-f937623a93f9.png)
テーブルを見ると、2バケット分のデータが同一テーブルにinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_09_unique"
``` 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/9568d99c-dd30-976c-5421-6aa902542c57.png)

「staging-auto-copy-01」バケットに、「staging-auto-copy-02」バケットに配置したファイル sample3.csv と全く同一のファイルを配置します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/f618dcb6-c831-60ba-6222-298049cf1a9d.png)
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。
```結果確認.sql
select * from "AutoCopyTest_09_unique"
``` 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3345257/f4580dd9-36cd-e7ad-936c-24a773e70c90.png)
結果として、同じファイルの内容が2度重複してテーブルにinsertされたことになります。
④と同じ結果となりました。
RedShift上での一意キー制約は意味をなさないので、重複したファイルの登録を避けることはできませんでした。

# 最後に
今回検証に用いた自動コピーはCOPYコマンドの拡張版であり、ロードするファイルに合わせてパラメータを設定することで調整が可能です。

従来はロードをGlue等のETLジョブ等で実行する必要がありました。自動コピーによって直接Redshiftでロードが減ります。ただ現在はプレビュー版ですので、一般公開を待ちましょう。

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



---
株式会社ジールでは、初期費用が不要で運用・保守の手間もかからず、ノーコード・ローコードですぐに手元データを分析可能なオールインワン型データ活用プラットフォーム「ZEUSCloud」を提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/