Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of Hive Index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Overview of Hive Indexes

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.

Create/build, show, and drop index:

CREATE INDEX table01_index ON TABLE table01 (column2) AS 'COMPACT';SHOW INDEX ON table01;DROP INDEX table01_index ON table01

Create then build, show formatted (with column names), and drop index:

CREATE INDEX table02_index ON TABLE table02 (column3) AS 'COMPACT' WITH DEFERRED REBUILD;ALTER INDEX table02_index ON table2 REBUILD;SHOW FORMATTED INDEX ON table02;DROP INDEX table02_index ON table02

Create bitmap index, build, show, and drop:

CREATE INDEX table03_index ON TABLE table03 (column4) AS 'BITMAP' WITH DEFERRED REBUILD;ALTER INDEX table03_index ON table03 REBUILD;SHOW FORMATTED INDEX ON table03;DROP INDEX table03_index ON table03

Create index in a new table: (you can put the index in a separate database)

CREATE INDEX table04_index ON TABLE table04 (column5) AS 'COMPACT' WITH DEFERRED REBUILD IN TABLE table04_index_table

Create index stored as RCFile:

CREATE INDEX table05_index ON TABLE table05 (column6) AS 'COMPACT' STORED AS RCFILE

Create index stored as text file:

CREATE INDEX table06_index ON TABLE table06 (column7) AS 'COMPACT' ROW FORMAT DELIMITED FIELDS TERMINATED BY'\ t 'STORED AS TEXTFILE

Create index with index properties:

CREATE INDEX table07_index ON TABLE table07 (column8) AS 'COMPACT' IDXPROPERTIES ("prop1" = "value1", "prop2" = "value2")

Create index with table properties:

CREATE INDEX table08_index ON TABLE table08 (column9) AS 'COMPACT' TBLPROPERTIES ("prop3" = "value3", "prop4" = "value4")

Drop index if exists:

DROP INDEX IF EXISTS table09_index ON table09

Rebuild index on a partition:

ALTER INDEX table10_index ON table10 PARTITION (columnX='valueQ', columnY='valueR') REBUILD

When build index, it will be realized through mapreduce.

A stus table (name,age)

K,1

W,4

L,1

Index age and index directory in warehouse.

Corresponding to the directory inside.

Is the index file, will be different according to the type of save, the default is text.

Open the index file and you can see

The file location of the indexed object is recorded. In this way, the part can be read to achieve the function of reducing map.

Run a group operation casually according to age. The comparison before and after the index is as follows

Map: 1 Reduce: 1 Cumulative CPU: 3.27 sec HDFS Read: 6694 HDFS Write: 8 SUCCESS

Map: 1 Reduce: 1 Cumulative CPU: 3.14 sec HDFS Read: 6715 HDFS Write: 8 SUCCESS

It is found that although more files are read, the time is faster, the dataset is too small, and the effect may not be obvious.

Index file, in fact, is also a table, so ROW FORMAT DELIMITED FIELDS,TORED AS RCFILE; and other can be used.

It's just that whether the index file format is COMPACT or BITMAP, etc.

The above is all the content of this article "sample Analysis of Hive Index". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.