跨帐号DataShare III

本节我们将在消费端redshift集群上进行datashare效果的验证

在account 2下，进入Query Editor，我们能看到customer_data_share已经成功加载出来：

image-20230820203106865

执行以下命令可以确认可用的datashare对象(tables, schema, UDFs):

--Displays datashares
SHOW DATASHARES

-- Displays all the individual objects inside datashare。 这个namespace id在前面章节中已获取到
DESC DATASHARE customer_sales_datashare OF ACCOUNT '<Producer_AWS_Account_Id' NAMESPACE '<Namespace-Customer-Redshift-Cluster>';

image-20230820203329293

image-20230820203309263

下面的查询即使用到了本集群的sales和product表，又使用到了account 1的customer数据：

SELECT
p.P_PRODUCTKEY AS PRODUCTKEY,
p.P_PRODUCTTYPE AS PRODUCTTYPE,
c.C_CUSTID AS CUSTOMERID,
c.C_FIRSTNAME AS FIRSTNAME,
c.C_LASTTNAME AS LASTNAME,
SUM(s.S_TOTALSALES) as TOTALSALES
FROM SALES s
inner join
customer_data_share.public.customer c
on s.S_CUSTOMERID=c.C_CUSTID

inner join PRODUCT p
on s.S_PRODUCTID=p.P_PRODUCTKEY

group by 1,2,3,4,5
order by 6 desc;

image-20230820203533218