How to convert sql table into a pyspark/python data structure and return back to sql in databricks notebook

Question

I am running a sql notebook on databricks. I would like to analyze a table with half a billion records in it. I can run simple sql queries on the data. However, I need to change the date column type from str to date.

Unfortunately, update/alter statements do not seem to be supported by sparkSQL so it seems I cannot modify the data in the table.

What would be the one-line of code that would allow me to convert the SQL table to a python data structure (in pyspark) in the next cell? Then I could modify the file and return it to SQL.

David · Accepted Answer · 2016-08-19 19:25:11Z

5

dataFrame = sqlContext.sql('select * from myTable')

answered Aug 19, 2016 at 19:25

David

11.6k4 gold badges44 silver badges46 bronze badges

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

2 Comments

Semihcan Doken Over a year ago

Thanks! And How would I return it back to sql so I can go back to querying it in sql in the next cell? Probably also one line. Is it something like dataFrame.to_sql (Have no clue. Just made it up to give you an idea of what I mean)

David Over a year ago

@Semihcan, you want the registerTempTable function spark.apache.org/docs/latest/…

Nick · Accepted Answer · 2019-01-06 01:46:18Z

1

df=sqlContext.sql("select * from table")

To convert dataframe back to sql view,

df.createOrReplaceTempView("myview")

edited Jan 6, 2019 at 1:46

Nick

147k23 gold badges67 silver badges106 bronze badges

answered Jan 6, 2019 at 1:23

srikanth holur

7804 silver badges11 bronze badges

Comments

Kawalpreet Singh · Accepted Answer · 2025-04-24 13:05:52Z

0

# Read from SQL table

df = spark.read.table("your_database.source_table")

# Transform: filter age > 25

df_filtered = df.filter(df.age > 25).select("name", "age")

# Write to new SQL table

df_filtered.write.mode("overwrite").saveAsTable("your_database.filtered_table")

answered Apr 24 at 13:05

Kawalpreet Singh

1

1 Comment

Community Apr 24 at 13:50

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to convert sql table into a pyspark/python data structure and return back to sql in databricks notebook

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related