Is it possible to copy a schema from a cluster to another cluster?

0

I wanted to copy a schema from a redshift cluster to another cluster in a testing environment is this possible?

asked 2 months ago138 views
2 Answers
1
Accepted Answer

You can copy a schema from one Amazon Redshift cluster to another with AWS Schema Conversion Tool (SCT). Here’s a quick guide on how to do it:

What is the AWS Schema Conversion Tool?

Converting data warehouse schemas to Amazon Redshift using AWS SCT

profile picture
answered 2 months ago
  • Hi Julio Cesar Oliveira,

    thanks for the Answer!

    just wanted to confirm if AWS SCT can also copy a schema along with its data to another Redshift cluster?

    Thanks!

  • I don't think the SCT can do that. Maybe Redshift could have some automated feature. If not, you can try to transfer the data with DMS (Database Migration Service) which is an incredible product.

1

A common pattern for sharing data between production and a test environment is to use Redshift Data Sharing.

https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html

Your production cluster will become the "producer" and your test environment will become the "consumer". On the consumer, execute:

select current_namespace;

Next, on the producer, say you want to share the prod schema.

create datashare prod_share set publicaccessible true;
alter datashare prod_share add schema prod;
alter datashare prod_share add all tables in schema prod;
grant usage on datashare prod_share to namespace '<consumer namespace>';

Now, on the consumer, you execute the following:

select producer_namespace from svv_datashares where share_name = 'prod_share';

Use this output to execute this command:

create database prod_db from datashare prod_share namespace '<producer_namespace>';

You can execute your tables with three part notation. For example:

select * from prod_db.prod.sales;

You can also create an external schema to use two part notation.

create external schema ext_prod from redshift database prod_db schema prod;

Now you can execute this:

select * from ext_prod.sales;

This is far quicker for making the production data available in your test environment and doesn't require making another copy of it. As soon as the data is updating in prod, it is also available in test through Data Sharing.

profile pictureAWS
EXPERT
answered 2 months ago

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