java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

Question

I have Hadoop 2.7.1 and apache-hive-1.2.1 versions installed on ubuntu 14.0.

Why this error is occurring ?
Is any metastore installation required?
When we typing hive command on terminal how the xml's internally called, what is the flow of those xml's?
Any other configuration's required?

When I am writing the hive command on ubuntu 14.0 terminal it is throwing the below exception.

 $ hive

    Logging initialized using configuration in jar:file:/usr/local/hive/apache-hive-1.2.1-bin/lib/hive-common-1.2.1.jar!/hive-log4j.properties
    Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:522)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        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:520)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
    Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1523)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:86)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:132)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
        at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3005)
        at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3024)
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:503)
        ... 8 more
    Caused by: java.lang.reflect.InvocationTargetException
        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:426)
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1521)
        ... 14 more
    Caused by: javax.jdo.JDOFatalInternalException: Error creating transactional connection factory
    NestedThrowables:
    java.lang.reflect.InvocationTargetException
        at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:587)
        at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:788)
        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:520)
        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:365)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPersistenceManager(ObjectStore.java:394)
        at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:291)
        at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:258)
        at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76)
        at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.<init>(RawStoreProxy.java:57)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:66)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:593)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:571)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:624)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:461)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:66)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:72)
        at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5762)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:199)
        at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:74)
        ... 19 more
    Caused by: java.lang.reflect.InvocationTargetException
        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:426)
        at org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:631)
        at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:325)
        at org.datanucleus.store.AbstractStoreManager.registerConnectionFactory(AbstractStoreManager.java:282)
        at org.datanucleus.store.AbstractStoreManager.<init>(AbstractStoreManager.java:240)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.<init>(RDBMSStoreManager.java:286)
        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:426)
        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)
        ... 48 more
    Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the "BONECP" plugin to create a ConnectionPool gave an error : The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
        at org.datanucleus.store.rdbms.ConnectionFactoryImpl.generateDataSources(ConnectionFactoryImpl.java:259)
        at org.datanucleus.store.rdbms.ConnectionFactoryImpl.initialiseDataSources(ConnectionFactoryImpl.java:131)
        at org.datanucleus.store.rdbms.ConnectionFactoryImpl.<init>(ConnectionFactoryImpl.java:85)
        ... 66 more
    Caused by: org.datanucleus.store.rdbms.connectionpool.DatastoreDriverNotFoundException: The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
        at org.datanucleus.store.rdbms.connectionpool.AbstractConnectionPoolFactory.loadDriver(AbstractConnectionPoolFactory.java:58)
        at org.datanucleus.store.rdbms.connectionpool.BoneCPConnectionPoolFactory.createConnectionPool(BoneCPConnectionPoolFactory.java:54)
        at org.datanucleus.store.rdbms.ConnectionFactoryImpl.generateDataSources(ConnectionFactoryImpl.java:238)
        ... 68 more

To avoid above error I created hive-site.xml with :

 <configuration>

      <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/home/local/hive-metastore-dir/warehouse</value>

      </property>

    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://localhost:3306/hivedb?createDatabaseIfNotExist=true</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>user</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>password</value>
    </property>

    <configuration>

Also provided the environment variables in ~/.bashrc file; Still the error persist

#HIVE home directory configuration
export HIVE_HOME=/usr/local/hive/apache-hive-1.2.1-bin
export PATH="$PATH:$HIVE_HOME/bin"

I also answer this question in this link https://stackoverflow.com/questions/22711364/java-lang-runtimeexceptionunable-to-instantiate-org-apache-hadoop-hive-metastor/51499009#51499009 — Hamid, Jul 24 '18 at 12:51

Ravi Vyas · Answer 1 · 2017-02-20T11:44:50.127

34

starting the hive metastore service worked for me. First, set up the database for hive metastore:

 $ hive --service metastore

` https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.4/bk_installing_manually_book/content/validate_installation.html

Second, run the following commands:

 $ schematool -dbType mysql -initSchema  
 $ schematool -dbType mysql -info

https://cwiki.apache.org/confluence/display/Hive/Hive+Schema+Tool

edited Feb 20 '17 at 11:44

answered Feb 20 '17 at 11:32

Ravi Vyas

410
6
12

hive --service metastore & and wait 5min was enough to repair hive in my case – 42n4 Mar 30 '21 at 06:55
It's also possible to use `derby` in local mode. Just replace `mysql` in the above commands with `derby` e.g. ```schematool -dbType derby -initSchema ``` – WestCoastProjects Nov 10 '22 at 20:21

score 32 · Accepted Answer · edited Jun 20 '20 at 09:12

I did below modifications and I am able to start the Hive Shell without any errors:

1. ~/.bashrc

Inside bashrc file add the below environment variables at End Of File : sudo gedit ~/.bashrc

#Java Home directory configuration
export JAVA_HOME="/usr/lib/jvm/java-9-oracle"
export PATH="$PATH:$JAVA_HOME/bin"

# Hadoop home directory configuration
export HADOOP_HOME=/usr/local/hadoop
export PATH=$PATH:$HADOOP_HOME/bin
export PATH=$PATH:$HADOOP_HOME/sbin

export HIVE_HOME=/usr/lib/hive
export PATH=$PATH:$HIVE_HOME/bin

2. hive-site.xml

You have to create this file(hive-site.xml) in conf directory of Hive and add the below details

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
</property>


<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>root</value>
</property>

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

<property>
  <name>datanucleus.autoCreateSchema</name>
  <value>true</value>
</property>

<property>
  <name>datanucleus.fixedDatastore</name>
  <value>true</value>
</property>

<property>
 <name>datanucleus.autoCreateTables</name>
 <value>True</value>
 </property>

</configuration>

3. You also need to put the jar file(mysql-connector-java-5.1.28.jar) in the lib directory of Hive

4. Below installations required on your Ubuntu to Start the Hive Shell:

MySql
Hadoop
Hive
Java

5. Execution Part:

Start all services of Hadoop: start-all.sh
Enter the jps command to check whether all Hadoop services are up and running: jps
Enter the hive command to enter into hive shell: hive

I am not able to find the solution and where i am doing wrong... I am getting error "FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient" when i run the command in hive>show databases; propmpt.. Please help me on that where i m doing wrong — Shyam Gupta, Feb 02 '17 at 06:13
Use this command to get jar on Centos: `wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.28/mysql-connector-java-5.1.28.jar` — Saurav Sahu, Jul 15 '20 at 06:31

score 22 · Answer 3 · answered Aug 22 '18 at 07:47

22

If you're just playing around in local mode, you can drop metastore DB and reinstate it:

rm -rf metastore_db/
$HIVE_HOME/bin/schematool -initSchema -dbType derby

answered Aug 22 '18 at 07:47

Ajeden

329
2
2

Apt answer. Anyone else try this first before any complex ones. – Mark Maxwell Jul 09 '23 at 11:21

score 14 · Answer 4 · answered Aug 20 '17 at 11:45

In my case when i tried

$ hive --service metastore

I got

MetaException(message:Version information not found in metastore. )

The necessary tables required for the metastore are missing in MySQL. Manually create the tables and restart hive metastore.

cd $HIVE_HOME/scripts/metastore/upgrade/mysql/ 

< Login into MySQL > 

mysql> drop database IF EXISTS <metastore db name>; 
mysql> create database <metastore db name>; 
mysql> use <metastore db name>; 
mysql> source hive-schema-2.x.x.mysql.sql;

metastore db name should match the database name mentioned in hive-site.xml files connection property tag.

hive-schema-2.x.x.mysql.sql file depends on the version available in the current directory. Try to go for the latest because it holds many old schema files also.

Now try to execute hive --service metastore If everything goes cool, then simply start the hive from terminal.

>hive

I hope the above answer serves your need.

it worked with & for a while but I had to reboot the main server to recover from all errors. — 42n4, Apr 11 '21 at 14:25

score 6 · Answer 5 · answered Apr 11 '18 at 09:18

6

Run hive in debug mode

hive -hiveconf hive.root.logger=DEBUG,console

and then execute

show tables

can find the actual problem

answered Apr 11 '18 at 09:18

Vignesh

93
1
7

score 4 · Answer 6 · answered Jun 18 '21 at 19:54

you just need to instantiate schema and you can do the same with the below commands.i dit it and am able to run hive query without throwing ERROR:Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

cd $HIVE_HOME
mv metastore_db metastore_db_bkup
schematool -initSchema -dbType derby
bin/hive

now run your query:

hive> show databases;

score 3 · Answer 7 · edited Jan 16 '19 at 19:17

I have used MySQL DB for Hive MetaStore. Please follow the below steps:

in hive-site.xml the metastore should be proper

<property>
 <name>javax.jdo.option.ConnectionURL</name> 
 <value>jdbc:mysql://localhost/metastorecreateDatabaseIfNotExist=true&amp;useSSL=false</value>
</property>

go to the mysql: mysql -u hduser -p
then run drop database metastore
then come out from MySQL and execute schematool -initSchema dbType mysql

Now error will go.

score 2 · Answer 8 · answered Feb 17 '16 at 11:05

2

In the middle of the stack trace, lost in the "reflection" junk, you can find the root cause:

The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.

answered Feb 17 '16 at 11:05

Samson Scharfrichter

8,884
1
17
36

Hi Samson Scharfrichter, As per the suggestion,I have provided the CLASSPATH specification, Now the DatastoreDriverNotFoundException is resolved. But still the Exception: Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient is there. Any suggestions for this. – Arti Nalawade Feb 18 '16 at 04:23

libin · Answer 9 · 2021-01-18T09:29:50.923

mybe your hive metastore are inconsistent! I'm in this scene.

first. I run

 $ schematool -dbType mysql -initSchema

then I found this

Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!

then I run

 $ schematool -dbType mysql -info

found this error

Hive distribution version: 2.3.0 Metastore schema version: 1.2.0 org.apache.hadoop.hive.metastore.HiveMetaException: Metastore schema version is not compatible. Hive Version: 2.3.0, Database Schema Version: 1.2.0

so i format my hive metastore, then it's done!

drop mysql database, the database named hive_db
run schematool -dbType mysql -initSchema for initialize metadata

score 2 · Answer 10 · answered Jul 13 '20 at 00:58

This is probably due to its lack of connections to the Hive Meta Store,my hive Meta Store is stored in Mysql,so I need to visit Mysql,So I add a dependency in my build.sbt

libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.38"

and the problem is solved!

score 1 · Answer 11 · answered Jul 26 '17 at 21:52

1

I have also faced this problem but i had restart Hadoop and use command hadoop dfsadmin -safemode leave

now start hive it will work i think

answered Jul 26 '17 at 21:52

Akash

11
4

score 1 · Answer 12 · answered Apr 08 '19 at 06:45

I solved this problem by removing --deploy-mode cluster from spark-submit code. By default , spark submit takes client mode which has following advantage :

1. It opens up Netty HTTP server and distributes all jars to the worker nodes.
2. Driver program runs on master node , which means dedicated resources to driver process.

While in cluster mode :

 1.  It runs on worker node.
 2. All the jars need to be placed in a common folder of the cluster so that it is accessible to all the worker nodes or in folder of each worker node.

Here it's not able to access hive metastore due to unavailability of hive jar to any of the nodes in cluster.

drorhun · Answer 13 · 2022-08-04T21:47:40.557

1- Append the following lines to the startup file ~/.bashrc

export HIVE_HOME=~/hive 
export PATH=$PATH:$HIVE_HOME/bin 
export CLASSPATH=$HADOOP_HOME/lib/* 
export CLASSPATH=$CLASSPATH:HIVE_HOME/lib/*:.

2- Edit the file $HIVE_HOME/conf/hive-env.sh

cd $HIVE_HOME/conf
cp hive-env.sh.template hive-env.sh

3- Edit the hive-env.sh file to append the following line

export HADOOP_HOME=$HADOOP_HOME

4- There are two different versions of the "guava" library used by Hadoop and Hive. The solution is to use the same version of "guava" in both, Hadoop and Hive: Note: (My system is using guava-27, that's why I shared this example. It depends on your guava version of Hadoop and Hive. You need to check it)

cp ~/hadoop/share/hadoop/hdfs/lib/guava-27.0-jre.jar   ~/hive/lib/
rm ~/hive/lib/guava-19.0.jar

5- In the conf directory of hive (~/hive/conf), create the metastore:

schematool -initSchema -dbType derby

6- We also need to create the configuration file of Hive, in the conf directory:

cp hive-default.xml.template  hive-site.xml

In the file, hive-site.xml, at line 3215, there are some characters that should be deleted around column 96. The four characters to delete are: 

7- Edit the file hive-site.xml as follows: Replace the occurrences of

${system:java.io.tmpdir} with /tmp/hive_io

Replace ${system:user.name} with hadoop

Note: ${system:java.io.tmpdir} occurs 3-4 times in hive-site.xml. Make sure change everything.

8- Finally you can type hive. (Don't forget, first of all you need to run hadoop before hive) Note: make sure starting the hive under hive path.

score 0 · Answer 14 · answered Mar 17 '20 at 08:31

0

just open the hive terminal from the hive folder,after editing (bashrc) and (hive-site.xml) files. Steps-- open hive folder where it is installed. now open terminal from folder.

answered Mar 17 '20 at 08:31

Abhinav Rai

9
1

Hey Abhinav, Could you at least elaborate, which of the questions this shall be the answer to? Please think about providing more context, so that other users understand why your proposed solution would work. – Aiyion.Prime Mar 17 '20 at 09:13

score 0 · Answer 15 · answered Dec 25 '20 at 08:04

In my case, I stopped my docker hive container and run it again and finally, it worked. Hope it will be useful for someone.

Note: This might be caused because there might be an instance running in the background so stopping the container will stop all background instances.

score 0 · Answer 16 · answered Mar 12 '21 at 13:40

This is happening because you have NOT started the Hive Metastore ... the simples way to do that is to use the default Derby database one ... you can follow this link : https://sparkbyexamples.com/apache-hive/hive-hiveexception-java-lang-runtimeexception-unable-to-instantiate-org-apache-hadoop-hive-ql-metadata-sessionhivemetastoreclient/

score 0 · Answer 17 · answered Sep 22 '21 at 05:01

I Fixed this Problem by creating and copying hive-default.xml.template to hive-site.xml. To create that use can use below commands

cd /usr/local/Cellar/hive/2.7.1/libexec/conf (please replace hive version)
cp hive-default.xml.template hive-site.xml

and changed the values of the below properties in hive-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
   <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hiveuser</value>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>password</value>
   </property>
   <property>
      <name>datanucleus.fixedDatastore</name>
      <value>false</value>
   </property>
   <property>
      <name>hive.exec.local.scratchdir</name>
      <value>/tmp/hive</value>
      <description>Local scratch space for Hive jobs</description>
   </property>
   <property>
      <name>hive.downloaded.resources.dir</name>
      <value>/tmp/hive</value>
      <description>Temporary local directory for added resources in the remote file system.</description>
   </property>
   <property>
      <name>hive.querylog.location</name>
      <value>/tmp/hive</value>
      <description>Location of Hive run time structured log file</description>
   </property>
  <property>
    <name>hive.druid.metadata.db.type</name>
    <value>mysql</value>
    <description>
      Expects one of the pattern in [mysql, postgresql, derby].
      Type of the metadata database.
    </description>
  </property>
</configuration>

After that, I created DB in MySql with the name matastore, created a usename password and grand permissions for it by using the following queries.

$ mysql
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE ON metastore.* TO 'hiveuser'@'localhost';

and run the script in MySql with the following command:

mysql> source /usr/local/Cellar/hive/3.1.2_3/libexec/scripts/metastore/upgrade/mysql/hive-schema-3.1.0.mysql.sql

Also don't forget to move SQL connector jar to hive package using the following commands Download MySQL connector and extract it

tar zxvf mysql-connector-java-5.1.35.tar.gz
sudo cp mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar /usr/local/Cellar/hive/2.7.1/libexec/lib/

That's it. Now I can successfully run show tables, etc commands in Hive. :)

score 0 · Answer 18 · answered Jun 27 '23 at 07:56

I encountered this error when running a spark application written in Java. I resolved it by making Spark related dependencies 'provided' as suggested by the official document. Not sure the exact reason but it works :).

    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_2.12</artifactId>
        <version>2.4.5</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.12</artifactId>
        <version>2.4.5</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-hive_2.12</artifactId>
        <version>2.4.5</version>
        <scope>provided</scope>
    </dependency>