Sqoop - Left Inner Join Issue

#1

Hello,

I am currently trying to do a Sqoop question involving a left inner join.
It involves getting all the customers who did not place orders - so left inner join between the orders and customers table.

I am using the --query and --split-by approach to solve this.

I am able to get the answer out on MySQL but I am unable to get it working on Sqoop.

My query on MySQL is as follows:
Select oi.customer_fname, oi.customer_lname from customers oi left join orders o on oi.customer_id = o.order_customer_id where o.order_customer_id is null order by customer_lname DESC, customer_fname DESC

On Sqoop, I am iusing the following approach:
Sqoop import
–connect jdbc:mysql://ms.itversity.com/retail_db
–username retail_user
–password itversity
–target-dir /user/por160893/problem2/solution/
–query “Select oi.customer_fname, oi.customer_lname from customers oi left join orders o on oi.customer_id = o.order_customer_id where o.order_customer_id is null AND $conditions order by customer_lname DESC, customer_fname DESC”
–split-by customer_id

The log code I generated is:
ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘oi.customer_order_id’ in
‘on clause’

Can someone please help me on this question?

Many thanks,
Patrick

0 Likes