カピバラ好きなエンジニアブログ

ITのこととか趣味のカメラのことなどを赴くままに書いていきます。カピバラの可愛さこそ至高。

S3上の日本語データをRedshiftにロードする

S3に格納されているCSVファイルのデータをRedshiftにCOPYコマンドで投入する必要があったので備忘のために書いておきます。


サンプルデータは何でもよかったんですが、なんとなくこれを使いました。
>年齢(5歳階級),男女別人口-都道府県(大正9年~平成27年
www.e-stat.go.jp


ファイルの中身はこんな感じです。
f:id:live-your-life-dd18:20210208120347p:plain


一部感覚で手順を実施しているところがあるので、そこはあまり参考にしないでください。

実施作業

RedshiftのCOPYコマンドについて

COPYコマンドについては以下に記載があります。
docs.aws.amazon.com


以下にあるように、COPYコマンドはRedshiftのベースとなっているアーキテクチャを使用して並列ロードが可能です。
複数ファイルに分割することで並列処理の効率を上げることができるようです。

COPY コマンドは Amazon Redshift の超並列処理 (MPP) アーキテクチャを利用し、Amazon S3 バケットのファイルからデータを並列でロードします。データを複数のファイルに分割し、テーブルに分散キーを設定すれば、並列処理の長所を最大限に活用できます。



余談ですが、ファイルを分割する場合はRedshiftクラスターのスライス数の倍数になるように分割する必要があります。
また、ファイル名のプレフィックスを共通にすることで複数ファイルの並列ロードができます。

ファイルの数がクラスターのスライスの数の倍数になるようにデータをファイルに分割します。そうすることで、Amazon Redshift はスライス間でデータを均等に分割できます

docs.aws.amazon.com

構成

今回の構成はこんな感じです。



シンプルなので別に構成図を作る必要はなかったんですが、
AWS構成図おすすめツール - Qiita
これが使いたくて作ってみました笑
これはかなり便利です。

準備

データを投入するRedshiftクラスターは事前に構築しておきます。
f:id:live-your-life-dd18:20210208115839p:plain


今回のために作成したS3バケットにダウンロードしたCSVファイルを格納します。
f:id:live-your-life-dd18:20210208120532p:plain


S3のEndpointが作成されていることを確認します。
f:id:live-your-life-dd18:20210208120714p:plain

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すると、データは入っていない空のテーブルが作成されます。
f:id:live-your-life-dd18:20210208132637p:plain

S3上ファイルからの取り込み

S3からCOPYするには、S3へアクセスするための認証が必要になります。
docs.aws.amazon.com


認証はIAMロール、またはIAMユーザのアクセスキー・シークレットキーで行います。
今回は過去にS3の権限を付与したIAMロールを作成していたのでそれを使用します。
f:id:live-your-life-dd18:20210208135151p:plain


サンプル見ながら作成した以下のコマンドを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ロールが設定されていなかったことが原因のようでした。
設定して再実行してみます。
f:id:live-your-life-dd18:20210208143944p:plain


またエラーが出ました。

原因調査②

エラーの内容は変わっているので先ほどのエラーは解消されたみたいですが、次は純粋にテーブルのロードに失敗したようです。

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にするべきでした。
f:id:live-your-life-dd18:20210208145119p:plain


以下を参考にカラムの属性を変更します。
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ファイル内の文字列に問題があるようです。
f:id:live-your-life-dd18:20210208150229p:plain

文字列には無効な UTF8 コードポイントまたはサポートされていない UTF8 コードポイントが含まれています。不正な UTF8 16 進数です。93 (エラー3)



調べてみると元ファイルの文字コードUTF-8のBOMなしになっていないんじゃないかというものがありました。
文字コードはUTF-8? UTF-8N? | Studio947 狩野祐東・狩野さやかの本

修正して再度実行してみます。


またエラーが出ました。

原因調査⑤

エラーの内容がまた変わりました。
f:id:live-your-life-dd18:20210208162330p:plain


ここでひとつ気が付きました。
「あれ?日本語の値が全部.に変換されている???」
あとでわかりましたが変換されていないわけではなく、単純に表示されていないだけでした。
f:id:live-your-life-dd18:20210208162420p:plain


試しにテーブルを一度削除し、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からクエリできるようになりました。
f:id:live-your-life-dd18:20210208164705p:plain

今回学んだこと

今回やってみたことで以下のことを学べました。

  • IAMロールで認証する場合はRedshiftへのアタッチを忘れずに
  • 区切り文字の指定はしておく
  • データの中身が日本語の場合はデータ型に気を付ける
  • 元ファイルの文字コードはBOMなしUTF-8にする
  • 元ファイルにカラム名がある場合はヘッダの指定をする

日本語を使わずにもっとシンプルなサンプルデータを使ってたら早く終わっていたと思いますが、日本語のデータを使ったばっかりに結構つまりました。

感想及び所感

勘所がわかっていればあとはその応用で解決できそうなので、まずは試してみるということが大切でした。