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

  • 0
    Like
  • 0
    Comment

    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