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