Accessing Hbase table from hive shell

Document created by wade on Feb 27, 2016
Version 1Show Document
  • View in full screen mode

Author: Jitendra Yadav, last modified by Sanjamala Nayeem on May 8, 2015

 

Original Publication Date: May 1, 2015

 

Environment
Hbase 0.94.X
and Hive 0.12, 0.13

 

Steps to create hbase table from hive shell.

1. Before working with hbase, hive need to have hbase and zookeper related jars under it's class path.

 

Required jars.

hive-hbase-handler-<version>.jar,

hbase-<version>.jar,

zookeeper-<version>.jar

 

We can add these through configuration in hive-site.xml or through direct hive shell using "--auxpath"

 

<property>

<name>hive.aux.jars.path</name>

<value>/somepath/hive-hbase-handler-<version>.jar,/somepath/hbase-<version>.jar,/somepath/zookeeper-<version>.jar</value>

</property>

 

OR

 

$ hive --auxpath /somepath/hive-hbase-handler-<version>.jar,/somepath/hbase-<version>.jar,/somepath/zookeeper-<version>.jar

 

2. Create table Hive table that will map to hbase table.

 

hive> CREATE TABLE IF NOT EXISTS pagecounts_hbase (rowkey STRING, pageviews STRING, bytes STRING)

STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,f:c1,f:c2')

TBLPROPERTIES ('hbase.table.name' = 'pagecounts');

 

3. Insert data into hbase table through hive shell.

hive> INSERT OVERWRITE TABLE pagecounts_hbase SELECT * from pageveiws WHERE rowkey LIKE 'en/q%' ;

 

2. Read hbase table data from hive shell.

 

hive> select * from pagecounts_hbase;

OK

en/q:Pan%27s_Labyrinth/20081001-080000  1       1153

en/q:Special:Search/Jazz/20081001-080000        1       980

en/q:Special:Search/peinture/20081001-080000    1       989

en/q:Special:Search/rock/20081001-080000        1       980

Attachments

    Outcomes