Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Netezza external table to HDFS using sqoop

Netezza external table to HDFS using sqoop

Rising Star

I am trying to offload data from Netezza external table to HDFS using sqoop and facing issue.

Netezza external table create statement.

CREATE EXTERNAL TABLE NETEZZA_EMP_EXT(
 employee_id integer, 
 employee_name character varying(100), 
 salary decimal (10,2))
USING (
  dataobject('D:\test\testfile.txt') 
  remotesource 'JDBC'
  delimiter ','
  skiprows 1);

testfile.txt contains below column values

employeeid,employeename,salary
1,'John Lee',100000
2,'Marty Short', 120000
3,'Jane Mars', 150000

Sqoop command:

sqoop import --direct --driver org.netezza.Driver --connection-manager org.apache.sqoop.manager.GenericJdbcManager --connect jdbc:netezza://**.***.***.***:5480/***** --username *** --password ****  --query "select employee_id, employee_name, salary from NETEZZA_EMP_EXT WHERE \$CONDITIONS "   --target-dir /user/aps/test142 --delete-target-dir -m 1

Error:

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:168)
        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:1709)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
Caused by: org.netezza.error.NzSQLException: ERROR:  Transaction rolled back by client
        at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
        at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
        at org.netezza.sql.NzConnection.execute(NzConnection.java:2819)
        at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
        at org.netezza.sql.NzPreparedStatament.executeQuery(NzPreparedStatament.java:172)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
        ... 12 more

Please help

Don't have an account?
Coming from Hortonworks? Activate your account here