Is there a way to run Databricks query on vba?

[HDW_14]

Currently, I just manually copy paste ​a code from an excel sheet and paste it on a databricks notebook and run for results, then, copy paste the results to the same workbook. I'm sure there's a faster way to do it. The only solutions i can find is using the query wizard but the issue is that the data is so large and the query changes everytime too. I've already connected the databricks database to my workbook

I can easily run sql on vba with ADO connection and paste the result but I have no clue how to do it for databricks.

I'm still fairly new to databrick so I welcome any advice coming my way. Thanks!​

[Anonymous · Accepted solution]

@Hanna Wade​ : Yes, it is possible to run Databricks queries from VBA by using the Databricks REST API. To run a Databricks query from VBA, you would need to use VBA's HTTP request library to send a POST request to the Databricks REST API endpoint that executes the query. You would also need to authenticate with Databricks using an access token or personal access token.

Sub RunDatabricksQuery()
 
    Dim apiUrl As String
    Dim accessToken As String
    Dim requestBody As String
    Dim http As New XMLHTTP60
    Dim responseText As String
    
    ' Set the Databricks API URL and access token
    apiUrl = "https://<databricks-instance>/api/2.0/sql/endpoints/<endpoint-id>/query"
    accessToken = "<access-token>"
    
    ' Set the query to be executed
    requestBody = "{""query"":""SELECT * FROM my_table""}"
    
    ' Send the POST request to execute the query
    http.Open "POST", apiUrl, False
    http.setRequestHeader "Authorization", "Bearer " & accessToken
    http.setRequestHeader "Content-Type", "application/json"
    http.send requestBody
    
    ' Retrieve the query results
    responseText = http.responseText
    
    ' Do something with the query results
    Debug.Print responseText
    
End Sub

Please refer the link: https://docs.databricks.com/dev-tools/api/index.html

[Anonymous]

Hi @Hanna Wade​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!