Pages

Creating index in Hive


Simple:
CREATE INDEX idx ON TABLE tbl(col_name) AS 'Index_Handler_QClass_Name' IN TABLE tbl_idx;
As to make pluggable indexing algorithms, one has to mention the associated class name that handles indexing say for eg:-org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler
The index handler classes implement HiveIndexHandler
Full Syntax:
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 (...)]
[COMMENT "index comment"]
  • WITH DEFERRED REBUILD - for newly created index is initially empty. REBUILD can be used to make the index up to date.
  • IDXPROPERTIES/TBLPROPERTIES - declaring keyspace properties
  • PARTITIONED BY - table columns where in the index get partitioned, if not specified index spans all table partitions
  • ROW FORMAT  - custom SerDe or using native SerDe(Serializer/Deserializer for Hive read/write). A native SerDe is used if ROW FORMAT is not specified
  • STORED AS  - index table storage format like RCFILE or SEQUENCFILE.The user has to uniquely specify tabl_idx name is required for a qualified index name across tables, otherwise they are named automatically. STORED BY - can be HBase (I haven't tried it)

The index can be stored in hive table or as RCFILE in an hdfs path etc. In this case, the implemented  index handler class usesIndexTable() method will return false.When index is created, the generateIndexBuildTaskList(...) in index handler class will generate a plan for building the index.

Consider CompactIndexHandler from Hive distribution,

It  only stores the addresses of HDFS blocks containing that value. The index is stored in hive metastore FieldSchema as _bucketname and _offsets in the index table.

ie the index table contains 3 columns, with _unparsed_column_names_from_field schema (indexed columns), _bucketname(table partition hdfs file having columns),[" _blockoffsets",..."]



See the code from CompactIndexHandler,
   

11 comments:

  1. It was really a nice article and i was really impressed by reading this Hadoop Admin Online Course Bangalore

    ReplyDelete
  2. Simply want to say your article is as surprising. The clarity on your put up is simply spectacular and that I could assume you are a professional on this subject. Fine with your permission let me to take hold of your RSS feed to keep up to date with forthcoming post. Thanks a million and please carry on the enjoyable work.
    야한동영상
    휴게텔
    외국인출장
    마사지

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Good explanation. Are you looking for best digital marketing services? No look further Avenir Digital Stories is the best digital marketing consultant in India which provide SEO,SMO, web design, corporate stationary, ads banner design and google ads services at an affordable price. Contact us for


    best social media agency in Gurgaon, content marketing agency Gurgaon,
    best digital marketing services in Delhi

    ReplyDelete
  5. Elevate your online presence with powerful dedicated server frankfurt.. Explore our Frankfurt options for global reach. Experience unmatched performance and reliability for your business needs.

    ReplyDelete
  6. Escape to a luxurious best resort in jaipur, where royal elegance meets modern comfort. Enjoy world-class amenities, serene landscapes, and unforgettable experiences.

    ReplyDelete