SQL to Spark Dataframe

[ChristianRRL]

Hi there,

Have a simple question. Not sure if Databricks supports this, but I'm wondering if there's a way to store the results of a sql cell into a spark dataframe? Or vice-versa, is there a way to take a sql query in python (saved as a string variable) and run the same exact query in a sql cell.

[menotron]

Hi @ChristianRRL, results of sql cell are automatically made available as a python dataframe using the _sqldf variable. You can read more about it here.

For the second part not sure why you would need it when you can simply run the query like:

spark.sql(qry)

But in case you want your query in sql cell or part of it to be set outside, you can explore query-parameters. And to achieve what you have mentioned, you could do either of the following.

Using parameter:

%py
query_text1 = "your query"
spark.sql(f"set param.query={query_text1}")

%sql
${param.query}

 Or Using Widgets:

%py
query_text2 = "your query"
dbutils.widgets.text("query", query_text2)

%sql
${query}