Hive Indexing

People coming from RDBMS background might know the benefit of Indexing.Indexes are useful for faster access to rows in a table. If we want to apply indexing using Hive then the first expectation might be that with indexing it should take less time to fetch records and it should not launch a map reduce job. Whereas in practice a map reduce job would still be launched on a Hive query even though an index is created on ahive table.Map/reduce job runs on the table that holds the index data to get all the relevant offsets into the main table and then using those offsets it figures out which blocks to read from the main table. So you will not see map/reduce go away even when you are running queries on tables with indexes on them. The biggest advantage of having index is that it does not require a full table scan and it would query only the HDFS blocks required.

The difference b/w compact and bitmap indexes(Hive 0.8) is how they store the mapping from values to the rows in which the value occurs (Compact Index seems to store (value, block-id) pairs while Bitmap Index stores (value , list of rows as a bitmap)).

For example on a hadoop cluster if we run the following query without index

select * from test_table where test_column=02196666033;

It takes 71 seconds to run as it would perform a complete table scan.  Note this table has storage in RCFile fromat and ~ 1 billion records.

The cluster is a 8 node hadoop cluster. 

Now we can create the index with following commands

hive> create INDEX test_column_index ON TABLE test_table(test_column) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'  WITH DEFERRED REBUILD;

OK

Time taken: 3.134 seconds

hive> ALTER INDEX  test_column_index  ON test_table REBUILD; 

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks not specified. Estimated from input data size: 26

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapred.reduce.tasks=<number>

Starting Job = job_201208160845_0121, Tracking URL = http://test-cluster-1:50030/jobdetails.jsp?jobid=job_201208160845_0121

Kill Command = /home/hadoop/hadoop-0.20.2-hdh3u3/bin/../bin/hadoop job  -Dmapred.job.tracker=test-cluster-1:50300 -kill job_201208160845_0121

2012-08-20 12:43:32,481 Stage-1 map = 0%,  reduce = 0%

2012-08-20 12:43:36,503 Stage-1 map = 1%,  reduce = 0%

…………………………………………………………………………………………….

2012-08-20 12:44:34,797 Stage-1 map = 100%,  reduce = 100%

Ended Job = job_201208160845_0121

Loading data to table default.default__test_table_test_column_index__

Deleted hdfs://test-cluster-1:54310/user/hive/warehouse/default__test_table_test_column_index__

Table default.default__test_table_test_column_index__ stats: [num_partitions: 0, num_files: 26, num_rows: 0, total_size: 179836098]

OK

Time taken: 68.077 seconds

Note this has now created an index table with following name under default Database “default__test_table_test_column_index__”

hive> INSERT OVERWRITE DIRECTORY "/tmp/index_test_result" SELECT `_bucketname` , `_offsets` FROM default__test_table_test_column_index__ where  test_column=02196666033 ;

……………..

Moving data to: /tmp/index_test_result

1 Rows loaded to /tmp/index_test_result

OK

Time taken: 2.683 seconds

hive> SET hive.index.compact.file=/tmp/index_test_result;

hive> SET hive.input.format=org.apache.hadoop.hive.ql.index.compact.HiveCompactIndexInputFormat;

hive> SELECT * FROM  test_table WHERE test_column=02196666033;

15836400000     21              02196666033     2813    13                     11               3               0       61075   1       0       0       2078   01

15836400000     21              02196666033     2813    13                     11               3               0       61075   1       0       0       2078   01

15836580000     21              02196666033     2813    13                     11               3               0       61075   1       0       0       2078   01

15836760000     21              02196666033     2813    13                     11               3               0       61075   1       0       0       2078   01

………………………………

15836760000     21              02196666033     2813    13                     11               3               0       61075   1       0       0       2078   01

Time taken: 2.479 seconds

Now If you see the difference, the same query would now take 2.4 seconds instead of 71 seconds as it is using indexing.

hive> SELECT count(*) FROM  test_table WHERE test_column=02196666033;

602

Time taken: 3.417 seconds

If you want to see what is  happened is that now the index table has stored the location of HDFS blocks where the data resides for this query and when query is fired it just reads form those blocks the actual data

hive> describe  default__test_table_test_column_index__;

OK

test_column  string  from deserializer

_bucketname     string  from deserializer

_offsets        array<bigint>   from deserializer

hive> select * from default__test_table_test_column_index__ where test_column=02196666033;

02196666033     hdfs://test-cluster-1:54310/user/hive/warehouse/test_table/000033_0       [1653776,9918864,6611866,8265443,28615496,18337446,30330621,21765257,25190947,14908809,26903328,16623458,20052481,194,4958659,3305997,23478272,11571563,13224916]