Error While Writing into a Hive table from Spark Sql

Question

I am trying to insert data into a Hive External table from Spark Sql. I am created the hive external table through the following command

CREATE EXTERNAL TABLE  tab1 ( col1 type,col2 type ,col3 type) CLUSTERED BY (col1,col2) SORTED BY (col1) INTO 8 BUCKETS STORED AS PARQUET

In my spark job , I have written the following code Dataset df = session.read().option("header","true").csv(csvInput);

df.repartition(numBuckets, somecol)
                  .write()
                  .format("parquet")
                  .bucketBy(numBuckets,col1,col2)
                  .sortBy(col1)
                  .saveAsTable(hiveTableName);

Each time I am running this code I am getting the following exception

org.apache.spark.sql.AnalysisException: Table `tab1` already exists.;
    at org.apache.spark.sql.DataFrameWriter.saveAsTable(DataFrameWriter.scala:408)
    at org.apache.spark.sql.DataFrameWriter.saveAsTable(DataFrameWriter.scala:393)
    at somepackage.Parquet_Read_WriteNew.writeToParquetHiveMetastore(Parquet_Read_WriteNew.java:100)

Answer 1

You are using the saveAsTable API, which create the table into Hive. Since you have already created the hive table through command, the table tab1 already exists. so when Spark API trying to create it, it throws error saying table already exists, org.apache.spark.sql.AnalysisException: Tabletab1already exists.

Either drop the table and let spark API saveAsTable create the table itself. Or use the API insertInto to insert into an existing hive table.

df.repartition(numBuckets, somecol)
                  .write()
                  .format("parquet")
                  .bucketBy(numBuckets,col1,col2)
                  .sortBy(col1)
                  .insertInto(hiveTableName);