S3へ書き出したDynamoDBのデータをRedshifへ取り込む際、指定方法がいろいろあったりして自分でも忘れやすいのでメモ

ここで打つコマンドはRedshfitコマンドであり、psqlコマンドとは異なる。

構文（抜粋）

COPY table-name 
[ column-list ]
FROM data_source
authorization
[ [ FORMAT ] [ AS ] data_format ] 
[ parameter [ argument ] [, ... ] ]

table-name

テーブルはすでにデータベースに存在する必要があります。テーブルは一時テーブルまたは永続的テーブルです。COPY コマンドは、新しい入力データをテーブルの既存の行に追加します。

FROM data_source

ターゲットテーブルにロードするソースデータの場所

S3
- FROM 's3://mybucket_name/target_dir/'
DynamoDB
- FROM 'dynamodb://table-name'
EMR
- FROM 'emr://emr_cluster_id/hdfs_filepath'

CREDENTIALの指定

S3やDynamoDBから引っ張ってくるときはクレデンシャルの指定が必要

AWS_CLIのキーを使用: 'aws_access_key_id=<キー>;aws_secret_access_key=<シークレットキー>'
IAM_Roleを使用: 'arn:aws:iam::<aws-account-id>:role/<role-name>'

オプション

列のマッピングオプション

列リスト
- COPY tablenem の後に、列をリストで列挙する方法

ソースデータフィールドを特定のターゲット列にロードするには、列名のカンマ区切りリストを指定します。COPY ステートメントで列は任意の順序に指定できますが、Amazon S3 バケットなどにあるフラットファイルからロードする場合、ソースデータの順序に一致する必要があります。Amazon DynamoDB テーブルからロードする場合、順序は関係ありません

COPY sample (id, name, age)

JSONPaths ファイル
- ソースデータの列情報をJSONファイルで記述し、S3に置いておく方法

データファイルを JSON または Avro 形式でロードする場合、COPY は Avro スキーマのフィールド名をターゲットテーブルまたは列リストの列名と一致させることで、JSON または Avro ソースデータのデータ要素をターゲットテーブルに自動的にマッピングします。

基本的にこっちを使っている。

JSONPath式

jsonpaths.json

{
    "jsonpaths": [ // 個々の記述は固定
        "$['venuename']", // 各列情報を"$['列名']で記載
        "$['venuecity']",
        "$['venuestate']",
        "$['venueseats']"
    ]
}

S3に書き出したDynamoDBの場合、 "$['カラム名']['型']", の形式で記載する。
項目の個数はCOPY先のテーブルに合わせなければならない

余談：
jsonpaths.jsonで指定したカラム数が７でCOPY先のテーブルのカラム数が８といったカラム数に差がある場合、下記のようなエラーがでる。

err

 error:  Number of jsonpaths and the number of columns should match. JSONPath size: 7, Number of columns in table or column list: 8

DynamoDBには確実に存在しないカラムであっても、Redshift側のテーブルにカラムが存在する場合、jsonpaths.jsonにそのカラムを指定し、COPY先のテーブルとカラム設定をあわせておけば
COPYを実行することが出来る。その際、DynamoDBに存在しないカラムのデータについては NULL で取り込む。指定した値で取り込むといった事がオプション指定で可能である。

取り込むテーブルとJSONのカラム数合わせろ、そこだけ確認しろ、自分。

データ形式パラメータ

FORMAT AS json
- jsonpathsファイルを指定する方法
  - FORMAT AS json 's3://mybucket_name/target_dir/jsonpaths.json'
DELIMITER [AS] ['delimiter_char']
- csv,tsv形式等を任意の区切り文字を指定して取り込む
  - DELIMITER AS '\t'
FIXEDWIDTH 'fixedwidth_spec'
- 固定長の場合に使用（ワタシは使用したこと無い。）
  - FIXEDWIDTH 'colLabel1:colWidth1,colLabel:colWidth2, ...'
BZIP2/GZIP/LZOP
- 取り込むデータがどんな圧縮をされているかを指定する。gzip圧縮されていてもそのまま取り込むことが出来る。

データ変換パラメータ

テーブルをロードする際に、COPY は暗黙的にソースデータの文字列をターゲット列のデータ型に変換しようとします。デフォルトの動作とは異なる変換を指定する必要がある場合、またはデフォルトの変換がエラーになった場合、次のパラメータを指定してデータ変換を管理できます。

ここは使ったことがあるのと無いのがある。

一覧

ACCEPTANYDATE
ACCEPTINVCHARS
BLANKSASNULL
DATEFORMAT
EMPTYASNULL
ENCODING
ESCAPE
EXPLICIT_IDS
FILLRECORD
IGNOREBLANKLINES
IGNOREHEADER
NULL AS
REMOVEQUOTES
ROUNDEC
TIMEFORMAT
TRIMBLANKS
TRUNCATECOLUMNS

幾つか使うやつの抜粋

BLANKSASNUL

NULL など、空白文字のみから構成される空のフィールドをロードします。このオプションは CHAR と VARCHAR の列にのみ適用されます。INT など、他のデータ型の空のフィールドは常に NULL でロードされます。

EMPTYASNULL

Amazon Redshift で CHAR と VARCHAR の空のフィールドを NULL としてロードすることを指定します。INT など、他のデータ型の空のフィールドは常に NULL でロードされます。

IGNOREHEADER [ AS ] number_rows

指定された number_rows をファイルヘッダーとして扱い、ロードされせん。

NULL AS 'null_string'

null_string に一致するフィールドを NULL としてロードします。

マニュフェストファイル

Datapipeline等を使用する場合、マニュフェストファイルが生成されます。
それを使用して取り込む場合に指定する。

manifest

manifest を指定する場合は、FORMAT でマニュフェストファイルを指定する。

サンプル

S3からCOPY

sample

COPY table
FROM 's3://mybucket_name/target_dir/' 
CREDENTIALS 'aws_access_key_id=<キー>;aws_secret_access_key=<シークレットキー>'
FORMAT AS json 's3://mybucket_name/target_dir/jsonpaths.json'
GZIP;

DynamoDBからCOPY