Redshift Datashares for Views of External Schema Tables

0

I have cluster A and cluster B. Cluster A has an external schema called 'landing_external' that contains many tables from our glue data catalog. Cluster A also has a local schema that is comprised of views that leverage data from 'landing_external' - this schema is called 'landing'.

Cluster A has a datashare that Cluster B is the consumer of. The 'landing' schema is shared with Cluster B, however, anytime a user attempts to select data from any of the views in 'landing' schema, they receive an error ERROR: permission denied for schema landing_external. I thought that creating all of the views with option 'WITH NO SCHEMA BINDING' would address this permission gap but it does not.

Any ideas on what I am missing?

tjtoll
asked a year ago3013 views
3 Answers
1
Accepted Answer

Redshift data sharing is for data that is contained and managed in Redshift, aka Redshift Managed Storage (RMS), thus the name "redshift data" sharing.

profile pictureAWS
answered a year ago
  • That is fair - I'll mark this as the accepted answer. However, I should share that being able to share views on top of external schema data across shares would help centralize business logic applied on top of views across all data shares. Instead, those views need to be created on any consumer clusters.

0

You're receiving the permission error because the external schema 'landing_external' isn't shared with Cluster B, while the views in the 'landing' schema are referencing the 'landing_external' schema.

To resolve this issue, you need to share the 'landing_external' schema with Cluster B:

-- On Cluster A, create a new data share that includes the 'landing_external' schema
CREATE SHARE landing_external_share;
ALTER SHARE landing_external_share ADD SCHEMA landing_external;

-- Grant usage on the new data share to the consumer account for Cluster B
GRANT USAGE ON SHARE landing_external_share TO ACCOUNT '<ConsumerAccountIdentifier>';

-- On Cluster B, create a new database that uses the shared 'landing_external' schema
CREATE DATABASE landing_external_db FROM SHARE <ProducerAccountIdentifier>.landing_external_share;

After completing these steps, users in Cluster B should have the required permissions to access the 'landing_external' schema and execute queries on the views in the 'landing' schema without encountering permission errors.

profile picture
EXPERT
answered a year ago
  • Unfortunately it does not appear that external schemas can be added to datashares:

    ERROR: External schema "landing_external" cannot be added to datashares.

  • @Sedat - Above mentioned solution won't work unless we explicitly copy the data into Redshift Managed Storage.

0

as per your latest comment I apologize for the confusion in my previous response. You're correct that external schemas cannot be added to data shares directly. As an alternative solution, you can create views in the 'landing' schema on Cluster A that reference the tables in the 'landing_external' schema. You can then share the 'landing' schema with Cluster B. This way, users in Cluster B can query the views without having direct access to the 'landing_external' schema.

How to example

On Cluster A, create views in the 'landing' schema that reference the tables in the 'landing_external' schema:

CREATE VIEW landing.view1
AS SELECT * FROM landing_external.table1;

Repeat this for each table in the 'landing_external' schema.

Add the 'landing' schema to the data share, if you haven't already:

CREATE SHARE landing_share;
ALTER SHARE landing_share ADD SCHEMA landing;

Grant usage on the data share to the consumer account for Cluster B:

GRANT USAGE ON SHARE landing_share TO ACCOUNT '<ConsumerAccountIdentifier>';

On Cluster B, create a new database that uses the shared 'landing' schema:

CREATE DATABASE landing_db FROM SHARE <ProducerAccountIdentifier>.landing_share;

This workaround allows users in Cluster B to query the data in the 'landing_external' schema without directly sharing the external schema. However, this may not be the most efficient solution if you have a large number of tables in the 'landing_external' schema. In such cases, you might consider automating the view creation process using a script or stored procedure.

profile picture
EXPERT
answered a year ago
  • I could be missing something, but I believe that is exactly what I am doing currently that presents this error: ERROR: permission denied for schema landing_external

  • To get past that permission denied just "grant usage on landing_external to public", but even then the consumer will throw the error message "Localized temp table cannot be created while getting lbv definition". This is because https://docs.aws.amazon.com/redshift/latest/dg/considerations.html#limitations-datashare states "Amazon Redshift doesn't support adding external schemas, tables, or late-binding views on external tables to datashares.". You can work around it by making a materialized view against the external table and having it refresh on a schedule

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions