A rewrite of a query based on a stale materialized view does not occur automatically.
If you want a rewrite of a stale or possibly stale materialized view, you can force a
rewrite.
For example, you might want to use the contents of a materialized view of a
non-transactional table because the freshness of such a table is unknown. The optimizer
cannot determine the data freshness if you use external tables. You can purposely
rewrite a query based on a stale materialized views using these techniques:
- Schedule the materialized view for rebuilding. For example, schedule a rebuild
to occur every x minutes.
- Adjust the rewriting time window to use stale or possibly stale data for a
period of time. For example, schedule the window within which to use stale data
for x + y minutes.
-
Create a scheduled query to invoke the rebuild statement every 10
minutes.
CREATE SCHEDULED QUERY scheduled_rebuild
EVERY 10 MINUTES AS
ALTER MATERIALIZED VIEW mv_recently_hired REBUILD;
-
Define the window of time for using stale data.
SET hive.materializedview.rewriting.time.window=10min;
