Bdtjtk

I would like to ask if someone can explain me a Sqoop "query" subcommand functionality in details.

I'm going to use an example of this video:

https://youtu.be/7oZ_CctyS5Q?list=PLf0swTFhTI8rJvGpOp-LujOcpk-Rlz-yE&t=1515

query="select * from orders join order_items on orders.order_id = order_items.order_item_order_id where $CONDITIONS"

As we can see, there is a parameter "$CONDITIONS" added in the where condition.

Probably I'm wrong, but after watch the video, I understood the condition will be replaced as "1=1" if the parameter "$CONDITIONS" exists or by "1=0" if don't.

My questions are:

1- Why it is recommend to use the parameter in the Where clause of the "query" subcommand

2- What happen if we really have a WHERE clause in a parameter which we want to use in the "query" subcommand and not in the "where" subcommand?

Appreciate your help on this,

David.

I have not exactly understood your queries, however let me try to answer. Let me know if you are looking for something else.

1- Why it is recommend to use the parameter in the Where clause of the "query" subcommand

SQOOP imports data in parallel from RDBMS. You can specify no of map tasks (parallel process) to use to perform the import by using the -m or --num-mappers argument. By default 4 map tasks will be used by SQOOP.

For example, if you had a table with a primary key column of order_id whose minimum value was 0 and maximum value was 1000, and SQOOP was directed to use 4 tasks, SQOOP would run four processes which each execute SQL statements of the form
SELECT * FROM orders WHERE $CONDITIONS

Now $CONDITIONS place holder would be replaced as below

1. 
   

   for map task 1: order_id >= 0 and order_id < 250
   and final SQL will look like

   
   
   

   
   

   SELECT * FROM orders WHERE order_id >= 0 and order_id < 250

   
   

   
   


2. 
   

   for map task 2: order_id >= 250 and order_id < 500
   and final SQL will look like

   
   
   

   
   

   SELECT * FROM orders WHERE order_id >= 250 and order_id < 500

   
   

   
   


3. 
   

   for map task 3: order_id >= 500 and order_id < 750
   and final SQL will look like

   
   
   

   
   

   SELECT * FROM orders WHERE order_id >= 500 and order_id < 750

   
   

   
   


4. 
   

   for map task 4: order_id >= 750 and order_id < 1001
   and final SQL will look like

   
   
   

   
   

   SELECT * FROM orders WHERE order_id >= 750 and order_id < 1001

   
   

   
   

As you can see $CONDITIONS is a place holder which SQOOP will search and replace with generated values. Please note that specifying $CONDITIONS is mandatory in case no of map tasks is greater than 1.

2- What happen if we really have a WHERE clause in a parameter which we want to use in the "query" subcommand and not in the "where" subcommand?

You can simply specify your filter criterion after $CONDITIONS prefixed by AND.

For example, if you want to load only those orders where order amount is greater than 100.

query="select * from orders join order_items on orders.order_id = order_items.order_item_order_id where $CONDITIONS AND order_amount > 100"

Hope this clarifies !