Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-3160

Sqoop (import + --table) with Oracle table_name having '$' fails with error (ORA-00942 or java.lang.NoClassDefFoundError)

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      Sqoop (import + --table) with Oracle table_name having '$' fails with error (ORA-00942 or java.lang.NoClassDefFoundError), see test case below.

      Test Case

      #################
      # STEP 01 - Create Data
      #################
      
      export MYCONN=jdbc:oracle:thin:@oracle1.cloudera.com:1521/db11g;
      export MYUSER=sqoop
      export MYPSWD=cloudera
      
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "drop table t\$1"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "create table t\$1 (c1 int, c2 date, c3 varchar(10))"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "insert into t\$1 values (1, current_date, 'some data')"
      sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query "select * from t\$1"
      
      Output:
      -----------------------------------------------
      | C1                   | C2      | C3         | 
      -----------------------------------------------
      | 1                    | 2017-03-21 13:31:18.0 | some data  | 
      -----------------------------------------------
      
      #################
      # STEP 02 - Import with '$' (--table T$1)
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T$1 --target-dir /user/root/t$1 --delete-target-dir --num-mappers 1
      
      Output:
      17/03/21 07:22:30 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "T" t WHERE 1=0
      17/03/21 07:22:30 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      
      #################
      # STEP 03 - Import with '$' (--table T\$1)
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table T\$1 --target-dir /user/root/t$1 --delete-target-dir --num-mappers 1
      
      Output:
      17/03/22 07:30:16 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "T$1" t WHERE 1=0
      17/03/22 07:30:16 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
      ~~~~
      17/03/22 07:30:38 INFO mapreduce.Job: Task Id : attempt_1488818259270_0124_m_000000_0, Status : FAILED
      Error: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
      	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:131)
      	at org.apache.sqoop.mapreduce.db.DBRecordReader.createValue(DBRecordReader.java:197)
      	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:230)
      	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:144)
      	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:415)
      	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1714)
      	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
      Caused by: java.lang.reflect.InvocationTargetException
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
      	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:129)
      	... 14 more
      Caused by: java.lang.NoClassDefFoundError: T$1$1
      	at T$1.init0(T$1.java:43)
      	at T$1.<init>(T$1.java:63)
      	... 19 more
      	
      #################
      # STEP 04 - Import with '$' (--table "T$1")
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table "T$1" --target-dir /user/root/t$1 --delete-target-dir --num-mappers 1
      
      Output:
      17/03/21 07:25:08 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "T" t WHERE 1=0
      17/03/21 07:25:08 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
      
      #################
      # STEP 5 - Import with '$' (--table "T\$1")
      #################
      
      sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table "T\$1" --target-dir /user/root/t\$1 --delete-target-dir --num-mappers 1
      
      Output:
      17/03/21 07:23:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "T$1" t WHERE 1=0
      17/03/21 07:23:05 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
      17/03/21 07:23:06 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/a33565fdf3098886715efc9bccdd031c/T$1.jar
      17/03/21 07:23:08 INFO tool.ImportTool: Destination directory /user/root/t$1 deleted.
      <SNIP>
      17/03/21 07:23:30 INFO mapreduce.Job: Task Id : attempt_1488818259270_0122_m_000000_0, Status : FAILED
      Error: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
      	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:131)
      	at org.apache.sqoop.mapreduce.db.DBRecordReader.createValue(DBRecordReader.java:197)
      	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:230)
      	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:144)
      	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:415)
      	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1714)
      	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
      Caused by: java.lang.reflect.InvocationTargetException
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
      	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:129)
      	... 14 more
      Caused by: java.lang.NoClassDefFoundError: T$1$1
      	at T$1.init0(T$1.java:43)
      	at T$1.<init>(T$1.java:63)
      	... 19 more
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                vasas Vasas Szabolcs
                Reporter:
                markuskemper@me.com Markus Kemper
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: