[
https://issues.apache.org/jira/browse/SQOOP-2791?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eugen Stoianovici updated SQOOP-2791:
-------------------------------------
Description:
SQOOP always fails with a syntax error when specifying an alias on the split
column of the source query.
{code}
$ sqoop import \
--query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
t1.i = t2.i' \
--target-dir /user/eugen/sqoop1 \
--split-by t1.i \
--connect jdbc:mysql://example.com/eugen \
--username usr1 \
--password-file /user/eugen/sqoop.password
{code}
Results in:
{code}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
't1.i' in 'field list'
{code}
Stack trace:
{code}
ERROR tool.ImportTool: Encountered IOException running import job:
java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Unknown column 't1.i' in 'field list'
at
org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:181)
at
org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:301)
at
org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:318)
at
org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:196)
at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1290)
at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1287)
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.mapreduce.Job.submit(Job.java:1287)
at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1308)
at
org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:196)
at
org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:169)
at
org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:266)
at org.apache.sqoop.manager.SqlManager.importQuery(SqlManager.java:736)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:499)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
column 't1.i' in 'field list'
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:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1524)
at
org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:149)
... 22 more
{code}
The query failing in this case is:
{code}
SELECT MIN(t1.i), MAX(t1.i) FROM (SELECT t1.i as something, t2.j FROM t1, t2
WHERE (1 = 1) AND t1.i = t2.i) AS t1
{code}
This is the query for generating the splits. We can rewrite the scenario to
make this particular query successful:
{code}
sqoop import \
--query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
t1.i = t2.i' \
--target-dir /user/eugen/sqoop1 \
--split-by something \
--connect jdbc:mysql://example.com/eugen \
--username usr1 \
--password-file /user/eugen/sqoop.password
{code}
This results in:
{code}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
'something' in 'where clause'
{code}
Stack trace:
{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: Unknown
column 'something' in 'where clause'
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:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2283)
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}
The query failing in this case is:
{code}
SELECT t1.i as something, t2.j FROM t1, t2 WHERE ( something >= ? ) AND (
something < ? ) AND t1.i = t2.i
{code}
was:
SQOOP always fails with a syntax error when specifying an alias on the split
column of the source query.
{code}
$ sqoop import \
--query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
t1.i = t2.i' \
--target-dir /user/eugen/sqoop1 \
--split-by t1.i \
--connect jdbc:mysql://example.com/eugen \
--username usr1 \
--password-file /user/eugen/sqoop.password
{code}
Results in:
{code}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
't1.i' in 'field list'
{code}
The query failing in this case is:
{code}
SELECT MIN(t1.i), MAX(t1.i) FROM (SELECT t1.i as something, t2.j FROM t1, t2
WHERE (1 = 1) AND t1.i = t2.i) AS t1
{code}
This is the query for generating the splits. We can rewrite the scenario to
make this particular query successful:
{code}
sqoop import \
--query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
t1.i = t2.i' \
--target-dir /user/eugen/sqoop1 \
--split-by something \
--connect jdbc:mysql://example.com/eugen \
--username usr1 \
--password-file /user/eugen/sqoop.password
{code}
This results in:
{code}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
'something' in 'where clause'
{code}
The query failing in this case is:
{code}
SELECT t1.i as something, t2.j FROM t1, t2 WHERE ( something >= ? ) AND (
something < ? ) AND t1.i = t2.i
{code}
> Errors in import when using query with aliases as source
> --------------------------------------------------------
>
> Key: SQOOP-2791
> URL: https://issues.apache.org/jira/browse/SQOOP-2791
> Project: Sqoop
> Issue Type: Bug
> Components: codegen
> Reporter: Eugen Stoianovici
> Priority: Minor
>
> SQOOP always fails with a syntax error when specifying an alias on the split
> column of the source query.
> {code}
> $ sqoop import \
> --query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
> t1.i = t2.i' \
> --target-dir /user/eugen/sqoop1 \
> --split-by t1.i \
> --connect jdbc:mysql://example.com/eugen \
> --username usr1 \
> --password-file /user/eugen/sqoop.password
> {code}
> Results in:
> {code}
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
> 't1.i' in 'field list'
> {code}
> Stack trace:
> {code}
> ERROR tool.ImportTool: Encountered IOException running import job:
> java.io.IOException:
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
> 't1.i' in 'field list'
> at
> org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:181)
> at
> org.apache.hadoop.mapreduce.JobSubmitter.writeNewSplits(JobSubmitter.java:301)
> at
> org.apache.hadoop.mapreduce.JobSubmitter.writeSplits(JobSubmitter.java:318)
> at
> org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:196)
> at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1290)
> at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1287)
> 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.mapreduce.Job.submit(Job.java:1287)
> at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:1308)
> at
> org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:196)
> at
> org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:169)
> at
> org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:266)
> at org.apache.sqoop.manager.SqlManager.importQuery(SqlManager.java:736)
> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:499)
> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
> at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
> at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown
> column 't1.i' in 'field list'
> 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:411)
> at com.mysql.jdbc.Util.getInstance(Util.java:386)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
> at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1524)
> at
> org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:149)
> ... 22 more
> {code}
> The query failing in this case is:
> {code}
> SELECT MIN(t1.i), MAX(t1.i) FROM (SELECT t1.i as something, t2.j FROM t1,
> t2 WHERE (1 = 1) AND t1.i = t2.i) AS t1
> {code}
> This is the query for generating the splits. We can rewrite the scenario to
> make this particular query successful:
> {code}
> sqoop import \
> --query 'SELECT t1.i as something, t2.j FROM t1, t2 WHERE $CONDITIONS AND
> t1.i = t2.i' \
> --target-dir /user/eugen/sqoop1 \
> --split-by something \
> --connect jdbc:mysql://example.com/eugen \
> --username usr1 \
> --password-file /user/eugen/sqoop.password
> {code}
> This results in:
> {code}
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
> 'something' in 'where clause'
> {code}
> Stack trace:
> {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: Unknown
> column 'something' in 'where clause'
> 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:411)
> at com.mysql.jdbc.Util.getInstance(Util.java:386)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
> at
> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2283)
> 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}
> The query failing in this case is:
> {code}
> SELECT t1.i as something, t2.j FROM t1, t2 WHERE ( something >= ? ) AND (
> something < ? ) AND t1.i = t2.i
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
