Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ms9617in株式会社ジール

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

Last updated at Posted at 2023-10-03

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

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

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

パブリックプレビューは 2023 年 10 月 15 日に終了します。プレビュークラスターは、プレビュー終了 2 週間後に自動的に削除されます。

概要

自動コピー機能はCOPYコマンドを拡張したものです。

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

  2. クラスター設定で「Preview track」から「preview_2022」を選択します。
    image.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 構文概要

COPY JOB (プレビュー).sql
COPY "スキーマ名"."テーブル名"
FROM 'S3バケットディレクトリ'
IAM_ROLE 'IAMロール'
IGNOREHEADER 1
JOB CREATE "ジョブ名"
AUTO ON;

②基本動作

今回の例では以下の通りです。
image.png

作成したテーブルは空の状態です。
image.png

COPYジョブに指定したS3のフォルダにテストファイルを置きます。
image.png

その後、テーブルを確認するとCSVファイルの中身が取り込まれています。
image.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

詳細

①バケットとジョブの状態による動作

色々なパターンでの動作を確認します。

  • 複数ファイルをまとめて追加する
    2.csvと3.csvを追加します。
    image.png
    追加した行が増えていますが、登録順はランダムになっています。
    image.png

  • ファイルを上書きする
    3.csvの内容を「valueをddd」として書き換えた状態で上書きしてみます。
    image.png
    valueがcccのままとなり、更新されません。
    image.png

  • ファイルを削除する
    2.csvのファイルを削除します。
    image.png
    こちらも変化は置きません。
    image.png

確認したパターンをまとめると以下の通りでした。

ジョブON ジョブOFF→ON
ファイルが既に置かれている場合 〇 〇
ファイルを上書きした場合 × ×
ファイルを削除した場合、削除されるか × ×

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

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

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

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

JSONファイルを追加してみます。
image.png
テーブルに変化はありません。
image.png
HISTRYを確認すると、4.jsonは認識していますがフォーマットはCSVであるため取り込まれません。
image.png

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

カラムの並び順を変えてみます。
image.png
テーブルに変化がなくERRORSを確認すると、
ヘッダーは考慮されずvalue列をid列として認識したためTypeエラーとなっていました。
image.png

  • カラムを追加する
    カラムを新たに追加してみます。
    image.png

Extra column(s) found のエラーとなり取り込まれません。
image.png

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

③同一バケット内の別フォルダのファイルで同一テーブルを更新させる場合

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
  • 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
    image.png
    テーブルを見ると、2ファイル分のデータが同一テーブルにinsertされたことを確認できました。
結果確認.sql
select * from "AutoCopyTest_08"

image.png
「auto-copy-01-01」フォルダに、「auto-copy-01-02」フォルダに配置したファイル sample2.csv と全く同一のファイルを配置します。
image.png
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。

結果確認.sql
select * from "AutoCopyTest_08"

image.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
  • 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
    image.png
    テーブルを見ると、2ファイル分のデータが同一テーブルにinsertされたことを確認できました。
結果確認.sql
select * from "AutoCopyTest_08_unique"

image.png
「auto-copy-01-01」フォルダに、「auto-copy-01-02」フォルダに配置したファイル sample2.csv と全く同一のファイルを配置します。
image.png
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。

結果確認.sql
select * from "AutoCopyTest_08_unique"

image.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
  • 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
    image.png
    テーブルを見ると、2バケット分のデータが同一テーブルにinsertされたことを確認できました。
結果確認.sql
select * from "AutoCopyTest_09"

image.png

「staging-auto-copy-01」バケットに、「staging-auto-copy-02」バケットに配置したファイル sample3.csv と全く同一のファイルを配置します。
image.png
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。

結果確認.sql
select * from "AutoCopyTest_09"

image.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
  • 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
    image.png
    テーブルを見ると、2バケット分のデータが同一テーブルにinsertされたことを確認できました。
結果確認.sql
select * from "AutoCopyTest_09_unique"

image.png

「staging-auto-copy-01」バケットに、「staging-auto-copy-02」バケットに配置したファイル sample3.csv と全く同一のファイルを配置します。
image.png
テーブルを見ると、追加したファイル分のデータがテーブルに追加でinsertされたことを確認できました。

結果確認.sql
select * from "AutoCopyTest_09_unique"

image.png
結果として、同じファイルの内容が2度重複してテーブルにinsertされたことになります。
④と同じ結果となりました。
RedShift上での一意キー制約は意味をなさないので、重複したファイルの登録を避けることはできませんでした。

最後に

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

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

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

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?