Sqoop Complete Tutorial Part-7

This is the continuation part of "Sqoop Complete Tutorial". If you want to read -

Part-1, please click here. 

Part-2, please click here. 

Part-3, please click here. 

Part-4, please click here. 

Part-5, please click here. 

Part-6, please click here. 

18) Importing all tables from MySQL to Hive  

Importing a table from MySQL to Hive's default database. The below command will help in copying all the tables from MySQL to Hive user database. 

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--hive-database dbTest

The result is below:

If you look at the result, the data is replicated. This is because the tables "emp" and "dept" already exists in the database. hive-overwrite will help in replacing the data if already exist. 

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--warehouse-dir /user/hive/warehouse/dbtest
--hive-database dbtest
--hive-overwrite

Here we have additionally provided the warehouse directory to specify the location of the database.

19) Importing all tables but excluding few from MySQL to Hive  

I have created a table named "location" in my current database 'empdept'.

I am about to import all tables but excluding 'emp' and 'dept' since those were already imported. Since "location" is the only table to import, I can specify the table name, however, let's see how it can be done with sqoop-import-all.

sqoop import-all-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera
--hive-import
--hive-database dbtest
--exclude-tables "emp,dept"

If you look at the above screenshot, the import process selecting only "loc" table and excluding the tables "emp" and "dept" from the import.

The import process is completed and the table schema and data populated into Hive warehouse/database. Let's verify in Hive.

 

Please click here for the next part.

Sqoop Complete Tutorial Part-6

This is the continuation part of "Sqoop Complete Tutorial". If you want to read -

Part-1, please click here. 

Part-2, please click here. 

Part-3, please click here. 

Part-4, please click here. 

Part-5, please click here. 

16) Importing a table from MySQL to Hive's default database.  

Importing a table from MySQL to Hive's default database. The below command will help in copying "emp" table and data from MySQL to Hive "default" database as "employee".

sqoop import
--connect jdbc:mysql://localhost/empdept
--table emp
--username root
--password cloudera
--hive-import
--hive-table employees

Verifying the data:

17) Importing a table from MySQL to Hive's user database.  

Importing a table from MySQL to Hive's default database. The below command will help in copying "emp" table and data from MySQL to Hive's user database (dbTest) as "employee" table.

sqoop import 
--connect jdbc:mysql://localhost/empdept 
--table emp 
--username root 
--password cloudera 
--hive-import 
--hive-table employees
--hive-database dbTest

Verifying the data:

Please click here for the next part.