- 最新
- 最多得票
- 最多評論
Redshift data sharing is for data that is contained and managed in Redshift, aka Redshift Managed Storage (RMS), thus the name "redshift data" sharing.
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.
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.
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.
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
相關內容
- AWS 官方已更新 4 個月前
- AWS 官方已更新 2 年前
- AWS 官方已更新 1 年前
- AWS 官方已更新 1 年前
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.