Cannot Get Query Results in SQL Alerts

AcrobaticMonkey
New Contributor III

‎10-29-2024 10:25 PM

Example Query:

select name, date from errors;

Now i want to trigger an alert if count is greater than 1, and a notification should be sent to slack with output rows (name and date values). Even if i use {{QUERY_RESULT_ROWS}}, it only gives value after count is applied. 

Is there any way to get the actual rows using Alerts?

Labels:
0 Kudos

VZLA
Databricks Employee
Databricks Employee

‎10-31-2024 09:34 AM

Note I have not tried this myself, but can you try the following and let me know if it helps:

  1. Create the query, so SELECT name, date FROM errors;
  2. Set up the alert. Set the condition to trigger the alert when the count of rows is greater than 1.
  3. Create a separate query that formats the result as a single string. 
    1. SELECT CONCAT_WS(', ', COLLECT_LIST(CONCAT(name, ' - ', date))) AS formatted_result
FROM errors
WHERE <your_condition>;
    2. Use this query in your alert to trigger the notification. The formatted_result should contain the concatenated rows.
  4. Send notification to Slack. In the alert settings, you can use the formatted_result to include the actual rows in the message.
0 Kudos