Copying a table from one redshift cluster to another redshift cluster(without using s3)

Question

Can we directly copy a table from one redshift cluster to another redshift cluster?

I know table copying can be achieved using s3 as temp storage(i.e. unload to s3 from first cluster and then copy from s3 to another cluster).

Answer 1

So the answer is NO. Following is the reply I got from AWS Support.

Hello, Thank you very much for contacting AWS Support. With Amazon RedShift, we do not have a mechanism to directly copy data from a table in a RedShift cluster to another table in another RedShift cluster. The normal procedure to achieve a similar result would be:

(1) UNLOAD to S3, then COPY from S3

With this approach, you use S3 as the intermediate storage. First you UNLOAD the data from the source cluster to S3, then COPY the data from S3 on the destination cluster. This is the method that you are familiar with, and is also the method we recommend. RedShift was designed to work with S3, and can achieve high efficiency with relatively low cost in doing this. For more information about UNLOADD and COPY operations in RedShift, please refer to the following AWS documentation:

http://docs.aws.amazon.com/redshift/latest/dg/t_Unloading_tables.html http://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.html

(2) Using a cluster snapshot

An alternative approach would be creating a snapshot of the source cluster, then restore the snapshot as the destination cluster. After that, drop the unnecessary tables from the destination cluster. The issue is, if you only need a small portion of the data (for example, one of the ten tables) on the destination cluster, then you might be using a (relatively) big cluster for a (relatively) small application.

For more information about managing RedShift cluster snapshots, please refer to the following AWS documentation:

http://docs.aws.amazon.com/redshift/latest/mgmt/managing-snapshots-console.html

In summary, we prefer the UNLOAD and COPY process, which is quite straight forward and cost-effective.