[
https://issues.apache.org/jira/browse/SQOOP-2791?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15105051#comment-15105051
]
Ping Wang commented on SQOOP-2791:
----------------------------------
Hi Jarek,
I re-created this problem with a simple query with aliases.
1) create table t1 in mysql
mysql> create table t1 (i int, j varchar(10));
2) run the sqoop import command with free-form query
sqoop import --query 'SELECT t1.i as something FROM t1 where $CONDITIONS'
--target-dir /tmp/sqoop7 --split-by t1.i --connect
jdbc:mysql://127.0.0.1:3306/test --username *** --password *** --verbose
Here's the full stack output:
......
16/01/18 01:50:49 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT
MIN(t1.i), MAX(t1.i) FROM (SELECT t1.i as something FROM t1 where (1 = 1) ) AS
t1
16/01/18 01:50:49 INFO mapreduce.JobSubmitter: Cleaning up the staging area
/user/ambari-qa/.staging/job_1453101683908_0006
16/01/18 01:50:49 DEBUG util.ClassLoaderStack: Restoring classloader:
sun.misc.Launcher$AppClassLoader@50b494a6
16/01/18 01:50:49 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:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2505)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
at
org.apache.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:149)
... 22 more
Ran the BoundingValsQuery from mysql hit the same problem. The wrapper SELECT
statement generated by Sqoop did not parse the aliases.
mysql> SELECT MIN(t1.i), MAX(t1.i) FROM (SELECT t1.i as something FROM t1 where
(1 = 1) ) AS t1;
ERROR 1054 (42S22): Unknown column 't1.i' in 'field list'
mysql> SELECT MIN(t1.something), MAX(t1.something) FROM (SELECT t1.i as
something FROM t1 where (1 = 1) ) AS t1;
+-------------------+-------------------+
| MIN(t1.something) | MAX(t1.something) |
+-------------------+-------------------+
| NULL | NULL |
+-------------------+-------------------+
1 row in set (0.00 sec)
> 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}
> 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}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
