In the following articles, I will go through some common scenarios when using Sqoop in real world.

NOTE: All the following problem scenarios are based on Cloudera QuickStart VM v5.8 and all the solutions can be reproduced using the aforementioned environment.

Sqoop Eval

Sqoop Eval allows users to execute user-defined queries against respective database servers and preview the results in console.

1
2
3
4
5

sqoop eval \
  --connect "jdbc:mysql://quickstart:3306/retail_db" \
  --username retail_dba \
  --password cloudera \
  --query "select * from retail_db.categories limit 5;"

Results will be something as below

1
2
3
4
5
6
7
8
9

----------------------------------------------------
| category_id | category_department_id | category_name |
----------------------------------------------------
| 1           | 2           | Football             |
| 2           | 2           | Soccer               |
| 3           | 2           | Baseball & Softball  |
| 4           | 2           | Basketball           |
| 5           | 2           | Lacrosse             |
----------------------------------------------------

We can also execute a insert query via sqoop eval like

1
2
3
4
5

sqoop eval \
  --connect "jdbc:mysql://quickstart:3306/retail_db" \
  --username retail_dba \
  --password cloudera \
  --query "insert into categories (category_id, category_department_id, category_name) values(60,9,'Foobar');"

We can verify the result still using sqoop eval

1
2
3
4
5

sqoop eval \
  --connect "jdbc:mysql://quickstart:3306/retail_db" \
  --username retail_dba \
  --password cloudera \
  --query "select * from retail_db.categories where category_id=60;"

1
2
3
4
5

----------------------------------------------------
| category_id | category_department_id | category_name |
----------------------------------------------------
| 60          | 9           | Foobar               | 
----------------------------------------------------

Sqoop List Databases

Sqoop List Databases can help you to display all the databases on the server

1
2
3
4

sqoop list-databases \
  --connect "jdbc:mysql://quickstart:3306/" \
  --username "retail_dba" \
  --password "cloudera"

Results:

1
2

information_schema
retail_db

Sqoop List Tables

Sqoop List Tables can help you to display all the tables in a particular database

1
2
3
4

sqoop list-tables \
  --connect "jdbc:mysql://quickstart:3306/retail_db" \
  --username "retail_dba" \
  --password "cloudera"

Results:

1
2
3
4
5
6

categories
customers
departments
order_items
orders
products