Sqoop : Import Data from MySql to Hive

Following are the steps to import data from MySql to Hive using Sqoop:

1) Login to mysql as follows:

mysql -h hostname -u root -p

2) Create Database in mysql 

mysql>CREATE DATABASE testhadoop;

Also you can use some existing database

3)Grant all privileges to all users to database

mysql>GRANT ALL PRIVILEGES ONtesthadoop.* TO '%'@'host-ip';

mysql>GRANT ALL PRIVILEGES ON testhadoop.* TO ''@'host-ip;

4) Create a table in database and insert some data in it.

mysql>USE testhadoop;

mysql>CREATE TABLE employee( empid INT, empname varchar(20), salray(int));

mysql>INSERT INTO employee VALUES(1,'emp1',15000);

5)For example , table contains following data :

mysql> select * from employee;

+-------+---------+--------+

| empid | empname | salary |

+-------+---------+--------+

|     1 | emp1    |  10000 |

|     2 | emp2    |  15000 |

|     3 | emp3    |  20000 |

+-------+---------+--------+

6) Copy the mysql-connector jar in $SQOOP_HOME/lib

7) Now , employee table from mysql can be imported to Hive using Sqoop as follows:

$ $SQOOP_HOME/bin/sqoop import --connect jdbc:mysql://host-ip/testhadoop --table employee --hive-import

Also, if you just want to import the table structure not the data , it an be done as 

 $ $sqoop create-hive-table --connect jdbc:mysql://host-ip/testhadoop --table employee --fields-terminated-by  ','

8 ) Now, login to hive and check the  table which we have imported

hive> select * from employee;

OK

1    emp1    10000

2    emp2    15000

3    emp3    20000

Time taken: 3.351 seconds

 Tags: Load data from mysql to Hive