Spark / Python - create table using hive fails with ParseException

user_9020512 0
Viewed 549

Trying to create a table in python from spark using hive fails with ParseException

 Cannot create hive serde table

Running on Hortonworks HDP 2.6

Code is

warehouse_location = abspath('spark-warehouse') 

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

# spark is an existing SparkSession
spark.sql("CREATE TABLE IF NOT EXISTS tom (key INT, value STRING) USING hive")

Error generated is: 

 INFO SparkSqlParser: Parsing command: CREATE TABLE IF NOT EXISTS tom (key INT, value STRING) USING hive
Traceback (most recent call last):
  File "/usr/repos/dataconnect/model/create_model.py", line 17, in <module>
    spark.sql("CREATE TABLE IF NOT EXISTS tom (key INT, value STRING) USING hive")
  File "/usr/hdp/2.6.1.0-129/spark2/python/lib/pyspark.zip/pyspark/sql/session.py", line 545, in sql
  File "/usr/hdp/2.6.1.0-129/spark2/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py", line 1133, in __call__
  File "/usr/hdp/2.6.1.0-129/spark2/python/lib/pyspark.zip/pyspark/sql/utils.py", line 73, in deco
pyspark.sql.utils.ParseException: u'\nCannot create hive serde table with CREATE TABLE USING\n== SQL ==\nCREATE TABLE IF NOT EXISTS tom (key INT, value STRING) USING hive'
apache-spark hive
2 Answers
scala> hiveContext.sql("CREATE TABLE IF NOT EXISTS tom (key INT, value STRING) row format delimited fields terminated by ','")

res157: org.apache.spark.sql.DataFrame = []


scala> hiveContext.sql("select * from tom");

res158: org.apache.spark.sql.DataFrame = [key: int, value: string]

scala> hiveContext.sql("select * from tom").show()
+---+-----+
|key|value|
+---+-----+
user_65849030

Just remove the "USING hive". So the command becomes -

spark.sql("CREATE TABLE IF NOT EXISTS spark_hive_table (key INT, value STRING)")

This command creates table in Hive. (I changed the table name to spark_hive_table, you can use your own)

user_23828030
Related