topic How to execute SQL statement using terraform in Data Engineering

How to execute SQL statement using terraform

chethankumar — Thu, 21 Nov 2024 18:56:59 GMT

Is there a way to execute SQL statements using Terraform
I can see it can be possible using API as bellow,
https://docs.databricks.com/api/workspace/statementexecution/executestatement

but I want to know is a strength way to run like bellow code

provider "databricks" {

host = var.databricks_host

token = var.databricks_token

}

resource "databricks_sql_query" "example" {

name = "example-query"

data_source_id = var.data_source_id

query = "SELECT * FROM your_table"

}

resource "local_file" "query_result" {

content = jsonencode(databricks_sql_query.example.output)

filename = "${path.module}/query_result.json"

}

Re: How to execute SQL statement using terraform

Louis_Frolio — Fri, 22 Nov 2024 15:19:48 GMT

I am not an expert here but some quick research it appears that you can execute SQL via Terraform but you need to utilize the Databricks Terraform provider and configure resources that support SQL execution. Terraform itself does not directly support SQL; however, it can provision resources like clusters or sql warehouses to run the sql.

You can leverage the SQL Perm resource:

resource "databricks_sql_permissions" "example_table" { table = "example_table" privilege_assignments { principal = "user@example.com" privileges = ["SELECT", "MODIFY"] } }

You can also schedule the SQL via a job, SDK, or API.

Cheers, Louis.

Re: How to execute SQL statement using terraform

Nes_Hdr — Fri, 22 Nov 2024 15:55:03 GMT

I was having the same question a while ago, and I couldn't find a way to automatically execute the query using terraform. What you can do though, is to set a schedule if the query needs to be executed rather regularly, or simply execute it manually in the UI if you need to execute it only once ...

Let me know if you find another solution 🙂

Re: How to execute SQL statement using terraform

chethankumar — Thu, 05 Dec 2024 09:34:52 GMT

I have used the bellow provider to run the query
https://registry.terraform.io/providers/hashicorp/http/latest

Re: How to execute SQL statement using terraform

KartikeyaJain — Wed, 09 Apr 2025 04:40:44 GMT

The official Databricks provider in Terraform only allows you to create SQL queries, not execute them. To actually run queries, you can either:

Use the http provider to make API calls to the Databricks REST API to execute SQL queries.
Alternatively, if you're using a Service Principal with a Client Secret, you can configure another http provider to obtain an access token via OIDC, which can then be used in place of a PAT token for authentication.