Best approach to automate archiving aws-redshift table

Question

I have a big table in redshift I need to automate the process of archiving monthly data.

The current approach is as follows(Manual):

unload the redshift query result to s3
create new backup table
copy files from s3 to redshift table
remove data from the original table

I need to automate this approach,
Is using aws data pipeline a good approach?
Please suggest any other effective approach, examples appreciated.

Thanks for the help!

Similar to https://stackoverflow.com/questions/47304870/scheduling-data-extraction-from-aws-redshift-to-s3 — David Webster, Jan 18 '19 at 15:59

score 2 · Answer 1 · answered Mar 22 '18 at 23:11

2

My suggested approach is to set up airflow in a small instance to run the scheduling. or if that is too much work set up a crontab.

using the redshift unload command, copy the data that you want to archive to s3, use a sub folder for each archive (e.g. monthly - use the year and month as the folder name)
delete the data from your redshift table.
set up a redshift spectrum external table definition for that data in s3, you can set that up to include all of the subfolders if you wish.

I suggest using gzip format and limiting the size to about 20-100mb per file

That way the data is outside redshift but can be accessed from redshift whenever you need it.

answered Mar 22 '18 at 23:11

Jon Scott

4,144
17
29

Is this the common practice? I was looking if there is any well-documented subsystem under the aws umbrella which can do this? why is aws data pipeline not suitable for this? – darekarsam Mar 23 '18 at 16:58
"Is this the common practice" - no - doesn't mean it not good though :) – Jon Scott Mar 23 '18 at 16:59
It definitely sounds good will try this, but was more keen on getting to know the common practice. – darekarsam Mar 23 '18 at 17:19
I would do exactly the same, using parquet instead of gzip – 1131 Feb 13 '22 at 22:49

score 1 · Answer 2 · answered Mar 25 '18 at 16:06

I do not know if this is a situation Amazon considers "common" enough to suggest practices, but here are several options (Note: the popular consensus seems to be that data pipeline is good for simple data loading, but it isn't intended as an archive method.)

Create a snapshot each month and then remove data from that table. You can use snapshot API actions or console scheduling that could be automated and your snapshots would have date stamps.
Copy the data over to an external table in S3 and then delete from the Redshift table. I think you can use Spectrum for this.
Use a third party backup solution such as N2WS, Panopoly.
Use AWS Glue (or perhaps Data Pipelines, I haven't used that) to pull out the table structure/data and then truncate the original table.
Use an external ETL product to do the same as Glue. Some are free, or your firm might already use one.

I didn't come across any best practices around this type of data copy.

You can use the functionality in Spectrify to accomplish #2 pretty easily. I'll also be updating the library to support partitioning by date pretty soon, making Spectrum queries even faster. https://github.com/hellonarrativ/spectrify — Colin Nichols, Mar 26 '18 at 01:05
Thank you Aby for the answer will do some more research on this — darekarsam, Mar 26 '18 at 17:02

Best approach to automate archiving aws-redshift table

2 Answers2