Sqoop import issue-Incorrect syntax


#1

Hi team,

I am using the below import statement using sqoop:-

sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true
–connect jdbc:mysql://ms.itversity.com/h1b_db
–username h1b_user
–password itversity
–query “select EMPLOYER_NAME, CASE_STATUS, count from (select trim(EMPLOYER_NAME) as EMPLOYER_NAME, trim(CASE_STATUS) as CASE_STATUS, count(1) as count from h1b_data group by trim(employer_name),trim(case_status)) as sub where $CONDITIONS order by EMPLOYER_NAME asc, count desc”
–target-dir ‘/user/moumita12/problem20/solution/’
–fields-terminated-by “\t”
–split-by ‘EMPLOYER_NAME’

But getting the followin error:-

18/10/10 17:06:51 INFO mapreduce.Job: Job job_1538287994192_5990 running in uber mode : false
18/10/10 17:06:51 INFO mapreduce.Job: map 0% reduce 0%
18/10/10 17:06:57 INFO mapreduce.Job: Task Id : attempt_1538287994192_5990_m_000002_0, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)
at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1869)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘uNT?:HO)’ ) order by EMPLOYER_NAME asc, count desc’ at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

Please help!


#2

port number is missing from sqoop connect localhost try with using port number

–connect jdbc:mysql://ms.itversity.com:3306/h1b_db


#3

Hi,

i tried using port number. It still fails.
Below is the import statement I am using:-

sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true
–connect jdbc:mysql://ms.itversity.com:3306/h1b_db
–username h1b_user
–password itversity
–query “select EMPLOYER_NAME, CASE_STATUS, count from (select trim(EMPLOYER_NAME) as EMPLOYER_NAME, trim(CASE_STATUS) as CASE_STATUS, count(1) as count from h1b_data group by trim(employer_name),trim(case_status)) as sub where $CONDITIONS order by EMPLOYER_NAME asc, count desc”
–target-dir ‘/user/moumita12/problem20/solution/’
–fields-terminated-by “\t”
–split-by ‘EMPLOYER_NAME’

And this failed with the same error(shown below):-

at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘uNT?:HO)’ ) order by EMPLOYER_NAME asc, count desc’ at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2326)
at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
… 12 more

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143.


#4

You have use ‘\$CONDITIONS’ instead of just $CONDITIONS. Try below command and let us know.

sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://ms.itversity.com:3306/h1b_db \
--username h1b_user \
--password itversity \
--query "select EMPLOYER_NAME, CASE_STATUS, count from (select trim(EMPLOYER_NAME) as EMPLOYER_NAME, trim(CASE_STATUS) as CASE_STATUS, count(1) as count from h1b_data group by trim(employer_name),trim(case_status)) as sub where '\$CONDITIONS' order by EMPLOYER_NAME asc, count desc" \
--target-dir '/user/moumita12/problem20/solution/' \
--fields-terminated-by "\t" \
--split-by 'EMPLOYER_NAME'

#5

Hi,

This works. Thanks!!

Regards,
Moumita


#6

Hi,

I have tried the below import. The import succeeds but no records are loaded. When I try running the same query in sqoop eval, this gives result. Could you please have a look?

sqoop import
–connect jdbc:mysql://ms.itversity.com/h1b_db
–username h1b_user
–password itversity
–query “select EMPLOYER_NAME, CASE_STATUS, count from (select trim(EMPLOYER_NAME) as EMPLOYER_NAME, trim(CASE_STATUS) as CASE_STATUS, count(1) as count from h1b_data group by trim(employer_name),trim(case_status)) as sub where ‘$CONDITIONS’”
–target-dir ‘/user/moumita12/problem20/solution/’
–fields-terminated-by “\t”
–num-mappers 1


#7

The query which you have given is incorrect. Try this query and let us know.

--query "select employer_name,case_status, count from(select trim(EMPLOYER_NAME) as EMPLOYER_NAME, trim(CASE_STATUS) as CASE_STATUS, count(1) as count from h1b_data group by trim(employer_name),trim(case_status)) as count WHERE \$CONDITIONS group by employer_name, case_status desc" \
--split-by case_status