Hive CLI and Hiveserver2 Inconsistent Metastore

Question

I'm trying to modify an existing Azure HDInsight cluster to point at an existing Hive Metastore (hosted on an MSSQL instance). I've changed the following parameters in hive-site.xml to point to the existing Metastore:

"javax.jdo.option.ConnectionDriverName" : "com.microsoft.sqlserver.jdbc.SQLServerDriver", 
"javax.jdo.option.ConnectionUserName" : "<<user>>",     
"javax.jdo.option.ConnectionPassword" : "<<password>>",   
"javax.jdo.option.ConnectionURL" : "jdbc:sqlserver://<<server>>.database.windows.net:1433;database=HiveMetaStoreEast;user=<<user>>;password=<<password>>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

This seems to have somewhat worked, as I am able to access both Hive CLI and Hiveserver2 via Beeline. The strange thing is show databases; output different results depending on the client being used. I read that starting Hive 0.14 (which I am running), more granular configuration is available for Hive/Hiveserver2 using hiveserver2-site.xml, etc. I've tried setting the hive.metastore.uris parameter in hiveserver2-site.xml to match what it shows in hive-site.xml but still get the same strange results.

In summary, how can I know for sure the Hiveserver2 and Hive CLI processes are pointed at the same (and correct) Metastore URIs?

Kyle Dunn Kyle Dunn · Accepted Answer · 2015-11-25T05:04:54

Just after posting this I found a similar thread on the Hortonworks website: http://hortonworks.com/community/forums/topic/configuration-of-hiveserver2-to-use-a-remote-metastore-server/#post-81960

It appears the startHiveserver2.sh.j2 start script, residing here (on my Hive nodes) /var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/templates/ contains an empty string CLI override of the hive.metastore.uris parameter which I believe forces Hiveserver2 to start in local metastore mode and hence creating inconsistent views between Hive CLI (using remote URIs) and Beeline (using local).

See below for the patch that resolved the inconsistency:

--- startHiveserver2.sh.j2  2015-11-25 04:06:15.357996439 +0000
+++ /var/lib/ambari-server/resources/common-services/HIVE/0.12.0.2.0/package/templates/startHiveserver2.sh.j2   2015-11-25 03:43:29.837452851 +0000
@@ -20,5 +20,6 @@
#

HIVE_SERVER2_OPTS=" -hiveconf hive.log.file=hiveserver2.log -hiveconf hive.log.dir=$5"
-HIVE_CONF_DIR=$4 {{hive_bin}}/hiveserver2 -hiveconf hive.metastore.uris=" " ${HIVE_SERVER2_OPTS} > $1 2> $2 &
+#HIVE_CONF_DIR=$4 {{hive_bin}}/hiveserver2 -hiveconf hive.metastore.uris=" " ${HIVE_SERVER2_OPTS} > $1 2> $2 &
+HIVE_CONF_DIR=$4 {{hive_bin}}/hiveserver2 ${HIVE_SERVER2_OPTS} > $1 2> $2 &
echo $!|cat>$3

Hive CLI and Hiveserver2 Inconsistent Metastore

1 Answers