What are Databricks SQL alerts?

Artikkeli
10/07/2024

Databricks SQL alerts periodically run queries, evaluate defined conditions, and send notifications if a condition is met. You can set up alerts to monitor your business and send notifications when reported data falls outside of expected limits. Scheduling an alert executes its underlying query and checks the alert criteria. This is independent of any schedule that might exist on the underlying query.

Important

Alerts leveraging queries with parameters use the default value specified in the SQL editor for each parameter.

View and organize alerts

Use one of the following options to access alerts:

Click the Workspace in the sidebar to view alerts in the Home folder, where they are stored by default. Users can organize alerts into folders in the workspace browser along with other Databricks objects.
Click the Alerts in the sidebar to view the alerts listing page.

By default, objects are sorted in reverse chronological order. You can reorder the list by clicking the column headings. Click the All alerts tab near the top of the screen to view all alerts in the workspace. Click the My alerts tab to view alerts where you are the owner.

Name shows the string name of each alert.
State shows whether the alert status is TRIGGERED, OK, or UNKNOWN.
Last Updated shows the last updated time or date.
Created at shows the date and time the alert was created.
- TRIGGERED means that on the most recent execution, the Value column in your target query met the Condition and Threshold you configured. If your alert checks whether “cats” is above 1500, your alert will be triggered as long as “cats” is above 1500.
- OK means that on the most recent query execution, the Value column did not meet the Condition and Threshold you configured. This doesn’t mean that the Alert was not previously triggered. If your “cats” value is now 1470, your alert will show as OK.
- UNKNOWN means Databricks SQL does not have enough data to evaluate the alert criteria. You will see this status immediately after creating your Alert and until the query has executed. You will also see this status if there was no data in the query result or if the most recent query result doesn’t include the Value Column you configured.

Create an alert

Follow these steps to create an alert on a single column of a query.

Do one of the following:
- Click New in the sidebar and select Alert.
- Click Alerts in the sidebar and click the + New Alert button.
- Click Workspace in the sidebar and click + Create Alert.
- Click the kebab menu in the upper-right corner of a saved query and click + Create Alert.
In the Query field, search for a target query.

To alert on multiple columns, you need to modify your query. See Alert aggregations.
In the Trigger condition field, configure the alert.
- The Value column drop-down controls which field of your query result is evaluated. Alert conditions can be set on the first value of a column in the query result, or you can choose to set an aggregation across all the rows of a single column, such as SUM or AVERAGE.
- The Operator drop-down controls the logical operation to be applied.
- The Threshold value text input is compared against the Value column using the Condition you specify.
Click Preview alert to preview the alert and test whether the alert would trigger with the current data.
In the When alert is triggered, send notification field, select how many notifications are sent when your alert is triggered:
- Just once: Send a notification when the alert status changes from OK to TRIGGERED.
- Each time alert is evaluated: Send a notification whenever the alert status is TRIGGERED regardless of its status at the previous evaluation.
- At most every: Send a notification whenever the alert status is TRIGGERED at a specific interval. This choice lets you avoid notification spam for alerts that trigger often.
Regardless of which notification setting you choose, you receive a notification whenever the status goes from OK to TRIGGERED or from TRIGGERED to OK. The schedule settings affect how many notifications you will receive if the status remains TRIGGERED from one execution to the next. For details, see Notification frequency.
In the Template drop-down, choose a template:
- Use default template: Alert notification is a message with links to the Alert configuration screen and the Query screen.
- Use custom template: Alert notification includes more specific information about the alert.
  1. A box displays, consisting of input fields for subject and body. Any static content is valid, and you can incorporate built-in template variables:
    - ALERT_STATUS: The evaluated alert status (string).
    - ALERT_CONDITION: The alert condition operator (string).
    - ALERT_THRESHOLD: The alert threshold (string or number).
    - ALERT_COLUMN: The alert column name (string).
    - ALERT_NAME: The alert name (string).
    - ALERT_URL: The alert page URL (string).
    - QUERY_NAME: The associated query name (string).
    - QUERY_URL: The associated query page URL (string).
    - QUERY_RESULT_TABLE: The query result HTML table (string). Results are limited to the first 100 rows.
    - QUERY_RESULT_VALUE: The query result value (string or number).
    - QUERY_RESULT_ROWS: The query result rows (value array).
    - QUERY_RESULT_COLS: The query result columns (string array).
    An example subject, for instance, could be: Alert "{{ALERT_NAME}}" changed status to {{ALERT_STATUS}}.
  2. You can use HTML to format messages in a custom template. The following tags and attributes are allowed in templates:
    - Tags: <a>, <abbr>, <acronym>, <b>, <blockquote>, <body>, <br>, <code>, <div>, <em>, <h1>, <h2>, <h3>, <h4>, <h5>, <h6>, <head>, <hr>, <html>, <i>, <li>, <ol>, <p>, <span>, <strong>, <table>, <tbody>, <td>, <th>, <tr>, <ul>
    - Attributes: href (for <a>), title (for <a>, <abbr>, <acronym>)
  3. Click the Preview toggle button to preview the rendered result.
    
    Important
    
    The preview is useful for verifying that template variables are rendered correctly. It is not an accurate representation of the eventual notification content, as each notification destination can display notifications differently.
  4. Click the Save Changes button.
Click Create Alert.
Click Add Schedule.
- Use the dropdown pickers to specify the frequency, period, starting time, and time zone. Optionally, select the Show cron syntax checkbox to edit the schedule in Quartz Cron Syntax.
- Choose More options to show optional settings. You can also choose:
  - A name for the schedule.
  - A SQL warehouse to power the query. By default, the SQL warehouse used for ad hoc query execution is also used for a scheduled job. Use this optional setting to select a different warehouse to run the scheduled query.
Click the Destinations tab in the Add schedule dialog.
- Use the drop-down to select an available notification destination. Or, start typing a username to add individuals.
Important

If you skip this step you will not be notified when the alert is triggered.
Click Create. Your saved alert and notification details appear on the screen.
Share the schedule.
- To the right of the listed schedule, choose the kebab menu and select Edit schedule permissions.
- Choose a user or group from the drop-down menu in the dialog.
- Choose out of the following schedule permissions:
  - NO PERMISSIONS: No permissions have been granted. Users with no permissions cannot see that the schedule exists, even if they are subscribers or included in listed notification destinations.
  - CAN VIEW: Grants permission to view scheduled run results.
  - CAN MANAGE RUN: Grants permission to view scheduled run results.
  - CAN MANAGE: Grants permission to view, modify, and delete schedules. This permission is required in order to make changes to the run interval, update the subscriber list, and pause or unpause the schedule.
  - IS OWNER: Grants all permissions of CAN MANAGE. Additionally, the credentials of the schedule owner will be used to run dashboard queries. Only a workspace admin can change the owner.
Important

Permissions for alerts and schedules are separate. Grant access to users and groups in the notifications destinations list so they can view scheduled run results.
Share the alert.
- Click near the top-right of the page.
- Add users or groups who should have access to the alert.
- Choose the appropriate permission level, then click Add.

Important

CAN MANAGE grants permission to view, modify, and delete schedules. This permission is required in order to make changes to the run interval, update the notification destination list, and pause or unpause the schedule.

For more information on alert permission levels, see Alerts ACLs.

Alert aggregations

An aggregation on an alert works by modifying the original SQL of the Databricks SQL query attached to the alert. The alert wraps the original query text in a common table expression (CTE) and performs a wrapping aggregation query on it to aggregate the query result.

As an example, a SUM aggregation on an alert attached to a query with text SELECT 1 AS column_name means that whenever the alert is refreshed, the modified SQL that runs would be: WITH q AS (SELECT 1 AS column_name) SELECT SUM(column_name) FROM q.

This means that the original query result (pre-aggregated) cannot be shown in an alert custom body (with parameters such as QUERY_RESULT_ROWS and QUERY_RESULT_COLS) whenever there is an aggregation on an alert. Instead, those variables will only display the final, post-aggregation query result.

Note

All trigger conditions related to aggregations are not supported by the API.

Alert on multiple columns

To set an alert based on multiple columns of a query, your query can implement the alert logic and return a boolean value for the alert to trigger on. For example:

SELECT CASE WHEN drafts_count > 10000 AND archived_count > 5000 THEN 1 ELSE 0 END
FROM (
SELECT sum(CASE WHEN is_archived THEN 1 ELSE 0 END) AS archived_count,
sum(CASE WHEN is_draft THEN 1 ELSE 0 END) AS drafts_count
FROM queries) data

This query returns 1 when drafts_count > 10000 and archived_count > 5000. Then you can configure the alert to trigger when the value is 1.

Notification frequency

Databricks SQL sends notifications to your chosen notification destinations whenever it detects that the Alert status has changed from OK to TRIGGERED or vice versa. Consider this example where an Alert is configured on a query that is scheduled to run once daily. The daily status of the Alert appears in the following table. Prior to Monday the alert status was OK.

Day	Alert Status
Monday	OK
Tuesday	OK
Wednesday	TRIGGERED
Thursday	TRIGGERED
Friday	TRIGGERED
Saturday	TRIGGERED
Sunday	OK

If the notification frequency is set to Just Once, Databricks SQL sends a notification on Wednesday when the status changed from OK to TRIGGERED and again on Sunday when it switches back. It does not send alerts on Thursday, Friday, or Saturday unless you specifically configure it to do so because the Alert status did not change between executions on those days.

Configure alert permissions and transfer alert ownership

You must have at least CAN MANAGE permission on a query to share queries. For alert permission levels, see Alerts ACLs.

In the sidebar, click Alerts.
Click an alert.
Click the button at the top right to open the Sharing dialog.
Search for and select the groups and users, and assign the permission level.
Click Add.

Transfer ownership of an alert

When you save an alert, you become the alert’s owner. If an alert’s owner is removed from a workspace, the alert no longer has an owner. A workspace admin user can transfer ownership of an alert to a different user. Service principals and groups cannot be assigned ownership of a alert. You can also transfer ownership using the Permissions API.

As a workspace admin, log in to your Azure Databricks workspace.
In the sidebar, click Alerts.
Click an alert.
Click the Share button at the top right to open the Sharing dialog.
Click on the gear icon at the top right and click Assign new owner.
Select the user to assign ownership to.
Click Confirm.

Jaa