“Redshift View Materializer” Now on Github

Although Amazon Redshift is a fantastic data warehousing product, it lacks several of the features available in other database systems. One such feature is materialized views, which are just what they sound like... views that have been pre-calculated and physically stored on disk for faster querying, and which are updated on request.

the view

No, not that kind of View.

Materialized views are very useful any time you want to have the results of complex queries readily and quickly available, and you don't mind if the data is a few hours stale. For instance, suppose you have an executive dashboard that displays your monthly gross margin for your highly successful app, which has millions of users. The query that does those calculations runs for 20 minutes (way longer than an exec is going to want to wait), but it only needs to be updated once a month. Materialized views are the perfect solution. Calculate once, cache the data, and reference the cache on-demand. Refresh when needed.

The "Redshift View Materializer", now available on GitHub, is a simple Python script that creates tables containing the results of arbitrary SQL queries on-demand. Simply set the script to run as a cron-job whenever you want your tables re-created, and you'll end up with a reasonably close approximation of materialized views. The script is in the public domain, but I'd love if you'd contribute if you make any improvements, bug fixes, or additions!

2 Responses

  1. Ruben Kogel September 30, 2016 / 7:33 pm

    what is the difference between a materialized view and an ad-hoc table that gets refreshed infrequently and is generated by a custom cron job?

    • daynebatten October 4, 2016 / 3:55 pm

      In this case, absolutely nothing. This is just pre-built script to do the work for you, with some added niceties (re-using code between multiple queries via snippets, for example).

      In a usual database, there’s still not much of a physical difference, except that you don’t need an external script to refresh the view. You can set up a trigger to do it when the underlying data is changed, for example.

Leave a Reply

Your email address will not be published. Required fields are marked *