Created 03-14-2017 11:19 AM
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
Created 03-14-2017 04:10 PM
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
Created 03-14-2017 11:30 AM
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
.
Created 03-14-2017 01:19 PM
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
Created 03-14-2017 01:37 PM
@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()); }
Created 03-14-2017 02:35 PM
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
.
Created 03-14-2017 03:25 PM
Now it compiled perfectly
TestOracle]# /usr/jdk64/jdk1.8.0_60/bin/java JDBCVersion JDBC driver version is 12.1.0.2.0
Created 03-14-2017 03:33 PM
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 ?
Created 03-14-2017 03:49 PM
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
Created 03-14-2017 03:56 PM
I have checked both parameters and are exactly the same that I used in the .java file 😞
Created 03-14-2017 04:10 PM
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