Chapter 7: Sqoop Tools and Commands
There are some useful Sqoop tools and commands those will help you a lot in your long run. Also, don’t forget to check our Blog section to keep yourself updated with the latest practice and tips on Hadoop.
Let me show you all these Sqoop useful tools and commands one by one.
How to show all the RDBMS database?
Let’s say we have lots of databases in MySQL and we are not sure about the name of those and you need so that you can find a table. In such case, you may want to list all the database available in your relational database.
Sqoop provides ‘list-databases’ command to show all the databases in your MySQL or any other RDBMS. Below is the complete command-
]$ sqoop list-databases \ --connect jdbc:mysql://localhost/ \ --username root
How to list all the tables in a Database?
Now suppose you have come to know the database name from above commands and want to find the table name? In such case you can check all the table available inside the database using ‘list-tables’ command and the complete command will be like below-
$ sqoop list-tables \ --connect jdbc:mysql://localhost/empdb \ --username root
Here I have tried to find all the tables available in the empdb database.
Sqoop Eval Command
Sqoop Eval command allows user to quickly run user defined SQL queries and get the output on console.
Let’s say we want to execute a simple select query like below-
SELECT * FROM emp LIMIT 3
Then to get the output themselves (I mean without going into MySQL), using the below command and Sqoop Eval function.
$ sqoop eval \ --connect jdbc:mysql://localhost/empdb \ --username root \ --query “SELECT * FROM emp LIMIT 3”
Similarly, if you want to insert a particular record into the MySQL table then you can do this using the Sqoop-eval command like below-
$ sqoop eval \ --connect jdbc:mysql://localhost/empdb \ --username root \ --query “INSERT INTO emp VALUES (105,‘Raj’,‘Sales’,1500,‘Google’)”
Sqoop Codegen command
By default, every database has one DAO class. DAO class contains getter and setter methods to initialize the object.
Sqoop Codegen command generates DAO class automatically. It generates DAO class in Java, based on the Table Schema structure.
For example, let’s generate the Java code for ‘emp’ table in empdb database-
$ sqoop codegen \ --connect jdbc:mysql://localhost/empdb \ --username root \ --table emp