Forum Discussion
How to run custom SQL queries in Azure Databricks without using ODBC in Power BI?
Hi everyone,
I'm using the native Azure Databricks connector in Power BI with this setup:
Databricks.Catalogs("adb-xxxx.azuredatabricks.net", "/sql/1.0/warehouses/xxxx", [Catalog = "dtXXXX", Database = "scorecards", EnableAutomaticProxyDiscovery = "disabled"])
I can successfully connect and access views like this:
Fonte{[Item="vw_table", Schema="scorecards", Catalog="dtXXXX"]}[Data]
However, I need to run a custom SQL query, like:
SELECT * FROM vw_table
I tried placing the SQL inside the Item field as a subquery:
Item = "(SELECT * FROM vw_table)"
But I get this error:
Expression.Error: The key did not match any rows in the table.
From what I understand, the Databricks.Catalogs connector doesn't support direct SQL or Value.NativeQuery.
Is there any way to run custom SQL queries using this native connector, without switching to ODBC?
I've read the documentation several times, but I still can't succeed in collecting data using SQL scripts through this connector.
Thanks in advance!
Hi everyone, thank you all for your contributions!
I wanted to share that I was able to solve the issue on my own. I had already read the documentation several times (including the official blog post), but I couldn’t get it to work in my specific context — especially through a script-based setup in Power Query.
In the end, I found a solution that worked by applying a native SQL query using this structure:
Fonte = Value.NativeQuery(
Databricks.Catalogs(
"adb-xxxx.azuredatabricks.net",
"/sql/1.0/warehouses/xxxx",
[Catalog = "dtXXXX", Database = null, EnableAutomaticProxyDiscovery = "enabled"]
){[Name="dtXXXX", Kind="Database"]}[Data],
"
SELECT * FROM your_table
WHERE my_column IS NOT NULL
"
)
This allowed me to run the SQL script directly without needing to rely on ODBC or predefined views.I’ll go ahead and mark the thread as resolved — though I won’t select any specific reply, since I was looking specifically for a script-based solution that I ended up crafting myself.
Thanks again to everyone who contributed!
6 Replies
Hi WILLIAM_AFFONSO,
The Databricks Power BI connector supports native query by following the steps posted in this Databricks blog:
https://www.databricks.com/blog/databricks-power-bi-connector-now-supports-native-query
Please let me know if following this guide works.If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
Since you mentioned that you have read the documentation, could you please check if this helps?
It seems that the anonymous user changed Databricks.Catalog to Databricks.Query to make it work, although the error is not the same as in the original post.
Hi WILLIAM_AFFONSO ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.Hi WILLIAM_AFFONSO ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra. Hi everyone, thank you all for your contributions!
I wanted to share that I was able to solve the issue on my own. I had already read the documentation several times (including the official blog post), but I couldn’t get it to work in my specific context — especially through a script-based setup in Power Query.
In the end, I found a solution that worked by applying a native SQL query using this structure:
Fonte = Value.NativeQuery(
Databricks.Catalogs(
"adb-xxxx.azuredatabricks.net",
"/sql/1.0/warehouses/xxxx",
[Catalog = "dtXXXX", Database = null, EnableAutomaticProxyDiscovery = "enabled"]
){[Name="dtXXXX", Kind="Database"]}[Data],
"
SELECT * FROM your_table
WHERE my_column IS NOT NULL
"
)
This allowed me to run the SQL script directly without needing to rely on ODBC or predefined views.I’ll go ahead and mark the thread as resolved — though I won’t select any specific reply, since I was looking specifically for a script-based solution that I ended up crafting myself.
Thanks again to everyone who contributed!
Hi WILLIAM_AFFONSO ,
Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept your own post as the solution, this will help other community members who might face a similar issue.
Thanks again for your contribution!
