Sqoop import from mysql:

Commands to validate connection from sqoop to mysql

Listing the databases:

sqoop list-databases --connect jdbc:mysql://localhost --username retail_dba --password cloudera


Listing the tables from Database;

sqoop list-tables --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera

Sqoop eval command:

sqoop eval  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --query "select *from departments";


Simple import with target-dir

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir department


Target-dir with --append option

sqoop import  --connect jdbc:mysql://localhost --username retail_dba --password cloudera --table departments --target-dir department --append


Import as avro data file

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir department --as-avrodatafile


Import as sequence file

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir department --as-sequencefile


Import with bopundary Query

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir department  -m 2 --boundary-query "select 2,8 from departments"

Split-by and selecting specific columns

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir department  -m 2 --split-by department_id --columns department_name


Sqoop import-all-tables

 sqoop import-all-tables --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --warehouse-dir /user/cloudera/sqoop_import_all/


Sqoop import all to hive with commpression

sqoop import-all-tables --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --hive-import --hive-overwrite --create-hive-table  --compress --compression-codec org.apache.hadoop.io.compress.SnappyCodec  -num-mappers 1

Sqoop import split-by and query options

sqoop import --connect jdbc:mysql://quickstart.cloudera/retail_db --username retail_dba --password cloudera --e "select *from departments where \$CONDITIONS" --target-dir /user/cloudera/dep --split-by department_id

sqoop import --connect jdbc:mysql://quickstart.cloudera/retail_db --username retail_dba --password cloudera --query "select *from orders join order_items on orders.order_id=order_items.order_item_order_id  where \$CONDITIONS" --target-dir /user/cloudera/dep --split-by order_id

Sqoop import to existing hive table

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --hive-import --hive-overwrite --hive-table departments

Sqoop incremental import

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir depart --incremental append --check-column "department_id" --last-value 9

import with delimiters

sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table dep --hive-import --hive-table departmet1 --null-string NA --null-non-string -1 -m 1


Changing delimiters

 sqoop import  --connect jdbc:mysql://localhost/retail_db --username retail_dba --password cloudera --table departments --target-dir dep2 --enclosed-by \" --fields-terminated-by \| --lines-terminated-by \]