Create Hive index on complex column

Question

It is possible to create an index on a complex column in hive. Complex as in map, struct, array, etc. columns.

Example:

CREATE TABLE employees (
  name         STRING,
  salary       FLOAT,
  subordinates ARRAY<STRING>,
  deductions   MAP<STRING, FLOAT>,
  address      STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

The following does not seem to work:

CREATE INDEX employees_index
ON TABLE employees (address.street)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;

FAILED: ParseException line 2:28 mismatched input '.' expecting ) near 'address' in create index statement

Aamir Aamir · Accepted Answer · 2016-05-23T09:48:04

It is not possible to create a index on element of complex data type. The reason is hive does not provide the separate column to element of complex data type and indexing is only possible on column of a table. To Understand more clearly read below.

The goal of Hive indexing is to improve the speed of query lookup on certain columns of a table. Without an index, queries with predicates like 'WHERE tab1.col1 = 10' load the entire table or partition and process all the rows. But if an index exists for col1, then only a portion of the file needs to be loaded and processed. The improvement in query speed that an index can provide comes at the cost of additional processing to create the index and disk space to store the index.

Following is the correct way to create a index on complex data type

CREATE INDEX employees_index
ON TABLE employees (address)
AS ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’
WITH DEFERRED REBUILD
IN TABLE employees_index_table
PARTITIONED BY (country,name)
COMMENT ‘index based on complex column’;

Lets understand how this program will work, Suppose we write a following query,

 select * from employees where address.street='baker';

In this baker is element of address(complex type STRUCT)
(e.g street:’baker’,city:’london’,state:’XYZ’, zip:84902)

In the above example ,query will search for address.street=’baker’ in index table instead of loading the entire table

Hope you find it useful. Thank you.

Create Hive index on complex column

2 Answers