Sunday, September 27, 2020

Sqoop Complete Tutorial Part-7

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


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.

No comments:

Post a Comment

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...