Insert into TempView using Spark.sql

Question

How can I make a simple insert in Spark SQL ? spark 2.1

I am able to make it work with simple sql code inside spark, with Spark.sql but it is not possible for me to make just an insert.

  from pyspark.sql import SparkSession
  spark = SparkSession.builder.appName('Basics').getOrCreate()
  df=spark.read.json(/path/.'/people.json')

  df.sow()

  +-----+---------+   
  |age  | name    |
  +-----+---------+
  |null | Michael |
  | 30  | And     |
  +-----+---------+    

 df.CreateOrReplaceTempView('people') # create temp table

 spark.sql("SELECT * FROM people where age == 30")

  +-----+---------+   
  |age  | name    |
  +-----+---------+
  | 30  | Andy    |
  +-----+---------+

So I understand SQL but I dont know who to make an Insert.

I tried all the posibles ways I imagine.

As far as I know, it depends on the database you are writing too, as each has its own connector (an existing one or needs one to be written). Also, the answer may differ between Spark, Spark Direct Streaming and Spark Structures Streaming. P.S. To reply use "@DannyVarod" at the beginning of your comment. — Danny Varod
– Danny Varod, Commented Oct 24, 2018 at 16:02
@DannyVarod Thanks for answer, I m not using anydatabase. its a Dataframe that I convert in a table and then with spark.sql it allows to write sql code. works for "select" but I m trying for insert — Enrique Benito Casado
– Enrique Benito Casado, Commented Oct 24, 2018 at 16:09

Danny Varod · Accepted Answer · 2022-03-20 10:20:29Z

3

You don't insert into dataframes, they are immutable and lazy.

You need to create a new dataframe which is the union between the original dataframe and the new data you want to add to it.

edited Mar 20, 2022 at 10:20

answered Oct 24, 2018 at 16:18

Danny Varod

18.3k5 gold badges76 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert into TempView using Spark.sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related