Pivotal Knowledge Base

Follow

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

Environment

Product Version
Pivotal HD 3.0.1
Hive 0.14
Ambari  2.1.x

Symptom

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)

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 passowrd 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.

 

Comments

Powered by Zendesk