databricks
Feature Request: Please add SQL Query and SQL Alert support to Asset Bundle
Hi, please add SQL query and SQL alert support to the asset bundle in addition to the job, and pipeline type.
With sql_task, it makes more sense to use alert creation and query creation. The asset bundle provides the best DEVOPS experience while keeping everything in source control.
Thanks for creating the issue.
We do not plan to add these in the next ~2 months, but we will consider it beyond that.
Let's leave the issue open to gather +1s from other folks who would like to see this happen.
Frequently we build (materialized) views in conjunction with jobs, and the refresh cycles are easiest to set up if this is done in the same repo and coordinated. I would, if I could, write idempotent SQL queries for the views, and I'd like to use sql_task syntax that @litan1106 suggested.
This is something I think a lot of us could use - or would use. Or should use. I mean, creating the views by hand would be possible. But it's messy to be doing some things by hand and others not.
@pietern We're all onboard with following best DevOps practice, right?
@Lonache The SQL queries and alerts that @litan1106 is referring to are proper resources.
If you're looking to manage SQL queries in text files, you can do that today with the sql_file_task.
You could check out the default-sql bundle template for an example on how to structure this.
To update views you can use CREATE OR REPLACE in the query text.
@Lonache The SQL queries and alerts that @litan1106 is referring to are proper resources.
If you're looking to manage SQL queries in text files, you can do that today with the
sql_file_task.You could check out the
default-sqlbundle template for an example on how to structure this.To update views you can use
CREATE OR REPLACEin the query text.
even if we can use the sql_file_task to create the view for the SQL alert, we still want to create the SQL alert with the asset bundle so we can deploy all at once instead of manually create alert in the UI.
Is there any timeline already to incorporate SQL Alerts into Bundles?.. My team is evaluating if we need to invest time into Terraform or is this already in the roadmap?
There is no timeline for SQL Alerts yet. First, we're working on adding support for AI/BI dashboards.
We'll definitely consider adding this and I agree it makes sense to do so. It is a matter of prioritization.
Agreed this would be a great addition.
Based on some of the comments around using the sql_task, I've added a bit of a workaround to create alerts/notifications that are configured in asset bundles in case anyone else finds it useful.
An SQL file can be added to the asset bundle that raises errors on a condition - e.g.:
IF(
Column = Condition,
"Data is OK",
raise_error('Alert has been triggered')
) AS Alert
FROM example_table
Then a scheduled job runs on our SQL warehouse - running that SQL file. If the SQL query raises an error, the webook notification gets activated and we get a notification.
resources:
jobs:
expectation:
name: Expectation
schedule:
quartz_cron_expression: cron_expression
timezone_id: some_timezone
webhook_notifications:
on_failure:
- id: ${var.teams_channel_id}
tasks:
- task_key: sql_alert_file
sql_task:
warehouse_id: ${var.sql_warehouse}
file:
path: ../../src/sql_alert_file.sql
This isn't quite as nice as using proper databricks alert resources, but lets us configure similar alert notifications without doing anything in the UI.
Hello, I'm also interested into managing my SQL queries and alerts with Databricks Asset Bundle. Because it's not possible to manage them with DAB, I do it the Databricks python SDK for the moment. Here's an example:
Create the SQL Query (Databricks resource) from an SQL File
from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql
w = WorkspaceClient()
srcs = w.data_sources.list()
with open("/path/to/query.sql", 'r') as file:
sql_query = file.read()
query = w.queries.create(query=sql.CreateQueryRequestQuery(
display_name=f'my_query',
warehouse_id=srcs[0].warehouse_id,
description="My Query",
query_text=sql_query,
parent_path="/path/where/I/want/to/put/my/query"
)
)
Then I create the alert
alert = w.alerts.create(
alert=sql.CreateAlertRequestAlert(
condition=sql.AlertCondition(
operand=sql.AlertConditionOperand(
column=sql.AlertOperandColumn(name="column_to_check")
),
op=sql.AlertOperator.GREATER_THAN_OR_EQUAL,
threshold=sql.AlertConditionThreshold(
value=sql.AlertOperandValue(
double_value=0.8
)
)
),
display_name=f'name-of-my-alert',
query_id=query.id, # Here's the query that I just created
parent_path="/path/where/I/want/to/put/my/alert"
)
)
I finally, i schedule a job to run my alert
from databricks.sdk.service import jobs
from databricks.sdk.service import compute
srcs = w.data_sources.list()
job = w.jobs.create(
name=f'name-of-my-alert',# Keep the same names logic as your own project
tasks=[
jobs.Task(
description="Description of my alert",
sql_task=jobs.SqlTask(
warehouse_id=srcs[0].warehouse_id,
alert=jobs.SqlTaskAlert(alert_id=alert.id) # Id of the alert that I just created
),
task_key="run_alert",
timeout_seconds=0
)
],
schedule=jobs.CronSchedule(
quartz_cron_expression="0 */15 * * * ?", # Run every 15 minutes
timezone_id="UTC"
)
)
No sure if it's the best way to do manage alert through code, but I let that code here if it can help :)
hey, I am also interested in this functionality. Is there any update on it?
We've been moving away from Terraform (toward DABs) for deploying Databricks resources, it would be great if DABs could handle alerts too.
What's the best way to add a vote to this? Simply commenting here or by creating another feature request similar to fritzpaz above?
If Databricks wants to open source this, or allow community contributions, would love to dedicate weekend cycles to get this in.
Thanks for the +1s all!
The backend APIs are missing a few features to support queries and alerts. We're working through these with the teams developing these APIs to make sure they align with what we need for DABs support. The missing features are different for queries and alerts, so support for both is unlikely to be released at the same time.
+1 we're really interested in getting that as well, it would be super useful for us!
I found a walk-around to add an alert to an existing job using Python SDK The Query and the Alert i created earlier.
from databricks.sdk.service.jobs import Task, SqlTask, SqlTaskAlert, SqlTaskSubscription, TaskDependency, JobSettings
# Params
job_name = '<You Job Name>'
new_task_name = '<Your new Task Name>'
depends_on_task_name = '<Add after this task name>'
subscribers = [<emails for the alert task'>]
# Get Job Id
jobs = list(w.jobs.list(name=job_name))
job_id = jobs[0].job_id
print(f"Will update job: id: {job_id}, name: {alert_name}")
# Get current job configuration
current_job = w.jobs.get(job_id=job_id)
# Create the new task
new_task = Task(
task_key=new_task_name,
depends_on=[TaskDependency(task_key=depends_on_task_name],
sql_task=SqlTask(
alert=SqlTaskAlert(
alert_id=alert_id,
subscriptions=[SqlTaskSubscription(user_name=user) for user in subscribers]
),
warehouse_id=warehouse_id
)
)
# Append Tasks (existing + new)
update_job = w.jobs.update(
job_id=job_id,
new_settings=JobSettings(
tasks=current_job.settings.tasks + [new_task]
)
)
print(f"Updated Job: id: {job_id}, name: {alert_name} with new task: {new_task_name}")
@shreyas-goenka .. Is alerts added to latest databricks cli? I am testing on 0.268.0 version and it doesnt recognize alerts yet..
@hariaculeti Not yet. We are working on fixing some platform-side issues. If everything goes well, it should be available in a couple of weeks.
@shreyas-goenka are deployable queries close behind this? The alerts is great, but the deployable queries is the final jigsaw piece
@shreyas-goenka with this addition, will be also possible set permissions (for example can_manage), to other users than owner of the alert? I have found this limitation, when creating alerts using SDK
I systematically tested all available Databricks APIs that could potentially manage AlertsV2 permissions:
- DBSQL Permissions API Endpoint: workspace_client.dbsql_permissions.set()
from databricks.sdk.service.sql import ObjectTypePlural, AccessControl
workspace_client.dbsql_permissions.set(
object_type=ObjectTypePlural.ALERTS,
object_id=str(alert_id),
access_control_list=[
AccessControl(user_name="[email protected]", permission_level="CAN_MANAGE")
]
)
Result: Failed - AlertsV2 not supported by this API
- SQL Permissions API Endpoint: /api/2.0/preview/sql/permissions/alert/{alert_id}
POST /api/2.0/preview/sql/permissions/alert/{alert_id}
{
"access_control_list": [
{
"user_name": "[email protected]",
"permission_level": "CAN_MANAGE"
}
]
}
Result: Failed - "Model not found for alert" error
- Access Policies API (UI Internal) Endpoint: /ajax-api/2.0/accesspolicies
PATCH /ajax-api/2.0/accesspolicies
{
"access_policy": {
"name": "alertsv2/{alert_id}",
"internal_name": "tree/{alert_id}",
"permissions": [
{
"principal": "principals/{user_id}",
"permissions": ["CAN_MANAGE"]
}
]
},
"send_notification": false
}
Result: Failed - 403 Forbidden, requires CSRF token and browser session authentication
- Workspace Permissions API Endpoint: workspace.set_permissions()
workspace_client.workspace.set_permissions(
workspace_object_type="alerts",
workspace_object_id=str(alert_id),
access_control_list=[...]
)
Result: Failed - AlertsV2 not recognized as valid workspace objects
Manual workaround: Share alerts through Databricks UI: Navigate to SQL → Alerts Find your alert Click Share button Add users/groups manually
are deployable queries close behind this? The alerts is great, but the deployable queries is the final jigsaw piece
@Thomas-Bailey, If you mean will alerts be deployable and parameterizable from DABs, the answer is yes.
are deployable queries close behind this? The alerts is great, but the deployable queries is the final jigsaw piece
@Thomas-Bailey, If you mean will alerts be deployable and parameterizable from DABs, the answer is yes.
OK, I can see in the proposed YAML a query_text param, which I guess is the proposed solution. Where I'm coming from is say we have a bunch of .sql files in the bundle and the query_text param could actually be a bundle path.