Replicating data from MySQL to Redshift?

  • 14Views
  • Last Post 28 January 2019
0
votes
Jamie Biggar posted this 25 January 2019

I have a MySQL database sitting in Amazon that I want to create an exact replica of in Redshift. The data will never change on Redshift, so a one way sync is totally fine.

 

I tried to set up a replication job, but I can't pick MySQL as the source. I can select MySQL as the source for an import job, but then have to set up all the tables and relationships manually. Is that a bug or a feature? If it has to be manual, do you have any tips and tricks for creating the correct tables and structures?

 

0
votes
Mariia Zaharova posted this 28 January 2019

 Hi! It is impossible to use relational database as a Source in Replication packages. This is as-designed behaviour. You can take a look at the Import package. Import package allows loading data from CSV files, cloud application objects or relational database tables to another cloud application or relational database. Skyvia Import supports all DML operations (including Upsert), and thus can be used for performing massive data updates.

One of our users found this workaround for target tables creation:

1) Create OData endpoint with your MySQL tables: https://skyvia.com/resources/docs/index.html?how_to_configure_odata_endpoint.htm

2) Create OData connection with this endpoint: https://skyvia.com/resources/docs/index.html?odata_connections.htm

3) Use this OData connection as a Source in Replication package (without 'Incremental Updates' option, but with the 'Create Tables' one). In order to just create target tables and limit records being transferred via OData protocol you can add filter conditions for each task.

4) Then, you can use Import package with an Upsert operation and MySQL connection as a Source, Redshift connection as a Target.

 

Please let us know if this workaround suits you.

Close