使用redshift加载S3桶数据
1. 创建S3桶
区域选择东京，创建S3桶s3-redshift-dw
2. 准备数据
2.1. 创建策略从其它账号S3桶复制数据
2.1.1. 创建策略AcrossAccountCopyS3
2.1.2. 对dw_user用户添加策略AcrossAccountCopyS3
2.1.3. 创建并下载dw_user的访问密钥
2.1.4. 使用访问密钥从其它账号S3桶中复制测试数据
3. 创建redshift
3.1. 对dw_user用户添加策略
https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2.html
AmazonRedshiftQueryEditor
AWSKeyManagementServicePowerUser
AmazonRedshiftQueryEditorV2FullAccess
AmazonRedshiftQueryEditorV2ReadSharing
AmazonRedshiftQueryEditorV2NoSharing
AmazonRedshiftQueryEditorV2ReadWriteSharing
3.2. 创建用于redshift的S3只读权限的角色
3.3. 使用控制台创建Redshift
3.3.1. 在Redshift控制台点击右上角区域，选择东京
3.3.2. 创建Redshift
点击集群->创建集群，在新打开的页面中进行如下操作：
- 输入集群标识符redshift-dw-test01
- 集群用途选择生产
- 节点大小选择ra3.4xlarge
- AQUA (高级查询加速器)默认自动
- 节点数改成4
- 不勾选加载示例数据
- 勾选自动生成密码或输入数据管理员用户密码，此步骤输入的密码_1234Qwer
- 集群权限选择3.2中创建的RedshiftRole-S3ReadOlny
- 点击页面下方创建集群，等待集群创建
3.4. 连接Redshift数据库
点击集群->连接到Redshift集群->查询数据，打开Redshift操作页面
3.5. 创建表
将创建表的sql语句粘贴到命令窗口，点击Run，执行sql语句创建表
create table slim_shred (
user_pseudo_id varchar(60),
advertising_id varchar(60),
event_timestamp int8,
event_name varchar(60),
country varchar(20),
version varchar(10),
id varchar(60),
user_first_touch_timestamp int8,
event_params super
);
创建完成后，在public下可以看到表slim_shred
4. 从S3加载数据
4.1. 执行以下命令，从s3加载数据
copy slim_shred from ‘s3://s3-redshift-dw/trans03/part’
iam_role ‘arn:aws:iam::961692981863:role/RedshiftRole-S3ReadOnly’
json ‘auto ignorecase’ MAXERROR 1000;
4.2. 执行过程报错
在执行过程中出现因country字段长度超过20，version字段长度超过10，导致插入数据失败
4.3. 对表结构进行调整
alter table slim_shred ALTER COLUMN country TYPE varchar(50);
alter table slim_shred ALTER COLUMN version TYPE varchar(20);
4.4. 重新执行加载命令
再次执行命令，经过22分钟21秒加载完成
4.5. 执行查询sql
执行以下sql，经过59秒查询出结果，共921条
SELECT
first_open_date,
country,
event_name,
DATE_DIFF(‘d’,retention_date, first_open_date) AS date_diff,
COUNT(DISTINCT first_uid) AS users,
COUNT(first_uid) AS events
FROM ((
SELECT
DATE( date_add( ‘h’,-8,date_add(‘us’,event_timestamp,’1970-01-01′))) AS first_open_date,
country AS country,
user_pseudo_id AS first_uid
FROM
slim_shred
WHERE
event_name = ’19d2da1d0d3c1cd08a318cbd5c04a3b0′
— AND _TABLE_SUFFIX BETWEEN ‘20200830’
— AND ‘20200918’
— AND app_info.id = ‘com.root.clean.boost.explorer.filemanager’
GROUP BY
first_open_date,
country,
first_uid )
INNER JOIN (
SELECT
DATE( date_add( ‘h’,-8,date_add(‘us’,event_timestamp,’1970-01-01′))) AS retention_date,
event_name,
user_pseudo_id AS retention_uid
FROM
slim_shred
WHERE
event_name IN (‘e37b4b622992267149d25437cbb75237’)
— AND _TABLE_SUFFIX BETWEEN ‘20200830’
— AND ‘20200918’
GROUP BY
retention_date,
event_name,
retention_uid )
ON
first_uid = retention_uid)
GROUP BY
first_open_date,
country,
event_name,
date_diff
HAVING
date_diff BETWEEN 0
AND 15
ORDER BY
first_open_date,
country,
date_diff
4.6. 执行重复sql
在新窗口中再次执行上述sql，经过7毫秒，查询出结果
