Skip to content

Using createOrReplaceTempView to replace a temp view not working as expected

I have a dataset something similar to this

{"name": "Michael", "age": "30", "producta1": "blah1", "producta3": "blah2"}
{"name": "Michael", "age": "31", "producta1": "blah3", "producta3": "blah4"}
{"name": "Michael", "age": "30", "producta1": "blah5", "producta3": "blah6"}
{"name": "Andy", "age": "28", "producta1": "blah5", "producta3": "blah6"}
{"name": "Andy", "age": "28", "producta1": "blah5", "producta3": "blah6"}
{"name": "Andy", "age": "28", "producta1": "blah5", "producta3": "blah6"}
{"name": "Justin", "age": "12", "producta1": "blah5", "producta3": "blah6"}
{"name": "Justin", "age": "12", "producta1": "blah5", "producta3": "blah6"}

My spark code is

SparkSession sc = SparkSession.builder().appName("example app").config("spark.master","local").getOrCreate();

Dataset<Row> df = sc.read().json("/Users/g.bhageshpur/Downloads/spark-master/examples/src/main/examples/src/main/resources/people.json");

df.createOrReplaceTempView("people");

Dataset<Row> dfpeople = sc.sql("select * from people where name='Michael'");

dfpeople.createOrReplaceTempView("people");

I am trying to replace the people view by calling createOrReplaceTempView

but i get the following error as below

Recursive view `people` detected (cycle: `people` -> `people`)

How do I replace the view in spark?

Answer

So I got the solution to the above question by the following code

Dataset<Row> df = sc.read().json("/Users/g.bhageshpur/Downloads/spark-master/examples/src/main/examples/src/main/resources/people.json");

df.createOrReplaceTempView("people");

Dataset<Row> dfpeople = sc.sql("select * from people where name='Michael'");

sc.sqlContext().dropTempTable("people");

dfpeople.createOrReplaceTempView("people");