Access HBase Tables from Impala working Examples

As you know Hadoop Hive or Impala does not properly support transaction data. HBase is best suited for the table which required lot of delete, update, insert etc. You may want to explore the data stored in the HBase table. This article, helps you to understand how to access HBase tables from Impala and we will check out process with an example.

Read other article on loading HBase table from Hive: Loading HBase Table from Apache Hive

Why you want to access the HBase tables from Impala?

This is obvious question, why you want to access the HBase tables from Impala?

You may have one or two transaction tables in HBase that you want to join with Impala tables for some decision making, to explore data or to generate some kind of report. In this scenario, you can access HBase table from Impala as if it is available in Impala, you can join that with other tables and explore HBase table data without actually knowing HBase table manipulation commands.

Access HBase Tables from Impala

You create the tables on the Impala side using the Hive shell, because the Impala CREATE TABLE statement currently does not support custom SerDes and some other syntax needed for these tables. Below is the example to create table on Hive:

Step1: Create Hive external table on top of HBase table

First step is to create the Hive external table on top of HBase table that you want to access. Say, we have personal table create in HBase. Use below script to create external table:

CREATE EXTERNAL TABLE hbase_personal (id INT, name STRING, city STRING, age INT) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,personal_data:name,personal_data:city,personal_data:age") 
TBLPROPERTIES("hbase.table.name" = "personal");

Step2: Issue INVALIDATE METADATA Command

Next step is to issue INVALIDATE METADATA table_name statement makes the table created through Hive visible to Impala.

[localhost:21000] > invalidate metadata hbase_personal;
Fetched 0 row(s) in 0.09s

Query: describe hbase_personal 
+------+--------+---------+ 
| name | type | comment | 
+------+--------+---------+ 
| id | int | | 
| name | string | | 
| city | string | | 
| age | int | | 
+------+--------+---------+

Step3: Query HBase table from Impala

Now you have the external table is visible in Impala, you can access it like any other normal Impala table.

Query: select * from hbase_personal 
+----+------+-----------+-----+ 
| id | name | city | age | 
+----+------+-----------+-----+ 
| 1 | Ram | Bangalore | 25 | 
+----+------+-----------+-----+