Difference between partition and index in hive

indexing hive partition
sonia picture sonia · Feb 9, 2015 · Viewed 13.8k times · Source

I am new in hadoop and hive and I would know what is the difference between index and partition in hive? When I use index and when partition?

Thank you!

Answer

mstang picture mstang · Feb 9, 2015

Indexes are new and evolving (features are being added) but currently Indexes are limited to single tables and cannot be used with external tables. Creating an index creates a separate table. Indexes can be partitioned (matching the partitions of the base table). Indexes are used to speed the search of data within tables.

Partitions provide segregation of the data at the hdfs level, creating sub-directories for each partition. Partitioning allows the number of files read and amount of data searched in a query to be limited. For this to occur however, partition columns must be specified in your WHERE clauses.

While building your data model you can determine the best use of indexes and/or partitions based on the size of your data and your expected use patterns.

Related questions

Finding the index of an item in a list

Given a list ["foo", "bar", "baz"] and an item in the list "bar", how do I get its index (1) in Python?

Read More
Getting the last element of a list

In Python, how do you get the last element of a list?

Read More
Python Pandas: Get index of rows which column matches certain value

Given a DataFrame with a column "BoolCol", we want to find the indexes of the DataFrame in which the values for "BoolCol" == True I currently have the iterating way to do it, which works perfectly: for i in range(100,3000): if …

Read More