Is it possible to insert into temporary table in spark?

user_11376186 0
Viewed 10851

I tested below queries using Databricks with Apache Spark 2.4: 

%sql

<step1>
create temporary view temp_view_t
as select 1 as no, 'aaa' as str;

<step2>
insert into temp_view_t values (2,'bbb');

And then I got this error message.

Error in SQL statement: AnalysisException: Inserting into an RDD-based table is not allowed.;; 'InsertIntoTable Project [1 AS no#824, aaa AS str#825], false, false +- LocalRelation [col1#831, col2#832]

My questions are

  1. Is it impossible to insert into temporary table in spark?
  2. How can I work for creating temporary data in spark sql?

Thank you.

apache-spark temporary
3 Answers

We can't insert data into the temporary table but we can mimic the insert with union all (or) union(to remove duplicates).

Example:

#create temp view
spark.sql("""create or replace temporary view temp_view_t as select 1 as no, 'aaa' as str""")

spark.sql("select * from temp_view_t").show()
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#+---+---+

#union all with the new data
spark.sql("""create or replace temporary view temp_view_t as select * from temp_view_t union all select 2 as no, 'bbb' as str""")

spark.sql("select * from temp_view_t").show()                                                                     
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#|  2|bbb|
#+---+---+

#to eliminate duplicates we can use union also. 
spark.sql("""create or replace temporary view temp_view_t as select * from temp_view_t union select 1 as no, 'aaa' as str""")

spark.sql("select * from temp_view_t").show()
#+---+---+
#| no|str|
#+---+---+
#|  1|aaa|
#|  2|bbb|
#+---+---+
user_76326950

I don't think the answer advising to do UNION works (on recent Databricks runtime at least, 8.2 spark runtime 3.1.1), a recursive view is detected at the execution. Example of the code above gives :

AnalysisException: Recursive view `temp_view_t` detected (cycle: `temp_view_t` -> `temp_view_t`)

Which seems pretty logic. Why not use some intermediary temp view or to write the view as a managed Delta table, this way it will handle INSERT operations.

user_89714010

Yes you can insert into temp view but it must be based on df build from file. Then new row will be saved as separte file on storage.

For instance

df.read.parquet(path).createOrReplaceTempView('temp')

spark.sql("INSERT INTO temp VALUES (....)")
user_158642200
Related