sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ramya Vasudevan <rvasude...@tableau.com>
Subject RE: SQOOP export to Oracle failing
Date Mon, 30 Nov 2015 22:49:02 GMT
Hi

The more I tried various options to get the sqoop export to work, the more I felt that this
could be a bug. I started digging into the Sqoop internal code to debug and found this logic
used in the code:

method getTableColumns:
It tries to gather column names and column (Oracle & JDBC) data types by
Step 1) First retrieving column_name and data_type FROM dba_tab_columns table ( )
And
Step 2) Then by using the column_name returned from Step 1 to retrieve the JDBC data types
( )

The details below are from me navigating through the code .
- Step 1) succeeds and Oracle returns column names as a, x, y -> (all lower case as expected)
- Step 2) Sqoop tries to make the following SQL to retrieve JDBC column metadata "select a,x,y
from "TestV500"."Xy" where 0=1;"
It fails here with the error "ORA-00904: "Y": invalid identifier".
This is because Sqoop is not enclosing the column names in quotes in the select statement
as it should for cases where there are lower case column names.


The code that is problematic is in the class org.apache.sqoop.manager.oracle.OraOopOracleQueries
at Line Number 683.

This is incorrect:
columnList.append(result.get(idx).getName());


It should be something like this:
if(!result.get(idx).getName().equals(result.get(idx).getName().toUpperCase()))
      columnList.append("\"" + result.get(idx).getName() + "\"");
else
      columnList.append(result.get(idx).getName());

Thank you
RV

From: Ramya Vasudevan
Sent: Wednesday, November 18, 2015 2:56 PM
To: 'user@sqoop.apache.org'
Subject: RE: SQOOP export to Oracle failing

Hi

I am still stuck in this issue. Any suggestion will be very helpful.

Thank you
RV

From: Ramya Vasudevan
Sent: Monday, November 16, 2015 3:41 PM
To: user@sqoop.apache.org<mailto:user@sqoop.apache.org>
Subject: RE: SQOOP export to Oracle failing

Thank You David.

I can see a little more information this time.

Export with direct option:
/usr/bin/sqoop export --connect jdbc:oracle:thin:@adhoc-oracle11:1521:orcl --direct --num-mappers
2 --username test --password password --export-dir "/hdfs_nfs_mount/tmp/oracle/TestV100/Xy.txt"
--table "\"\"TestV500\".\"Xy\"\"" --fields-terminated-by "\t" --input-null-string null -m
1 -verbose

Output:
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
15/11/16 15:27:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.1
15/11/16 15:27:31 DEBUG tool.BaseSqoopTool: Enabled debug logging.
15/11/16 15:27:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure.
Consider using -P instead.
15/11/16 15:27:31 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
15/11/16 15:27:31 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
15/11/16 15:27:31 DEBUG sqoop.ConnFactory: Trying ManagerFactory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
15/11/16 15:27:31 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop
can be called by Sqoop!
15/11/16 15:27:31 INFO manager.SqlManager: Using default fetchSize of 1000
15/11/16 15:27:32 INFO oracle.OraOopOracleQueries: Current schema is: TEST
15/11/16 15:27:32 DEBUG oracle.OraOopUtilities: The Oracle table context has been derived
from:
        oracleConnectionUserName = test
        tableStr = "TestV500"."Xy"
        as:
        owner : TestV500
        table : Xy
15/11/16 15:27:32 INFO oracle.OraOopManagerFactory:
**************************************************
*** Using Data Connector for Oracle and Hadoop ***
**************************************************
15/11/16 15:27:32 DEBUG oracle.OraOopUtilities: Updated mapred.child.java.opts from "null"
to "-Djava.security.egd=file:///dev/urandom"
15/11/16 15:27:32 INFO oracle.OraOopManagerFactory: Oracle Database version: Oracle Database
11g Release 11.1.0.6.0 - Production
15/11/16 15:27:32 INFO oracle.OraOopManagerFactory: This Oracle database is not a RAC.
15/11/16 15:27:32 DEBUG oracle.OraOopManagerFactory: Setting mapper url oraoop.mapper.jdbc.url.0
= jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=adhoc-oracle11)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))
15/11/16 15:27:32 DEBUG oracle.OraOopManagerFactory: Setting mapper url oraoop.mapper.jdbc.url.1
= jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=adhoc-oracle11)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))
15/11/16 15:27:32 INFO Configuration.deprecation: mapred.map.max.attempts is deprecated. Instead,
use mapreduce.map.maxattempts
15/11/16 15:27:32 DEBUG sqoop.ConnFactory: Instantiated ConnManager org.apache.sqoop.manager.oracle.OraOopConnManager@7db63b8f<mailto:org.apache.sqoop.manager.oracle.OraOopConnManager@7db63b8f>
15/11/16 15:27:32 INFO tool.CodeGenTool: Beginning code generation
15/11/16 15:27:32 DEBUG oracle.OraOopOracleQueries: getTableColumns() : sql =
SELECT column_name, data_type  FROM dba_tab_columns WHERE owner = ? and table_name = ?  and
(DATA_TYPE IN ('BINARY_DOUBLE','BINARY_FLOAT','CHAR','DATE','FLOAT','NCHAR','NUMBER','NVARCHAR2','ROWID','URITYPE','VARCHAR2')
OR DATA_TYPE LIKE 'INTERVAL YEAR(%) TO MONTH' OR DATA_TYPE LIKE 'INTERVAL DAY(%) TO SECOND(%)'
OR DATA_TYPE LIKE 'TIMESTAMP(%)' OR DATA_TYPE LIKE 'TIMESTAMP(%) WITH TIME ZONE' OR DATA_TYPE
LIKE 'TIMESTAMP(%) WITH LOCAL TIME ZONE') ORDER BY column_id
15/11/16 15:27:32 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException:
java.sql.SQLSyntaxErrorException: ORA-00904: "Y": invalid identifier

java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00904: "Y": invalid identifier

        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:226)
        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.ExportTool.exportTable(ExportTool.java:64)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
        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-00904: "Y": invalid identifier

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:774)
        at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:849)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)
        at org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:689)
        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


It looks like it is resolving the column "y" as Y  and failing saying column Y doesn't exist:
This is the table DDL:
CREATE TABLE "TestV500"."Xy"
   (   "a" NVARCHAR2(255) DEFAULT NULL,
       "x" NUMBER(10,0) DEFAULT NULL,
       "y" NUMBER(10,0) DEFAULT NULL
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

Any idea on how I can get past this error?

Thank You
RV

From: David Robson [mailto:David.Robson@software.dell.com]
Sent: Monday, November 16, 2015 2:49 PM
To: user@sqoop.apache.org<mailto:user@sqoop.apache.org>
Subject: RE: SQOOP export to Oracle failing

Hi Ramya,

Have you tried the "-direct" option? I have tested mixed case table and schema names with
this before and it should work. We even added a section to the documentation on quoting them
which you can read here:

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

The only thing you will need to do is use more mappers - the direct connector only works with
2 or more mappers. So add "-direct" to your command and increase the number of mappers to
at least 2.

David

From: Ramya Vasudevan [mailto:rvasudevan@tableau.com]
Sent: Tuesday, 17 November 2015 5:20 AM
To: user@sqoop.apache.org<mailto:user@sqoop.apache.org>
Subject: SQOOP export to Oracle failing


Hi
I am facing an issue with export to Oracle:

Scenario:
sqoop export from hdfs to oracle where the schema and objects in oracle has to be mixed case.

Issue:
I created a schema (TestV500) and table (Xy) in oracle 11g using sqoop export commands:
sqoop eval --connect jdbc:oracle:thin:@adhoc-oracle11:1521:orcl -username test -password password
-query "CREATE USER \"TestV500\" identified by \"password\""
sqoop eval --connect jdbc:oracle:thin:@adhoc-oracle11:1521:orcl -username test -password password
-query "GRANT CONNECT TO \"TestV500\""
sqoop eval --connect jdbc:oracle:thin:@adhoc-oracle11:1521:orcl -username test -password password
-query "ALTER USER \"TestV500\" QUOTA UNLIMITED ON USERS"
sqoop eval --connect jdbc:oracle:thin:@adhoc-oracle11:1521:orcl -username test -password password
-query "CREATE TABLE \"TestV500\".\"Xy\"( \"a\" NVARCHAR2(255) DEFAULT NULL, \"x\" NUMBER(10,0)
DEFAULT NULL, \"y\" NUMBER(10,0) DEFAULT NULL )"


Installations used:
Oracle Version: 11.1.0.6.0
Sqoop Version: 1.4.5-cdh5.4.1
Ojdbc version:  ojdbc6.jar

I am now trying to load the table using a tab delimited file:
/usr/bin/sudo -u hdfs sqoop export  --connect jdbc:oracle:thin:@oracle11:1521:orcl -username
test -password password "/hdfs_nfs_mount/tmp/oracle/TestV500/Xy.txt" --table "\"\"TestV500\".\"Xy\"\""
--fields-terminated-by "\t" --input-null-string null -m 1

I get this error :
15/11/12 09:33:03 INFO tool.CodeGenTool: Beginning code generation
15/11/12 09:33:03 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM "TestV500"."Xy"
t WHERE 1=0
15/11/12 09:33:03 DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM
"TestV500"."Xy" t WHERE 1=0
15/11/12 09:33:03 DEBUG manager.OracleManager: Creating a new connection for jdbc:oracle:thin:@adhoc-oracle11:1521:orcl,
using username: test
15/11/12 09:33:03 DEBUG manager.OracleManager: No connection paramenters specified. Using
regular API for making connection.
15/11/12 09:33:03 INFO manager.OracleManager: Time zone has been set to GMT
15/11/12 09:33:03 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
15/11/12 09:33:03 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "TestV500"."Xy"
t WHERE 1=0
15/11/12 09:33:04 DEBUG manager.SqlManager: Found column a of type [12, 255, 0]
15/11/12 09:33:04 DEBUG manager.SqlManager: Found column x of type [2, 10, 0]
15/11/12 09:33:04 DEBUG manager.SqlManager: Found column y of type [2, 10, 0]
15/11/12 09:33:04 DEBUG manager.OracleManager$ConnCache: Caching released connection for jdbc:oracle:thin:@adhoc-oracle11:1521:orcl/test
15/11/12 09:33:04 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.IllegalArgumentException:
There is no column found in the target table "TestV500"."Xy". Please ensure that your table
name is correct.
java.lang.IllegalArgumentException: There is no column found in the target table "TestV500"."Xy".
Please ensure that your table name is correct.
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1658)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:64)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:100)
        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)


This is the input file Xy.txt:
a       0       0
a       1       4
a       2       2


Observations:
1.
Schema and table was created successfully in oracle.
2.
If I connect to the database directly as 'test' user and run this command, it works fine (it
shows zero rows as nothing is loaded yet)
SELECT t.* FROM "TestV500"."Xy" t WHERE 1=0;
3.
If I create a schema called testv500 with a table called xy (without mixed case), the exact
same Sqoop export command, the input file loads the data in the table.

Unless I am missing something here, I wonder if  I hit a bug not being able to handle mixed
case.

Any help will be great!

Thank You,
RV



Mime
View raw message