Support Questions

Find answers, ask questions, and share your expertise

Hive metatool cannot connect to oracle database using long jdbc uri

avatar
Expert Contributor

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

1 ACCEPTED SOLUTION

avatar

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

View solution in original post

10 REPLIES 10

avatar
Master Mentor

@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

.

avatar
Expert Contributor

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

avatar
Expert Contributor

@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());    }

avatar
Master Mentor

@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

avatar
Expert Contributor

@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

avatar
Master Mentor

@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 ?

avatar
Master Mentor

@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 

.

avatar
Expert Contributor

@Jay SenSharma

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

avatar

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