How to convert sql output to Dataframe?

Question

I have a Dataframe, from which a create a temporary view in order to run sql queries. After a couple of sql queries, I'd like to convert the output of sql query to a new Dataframe. The reason I want data back in Dataframe is so that I can save it to blob storage.

So, the question is: what is the proper way to convert sql query output to Dataframe?

Here's the code I have so far:

%scala
//read data from Azure blob
...
var df = spark.read.parquet(some_path)

// create temp view
df.createOrReplaceTempView("data_sample")

%sql
//have some sqlqueries, the one below is just an example
SELECT
   date,
   count(*) as cnt
FROM
   data_sample
GROUP BY
   date

//Now I want to have a dataframe  that has the above sql output. How to do that?
Preferably the code would be in python or scala.

Luiz Viola · Accepted Answer · 2022-07-20 13:52:16Z

7

Scala:

var df = spark.sql(s"""
SELECT
   date,
   count(*) as cnt
FROM
   data_sample
GROUP BY
   date
""")

PySpark:

df = spark.sql(f'''
SELECT
   date,
   count(*) as cnt
FROM
   data_sample
GROUP BY
   date
''')

edited Jul 20, 2022 at 13:52

answered Jul 20, 2022 at 13:40

Luiz Viola

2,4642 gold badges17 silver badges36 bronze badges

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

3 Comments

Alcibiades Over a year ago

Can the sql string be parametrized? Something like f’’’ SELECT date + {timezone} as date, … ‘’’ where timezone is a parameter?

Luiz Viola Over a year ago

yes, like f'''SELECT date, {timezone} from ... '''

Luiz Viola Over a year ago

in PySpark: table = 'schema.my_table' df = spark.sql(f'''select * from {table}''')

Pavel Sinkevich · Accepted Answer · 2023-01-14 10:01:42Z

2

You can create temporary view in %%sql code, and then reference it from pyspark or scala code like this:

%sql
create temporary view sql_result as
SELECT ...

%scala
var df = spark.sql("SELECT * FROM sql_result")

answered Jan 14, 2023 at 10:01

Pavel Sinkevich

6895 silver badges13 bronze badges

Comments

Alex Ott · Accepted Answer · 2023-01-16 16:24:42Z

1

Since May 2022nd, results of SQL queries in the notebooks are available as _sqldf variable that is corresponding DataFrame object accessible from Python code. See documentation for more details & description of behavior.

answered Jan 16, 2023 at 16:24

Alex Ott

88.1k10 gold badges110 silver badges157 bronze badges

Collectives™ on Stack Overflow

How to convert sql output to Dataframe?

3 Answers 3

Scala:

PySpark:

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Scala:

PySpark:

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related