question Re: Hive metatool cannot connect to oracle database using long jdbc uri in Support Questions

Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 18:19:13 GMT

Hello community!

I have to test the command hive --service metatool in order to change the sederproperties of all tables due a namenode HA enablement but I'm having an error with username/password when they are fine in the config files.

Environment:

HDP: 2.4.0

Hive: 1.2.1.2.4

jdbc driver: ojdbc7

DB: oracle exadata 11g

jdbc uri: jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=ON)(LOAD_BALANCE=OFF)(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-3.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SD0MISC)))(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEV01))))

I'm having the following error:

[admin@node01.hadoop01.int ~]$ hive --service metatool -listFSRoot
WARNING: Use "yarn jar" to launch YARN applications.
Initializing HiveMetaTool..
17/03/14 10:51:22 INFO metastore.ObjectStore: ObjectStore, initialize called
17/03/14 10:51:22 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
17/03/14 10:51:22 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored
17/03/14 10:51:27 ERROR Datastore.Schema: Failed initialising database.
Unable to open a test connection to the given database. JDBC url = jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=ON)(LOAD_BALANCE=OFF)(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-3.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SD0MISC)))(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-3.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEV01)))), username = devhive. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
java.sql.SQLException: ORA-01017: invalid username/password; logon denied


        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:675)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:448)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:383)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:776)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:432)
        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:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:208)
        at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:361)
        at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:416)
        at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:120)
        at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection(ConnectionFactoryImpl.java:501)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.<init>(RDBMSStoreManager.java:298)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:631)
        at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:301)
        at org.datanucleus.NucleusContext.createStoreManagerForProperties(NucleusContext.java:1187)
        at org.datanucleus.NucleusContext.initialise(NucleusContext.java:356)
        at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:775)
        at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:333)
        at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:202)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at javax.jdo.JDOHelper$16.run(JDOHelper.java:1965)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.jdo.JDOHelper.invoke(JDOHelper.java:1960)
        at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1166)
        at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:808)
        at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:701)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPMF(ObjectStore.java:374)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPersistenceManager(ObjectStore.java:403)
        at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:296)
        at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:263)
        at org.apache.hadoop.hive.metastore.tools.HiveMetaTool.initObjectStore(HiveMetaTool.java:119)
        at org.apache.hadoop.hive.metastore.tools.HiveMetaTool.listFSRoot(HiveMetaTool.java:133)
        at org.apache.hadoop.hive.metastore.tools.HiveMetaTool.main(HiveMetaTool.java:398)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
------

I also tried with "export HIVE_CONF_DIR=/etc/hive/2.4.0.0-169/0" before input the command but the problem persist, as you can see it reads the configuration and is able to get user so I think it's reading the config file.

Any clue about what is happening?

Thank you in advance

Re: Hive metatool cannot connect to oracle database using long jdbc uri

jsensharma — Tue, 14 Mar 2017 18:30:02 GMT

@Juan Manuel Nieto

As you are getting the error from Oracle side. Which means your JDBC driver is passing your connection string & credentials to the DB but the DB is rejecting due to incorrect username/password. with the error code ORA-01017

java.sql.SQLException: ORA-01017: invalid username/password; logon denied          
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

So it will be best to try using some Standalone Java Program (Or some native clients like sqlplus client) to validate if your DB username & Password is correct or not? Or if it is expired? The credentials are case sensitive.

So we should isolate the issue first whether oyu are entering valid credentials ? Following link provides some generic tips from Oracle side: http://www.dba-oracle.com/t_ora_01017.htm

You can try the following Java code to validate if you are able to connect to the oracle DB . You only need to change the following code with your DB connection URL , dbUsername & dbPassword in the following line

"jdbc:oracle:thin:@host:port:sid","scott","tiger"

vi /tmp/JDBCVersion.java

import java.sql.*;
import oracle.jdbc.driver.*;

public class JDBCVersion {
    public static void main (String args []) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:sid","scott","tiger");
    DatabaseMetaData meta = conn.getMetaData ();
    System.out.println("JDBC driver version is " + meta.getDriverVersion());
    }
}

How to compile and run.

cd /tmp
ls -l /tmp/JDBCVersion.java
export CLASSPATH=/PATH/TO/ojdbc7.jar:.:
javac JDBCVersion.java
java JDBCVersion

Re: Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 20:19:05 GMT

Hello @Jay SenSharma,

Yes I was able to connect via sqlplus using the password stored at hive-site.xml

The password is ok in the config file.

I did it before to check the SERDE_PARAMS table.

The problem is not the password stored in hive-site.xml

Re: Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 20:37:15 GMT

@Jay SenSharma I've tried to compile your code but it gives me an error

export CLASSPATH=./ojdbc7.jar:.:
/usr/jdk64/jdk1.8.0_60/bin/javac /root/JDBCVersion.java
/root/JDBCVersion.java:10: error: reached end of file while parsing

The code is the following:

import java.sql.*;import oracle.jdbc.driver.*; public class JDBCVersion {    public static void main (String args []) throws SQLException {    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=ON)(LOAD_BALANCE=OFF)(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-3.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SD0MISC)))(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEV01))))","devhive","<password>");    DatabaseMetaData meta = conn.getMetaData ();    System.out.println("JDBC driver version is " + meta.getDriverVersion());    }

Re: Hive metatool cannot connect to oracle database using long jdbc uri

jsensharma — Tue, 14 Mar 2017 21:35:52 GMT

@Juan Manuel Nieto

As this is basically a JDBC call that is failing hence it will be really good to test the same using the oracle jdbc driver that you are using.

Hence i have modified the code "JDBCVersion.java" and attached here.

import java.sql.*;
import oracle.jdbc.driver.*;
public class JDBCVersion {
   public static void main (String args []) throws SQLException {
       
        String connection_URL = "jdbc:oracle:thin:@(DESCRIPTION_LIST=(FAILOVER=ON)(LOAD_BALANCE=OFF)(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa02-3.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SD0MISC)))(DESCRIPTION=(RETRY_COUNT=3)(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-1.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=exa01-2.int)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEV01))))";
        String dbUsername = "ambari";
        String dbPassword = "bigdata";

        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection conn = DriverManager.getConnection(connection_URL, dbUsername, dbPassword);
        DatabaseMetaData meta = conn.getMetaData ();
        System.out.println("JDBC driver version is " + meta.getDriverVersion());
   }
}

The only change in the above code you will need to make is the dbUsername, dbPassword. I have already placed the same "connection_URL" as you mentioned above.

In order to test the same please do the following:

$ mkdir /tmp/TestOracle
$ cp -f ~/Downloads/ojdbc7.jar  /tmp/TestOracle
$ cp -f ~/Desktop/JDBCVersion.java  /tmp/TestOracle
$ cd /tmp/TestOracle

$ export CLASSPATH=/tmp/TestOracle/ojdbc7.jar:.:
$ javac JDBCVersion.java 
$ java JDBCVersion

jdbcversion.zip

Re: Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 22:25:57 GMT

@Jay SenSharma

Now it compiled perfectly

 TestOracle]# /usr/jdk64/jdk1.8.0_60/bin/java JDBCVersion
JDBC driver version is 12.1.0.2.0

Re: Hive metatool cannot connect to oracle database using long jdbc uri

jsensharma — Tue, 14 Mar 2017 22:33:48 GMT

@Juan Manuel Nieto

The result is good and it indicates that the username that you passed to the above mentioned java code is correct.

So looks like when you are running the command "hive --service metatool -listFSRoot" then it is taking the password from somewhere else. Or may be getting incorrect password.

Can you please check the "javax.jdo.option.connectionpassword" and "javax.jdo.option.ConnectionUserName" property in your hive-site.xml ?

Re: Hive metatool cannot connect to oracle database using long jdbc uri

jsensharma — Tue, 14 Mar 2017 22:49:06 GMT

@Juan Manuel Nieto

Also can we specify the "--config" to make sure that it is taking the right configuration files.

Example:

# hive --config /etc/hive/conf.server --service metatool -listFSRoot

Re: Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 22:56:50 GMT

@Jay SenSharma

I have checked both parameters and are exactly the same that I used in the .java file 😞

Re: Hive metatool cannot connect to oracle database using long jdbc uri

deepesh1 — Tue, 14 Mar 2017 23:10:36 GMT

I am guessing the issue here is that you are not able to read the server config. To run this, ssh into the hive host and run the following:

export HIVE_CONF_DIR=/etc/hive/conf/conf.server
hive --service metatool -listFSRoot

Re: Hive metatool cannot connect to oracle database using long jdbc uri

juan_manuel_nie — Tue, 14 Mar 2017 23:36:04 GMT

Hi @Deepesh , that was my problem, by default it takes /etc/hive/conf/ when I tried to use the conf.server directory also forgot the export and just declared a local val that's why it failed when I tried it. Thank you.