topic Re: Databricks SQL Connector in Data Engineering

Databricks SQL Connector

BorislavBlagoev — Thu, 16 Dec 2021 16:02:03 GMT

I started to explore Databricks SQL Connector. And I'm wondering can I execute query from Databricks SQL Connector from notebook or job?

sample code:

'test_query' lives in Databricks SQL. It is ready to use query!

from databricks import sql
 
hostname = '<name>.databricks.com'
http_path = '/sql/1.0/endpoints/<endpoint_id>'
access_token = '<personal_token>'
 
connection = sql.connect(server_hostname=hostname, http_path=http_path, access_token=access_token)
 
cursor = connection.cursor()
 
cursor.execute('test_query')
 
result = cursor.fetchall()
 
for row in result:
  print(row)
 
cursor.close()

Re: Databricks SQL Connector

Hubert-Dudek — Thu, 16 Dec 2021 16:09:53 GMT

yes you can as it is just python but in notebook you can use native SQL commands (of course there can be situation that you have some really big sql enpoint (like 64 DBU) so it can make sense in some situations but I don't think much and it creates complicated architecture)

Re: Databricks SQL Connector

BorislavBlagoev — Thu, 16 Dec 2021 16:13:39 GMT

Than that make more sense to use that in job? And what is the big benefit of Databricks SQL at all? I mean what is the main usage of Databricks SQL?

Re: Databricks SQL Connector

Hubert-Dudek — Thu, 16 Dec 2021 16:15:54 GMT

except nice UI and cool features there 😉 for me biggest advantage is that from PowerBI and Qlikview I can query data using SQL and use serverless SQL endpoint.

Re: Databricks SQL Connector

BorislavBlagoev — Thu, 16 Dec 2021 16:19:55 GMT

That means there is no advantages if I use Databricks SQL in my job

Re: Databricks SQL Connector

Hubert-Dudek — Thu, 16 Dec 2021 16:24:32 GMT

I would use just native SQL syntax in job but maybe there is some nice trick...

Re: Databricks SQL Connector

BorislavBlagoev — Thu, 16 Dec 2021 16:25:07 GMT

Thank you!

Re: Databricks SQL Connector

Anonymous — Thu, 16 Dec 2021 19:45:41 GMT

@Borislav Blagoev - Would you be happy to mark which of @Hubert Dudek's answers offered the best resolution as best?

Re: Databricks SQL Connector

BorislavBlagoev — Thu, 16 Dec 2021 21:26:29 GMT

I can't understand your question

Re: Databricks SQL Connector

BorislavBlagoev — Fri, 17 Dec 2021 16:14:45 GMT

@Hubert Dudek

Error during OpenSession; Request: TOpenSessionReq(client_protocol=5, username=None, password=None, configuration=None) Error: None Bounded-Retry-Delay: None Attempt: 1/30 Elapsed-Seconds: 1.3954582214355469/900.0
OperationalError: ('', EOFError())

I get the following error when I try to execute the code above. Do you know something about this?

Re: Databricks SQL Connector

Anonymous — Fri, 17 Dec 2021 16:18:02 GMT

@Borislav Blagoev - There is a "Select as Best" link underneath each reply. All you need to do, if you choose, is to click on that link for whichever reply best answered your question. The purpose is to help other members find helpful solutions more easily.

I'm attaching a screenshot to help.

Re: Databricks SQL Connector

BorislavBlagoev — Fri, 17 Dec 2021 17:01:43 GMT

Done!