SQOOP: Import data from MYSQL database to HDFS and HIVE

To Get MYSQL Server hostname and port number
Use the following commands inside MySQL


use information_schema;
select * from GLOBAL_VARIABLES where variable_name like 'hostname';
select * from GLOBAL_VARIABLES where variable_name like 'port';


Display all the database names present in MYSQL Server

sqoop list-databases --connect  jdbc:mysql://localhost –username=hive –password=hive

IMPORT data from MYSQL TABLE to HIVE TABLE

sqoop import --connect jdbc:mysql://localhost/database_name --username=hive --password=hive --table table_name --hive-import

database_name  -- Replace this with MYSQL database name
table_name  -- Replace this with table name


IMPORT data from MYSQL TABLE with no primary key to HIVE TABLE

sqoop import --connect jdbc:mysql://localhost/database_name --username=hive --password=hive --table table_name --hive-import -m 1

database_name -- Replace with MYSQL Database
table_name  -- Replace with table name in MYSQL

-m 1 -- allocates one mapper.
–split-by column_name
you can use either -m option or --split-by option

To import data from MYSQL to an existing folder in HDFS

sqoop import --connect jdbc:mysql://localhost/Database_Name --username=hive --password=hive --table table_name  --append

database_name -- Replace with MYSQL Database
table_name  -- Replace with table name in MYSQL
--append option to write files in the existing folder.

IMPORT data using MYSQL Query to HDFS

sqoop import --connect jdbc:mysql://localhost/Database_Name --username=hive --password=hive -e’select * from employee where $CONDITIONS’  --target-dir /user/root/database/tablename

 

database_name -- Replace with MYSQL Database

--target-dir option path starts from the home folder.