Can I use Database migration service (DMS) to move data into Redshift?

Question

I have my data in a MySQL database and would like to move it to Redshift. Can I accomplish this with Database migrations service (DMS). Are there any alternatives? Should I simply consider using Amazon Redshift Spectrum and not move the data?

Answer 1

Another option is to use a Federated Query (in Preview) to extract the data directly from the other database.

See: Amazon Redshift introduces support for federated querying (preview)

Amazon Redshift Spectrum can only read from Amazon S3, in which case you could simply use the COPY command to load the data.

Answer 2

You can indeed use Redshift as a target endpoint.

This would be the suggestion for a migration from MySQL to Redshift, as it can help keep everything in sync with a minimal effort in comparison to other solutions.

Redshift spectrum whilst useful, would not benefit directly from this as you would be creating an empty cluster that would query your SQL queries with no real performance benefit.

To benefit from the features such as MPP (massively parallel processing) and the query optimiser you would need to have your data stored in the Redshift cluster.

Ultimately it all depends for your use case, I hope this provides some insight.

Answer 3

Alternative to redshift you can get the data in s3 and use Athena for query purpose. This would save the additional cluster costs

Answer 4

Another option would be to use LakeFormation Workflow Blueprints (ie. Glue Jobs).