CODEWITHSUNDEEP
mysqlpostgresqlamazon-web-servicesamazon-redshiftdata-migration
A. Pearson
A. Pearson

Reputation: 191

Can Redshift's COPY command copy more than one table?

I am looking to move some MySQL databases to the cloud in Amazon Redshift. Currently I am creating a Python script to convert the tables to CSVs, encrypt them, put them in S3, then COPY the data into Redshift. However, the way it is set up I would have to copy the data one table at a time. I have read that you can split your data into multiple files and upload them in parallel, however I believe this is still only for loading data into one table. Is there a way to use COPY on multiple tables at once? Having to copy data over from each table individually seems very inefficient.

Upvotes: 1

Views: 1318

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269191

All of your statements are correct.

The COPY command can load from multiple files in parallel (in fact, that is recommended because it can then spread the load job across multiple nodes), but it only loads on table per COPY command.

You could connect to Redshift via multiple sessions and run a COPY command in each session to load multiple tables simultaneously (but be careful of the impact on production users).

If you are wishing to migrate data from an on-premises database to Amazon Redshift, consider using:

The Database Migration Service can even perform on-going updates of Redshift whenever data is updated in the source database.

Upvotes: 3

Related Questions