AWS Redshift : libraries for mock/mirror redshift

Question

Is there a library for mocking/mirroring Redshift SQL interface to run tests in an isolated dev environment ?

If not, please point to suggested best practices for such testing ?

Answer 1

Amazon Redshift was specifically created to run on AWS infrastructure. It is not available as a download. (Interestingly, Amazon DynamoDB does have a downloadable version for development purposes.)

The cheapest option might be to shutdown your Dev & Test instances each night and on weekends. Take a snapshot before deleting the cluster, then create a cluster the next morning based on the snapshot. This can be automated via the AWS Command-Line Interface (CLI), making it easy to schedule with cron or Scheduled Tasks.

You could also have a snapshot of Test data and restore that snapshot each morning, which means the test database doesn't fill-up with test cases.

Another cost saving might be to reduce the number of nodes for the non-production systems. Queries will run slower and the total amount of storage will be reduced, but it could be more cost-effective. Or even use a "Dense Storage" 2TB node instead of several "Dense Compute" SSD instances -- they will provide more storage on less nodes.

Answer 2

If your application is written in Java, you can intercept and mock Redshift specific commands via custom JDBC driver.

Custom JDBC driver will work as follows.

• Parse the given statement.
• If statement is COPY, download data from S3 then insert.
• If statement is UNLOAD, select from target table, then upload to S3.

I've just released redshift-fake-driver, a JDBC driver emulates Redshift specific commands. I hope this will help you.

Answer 3

Redshift SQL quacks mostly like PostgreSQL and I have had 'some' success with a mock db that is just postgres. TBH I eventually ripped that out, principally because window functions were just so darn different between the two.