CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshiftmaterialized-viewsamazon-redshift-spectrum
Ivan Rubanau
Ivan Rubanau

Reputation: 384

Is it any way to auto refresh Redshift Matearilized view based on external table?

I have materialized view in Redshift, that based on data from external Redshift Spectrum table, so it's impossible to use Redshift auto refresh feature.

I just don't wont to refresh it by hand.

I don't care much about data consistency, so delay for some time (up to 1+ hour) is fine for me.

So, is it any way to update materialized view automatically? Maybe, it's possible to configure some TTL for it? Any other ideas?

Upvotes: 1

Views: 2722

Answers (2)

user433342
user433342

Reputation: 1036

You can now have an incrementally refreshed mat view on an external table (in preview track) but you can’t make it auto-refresh (unless you manually call refresh from a scheduled query).

Upvotes: 0

Scoby
Scoby

Reputation: 177

Usually, the location of the Spectrum files is Amazon S3. You can automate the update by scheduled Lambda that triggers periodically. You can also establish a trigger that fires every time a file is created in a part of a bucket. This trigger creates an event that initiates a Lambda function that issues the desired update.

I'd start simple if you can and work up to Redshift Data API and Step functions.

This first option might be a better solution than invoking a Lambda on every object upload, especially if you are receiving lots of files continuously.

Upvotes: 0

Related Questions