Sqoop import 'sort by' and 'group by ' issue

Hi All,

I am trying to run the sqoop commands -

sqoop import
–connect jdbc:mysql://ms.itversity.com/retail_db
–username retail_user
–password itversity
–query “select order_id from orders where SORT BY order_id and $CONDITIONS”
–as-avrodatafile
–delete-target-dir
–split-by order_id
–target-dir /user/xyz/sc5

It is throwing an error :…
19/08/27 10:49:43 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check
the manual that corresponds to your MariaDB server version for the right syntax to use near ‘BY order_id and (1 = 0)’ at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the rig
ht syntax to use near ‘BY order_id and (1 = 0)’ at line 1.

I do not understand why ?
Also , the same error comes when I am trying to use :…
–query "select order_status,count(1) order_count from orders where $CONDITIONS and group by order_status order by order_count desc "

I am not sure what I am doing wrong.?
Pls assist.
Thanks.

Hi @dgadiraju , @itversity , @annapurna , @itversity1 , @Itversity_Training , ,

Pls assist in above mentioned issue. Thanks.

Can you try below query and split by order_status:

--query "select order_status,count(1) as order_count from orders where '\$CONDITIONS' group by order_status order by order_count desc

Hi ,
@annapurna :…

I tried below mentioned code snippet , it is running without any error but does not load any data .
sqoop import
–connect jdbc:mysql://ms.itversity.com/h1b_db
–username h1b_user
–password itversity
–delete-target-dir
–target-dir /user/rumanshi/scenarios/two/prob20
–as-textfile
–m 1
–fields-terminated-by ‘\t’
–query “select employer_name ,case_status,count(1) as count from h1b_data where ‘$CONDITIONS’ group by employer_name ,case_status order by employer_name, count desc”

NOTE : backslash is there in the above query , it got removed when I posted it.
result :…
Launched map tasks=1
Other local map tasks=1
Total time spent by all maps in occupied slots (ms)=6010
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=3005
Total vcore-milliseconds taken by all map tasks=3005
Total megabyte-milliseconds taken by all map tasks=6154240
Map-Reduce Framework
Map input records=0
Map output records=0
Input split bytes=87
Spilled Records=0
Failed Shuffles=0
Merged Map outputs=0
GC time elapsed (ms)=44
CPU time spent (ms)=1710
Physical memory (bytes) snapshot=277520384
Virtual memory (bytes) snapshot=3731914752
Total committed heap usage (bytes)=223346688
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=0
19/09/13 06:09:57 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 23.9991 seconds (0 bytes/sec)
19/09/13 06:09:57 INFO mapreduce.ImportJobBase: Retrieved 0 records.

show zero records retrieved .

Pls help me understand the reason behind it .

Thanks.