Search code examples
sqlhadoophivehbaseimpala

Create External Hive Table Pointing to HBase Table

I have a table named "HISTORY" in HBase having column family "VDS" and the column names ROWKEY, ID, START_TIME, END_TIME, VALUE. I am using Cloudera Hadoop Distribution. I want to provide SQL interface to HBase table using Impala. In order to do this we have to create respective External Table in Hive? So how to create external hive table pointing to this HBase table?

Solution

  • Run the following code in Hive Query Editor:

    CREATE EXTERNAL TABLE IF NOT EXISTS HISTORY 
(
    ROWKEY STRING,
    ID STRING,
    START_TIME STRING,
    END_TIME STRING,
    VALUE DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES
(
    "hbase.columns.mapping" = ":key,VDS:ID,VDS:START_TIME,VDS:END_TIME,VDS:VALUE"
)
TBLPROPERTIES("hbase.table.name" = "HISTORY");

    Don't forget to Refresh Impala Metadata after External Table Creation with the following bash command:

    echo "INVALIDATE METADATA" | impala-shell;