Uploaded image for project: 'Sqoop (Retired)'
  1. Sqoop (Retired)
  2. SQOOP-2791

Errors in import when using query with aliases as source

Add voteWatch issue
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • None
    • None
    • codegen
    • None

    Description

      SQOOP always fails with a syntax error when specifying an alias on the split column of the source query.

      $ 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
      

      Results in:

      com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't1.i' in 'field list'
      

      Stack trace:

      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
      

      The query failing in this case is:

        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
      

      This is the query for generating the splits. We can rewrite the scenario to make this particular query successful:

      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
      

      This results in:

      com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'something' in 'where clause'
      

      Stack trace:

       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
      

      The query failing in this case is:

        SELECT t1.i as something, t2.j FROM t1, t2 WHERE ( something >= ? ) AND ( something < ? ) AND t1.i = t2.i
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            estoianovici Eugen Stoianovici

            Dates

              Created:
              Updated:

              Issue deployment