Import data from MySQL to HBase using Sqoop

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:

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

87
0

Join in hive with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Join in pyspark with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Join in spark using scala with example

Requirement You have two table named as A and B. and you want to perform all types of join in ...
Read More

Java UDF to convert String to date in PIG

About Code Many times it happens like you have received data from many systems and each system operates on a ...
Read More
/ java udf, Pig, pig, pig udf, string to date, udf

Leave a Reply

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