Spark Streaming Redshift Performance Issues

Question

I have many Create Tables, Inserts and Updates in my Spark streaming code operating on the Redshift tables. I am using Spark 2.2, spark-redshift-preview-3.0.0 jar and scala 2.11.8. It is taking more than 20 mins to insert/update 5000 rows in Redshift by the streaming job - this is exceeding my spark micro-batch window and making my system unstable.

What config params i should set to speedup my Redshift queries from Spark Streaming job? I have to insert and update atleast 10K rows per 5 mins.

Answer 1

The reason that it is so slow is that your spark code is generating commands such as

insert into table ...
update table ...
delete from table ...

For low volumes of data (a few rows of data occasionally) it is OK to use this method, but for larger volumes you must always:

1. Write data to s3, preferably chunked up into 1MB to 1GB files, preferable gzipped.
2. Run redshift copy command to load that s3 data into redshift "staging" area.
3. Run redshift sql to merge the staging data into your target tables.

using this method may be hundreds of times more efficient than individual inserts/updates/deletes.

You can run this batch update every few minutes to keep redshift data latency low.