You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@sqoop.apache.org by Ted Dong <td...@splicemachine.com> on 2015/11/16 22:14:24 UTC

Oracle Import Error Using --direct Option

Hi,

I was able to use the --direct option to import data from an Oracle
database to HDFS. The Oracle database was a development instance and the
credentials I was using had full read/write privileges.

I then switched to another Oracle database which is a QA instance. The
credentials I was given contains only read-only access.

When I run Sqoop with the --direct option I get this error:

15/11/16 13:06:09 WARN oracle.OraOopManagerFactory: Unable to determine
whether the Oracle table "IAPPS"."MF_PROFILE" is an index-organized table.
Error:
ORA-00942: table or view does not exist
15/11/16 13:06:09 ERROR sqoop.Sqoop: Got exception running Sqoop:
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942:
table or view does not exist

java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942:
table or view does not exist

        at
org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:133)
        at
org.apache.sqoop.manager.oracle.OraOopConnManager.getSelectedColumnNamesInOracleTable(OraOopConnManager.java:144)
        at
org.apache.sqoop.manager.oracle.OraOopConnManager.getColTypesQuery(OraOopConnManager.java:216)
        at
org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:225)
        at
org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnTypes(OraOopConnManager.java:504)
        at
org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
        at
org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
        at
org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
        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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does
not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
        at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
        at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
        at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
        at
org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:658)
        at
org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:767)
        at
org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumnNames(OraOopOracleQueries.java:624)
        at
org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:124)
        ... 16 more

I confirmed that the credentials I am using to access the QA database can
see and read from the table I am trying to import to HDFS (confirmed using
toad).

Are there some other user account settings that is required on the Oracle
side to get the --direct option working from Sqoop?

Thanks!

Re: Oracle Import Error Using --direct Option

Posted by Ted Dong <td...@splicemachine.com>.

Thanks David!

On Mon, Nov 16, 2015 at 1:31 PM, David Robson <
David.Robson@software.dell.com> wrote:

> Hi Ted,
>
>
>
> The direct connector requires select access on some data dictionary views
> which is probably why you are having issues. See if the DBA can grant you
> the privileges outlined in this section of the user guide:
>
>
>
>
> http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_oracle_roles_and_privileges
>
>
>
> David
>
>
>
> *From:* Ted Dong [mailto:tdong@splicemachine.com]
> *Sent:* Tuesday, 17 November 2015 8:14 AM
> *To:* user@sqoop.apache.org
> *Subject:* Oracle Import Error Using --direct Option
>
>
>
> Hi,
>
>
>
> I was able to use the --direct option to import data from an Oracle
> database to HDFS. The Oracle database was a development instance and the
> credentials I was using had full read/write privileges.
>
>
>
> I then switched to another Oracle database which is a QA instance. The
> credentials I was given contains only read-only access.
>
>
>
> When I run Sqoop with the --direct option I get this error:
>
>
>
> 15/11/16 13:06:09 WARN oracle.OraOopManagerFactory: Unable to determine
> whether the Oracle table "IAPPS"."MF_PROFILE" is an index-organized table.
>
> Error:
>
> ORA-00942: table or view does not exist
>
> 15/11/16 13:06:09 ERROR sqoop.Sqoop: Got exception running Sqoop:
> java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942:
> table or view does not exist
>
>
>
> java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942:
> table or view does not exist
>
>
>
>         at
> org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:133)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopConnManager.getSelectedColumnNamesInOracleTable(OraOopConnManager.java:144)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopConnManager.getColTypesQuery(OraOopConnManager.java:216)
>
>         at
> org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:225)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnTypes(OraOopConnManager.java:504)
>
>         at
> org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
>
>         at
> org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
>
>         at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
>
>         at
> org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
>
>         at
> org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
>
>         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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does
> not exist
>
>
>
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
>
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
>
>         at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
>
>         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
>
>         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
>
>         at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
>
>         at
> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
>
>         at
> oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
>
>         at
> oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
>
>         at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
>
>         at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
>
>         at
> oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
>
>         at
> oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:658)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:767)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumnNames(OraOopOracleQueries.java:624)
>
>         at
> org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:124)
>
>         ... 16 more
>
>
>
> I confirmed that the credentials I am using to access the QA database can
> see and read from the table I am trying to import to HDFS (confirmed using
> toad).
>
>
>
> Are there some other user account settings that is required on the Oracle
> side to get the --direct option working from Sqoop?
>
>
>
> Thanks!
>
>
>



-- 
Ted Dong, Technical Architect • Splice Machine
323-533-5909 (c) • www.splicemachine.com

RE: Oracle Import Error Using --direct Option

Posted by David Robson <Da...@software.dell.com>.

Hi Ted,

The direct connector requires select access on some data dictionary views which is probably why you are having issues. See if the DBA can grant you the privileges outlined in this section of the user guide:

http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_oracle_roles_and_privileges

David

From: Ted Dong [mailto:tdong@splicemachine.com]
Sent: Tuesday, 17 November 2015 8:14 AM
To: user@sqoop.apache.org
Subject: Oracle Import Error Using --direct Option

Hi,

I was able to use the --direct option to import data from an Oracle database to HDFS. The Oracle database was a development instance and the credentials I was using had full read/write privileges.

I then switched to another Oracle database which is a QA instance. The credentials I was given contains only read-only access.

When I run Sqoop with the --direct option I get this error:

15/11/16 13:06:09 WARN oracle.OraOopManagerFactory: Unable to determine whether the Oracle table "IAPPS"."MF_PROFILE" is an index-organized table.
Error:
ORA-00942: table or view does not exist
15/11/16 13:06:09 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:133)
        at org.apache.sqoop.manager.oracle.OraOopConnManager.getSelectedColumnNamesInOracleTable(OraOopConnManager.java:144)
        at org.apache.sqoop.manager.oracle.OraOopConnManager.getColTypesQuery(OraOopConnManager.java:216)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:225)
        at org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnTypes(OraOopConnManager.java:504)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
        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: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
        at org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:658)
        at org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:767)
        at org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumnNames(OraOopOracleQueries.java:624)
        at org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:124)
        ... 16 more

I confirmed that the credentials I am using to access the QA database can see and read from the table I am trying to import to HDFS (confirmed using toad).

Are there some other user account settings that is required on the Oracle side to get the --direct option working from Sqoop?

Thanks!