S3に格納されているCSVファイルのデータをRedshiftにCOPYコマンドで投入する必要があったので備忘のために書いておきます。
サンプルデータは何でもよかったんですが、なんとなくこれを使いました。
>年齢（５歳階級），男女別人口－都道府県（大正９年～平成27年）
www.e-stat.go.jp
ファイルの中身はこんな感じです。
一部感覚で手順を実施しているところがあるので、そこはあまり参考にしないでください。
実施作業
RedshiftのCOPYコマンドについて
COPYコマンドについては以下に記載があります。
docs.aws.amazon.com
以下にあるように、COPYコマンドはRedshiftのベースとなっているアーキテクチャを使用して並列ロードが可能です。
複数ファイルに分割することで並列処理の効率を上げることができるようです。
COPY コマンドは Amazon Redshift の超並列処理 (MPP) アーキテクチャを利用し、Amazon S3 バケットのファイルからデータを並列でロードします。データを複数のファイルに分割し、テーブルに分散キーを設定すれば、並列処理の長所を最大限に活用できます。
余談ですが、ファイルを分割する場合はRedshiftクラスターのスライス数の倍数になるように分割する必要があります。
また、ファイル名のプレフィックスを共通にすることで複数ファイルの並列ロードができます。
ファイルの数がクラスターのスライスの数の倍数になるようにデータをファイルに分割します。そうすることで、Amazon Redshift はスライス間でデータを均等に分割できます
構成
今回の構成はこんな感じです。
シンプルなので別に構成図を作る必要はなかったんですが、
AWS構成図おすすめツール - Qiita
これが使いたくて作ってみました笑
これはかなり便利です。
準備
データを投入するRedshiftクラスターは事前に構築しておきます。
今回のために作成したS3バケットにダウンロードしたCSVファイルを格納します。
S3のEndpointが作成されていることを確認します。
Redshiftクラスターでテーブル作成
S3上のファイルをCOPYで取り込む場合、事前にRedshiftクラスターにテーブルを作成しておく必要があります。
docs.aws.amazon.com
docs.aws.amazon.com
こんな感じのCREATE文作ってみました。
create table sample( 都道府県コード INTEGER NOT NULL, 都道府県名 VARCHAR(20) NOT NULL, 年齢階級 VARCHAR(20) NOT NULL, 元号 VARCHAR(10) NOT NULL, 和暦_年 INTEGER NOT NULL, 西暦_年 INTEGER NOT NULL, 人口_総数 INTEGER NOT NULL, 人口_男 INTEGER NOT NULL, 人口_女 INTEGER NOT NULL );
これをRedshiftから実行後SELECTすると、データは入っていない空のテーブルが作成されます。
S3上ファイルからの取り込み
S3からCOPYするには、S3へアクセスするための認証が必要になります。
docs.aws.amazon.com
認証はIAMロール、またはIAMユーザのアクセスキー・シークレットキーで行います。
今回は過去にS3の権限を付与したIAMロールを作成していたのでそれを使用します。
サンプル見ながら作成した以下のコマンドをRedshiftのクエリエディタから実行してみます。
copy sample from 's3://test-tmp-20210208/サンプルファイル.csv' iam_role 'arn:aws:iam::XXXXXXXXXXX:role/test-s3-access-role';
エラーで失敗しました。
どうやらRedshiftのクエリエディタにログインしているユーザに、IAMロールを使用するための権限がないことが原因のようです。
ERROR: User arn:aws:redshift:ap-northeast-1:XXXXXXXXXXX:dbuser:redshift-cluster-1/admin is not authorized to assume IAM Role arn:aws:iam::XXXXXXXXXXX:role/test-s3-access-role. Detail: ----------------------------------------------- error: User arn:aws:redshift:ap-northeast-1:XXXXXXXXXXX:dbuser:redshift-cluster-1/admin is not authorized to assume IAM Role arn:aws:iam::XXXXXXXXXXX:role/test-s3-access-role. code: 8001 context: IAM Role=arn:aws:iam::XXXXXXXXXXX:role/test-s3-access-role query: 2446 location: xen_aws_credentials_mgr.cpp:449 process: padbmaster [pid=5710] -----------------------------------------------
原因調査①
調べたらどうやらRedshiftに対象のIAMロールが設定されていなかったことが原因のようでした。
設定して再実行してみます。
またエラーが出ました。
原因調査②
エラーの内容は変わっているので先ほどのエラーは解消されたみたいですが、次は純粋にテーブルのロードに失敗したようです。
ERROR: Load into table 'sample' failed. Check 'stl_load_errors' system table for details.
以下のSQLを実行して確認します。
select * from stl_load_errors
以下が実行結果です。
err_reasonカラムに「Delimiter not found」とあるので、CSVファイルの区切り文字が正しく認識されていないようです。
userid,slice,tbl,starttime,session,query,filename,line_number,colname,type,col_length,position,raw_line,raw_field_value,err_code,err_reason
100,1,101504,2021-02-08 05:38:48.507773,13057,3129,"s3://test-tmp-20210208/サンプルファイル.csv ",1,"都道府県コード ","int4 ","0 ",0,".s...{...R.[.h,.s...{....,.N..5...K..,....,.a...i.N.j,.....i.N.j,.l...i.....j,.l...i.j.j,.l...i...j ",".s...{...R.[.h,.s...{....,.N..5...K..,....,.a...i.N.j,.....i.N.j,.l...i.....j,.l...i.j.j,.l...i...j ",1214,"Delimiter not found "
delimiterの指定を追加してもう一度実行します。
copy sample from 's3://test-tmp-20210208' iam_role 'arn:aws:iam::XXXXXXXXXXX:role/test-s3-access-role' delimiter ',';
またエラーが出ました。
原因調査③
Delimiterのエラーは解消されましたが、無効な桁数としてデータ側でエラーが出ました。
都道府県コードのカラムにはINTEGERを設定していましたが、01/02/03~とあるのでINTEGERではなくVARCHARにするべきでした。
以下を参考にカラムの属性を変更します。
qiita.com
SQLを実行して再度COPYコマンドを実行します。
$ ALTER TABLE sample ADD COLUMN 都道府県コード1 VARCHAR(10) $ ALTER TABLE sample DROP COLUMN 都道府県コードCASCADE; $ ALTER TABLE sample RENAME COLUMN 都道府県コード1 TO 都道府県コード;
またエラーが出ました。
原因調査④
今度はCSVファイル内の文字列に問題があるようです。
文字列には無効な UTF8 コードポイントまたはサポートされていない UTF8 コードポイントが含まれています。不正な UTF8 16 進数です。93 (エラー3)
調べてみると元ファイルの文字コードがUTF-8のBOMなしになっていないんじゃないかというものがありました。
文字コードはUTF-8? UTF-8N? | Studio947 狩野祐東・狩野さやかの本
修正して再度実行してみます。
またエラーが出ました。
原因調査⑤
エラーの内容がまた変わりました。
ここでひとつ気が付きました。
「あれ？日本語の値が全部.に変換されている？？？」
あとでわかりましたが変換されていないわけではなく、単純に表示されていないだけでした。
試しにテーブルを一度削除し、INT型で作成していたカラムをVARCHAR型に修正して再作成しました。
そのうえでCOPYを実行してみます。
また、併せてデータファイルに入っていた"~"等の特殊文字も別の文字列に修正してます。
create table sample( 都道府県コード VARCHAR(20) NOT NULL, 都道府県名 VARCHAR(20) NOT NULL, 年齢階級 VARCHAR(20) NOT NULL, 元号 VARCHAR(10) NOT NULL, 和暦_年 VARCHAR(20) NOT NULL, 西暦_年 VARCHAR(20) NOT NULL, 人口_総数 VARCHAR(20) NOT NULL, 人口_男 VARCHAR(20) NOT NULL, 人口_女 VARCHAR(20) NOT NULL );
実行結果
COPYは成功し、Select文でファイルの中のデータをRedshiftからクエリできるようになりました。
感想及び所感
勘所がわかっていればあとはその応用で解決できそうなので、まずは試してみるということが大切でした。
