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.

How do you use oraoop direct mode?

Highlighted

How do you use oraoop direct mode?

New Contributor

Hi,

The version information is as follows.

HDP: 2.6

Sqoop: 1.4.6

Oracle: 12c

oraoop can not be found in the HDP package and once installed and found cloudera oraoop.

The first question is whether hortonworks does not have support for oraoop.

The second question is that when I run the following command, I get the following error.

Command>

[sqoop @ mdw ~] $ sqoop import --direct --connect jdbc: oracle: thin: @ etl1: 1521 / ORA12C --username sqoop -P --table sqoop --split-by personName --target -dir / sqoop / tmp1 -m 3 --columns personName, company --connection-param-file = / home / sqoop / propfile.cfg 

Error>

17/10/29 10:42:26 INFO mapreduce.Job: Task Id : attempt_1509241018194_0001_m_000000_0, Status : FAILED
Error: java.io.IOException: java.sql.SQLException: Could not commit with auto-commit set on
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:173)
at com.quest.oraoop.OraOopDBRecordReader.close(OraOopDBRecordReader.java:413)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.close(MapTask.java:523)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:791)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
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:1866)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.sql.SQLException: Could not commit with auto-commit set on
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:169)
... 9 more

Attach a complete log.

[sqoop@mdw ~]$ sqoop import --direct --connect jdbc:oracle:thin:@etl1:1521/ORA12C --username sqoop -P --table sqoop --split-by personName --target-dir /sqoop/tmp1 -m 3 --columns personName,company --connection-param-file=/home/sqoop/propfile.cfg
Warning: /usr/hdp/2.6.2.0-205/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/hdp/2.6.2.0-205/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/10/29 10:41:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.2.0-205
Enter password:
17/10/29 10:41:48 INFO manager.SqlManager: Using default fetchSize of 1000
17/10/29 10:41:59 INFO oraoop.OraOopManagerFactory:
***********************************************************************
*** Using Quest® Data Connector for Oracle and Hadoop 1.6.0-cdh4-20 ***
*** Copyright 2012 Quest Software, Inc.                             ***
*** ALL RIGHTS RESERVED.                                            ***
***********************************************************************
17/10/29 10:41:59 INFO oraoop.OraOopManagerFactory: Oracle Database version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
17/10/29 10:41:59 INFO oraoop.OraOopManagerFactory: This Oracle database is not a RAC.
17/10/29 10:41:59 INFO tool.CodeGenTool: Beginning code generation
17/10/29 10:41:59 INFO manager.SqlManager: Executing SQL statement: SELECT "PERSONNAME","COMPANY" FROM sqoop WHERE 0=1
17/10/29 10:41:59 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hdp/2.6.2.0-205/hadoop-mapreduce
Note: /tmp/sqoop-sqoop/compile/4c29f43ed234adbe50257d811a536689/sqoop.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/10/29 10:42:01 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-sqoop/compile/4c29f43ed234adbe50257d811a536689/sqoop.jar
17/10/29 10:42:01 INFO mapreduce.ImportJobBase: Beginning import of sqoop
17/10/29 10:42:02 INFO client.RMProxy: Connecting to ResourceManager at mdw 17/10/29 10:42:02 INFO client.AHSProxy: Connecting to Application History server at mdw 17/10/29 10:42:04 WARN oraoop.OraOopUtilities: System property java.security.egd is not set to file:///dev/urandom - Oracle connections may time out.
17/10/29 10:42:04 INFO db.DBInputFormat: Using read commited transaction isolation
17/10/29 10:42:04 INFO oraoop.OracleConnectionFactory: Session Time Zone set to GMT
17/10/29 10:42:04 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL :
begin
  dbms_application_info.set_module(module_name => 'Quest® Data Connector for Oracle and Hadoop', action_name => 'import 20171029104159KST');
end;
17/10/29 10:42:04 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session disable parallel query
17/10/29 10:42:04 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set "_serial_direct_read"=true
17/10/29 10:42:04 INFO oraoop.OracleConnectionFactory: Initializing Oracle session with SQL : alter session set tracefile_identifier=oraoop
17/10/29 10:42:05 INFO oraoop.OraOopDataDrivenDBInputFormat: The table being imported by sqoop has 8 blocks that have been divided into 1 chunks which will be processed in 1 splits. The chunks will be allocated to the splits using the method : ROUNDROBIN
17/10/29 10:42:05 INFO mapreduce.JobSubmitter: number of splits:1
17/10/29 10:42:05 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1509241018194_0001
17/10/29 10:42:06 INFO impl.YarnClientImpl: Submitted application application_1509241018194_0001
17/10/29 10:42:06 INFO mapreduce.Job: The url to track the job: http://mdw:8088/proxy/application_1509241018194_0001/
17/10/29 10:42:06 INFO mapreduce.Job: Running job: job_1509241018194_0001
17/10/29 10:42:17 INFO mapreduce.Job: Job job_1509241018194_0001 running in uber mode : false
17/10/29 10:42:17 INFO mapreduce.Job:  map 0% reduce 0%
17/10/29 10:42:26 INFO mapreduce.Job: Task Id : attempt_1509241018194_0001_m_000000_0, Status : FAILED
Error: java.io.IOException: java.sql.SQLException: Could not commit with auto-commit set on
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:173)
at com.quest.oraoop.OraOopDBRecordReader.close(OraOopDBRecordReader.java:413)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.close(MapTask.java:523)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:791)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
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:1866)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.sql.SQLException: Could not commit with auto-commit set on
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:169)
... 9 more
17/10/29 10:42:51 INFO mapreduce.Job: Task Id : attempt_1509241018194_0001_m_000000_1, Status : FAILED
Error: java.io.IOException: java.sql.SQLException: Could not commit with auto-commit set on
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:173)
at com.quest.oraoop.OraOopDBRecordReader.close(OraOopDBRecordReader.java:413)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.close(MapTask.java:523)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:791)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
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:1866)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.sql.SQLException: Could not commit with auto-commit set on
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:169)
... 9 more
17/10/29 10:42:56 INFO mapreduce.Job: Task Id : attempt_1509241018194_0001_m_000000_2, Status : FAILED
Error: java.io.IOException: java.sql.SQLException: Could not commit with auto-commit set on
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:173)
at com.quest.oraoop.OraOopDBRecordReader.close(OraOopDBRecordReader.java:413)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.close(MapTask.java:523)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:791)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
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:1866)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.sql.SQLException: Could not commit with auto-commit set on
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
at org.apache.sqoop.mapreduce.db.DBRecordReader.close(DBRecordReader.java:169)
... 9 more
17/10/29 10:43:08 INFO mapreduce.Job:  map 100% reduce 0%
17/10/29 10:43:08 INFO mapreduce.Job: Job job_1509241018194_0001 failed with state FAILED due to: Task failed task_1509241018194_0001_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0
17/10/29 10:43:09 INFO mapreduce.Job: Counters: 8
Job Counters
Failed map tasks=4
Launched map tasks=4
Other local map tasks=4
Total time spent by all maps in occupied slots (ms)=260040
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=43340
Total vcore-milliseconds taken by all map tasks=43340
Total megabyte-milliseconds taken by all map tasks=266280960
17/10/29 10:43:09 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
17/10/29 10:43:09 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 66.9957 seconds (0 bytes/sec)
17/10/29 10:43:09 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead
17/10/29 10:43:09 INFO mapreduce.ImportJobBase: Retrieved 0 records.
17/10/29 10:43:09 ERROR tool.ImportTool: Error during import: Import job failed!
2 REPLIES 2

Re: How do you use oraoop direct mode?

Hi @Sangsun Ko,

In the query, can you please try changing --table sqoop to --query "select * from sqoop"

Thanks,

Aditya

Re: How do you use oraoop direct mode?

New Contributor

Hi, How to enable oraoop option in sqoop import and where can i download oraoop jar ?

I ran sample sqoop import for a table and below is sqoop log :


19/04/20 00:23:00 DEBUG oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop can be called by Sqoop!

19/04/20 00:23:00 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop is disabled.


Our env is - HDP 2.6.4, Sqoop 1.4.6, Oracle 12c


Can someone able answer ?


Thanks,

Shashi