Indexing in Hive: What is View & Index with Example

What is a View?

Views are similar to tables, which are generated based on the requirements.

  • We can save any result set data as a view in Hive
  • Usage is similar to as views used in SQL
  • All type of DML operations can be performed on a view

Creation of View:

Syntax:

Create VIEW <VIEWNAME> AS SELECT

Example:

Hive>Create VIEW Sample_ViewAS SELECT * FROM employees WHERE salary>25000

In this example, we are creating view Sample_View where it will display all the row values with salary field greater than 25000.

What is Index?

Indexes are pointers to particular column name of a table.

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

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 index on table guruhive_internaltable for column name id.