All CategoriesBig Data Hadoop & Spark

HBase Write Using Hive

HBase is one of the most popular NoSQL databases which runs on top of the Hadoop eco-system. In this blog, we will be discussing the ways of HBase write into HBase table using Hive.

For learning the basics of HBase, you can refer to our blog on Beginners Guide of HBase.

Now let’s start with creating a reference table in Hive for the table in HBase.

Creating a Table in HBase

Firstly, we will create a table in HBase for storing the employee data as shown below.

Create 'employee','emp_details'

You can see the same in the below screen shot

We have successfully created a table in HBase. There is no data in the table. Let’s quickly insert some data into the table.

put 'employee',1,'emp_details:first_name','Debra'
put 'employee',1,'emp_details:last_name','Burke'
put 'employee',1,'emp_details:email','[email protected]'

In the below screenshot, you can see that we have successfully inserted the data into the HBase table.

Creating an External Table in Hive

Let us query this data from Hive. As we have already created a table in HBase, so we need to create an external table in Hive referring to HBase table. That can be created as shown below.

CREATE EXTERNAL TABLE employee(
  id string COMMENT 'from deserializer',
  first_name string COMMENT 'from deserializer',
  last_name string COMMENT 'from deserializer',
  email string COMMENT 'from deserializer')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
  'hbase.columns.mapping'=':key,emp_details:first_name,emp_details:last_name,emp_details:email',
  'serialization.format'='1')
TBLPROPERTIES (
  'hbase.table.name'='employee'
) ;

We have successfully created a Hive table which refers to the HBase table. You can see the same in the below screen shot.

This is how we can create a reference table for HBase table using Hive. Let us now insert data into this Hive table which in turn will get reflected in HBase table.

Inserting Values into HBase Table Through Hive

For inserting data into the HBase table through Hive, you need to specify the HBase table name in the hive shell by using the below property before running the insert command.

set hbase.mapred.output.outputtable=employee;

If the above property is not set, then you will get an error as shown below.

Job Submission failed with exception 'java.io.IOException(org.apache.hadoop.Hive.ql.metadata.HiveException: java.lang.IllegalArgumentException: Must specify table name)'
FAILED: Execution Error, return code 1 from org.apache.hadoop.Hive.ql.exec.mr.MapRedTask

Let us now insert one record into this Hive table using the below insert statement.

insert into table employee values('2','Robin','Harvey','[email protected]');

In the below screenshot you can see that we have successfully inserted one record.

Let’s check for the same in the HBase table.

In the above screenshot, we can see that the record inserted into Hive got successfully written into the HBase table also.

This sums up the discussion regarding loading a record into HBase through Hive.

Let’s now implement to write the complete file(cluster of many records) into HBase table through Hive. The Hive table which refers to the HBase table is non-native so we can not directly load the data into this table. If you try to do that, you will get the below exception.

FAILED: SemanticException [Error 10101]: A non-native table cannot be used as target for LOAD

So for loading the data in a file, we need to create a normal Hive table and then we need to write the insert overwrite statement. This can be done as follows.

Hadoop

Loading the data in a file into HBase using Hive

Let’s create a staging table for the employee table as shown below.

CREATE TABLE employee_stg (
id STRING,
first_name STRING,
last_name STRING,
email STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

In the below screenshot, we can see that we have successfully loaded the data into staging table.

You can download the emp dataset from here.

Now let’s copy these contents into the employee table using insert overwrite statement.

Insert overwrite table employee select * from employee_stg;

In the below screenshot, you can see that we have successfully written the data into the employee table.

Let’s check for the data in HBase.

Here in HBase, you can see that the complete data has got populated.

So far we have seen how to write data into a table which is present in HBase using Hive.

Creating a table in HBase from Hive

Now let’s see creating a HBase table from Hive itself and inserting the data into that HBase table. This is far simple, earlier we have created an external table in Hive, now we will create a managed table in Hive.

CREATE TABLE employee1(
  id string COMMENT 'from deserializer',
  first_name string COMMENT 'from deserializer',
  last_name string COMMENT 'from deserializer',
  email string COMMENT 'from deserializer')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
  'hbase.columns.mapping'=':key,emp_details:first_name,emp_details:last_name,emp_details:email',
  'serialization.format'='1')
TBLPROPERTIES (
  'hbase.table.name'='employee1'
) ;

Here the above query will create a Hive table with name employee1 and also a HBase table with name employee1.Let’s now insert the data

insert into table employee1 values('2','Robin','Harvey','[email protected]');

In the below screenshot, we can see the same.

Let’s check for the employee1 table in HBase and the data in it.

We have successfully created a HBase table from Hive and inserted the data into it.

Loading the data in a file into HBase using Hive

To insert the data in a file, we need to follow the same procedure which we have used earlier by creating a staging table and using the insert overwrite statement.

Insert overwrite table employee1 select * from employee_stg;

We have successfully inserted the data, let’s check for the data

Let’s check for the complete data in HBase table.

In the above screenshot, we can see the data written successfully into HBase table also.

We hope this blog helped you in understanding how to write data into HBase using Hive. Keep visiting our site www.acadgild.com for more updates on Big data and other technologies.

Hadoop

Tags

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles

Close