Failed initializing database with "Access denied" when accessing the Hive metatool in Pivotal HD
search cancel

Failed initializing database with "Access denied" when accessing the Hive metatool in Pivotal HD

book

Article ID: 294776

calendar_today

Updated On:

Products

Services Suite

Issue/Introduction

Symptoms:

When trying to run Hive metatool commands via the CLI, access denied messages are displayed:

-bash-4.1$ hive --service metatool -listFSRoot
WARNING: Use "yarn jar" to launch YARN applications.
Initializing HiveMetaTool..
16/07/02 03:23:10 INFO metastore.ObjectStore: ObjectStore, initialize called
16/07/02 03:23:10 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
16/07/02 03:23:10 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored
16/07/02 03:23:11 ERROR Datastore.Schema: Failed initialising database.
Unable to open a test connection to the given database. JDBC url = jdbc:mysql://hawq20.lab/hive?createDatabaseIfNotExist=true, username = hive. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
java.sql.SQLException: Access denied for user 'hive'@'hawq20.lab' (using password: YES)

Environment


Cause

The Hive Metatool reads the /etc/hive/conf/hive-site.xml to find the password for the Metastore RDMS password. However from Ambari 2.1.x, passwords have been removed from the hive-site.xml for security reasons.

Resolution

There are two ways to resolve this issue (both will require knowing the Metastore RDMS password):

  • Temporary solution: Update hive-site.xml with the password
  • Permanent solution: Store the Metatstore RDMS password in a JCEKS file (locally or in HDFS) by using "hadoop credentials" command.

Resolution A: Update hive-site.xml with the password

1. On the node where the Hive Metatool will be run, edit /etc/hive/conf/hive-site.xml via CLI (not via Ambari).

2. Add in the lines (where changeme is the MYSQL password for user Hive):

    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>changeme</value>
    </property>
 

3. The connection should now work:

[root@hawq20 ~]# hive --service metatool -listFSRoot
WARNING: Use "yarn jar" to launch YARN applications.
Initializing HiveMetaTool..
16/07/02 04:21:02 INFO metastore.ObjectStore: ObjectStore, initialize called
16/07/02 04:21:03 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
16/07/02 04:21:03 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored
16/07/02 04:21:04 INFO metastore.ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,Database,Type,FieldSchema,Order"
16/07/02 04:21:06 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:21:06 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:21:06 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:21:06 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:21:06 INFO DataNucleus.Query: Reading in results for query "org.datanucleus.store.rdbms.query.SQLQuery@0" since the connection used is closing
16/07/02 04:21:06 INFO metastore.MetaStoreDirectSql: Using direct SQL, underlying DB is MYSQL
16/07/02 04:21:06 INFO metastore.ObjectStore: Initialized ObjectStore
Listing FS Roots..
hdfs://hawq20.lab:8020/apps/hive/warehouse
[root@hawq20 ~]#

 

Resolution B: Store the Metastore RDMS password in a JCEKS file

1. On the node where the Hive Metatool will be used, create the JCEKS file as root:

hadoop credential create javax.jdo.option.ConnectionPassword -provider jceks://file/usr/lib/hive/conf/hive.jceks
Enter password: <ENTER RDMS PASSWORD>
Enter password again: 
javax.jdo.option.ConnectionPassword has been successfully created.
org.apache.hadoop.security.alias.JavaKeyStoreProvider has been updated.
2. Set correct ownership on the /usr/lib/hive/conf/hive.jceks file:
chown hive:hadoop /usr/lib/hive/conf/hive.jceks
3. In Ambari, under HIVE / Configs / Advanced / Customer hive-site.xml, add in the configuration:
hadoop.security.credential.provider.path=jceks://file/usr/lib/hive/conf/hive.jceks

4. Restart services as requested by Ambari.

5. Connection to the Metatool should now work:

[root@hawq20 conf]# hive --service metatool -listFSRoot
WARNING: Use "yarn jar" to launch YARN applications.
Initializing HiveMetaTool..
16/07/02 04:47:07 INFO metastore.ObjectStore: ObjectStore, initialize called
16/07/02 04:47:07 INFO DataNucleus.Persistence: Property hive.metastore.integral.jdo.pushdown unknown - will be ignored
16/07/02 04:47:07 INFO DataNucleus.Persistence: Property datanucleus.cache.level2 unknown - will be ignored
16/07/02 04:47:08 INFO metastore.ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,Database,Type,FieldSchema,Order"
16/07/02 04:47:09 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:47:09 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:47:09 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:47:09 INFO DataNucleus.Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
16/07/02 04:47:09 INFO DataNucleus.Query: Reading in results for query "org.datanucleus.store.rdbms.query.SQLQuery@0" since the connection used is closing
16/07/02 04:47:09 INFO metastore.MetaStoreDirectSql: Using direct SQL, underlying DB is MYSQL
16/07/02 04:47:09 INFO metastore.ObjectStore: Initialized ObjectStore
Listing FS Roots..
hdfs://hawq20.lab:8020/apps/hive/warehouse
[root@hawq20 conf]# 

Note: If the connection still fails, grants within the MYSQL database should be checked.


Powered by Wolken Software