Hive View Types — Virtual, Materialized, Union

What Hive stores when you create a view

A view is a metastore object. CREATE VIEW writes the view's name, its resolved column schema, and the text of its defining query into the metastore -- and that resolution step at creation time is more consequential than it looks.

Hive stores an expanded form of the query in which SELECT * has already been replaced by the actual column list and identifiers have been fully qualified. Two consequences follow. Adding a column to a base table does not add it to a view defined with SELECT *; the view keeps the column set it was created with, which is generally what you want for stability and is invariably a surprise the first time. And renaming or dropping a base column does not fail at view-creation time; it fails when someone queries the view, because nothing revalidates the stored text.

The tools for inspecting this are worth knowing: SHOW CREATE TABLE view_name returns the definition, and DESCRIBE FORMATTED view_name shows both the original and expanded text plus the view's own schema. ALTER VIEW ... AS <new query> redefines it in place, which is the correct way to evolve a view rather than dropping and recreating -- a drop takes any grants on the view with it.

A view is read-only. There is no updatable-view mechanism, and there are no materialised semantics unless you asked for them explicitly.

Virtual views — inlined, not executed

When a query references a virtual view, the planner substitutes the view's definition into the query tree and then optimises the combined whole. There is no separate step that 'runs the view' and hands its rows onward. The plan you get is the plan you would have got by pasting the view's SQL into a subquery, which is exactly why a virtual view cannot make anything faster.

What it can do is make the optimiser's job easier or harder. Because the planner sees everything, predicates from the outer query normally push down through the view into the base table scan -- so filtering a view on a partition column still prunes partitions, which is the property that makes views usable at all on large tables. That pushdown is blocked by particular constructs inside the view: window functions, DISTINCT, aggregation over a different grouping, LIMIT, and non-deterministic expressions all create a boundary the optimiser cannot safely move a filter across. A view that looks like a thin projection but contains a ROW_NUMBER() will scan everything, every time.

The costs that do accrue are structural. Nested views -- a view over a view over a view, which happens naturally as teams build on each other's definitions -- expand into a plan far larger than any human wrote, and the resulting query can be slow for reasons invisible at the SQL level. Reading the expanded plan with EXPLAIN is the only reliable way to see what is actually being run, and it is the first thing to do when a view-based query is inexplicably expensive.

What virtual views are genuinely for

Abstraction over physical layout. Consumers query sales_current while the underlying table is repartitioned, re-formatted from text to ORC, or relocated. Redefining the view is a metastore operation; changing hundreds of downstream queries is not.

Encoding correct business logic once. The rules for what counts as an active customer, or how a currency conversion is applied, belong in one definition rather than in each analyst's query. This is the strongest everyday argument for views, and it is a correctness argument rather than a performance one.

Security. A view can project a subset of columns and filter to a subset of rows, and access can be granted on the view while being withheld on the base table. This is the standard way to expose a table containing personal data to a broad audience with the sensitive columns removed. The caveat is that it only holds under an authorization model that actually enforces it -- SQL standard-based authorization in Hive, or a policy engine such as Ranger, where modern deployments generally prefer column masking and row-level filter policies applied directly to the table. Views as a security mechanism work; they are also easy to bypass if the base table's storage permissions were never tightened, so verify at the filesystem layer too.

Simplifying joins. A denormalising view that joins a fact table to its dimensions gives analysts a wide, obvious table to query. Be aware this is where nesting starts: the wide view becomes the base for further views, and three layers later a simple-looking count triggers a six-way join.

Materialized views — precomputed and stored

A materialized view is a table. CREATE MATERIALIZED VIEW runs the query and stores its results in real files, typically ORC, and the metastore records the definition so the optimiser knows what those files represent.

CREATE MATERIALIZED VIEW daily_sales_mv
  STORED AS ORC
  TBLPROPERTIES ('transactional'='true')
AS
SELECT store_id, sale_date, SUM(amount) AS total, COUNT(*) AS txns
FROM sales
GROUP BY store_id, sale_date;

Queries can reference it directly, but the point is that they should not have to. The reason to create one is automatic query rewriting: the optimiser recognises that an incoming query can be answered from the materialized view and substitutes it without the query mentioning it. Analysts keep writing against the base tables and get the precomputed answer.

The prerequisites are specific. Rewriting must be enabled -- it is governed by hive.materializedview.rewriting and by whether the individual view has rewriting enabled, which can be toggled with ALTER MATERIALIZED VIEW ... DISABLE REWRITE. The source tables generally need to be transactional for Hive to reason about freshness. And a staleness window, configured through hive.materializedview.rewriting.time.window, decides whether a view that has not been rebuilt recently is still eligible: zero means only a fully up-to-date view may be used, a positive value permits bounded staleness in exchange for far better hit rates.

How rewriting actually matches a query

The matching is done by the Calcite-based optimiser and it is more capable than textual equivalence, which is what makes materialized views worth the trouble.

Filter subsumption. A view covering the last two years can serve a query asking for last month, by applying the extra predicate to the view's rows.

Aggregate rollup. A view grouped by store and day can serve a query grouped by store and month, because summing daily totals yields monthly totals. This is the highest-value case: one view at a fine grain answers a family of coarser questions. It works for additive measures -- sums, counts, min, max -- and it does not work for non-additive ones. A view storing AVG(amount) cannot be rolled up correctly, so store SUM and COUNT and let the query divide. Distinct counts have the same problem and are the classic reason a rewrite silently fails to happen.

Join subsumption. A query joining fewer tables than the view can sometimes still use it, provided the extra joins in the view are known not to change row multiplicity -- which requires declared constraints. Hive supports constraint declarations that are not enforced but are trusted by the optimiser; declaring primary and foreign keys on your dimensional model is what unlocks this class of rewrite, and declaring one that is not actually true produces wrong answers rather than an error.

When a rewrite does not happen, EXPLAIN on the query shows the base tables rather than the view, and that is the diagnostic. Work through the list above: rewriting disabled, view too stale for the window, non-additive aggregate, missing constraint, or a construct in the view the matcher does not support.

Keeping a materialized view fresh

Refresh is explicit: ALTER MATERIALIZED VIEW ... REBUILD. What happens under that command varies, and the difference is large.

A full rebuild recomputes the whole result from the base tables. Simple, correct, and proportional to the size of the source -- which for a large fact table means it is not something to run every ten minutes.

An incremental rebuild reads only what changed since the last rebuild and merges it in. This is available when the source tables are transactional and, in the general case, when the changes have been inserts only; updates and deletes against sources make incremental maintenance considerably harder and commonly force a full rebuild. The mechanism is worth noticing because it explains the third view type in this article's title: an incremental rebuild is implemented as a union of the existing materialized contents with the newly computed delta, followed by a re-aggregation. Union is not just a modelling pattern in Hive, it is the internal shape of incremental maintenance.

Scheduling is yours to own. Hive supports scheduled queries, so a rebuild can be registered to run on a cadence, but the decision -- how stale is acceptable, and how much compute the rebuild may consume -- is a product decision. Pair it with the rewriting time window: a view rebuilt hourly with a two-hour staleness window is continuously eligible, while the same view with a zero window is eligible only in the moments after each rebuild, which is the configuration that makes people conclude rewriting does not work.

Union views — one logical table over several physical ones

The pattern is old and durable: recent data lives in one table optimised for fast ingestion, historical data lives in another optimised for scanning, and consumers should not have to know. A view over UNION ALL presents them as one.

CREATE VIEW events AS
SELECT event_id, user_id, event_ts, payload, 'hot' AS tier
  FROM events_recent
UNION ALL
SELECT event_id, user_id, event_ts, payload, 'cold' AS tier
  FROM events_archive;

Variants of the same idea cover a format migration in progress -- old partitions in text, new in ORC, one view over both -- and a schema change where old and new rows need different projections reconciled with casts and literals.

Three practical rules. Use UNION ALL, not UNION: the deduplicating form imposes a full sort or hash of the combined result and is almost never what you want, while UNION ALL is a concatenation. Make the branch schemas match exactly in order and type, casting explicitly rather than relying on implicit coercion, because a silent widening changes results at the edges. And keep the branches genuinely disjoint -- an overlap window between hot and cold tables duplicates rows, which is the most common defect in this pattern and one that no error message will tell you about.

On performance: predicates push into each branch independently, so a filter on a partition column prunes within both, and a filter that excludes an entire branch still usually costs a metadata check rather than a scan. That is good, but it is per-branch work, so a union view over twenty tables asks the planner to do twenty times the planning. Beyond a handful of branches, prefer one partitioned table with a tiering strategy over a union of many.

Views in Impala, which is a different engine

Hive and Impala share the metastore, so a view created in one is visible in the other. They do not share a SQL dialect or an execution engine, and that gap produces most of the friction.

Impala supports virtual views with the same semantics -- stored definition, inlined at plan time. What it does not do is execute Hive-specific syntax. A view defined in Hive using a Hive built-in function Impala lacks, a lateral view with explode, or a Hive-only type, appears in Impala's catalog and fails when queried. If a view is meant to serve both engines, define it in the intersection of the two dialects and test it from both -- the metastore will happily store something only one engine can run.

Metadata propagation is the other half. Impala caches catalog metadata, so a view created or altered in Hive is not visible until Impala refreshes -- historically INVALIDATE METADATA, with automatic invalidation available in current deployments. A missing-view error immediately after a Hive DDL is nearly always this rather than a real problem.

Materialized views are the sharper divergence: they are a Hive feature, and Impala has not historically supported Hive materialized views or their automatic rewriting. Impala can of course query the underlying materialized table directly, but it will not transparently substitute it into a query against the base tables. Where both engines must benefit from precomputation, the portable answer is an ordinary summary table maintained by a scheduled job, referenced explicitly -- less elegant, and it works everywhere.

Choosing between the three

Use a virtual view when the goal is abstraction, reuse, correctness of shared logic, or column and row restriction, and when the underlying query is already fast enough. It costs nothing, it is instantly consistent, and it never needs maintaining.

Use a materialized view when an expensive aggregation or join is repeated often enough that precomputing it pays for the storage and the rebuild, and when consumers should not have to know it exists. The test is whether automatic rewriting will actually fire for the query shapes you care about -- if the answer is no, you are building a summary table with extra steps, and an explicit summary table is more predictable.

Use a union view when the same logical dataset genuinely lives in more than one physical place and consolidating it is not worth doing -- a hot-and-cold split, a migration in flight, an acquisition's data landing in its own tables. Treat it as a transitional or operational tool rather than a permanent modelling choice, and keep the branch count small.

These compose, and the composition is often the right answer: a union view over hot and cold tables, with a materialized view over the union for the handful of aggregate queries that dominate the workload. Just keep the depth honest -- every layer is invisible in the SQL and fully present in the plan.

Anti-patterns worth naming

Deep view stacks. Three or more layers is where planning time, unexpected full scans and impossible-to-debug performance start. Flatten periodically; a view that exists only to add two columns to another view usually should not.

Assuming a view is a cache. Virtual views recompute in full on every query. Teams routinely create a view over an expensive query, observe that it is still expensive, and conclude Hive is slow. The fix is a materialized view or a summary table, not a better virtual one.

Materialized views nobody validated. A view that is never rebuilt, or whose staleness window excludes it from rewriting, consumes storage and delivers nothing. Check rewrite hit rates rather than assuming; the cheapest check is EXPLAIN on the queries you built it for.

Non-additive measures in an aggregate view. Storing averages, percentiles or distinct counts blocks rollup and quietly prevents most of the rewrites you wanted. Store the additive components.

Overlapping union branches. Duplicated rows from an ingestion window that appears in both the hot and the cold table. Enforce disjointness by construction -- a boundary timestamp applied identically in both branches -- and check it with a periodic count rather than trusting the loader.

Views that hide a full scan from an unpartitioned filter. If the view filters on a non-partition column, every query through it reads the whole table. Expose the partition column through the view so callers can prune, or the abstraction costs more than it saves.