Built with Tutify.io

    AIStudyLabs

    PySpark AnalysisException Hive Support is Required to Create Hive Table as Select

    PySpark AnalysisException Hive Support is Required to Create Hive Table as Select

    While I am trying to create a Hive Table from PySpark I was getting an error AnalysisException: Hive support is required to CREATE Hive TABLE (AS SELECT);

    
from os.path import abspath
from pyspark.sql import SparkSession

# warehouse_location
warehouse_location = abspath('spark-warehouse')

# Create spark session with hive enabled
spark = SparkSession \
    .builder \
    .appName("AIStudyLabs.com") \
    .config("spark.sql.warehouse.dir", warehouse_location) \
    .enableHiveSupport() \
    .getOrCreate()

columns = ["id", "name","age","gender"]

# Create DataFrame 
data = [(1, "James",30,"M"), (2, "Ann",40,"F"),
    (3, "Jeff",41,"M"),(4, "Jennifer",20,"F")]
sampleDF = spark.sparkContext.parallelize(data).toDF(columns)

# Create temporary view
sampleDF.createOrReplaceTempView("sampleView")

# Create a Database CT
spark.sql("CREATE DATABASE IF NOT EXISTS ct")

# Create a Table naming as sampleTable under CT database.
spark.sql("CREATE TABLE ct.sampleTable (id Int, name String, age Int, gender String)")

# Insert into sampleTable using the sampleView. 
spark.sql("INSERT INTO TABLE ct.sampleTable  SELECT * FROM sampleView")

# Lets view the data in the table
spark.sql("SELECT * FROM ct.sampleTable").show()

    Running the above PySpark application I got the following error.

    AnalysisException: Hive support is required to CREATE Hive TABLE (AS SELECT); 'CreateTable ct.sampleTable, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, ErrorIfExists

    <a name="h-why-are-we-getting-this-error"></a>

    Why are we getting this error?

    From Spark documentation & https://stackoverflow.com/ I found the following explanation, hope this helps you understanding.

    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:

    Solution

    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 a SparkSession object

    As suggested by others I have also tried setting the spark.sql.catalogImplementation property to hive while creating PySpark SparkSession, but the problem is not solved.

    
# Create spark session with hive enabled
spark = SparkSession \
    .builder \
    .appName("AIStudyLabs.com") \
    .config("spark.sql.warehouse.dir", warehouse_location) \
    .config("spark.sql.catalogImplementation", "hive") \
    .enableHiveSupport() \
    .getOrCreate()

# tried the below
spark.conf.set("spark.sql.catalogImplementation", "hive")

    Finally setting the same property --conf spark.sql.catalogImplementation=hive to spark-submit the problem is solved.

    
spark-submit --deploy-mode cluster 
             --master yarn 
             --conf spark.sql.catalogImplementation=hive
             .........
             ........

    Or you can also configure it for all Spark jobs by setting the property in /etc/spark/conf/spark-defaults.conf file.

    I hope you are able to solve the error AnalysisException: Hive support is required to CREATE Hive TABLE (AS SELECT);

    Happy Learning !!

    <a name="h-related-articles"></a>

    Related Articles