How to refer HBase namespace in hive create table "TBLPROPERTIES"

Question

I am trying to create Hive external table with HBase table ('test_table) as source. The HBase table is created under HBase namespace 'test_namespace'. In other sections of applications I access the table using following syntx

test_namespace:test_table or hbase://test_namespace:test_table

Following the same approach I created a hive script

CREATE EXTERNAL TABLE IF NOT EXISTS TEST_INDIVIDUAL(
    key    string,
    test   string,
    photo  string,
    location string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping"="key,default:test,default:photo,default:location")
TBLPROPERTIES("hbase.table.name" = "test_namespace:test_table");

But when I execute the hive script I get the error table not found. If I try

hbase://test_namespace:test_table

it throws invalid table name error.

I tried to google on how to refer HBase namespace inside hive script . But no luck.

Thanks in advance for the help!

Answer 1

I have tried the same problem and it works fine,

Below are the sequence of steps,

A. Perform the operations in HBase Shell

1. Create namespace(i.e Database/Schema in HBase)

   create_namespace 'krishna'

2. Check the existance of namespace

   list_namespace

3. Create table inside namespace

   create 'krishna:hivehbase', 'ratings'

4. Verify table creation inside namespace

   list_namespace_tables 'krishna'

5. Add rows and columnfamily(populate table)

   put 'krishna:hivehbase', 'row1', 'ratings:userid', 'user1' put 'krishna:hivehbase', 'row1', 'ratings:bookid', 'book1' put 'krishna:hivehbase', 'row1', 'ratings:rating', '1'

   put 'krishna:hivehbase', 'row2', 'ratings:userid', 'user2' put 'krishna:hivehbase', 'row2', 'ratings:bookid', 'book1' put 'krishna:hivehbase', 'row2', 'ratings:rating', '3'

   put 'krishna:hivehbase', 'row3', 'ratings:userid', 'user2' put 'krishna:hivehbase', 'row3', 'ratings:bookid', 'book2' put 'krishna:hivehbase', 'row3', 'ratings:rating', '3'

   put 'krishna:hivehbase', 'row4', 'ratings:userid', 'user2' put 'krishna:hivehbase', 'row4', 'ratings:bookid', 'book4' put 'krishna:hivehbase', 'row4', 'ratings:rating', '1'

6. Verify table data

   scan 'krishna:hivehbase'

B. Perform the operations in Hive Shell

1. Create External Table

   CREATE EXTERNAL TABLE hbasehive_table (key string, userid string,bookid string,rating int) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,ratings:userid,ratings:bookid,ratings:rating") TBLPROPERTIES ("hbase.table.name" = "krishna:hivehbase");

2. Verify External Table

   select * from hbasehive_table;