このガイドでは、Redshift の ［COPY］コマンドについて、Redshift データベースにデータをインポートする際の使い方やそのシンタックス、そしてその際遭遇する可能性のあるトラブルについて見ていきます。

目次

Redshift の COPY コマンドとは

Redshift の COPY コマンドは、おかしなことに、あるソースからデータをコピーして、それを Amazon Redshift データベースに格納します。そして、ソースは以下の項目のいずれかになります：

  1. Amazon S3 バケット(最も一般的なソース)
  2. Amazon EMR クラスタ
  3. Amazon DynamoDB テーブル
  4. 外部ホスト（SSH経由）

テーブルに既にデータがある場合、COPY コマンドはテーブルの下に行を追加しますが、Redshift の COPY コマンドを使う上で注意すべき点が以下のようにいくつかあります：

  • あらゆるソースからの１つの入力行の最大サイズは、４MBである。
  • Amazon Redshift Spectrum の外部テーブルは読み取り専用で、外部テーブルへの COPY はできない。
  • COPY コマンドは、既存の行を変更することなく、新しいデータをテーブルの末尾に追加する。
  • Amazon Redshift では、プライマリキーは強制されないことから、重複排除はアプリケーションでの処理が必要ということになる。

この最後の注意点においては、Amazon Redshift でレコードを重複排除する方法として「upsert（Udate + Insert）」オペレーションの使用が推奨されています。では、次のセクションで、upsert について詳しく見ていくことにしましょう。

Redshift COPY：シンタックス と パラメータ

COPYコマンドはとてもシンプルであり、使用には、以下の３つが必要です：

  1. データをコピーしたいテーブルの名前
  2. データソース（上記のデータソースの候補のリストを参照）
  3. データソースへのアクセスの権限（通常は IAM ロール、または IAM ユーザーのアクセス ID と シークレットキーのいずれか）。

ソースファイルとテーブルの列が自然に並ばない場合は、列リストを含めることで列の順序を指定することができます。

以下に、s3 のテキストファイルから Redshift のテーブルにデータをコピーする簡単な例をご紹介します：

copy catdemo
from 's3://awssampledbuswest2/tickit/category_pipe.txt'
iam_role 'arn:aws:iam:::role/'
region 'us-west-2';

ソースファイルとテーブルの列が自然に並ばない場合は、COPYコマンドに列リストを含めることで、次のように列の順序を指定することができます：

copy catdemo (column1, column2, etc.)
from 's3://awssampledbuswest2/tickit/category_pipe.txt'
iam_role 'arn:aws:iam:::role/'
region 'us-west-2';

AWSは、ソースが UTF-8 で、パイプ区切りのテキストファイルであることを想定していますが、もしそうでない場合は、FORMAT AS パラメータを使って知らせる必要があります。正確な使用方法については、こちらで詳しく説明していますので、何か質問があればコメントで教えてください！

様々な s3 リージョンからデータを格納する方法

自分の S3 バケットから Redshift クラスタにデータをコピーしようとすると、トラブルに見舞われる人がいるかもしれませんが、それは S3 バケットが Redshiftクラスタがあるリージョンとは違うリージョンで作成されている場合に起こりやすいです。表示されるエラーメッセージは、必ずしも明確なものではなく、非常に混乱する可能性がありますが、幸い、そのエラーは、追加のパラメータを追加することで簡単に回避できます。

Redshift COPY の性能を上げる： 不要なクエリの排除

デフォルトでは、Redshift の COPY コマンドは、COPY トランザクションの一部として、以下の２つのコマンドを自動的に実行します：

  1. “COPY ANALYZE PHASE 1|2”
  2. “COPY ANALYZE $temp_table_name”

Redshift はこのコマンドを実行して、コピーされるデータの正しいエンコーディングを決定するため、テーブルが空の場合に有用な場合があります。ただし以下のようなケースでは、余分なクエリは無駄なので排除が必要です：

  • Upsert 操作の一部としてなど、一時表への COPY の実行
  • テーブルに既にデータがある場合の COPY の実行：Redshift では、既存のテーブルのデータエンコーディングの変更はできず、COPY コマンドでより良いエンコーディングスタイルが存在すると判断しても、ディープコピー操作を行わないと、テーブルのエンコーディングの変更は不可能。

以下の例では、COPY コマンド１つで「analyze compression」コマンドが１８個と「copy analyze」コマンドが１つ生成されます：

（Image）

余分なクエリは、Amazon Redshift 上で実行されている他のクエリに性能の問題を引き起こす可能性があります。例えば、WLM （ワークロードマネージャ）キューのスロット数を飽和させ、それによって他のクエリ全てに待ち時間を発生させる可能性があります。

その場合、COPY コマンドのパラメータに「COMPUPDATE OFF」と「STATUPDATE OFF」を追加し、Upsert 操作時にその機能を無効にすることで解決します。以下は、そのようなオプションを設定した COPY コマンドの例です：

1

2

3

4

5

6

7

8

9

-- Load data into the staging table

 

COPY users_staging (id, name, city)

 

FROM 's3://.......'

 

CREDENTIALS 'aws_access_key_id=xxxxxxx;aws_secret_access_key=xxxxxxx'

 

COMPUPDATE OFF STATUPDATE OFF;

S3ServiceException エラー

特別なオプションなしで通常の COPY コマンドが動作するには、S3 バケットが Redshift クラスタと同じリージョンにあることが必要です。

copy one_column ("number")
from 's3://test-buckets/region_test'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=XXXXXXXXXXX'
delimiter '\t';

そうでない場合は、次のようなエラーが表示されることがあります：

ERROR:  S3ServiceException:The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.,Status 301,Error PermanentRedirect,Rid
DETAIL:
  -----------------------------------------------
  error:  S3ServiceException:The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.,Status 301,Error PermanentRedirect,Rid
  code:      8001
  context:   Listing bucket=test-buckets prefix=region_test
  query:     12715653
  location:  s3_utility.cpp:529
  process:   padbmaster [pid=31787]
  -----------------------------------------------

このエラーメッセージは少し曖昧です。実際には、COPY コマンドに別のオプションを追加して、S3 バケット領域の確定が必要であることが述べられています。

S3リージョンの指定

これを行うには、COPY コマンドに［REGION］オプションの追加が必要です。例えば、S3 バケットが US-WEST-2 リージョンにあった場合、次のように COPY コマンドに追加します：

copy one_column ("number")
from 's3://test-buckets/region_test'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=XXXXXXXXXXX'
delimiter '\t'
region as 'us-west-2';

この方法は、Redshift クラスタのリージョンが明確でない場合の、Redshift クラスタのリージョンの確認にも利用できます。COPY コマンドの引数 REGION を使わずに COPY が成功した場合、Redshift クラスタが S3 バケットと同じリージョンであることの確認ができます。

Redshift の使い方やデータウェアハウスの管理についてさらに詳しく知りたい方は、サポートチームにお問い合わせいただくか、コチラから無料トライアルをぜひご体験下さい。