Import data from MySQL to HBase using Sqoop

Requirement

Suppose, we have data in a table EMPLOYEE in MySQL database. We want to ingest same data in NoSQL HBase table. So, the requirement is to import data from MySQL to HBase using Sqoop.

Components Involved

Following components are involved in this requirement

  • MySQL – Source for the Ingestion.
  • HBase – Target for the Ingestion.
  • Sqoop – Using to import data from MySQL to HBase.

Sample Data

Below are some sample records for this requirement:

 id,first_name,last_name,gender,designation,city,country
1,Thayne,Mocher,Male,Administrative Officer,Port Colborne,Canada
2,Shelly,Bulfoot,Female,Analyst Programmer,Bacong,Philippines
3,Hercule,Chorlton,Male,Operator,Al Mazzūnah,Tunisia
4,Thorstein,Epton,Male,Administrative Officer,Tayirove,Ukraine
5,Madelena,Savin,Female,Help Desk Technician,Jinjiang,China
6,Adeline,Imesson,Female,Legal Assistant,Fort Beaufort,South Africa
7,Ambros,Richards,Male,Chemical Engineer,Dubiecko,Poland
8,Lilas,Harrowing,Female,Assistant Media Planner,Guayatá,Colombia
9,Freida,Leivers,Female,Legal Assistant,Bangus Kulon,Indonesia
10,Celie,Dolligon,Female,Data Coordiator,Paraty,Brazil
11,Berkley,Orteaux,Male,Assistant Professor,Zinder,Niger
12,Gilburt,Minot,Male,Marketing Assistant,Hanyuan,China
13,Blaine,Treverton,Male,Research Associate,Yuankeng,China
14,Benjamen,Dodd,Male,Assistant Professor,Beberon,Philippines
15,Nikos,Worpole,Male,Human Resources Assistant II,Esmeralda,Cuba

Solutions

We have already done data preparation in MySQL table in this post. You can look into it for MySQL table data. Let’s start with creating the Sqoop job.

Step 1: Create Sqoop Job

We have to create a Sqoop job which will include all the MySQL configuration, Source table name,  target table name etc. Find below sqoop job for ingesting data from MySQL to HBase.

 sqoop import \
      --connect jdbc:mysql://localhost:3306/db_bdp \
      --driver com.mysql.jdbc.Driver \
      --username root \
      --table employee \
      --hbase-create-table \
      --hbase-table employee_details \
      --column-family employees \
      --hbase-row-key id -m 1

hbase-create-table – it will create HBase table if not exist

hbase-table – take table name in which data will get store

column-family – it takes column family value. We have provided value employees.

hbase-row-key – Use MySQL column name for HBase row key

Step 2: Job Execution

In this step, we will execute the sqoop job which we have created in the previous step. We have mentioned the number of mapper to 1.

Step 3: Data Validation

In this step, we can validate the records between MySQL and HBase. To make simple validation, checking an only number of records. In MySQL,  the table EMPLOYEE has 1000 records and same has been ingested into HBase table.

Wrapping Up

In this post, we have gone through a requirement to ingest data from MySQL to HBase table. We created the Sqoop job which took multiple arguments. We understood all the arguments individually.

Sharing is caring!

Subscribe to our newsletter
Loading

Leave a Reply