Unable to connect hive metastore from sparksql

Question

Hive .14 Spark 1.6 .Trying to connect hive table from spark pragmatically. I have already put my hive-site.xml in spark conf folder. But when I run this code, everytime its connecting to underlying hive metastore i.e. Derby. I tried googled a lot but evertywhere I am getting suggestion to put hive-site.xml in spark cofiguration folder, which I already did. Please someone suggest me the solution.Below is my code

FYI: My existing hive is using MYSQL as metastore.

I am running this code directly from eclipse, not using spark-submit utility.

package org.scala.spark

import org.apache.spark.SparkContext
import org.apache.spark.SparkConf
import org.apache.spark.sql.hive.HiveContext

object HiveToHdfs {

def main(args: Array[String]) 
  {

    val conf=new SparkConf().setAppName("HDFS to Local").setMaster("local")
    val sc=new SparkContext(conf)  
    val hiveContext=new org.apache.spark.sql.hive.HiveContext(sc)
    import hiveContext.implicits._
    hiveContext.sql("load data local inpath '/home/cloudera/Documents/emp_table.txt' into table employee")
    sc.stop()
  }
}

Below are my eclipse error log:

16/11/18 22:09:03 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/11/18 22:09:03 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
16/11/18 22:09:06 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MFieldSchema" is tagged as "embedded-only" so does not have its own datastore table.
16/11/18 22:09:06 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MOrder" is tagged as "embedded-only" so does not have its own datastore table.
**16/11/18 22:09:06 INFO MetaStoreDirectSql: Using direct SQL, underlying DB is DERBY**
16/11/18 22:09:06 INFO ObjectStore: Initialized ObjectStore
16/11/18 22:09:06 WARN ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 1.2.0
16/11/18 22:09:06 WARN ObjectStore: Failed to get database default, returning NoSuchObjectException
16/11/18 22:09:07 INFO HiveMetaStore: Added admin role in metastore
16/11/18 22:09:07 INFO HiveMetaStore: Added public role in metastore
16/11/18 22:09:07 INFO HiveMetaStore: No user is added in admin role, since config is empty
16/11/18 22:09:07 INFO HiveMetaStore: 0: get_all_databases
16/11/18 22:09:07 INFO audit: ugi=cloudera  ip=unknown-ip-addr  cmd=get_all_databases   
16/11/18 22:09:07 INFO HiveMetaStore: 0: get_functions: db=default pat=*
16/11/18 22:09:07 INFO audit: ugi=cloudera  ip=unknown-ip-addr  cmd=get_functions: db=default pat=* 
16/11/18 22:09:07 INFO Datastore: The class "org.apache.hadoop.hive.metastore.model.MResourceUri" is tagged as "embedded-only" so does not have its own datastore table.
Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: The root scratch dir: /tmp/hive on HDFS should be writable. Current permissions are: rwx------
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:522)
    at org.apache.spark.sql.hive.client.ClientWrapper.<init>(ClientWrapper.scala:194)
    at org.apache.spark.sql.hive.client.IsolatedClientLoader.createClient(IsolatedClientLoader.scala:238)
    at org.apache.spark.sql.hive.HiveContext.executionHive$lzycompute(HiveContext.scala:218)
    at org.apache.spark.sql.hive.HiveContext.executionHive(HiveContext.scala:208)
    at org.apache.spark.sql.hive.HiveContext.functionRegistry$lzycompute(HiveContext.scala:462)
    at org.apache.spark.sql.hive.HiveContext.functionRegistry(HiveContext.scala:461)
    at org.apache.spark.sql.UDFRegistration.<init>(UDFRegistration.scala:40)
    at org.apache.spark.sql.SQLContext.<init>(SQLContext.scala:330)
    at org.apache.spark.sql.hive.HiveContext.<init>(HiveContext.scala:90)
    at org.apache.spark.sql.hive.HiveContext.<init>(HiveContext.scala:101)
    at org.scala.spark.HiveToHdfs$.main(HiveToHdfs.scala:15)
    at org.scala.spark.HiveToHdfs.main(HiveToHdfs.scala)
Caused by: java.lang.RuntimeException: The root scratch dir: /tmp/hive on HDFS should be writable. Current permissions are: rwx------
    at org.apache.hadoop.hive.ql.session.SessionState.createRootHDFSDir(SessionState.java:612)
    at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:554)
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:508)
    ... 12 more
16/11/18 22:09:07 INFO SparkContext: Invoking stop() from shutdown hook

Please let me know if any other in other information is also needed to rectify it.

Hi Nirmal, this is my hive-site.xml. I changed it into txt format [hive-site.xml](https://www.dropbox.com/s/gj6mtt07e0po20w/hive-site.txt?dl=0) — Kapil Kumar, Nov 19 '16 at 09:23
Check the permission of the directory. i am seeing below error Caused by: java.lang.RuntimeException: The root scratch dir: /tmp/hive on HDFS should be writable. Current permissions are: rwx------ — Ishan Kumar, Nov 19 '16 at 09:47
Ishan, its local directory for derby metastore. But it should not go to derby metastore. It should go to mysql metastore. — Kapil Kumar, Nov 19 '16 at 09:50
Yes Ishan, I did copied hive-site.xml into spark conf folder — Kapil Kumar, Nov 19 '16 at 09:01

score 0 · Answer 1 · answered Nov 19 '16 at 14:06

check this link -> https://issues.apache.org/jira/browse/SPARK-15118 metastore might be using mysql db

the above error is from,

  <property>
    <name>hive.exec.scratchdir</name>
    <value>/tmp/hive</value>
    <description>HDFS root scratch dir for Hive jobs which gets created with write all (733) permission. For each connecting user, an HDFS scratch dir: ${hive.exec.scratchdir}/&lt;username&gt; is created, with ${hive.scratch.dir.permission}.</description>
  </property>

give permission for /tmp/hive

Unable to connect hive metastore from sparksql

1 Answers1