背景・目的

Redshiftの性能や機能を試すためには、ある程度サイズや件数が多いデータが必要になります。
そのため、awslabs/amazon-redshift-utilsリポジトリのCloud Data Warehouse Benchmark（以降、Cloud DW benchmarkという。）を利用してデータを準備し、今後の検証に利用します。
Cloud DW benchmarkは、TPC-Hから派生したベンチマークツールです。3TBと30TB用のディレクトリがあるが、今回は3TBを利用します。

サマリ

Redshiftのデータを準備しました。
一定以上の大きさのデータをさくっと用意するには、awslabsが有用だと感じました。
- ただし、ロードの時間を考えると同一リージョン(us-east-1)にRedshiftを構築したほうが良い。

実践

データベースの作成

データベースを用意します。

CREATE DATABASE tpch_3tb;

2.作成したデータベースに接続します。

テーブルの作成

1.ddl.sqlのうち、テーブル部分だけ流します。

DDL

create table customer (
  c_custkey int8 not null ,
  c_name varchar(25) not null,
  c_address varchar(40) not null,
  c_nationkey int4 not null,
  c_phone char(15) not null,
  c_acctbal numeric(12,2) not null,
  c_mktsegment char(10) not null,
  c_comment varchar(117) not null,
  Primary Key(C_CUSTKEY)
) distkey(c_custkey) sortkey(c_custkey);

create table lineitem (
  l_orderkey int8 not null ,
  l_partkey int8 not null,
  l_suppkey int4 not null,
  l_linenumber int4 not null,
  l_quantity numeric(12,2) not null,
  l_extendedprice numeric(12,2) not null,
  l_discount numeric(12,2) not null,
  l_tax numeric(12,2) not null,
  l_returnflag char(1) not null,
  l_linestatus char(1) not null,
  l_shipdate date not null ,
  l_commitdate date not null,
  l_receiptdate date not null,
  l_shipinstruct char(25) not null,
  l_shipmode char(10) not null,
  l_comment varchar(44) not null,
  Primary Key(L_ORDERKEY, L_LINENUMBER)
) distkey(l_orderkey) sortkey(l_shipdate,l_orderkey)  ;

create table nation (
  n_nationkey int4 not null,
  n_name char(25) not null ,
  n_regionkey int4 not null,
  n_comment varchar(152) not null,
  Primary Key(N_NATIONKEY)                                
) distkey(n_nationkey) sortkey(n_nationkey) ;

create table orders (
  o_orderkey int8 not null,
  o_custkey int8 not null,
  o_orderstatus char(1) not null,
  o_totalprice numeric(12,2) not null,
  o_orderdate date not null,
  o_orderpriority char(15) not null,
  o_clerk char(15) not null,
  o_shippriority int4 not null,
  o_comment varchar(79) not null,
  Primary Key(O_ORDERKEY)
) distkey(o_orderkey) sortkey(o_orderdate, o_orderkey) ;

create table part (
  p_partkey int8 not null ,
  p_name varchar(55) not null,
  p_mfgr char(25) not null,
  p_brand char(10) not null,
  p_type varchar(25) not null,
  p_size int4 not null,
  p_container char(10) not null,
  p_retailprice numeric(12,2) not null,
  p_comment varchar(23) not null,
  PRIMARY KEY (P_PARTKEY)
) distkey(p_partkey) sortkey(p_partkey);

create table partsupp (
  ps_partkey int8 not null,
  ps_suppkey int4 not null,
  ps_availqty int4 not null,
  ps_supplycost numeric(12,2) not null,
  ps_comment varchar(199) not null,
  Primary Key(PS_PARTKEY, PS_SUPPKEY)
) distkey(ps_partkey) sortkey(ps_partkey);

create table region (
  r_regionkey int4 not null,
  r_name char(25) not null ,
  r_comment varchar(152) not null,
  Primary Key(R_REGIONKEY)                             
) distkey(r_regionkey) sortkey(r_regionkey);

create table supplier (
  s_suppkey int4 not null,
  s_name char(25) not null,
  s_address varchar(40) not null,
  s_nationkey int4 not null,
  s_phone char(15) not null,
  s_acctbal numeric(12,2) not null,
  s_comment varchar(101) not null,
  Primary Key(S_SUPPKEY)
) distkey(s_suppkey) sortkey(s_suppkey)
;

2.テーブル定義の詳細は確認しませんが、作成されているのは確認できました。

IAMポリシーの修正

1.RedshiftクラスタにアタッチしているロールのIAMポリシーに、以下のCOPY元のバケットへのgetとListの権限を付与しておきます。

"arn:aws:s3:::redshift-downloads",
"arn:aws:s3:::redshift-downloads/*"

COPY

1.COPY前に件数を確認します。

select 'region',count(1) from region
union all
select 'nation',count(1) from nation
union all
select 'lineitem',count(1) from lineitem
union all
select 'orders',count(1) from orders
union all
select 'part',count(1) from part
union all
select 'supplier',count(1) from supplier
union all
select 'partsupp',count(1) from partsupp
union all
select 'customer',count(1) from customer
order by 1;

2.以下のコマンドでCOPYします。
※ us-east-1のS3からap-northeast-1のRedshiftにCOPYするので時間かかります。

copy region from 's3://redshift-downloads/TPC-H/2.18/3TB/region/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy nation from 's3://redshift-downloads/TPC-H/2.18/3TB/nation/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy lineitem from 's3://redshift-downloads/TPC-H/2.18/3TB/lineitem/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy orders from 's3://redshift-downloads/TPC-H/2.18/3TB/orders/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy part from 's3://redshift-downloads/TPC-H/2.18/3TB/part/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy supplier from 's3://redshift-downloads/TPC-H/2.18/3TB/supplier/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy partsupp from 's3://redshift-downloads/TPC-H/2.18/3TB/partsupp/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';
copy customer from 's3://redshift-downloads/TPC-H/2.18/3TB/customer/' iam_role '{IAMロール}' delimiter '|' region 'us-east-1';

COPYに要した時間は以下の通り。

データ	実行時間
region	数秒
nation	数秒
lineitem	4時間
orders	47分
part	7分
supplier	52秒
partsupp	31分
customer	7分

最も時間がかかったのがlineitemの4H。

3.COPY後の件数を確認します。

select 'region',count(1) from region
union all
select 'nation',count(1) from nation
union all
select 'lineitem',count(1) from lineitem
union all
select 'orders',count(1) from orders
union all
select 'part',count(1) from part
union all
select 'supplier',count(1) from supplier
union all
select 'partsupp',count(1) from partsupp
union all
select 'customer',count(1) from customer
order by 1;

4.テーブルごとのサイズを確認します。（上記のクエリじゃなくても良かったかも）

select database,"table",tbl_rows,size from svv_table_info

database	table	tbl_rows	size
tpch_3tb	region	5	48
tpch_3tb	nation	25	154
tpch_3tb	supplier	30000000	2676
tpch_3tb	customer	450000000	41846
tpch_3tb	part	600000000	34193
tpch_3tb	partsupp	2400000000	161049
tpch_3tb	orders	4500000000	252517
tpch_3tb	lineitem	18000048306	949226

Cloud Data Warehouse BenchmarkでRedshiftのデータを準備する

背景・目的

サマリ

実践

データベースの作成

テーブルの作成

IAMポリシーの修正

COPY

参考