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?

已提问 3 个月前165 查看次数
2 回答
1
已接受的回答

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
已回答 3 个月前
  • 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
专家
已回答 3 个月前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则