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달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠