Sqoop Export HBase Table into Relational Database

  • Post author:
  • Post last modified:March 8, 2019
  • Post category:BigData
  • Reading time:4 mins read

You can use Apache Sqoop to export HBase table into relational table (RDBMS). Sqoop does not support direct export from HBase to relational databases. You have to use the work around to export data out to relational database, in this article, we will check out Sqoop export HBase table into relational database and steps with an examples.

Sqoop Export HBase Table into Relational Database

Sqoop Export HBase Table into Relational Database

HBase structure doesn’t map very well to the typical relational database such as Netezza, Oracle, SQL Servers etc. In relational databases fixed schema for the tables but, in Hbase there is no fixed schema with number of columns. You cannot directly use sqoop export for your requirement.

Read my other post on Sqoop Import RDBMS table  

Steps to export HBase Table to Relational Database using Sqoop export

Follow below steps to export HBase table to RDBMase

Create Hive External Table on Top of HBase Table

As a first step to export data to RDBMS, you have to create Hive external table on top of HBase table that you want to export. Also create permanent table similar to external table to hold data from external table.

Below is the Hadoop Hive external table command:

CREATE EXTERNAL TABLE hbase_hive_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");

Create Hive permanent table and populate this table with data from external table:

create table hb_exprt_test(id int, name string, city string, age int)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

hive> select * from ext_hbase_hive_personal limit 1;
OK
2 sham Bengaluru 24
Time taken: 0.665 seconds, Fetched: 1 row(s)
hive>

Create Hive table from above external table so that you can directly Hive export directory to RDBMS

hive> insert into hb_exprt_test select * from ext_hbase_hive_personal;

hive> select * from hb_exprt_test;
OK
2 sham Bengaluru 24
3 Guru New Delhi 27
4 John NY 26
5 Rock DC 30

Time taken: 0.107 seconds, Fetched: 4 row(s)
hive>

Export Hive Directory to RDBMS using Sqoop

Second step is to use sqoop command to export data from the external table that we have created in previous step.

Use below Sqoop export command:

sqoop export \
 --connect jdbc:netezza://192.168.41.88/BENCHQADS \
 --username vithal \
 --password vithal \
 --table HB_EXPRT_TEST \
 --export-dir /apps/hive/warehouse/hb_exprt_test \
 --input-fields-terminated-by ',' \
 --lines-terminated-by '\n'

Netezza Table Verification

Now verify the Netezza table to check if data is exported properly.

=> select * from "HB_EXPRT_TEST";
 ID | NAME | CITY | AGE
----+------+-----------+-----
 3 | Guru | New Delhi | 27
 5 | Rock | DC | 30
 4 | John | NY | 26
 2 | sham | Bengaluru | 24
(4 rows)

Please let me know if you find any better method to export data from HBase table to RDBMS.

You can also check out Apache Sqoop import commands and various examples in below articles: 

Tags: ,