この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、鈴木(純)です。
Redshift勉強していてちょうどよくS3からデータをロードするチュートリアルがあったのでやってみました。
はじめに
今回やるのはこちらのチュートリアルです。
Redshiftのクラスターを起動して、サンプルのS3をアップロード。起動したRedshiftからサンプルデータをロードするところまでをこのチュートリアルでは体験できます。
やってみた
それではさっそくやっていきます。
1. IAMロールを作成する
まずは前準備として、RedShiftのクラスターへS3へのアクセスを許可するロールを作成します。
AWSサービスの中からRedshiftを選択します。
クリックすると、自動でスクロールされてユースケースの選択画面へ移動するので、Redshiftを選択して次へ。
今回はチュートリアルなので、とりあえずS3フルアクセスのポリシーをアタッチします。このチュートリアル上ではGetとListの権限があれば問題ありません。
ロール名を入力してロールを作成します。
2. クラスターを作成する
Redshiftの画面へ進むと、クラスターを作成ボタンがあるのでそこから作成していきます。
無料トライアルにチェックを入れてデータベース設定はマスターユーザーのパスワード以外はデフォルトで進めます。
クラスターのアクセス許可を与える項目では、先ほど作成したIAMロールを選択してから「IAMロールの追加」をクリックしてください。
その他はデフォルトのままクラスターを作成します。
3. サンプルデータファイルのダウンロード
今回はチュートリアルのため、サンプル用のデータをダウンロードしましょう。
以下のリンクからサンプルデータをダウンロードすることができます。
Zipファイルを展開すると以下のようなサンプルファイルを確認することができます。
4. ファイルを Amazon S3 バケットへアップロードする
このステップではダウンロードしたサンプルファイルをS3にアップロードします。
今回はチュートリアル用のS3バケット「redshift-tutorial-bucket」を作成しました。Redshiftのリージョンと同じリージョンで作成するようにしてください。今回はオレゴンリージョンで実行しているため、S3もオレゴンに作成しています。バケット名以外はデフォルトで問題ないです。
バケットを作成したら、そのバケットの中に「load」という名前でフォルダを作成しましょう。
上で作成したloadファイルをS3上で開いて先ほどダウンロードしたサンプルファイルをアップロードしましょう。アップロードする時は、サンプルファイルをまとめて選択してアップロードできます。
アップロードが完了を確認しましょう。
5. サンプルテーブルの作成
それではRedshiftへサンプルテーブルを作成していきます。
今回はpsqlを使ってRedshiftへアクセスしてみます。psqlの場合は以下を書き換えて接続できます。
psql -h <endpoint> -U <userid> -d <databasename> -p <port>エンドポイントはクラスターの画面から確認できます。これをコピーすると、endpoint:port/databasenameの形式でコピーされます。
正しく接続できればパスワードの入力が求められるので、最初に設定したパスワードを入力しましょう。
接続できたら、以下のコマンドを実行してサンプルテーブルを作成します。
CREATE TABLE part
(
p_partkey INTEGER NOT NULL,
p_name VARCHAR(22) NOT NULL,
p_mfgr VARCHAR(6),
p_category VARCHAR(7) NOT NULL,
p_brand1 VARCHAR(9) NOT NULL,
p_color VARCHAR(11) NOT NULL,
p_type VARCHAR(25) NOT NULL,
p_size INTEGER NOT NULL,
p_container VARCHAR(10) NOT NULL
);
CREATE TABLE supplier
(
s_suppkey INTEGER NOT NULL,
s_name VARCHAR(25) NOT NULL,
s_address VARCHAR(25) NOT NULL,
s_city VARCHAR(10) NOT NULL,
s_nation VARCHAR(15) NOT NULL,
s_region VARCHAR(12) NOT NULL,
s_phone VARCHAR(15) NOT NULL
);
CREATE TABLE customer
(
c_custkey INTEGER NOT NULL,
c_name VARCHAR(25) NOT NULL,
c_address VARCHAR(25) NOT NULL,
c_city VARCHAR(10) NOT NULL,
c_nation VARCHAR(15) NOT NULL,
c_region VARCHAR(12) NOT NULL,
c_phone VARCHAR(15) NOT NULL,
c_mktsegment VARCHAR(10) NOT NULL
);
CREATE TABLE dwdate
(
d_datekey INTEGER NOT NULL,
d_date VARCHAR(19) NOT NULL,
d_dayofweek VARCHAR(10) NOT NULL,
d_month VARCHAR(10) NOT NULL,
d_year INTEGER NOT NULL,
d_yearmonthnum INTEGER NOT NULL,
d_yearmonth VARCHAR(8) NOT NULL,
d_daynuminweek INTEGER NOT NULL,
d_daynuminmonth INTEGER NOT NULL,
d_daynuminyear INTEGER NOT NULL,
d_monthnuminyear INTEGER NOT NULL,
d_weeknuminyear INTEGER NOT NULL,
d_sellingseason VARCHAR(13) NOT NULL,
d_lastdayinweekfl VARCHAR(1) NOT NULL,
d_lastdayinmonthfl VARCHAR(1) NOT NULL,
d_holidayfl VARCHAR(1) NOT NULL,
d_weekdayfl VARCHAR(1) NOT NULL
);
CREATE TABLE lineorder
(
lo_orderkey INTEGER NOT NULL,
lo_linenumber INTEGER NOT NULL,
lo_custkey INTEGER NOT NULL,
lo_partkey INTEGER NOT NULL,
lo_suppkey INTEGER NOT NULL,
lo_orderdate INTEGER NOT NULL,
lo_orderpriority VARCHAR(15) NOT NULL,
lo_shippriority VARCHAR(1) NOT NULL,
lo_quantity INTEGER NOT NULL,
lo_extendedprice INTEGER NOT NULL,
lo_ordertotalprice INTEGER NOT NULL,
lo_discount INTEGER NOT NULL,
lo_revenue INTEGER NOT NULL,
lo_supplycost INTEGER NOT NULL,
lo_tax INTEGER NOT NULL,
lo_commitdate INTEGER NOT NULL,
lo_shipmode VARCHAR(10) NOT NULL
);6. COPYコマンドの実行
S3バケットからRedshiftへデータをロードしていきます。
S3への認証は最初に作成したIAMロールを使用していきます。コマンド内のiam_roleは適宜置き換えて実行してください。
partテーブルのロード
partテーブルでロードするファイルはcsv形式のためオプションでcsvを指定します。今回のサンプルデータの中にはNULL値が含まれているため、NULL AS オプションを使用してロードを行います。
copy part from 's3://<your-bucket-name>/load/part-csv.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
csv
null as '\000';以下のコマンドでNULLが含まれている行を確認することができます。
$ select p_partkey, p_name, p_mfgr, p_category from part where p_mfgr is null;
p_partkey | p_name | p_mfgr | p_category
-----------+----------+--------+------------
15 | NUL next | | MFGR#47
81 | NUL next | | MFGR#23
133 | NUL next | | MFGR#44
(3 rows)supplierテーブルのロード
このチュートリアルでは、自分で作成したバケットではなくAWSで用意されているバケットからデータをロードしているため、バケット名はそのままで大丈夫です。
copy supplier from 's3://awssampledbuswest2/ssbgz/supplier.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
delimiter '|'
gzip
region 'us-west-2';customerテーブルのロード
customerテーブルのロードではS3バケットの中から必要なファイルのみをロードするために、MANIFESTを使用します。サンプルデータのファイルにはロードしたいcustomer-fw.tbl-000,
customer-fw.tbl-001といったファイルの他に、ロードしたくないcustomer-fw.tbl-000.bakやcustomer-fw.tbl.logといったファイルが混在しています。そのためustomer-fw-manifestのファイルを編集して、正しいロード対象を記載していきます。
S3バケットのサンプルデータの中に、customer-fw-manifestがあるはずなので、こちらをダウンロードしてください。
ファイルを開くと、以下のようなJSONが確認できるので、<my-bucket>を自分で作成したバケット名に置き換えて上書き保存します。
{
"entries": [
{"url":"s3://<my-bucket>/load/customer-fw.tbl-000"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-001"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-002"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-003"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-004"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-005"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-006"},
{"url":"s3://<my-bucket>/load/customer-fw.tbl-007"}
]
}データをロードする時にはmanifestのオプションを使用しましょう。
copy customer from 's3://<your-bucket-name>/load/customer-fw-manifest'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
fixedwidth 'c_custkey:10, c_name:25, c_address:25, c_city:10, c_nation:15, c_region :12, c_phone:15,c_mktsegment:10'
maxerror 10
acceptinvchars as '^'
manifest;maxerrorはテスト中最初のロードではエラーが予想されるため、それを許容するオプションです。このオプションをつけることで、エラーが発生した場合でもロードを続行します。
acceptinvcharsはデータ型でサポートされていない文字を検出した場合、有効な文字に置き換えてロードを続行するオプションです。
dwdateテーブルのロード
copy dwdate from 's3://<your-bucket-name>/load/dwdate-tab.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
delimiter '\t'
dateformat 'auto';dateformatでautoを使用すると、ロードデータに一貫性がない場合の日付を自動的に認識して変換してくれます。詳細は以下ドキュメントを参照してください。
lineorderテーブルのロード
AWSで用意されているバケットを使用します。credentialsだけ書き換えて実行してください。このテーブルでは1つのファイルからロードする場合と、複数ファイルからロードする場合で実行時間に差があることを確認できます。
以下二つのコマンドを実行すると、ノード数によるロードの速さが比較できます。今回はノード数１のためほとんど実行時間に差はありませんでした。
copy lineorder from 's3://awssampledb/load/lo/lineorder-single.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
gzip
compupdate off
region 'us-east-1';copy lineorder from 's3://awssampledb/load/lo/lineorder-multi.tbl'
iam_role 'arn:aws:iam::111111111111:role/Redshift-Tutorial-Role'
gzip
compupdate off
region 'us-east-1';これで全テーブルにサンプルデータをS3からロードすることができました。 自由にSELECTなどを実行してテーブルを確認してみてください。
まとめ
1時間程度の短い時間でS3からRedshiftへのデータロードが体験できるので、Redshiftちょっと触ってみたいという人にはちょうど良いチュートリアルでした。ロードするときのオプションもいくつか学ぶことができるので、少しRedshift触ってみたい人は是非やってみてください。
1
