No permission to use SUM in query

sqoop

#1

Hi.

Section 4 Data Ingestion - Apache Sqoop
Lesson 44 on Columns and query

This works:
sqoop-eval --connect jdbc:mysql://ms.itversity.com/retail_db --username retail_user --password itversity --query “select * from orders limit 4”

But this fails:
sqoop-eval --connect jdbc:mysql://ms.itversity.com/retail_db --username retail_user --password itversity --query “select o.*, sum (oi.order_item_subtotal) as order_revenue from orders o join order_items oi on o.order_id = oi.order_items_order_id AND $CONDITIONS group by o.order_id, o.order_date, o.order_customer_id, o.order_status”

And this sql gives the same error:
mysql> select o.*, sum (order_item_subtotal) as order_revenue from orders o join order_items oi on o.order_id = oi.order_items_order_id group by o.order_id, o.order_date, o.order_customer_id, o.order_status limit 5;
ERROR 1370 (42000): execute command denied to user ‘retail_user’@’%’ for routine ‘retail_db.sum’

Please fix this - either my query, or the permissions. Thank you!


#2

@devfactor:

Your original script:

sqoop-eval --connect jdbc:mysql://ms.itversity.com/retail_db --username retail_user --password itversity --query “select o.*, sum (oi.order_item_subtotal) as order_revenue from orders o join order_items oi on o.order_id = oi.order_items_order_id AND $CONDITIONS group by o.order_id, o.order_date, o.order_customer_id, o.order_status”

I did below changes:

  1. as - not required
  2. It’s oi.order_item_order_id (NOT order_items_order_id)
  3. $CONDITIONS = ‘$CONDITIONS’
  4. Point # 3 not required becoz no conditions you provided. If no conditions provided, it will ONLY show header part of the result not with data. So, I removed this.
  5. I added “limit 10” instead to display all records.

Below is my script with all above changes and works successfully.

sqoop-eval
–connect jdbc:mysql://ms.itversity.com/retail_db
–username retail_user
–password itversity
–query “SELECT o.*,sum(oi.order_item_subtotal) order_revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
GROUP BY o.order_id, o.order_date, o.order_customer_id, o.order_status limit 10”

Proof of my script running successfully :

[vanampudi@gw01 ~]$ sqoop-eval --connect jdbc:mysql://ms.itversity.com/retail_db --username retail_user --password itversity --query "SELECT o.*,sum(oi.order_item_subtotal) order_revenue
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
GROUP BY o.order_id, o.order_date, o.order_customer_id, o.order_status limit 10"
Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
18/01/04 19:31:44 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
18/01/04 19:31:44 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
18/01/04 19:31:44 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

| order_id | order_date | order_customer_id | order_status | order_revenue |

| 1 | 2013-07-25 00:00:00.0 | 11599 | CLOSED | 299.9800109863281 |
| 2 | 2013-07-25 00:00:00.0 | 256 | PENDING_PAYMENT | 579.9800109863281 |
| 4 | 2013-07-25 00:00:00.0 | 8827 | CLOSED | 699.8500099182129 |
| 5 | 2013-07-25 00:00:00.0 | 11318 | COMPLETE | 1129.8600387573242 |
| 7 | 2013-07-25 00:00:00.0 | 4530 | COMPLETE | 579.9200134277344 |
| 8 | 2013-07-25 00:00:00.0 | 2911 | PROCESSING | 729.8400115966797 |
| 9 | 2013-07-25 00:00:00.0 | 5657 | PENDING_PAYMENT | 599.9600067138672 |
| 10 | 2013-07-25 00:00:00.0 | 5648 | PENDING_PAYMENT | 651.920015335083 |
| 11 | 2013-07-25 00:00:00.0 | 918 | PAYMENT_REVIEW | 919.7899932861328 |
| 12 | 2013-07-25 00:00:00.0 | 1837 | CLOSED | 1299.8700256347656 |

Hope this helps.
Thanks
Venkat


#3

Wow. Thanks a lot, Venkat, for the detailed answer. I really appreciate your help!

Best regards,
Srini


#4

This must be like this. Not sure why it is showing with " \ " after save the msg.
Otherwise, this $ will be considered as Linux Variable which is not correct in Sqoop.
REPLACE # BELOW WITH BACK SLASH JUST RIGHT BEFORE CONDITIONS

$CONDITIONS = ‘#$CONDITIONS’


#5

Actually, Venkat, I was wondering why I put the $CONDITIONS in the query. While testing just now, I realized it. The sqoop-eval worked yesterday, but sqoop-import was failing with this message:

18/01/04 21:03:20 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Query [SELECT o.*,sum(oi.order_item_subtotal) order_revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_item_order_id GROUP BY o.order_id, o.order_date, o.order_customer_id, o.order_status limit 10] must contain ‘$CONDITIONS’ in WHERE clause.

That is the reason I had added $CONDITIONS.

I just noticed that with each lesson in the class, you guys have also put the sqoop commands. So now I got it. It was my silly mistake of a) connecting to the wrong db, and b) not properly escaping the $CONDITIONS with a backslash. Works great now.

Thank you!
-Srini


#6

@devfactor:

When you use ‘$CONDITIONS’, you can provide “WHERE” condition in your script. This will further filter your output. Glad you got the solution.
Thanks
Venkat