You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by "Munjuluri, Shyam" <Mu...@AETNA.com> on 2014/07/28 04:02:36 UTC
SQOOP Export to Netezza fails with "--direct" option: Table Owner
not recognized
I was able to successfully import data from Netezza to HDFS with & without the "--direct" option. I'm also able to export data to Netezza from HDFS successfully without the "-direct' option.
But SQOOP fails when I try to export data to Netezza from HDFS with the "-direct" option. It surprisingly does not recognize the "owner" of the table properly. Can you please help check the issue?
NETEZZA DBMS Version: Release 7.0.4.3-P2 [Build 35925]
NETEZZA driver: nzjdbc3.jar
Here is the SQOOP statement & the error;
/sqoop_hdp2/bin/sqoop export
--direct
--connect "jdbc:netezza://netezza-server:5480/NZ_DB;schema=ADMIN;"
--username nzuser --password nzpass
--export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG --table "NZ_TBL_GEOG"
--num-mappers 8
--input-fields-terminated-by ","
--input-escaped-by '\\'
--verbose
Error:
14/07/25 00:56:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
14/07/25 00:56:45 DEBUG tool.BaseSqoopTool: Enabled debug logging.
14/07/25 00:56:45 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
14/07/25 00:56:45 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:netezza:
14/07/25 00:56:45 INFO manager.SqlManager: Using default fetchSize of 1000
14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Instantiated ConnManager
org.apache.sqoop.manager.DirectNetezzaManager@643c0007
14/07/25 00:56:45 INFO tool.CodeGenTool: The connection manager declares that it self manages mapping between records & fields and rows & columns. No class will will be generated.
14/07/25 00:56:46 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
14/07/25 00:56:47 ERROR manager.DirectNetezzaManager: NZ_TBL_GEOG is not a valid Netezza table. Please make sure that you have connected to the Netezza DB and the table name is right. The current values are
connection string : jdbc:netezza:// netezza-server:5480/NZ_DB;schema=admin;
table owner : shyam (windows network login ID)
table name : NZ_TBL_GEOG
Now, I removed the "schema" from the JDBC connection string and added "owner" name to the table name in the "-table" switch. The owner name of the table repeats itself 2 times in the error log below. "NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG".
Here is the SQOOP statement;
/sqoop_hdp2/bin/sqoop export
--direct
--connect "jdbc:netezza://netezza-server:5480/NZ_DB"
--username nzuser --password nzpass
--export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG
--table "ADMIN.NZ_TBL_GEOG"
--num-mappers 8
--input-fields-terminated-by ","
--input-escaped-by '\\'
--verbose
Error:
14/07/25 01:09:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
14/07/25 01:09:07 DEBUG tool.BaseSqoopTool: Enabled debug logging.
14/07/25 01:09:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
14/07/25 01:09:07 DEBUG manager.DefaultManagerFactory: Trying with scheme: jdbc:netezza:
14/07/25 01:09:07 INFO manager.SqlManager: Using default fetchSize of
1000
14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Instantiated ConnManager
org.apache.sqoop.manager.DirectNetezzaManager@4cc39a20
14/07/25 01:09:07 INFO tool.CodeGenTool: The connection manager declares that it self manages mapping between records & fields and rows & columns. No class will will be generated.
14/07/25 01:09:07 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
14/07/25 01:09:09 INFO mapreduce.ExportJobBase: Beginning export of
ADMIN.NZ_TBL_GEOG
14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Attempting to load jar through URL:
jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Previous classloader is
sun.misc.Launcher$AppClassLoader@546b97fd
14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Loaded jar into current
JVM: jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Added classloader for
jar /usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar:
java.net.FactoryURLClassLoader@6655bb93
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in
[jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/
StaticLoggerBinder.class]
SLF4J: Found binding in
[jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/St
aticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
14/07/25 01:09:09 INFO Configuration.deprecation: mapred.jar is
deprecated. Instead, use mapreduce.job.jar
14/07/25 01:09:09 DEBUG mapreduce.JobBase: Using InputFormat: class
org.apache.sqoop.mapreduce.ExportInputFormat
14/07/25 01:09:10 DEBUG db.DBConfiguration: Securing password into job
credentials store
14/07/25 01:09:10 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
14/07/25 01:09:11 ERROR manager.SqlManager: Error executing statement:
org.netezza.error.NzSQLException: ERROR: relation does not exist: NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG
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.NzPreparedStatament._execute(NzPreparedStatament.java:1126)
at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1143)
at org.netezza.sql.NzPreparedStatament.<init(NzPreparedStatament.java:89)
at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1588)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:689)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:712)
at org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlManager.java:132)
at org.apache.sqoop.manager.SqlManager.getColumnNames(SqlManager.java:116)
at org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.configureOutputFormat(NetezzaExternalTableExportJob.java:128)
at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:398)
at org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezzaManager.java:179)
at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
14/07/25 01:09:11 DEBUG util.ClassLoaderStack: Restoring classloader:
sun.misc.Launcher$AppClassLoader@546b97fd
14/07/25 01:09:11 ERROR sqoop.Sqoop: Got exception running Sqoop:
java.lang.NullPointerException java.lang.NullPointerException
at org.apache.sqoop.mapreduce.db.DBOutputFormat.setOutput(DBOutputFormat.java:139)
at org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.configureOutputFormat(NetezzaExternalTableExportJob.java:134)
at org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:398)
at org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezzaManager.java:179)
at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
Thanks,
Shyam Munjuluri
Integrated Systems Engineering
Office: (M, W, F) 860-273-0595
WAH: (Tu, Th) 860-404-5219
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
RE: SQOOP Export to Netezza fails with "--direct" option: Table
Owner not recognized
Posted by "Munjuluri, Shyam" <Mu...@AETNA.com>.
Right, I think that is what I'm seeing.
When I don’t specify anything as the owner, it treats it as 'user_login.tableName' which is incorrect as the table owner is 'ADMIN'.
And if I specify, 'ADMIN.tableName', the log shows it taking as 'ADMIN.ADMIN.tableName' which is also incorrect since the owner name is repeated twice. It looks like there is no way around this. At least, it works without the "direct" option.
Is this something that we will need to report to IBM?
Thanks,
Shyam Munjuluri
Integrated Systems Engineering
Office: (M, W, F) 860-273-0595
WAH: (Tu, Th) 860-404-5219
-----Original Message-----
From: Venkat Ranganathan [mailto:vranganathan@hortonworks.com]
Sent: Monday, July 28, 2014 7:41 PM
To: user@sqoop.apache.org
Subject: Re: SQOOP Export to Netezza fails with "--direct" option: Table Owner not recognized
Netezza connector (direct mode ) uses owner.table scheme. So, if
you specify ADMIN.TABLE, then the owner is ADMIN (not schema). The
schema is the same as the login user. So, if you logon as ADMIN, then the tablename is ADMIN.tablename.
Venkat
On Mon, Jul 28, 2014 at 10:29 AM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
> Thanks Venkat for your reply.
>
> Just to clarify, I'm trying to export to a Netezza table whose schema is: ADMIN. Also, the export works if I do NOT use "--direct" method for a table whose schema is: ADMIN.
>
> So, the issue here is only with the "--direct" method?
>
> Is this something we have to take up with IBM?
>
> Thanks,
>
> Shyam Munjuluri
> Integrated Systems Engineering
> Office: (M, W, F) 860-273-0595
> WAH: (Tu, Th) 860-404-5219
>
> -----Original Message-----
> From: Venkat Ranganathan [mailto:vranganathan@hortonworks.com]
> Sent: Monday, July 28, 2014 1:03 PM
> To: user@sqoop.apache.org
> Subject: Re: SQOOP Export to Netezza fails with "--direct" option:
> Table Owner not recognized
>
> Netezza driver does not support multiple schema support introduced in Netezza 7.x.
>
> Thanks
>
> Venkat
>
> On Sun, Jul 27, 2014 at 7:02 PM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
>> I was able to successfully import data from Netezza to HDFS with &
>> without the “--direct” option. I’m also able to export data to
>> Netezza from HDFS successfully without the “—direct’ option.
>>
>> But SQOOP fails when I try to export data to Netezza from HDFS with
>> the “—direct” option. It surprisingly does not recognize the “owner”
>> of the table properly. Can you please help check the issue?
>>
>>
>>
>> NETEZZA DBMS Version: Release 7.0.4.3-P2 [Build 35925]
>>
>> NETEZZA driver: nzjdbc3.jar
>>
>>
>>
>> Here is the SQOOP statement & the error;
>>
>>
>>
>> /sqoop_hdp2/bin/sqoop export
>>
>> --direct
>>
>> --connect "jdbc:netezza://netezza-server:5480/NZ_DB;schema=ADMIN;"
>>
>> --username nzuser --password nzpass
>>
>> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG --table "NZ_TBL_GEOG"
>>
>> --num-mappers 8
>>
>> --input-fields-terminated-by ","
>>
>> --input-escaped-by '\\'
>>
>> --verbose
>>
>>
>>
>> Error:
>>
>> 14/07/25 00:56:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>>
>> 14/07/25 00:56:45 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>>
>> 14/07/25 00:56:45 WARN tool.BaseSqoopTool: Setting your password on
>> the command-line is insecure. Consider using -P instead.
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Loaded manager factory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 00:56:45 DEBUG manager.DefaultManagerFactory: Trying with scheme:
>> jdbc:netezza:
>>
>> 14/07/25 00:56:45 INFO manager.SqlManager: Using default fetchSize of
>> 1000
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>>
>> org.apache.sqoop.manager.DirectNetezzaManager@643c0007
>>
>> 14/07/25 00:56:45 INFO tool.CodeGenTool: The connection manager
>> declares that it self manages mapping between records & fields and rows & columns.
>> No class will will be generated.
>>
>> 14/07/25 00:56:46 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 00:56:47 ERROR manager.DirectNetezzaManager: NZ_TBL_GEOG is
>> not a valid Netezza table. Please make sure that you have connected
>> to the Netezza DB and the table name is right. The current values
>> are
>>
>>
>>
>> connection string : jdbc:netezza://
>> netezza-server:5480/NZ_DB;schema=admin;
>>
>> table owner : shyam (windows network login ID)
>>
>> table name : NZ_TBL_GEOG
>>
>>
>>
>>
>>
>> Now, I removed the “schema” from the JDBC connection string and
>> added “owner” name to the table name in the “—table” switch. The
>> owner name of the table repeats itself 2 times in the error log below.
>> “NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG”.
>>
>>
>>
>> Here is the SQOOP statement;
>>
>>
>>
>> /sqoop_hdp2/bin/sqoop export
>>
>> --direct
>>
>> --connect "jdbc:netezza://netezza-server:5480/NZ_DB"
>>
>> --username nzuser --password nzpass
>>
>> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG
>>
>> --table "ADMIN.NZ_TBL_GEOG"
>>
>> --num-mappers 8
>>
>> --input-fields-terminated-by ","
>>
>> --input-escaped-by '\\'
>>
>> --verbose
>>
>>
>>
>> Error:
>>
>>
>>
>> 14/07/25 01:09:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>>
>> 14/07/25 01:09:07 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>>
>> 14/07/25 01:09:07 WARN tool.BaseSqoopTool: Setting your password on
>> the command-line is insecure. Consider using -P instead.
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Loaded manager factory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 01:09:07 DEBUG manager.DefaultManagerFactory: Trying with scheme:
>> jdbc:netezza:
>>
>> 14/07/25 01:09:07 INFO manager.SqlManager: Using default fetchSize of
>>
>> 1000
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>>
>> org.apache.sqoop.manager.DirectNetezzaManager@4cc39a20
>>
>> 14/07/25 01:09:07 INFO tool.CodeGenTool: The connection manager
>> declares that it self manages mapping between records & fields and rows & columns.
>> No class will will be generated.
>>
>> 14/07/25 01:09:07 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 01:09:09 INFO mapreduce.ExportJobBase: Beginning export of
>>
>> ADMIN.NZ_TBL_GEOG
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Attempting to load jar
>> through URL:
>>
>> jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Previous classloader
>> is
>>
>> sun.misc.Launcher$AppClassLoader@546b97fd
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Loaded jar into
>> current
>>
>> JVM: jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Added classloader for
>>
>> jar /usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar:
>>
>> java.net.FactoryURLClassLoader@6655bb93
>>
>> SLF4J: Class path contains multiple SLF4J bindings.
>>
>> SLF4J: Found binding in
>>
>>
>> [jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl
>> /
>>
>> StaticLoggerBinder.class]
>>
>> SLF4J: Found binding in
>>
>>
>> [jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/S
>> t
>>
>> aticLoggerBinder.class]
>>
>> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
>> explanation.
>>
>> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
>>
>> 14/07/25 01:09:09 INFO Configuration.deprecation: mapred.jar is
>>
>> deprecated. Instead, use mapreduce.job.jar
>>
>> 14/07/25 01:09:09 DEBUG mapreduce.JobBase: Using InputFormat: class
>>
>> org.apache.sqoop.mapreduce.ExportInputFormat
>>
>> 14/07/25 01:09:10 DEBUG db.DBConfiguration: Securing password into
>> job
>>
>> credentials store
>>
>> 14/07/25 01:09:10 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 01:09:11 ERROR manager.SqlManager: Error executing statement:
>>
>> org.netezza.error.NzSQLException: ERROR: relation does not exist:
>> NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG
>>
>> at
>> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:2
>> 8
>> 0)
>>
>> at
>> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
>>
>> at
>> org.netezza.sql.NzConnection.execute(NzConnection.java:2819)
>>
>> at
>> org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:
>> 1126)
>>
>> at
>> org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:
>> 1
>> 143)
>>
>> at
>> org.netezza.sql.NzPreparedStatament.<init(NzPreparedStatament.java:89
>> )
>>
>> at
>> org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1588)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:689)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:712)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlMana
>> g
>> er.java:132)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnNames(SqlManager.java:11
>> 6
>> )
>>
>> at
>> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.conf
>> i
>> gureOutputFormat(NetezzaExternalTableExportJob.java:128)
>>
>> at
>> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
>> 398)
>>
>> at
>> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetez
>> z
>> aManager.java:179)
>>
>> at
>> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>>
>> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>>
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>>
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>>
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>>
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>>
>> 14/07/25 01:09:11 DEBUG util.ClassLoaderStack: Restoring classloader:
>>
>> sun.misc.Launcher$AppClassLoader@546b97fd
>>
>> 14/07/25 01:09:11 ERROR sqoop.Sqoop: Got exception running Sqoop:
>>
>> java.lang.NullPointerException java.lang.NullPointerException
>>
>> at
>> org.apache.sqoop.mapreduce.db.DBOutputFormat.setOutput(DBOutputFormat.
>> java:139)
>>
>> at
>> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.conf
>> i
>> gureOutputFormat(NetezzaExternalTableExportJob.java:134)
>>
>> at
>> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
>> 398)
>>
>> at
>> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetez
>> z
>> aManager.java:179)
>>
>> at
>> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>>
>> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>>
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>>
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>>
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>>
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>>
>>
>>
>>
>>
>> Thanks,
>>
>>
>>
>>
>>
>> Shyam Munjuluri
>> Integrated Systems Engineering
>> Office: (M, W, F) 860-273-0595
>> WAH: (Tu, Th) 860-404-5219
>>
>>
>>
>> This e-mail may contain confidential or privileged information. If
>> you think you have received this e-mail in error, please advise the
>> sender by reply e-mail and then delete this e-mail immediately. Thank you.
>> Aetna
>
> --
> CONFIDENTIALITY NOTICE
> NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
--
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
Re: SQOOP Export to Netezza fails with "--direct" option: Table Owner
not recognized
Posted by Venkat Ranganathan <vr...@hortonworks.com>.
Netezza connector (direct mode ) uses owner.table scheme. So, if
you specify ADMIN.TABLE, then the owner is ADMIN (not schema). The
schema is the same as the login user. So, if you logon as ADMIN, then
the tablename is ADMIN.tablename.
Venkat
On Mon, Jul 28, 2014 at 10:29 AM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
> Thanks Venkat for your reply.
>
> Just to clarify, I'm trying to export to a Netezza table whose schema is: ADMIN. Also, the export works if I do NOT use "--direct" method for a table whose schema is: ADMIN.
>
> So, the issue here is only with the "--direct" method?
>
> Is this something we have to take up with IBM?
>
> Thanks,
>
> Shyam Munjuluri
> Integrated Systems Engineering
> Office: (M, W, F) 860-273-0595
> WAH: (Tu, Th) 860-404-5219
>
> -----Original Message-----
> From: Venkat Ranganathan [mailto:vranganathan@hortonworks.com]
> Sent: Monday, July 28, 2014 1:03 PM
> To: user@sqoop.apache.org
> Subject: Re: SQOOP Export to Netezza fails with "--direct" option: Table Owner not recognized
>
> Netezza driver does not support multiple schema support introduced in Netezza 7.x.
>
> Thanks
>
> Venkat
>
> On Sun, Jul 27, 2014 at 7:02 PM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
>> I was able to successfully import data from Netezza to HDFS with &
>> without the “--direct” option. I’m also able to export data to Netezza
>> from HDFS successfully without the “—direct’ option.
>>
>> But SQOOP fails when I try to export data to Netezza from HDFS with
>> the “—direct” option. It surprisingly does not recognize the “owner”
>> of the table properly. Can you please help check the issue?
>>
>>
>>
>> NETEZZA DBMS Version: Release 7.0.4.3-P2 [Build 35925]
>>
>> NETEZZA driver: nzjdbc3.jar
>>
>>
>>
>> Here is the SQOOP statement & the error;
>>
>>
>>
>> /sqoop_hdp2/bin/sqoop export
>>
>> --direct
>>
>> --connect "jdbc:netezza://netezza-server:5480/NZ_DB;schema=ADMIN;"
>>
>> --username nzuser --password nzpass
>>
>> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG --table "NZ_TBL_GEOG"
>>
>> --num-mappers 8
>>
>> --input-fields-terminated-by ","
>>
>> --input-escaped-by '\\'
>>
>> --verbose
>>
>>
>>
>> Error:
>>
>> 14/07/25 00:56:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>>
>> 14/07/25 00:56:45 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>>
>> 14/07/25 00:56:45 WARN tool.BaseSqoopTool: Setting your password on
>> the command-line is insecure. Consider using -P instead.
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Loaded manager factory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 00:56:45 DEBUG manager.DefaultManagerFactory: Trying with scheme:
>> jdbc:netezza:
>>
>> 14/07/25 00:56:45 INFO manager.SqlManager: Using default fetchSize of
>> 1000
>>
>> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>>
>> org.apache.sqoop.manager.DirectNetezzaManager@643c0007
>>
>> 14/07/25 00:56:45 INFO tool.CodeGenTool: The connection manager
>> declares that it self manages mapping between records & fields and rows & columns.
>> No class will will be generated.
>>
>> 14/07/25 00:56:46 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 00:56:47 ERROR manager.DirectNetezzaManager: NZ_TBL_GEOG is
>> not a valid Netezza table. Please make sure that you have connected
>> to the Netezza DB and the table name is right. The current values are
>>
>>
>>
>> connection string : jdbc:netezza://
>> netezza-server:5480/NZ_DB;schema=admin;
>>
>> table owner : shyam (windows network login ID)
>>
>> table name : NZ_TBL_GEOG
>>
>>
>>
>>
>>
>> Now, I removed the “schema” from the JDBC connection string and added
>> “owner” name to the table name in the “—table” switch. The owner name
>> of the table repeats itself 2 times in the error log below.
>> “NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG”.
>>
>>
>>
>> Here is the SQOOP statement;
>>
>>
>>
>> /sqoop_hdp2/bin/sqoop export
>>
>> --direct
>>
>> --connect "jdbc:netezza://netezza-server:5480/NZ_DB"
>>
>> --username nzuser --password nzpass
>>
>> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG
>>
>> --table "ADMIN.NZ_TBL_GEOG"
>>
>> --num-mappers 8
>>
>> --input-fields-terminated-by ","
>>
>> --input-escaped-by '\\'
>>
>> --verbose
>>
>>
>>
>> Error:
>>
>>
>>
>> 14/07/25 01:09:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>>
>> 14/07/25 01:09:07 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>>
>> 14/07/25 01:09:07 WARN tool.BaseSqoopTool: Setting your password on
>> the command-line is insecure. Consider using -P instead.
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Loaded manager factory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>>
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>>
>> 14/07/25 01:09:07 DEBUG manager.DefaultManagerFactory: Trying with scheme:
>> jdbc:netezza:
>>
>> 14/07/25 01:09:07 INFO manager.SqlManager: Using default fetchSize of
>>
>> 1000
>>
>> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>>
>> org.apache.sqoop.manager.DirectNetezzaManager@4cc39a20
>>
>> 14/07/25 01:09:07 INFO tool.CodeGenTool: The connection manager
>> declares that it self manages mapping between records & fields and rows & columns.
>> No class will will be generated.
>>
>> 14/07/25 01:09:07 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 01:09:09 INFO mapreduce.ExportJobBase: Beginning export of
>>
>> ADMIN.NZ_TBL_GEOG
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Attempting to load jar
>> through URL:
>>
>> jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Previous classloader is
>>
>> sun.misc.Launcher$AppClassLoader@546b97fd
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Loaded jar into current
>>
>> JVM: jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>>
>> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Added classloader for
>>
>> jar /usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar:
>>
>> java.net.FactoryURLClassLoader@6655bb93
>>
>> SLF4J: Class path contains multiple SLF4J bindings.
>>
>> SLF4J: Found binding in
>>
>>
>> [jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/
>>
>> StaticLoggerBinder.class]
>>
>> SLF4J: Found binding in
>>
>>
>> [jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/St
>>
>> aticLoggerBinder.class]
>>
>> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
>> explanation.
>>
>> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
>>
>> 14/07/25 01:09:09 INFO Configuration.deprecation: mapred.jar is
>>
>> deprecated. Instead, use mapreduce.job.jar
>>
>> 14/07/25 01:09:09 DEBUG mapreduce.JobBase: Using InputFormat: class
>>
>> org.apache.sqoop.mapreduce.ExportInputFormat
>>
>> 14/07/25 01:09:10 DEBUG db.DBConfiguration: Securing password into job
>>
>> credentials store
>>
>> 14/07/25 01:09:10 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>>
>> 14/07/25 01:09:11 ERROR manager.SqlManager: Error executing statement:
>>
>> org.netezza.error.NzSQLException: ERROR: relation does not exist:
>> NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG
>>
>> at
>> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:28
>> 0)
>>
>> at
>> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
>>
>> at
>> org.netezza.sql.NzConnection.execute(NzConnection.java:2819)
>>
>> at
>> org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:
>> 1126)
>>
>> at
>> org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1
>> 143)
>>
>> at
>> org.netezza.sql.NzPreparedStatament.<init(NzPreparedStatament.java:89)
>>
>> at
>> org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1588)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:689)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:712)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlManag
>> er.java:132)
>>
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnNames(SqlManager.java:116
>> )
>>
>> at
>> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.confi
>> gureOutputFormat(NetezzaExternalTableExportJob.java:128)
>>
>> at
>> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
>> 398)
>>
>> at
>> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezz
>> aManager.java:179)
>>
>> at
>> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>>
>> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>>
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>>
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>>
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>>
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>>
>> 14/07/25 01:09:11 DEBUG util.ClassLoaderStack: Restoring classloader:
>>
>> sun.misc.Launcher$AppClassLoader@546b97fd
>>
>> 14/07/25 01:09:11 ERROR sqoop.Sqoop: Got exception running Sqoop:
>>
>> java.lang.NullPointerException java.lang.NullPointerException
>>
>> at
>> org.apache.sqoop.mapreduce.db.DBOutputFormat.setOutput(DBOutputFormat.
>> java:139)
>>
>> at
>> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.confi
>> gureOutputFormat(NetezzaExternalTableExportJob.java:134)
>>
>> at
>> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
>> 398)
>>
>> at
>> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezz
>> aManager.java:179)
>>
>> at
>> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>>
>> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>>
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>>
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>>
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>>
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>>
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>>
>>
>>
>>
>>
>> Thanks,
>>
>>
>>
>>
>>
>> Shyam Munjuluri
>> Integrated Systems Engineering
>> Office: (M, W, F) 860-273-0595
>> WAH: (Tu, Th) 860-404-5219
>>
>>
>>
>> This e-mail may contain confidential or privileged information. If you
>> think you have received this e-mail in error, please advise the sender
>> by reply e-mail and then delete this e-mail immediately. Thank you.
>> Aetna
>
> --
> CONFIDENTIALITY NOTICE
> NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
--
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is confidential,
privileged and exempt from disclosure under applicable law. If the reader
of this message is not the intended recipient, you are hereby notified that
any printing, copying, dissemination, distribution, disclosure or
forwarding of this communication is strictly prohibited. If you have
received this communication in error, please contact the sender immediately
and delete it from your system. Thank You.
RE: SQOOP Export to Netezza fails with "--direct" option: Table
Owner not recognized
Posted by "Munjuluri, Shyam" <Mu...@AETNA.com>.
Thanks Venkat for your reply.
Just to clarify, I'm trying to export to a Netezza table whose schema is: ADMIN. Also, the export works if I do NOT use "--direct" method for a table whose schema is: ADMIN.
So, the issue here is only with the "--direct" method?
Is this something we have to take up with IBM?
Thanks,
Shyam Munjuluri
Integrated Systems Engineering
Office: (M, W, F) 860-273-0595
WAH: (Tu, Th) 860-404-5219
-----Original Message-----
From: Venkat Ranganathan [mailto:vranganathan@hortonworks.com]
Sent: Monday, July 28, 2014 1:03 PM
To: user@sqoop.apache.org
Subject: Re: SQOOP Export to Netezza fails with "--direct" option: Table Owner not recognized
Netezza driver does not support multiple schema support introduced in Netezza 7.x.
Thanks
Venkat
On Sun, Jul 27, 2014 at 7:02 PM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
> I was able to successfully import data from Netezza to HDFS with &
> without the “--direct” option. I’m also able to export data to Netezza
> from HDFS successfully without the “—direct’ option.
>
> But SQOOP fails when I try to export data to Netezza from HDFS with
> the “—direct” option. It surprisingly does not recognize the “owner”
> of the table properly. Can you please help check the issue?
>
>
>
> NETEZZA DBMS Version: Release 7.0.4.3-P2 [Build 35925]
>
> NETEZZA driver: nzjdbc3.jar
>
>
>
> Here is the SQOOP statement & the error;
>
>
>
> /sqoop_hdp2/bin/sqoop export
>
> --direct
>
> --connect "jdbc:netezza://netezza-server:5480/NZ_DB;schema=ADMIN;"
>
> --username nzuser --password nzpass
>
> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG --table "NZ_TBL_GEOG"
>
> --num-mappers 8
>
> --input-fields-terminated-by ","
>
> --input-escaped-by '\\'
>
> --verbose
>
>
>
> Error:
>
> 14/07/25 00:56:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>
> 14/07/25 00:56:45 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>
> 14/07/25 00:56:45 WARN tool.BaseSqoopTool: Setting your password on
> the command-line is insecure. Consider using -P instead.
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Loaded manager factory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 00:56:45 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:netezza:
>
> 14/07/25 00:56:45 INFO manager.SqlManager: Using default fetchSize of
> 1000
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>
> org.apache.sqoop.manager.DirectNetezzaManager@643c0007
>
> 14/07/25 00:56:45 INFO tool.CodeGenTool: The connection manager
> declares that it self manages mapping between records & fields and rows & columns.
> No class will will be generated.
>
> 14/07/25 00:56:46 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 00:56:47 ERROR manager.DirectNetezzaManager: NZ_TBL_GEOG is
> not a valid Netezza table. Please make sure that you have connected
> to the Netezza DB and the table name is right. The current values are
>
>
>
> connection string : jdbc:netezza://
> netezza-server:5480/NZ_DB;schema=admin;
>
> table owner : shyam (windows network login ID)
>
> table name : NZ_TBL_GEOG
>
>
>
>
>
> Now, I removed the “schema” from the JDBC connection string and added
> “owner” name to the table name in the “—table” switch. The owner name
> of the table repeats itself 2 times in the error log below.
> “NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG”.
>
>
>
> Here is the SQOOP statement;
>
>
>
> /sqoop_hdp2/bin/sqoop export
>
> --direct
>
> --connect "jdbc:netezza://netezza-server:5480/NZ_DB"
>
> --username nzuser --password nzpass
>
> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG
>
> --table "ADMIN.NZ_TBL_GEOG"
>
> --num-mappers 8
>
> --input-fields-terminated-by ","
>
> --input-escaped-by '\\'
>
> --verbose
>
>
>
> Error:
>
>
>
> 14/07/25 01:09:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>
> 14/07/25 01:09:07 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>
> 14/07/25 01:09:07 WARN tool.BaseSqoopTool: Setting your password on
> the command-line is insecure. Consider using -P instead.
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Loaded manager factory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 01:09:07 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:netezza:
>
> 14/07/25 01:09:07 INFO manager.SqlManager: Using default fetchSize of
>
> 1000
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>
> org.apache.sqoop.manager.DirectNetezzaManager@4cc39a20
>
> 14/07/25 01:09:07 INFO tool.CodeGenTool: The connection manager
> declares that it self manages mapping between records & fields and rows & columns.
> No class will will be generated.
>
> 14/07/25 01:09:07 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 01:09:09 INFO mapreduce.ExportJobBase: Beginning export of
>
> ADMIN.NZ_TBL_GEOG
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Attempting to load jar
> through URL:
>
> jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Previous classloader is
>
> sun.misc.Launcher$AppClassLoader@546b97fd
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Loaded jar into current
>
> JVM: jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Added classloader for
>
> jar /usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar:
>
> java.net.FactoryURLClassLoader@6655bb93
>
> SLF4J: Class path contains multiple SLF4J bindings.
>
> SLF4J: Found binding in
>
>
> [jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/
>
> StaticLoggerBinder.class]
>
> SLF4J: Found binding in
>
>
> [jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/St
>
> aticLoggerBinder.class]
>
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
>
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
>
> 14/07/25 01:09:09 INFO Configuration.deprecation: mapred.jar is
>
> deprecated. Instead, use mapreduce.job.jar
>
> 14/07/25 01:09:09 DEBUG mapreduce.JobBase: Using InputFormat: class
>
> org.apache.sqoop.mapreduce.ExportInputFormat
>
> 14/07/25 01:09:10 DEBUG db.DBConfiguration: Securing password into job
>
> credentials store
>
> 14/07/25 01:09:10 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 01:09:11 ERROR manager.SqlManager: Error executing statement:
>
> org.netezza.error.NzSQLException: ERROR: relation does not exist:
> NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG
>
> at
> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:28
> 0)
>
> at
> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
>
> at
> org.netezza.sql.NzConnection.execute(NzConnection.java:2819)
>
> at
> org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:
> 1126)
>
> at
> org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1
> 143)
>
> at
> org.netezza.sql.NzPreparedStatament.<init(NzPreparedStatament.java:89)
>
> at
> org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1588)
>
> at
> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:689)
>
> at
> org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:712)
>
> at
> org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlManag
> er.java:132)
>
> at
> org.apache.sqoop.manager.SqlManager.getColumnNames(SqlManager.java:116
> )
>
> at
> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.confi
> gureOutputFormat(NetezzaExternalTableExportJob.java:128)
>
> at
> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
> 398)
>
> at
> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezz
> aManager.java:179)
>
> at
> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>
> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>
> 14/07/25 01:09:11 DEBUG util.ClassLoaderStack: Restoring classloader:
>
> sun.misc.Launcher$AppClassLoader@546b97fd
>
> 14/07/25 01:09:11 ERROR sqoop.Sqoop: Got exception running Sqoop:
>
> java.lang.NullPointerException java.lang.NullPointerException
>
> at
> org.apache.sqoop.mapreduce.db.DBOutputFormat.setOutput(DBOutputFormat.
> java:139)
>
> at
> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.confi
> gureOutputFormat(NetezzaExternalTableExportJob.java:134)
>
> at
> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:
> 398)
>
> at
> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezz
> aManager.java:179)
>
> at
> org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>
> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>
>
>
>
>
> Thanks,
>
>
>
>
>
> Shyam Munjuluri
> Integrated Systems Engineering
> Office: (M, W, F) 860-273-0595
> WAH: (Tu, Th) 860-404-5219
>
>
>
> This e-mail may contain confidential or privileged information. If you
> think you have received this e-mail in error, please advise the sender
> by reply e-mail and then delete this e-mail immediately. Thank you.
> Aetna
--
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
Re: SQOOP Export to Netezza fails with "--direct" option: Table Owner
not recognized
Posted by Venkat Ranganathan <vr...@hortonworks.com>.
Netezza driver does not support multiple schema support introduced in
Netezza 7.x.
Thanks
Venkat
On Sun, Jul 27, 2014 at 7:02 PM, Munjuluri, Shyam <Mu...@aetna.com> wrote:
> I was able to successfully import data from Netezza to HDFS with & without
> the “--direct” option. I’m also able to export data to Netezza from HDFS
> successfully without the “—direct’ option.
>
> But SQOOP fails when I try to export data to Netezza from HDFS with the
> “—direct” option. It surprisingly does not recognize the “owner” of the
> table properly. Can you please help check the issue?
>
>
>
> NETEZZA DBMS Version: Release 7.0.4.3-P2 [Build 35925]
>
> NETEZZA driver: nzjdbc3.jar
>
>
>
> Here is the SQOOP statement & the error;
>
>
>
> /sqoop_hdp2/bin/sqoop export
>
> --direct
>
> --connect "jdbc:netezza://netezza-server:5480/NZ_DB;schema=ADMIN;"
>
> --username nzuser --password nzpass
>
> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG --table "NZ_TBL_GEOG"
>
> --num-mappers 8
>
> --input-fields-terminated-by ","
>
> --input-escaped-by '\\'
>
> --verbose
>
>
>
> Error:
>
> 14/07/25 00:56:45 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>
> 14/07/25 00:56:45 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>
> 14/07/25 00:56:45 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Loaded manager factory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 00:56:45 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:netezza:
>
> 14/07/25 00:56:45 INFO manager.SqlManager: Using default fetchSize of 1000
>
> 14/07/25 00:56:45 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>
> org.apache.sqoop.manager.DirectNetezzaManager@643c0007
>
> 14/07/25 00:56:45 INFO tool.CodeGenTool: The connection manager declares
> that it self manages mapping between records & fields and rows & columns.
> No class will will be generated.
>
> 14/07/25 00:56:46 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 00:56:47 ERROR manager.DirectNetezzaManager: NZ_TBL_GEOG is not a
> valid Netezza table. Please make sure that you have connected to the
> Netezza DB and the table name is right. The current values are
>
>
>
> connection string : jdbc:netezza://
> netezza-server:5480/NZ_DB;schema=admin;
>
> table owner : shyam (windows network login ID)
>
> table name : NZ_TBL_GEOG
>
>
>
>
>
> Now, I removed the “schema” from the JDBC connection string and added
> “owner” name to the table name in the “—table” switch. The owner name of the
> table repeats itself 2 times in the error log below.
> “NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG”.
>
>
>
> Here is the SQOOP statement;
>
>
>
> /sqoop_hdp2/bin/sqoop export
>
> --direct
>
> --connect "jdbc:netezza://netezza-server:5480/NZ_DB"
>
> --username nzuser --password nzpass
>
> --export-dir /user/nzuser/NZ_DB/NZ_TBL_GEOG
>
> --table "ADMIN.NZ_TBL_GEOG"
>
> --num-mappers 8
>
> --input-fields-terminated-by ","
>
> --input-escaped-by '\\'
>
> --verbose
>
>
>
> Error:
>
>
>
> 14/07/25 01:09:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.1
>
> 14/07/25 01:09:07 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>
> 14/07/25 01:09:07 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Loaded manager factory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>
> com.cloudera.sqoop.manager.DefaultManagerFactory
>
> 14/07/25 01:09:07 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:netezza:
>
> 14/07/25 01:09:07 INFO manager.SqlManager: Using default fetchSize of
>
> 1000
>
> 14/07/25 01:09:07 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>
> org.apache.sqoop.manager.DirectNetezzaManager@4cc39a20
>
> 14/07/25 01:09:07 INFO tool.CodeGenTool: The connection manager declares
> that it self manages mapping between records & fields and rows & columns.
> No class will will be generated.
>
> 14/07/25 01:09:07 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 01:09:09 INFO mapreduce.ExportJobBase: Beginning export of
>
> ADMIN.NZ_TBL_GEOG
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Attempting to load jar
> through URL:
>
> jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Previous classloader is
>
> sun.misc.Launcher$AppClassLoader@546b97fd
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Loaded jar into current
>
> JVM: jar:file:/usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar!/
>
> 14/07/25 01:09:09 DEBUG util.ClassLoaderStack: Added classloader for
>
> jar /usr/lib/hadoop/hadoop-common-2.2.0.2.0.6.0-102.jar:
>
> java.net.FactoryURLClassLoader@6655bb93
>
> SLF4J: Class path contains multiple SLF4J bindings.
>
> SLF4J: Found binding in
>
> [jar:file:/usr/lib/hadoop/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/
>
> StaticLoggerBinder.class]
>
> SLF4J: Found binding in
>
> [jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/St
>
> aticLoggerBinder.class]
>
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
>
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
>
> 14/07/25 01:09:09 INFO Configuration.deprecation: mapred.jar is
>
> deprecated. Instead, use mapreduce.job.jar
>
> 14/07/25 01:09:09 DEBUG mapreduce.JobBase: Using InputFormat: class
>
> org.apache.sqoop.mapreduce.ExportInputFormat
>
> 14/07/25 01:09:10 DEBUG db.DBConfiguration: Securing password into job
>
> credentials store
>
> 14/07/25 01:09:10 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
>
> 14/07/25 01:09:11 ERROR manager.SqlManager: Error executing statement:
>
> org.netezza.error.NzSQLException: ERROR: relation does not exist:
> NZ_DB.ADMIN.ADMIN.NZ_TBL_GEOG
>
> 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.NzPreparedStatament._execute(NzPreparedStatament.java:1126)
>
> at
> org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1143)
>
> at
> org.netezza.sql.NzPreparedStatament.<init(NzPreparedStatament.java:89)
>
> at
> org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1588)
>
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:689)
>
> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:712)
>
> at
> org.apache.sqoop.manager.SqlManager.getColumnNamesForRawQuery(SqlManager.java:132)
>
> at
> org.apache.sqoop.manager.SqlManager.getColumnNames(SqlManager.java:116)
>
> at
> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.configureOutputFormat(NetezzaExternalTableExportJob.java:128)
>
> at
> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:398)
>
> at
> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezzaManager.java:179)
>
> at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>
> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>
> 14/07/25 01:09:11 DEBUG util.ClassLoaderStack: Restoring classloader:
>
> sun.misc.Launcher$AppClassLoader@546b97fd
>
> 14/07/25 01:09:11 ERROR sqoop.Sqoop: Got exception running Sqoop:
>
> java.lang.NullPointerException java.lang.NullPointerException
>
> at
> org.apache.sqoop.mapreduce.db.DBOutputFormat.setOutput(DBOutputFormat.java:139)
>
> at
> org.apache.sqoop.mapreduce.netezza.NetezzaExternalTableExportJob.configureOutputFormat(NetezzaExternalTableExportJob.java:134)
>
> at
> org.apache.sqoop.mapreduce.ExportJobBase.runExport(ExportJobBase.java:398)
>
> at
> org.apache.sqoop.manager.DirectNetezzaManager.exportTable(DirectNetezzaManager.java:179)
>
> at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:81)
>
> at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
>
> at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>
> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>
> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>
> at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
>
>
>
>
>
> Thanks,
>
>
>
>
>
> Shyam Munjuluri
> Integrated Systems Engineering
> Office: (M, W, F) 860-273-0595
> WAH: (Tu, Th) 860-404-5219
>
>
>
> This e-mail may contain confidential or privileged information. If you think
> you have received this e-mail in error, please advise the sender by reply
> e-mail and then delete this e-mail immediately. Thank you. Aetna
--
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is confidential,
privileged and exempt from disclosure under applicable law. If the reader
of this message is not the intended recipient, you are hereby notified that
any printing, copying, dissemination, distribution, disclosure or
forwarding of this communication is strictly prohibited. If you have
received this communication in error, please contact the sender immediately
and delete it from your system. Thank You.