RedshiftのCOPYコマンドでDELIMITERパラメータを指定してみた

データアナリティクス事業本部の鈴木です。

COPYコマンドでRedshiftに、フィールドの区切り文字がカンマ以外のファイルをロードする機会がありました。 データベース開発者ガイド - データ形式パラメータを確認すると、記事執筆時点だとCOPYコマンドのデフォルトの区切り文字はパイプ文字(|)ですが、CSVパラメータを設定するとデフォルトの区切り文字はカンマ(,)になります。

デフォルト以外の区切り文字を使いたいとき、区切り文字はDELIMITERパラメータで指定できます。今回はこの機能を使ってTSVファイルをロードしてみました。

準備

1. サンプルのファイルの作成

まず検証用のTSVファイルを作成しました。今回はCOPYコマンドでロードできるかどうかを確認したいだけなので、小さなものを手作りしました。

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パラメータで引用文字が指定できるので、一部のフィールドに区切り文字が含まれている場合にもロードすることができます。デフォルトの引用文字は二重引用符(")です。

例えば、あまり無いケースと思いますが、以下のようなフィールドにもタブが入っているデータを用意します。

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パラメータと一緒に使った際の違いについても確認しました。

参考になりましたら幸いです。

参考

• データ形式パラメータ - CSV
• COPY の例