Hive View and Indexing: Create with Examples

By : David Carter David Carter
Hours Updated

⚡ Smart Summary

Views in Hive are saved queries that behave like read-only tables, while indexes are pointers to a column that speed up lookups, and both are created with short HiveQL statements shown here.

  • 👁️ View is logical: A view stores only its SELECT statement in the metastore, so it occupies no disk space of its own.
  • 🔒 Read-only by design: A view cannot be the target of LOAD, INSERT or ALTER, because Hive evaluates it fresh on every query.
  • 📍 Index points at data: An index is a pointer to a column value that lets Hive read part of a file instead of the whole table.
  • 🗂️ Two handlers: Compact indexing suits high-cardinality columns and bitmap indexing suits columns with few distinct values.
  • 🔄 Manual rebuild: An index is never refreshed automatically, so ALTER INDEX REBUILD must run after the base table changes.
  • 🚫 Removed in Hive 3.0: Indexing was dropped under HIVE-18448, and materialized views, ORC or Parquet storage and partitioning replace it.
Read More

Views and indexes in Hive explained with examples

What is a View?

Views are similar to tables, and they are generated based on the requirements. A view is a purely logical object with no storage of its own: Hive keeps only the query text in the metastore and evaluates it every time the view is referenced.

  • We can save any result set data as a view in Hive
  • Usage is similar to views used in SQL
  • A view is read-only, so it cannot be the target of a LOAD, INSERT or ALTER statement that writes data

Creation of View:

Syntax:

Create VIEW <VIEWNAME> AS SELECT

The full documented form also accepts an IF NOT EXISTS clause and an optional column list, which is useful when the SELECT list contains expressions rather than plain column names.

Example:

Hive>Create VIEW Sample_View AS SELECT * FROM employees WHERE salary>25000

In this example, we are creating the view Sample_View, which displays all the row values with a salary field greater than 25000. The filter lives inside the view, so any query that selects from Sample_View sees only those rows.

What is Index?

Indexes are pointers to a particular column name of a table. The goal of an index is to improve lookup speed: without one, a query with a predicate such as WHERE tab1.col1 = 10 loads the whole table or partition and processes every row, while an index on col1 lets Hive read only part of the file.

  • The user has to manually define the index
  • Wherever we are creating an index, it means that we are creating a pointer to a particular column name of the table
  • Any changes made to the column present in the table are stored using the index value created on the column name

That speed-up is not free. Building the index costs extra processing, and the index itself occupies disk space that has to be maintained alongside the table.

Syntax:

Create INDEX <INDEX_NAME> ON TABLE <TABLE_NAME(column names)>

Example:

Create INDEX sample_Index ON TABLE guruhive_internaltable(id)

Here we are creating an index on the table guruhive_internaltable for the column name id. Note that a complete statement on a release that still supports indexing also needs an index-handler clause, which the next section shows in full.

Difference Between View and Index in Hive

Views and indexes are often introduced together because both sit on top of an existing table, but they solve different problems. A view changes what a query sees, while an index changes how quickly Hive finds it. The table below compares them.

Aspect View Index
What it stores Only the SELECT statement, in the metastore A separate index table holding pointers to the data
Purpose Simplify or restrict what a query returns Reduce the amount of data scanned for a predicate
Disk cost None Extra storage plus a rebuild after data changes
Write access Read-only Not queried directly; the optimiser uses it
Current status Fully supported Removed in Hive 3.0

In practice a view is created for readability and access control, and an index was created purely for performance on a selective column.

RELATED ARTICLES

Types of Index in Hive with Syntax

Releases up to Hive 2.x shipped two index handlers, and the handler is named in the mandatory AS clause. Compact indexing arrived in Hive 0.7.0 and bitmap indexing in Hive 0.8.0.

  • Compact index: stores the value together with the address of the HDFS block that holds it, instead of recording the location of every single occurrence. It suits columns with many distinct values.
  • Bitmap index: stores a bitmap per distinct value, which is the usual approach for a column with only a small number of distinct values, such as a status or gender flag.

A compact index is created, listed and dropped as follows:

CREATE INDEX table01_index ON TABLE table01 (column2) AS 'COMPACT';
SHOW INDEX ON table01;
DROP INDEX table01_index ON table01;

The WITH DEFERRED REBUILD option registers the index without populating it, so the build can be scheduled separately with ALTER INDEX. A bitmap index is created the same way, with a different handler name:

CREATE INDEX table03_index ON TABLE table03 (column4) AS 'BITMAP' WITH DEFERRED REBUILD;
ALTER INDEX table03_index ON table03 REBUILD;
SHOW FORMATTED INDEX ON table03;
DROP INDEX table03_index ON table03;

An index is not refreshed automatically. Whenever the base table receives new data, ALTER INDEX … REBUILD has to be run again, and on a partitioned table the rebuild can be limited to a single partition.

Why Indexing Was Removed in Hive 3.0

Indexing was removed from Hive in version 3.0 under HIVE-18448, so CREATE INDEX, SHOW INDEX and DROP INDEX no longer exist on a current cluster. The feature was rarely worth its rebuild cost once columnar storage and the cost-based optimiser matured. Three replacements cover the same ground.

  • Materialized views: introduced in Hive 3.0.0, a materialized view stores the pre-computed result of a query and the optimiser rewrites incoming queries against it automatically.
  • Columnar file formats: ORC and Parquet carry their own lightweight indexes and min/max statistics, so the reader can skip whole stripes, blocks or files without any user-defined index.
  • Partitions and buckets: partitioning and bucketing prune data at the directory and file level, which usually removes far more input than an index ever did.

On Hive 2.x an index is still valid, but new work is better served by one of the options above.

FAQs

DROP VIEW view_name removes it, and ALTER VIEW view_name RENAME TO new_name renames it. Because a view holds no data, dropping one never touches the base table; only the metastore entry disappears.

A materialized view stores the pre-computed query result as real data, so it costs disk space and needs a REBUILD. A normal view stores only the query text and is recomputed on every reference.

No. The metastore keeps the SELECT statement and the resolved column list, nothing more. Every reference re-runs the underlying query, which is why a view over a slow join stays slow.

In Hive 0.12.0 and earlier the name was case sensitive for CREATE INDEX and DROP INDEX, while ALTER INDEX needed lowercase. Hive 0.13.0 made index names case insensitive for every statement.

Yes on any modern cluster. Partition pruning removes whole directories before the scan starts and bucketing narrows a join or sample to specific files, which usually beats what an index table could deliver.

Machine learning tools profile query logs, rank predicate columns by selectivity and frequency, and suggest where a materialized view or partition scheme would pay off. Validate each suggestion against an EXPLAIN plan before applying it.

It produces CREATE VIEW statements reliably from a short comment. Check anything version specific, because it still emits CREATE INDEX syntax that a Hive 3.0 or later cluster rejects outright.

The index is a separate table of pointers, and Hive never refreshes it when the base table changes. Without a rebuild the pointers go stale, so the optimiser either skips the index or returns outdated matches.

Summarize this post with: