I am aware that there are many ways to export data from RDS into Redshift, but I was wondering if there is any way to export data directly from Redshift directly into an RDS MySQL table (using preferably SQL or Python)?
Example use case: an intensive Redshift query which creates a daily report that needs to be read from a web-app
Or is my only option:
Redshift ==> Export ==> S3 ==> Import ==> RDS
You have couple of simple solutions for that.
The first one is to use a scheduled AWS Lambda function that will trigger an UNLOAD command with your aggregation. This UNLOAD can use the single file flag or use the option of multiple files. These files will be written to S3, from where you can trigger another Lambda function that will load the data into MySQL. The option of a single file or multiple files depends on your MySQL version and the loading tools that you have there.
The second option is to use RDS with PostgreSQL and not with MySQL. PostgreSQL has the option of opening dblink into Redshift and through this dblink you can query the data into the RDS.