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.