Usually, we learn to import RDBMS data into HDFS but there are times when we need to directly import data from MySQL to Hive using Sqoop.
In this tutorial, I will share how to import data from MySQL to Hive using Sqoop in an easy way. Using this method you can simply import RDBMS data into Hive using Sqoop.
Import MySQL Data to Hive using Sqoop
Just follow the below steps to import MySQL table in Hive using Sqoop.
I. Check MySQL Table emp
Let’s suppose we have one table in MySQL database “empdb” with the name “emp”. The table is looking like below-
dept_no | dept_name |
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales
We have to import the above emp table into Hive using Sqoop command.
II. Now write the Sqoop import scripts to import MySQL data into Hive
sqoop import \
–connect jdbc:mysql://localhost/empdb \
--username userid \
--password pwd \
--table emp \
-m 1 \
--hive-table emp_mysql \
--target-dir /user/hive/warehouse/ \
--enclosed-by '\"' \
--fields-terminated-by , \
--escaped-by \\ \
III. Check the file in HDFS
As we know all the files will get stored in HDFS and so we can check the file in HDFS at the below Hive location as specified in the above query-
IV. Verify the number of records
We can also verify the records like below-
$ hadoop fs -cat /user/hive/warehouse/emp_mysql/part-m-00000 | wc –l
V. Check the imported records in HDFS
If you want to see the table you have imported in HDFS, you can use the below command-
$ hadoop fs -cat /user/hive/warehouse/emp_mysql/part-m-00000 | more
VI. Verify data in Hive
Now as we have imported the data in Hive from MySQL and so let’s verify in Hive to ensure that all the process has been done correctly.
As we created a table in Hive while writing Sqoop command “emp_mysql” and so just do select operation on this table in Hive.
hive> select * from emp_mysql;
If it has been done successfully then you will get the result like below-
"d009" "Customer Service"
"d003" "Human Resources"
"d006" "Quality Management"
This was all about how to import data from MySQL to Hive using Sqoop. Hope this method to import MySQL data in Hive using Sqoop will help you while importing data directly in Hive using Sqoop.
Want to learn Hadoop? Just subscribe us and we will send you our premium courses to you for free.