AWS
S3
redshift
DynamoDB

S3へ書き出したDynamoDBデータをRedshiftへコピーする [Copyコマンドについて]

More than 1 year has passed since last update.

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で指定したカラム数が7でCOPY先のテーブルのカラム数が8といったカラム数に差がある場合、下記のようなエラーがでる。

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

sample
COPY table 
FROM 'dynamodb://table_name' 
CREDENTIALS 'aws_access_key_id=<キー>;aws_secret_access_key=<シークレットキー>'
readratio 0.7;

readratio は確保されたキャパシティに対して使用する割合

マニュフェストファイルを使用した取り込み

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

資料

COPY