Using Hive's HBase handler

Hive supports per HIVE-705 HBase integration for SELECT and write INSERT both and is well described Hive's wiki. Note, as of Hive 0.9x the integration requires HBase 0.92x. In this article I'll show how to use existing HBase tables with Hive.

To use Hive in conjunction with HBase, a storage-handler is needed. Per default, the storage handler comes along with your Hive installation and should be available in Hive's lib directory ($HIVE_HOME/lib/hive-hbase-handler*). The handler requires hadoop-0.20x and later as well as zookeeper 3.3.4 and up.

To get Hive and HBase working together, add HBase's config directory into hive-site.xml:

<property>
<name>hive.aux.jars.path</name>
<value>file:///etc/hbase/conf</value>
</property>

and sync the configs (hbase-site.xml as well as hive-site.xml) to your clients. Add a table in Hive using the HBase handler:

CREATE TABLE hbase_test
(
key1 string,
col1 string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:c1 ")
TBLPROPERTIES("hbase.table.name" = "hive_test");

This statement creates the table in HBase as well:

hbase(main):001:0> describe 'hive_test'
DESCRIPTION ENABLED
{NAME => 'hive_test', FAMILIES => [{NAME => 'cf1', BLOOMFILTER => 'NONE', REPLICATION_SCOPE => ' true
0', VERSIONS => '3', COMPRESSION => 'NONE', MIN_VERSIONS => '0', TTL => '2147483647', BLOCKSIZE
=> '65536', IN_MEMORY => 'false', BLOCKCACHE => 'true'}]}
1 row(s) in 0.1190 seconds

Existing HBase tables can be connected as follows, by using the EXTERNAL TABLE statement. The using of the correct ColumnFamily is the key to success, describe shows them:

hbase(main):003:0> describe 't1'
DESCRIPTION ENABLED
{NAME => 't1', FAMILIES => [{NAME => 'f1', BLOOMFILTER => 'NONE', REPLICATION_SCOPE => '0', COMP true
RESSION => 'NONE', VERSIONS => '1', TTL => '2147483647', MIN_VERSIONS => '0', BLOCKSIZE => '6553
6', IN_MEMORY => 'false', BLOCKCACHE => 'true'}]}
1 row(s) in 0.0700 seconds

The new Hive table have to be created with CREATE EXTERNAL TABLE, Hive doesn't support ALTER statements for non-native tables.

CREATE EXTERNAL TABLE hbase_test2
(
key1 string,
col1 string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:c1 ") TBLPROPERTIES("hbase.table.name" = "t1");

hive> describe hbase_test2;
OK
key1 string from deserializer
col1 string from deserializer
Time taken: 0.106 seconds


Comments

  1. Hi,
    When I try to create existing Hbase table in Hive, getting following exception

    FAILED: Error in metadata: java.lang.reflect.UndeclaredThrowableException
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

    Please suggest..

    Thanks
    prasath

    ReplyDelete
  2. Thank you Alexander, this doc is really helpful to me! :)

    ReplyDelete

Post a Comment

Popular posts from this blog

Deal with corrupted messages in Apache Kafka

Hive query shows ERROR "too many counters"

Embedded Linux won't reboot - how to fix and repair