cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Trigger queries to SQL warehouse from Databricks notebook

sathya08
New Contributor III

Hello,

 

I am trying to explore triggering for sql queries from Databricks notebook to serverless sql warehouse along with nest-asyncio module.

Both the above are very new for me and need help on the same.

For triggering the API from notebook, I am using the link to avoid the authentication issues, w.queries: Queries โ€” Databricks SDK for Python beta documentation

When I try to execute the query, it says, AttributeError: module 'databricks.sdk.service.sql' has no attribute 'CreateQueryRequestQuery'.

Can anyone shed some light with an example if you have tried this option.

Also let me know if any better option available.

Thanks

Sathya

1 ACCEPTED SOLUTION

Accepted Solutions

ameet9257
New Contributor III

Hi @sathya08,

Maybe you can try the below one. 

I can execute the query and get the output without authenticating to the SQL Warehouse cluster. 

 

from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql
import time
w = WorkspaceClient(
host = "https://*************.azuredatabricks.net/",
token = "********************",
)

exe_stt = w.statement_execution.execute_statement(statement='SHOW TABLES',warehouse_id='*************',catalog='*****',schema='*****')
#print(exe_stt.statement_id)
get_statement = w.statement_execution.get_statement(statement_id=exe_stt.statement_id)
#print(get_statement)
print(get_statement.result.data_array)
 
Thanks,
Amit
Amit Prajapati

View solution in original post

9 REPLIES 9

VZLA
Databricks Employee
Databricks Employee

Hi @sathya08 ,

Are you using Databricks SDK version v0.28.0 or older? I believe the CreateQueryRequestQuery was only added in recent releases (v0.28.0+), so older versions would fail with such error. Can you please try upgrading the sdk?

Check the current version:

%pip show databricks-sdk | grep -oP '(?<=Version: )\S+'
Then upgrade:
%pip install --upgrade databricks-sdk
Check and confirm you have the most recent version available (0.36.0 if I'm not wrong):
%pip show databricks-sdk | grep -oP '(?<=Version: )\S+'

sathya08
New Contributor III

Yes, thanks, the error resolved after the upgrade. I have trying to execute_statement from notebook to the sql warehouse as below,

from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql
import time
w = WorkspaceClient()
srcs = w.data_sources.list()
s = sql.StatementExecutionAPI(api_client=sql)
s.execute_statement(statement='SHOW TABLES',warehouse_id='XXXXXX',catalog='hello',schema='silver')
 
 but I am getting an error ==>  AttributeError: module 'databricks.sdk.service.sql' has no attribute 'do'.
Upon checking the sdk document, it has ' res = self._api.do('POST', '/api/2.0/sql/statements/', body=body, headers=headers)
return StatementResponse.from_dict(res)'
 
I am not sure how to give the parameters to execute.

sathya08
New Contributor III

can anyone help here.

Thanks

Sathya

ameet9257
New Contributor III

Hi @sathya08 

I understood that you want to submit an SQL query from a notebook to a serverless cluster.

Maybe you can try the below approach to achieve this. 

You need to install the below package: 

%pip install databricks-sql-connector

import os
from databricks import sql
catalog = 'default'
schema_name = 'test'
table_name = "trips"
with sql.connect(server_hostname = "**************.azuredatabricks.net",
http_path = "/sql/1.0/warehouses/*********",
access_token = "*****************") as connection:
 
cursor = connection.cursor()

cursor.execute("show tables")
print(cursor.fetchall())

cursor.execute(f"SELECT * FROM {catalog}.{schema_name}.{table_name} LIMIT 2")
result = cursor.fetchall()

cursor.close()
connection.close()
 
Let me know if it's helping you. 
Thanks
Amit Prajapati

sathya08
New Contributor III

Hi Amit, Thanks for your reply and the steps. I have already tried this option and its working. The main reason for me to go to sdk was to avoid the authentication part.

Would you be able to help in with the understanding on the *do* part of the function which is erroring out.

Thanks again for your help

Sathya

ameet9257
New Contributor III

Hi @sathya08,

Maybe you can try the below one. 

I can execute the query and get the output without authenticating to the SQL Warehouse cluster. 

 

from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql
import time
w = WorkspaceClient(
host = "https://*************.azuredatabricks.net/",
token = "********************",
)

exe_stt = w.statement_execution.execute_statement(statement='SHOW TABLES',warehouse_id='*************',catalog='*****',schema='*****')
#print(exe_stt.statement_id)
get_statement = w.statement_execution.get_statement(statement_id=exe_stt.statement_id)
#print(get_statement)
print(get_statement.result.data_array)
 
Thanks,
Amit
Amit Prajapati

sathya08
New Contributor III

Thanks Amit,

I have a dumb question to ask, since I am mentioning the token is that not part of authentication ? because you mentioned that this is without the authentication. please clarify.

Another one, how can I make sure that this query runs on sql warehouse, When I check the Queries tab in databricks, I don't see any new query executed.

Thanks so much for your help

Sathya

ameet9257
New Contributor III

Hi @sathya08,

I'm not re-authenticating to the SQL server serverless for the authentication, but a one-time authentication to the workspace is required in my case. Without credentials, I'm getting below error. 

default auth: cannot configure default credentials

Looks like, if we don't provide any credential details it will search for the default credential set at my profile level.

For the query execution log, you can check in the Query History tab. Make sure you remove the filter from the user to see the complete logs. 

ameet9257_0-1731565132833.png

Thanks

Amit Prajapati

sathya08
New Contributor III

Thankyou, it really helped.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group