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).

Elliottellipse

Adimz posted an Answer saying "There is a solution made by AWS https://github.com/awslabs/amazon-redshift-utils/tree/master/src/UnloadCopyUtility" – Hakenkreuz

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.

Elliottellipse

But the unload/copy is darn easy - like two commands - one in the source cluster and one in the new... – Bactria

You can now restore an individual table from a cluster snapshot. docs.aws.amazon.com/redshift/latest/mgmt/… This is a new feature ~ March, 2016 – Edaedacious

Tables can be restored only within the same cluster. – Callous

Make sure to test your copy/unload scripts. Default does not escape delimiters for example and you can get errors like extra columns found in file. We now include "addquotes escape" to our unload commands (and related copy's "removequotes escape"). Also for building ddl for the copy command in an empty schema, check out "admin.v_generate_tbl_ddl" from amazons github redshift utilities – Carryingon

@Elliottellipse do you know if there is any additional (maybe faster) solution, exept those two mentioned by you? – Hayrick

Answer 2

Depending on your reason for wanting to copy the table, the best solution may now be to use Redshift data sharing: https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html

Assuming the prerequisites are met, this will allow you to make a table available to read in another Redshift cluster.

Serviceman

Answer 3

With new Datashare feature now you can share data between redshift clusters , accounts without moving data .

Katelynnkaterina

Answer 4

Just used datagrip export option. Save SQL inserts in a file. Run file as script.

Predictory

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Diaphone

Answer 5

For DataGrip,

• Add the source redshift cluster and destination redshift cluster to DataGrip
• Right-click the table name --> Import/Export --> Copy Table to...
• Set the target schema and table from the popup subwindow
• Click Import, both the schema and data of the source table will be transfer to the destination cluster

Nodical