Trigger queries to SQL warehouse from Databricks notebook

[sathya08]

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

[VZLA]

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]

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]

can anyone help here.

Thanks

Sathya

[ameet9257]

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

[sathya08]

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 · Accepted solution]

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/",

account_id = "https://accounts.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

[sathya08]

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]

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. 

Thanks

[sathya08]

Thankyou, it really helped.