sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Luca Piccolo <luca.picco...@outlook.com>
Subject Minimum Oracle grants for sqoop direct
Date Mon, 11 Apr 2016 08:52:09 GMT
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 		 	   		   		 	   		  
Mime
View raw message