[
https://issues.apache.org/jira/browse/SQOOP-3049?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15676364#comment-15676364
]
Attila Szabo commented on SQOOP-3049:
-------------------------------------
Hi [~suchitraramani],
Thank you very much to reach out to the Sqoop team with you problem!
You're absolutely right, that this feature/capability is not supported by the
current implementation of Sqoop's MySQL connector.
[~sowmyaramesh] has already implemented quite the same capability for Oracle
already in [SQOOP-2737].
At this point it would make sense that Sqoop team and the component would
provide some general approach/solution for this.
Let me bring this conversation to our dev mail list, and reflect back to you
after the first turn around how do we solve this problem in case of MySQL.
Thanks,
[~maugli]
> Sqoop import fails for MySQL when column name has space
> -------------------------------------------------------
>
> Key: SQOOP-3049
> URL: https://issues.apache.org/jira/browse/SQOOP-3049
> Project: Sqoop
> Issue Type: Bug
> Affects Versions: 1.4.6
> Reporter: Suchitra Ramani
>
> Having trouble with sqoop import command when --where option has column name
> with a space. Database is MySQL.
> MySQL table
> {code}
> create table testsqoop(`x t` integer);
> insert into testsqoop values(1);
> insert into testsqoop values(2);
> insert into testsqoop values(3);
> insert into testsqoop values(4);
> insert into testsqoop values(5);
> select * from testsqoop where `x t` > 2;
> +------+
> | x t |
> +------+
> | 3 |
> | 4 |
> | 5 |
> +------+
> 3 rows in set (0.00 sec)
> {code}
> Sqoop command
> {code}
> sqoop import --connect
> 'jdbc:mysql://127.0.0.1:3306/mytestdb?zeroDateTimeBehavior=convertToNull&?user=test&password=test'
> --username test --password test --driver com.mysql.jdbc.Driver --table
> testsqoop --target-dir /user/tmp/testsqoop -m 1 --hive-import
> --hive-overwrite --hive-table default.test --hive-delims-replacement ' '
> --outdir /tmp/ --direct --where "\`x t\` > 2"
> {code}
> Error
> {code}
> 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:164)
> 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:1657)
> at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
> 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 '> 2 ) AND ( 1=1 ) AND
> ( 1=1 )' 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:422)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
> at com.mysql.jdbc.Util.getInstance(Util.java:381)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
> at
> org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
> at
> org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
> ... 12 more
> {code}
> Note - The free style query option works
> {code}
> sqoop import --connect
> 'jdbc:mysql://127.0.0.1:3306/mytestdb?zeroDateTimeBehavior=convertToNull&?user=test&password=test'
> --username test --password test --driver com.mysql.jdbc.Driver --target-dir
> /user/tmp/testsqooq -m 1 --hive-import --hive-overwrite --hive-table
> default.test --hive-delims-replacement ' ' --outdir /tmp/ --direct --query
> "select * from testsqoop where \`x t\` > 2 AND \$CONDITIONS"
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
