You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by Luca Piccolo <lu...@outlook.com> on 2016/04/11 10:52:09 UTC
Minimum Oracle grants for sqoop direct
I'm using Sqoop to import tables from an Oracle database, but it's very slow, so I'm trying to use direct mode in order to speed up the import. Although standard sqoop with 1 mapper works fine, sqoop in direct mode fails (I report the complete exception below). I found the following link (http://grokbase.com/t/cloudera/cdh-user/124csac138/table-doesnt-exist-when-use-oraoop-with-sqoop) that suggests to grant the SELECT_CATALOG_ROLE role to the user, but I was denied the request by the DBA because it's too broad (too many permissions). What are the minimum Oracle grants in order to use direct mode?
Below the stacktrace:[<user>@XXXX ~]$ sqoop import --connect jdbc:oracle:thin:@<ip_address>:<port>/<database> --username <username> --password XXXX --table <table> --target-dir /XXXX --as-avrodatafile --compression-codec org.apache.hadoop.io.compress.SnappyCodec --directWarning: /opt/cloudera/parcels/CDH-5.6.0-1.cdh5.6.0.p0.45/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.Please set $ACCUMULO_HOME to the root of your Accumulo installation.16/04/11 10:17:07 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.6.016/04/11 10:17:07 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.16/04/11 10:17:08 INFO manager.SqlManager: Using default fetchSize of 100016/04/11 10:17:10 WARN oracle.OraOopManagerFactory: Unable to determine the Oracle-type of the object named <table> owned by <username>.Error:ORA-00942: table or view does not exist
16/04/11 10:17:10 WARN oracle.OraOopManagerFactory: Unable to determine whether the Oracle table "<username>"."<table>" is an index-organized table.Error:ORA-00942: table or view does not exist
16/04/11 10:17:10 INFO oracle.OraOopManagerFactory:***************************************************** Using Data Connector for Oracle and Hadoop *****************************************************16/04/11 10:17:10 INFO oracle.OraOopManagerFactory: Oracle Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production16/04/11 10:17:10 INFO oracle.OraOopManagerFactory: This Oracle database is a RAC.16/04/11 10:17:10 ERROR oracle.OracleConnectionFactory: Unable to obtain a JDBC connection to the URL "jdbc:oracle:thin:@<host>:<port>:ICMP1" as user "<username>":java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:187) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createConnection(OracleConnectionFactory.java:96) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createOracleJdbcConnection(OracleConnectionFactory.java:53) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.testDynamicallyGeneratedOracleRacInstanceConnection(OraOopManagerFactory.java:526) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.generateJdbcConnectionUrlsByActiveInstance(OraOopManagerFactory.java:466) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.setMapperConnectionDetails(OraOopManagerFactory.java:384) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.accept(OraOopManagerFactory.java:260) at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:184) at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:258) at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:89) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:593) 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: oracle.net.ns.NetException: The Network Adapter could not establish the connection at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:439) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:454) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ... 22 moreCaused by: java.net.UnknownHostException: <host> at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:901) at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1293) at java.net.InetAddress.getAllByName0(InetAddress.java:1246) at java.net.InetAddress.getAllByName(InetAddress.java:1162) at java.net.InetAddress.getAllByName(InetAddress.java:1098) at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117) at oracle.net.nt.ConnOption.connect(ConnOption.java:133) at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:405) ... 27 more16/04/11 10:17:10 WARN oracle.OraOopManagerFactory: The dynamically generated JDBC URL "jdbc:oracle:thin:@<host>:<port>:ICMP1" was unable to connect to an instance in the Oracle RAC.java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:187) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createConnection(OracleConnectionFactory.java:96) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createOracleJdbcConnection(OracleConnectionFactory.java:53) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.testDynamicallyGeneratedOracleRacInstanceConnection(OraOopManagerFactory.java:526) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.generateJdbcConnectionUrlsByActiveInstance(OraOopManagerFactory.java:466) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.setMapperConnectionDetails(OraOopManagerFactory.java:384) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.accept(OraOopManagerFactory.java:260) at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:184) at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:258) at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:89) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:593) 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: oracle.net.ns.NetException: The Network Adapter could not establish the connection at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:439) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:454) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ... 22 moreCaused by: java.net.UnknownHostException: <host> at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:901) at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1293) at java.net.InetAddress.getAllByName0(InetAddress.java:1246) at java.net.InetAddress.getAllByName(InetAddress.java:1162) at java.net.InetAddress.getAllByName(InetAddress.java:1098) at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117) at oracle.net.nt.ConnOption.connect(ConnOption.java:133) at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:405) ... 27 more16/04/11 10:17:10 ERROR oracle.OracleConnectionFactory: Unable to obtain a JDBC connection to the URL "jdbc:oracle:thin:@<host2>:<port>:ICMP2" as user "<username>":java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:187) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createConnection(OracleConnectionFactory.java:96) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createOracleJdbcConnection(OracleConnectionFactory.java:53) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.testDynamicallyGeneratedOracleRacInstanceConnection(OraOopManagerFactory.java:526) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.generateJdbcConnectionUrlsByActiveInstance(OraOopManagerFactory.java:466) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.setMapperConnectionDetails(OraOopManagerFactory.java:384) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.accept(OraOopManagerFactory.java:260) at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:184) at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:258) at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:89) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:593) 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: oracle.net.ns.NetException: The Network Adapter could not establish the connection at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:439) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:454) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ... 22 moreCaused by: java.net.UnknownHostException: <host2> at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:901) at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1293) at java.net.InetAddress.getAllByName0(InetAddress.java:1246) at java.net.InetAddress.getAllByName(InetAddress.java:1162) at java.net.InetAddress.getAllByName(InetAddress.java:1098) at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117) at oracle.net.nt.ConnOption.connect(ConnOption.java:133) at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:405) ... 27 more16/04/11 10:17:10 WARN oracle.OraOopManagerFactory: The dynamically generated JDBC URL "jdbc:oracle:thin:@<host2>:<port>:ICMP2" was unable to connect to an instance in the Oracle RAC.java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:187) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createConnection(OracleConnectionFactory.java:96) at org.apache.sqoop.manager.oracle.OracleConnectionFactory.createOracleJdbcConnection(OracleConnectionFactory.java:53) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.testDynamicallyGeneratedOracleRacInstanceConnection(OraOopManagerFactory.java:526) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.generateJdbcConnectionUrlsByActiveInstance(OraOopManagerFactory.java:466) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.setMapperConnectionDetails(OraOopManagerFactory.java:384) at org.apache.sqoop.manager.oracle.OraOopManagerFactory.accept(OraOopManagerFactory.java:260) at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:184) at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:258) at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:89) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:593) 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: oracle.net.ns.NetException: The Network Adapter could not establish the connection at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:439) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:454) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:693) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:251) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ... 22 moreCaused by: java.net.UnknownHostException: <host2> at java.net.Inet4AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:901) at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1293) at java.net.InetAddress.getAllByName0(InetAddress.java:1246) at java.net.InetAddress.getAllByName(InetAddress.java:1162) at java.net.InetAddress.getAllByName(InetAddress.java:1098) at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117) at oracle.net.nt.ConnOption.connect(ConnOption.java:133) at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:405) ... 27 more16/04/11 10:17:10 INFO oracle.OraOopManagerFactory: Data Connector for Oracle and Hadoop will not attempt to load-balance sessions across instances of an Oracle RAC - as multiple JDBC URLs to the Oracle RAC could not be dynamically generated.16/04/11 10:17:10 INFO Configuration.deprecation: mapred.map.max.attempts is deprecated. Instead, use mapreduce.map.maxattempts16/04/11 10:17:10 INFO tool.CodeGenTool: Beginning code generation16/04/11 10:17:10 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:226) at org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnTypes(OraOopConnManager.java:504) at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:327) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1834) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1646) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) 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