Spark Java append data to Hive table

Question

I'm facing some problem when trying to append data to an hive table. I declared the session correctly the session: I can retrieve data from the table

SparkSession spark = SparkSession
                .builder()
                .appName("Java Spark SQL basic example")
                .config("hive.metastore.uris", "thrift://localhost:9083")

                .enableHiveSupport()
                .master("local[*]")
                .getOrCreate();

When trying to append some data using df.write().mode(SaveMode.Append).saveAsTable("sample.test_table"); I'm getting

Exception in thread "main" java.lang.ClassNotFoundException: Failed to find data source: hive. Please find packages at http://spark.apache.org/third-party-projects.html

What I'm missing?

EDIT: Using df.write().insertInto("prova2.test_table"); works, no idea how it works

What are the spark and hive versions you are using? – Pubudu Sitinamaluwa Jul 10 '21 at 10:39 — Pubudu Sitinamaluwa, Jul 10 '21 at 10:39

Pubudu Sitinamaluwa · Accepted Answer · 2021-07-10T11:09:55.130

1

saveAsTable() seems to not work as you intend. Try this instead,

Register a temp table

df.registerTempTable("sample.temptable")

Create and insert data if the table does not exitsts already,

sqlContext.sql("CREATE TABLE IF NOT EXISTS sample.test_table as select * from sample.temptable")

OR simpty create it without inserting any value so that you can rerun the code without any exceptions

sqlContext.sql("CREATE TABLE IF NOT EXISTS sample.test_table")

Insert data (Table should exist)

sqlContext.sql("insert into table sample.test_table select * from sample.temptable")

Drop temp table

sqlContext.sql("DROP TABLE IF EXISTS sample.temptable")

Read more on temporary table usage

edited Jul 10 '21 at 11:09

answered Jul 10 '21 at 11:04

Pubudu Sitinamaluwa

958
7
21

Thanks, sounds a little bit strange but it works! – Roberto Bressani Jul 10 '21 at 15:18
Indeed. Glad it helped. – Pubudu Sitinamaluwa Jul 10 '21 at 15:19
By chance, do you know which is the rational behind it? – Roberto Bressani Jul 10 '21 at 15:26
I'm not sure but I guess what `saveAsTable()` is trying to do is to create a new table. Since the table already exists it might be failing to do so. I think it's something similar with `insertInto()` too. Check this out. https://community.cloudera.com/t5/Support-Questions/Inserting-data-from-a-dataframe-to-an-existing-Hive-Table/td-p/61543 – Pubudu Sitinamaluwa Jul 10 '21 at 15:35
Thanks, `insertInto()` unluckily has some peculiar behaviour with column position... – Roberto Bressani Jul 10 '21 at 16:08

Spark Java append data to Hive table

1 Answers1