Redshift Demo

一、前言

Amazon Redshift 是一种完全托管的 PB 级云中数据仓库服务。Amazon Redshift 数据仓库是一个节点组成的各种计算资源构成的集合，这些节点归属于集群的组中。每个集群运行一个 Amazon Redshift 引擎并包含一个或多个数据库。开始时，您可以只使用几百 GB 数据，然后扩展至 1 PB 或更多。这样，您可以使用数据获得对您的业务和客户的新简介。

本文是个简单测试，本文汇总了AWS官网文档中的操作和交互流程，并整理成如下文章。

二、准备数据

1、从这个地址下载数据。将ZIP文件解压缩后，获得一系列TXT文件，实质是CSV格式，用 ｜ 符号作为分割项。整个压缩包大约11MB。

2、在S3上新建立一个bucket，推荐bucket和要启动redshift的环境在同一个Region内，然后将上一步解压缩出来几个文件文件上传到bucket内。

注：文件不需要设置为public权限，保持S3上私有即可。

3、配置IAM Role。在IAM上创建新Role，选择适用对象是redshift，添加S3 readonly权限，并将这个Role取名 为redshift-demo-01 。

三、创建并导入数据

1、创建Redshift集群。本Demo可以创建一个单节点的redshift，例如选择dc2.large类型，将包含2vCPU，15.25GB内存，160GB SSD的规格。

创建时候注意事项：

• 网络安全组放行TCP 5432端口
• 运行角色选择为刚才创建的Redshift可读取S3的角色

2、创建表结构。点击Web界面上的“Query Editor”，连接到已经创建好的Redshift，执行如下命令建立表结构。

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);

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

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

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'));

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);

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);

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);

3、通过Web界面导入数据

点击Web界面上的“Query Editor”，连接到已经创建好的Redshift，执行如下命令导入数据。

在如下例子中，部分信息需要替换：

• S3的bucket名字需要更换为上一步的创建的名字
• IAM账户需要替换为上一步创建好的IAM Role的ARN
• 区域需要在北京（cn-north-1）和宁夏（cn-northwest-1）之间作出选择

copy users from 's3://redshift-demo-lxy/allusers_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' region 'cn-northwest-1';

copy venue from 's3://redshift-demo-lxy/venue_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' region 'cn-northwest-1';

copy category from 's3://redshift-demo-lxy/category_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' region 'cn-northwest-1';

copy date from 's3://redshift-demo-lxy/date2008_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' region 'cn-northwest-1';

copy event from 's3://redshift-demo-lxy/allevents_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS'  region 'cn-northwest-1';

copy listing from 's3://redshift-demo-lxy/listings_pipe.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '|' region 'cn-northwest-1';

copy sales from 's3://redshift-demo-lxy/sales_tab.txt' 
credentials 'aws_iam_role=arn:aws-cn:iam::420029960748:role/lxy-redshift' 
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'cn-northwest-1';

导入数据操作完成。

四、查询数据

1、通过WEB界面查询结果

点击Web界面上的“Query Editor”，连接到已经创建好的Redshift，执行如下命令即可查询。

-- Get definition for the sales table.
SELECT *    
FROM pg_table_def    
WHERE tablename = 'sales';    

-- Find total sales on a given calendar date.
SELECT sum(qtysold) 
FROM   sales, date 
WHERE  sales.dateid = date.dateid 
AND    caldate = '2008-01-05';

-- Find top 10 buyers by quantity.
SELECT firstname, lastname, total_quantity 
FROM   (SELECT buyerid, sum(qtysold) total_quantity
        FROM  sales
        GROUP BY buyerid
        ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;

-- Find events in the 99.9 percentile in terms of all time gross sales.
SELECT eventname, total_price 
FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 
       FROM (SELECT eventid, sum(pricepaid) total_price
             FROM   sales
             GROUP BY eventid)) Q, event E
       WHERE Q.eventid = E.eventid
       AND percentile = 1
ORDER BY total_price desc;

2、使用客户端查询数据

除了使用控制台自带的Query Editor外，还可以使用SQL Workbench/J这样的客户端访问。

首先需要将数据库设置为public可访问状态，即可获得公网入口endpoint，直接从互联网发起连接即可。

官网下载地址：http://www.sql-workbench.net/

下载后还需要加载JDBC驱动，适合Redshift的JDBC驱动在这里下载：https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.36.1060/RedshiftJDBC42-no-awssdk-1.2.36.1060.jar

加载JDBC后即可连接到Redshift执行查询。

五、其他参考资料

Redshift的规划、性能优化参考文档：https://docs.aws.amazon.com/zh_cn/redshift/latest/dg/best-practices.html