I have been looking to the best way to programatically pull Redshift table (table needs to be aggregated) into s3.

What would be the best solution. From Athena to s3 I found this article however, I could not find any information to do it from Redshift to s3.

https://www.datastackpros.com/2020/07/export-athena-view-as-csv-to-aws-s3.html

I would be daily ingestion and the csv file should be overwritten.

Thanks

CodePudding user response：

There are 2 ways that come to mind right away - UNLOAD and CREATE EXTERNAL TABLE. Each has its pros and cons. Your use case isn't completely clear as to what you need the resulting file(s) to look like but let me take a guess.

I expect you need a single CSV file (with or without header row?) for other tools to read / use. In this case I'd use UNLOAD with PARALLEL OFF to save the result of the query to S3. This will produce 1 file in S3 ONLY IF the resulting size is less than 5GB.