topic Re: sql query is not returning _sqldf. in Data Engineering

sql query is not returning _sqldf.

Somia — Wed, 05 Feb 2025 14:42:24 GMT

Notebooks in my workspace are not returning _sqldf when a sql query is run.

If I run this code, it would give an error in second cell that _sqldf is not defined.

First Cell:

%sql

select * from some_table limit 10

Second Cell:

%sql

select * from _sqldf

However, the same code runs fine in other people's notebook in my organization. I suspect it started when I connected my notebook with sql warehouse as my whole notebook was in sql rather than an all purpose compute. Now I cannot run this above code even when I make a new notebook.

Can anybody suggest how to fix this.

Re: sql query is not returning _sqldf.

Alberto_Umana — Wed, 05 Feb 2025 14:53:34 GMT

Hi @Somia,

If you run that function in all-purpose does it work fine?

Re: sql query is not returning _sqldf.

Somia — Thu, 06 Feb 2025 17:32:31 GMT

I switched my notebook back to all purpose compute as _sqldf is not supported with sql warehouse notebook. It didn't work as explained above.

Re: sql query is not returning _sqldf.

MadhuB — Thu, 06 Feb 2025 20:33:36 GMT

@Somia

To replicate your scenario, you need an All-Purpose Cluster and a notebook defaulted to python language. Then query a table using %sql as below. This creates a temp dataframe for you to use it in the python cells. Keep in mind, that this dataframe keep changing as you execute a different %sql cell.

%sql -- cell 1 select * from catalog.schema.123_sample

# cell 2 display(_sqldf)

To summarize, the %sql magic command behaves differently depending on whether your Databricks notebook is connected to an All-Purpose cluster or a SQL Warehouse.

All-Purpose Cluster: %sql creates a DataFrame named _sqldf that you can use in subsequent Python cells.
SQL Warehouse: %sql executes the query but does not create the _sqldf DataFrame.

Please let me know for anything, else mark it as a solution.

Re: sql query is not returning _sqldf.

JakubSkibicki — Thu, 06 Feb 2025 22:19:32 GMT

@Somia _sqldf is a pyspark df not a sql object.

It works only for such direction

exec sql -> call _sqldf in pyspark

Re: sql query is not returning _sqldf.

Somia — Fri, 07 Feb 2025 13:41:51 GMT

Changing the notebook to default python and all purpose compute have fixed the issue. I am able to access _sqldf in subsequent sql or python cell.

Re: sql query is not returning _sqldf.

MadhuB — Fri, 07 Feb 2025 18:07:28 GMT

@Somia Can you mark my detailed explanation as solution that helped to resolve your issue.

Re: sql query is not returning _sqldf.

pdiamond — Mon, 07 Jul 2025 14:45:54 GMT

Is there any equivalent to _sqldf for SQL Warehouses?