跨帐号DataShare I

本节将介绍跨帐号的DataShare。我们将在Account 1下建立生产集群，在Account 2下建立消费集群

本实验前先把上一节创建的cloudformation stack删除，否则会报Role重复的错误

Cross_Group_Collaboration

创建两个集群

创建的过程跟第一节是一样的。在account 1下进入CloudFormation服务(地区在美东1)，然后创建stack，使用以下的S3 URL:

https://pingfan.s3.amazonaws.com/files/CFCrossGroupCollaborationAccount1.yaml

在EETeamRoleArn参数部分使用arn:aws:iam::<account-id>:user/<iam-user-name> 进行替换，其他参数可以保持不变：

image-20230820200015539

在account 2下进入CloudFormation服务，创建stack，使用以下S3 URL:

https://pingfan.s3.amazonaws.com/files/CFCrossGroupCollaborationAccount2.yaml

在EETeamRoleArn参数部分同样使用arn:aws:iam::<account-id>:user/<iam-user-name> 进行替换。

两个CloudFormation创建完成后，分别在两个帐号的redshift集群下打开Query Editor，执行以下SQL获取Redshift集群的namespace id:

select current_namespace;

后面的实验会用到这两个id：

image-20230820201115461

image-20230820201537520

导入数据

在account 1的customer-redshift-cluster下导入customer表：

DROP TABLE IF EXISTS customer;
CREATE TABLE customer
(
C_CUSTID bigint NOT NULL,
C_PREFIX varchar(10),
C_FIRSTNAME varchar(25),
C_MIDDLENAME varchar(25),
C_LASTTNAME varchar(25),
C_SUFFIX varchar(10),
C_GENDER char (1),
C_DATEOFBIRTH date,
C_ADDRESS varchar(255)
)
diststyle all;


COPY customer from 's3://ee-assets-prod-us-east-1/modules/644e76123e9946298fdf49752096b4a1/v1/customers.csv'
iam_role default
FORMAT AS CSV DELIMITER ',' QUOTE '"' IGNOREHEADER 1 ACCEPTANYDATE REGION AS 'us-east-1';

在account 2的sales-redshift-cluster下导入sales和product表：


DROP TABLE IF EXISTS product;
CREATE TABLE product (
P_PRODUCTKEY bigint NOT NULL,
P_SKUNUMBER varchar (20),
P_PRODUCTNAME varchar(100),
P_PRODUCTDESC varchar(100),
P_BRAND varchar(100),
P_UNITPRICE bigint,
P_CATEGORY varchar(100),
P_SUBCATEGORY varchar(100),
P_PRODUCTTYPE varchar(100)

)
distkey (P_PRODUCTKEY)
sortkey (P_PRODUCTKEY);


COPY product from 's3://ee-assets-prod-us-east-1/modules/644e76123e9946298fdf49752096b4a1/v1/products.csv'
iam_role default
FORMAT AS CSV DELIMITER ',' QUOTE '"' IGNOREHEADER 1 ACCEPTANYDATE REGION AS 'us-east-1';


DROP TABLE IF EXISTS sales;
CREATE TABLE sales
(
S_TRANSACTIONDATE date,
S_CUSTOMERID bigint ,
S_PRODUCTID bigint ,
S_QUANTITY bigint,
S_TOTALSALES decimal(18,4)
)
diststyle all;


COPY sales from 's3://ee-assets-prod-us-east-1/modules/644e76123e9946298fdf49752096b4a1/v1/sales.csv'
iam_role default
FORMAT AS CSV DELIMITER ',' QUOTE '"' IGNOREHEADER 1 ACCEPTANYDATE REGION AS 'us-east-1';

image-20230820201653689

导入成功后，我们就可以在account 1下面进行datashare了。