RedshiftのCOPYコマンドを試してみた

背景・目的

Redshiftのドキュメントの手順に倣い、S3上データをRedshiftへCOPYする。

内容

概要

COPYコマンドの特徴

• COPYコマンドに使用できるデータソースは、DynamoDB、S3、EMR、EC2上のファイルを指定することができる。
• COPYコマンドは、新しい入力データをテーブルの既存の行に追加する。
• 単一入力行の最大サイズは4MB

COPYコマンドの構文

COPY table-name 
FROM data-source
authorization;

• table-name
  • COPY先のターゲットテーブル名。
• FROM data_source
  • ソースデータの場所
  • マニフェストファイルを用いることで複数のデータソースを指定できる。
• authorization
  • Redshiftクラスタが、AWSリソースにアクセスする。
  • IAMロールまたは、アクセスキーとシークレットアクセスキーを指定できる。

事前準備

• 以下を参考に、ターゲット側のRedshiftの準備を行う。

データベースを作成する

• 以下を参考に、今回使用するデータベースを準備する。
• データベース名は、tickitとする。

CREATE DATABASE tickit WITH OWNER = {ユーザ名}

テーブルを作成する

• 以下のDDLを実行する。

users

create table users(
	userid integer not null distkey sortkey,
	username char(8),
	firstname varchar(30),
	lastname varchar(30),
	city varchar(30),
	state char(2),
	email varchar(100),
	phone char(14),
	likesports boolean,
	liketheatre boolean,
	likeconcerts boolean,
	likejazz boolean,
	likeclassical boolean,
	likeopera boolean,
	likerock boolean,
	likevegas boolean,
	likebroadway boolean,
	likemusicals boolean);

venue

create table venue(
	venueid smallint not null distkey sortkey,
	venuename varchar(100),
	venuecity varchar(30),
	venuestate char(2),
	venueseats integer);

category

create table category(
	catid smallint not null distkey sortkey,
	catgroup varchar(10),
	catname varchar(10),
	catdesc varchar(50));

date

create table date(
	dateid smallint not null distkey sortkey,
	caldate date not null,
	day character(3) not null,
	week smallint not null,
	month character(5) not null,
	qtr character(5) not null,
	year smallint not null,
	holiday boolean default('N'));

event

create table event(
	eventid integer not null distkey,
	venueid smallint not null,
	catid smallint not null,
	dateid smallint not null sortkey,
	eventname varchar(200),
	starttime timestamp);

listing

create table listing(
	listid integer not null distkey,
	sellerid integer not null,
	eventid integer not null,
	dateid smallint not null  sortkey,
	numtickets smallint not null,
	priceperticket decimal(8,2),
	totalprice decimal(8,2),
	listtime timestamp);

sales

create table sales(
	salesid integer not null,
	listid integer not null distkey,
	sellerid integer not null,
	buyerid integer not null,
	eventid integer not null,
	dateid smallint not null sortkey,
	qtysold smallint not null,
	pricepaid decimal(8,2),
	commission decimal(8,2),
	saletime timestamp);

• 最後にテーブルを確認。

select tablename from pg_tables where schemaname ='public'

S3にデータを用意する

• 専用のバケットを作成する。
• 公式ドキュメントにある tickitdb.zipからファイルをダウンロードし、作成したバケット（tickitdbフォルダ）にアップロードする。　（ファイルサイズはそれほど大きくない。）

IAMロールとIAMポリシーを作成する。

• s3:ListBucketとs3:GetObjectをポリシーにアタッチする。

実践

COPYコマンドを実行

• 事前に、該当テーブルのレコードを確認する。

select 'category',count(1) from category
union
select 'date',count(1) from date
union 
select 'event',count(1) from event
union
select 'listing',count(1) from listing
union
select 'sales',count(1) from sales
union
select 'users',count(1) from users
union
select 'venue',count(1) from venue

--- 結果 
category	0
listing	0
date	0
event	0
venue	0
users	0
sales	0

• COPYコマンドを実行する。

copy users from 's3://{バケット名}/tickit/allusers_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' region 'ap-northeast-1';

copy venue from 's3://{バケット名}/tickit/venue_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' region 'ap-northeast-1';

copy category from 's3://{バケット名}/tickit/category_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' region 'ap-northeast-1';

copy date from 's3://{バケット名}/tickit/date2008_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' region 'ap-northeast-1';

copy event from 's3://{バケット名}/tickit/allevents_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'ap-northeast-1';

copy listing from 's3://{バケット名}/tickit/listings_pipe.txt' 
iam_role '{IAMロールのARN}'
delimiter '|' region 'ap-northeast-1';

copy sales from 's3://{バケット名}/tickit/sales_tab.txt'
iam_role '{IAMロールのARN}'
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'ap-northeast-1';

--- 経過時間: 00 分 15 秒

• 実行後のレコードを確認する。

select 'category',count(1) from category
union
select 'date',count(1) from date
union 
select 'event',count(1) from event
union
select 'listing',count(1) from listing
union
select 'sales',count(1) from sales
union
select 'users',count(1) from users
union
select 'venue',count(1) from venue

--- 結果
sales	172456
category	11
listing	192497
date	365
venue	202
users	49990
event	8798

考察

• COPYコマンドを実行することで、簡単にロードができる。
• 基本的な書き方は理解できた。他にもオプションがあるので、今後確認していく。

参考

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY.html
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/copy-parameters-data-source-s3.html