Purposely using a stale materialized view

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;