Sqoop import Error with Oracle

sqoop import   --connect 'jdbc:oracle:thin:@ip_add/dbname' --username 'xxx' --password 'xxxx' --table dbname.table_name  --target-dir /xx/xx/Talend/test/test2    -m 1


 Error:

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
17/11/02 14:38:25 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Invalid Oracle URL specified
java.sql.SQLException: Invalid Oracle URL specified
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
     

Solution : In case we have to add "thin" with jdbc connection string
Example :- --connect 'jdbc:oracle:thin:@ipadd/dbname'

Again Error:

SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
17/11/02 14:40:55 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: The Network Adapter could not establish the connection
java.sql.SQLException: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:480)


Solution:-

 Instead of giving the URLand database name you have to replace whole connection string of oracle with TNSNAME entry of that database of oracle:

Example:

Oracle tnsname entry:

xxx =

  (DESCRIPTION =

    (LOAD_BALANCE = yes)

    (SDU = 32768)

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = ip_address)

      (PORT = 1526)

    )

    (CONNECT_DATA =

      (SERVICE_= nnnnnDSI)

    )

  )

Now in sqoop cmd you have to make the change accordingly.

sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = ip_addr)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = HPIPODSI)))' --username 'xxx' --password 'xxxx' --table dbname.table  --target-dir /apps/hive/warehouse/test001.db --escaped-by '\"' --enclosed-by '\"'  --fields-terminated-by '|'  -m 1

Again you are getting error.

LF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

17/11/02 14:45:33 INFO manager.OracleManager: Time zone has been set to GMT

17/11/02 14:45:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "AMSPODS_PUB"."end_cust_id" t WHERE 1=0

17/11/02 14:45:33 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

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

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)

Solution:

Use uppercase for Oracle Username,Database name and Table name 

Example :

sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = ip_address)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = xx)))' --username 'xx' --password '#' --table dbname.END_CUST_ID  --target-dir /apps/hive/warehouse/test001.db --escaped-by '\"' --enclosed-by '\"'  --fields-terminated-by '|'  -m 1

Now data has successfully imported.