Enable Amazon Redshift Data Sharing

Lab 5

The producer cluster administrator, who wants to share data, first sets up the producer cluster for data sharing by running the below commands in the query editor:

Step-1: Create a datashare on the producer cluster

Make sure you are connected to the producer cluster as Admin in us-east-1 region. Then go to query editor to run below command:

CREATE DATASHARE MarketingShare;

Step-2: Add database objects to the datashare

The producer cluster administrator then adds the needed database objects. These might be schemas, tables, and views to the datashare and specifies a list of consumers that the objects to be shared with:

ALTER DATASHARE MarketingShare ADD SCHEMA public;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_users;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_venue;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_category;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_date;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_event;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_sales;
ALTER DATASHARE MarketingShare ADD TABLE public.lab_listing;

Step-3: Grant access on datashare to the consumer cluster

1. Go to the consumer cluster in us-west-1 and note down the cluster namespace from the Amazon Redshift cluster details page:

2. Go to producer cluster in us-east-1 and grant access on datashare to the Consumer cluster namespace (noted from previous step) :

GRANT USAGE ON DATASHARE MarketingShare TO NAMESPACE 'replace-with-your-consumer-cluster-namespace';

Step-4: Data dictionary validation

Let’s validate the steps performed in above steps by querying data dictionary of producer cluster:

1. Run below SQL query to find MarketingShare datashare type:

SELECT * FROM svv_datashares;

You can see MarketingShare is an OUTBOUND datashare type.

2. Run the below SQL query to list objects and types:

SELECT * FROM svv_datashare_objects;

You can see list of objects and types (schema, table etc.) shared, and all of them are as OUTBOUND share type.

3. Run below query to verify which cluster namespace has been granted access for datashare:

SELECT * FROM svv_datashare_consumers;

You can see which namespace(s), or clusters have been granted access to the data shares.

We have now granted access on the producer cluster data share to the consumer cluster. Next, let’s validate if the consumer cluster can access this data share.