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.
  1. 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; 
  2. Define the window of time for using stale data.
    SET hive.materializedview.rewriting.time.window=10min;