The Hortonworks Community Connection is now live. A completely rebuilt Q&A forum, Knowledge Base, Code Hub and more, backed by the experts in the industry.

You will be redirected here in 10 seconds. If your are not redirected, click here to visit the new site.

The legacy Hortonworks Forum is now closed. You can view a read-only version of the former site by clicking here. The site will be taken offline on January 31,2016

HBase Forum

Hive External Table Pointing to HBASE

  • #45119
    Zin Zin
    Participant

    I have a Hbase table called

    Client_Txn with column famlies
    Txn (Txn_no)
    Dt (Txn_dt)
    Txn_Type (Type)
    Cf1 (val1, val2)
    Cf2 (val3, val4)

    Can I create a Hive Table pointing to this structure

    I basically want to create the Hive Table that refers to this external table and bring in only

    Txn_no
    Txn_dt
    Txn_Type
    Cf1_Val (from Cf1:val1)
    Cf2_Val (from Cf2:val3)

    Is it possible ?

  • Author
    Replies
  • #45125
    Nick Dimiduk
    Moderator

    Yes, you can select which HBase column qualifiers are made available to Hive and using what name. This is the column mapping Serde property. See http://hortonworks.com/blog/using-hive-to-interact-with-hbase-part-2/ for a working (less complex) example.

    #45156
    Zin Zin
    Participant

    Thanks. This is what I tried

    create external table hive_txn (
    id int,
    pol_no string,
    pol_amt int,
    bene_amt int
    )
    stored by
    ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
    WITH SERDEPROPERTIES
    (“hbase.columns.mapping”=”:key,pol:pol_no,cf1:pol_amt,cf2:bene_amt”)
    TBLPROPERTIES
    (“hbase.table.name”=”txn”)

    Getting into the following error

    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org/apache/hadoop/hbase/HBaseConfiguration

    Where cpi;d I be wrong ?

    #45328
    abdelrahman
    Moderator

    Hi Paulie,

    What are the HBase/Hive jars that were added to the Hive query as well as Set values? Here are some steps to help resolve the issue:

    In the Hql do the following:
    — Change this to the Zookeeper servers
    set hbase.zookeeper.quorum=ZKHOST1,ZKHOST2,ZKHOST3;
    set hive.zookeeper.client.port=2181;
    — The following may vary
    set zookeeper.znode.parent=/hbase-unsecure ;

    In order to have all the Jars without the use of “add jar” operator, please follow these steps: (Change the jar versions to the latest)
    – Create a directory called auxlib
    #mkdir /usr/lib/hive/auxlib
    – Copy all of the following Jars to the previous directory:
    /usr/lib/hive/lib/hive-hbase-handler-0.11.0.1.3.0.0-107.jar;
    /usr/lib/zookeeper/zookeeper-3.4.5.1.3.0.0-107.jar ;
    /usr/lib/hbase/hbase-0.94.6.1.3.0.0-107-security.jar;
    /usr/lib/hbase/lib/guava-11.0.2.jar;

    Hope this helps

    Thanks
    -Rahman

    #81399
    Chirag Ahuja
    Participant

    <p style=”box-sizing: border-box; margin-top: 0px; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>Hi</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>I am using HDP 2.3 which comes with Hadoop 2.7.1 , Hbase 1.1.1, and Hive 1.2. So when I am trying Hive-Hbase Integration I am having the following error.</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>“Failed with exception java.io.IOException:java.io.IOException: java.lang.reflect.InvocationTargetException”</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>I have added the following Jars in my hive<br style=”box-sizing: border-box;” />ADD jar ADD jar /usr/hdp/current/hbase-client/lib/hbase-annotations-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-client-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-common-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-hadoop2-compat-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-it-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-prefix-tree-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-procedure-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-protocol-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-server-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-rest-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-shell-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hbase-client/lib/hbase-thrift-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hive-client/lib/hive-hbase-handler-1.2.1.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hive-client/lib/zookeeper-3.4.6.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hive-client/lib/hive-hbase-handler-1.2.1.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hive-client/lib/hbase-client-1.1.2.2.3.2.0-2950.jar;<br style=”box-sizing: border-box;” />ADD jar /usr/hdp/current/hive-client/lib/guava-14.0.1.jar;</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>And also tried setting “hive.aux.jars.path”</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>Also I configured the following in the HiveConf<br style=”box-sizing: border-box;” />hbase.zookeeper.quorum<br style=”box-sizing: border-box;” />hbase.zookeeper.property.clientPort<br style=”box-sizing: border-box;” />zookeeper.znode.parent</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>But still I am facing the</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>“java.lang.ClassNotFoundException: org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory”<br style=”box-sizing: border-box;” />and<br style=”box-sizing: border-box;” />“Failed with exception java.io.IOException:java.io.IOException: java.lang.reflect.InvocationTargetException”</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>Then I looked into<br style=”box-sizing: border-box;” />Section 4.1, Hadoop version support matrix</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>It shows that Hadoop 2.7 is not compatible with Hbase 1.1.</p>
    <p style=”box-sizing: border-box; font-family: ‘Helvetica Neue’, Helvetica, Arial, ‘Open Sans’, ‘Lucida Grande’, sans-serif;”>So, is there any way around so that I can achieve Hive-Hbase Integration ?</p>

    #81400
    Chirag Ahuja
    Participant

    Hi

    I am using HDP 2.3 which comes with Hadoop 2.7.1 , Hbase 1.1.1, and Hive 1.2. So when I am trying Hive-Hbase Integration I am having the following error.

    “Failed with exception java.io.IOException:java.io.IOException: java.lang.reflect.InvocationTargetException”

    I have added the following Jars in my hive

    ADD jar ADD jar /usr/hdp/current/hbase-client/lib/hbase-annotations-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-client-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-common-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-hadoop2-compat-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-it-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-prefix-tree-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-procedure-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-protocol-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-server-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-rest-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-shell-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hbase-client/lib/hbase-thrift-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hive-client/lib/hive-hbase-handler-1.2.1.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hive-client/lib/zookeeper-3.4.6.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hive-client/lib/hive-hbase-handler-1.2.1.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hive-client/lib/hbase-client-1.1.2.2.3.2.0-2950.jar;

    ADD jar /usr/hdp/current/hive-client/lib/guava-14.0.1.jar;

     

    And also tried setting “hive.aux.jars.path”

    Also I configured the following in the HiveConf

    hbase.zookeeper.quorum

    hbase.zookeeper.property.clientPort

    zookeeper.znode.parent

    But still I am facing the

    “java.lang.ClassNotFoundException: org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory”

    and

    “Failed with exception java.io.IOException:java.io.IOException: java.lang.reflect.InvocationTargetException”

    Then I looked into

    Section 4.1, Hadoop version support matrix

     

    It shows that Hadoop 2.7 is not compatible with Hbase 1.1.

    So, is there any way around so that I can achieve Hive-Hbase Integration ?

The forum ‘HBase’ is closed to new topics and replies.

Support from the Experts

A HDP Support Subscription connects you experts with deep experience running Apache Hadoop in production, at-scale on the most demanding workloads.

Enterprise Support »

Become HDP Certified

Real world training designed by the core architects of Hadoop. Scenario-based training courses are available in-classroom or online from anywhere in the world

Training »

Hortonworks Data Platform
The Hortonworks Data Platform is a 100% open source distribution of Apache Hadoop that is truly enterprise grade having been built, tested and hardened with enterprise rigor.
Get started with Sandbox
Hortonworks Sandbox is a self-contained virtual machine with Apache Hadoop pre-configured alongside a set of hands-on, step-by-step Hadoop tutorials.
Modern Data Architecture
Tackle the challenges of big data. Hadoop integrates with existing EDW, RDBMS and MPP systems to deliver lower cost, higher capacity infrastructure.