Spark: Create temporary table by executing sql query on temporary tables

Question

I am using Spark and I would like to know: how to create temporary table named C by executing sql query on tables A and B ?

sqlContext
   .read.json(file_name_A)
   .createOrReplaceTempView("A")

sqlContext
   .read.json(file_name_B)
   .createOrReplaceTempView("B")

val tableQuery = "(SELECT A.id, B.name FROM A INNER JOIN B ON A.id = B.fk_id) C"

sqlContext.read
   .format(SQLUtils.FORMAT_JDBC)
   .options(SQLUtils.CONFIG())
   .option("dbtable", tableQuery)
   .load()

flurdy · Accepted Answer · 2021-07-01 10:13:16Z

8

You need to save your results as temp table

tableQuery .createOrReplaceTempView("dbtable")

Permanant storage on external table you can use JDBC

val prop = new java.util.Properties
prop.setProperty("driver", "com.mysql.jdbc.Driver")
prop.setProperty("user", "vaquar")
prop.setProperty("password", "khan") 
 
//jdbc mysql url - destination database is named "temp"
val url = "jdbc:mysql://localhost:3306/temp"
 
//destination database table 
val dbtable = "sample_data_table"
 
//write data from spark dataframe to database
df.write.mode("append").jdbc(url, dbtable, prop)

https://docs.databricks.com/spark/latest/data-sources/sql-databases.html

http://spark.apache.org/docs/latest/sql-programming-guide.html#saving-to-persistent-tables

edited Jul 1, 2021 at 10:13

flurdy

3,97231 silver badges31 bronze badges

answered Jun 20, 2018 at 16:33

vaquar khan

11.5k7 gold badges81 silver badges103 bronze badges

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

Comments

Chandan Ray · Accepted Answer · 2018-06-20 17:44:12Z

2

sqlContext.read.json(file_name_A).createOrReplaceTempView("A")
sqlContext.read.json(file_name_B).createOrReplaceTempView("B")
val tableQuery = "(SELECT A.id, B.name FROM A INNER JOIN B ON A.id = B.fk_id) C"
sqlContext.sql(tableQuery).createOrReplaceTempView("C")

Try the above code it will work.

answered Jun 20, 2018 at 17:44

Chandan Ray

2,0911 gold badge13 silver badges16 bronze badges

1 Comment

Theophile Champion Over a year ago

Your answer is working, unfortunatly I can not use sqlContext.sql since it will not execute the entire request in the database. Indeed, sqlContext.sql can split the query into two select and then execute the join on diferrents worker. In my case, the workers have not enough memory to handle it and they crash...

Collectives™ on Stack Overflow

Spark: Create temporary table by executing sql query on temporary tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related