Sqoop netezza import error - "SQLException in nextKeyValue"

Showing 1-12 of 12 messages
Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 9/25/12 11:22 AM
Hi Guys,

    I am using sqoop command to import data from Netezza. Whenever I use the "sqoop import" command without "--direct" it gives me the error "SQLException in nextKeyValue". 

Here is command(without direct):  sqoop import --table table1 --connect jdbc:netezza://<ip>/schema --driver org.netezza.Driver --hive-table hive_table1 --username '<username>' --password '<password>' --hive-import 
Works fine if "--direct" is included

Stacktrace:
java.io.IOException: SQLException in nextKeyValue
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:458)
        at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:76)
        at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:85)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:139)
        at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:183)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:645)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:325)
        at org.apache.hadoop.mapred.Child$4.run(Child.java:270)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:396)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1232)
        at org.apache.hadoop.mapred.Child.main(Child.java:264)

Re: Sqoop netezza import error - "SQLException in nextKeyValue" Jarek Jarcec Cecho 9/25/12 11:38 PM
Hi Nitin,
would you mind sharing with us following extra information?

* CDH Version
* Cloudera Teradata connector version
* Teradata server version
* Teradata JDBC Driver version
* Task log where this error occurs
* Entire sqoop log generated with --verbose parameter

Jarcec

On Tue, Sep 25, 2012 at 11:22:28AM -0700, nitin@quaero wrote:
> Hi Guys,
>
>     I am using sqoop command to import data from Netezza. Whenever I use
> the "sqoop import" command without "--direct" it gives me the
> error "SQLException in nextKeyValue".
>
> Here is command(without direct):  *sqoop import --table table1 --connect
> jdbc:netezza://<ip>/schema --driver org.netezza.Driver --hive-table
> hive_table1 --username '<username>' --password '<password>' --hive-import *
> *Works fine if "--direct" is included*
>
> Stacktrace:
> java.io.IOException: SQLException in nextKeyValue
>         at
> org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:265)
>         at
> org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:458)
>         at
> org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:76)
>         at
> org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:85)
>         at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:139)
>         at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:183)
>         at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:645)
>         at org.apache.hadoop.mapred.MapTask.run(MapTask.java:325)
>         at org.apache.hadoop.mapred.Child$4.run(Child.java:270)
>         at java.security.AccessController.doPrivileged(Native Method)
>         at javax.security.auth.Subject.doAs(Subject.java:396)
>         at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1232)
>         at org.apache.hadoop.mapred.Child.main(Child.java:264)
>
> --
>
>
>
Re: Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 9/26/12 9:59 AM
Hi Jarcec,

Thanks a lot for replying. Here are the details.

CDH Version: 2.0.0-cdh4.0.1
Netezza Connector Version: 1.1.0
Netezza JDBC Driver Version: 6.0.3 
Netezza Server Version: 6.0.3 (P-5) 

Please find the sqoop logs attached.

Thanks,
Nitin
Re: Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 9/26/12 10:11 AM
I can see there is an issue in the SQL query fired in Netezza. Some table alias name thing in the query. Not sure how it can be solved.
Re: Sqoop netezza import error - "SQLException in nextKeyValue" Jarek Jarcec Cecho 9/26/12 10:44 AM
Hi Nitin,
thank you very much for your feedback. I've read all the logs and I do have couple of notes:

1) It seems that the sqoop log was generated without --verbose flag, would you mind regenerating it?

2) It seems that in your original email you were trying to export table table1, however attached sqoop log is trying to export table F_IMPRESSION. Would you mind sharing entire sqoop command line? Please feel free to substitute password with some dummy value

Also I do have couple of ideas that might help you:

1) Please drop the parameter "--driver org.netezza.Driver". Driver will be correctly chosen by Connector manager automatically. Explicitly specifying --driver might result in using generic jdbc connector instead of netezza one.

2) Also based on the log, I got a feeling that you might be using parameter "--table dbo.F_IMPRESSION"? If that is really the case, please drop the prefix "dbo." as Sqoop is expecting only table name.

Jarcec
> --
>
>
>
Re: Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 9/26/12 2:16 PM
Hi Jarcec,

    Thanks a lot for your help. Removing DBO. resolved the issue. Actually DBO is not a schema name but Netezza allows use of <user-name>.<table-name>, however the table can be accessed using just <table-name>. Didn't know that .

Thanks,
Nitin
Re: Sqoop netezza import error - "SQLException in nextKeyValue" Sumit Ghosh 2/27/13 7:39 PM
Hi,

  1. Here if we have the same table in multiple schema then how do we do the import?
  2. I am getting this error in some cases and in some it is working fine - what can be the possible cause?
Thanks,
Sumit.
Re: Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 2/28/13 8:05 AM
Hi Sumit,

     In my case DBO was actually one of the many users in one schema. Schema(or catalog) is something which you specify in the JDBC connection string. So, you would have different JDBC connection strings for different schema(answer to your first question).  I think netezza doesn't care about usernames in terms of uniqueness. What I mean is you can't have the same table name with different usernames in the same schema. Hope it helps.

Regards,
Nitin

--
 
 
 

Re: Sqoop netezza import error - "SQLException in nextKeyValue" Sumit Ghosh 2/28/13 10:49 AM
Hi Nitin,

I am using IBM DB2.
Can you tell how do we specify the schema in the connection? I am using this
-connect "jdbc:db2://<server name>:port/<DB name>:currentSchema=<schema name>;"

Have a great Day Ahead!
Sumit


--
 
 
 

Re: Sqoop netezza import error - "SQLException in nextKeyValue" nitin@quaero 2/28/13 4:01 PM
Well for netezza I use something like this... jdbc:netezza://<ip>:<port>/<schema-name>. Never used DB2

I think it would be helpful if you post in the following

* CDH Version
* Cloudera Teradata connector version
* Teradata server version
* Teradata JDBC Driver version
* Task log where this error occurs
* Entire sqoop log generated with --verbose parameter


--
 
 
 

Re: Sqoop netezza import error - "SQLException in nextKeyValue" Sumit Ghosh 2/28/13 4:36 PM
Hi Nitin,

IBM DB2 driver Version -
Manifest-Version: 1.0
Created-By: 1.3.1 (IBM Corporation)

Here are the logs:
13/03/01 00:28:00 DEBUG tool.BaseSqoopTool: Enabled debug logging.
13/03/01 00:28:00 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/03/01 00:28:00 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
13/03/01 00:28:00 DEBUG sqoop.ConnFactory: Trying ManagerFactory: com.cloudera.sqoop.manager.DefaultManagerFactory
13/03/01 00:28:00 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:db2:
13/03/01 00:28:00 INFO manager.SqlManager: Using default fetchSize of 1000
13/03/01 00:28:00 DEBUG sqoop.ConnFactory: Instantiated ConnManager com.cloudera.sqoop.manager.Db2Manager@2d342ba4
13/03/01 00:28:00 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.


Have a great Day Ahead!
Sumit


--
 
 
 

Re: Sqoop netezza import error - "SQLException in nextKeyValue" Jarek Jarcec Cecho 2/28/13 7:26 PM
Hi Sumit,
I believe that specifying the currentSchema JDBC parameter should do the trick. Would you mind sharing entire Sqoop command line and generated log with parameter --verbose?

Jarcec
> >>>>    1. Here if we have the same table in multiple schema then how do we
> >>>>    do the import?
> >>>>    2. I am getting this error in some cases and in some it is working
> >>>>>> > >> org.apache.sqoop.mapreduce.db.**DBRecordReader.nextKeyValue(**DBRecordReader.java:265)
> >>>>>>
> >>>>>> > >>
> >>>>>> > >> >         at
> >>>>>> > >> >
> >>>>>> > >> org.apache.hadoop.mapred.**MapTask$**NewTrackingRecordReader.**nextKeyValue(MapTask.java:458)
> >>>>>>
> >>>>>> > >>
> >>>>>> > >> >         at
> >>>>>> > >> >
> >>>>>> > >> org.apache.hadoop.mapreduce.**task.MapContextImpl.**
> >>>>>> nextKeyValue(MapContextImpl.**java:76)
> >>>>>> > >>
> >>>>>> > >> >         at
> >>>>>> > >> >
> >>>>>> > >> org.apache.hadoop.mapreduce.**lib.map.WrappedMapper$Context.**
> >>>>>> nextKeyValue(WrappedMapper.**java:85)
> >>>>>> > >>
> >>>>>> > >> >         at org.apache.hadoop.mapreduce.**Mapper.run(Mapper.java:139)
> >>>>>>
> >>>>>> > >> >         at
> >>>>>> > >> >
> >>>>>> > >> org.apache.sqoop.mapreduce.**AutoProgressMapper.run(**AutoProgressMapper.java:183)
> >>>>>>
> >>>>>> > >>
> >>>>>> > >> >         at
> >>>>>> > >> org.apache.hadoop.mapred.**MapTask.runNewMapper(MapTask.**java:645)
> >>>>>>
> >>>>>> > >> >         at org.apache.hadoop.mapred.**MapTask.run(MapTask.java:325)
> >>>>>>
> >>>>>> > >> >         at org.apache.hadoop.mapred.**Child$4.run(Child.java:270)
> >>>>>>
> >>>>>> > >> >         at java.security.**AccessController.doPrivileged(**Native
> >>>>>> Method)
> >>>>>> > >> >         at javax.security.auth.Subject.**doAs(Subject.java:396)
> >>>>>>
> >>>>>> > >> >         at
> >>>>>> > >> >
> >>>>>> > >> org.apache.hadoop.security.**UserGroupInformation.doAs(**
> >>>>>> UserGroupInformation.java:**1232)
> >>>>>> > >>
> >>>>>> > >> >         at org.apache.hadoop.mapred.**Child.main(Child.java:264)
> >>>>>>
> >>>>>> > >> >
> >>>>>> > >> > --
> >>>>>> > >> >
> >>>>>> > >> >
> >>>>>> > >> >
> >>>>>> > >>
> >>>>>> > >
> >>>>>> >
> >>>>>> > --
> >>>>>> >
> >>>>>> >
> >>>>>> >
> >>>>>>
> >>>>>  --
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>  --
> >>>
> >>>
> >>>
> >>>
> >>
> >>  --
> >>
> >>
> >>
> >>
> >
> >  --
> >
> >
> >
> >
>
> --
>
>
>