この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部の鈴木です。
COPYコマンドでRedshiftに、フィールドの区切り文字がカンマ以外のファイルをロードする機会がありました。
データベース開発者ガイド - データ形式パラメータを確認すると、記事執筆時点だとCOPYコマンドのデフォルトの区切り文字はパイプ文字(|
)ですが、CSV
パラメータを設定するとデフォルトの区切り文字はカンマ(,
)になります。
デフォルト以外の区切り文字を使いたいとき、区切り文字はDELIMITER
パラメータで指定できます。今回はこの機能を使ってTSVファイルをロードしてみました。
準備
1. サンプルのファイルの作成
まず検証用のTSVファイルを作成しました。今回はCOPYコマンドでロードできるかどうかを確認したいだけなので、小さなものを手作りしました。
sample.tsv
1 test11 test12
2 test21 test22
2. S3へのファイルのアップロード
先ほど作ったTSVファイルを、例えばcm-nayuts-load-bucket
という名前のS3バケットにアップロードしておきます。
また、Redshiftからこのファイルにアクセスできる必要があります。RedshiftからS3にアクセスできる場合は、IAMロールを使ってRedshiftクラスターに権限を付与することになります。詳しくは以下のドキュメントをご確認ください。
ユーザーに代わって Amazon Redshift が他の AWS サービスにアクセスすることを許可する - Amazon Redshift
3. ロード先のテーブルの作成
ロード先のテーブルをRedshiftに作成します。
以下のようなテーブルを作成しておきました。
CREATE TABLE IF NOT EXISTS cm_nayuts.sample_tsv_tb(
col1 INT,
col2 VARCHAR(6),
col3 VARCHAR(6),
PRIMARY KEY (col1)
)
実行すると以下のように空のテーブルができます。
やってみる
1. TSVファイルをロードしてみる
SQLクライアントからRedshiftクラスターに接続し、COPYコマンドを実行します。
まず、DELIMITER
パラメータのみを使ったSQLを試してみます。
COPY cm_nayuts.sample_tsv_tb
FROM 's3://cm-nayuts-load-bucket/sample.tsv'
IAM_ROLE 'arn:aws:iam::123456789123:role/cm-redshift-fullaccess-role'
DELIMITER '\t';
以下のようにロードできました。
また、CSV
パラメータとDELIMITER
パラメータを組み合わせることも可能でした。
COPY cm_nayuts.sample_tsv_tb
FROM 's3://cm-nayuts-load-bucket/sample.tsv'
IAM_ROLE 'arn:aws:iam::123456789123:role/cm-redshift-fullaccess-role'
FORMAT AS CSV DELIMITER '\t';
こちらも同様の結果になりました。
2. CSVパラメータを使用した際の違いをみてみる
上記の結果だと、CSV
パラメータを使用してもしなくてもロードできました。違いとしては、CSV
パラメータと組み合わせると、QUOTE
パラメータで引用文字が指定できるので、一部のフィールドに区切り文字が含まれている場合にもロードすることができます。デフォルトの引用文字は二重引用符("
)です。
例えば、あまり無いケースと思いますが、以下のようなフィールドにもタブが入っているデータを用意します。
sample2.tsv
1 test11 "test12 test13"
2 test21 "test22 test23"
以下の定義のテーブルを作成しておきます。
CREATE TABLE IF NOT EXISTS cm_nayuts.sample_tsv_tb2(
col1 INT,
col2 VARCHAR(6),
col3 TEXT,
PRIMARY KEY (col1)
)
CSVパラメータを使用してロードすると成功します。
COPY cm_nayuts.sample_tsv_tb2
FROM 's3://cm-nayuts-load-bucket/sample2.tsv'
IAM_ROLE 'arn:aws:iam::123456789123:role/cm-redshift-fullaccess-role'
FORMAT AS CSV DELIMITER '\t';
一方で、CSV
パラメータを使用せずロードすると、Extra column(s) found
という理由で失敗します。
COPY cm_nayuts.sample_tsv_tb2
FROM 's3://cm-nayuts-load-bucket/sample2.tsv'
IAM_ROLE 'arn:aws:iam::123456789123:role/cm-redshift-fullaccess-role'
DELIMITER '\t';
最後に
今回はTSVファイルのロードを例に、COPYコマンドで区切り文字が指定できるDELIMITER
パラメータをご紹介しました。また、CSV
パラメータと一緒に使った際の違いについても確認しました。
参考になりましたら幸いです。