Creating an Index

An Index is nothing but a pointer on a particular column of a table. Creating an index means creating a pointer on a particular column of a table. Its syntax is as follows −

CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS ''index.handler.class.name''
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name = property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)][
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]

Example

Let us take an example to understand the concept of index. Use the same employee table that we have used earlier with the fields Id, Name, Salary, Designation, and Dept. Create an index named index_salary on the salary column of the employee table.

The following query creates an index −

./hcat –e "CREATE INDEX inedx_salary ON TABLE employee(salary)
AS ''org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'';"

It is a pointer to the salary column. If the column is modified, the changes are stored using an index value.

Dropping an Index

The following syntax is used to drop an index −

DROP INDEX <index_name> ON <table_name>

The following query drops the index index_salary −

./hcat –e "DROP INDEX index_salary ON employee;"