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: 

Is there a way to run Databricks query on vba?

HDW_14
New Contributor

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!​

1 ACCEPTED SOLUTION

Accepted Solutions

Anonymous
Not applicable

@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

View solution in original post

2 REPLIES 2

Anonymous
Not applicable

@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
Not applicable

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!

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!