【AWS】RedshiftにデータをロードするCOPYコマンドについて

Amazon Redshift の COPY コマンドについてまとめました。まだ全てを試したことはないので、今後気づいた事は随時更新していきます。

COPYコマンド

Redshift では COPY コマンドを使用して Amazon S3、Amazon EMR クラスター、Amazon DynamoDB、またはリモートホストから SSH 接続を使用して、テーブルにデータをロードすることができます。基本的なコマンドの構文は以下の通りです。ここからロードするデータに合わせてオプションのパラメータを付加します。

COPY table_name
FROM 's3://copy_from_s3_objectpath' | 'emr://emr_cluster_id/hdfs_filepath' | 'dynamodb://table_name'
CREDENTIALS 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>';

 COPY - Amazon Redshift

パラメータ一覧

COPY コマンドに付加できるパラメータは以下の通りです。ビッグデータをロードする際に適したパラメータが色々と用意されています。

データ形式 パラメータ	機能
CSV	CSV 形式の使用を有効にする。FIXEDWIDTH、REMOVEQUOTES、または ESCAPE と共に使用することはできない。
QUOTE	引用文字として使用する文字を指定。デフォルトは二重引用符（"）。CSV パラメータを使用する場合に限り使用可能。
DELIMITER	入力ファイルのフィールド区切り文字。デフォルトはパイプ文字（|）、CSVオプションを使用する場合のデフォルトはカンマ（,）FIXEDWIDTHとの併用はできない。
FIXEDWIDTH	列を区切り記号で区切らずに各列幅を固定長にしたファイルからデータをロードする。
AVRO	ソースデータが Avro 形式であることを指定。
JSON	ソースデータが JSON 形式であることを指定。
BZIP2	入力ファイルが bzip2 形式であることを指定。
GZIP	入力ファイルが gzip 形式であることを指定。
LZOP	入力ファイルが lzop 形式であることを指定。

データ変換 パラメータ	機能
REGION	ソースデータが配置されている AWS のリージョンを指定。
MANIFEST	Amazon S3 からロードするデータファイルの識別にマニフェストを使用することを指定。
SSH	SSH プロトコルを使用してリモートホストからデータがロードされることを指定。
ENCRYPTED	クライアント側の暗号機能を使って暗号化されることを指定。
ENCODING	ロードデータのエンコードタイプを指定。指定したエンコードから UTF-8 に変換される。
REMOVEQUOTES	入力データの文字列を囲む引用符を削除。
EXPLICIT_IDS	IDENTITY 列を持つテーブルに明示的な値でオーバーライドする。
ACCEPTINVCHARS	データに無効な UTF-8 文字がある場合でも、VARCHAR 列へのデータのロードを有効にする。
DATEFORMAT	デフォルト形式はYYYY-MM-DD
TIMEFORMAT	デフォルト形式はYYYY-MM-DD HH:MI:SS
IGNOREHEADER	指定された行数をファイルヘッダーとして扱いロードしない。
ACCEPTANYDATE	無効な形式も含め任意の日付形式をエラーなしにロードできるようにする。
IGNOREBLANKLINES	データファイルでラインフィードのみ含む空白行を無視してロードしない。
TRUNCATECOLUMNS	データ型が VARCHAR または CHAR の列において、列の文字数でデータを切り捨てる。
FILLRECORD	一部のレコードの最後で連続する列が欠落している場合に、NULLまたは空文字に置き換えてロード可能にする。
TRIMBLANKS	VARCHAR 文字列から末尾の空白文字を削除。
NULL AS	指定した文字列一致するフィールドを NULL としてロードする。
EMPTYASNULL	CHAR と VARCHAR の空のフィールドを NULL としてロードする。
BLANKSASNULL	連続する空白文字のみから構成される空のフィールドを NULL としてロードする。
ESCAPE	入力データのバックスラッシュ文字（\）はエスケープ文字として扱われる。
ROUNDEC	入力値の小数点以下の桁数が列の小数点以下の桁数よりも多い場合に数値を四捨五入。デフォルトは切り捨て。

データの ロード操作	機能
COMPROWS	圧縮分析のサンプルサイズとして使用される行数を指定。
COMPUPDATE	COPY 実行中に圧縮エンコードを自動的に適用するかどうかを制御。
MAXERROR	ロードのエラー数が指定した数以上である場合にロードが失敗する。
NOLOAD	データを実際にロードせずにデータファイルの有効性をチェックし、エラー無しでロードできることを確認する。
STATUPDATE	自動計算とオプティマイザ統計の更新を制御。

CSVファイルのロード

次のようなCSVデータをid INTEGER, name VARCHAR(20), note VARCHAR(20)のテーブルにDELIMITER ','でロードしようとした場合、3つ目の入力フィールドにカンマが含まれているため失敗します。

1,yamada,Hello World
2,sato,Hello, Comma
3,suzuki,Hello Text

正常にロードする方法として1つ目は、カンマをバックスラッシュ文字\でエスケープし、ESCAPEパラメータを使用してファイルをロードする方法があります。

1,yamada,Hello World
2,sato,Hello\, Comma
3,suzuki,Hello Text

COPY public.test_table
FROM 's3://bucket/work/test.csv'
CREDENTIALS 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
DELIMITER ','
ESCAPE;
INFO:  Load into table 'test_table' completed, 3 record(s) loaded successfully.

2つ目の方法としてはCSVパラメータを使用して、カンマを含むフィールドを引用符（デフォルト二重引用符）で囲みます。

1,yamada,"Hello World"
2,sato,"Hello, Comma"
3,suzuki,"Hello Text"

ただし、引用符で囲んだ文字列内に引用符がある場合、引用符を 2 つにしてエスケープする必要があります。つまりバックスラッシュ文字ではなく、二重引用符（デフォルト）でエスケープするということです。 ここら辺がESCAPEと違うところで混乱するので気をつけましょう。CSVパラメータはESCAPEと一緒に使用することができません。

1,yamada,"Hello ""The"" World"
2,sato,"Hello, Comma"
3,suzuki,"Hello Text"

テーブルのカラム数とロードするファイルのカラム数が違う場合

例えば以下のようなテーブルがあったとします。日付項目はgetdate()により自動でセットされるので、ロードする項目はidとnameだけです。

CREATE TABLE users
(
    id INTEGER,
    name VARCHAR(255),
    create_time TIMESTAMP DEFAULT getdate(),
    update_time TIMESTAMP DEFAULT getdate()
)

この場合、普通に2項目のみファイルからロードしようするとエラーになりますので、以下のようにテーブル名のあとにtable_name (column1 [,column2, ...])のように対応する項目を指定してあげる必要があります。

COPY table_name (id, name)
FROM 's3://copy_from_s3_objectpath'
CREDENTIALS 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>';

値の加工とNULLに関連するオプション

空のフィールド（CSVの場合は12345,,,test,のような空のフィールドがあるデータ）は INT などのデータ型であれば常に NULL でロードされますが、 CHAR と VARCHAR は空のフィールドでロードされます。これを一律で NULL とするためにEMPTYASNULLを指定することが有効です。NULL AS ''でも同じ動作をします。

また、空のフィールドでないにしろ値がセットされていない（12345, ,,test,のようにスペースのみのフィールド）場合も、同様に NULL にしたい場合はBLANKSASNULLを指定します。こちらも INT など他のデータ型はデフォルトで常に NULL でロードされます。

TRIMBLANKSは文字列の後ろに余計な空白文字が含まれている場合、自動で削除してロードしてくれ、IGNOREBLANKLINESは空白行を無視してロードしません。TRUNCATECOLUMNSは列の文字数でデータを切り捨ててくれますので、予想外に長い文字列などが入ってくる場合がある時（URLやReferrerなど）もし切り捨ててもいいデータであれば指定しておくといいです。

ロードの失敗を回避する方法

Redshift はビッグデータ向けの DWH ということでログデータのような予期しない値が含まれているデータをロードする際にも適したオプションが用意されています。

FILLRECORDを使用すると、一部のレコードの最後で連続する列が欠落している場合に、データをロードできるようになります。基本は欠落している対象列のデータ型に合わせて、長さがゼロの文字列または NULL がロードされますが、EMPTYASNULL オプションも一緒に指定していれば、欠落している列が CHAR または VARCHAR 列でも NULL がロードされます。

ACCEPTANYDATEは無効な形式も含めてエラーなしに日付をロードできるようにします。DATEFORMATオプションと共に使用してください。

MAXERROR <error_count>はエラーが出ても指定した件数内なら無視してロードを続けます。

ロードエラーの確認方法

ロードエラーが発生した場合はSTL_LOAD_ERRORSからエラーに関する一般的な情報を得た後で、解析エラーが発生したデータの正確な行と列などの追加的な詳細を得るためにSTL_LOADERROR_DETAILを確認します。ロードエラーに関する情報を確認するためにビューを作成しておきましょう。

create view loadview as
(select distinct tbl, trim(name) as table_name, query, starttime,
trim(filename) as input, line_number, colname, err_code,
trim(err_reason) as reason
from stl_load_errors sl, stv_tbl_perm sp
where sl.tbl = sp.id);

テーブルにロードしたあとにエラーが発生していたらどのようなエラーなのか確認します。

select * from loadview where table_name='table_name';
select * from stl_loaderror_detail where query = 1000;

 STL_LOAD_ERRORS - Amazon Redshift
 STL_LOADERROR_DETAIL - Amazon Redshift
 ロードエラー参照 - Amazon Redshift

複数のファイルをロードするデータソースの指定について

ロードするデータはFROM 's3://copy_from_s3_objectpath'パラメータで指定します。ファイルは1つのファイルをロードすることも、同じキープレフィックスを持つディレクトリまたはファイルを複数ロードすることもできます。たとえば、以下のような構造であったとして、s3://workbt/bar1と指定すればbar1/test1.jpgとbar1/test2.jpgがロードされ、s3://workbt/barと指定すればbar1/test1.jpgからbar2/test2.jpgまで全てロードされます。

$ aws s3 ls s3://workbt --recursive
2015-09-06 20:34:37          0 bar1/test1.jpg
2015-09-06 20:34:43          0 bar1/test2.jpg
2015-09-06 20:33:55          0 bar2/test1.jpg
2015-09-06 20:34:00          0 bar2/test2.jpg

同じディレクトリ内にロードしたいファイルとロードしたくないファイルが混じっている場合はマニフェストファイルが使えます。マニフェストファイルは以下のようにロードしたいファイルを指定し、FROM 's3://copy_from_ssh_manifest_file'のように1 つのファイルを明示的に参照する必要があります。マニフェストファイルの暗号化または圧縮はしてはいけません。 mandatoryはファイルが見つからなかった場合 COPY を終了するかどうか指定します。デフォルトは FALSE です。COPY コマンドに MANIFEST オプションを指定しなかった場合、マニフェストファイル自体がデータファイルであると想定されてロードされるので注意しましょう。

{
  "entries": [
    {"url":"s3://mybucket-alpha/custdata.1","mandatory":true},
    {"url":"s3://mybucket-alpha/custdata.2","mandatory":true},
    {"url":"s3://mybucket-beta/custdata.1","mandatory":false}
  ]
}

 マニフェストを使用し、データファイルを指定する - Amazon Redshift

REGIONオプション

REGION [AS] 'aws_region'はソースデータが配置されている AWS のリージョンを指定します。COPY コマンドは デフォルトではデータが Amazon Redshift クラスターと同じリージョンにあると見なします。データを含む AWS のリソースが Amazon Redshift クラスターと同じリージョンにない場合に必要です。リージョン間でデータを転送する場合、Amazon S3 や Amazon DynamoDB テーブルに対して追加料金が発生するので注意しましょう。