Looking for Suggestions on Archiving Data from On Premise to Cloud

Help
1

Suppose I had a smallish, on premise cluster of customer sales. To prevent it from exhausting the disk, I only want to keep orders that have not yet shipped on premise and I want to move orders that have shipped to a cloud instance where I can take advantage of the bottomless storage and then delete the records from the on premise instance. The on premise instance would be used by the shipping team for in progress orders while the cloud instance would be used by the sales team to understand order history, inventory, customer loyalty, etc …

This does not need to be real time like replication (although it could be) and a once a day, end of day batch solution would work fine.

There is no requirement to move the data back to on premise, so uni directional is fine.

There are specific reasons why the inflight data needs to remain on premise, so migrate everything to the cloud is not a solution.

Suggestions on implementations that worked and those that did not from anyone who has done similar?

2

The first thing that comes to mind is that when you decide to move a record to the cloud, you could do SELECT INTO S3 (as csv), then use LOAD DATA to load it into your cloud instance (into the table with the same name). Do this for each table that had updates you want to archive to the cloud. Then go back and delete moved records from the on-prem DB.

Then there are variations of this, like you could SELECT INTO FS first, then upload the files to S3. If you have a lot of data, you could make multiple files, and use PIPELINES to load instead of LOAD DATA.