Spark build in hive MySQL metastore isn't being used

Question

I'm using Apache Spark 2.1.1 and I have put the following hive-site.xml on $SPARK_HOME/conf folder:

<?xml version="1.0"?>
<configuration>
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://mysql_server:3306/hive_metastore?createDatabaseIfNotExist=true</value>
  <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
  <description>Driver class name for a JDBC metastore</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
  <description>username to use against metastore database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>password</value>
  <description>password to use against metastore database</description>
</property>
<property>
  <name>hive.metastore.schema.verification</name>
  <value>false</value>
  <description>password to use against metastore database</description>
</property>
<property>
  <name>hadoop.tmp.dir</name>
  <value>${test.tmp.dir}/hadoop-tmp</value>
  <description>A base for other temporary directories.</description>
</property>
<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>hdfs://hadoop_namenode:9000/value_iq/hive_warehouse/</value>
  <description>Warehouse Location</description>
</property>
</configuration>

When I start the thrift server the metastore schema is created on my MySQL DB but is not used, instead Derby is used.

Could not find any error on the thrift server log file, the only thing that calls my attentions is that it attempts to use MySQL at first (INFO MetaStoreDirectSql: Using direct SQL, underlying DB is MYSQL) but then without any error use Derby instead (INFO MetaStoreDirectSql: Using direct SQL, underlying DB is DERBY). This is the thrift server log https://www.dropbox.com/s/rxfwgjm9bdccaju/spark-root-org.apache.spark.sql.hive.thriftserver.HiveThriftServer2-1-s-master.value-iq.com.out?dl=0

I have no hive installed on my system, I just pretend to use the built in Hive of Apache Spark.

I'm using mysql-connector-java-5.1.23-bin.jar which is located on $SPARK_HOME/jars folder.

Burly

set, value of hive.metastore.schema.verification to false in hive-site.xml in both hive and spark conf and restart services and try again – Lower

I have set it and same behavior. When you say in both hive and spark you mean in $SPARK_HOME/conf/hive-site.xml and $SPARK_HOME/conf/spark-defaults.conf? Remember I don't have hive installed, I'm using Spark built in Hive. – Brotherly

Answer 1

As it appears in the hive-site.xml you have not set the metastore service to connect to. So spark will use the default one which is local metastore service with derby DB backend
I order to use Metastore service that has MySQL DB as its backend, you have to :

1. Start the metastore service. you can have a look here how to start the service hive metastore admin manual. You start your metastore service with the backend of MySQL DB, using your same hive-site.xml and you add the folowing lines to start the metastore service on METASTORESERVER on the port XXXX:

   <property>
     <name>hive.metastore.uris</name>
     <value>thrift://METASTRESERVER:XXXX</value>
   </property>
   

2. Let spark knows where the metastore service has started. That could be done using the same hive-site.xml you'have used when starting the metastore service (with the lines above added to it) copy this file into the configuration path of Spark, then restart your spark thrift server

Monopolize

But I don't hive installed, I just have Apache Spark. So, if I start the thrift server located on $SPARK_HOME/sbin folder it will use the only hive-site.xml available on $SPARK_HOME/conf. Or there is something that I'm still not understanding? – Brotherly

Unfortunately, I do not know another way to start the metastore service without using hive. your understaing is good, but the idea is that spark thrift server is build upon hive server, using metastore service which play the role of intermediating between hive/spark server and the backend database – Monopolize

to start a metastore using hive, you need just to download a version of hive unzip it and then put your hive-site.xml in the configuration of hive, then start its metastore server. no extra configuration needed. – Monopolize

Thanks for your answer, I have rewarded it. One thing, if I use hive then do I need to install it on every node of my cluster, or it will be enough to have it in one machine? – Brotherly

If you want to use hive just to start the metastore, then you install it on one node in the cluster. NO need to install it on all nodes.<br/> thanks for the bounty, please when you feel this answer is the solution to the problem, accept it so it will be marked as answered ;) – Monopolize

The problem is that I want to use the thrift server, the one provided by spark to query it through JDBC, but if I start hive thrift server then the queries will run over hive not using the spark engine. What can I do? – Brotherly

Let us continue this discussion in chat. – Monopolize

I have found the way to solve the issue, as you correctly suggest I have installed hive to use the metastore service, the problem was that I have installed Hive 2.1 and I was reading some documentation about spark and spark is build with Hive 1.2, so I have installed that one and then it works. Somehow when I start the thrift server it still logs Using direct SQL, underlying DB is DERBY but it connects to the hive metastore URI too. I have add in the question that I wasn't using Hive, so pls add to your answer that I should install Hive the 1.2 version and I'll accept it. Thanks a lot. – Brotherly

Good catch!!, I forgot to say that. if you want to dive more in the dependencies and their versions have a look github.com/apache/spark/blob/v2.1.0/pom.xml#L133 – Monopolize

I actually found that the metastore service was not needed. After originally following these directions and getting it working with it, I then just removed hive.metastore.uris, shut down the hive metastore service and had spark connect direct to MySQL and that seemed to work exactly the same but without the hive requirement at all. Is there any downside of doing it this way? It seems to be what the OP tried to do but I'm not sure why it didn't work out then. – Olivo