How to connect Spark SQL to remote Hive metastore (via thrift protocol) with no hive-site.xml?

Question

I'm using HiveContext with SparkSQL and I'm trying to connect to a remote Hive metastore, the only way to set the hive metastore is through including the hive-site.xml on the classpath (or copying it to /etc/spark/conf/).

Is there a way to set this parameter programmatically in a java code without including the hive-site.xml ? If so what is the Spark configuration to use ?

Answer 1

For Spark 1.x, you can set with :

System.setProperty("hive.metastore.uris", "thrift://METASTORE:9083");

final SparkConf conf = new SparkConf();
SparkContext sc = new SparkContext(conf);
HiveContext hiveContext = new HiveContext(sc);

Or

final SparkConf conf = new SparkConf();
SparkContext sc = new SparkContext(conf);
HiveContext hiveContext = new HiveContext(sc);
hiveContext.setConf("hive.metastore.uris", "thrift://METASTORE:9083");

Update If your Hive is Kerberized :

Try setting these before creating the HiveContext :

System.setProperty("hive.metastore.sasl.enabled", "true");
System.setProperty("hive.security.authorization.enabled", "false");
System.setProperty("hive.metastore.kerberos.principal", hivePrincipal);
System.setProperty("hive.metastore.execute.setugi", "true");

Answer 2

In spark 2.0.+ it should look something like that:

Don't forget to replace the "hive.metastore.uris" with yours. This assume that you have a hive metastore service started already (not a hiveserver).

 val spark = SparkSession
          .builder()
          .appName("interfacing spark sql to hive metastore without configuration file")
          .config("hive.metastore.uris", "thrift://localhost:9083") // replace with your hivemetastore service's thrift url
          .enableHiveSupport() // don't forget to enable hive support
          .getOrCreate()

        import spark.implicits._
        import spark.sql
        // create an arbitrary frame
        val frame = Seq(("one", 1), ("two", 2), ("three", 3)).toDF("word", "count")
        // see the frame created
        frame.show()
        /**
         * +-----+-----+
         * | word|count|
         * +-----+-----+
         * |  one|    1|
         * |  two|    2|
         * |three|    3|
         * +-----+-----+
         */
        // write the frame
        frame.write.mode("overwrite").saveAsTable("t4")

Answer 3

I too faced same problem, but resolved. Just follow this steps in Spark 2.0 Version

Step1: Copy hive-site.xml file from Hive conf folder to spark conf.

Step 2: edit spark-env.sh file and configure your mysql driver. (If you are using Mysql as a hive metastore.)

Or add MySQL drivers to Maven/SBT (If using those)

Step3: When you are creating spark session add enableHiveSupport()

val spark = SparkSession.builder.master("local").appName("testing").enableHiveSupport().getOrCreate()

Sample code:

package sparkSQL

/**
  * Created by venuk on 7/12/16.
  */

import org.apache.spark.sql.SparkSession

object hivetable {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder.master("local[*]").appName("hivetable").enableHiveSupport().getOrCreate()

    spark.sql("create table hivetab (name string, age int, location string) row format delimited fields terminated by ',' stored as textfile")
    spark.sql("load data local inpath '/home/hadoop/Desktop/asl' into table hivetab").show()
    val x = spark.sql("select * from hivetab")
    x.write.saveAsTable("hivetab")
  }
}

Output:

Answer 4

Spark Version : 2.0.2

Hive Version : 1.2.1

Below Java code worked for me to connect to Hive metastore from Spark:

import org.apache.spark.sql.SparkSession;

public class SparkHiveTest {

    public static void main(String[] args) {

        SparkSession spark = SparkSession
                  .builder()
                  .appName("Java Spark Hive Example")
                  .config("spark.master", "local")
                  .config("hive.metastore.uris",                
                   "thrift://abc123.com:9083")
                  .config("spark.sql.warehouse.dir", "/apps/hive/warehouse")
                  .enableHiveSupport()
                  .getOrCreate();

        spark.sql("SELECT * FROM default.survey_data limit 5").show();
    }
}

Answer 5

Some of the similar questions are marked as duplicate, this is to connect to Hive from Spark without using hive.metastore.uris or separate thrift server(9083) and not copying hive-site.xml to the SPARK_CONF_DIR.

import org.apache.spark.sql.SparkSession
val spark = SparkSession
  .builder()
  .appName("hive-check")
  .config(
    "spark.hadoop.javax.jdo.option.ConnectionURL",
    "JDBC_CONNECT_STRING"
  )
  .config(
    "spark.hadoop.javax.jdo.option.ConnectionDriverName",
    "org.postgresql.Driver"
  )
  .config("spark.sql.warehouse.dir", "/user/hive/warehouse")
  .config("spark.hadoop.javax.jdo.option.ConnectionUserName", "JDBC_USER")
  .config("spark.hadoop.javax.jdo.option.ConnectionPassword", "JDBC_PASSWORD")
  .enableHiveSupport()
  .getOrCreate()
spark.catalog.listDatabases.show(false)

Answer 6

I observed one strange behavior while trying connecting to hive metastore from spark without using hive-site.xml.

Everything works fine When we use hive.metastore.uris property within spark code while creating SparkSession. But if we don't specify in code but specify while using spark-shell or spark-submit with --conf flag it will not work.

It will throw a warning as shown below and it will not connect to remote metastore.

Warning: Ignoring non-Spark config property: hive.metastore.uris

One workaround for this is to use below property.

spark.hadoop.hive.metastore.uris

Answer 7

For Spark 3.x:

// Scala
import org.apache.spark.sql.{Row, SaveMode, SparkSession}

val spark = SparkSession
  .builder()
  .appName("Spark Hive Example")
  .config("spark.sql.warehouse.dir", "hive_warehouse_hdfs_path")
  .enableHiveSupport()
  .getOrCreate()

# Python
from pyspark.sql import SparkSession

spark = SparkSession \
    .builder \
    .appName("Python Spark SQL Hive integration example") \
    .config("spark.sql.warehouse.dir", "hive_warehouse_hdfs_path") \
    .enableHiveSupport() \
    .getOrCreate()

Check databases available with:

spark.catalog.listDatabases().show()

source

Answer 8

Below code worked for me. We can ignore the config of hive.metastore.uris for local metastore, spark will create hive objects in spare-warehouse directory locally.

import org.apache.spark.sql.SparkSession;

object spark_hive_support1 
{
  def main (args: Array[String]) 
   {
    val spark = SparkSession
      .builder()
      .master("yarn")
      .appName("Test Hive Support")
      //.config("hive.metastore.uris", "jdbc:mysql://localhost/metastore")
      .enableHiveSupport
      .getOrCreate();

    import spark.implicits._

    val testdf = Seq(("Word1", 1), ("Word4", 4), ("Word8", 8)).toDF;
    testdf.show;
    testdf.write.mode("overwrite").saveAsTable("WordCount");
  }
}

Answer 9

In Hadoop 3 Spark and Hive catalogs are separated so:

For spark-shell (it comes with .enableHiveSupport() by default) just try:

pyspark-shell --conf spark.hadoop.metastore.catalog.default=hive

For spark-submit job create you spark session like this:

SparkSession.builder.appName("Test").enableHiveSupport().getOrCreate()

then add this conf on your spark-submit command:

--conf spark.hadoop.metastore.catalog.default=hive

But for ORC table(and more generally internal table) it is recommended to use HiveWareHouse Connector.

Answer 10

Setting spark.hadoop.metastore.catalog.default=hive worked for me.