Why do I get a “Hive support is required to CREATE Hive TABLE (AS SELECT)” error when creating a table?

Question

I'm running into an issue when trying to create a table.

Here is the code to create the table, where the exception is occurring:

sparkSession.sql(s"CREATE TABLE IF NOT EXISTS mydatabase.students(" +
s"name string," + s"age int)")

Here is the spark session configuration:

lazy val sparkSession = SparkSession
.builder()
.appName("student_mapping")
.enableHiveSupport()
.getOrCreate()

And this is the exception:

org.apache.spark.sql.AnalysisException: Hive support is required to 
CREATE Hive TABLE (AS SELECT);;'CreateTable `mydatabase`.`students`,
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Ignore

My question is: Why is this exception occurring? I have several other spark programs running with the same session configurations, running flawlessly. I'm using Scala 2.11 and Spark 2.3.

Answer 1

SparkSession is the entry point to Spark SQL. It is one of the very first objects you create while developing a Spark SQL application.

SessionState is the state separation layer between Spark SQL sessions, including SQL configuration, tables, functions, UDFs, SQL parser, and everything else that depends on a SQLConf.

SessionState is available as the sessionState property of a SparkSession

Internally, sessionState clones the optional parent SessionState (if given when creating the SparkSession) or creates a new SessionState using BaseSessionStateBuilder as defined by spark.sql.catalogImplementation configuration property:

in-memory (default) for org.apache.spark.sql.internal.SessionStateBuilder

hive for org.apache.spark.sql.hive.HiveSessionStateBuilder

For using hive you should use the class org.apache.spark.sql.hive.HiveSessionStateBuilder and according to the document this can be done by setting the property spark.sql.catalogImplementation to hive when creating SparkSession object:

val conf = new SparkConf
      .set("spark.sql.warehouse.dir", "hdfs://namenode/sql/metadata/hive")
      .set("spark.sql.catalogImplementation","hive")
      .setMaster("local[*]")
      .setAppName("Hive Example")

val spark = SparkSession.builder()
      .config(conf)
      .enableHiveSupport()
      .getOrCreate()

or you can pass the property --conf spark.sql.catalogImplementation=hive when you submit your job to the cluster.