Databricks Community

PTalathi · ‎10-22-2024

I am using a custom template in "Databricks Alerts" and an html code within it in order to trigger the query results within the body of the email. But unfortunately the email body contains the only the header specified in the html code and not the rows. Has anyone implemented such a scenario in Databricks alerts.

VZLA · ‎10-31-2024

I'd like to suggest trying it out first in a notebook, confirm the HTML code is working as per your expectations before implementing it. Hope the below helps.

Notebook Cell #1 (Python Lang)

%python
html_template = """
<!DOCTYPE html>
<html>
<head>
<style>
table {
    font-family: Calibri, sans-serif;
    border-collapse: collapse;
    width: 100%;
}
td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}
th {
    color: #FFF;
    background-color: #000000;
}
tr:nth-child(even) {
    background-color: #dddddd;
}
</style>
</head>
<body>
<h2>TraKic_Store_Count_Check</h2>
<p><b>Key Statistics</b></p>

<table>
    <tr>
        <th>bm_reporting_region_nm</th>
        <th>DiKerence</th>
    </tr>
    {{#QUERY_RESULT_TABLE}}
    <tr>
        <td>{{bm_reporting_region_nm}}</td>
        <td>{{DiKerence}}</td>
    </tr>
    {{/QUERY_RESULT_TABLE}}
</table>
</body>
</html>
"""

Notebook Cell #2 (Python Lang)

%python
# Sample data that simulates the query result
data = [
    {"bm_reporting_region_nm": "Region A", "DiKerence": 100},
    {"bm_reporting_region_nm": "Region B", "DiKerence": 200},
    {"bm_reporting_region_nm": "Region C", "DiKerence": 300}
]

# Generate table rows based on the sample data
rows_html = ""
for row in data:
    rows_html += f"<tr><td>{row['bm_reporting_region_nm']}</td><td>{row['DiKerence']}</td></tr>"

# Replace the placeholder tags in the template
rendered_html = html_template.replace("{{#QUERY_RESULT_TABLE}}", rows_html).replace("{{/QUERY_RESULT_TABLE}}", "")

# Display the rendered HTML in the notebook
from IPython.display import display, HTML
display(HTML(rendered_html))

Your template looks good to me, I did not try it via an Alert though, can you check if replacing {{#QUERY_RESULT_TABLE}} and {{/QUERY_RESULT_TABLE}} with {{#QUERY_RESULT}} and {{/QUERY_RESULT}} helps in rendering.

Also make sure the query behind the alert returns rows of data during alert evaluation; if there are no rows, the template won’t render any data rows. You can use {{#QUERY_RESULT}} to check if data exists and {{^QUERY_RESULT}} (inverted section) to handle the case when no data is present. This will at least help you with debugging.

<!DOCTYPE html>
<html>
<head>
<style>
table {
    font-family: Calibri, sans-serif;
    border-collapse: collapse;
    width: 100%;
}
td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}
th {
    color: #FFF;
    background-color: #000000;
}
tr:nth-child(even) {
    background-color: #dddddd;
}
</style>
</head>
<body>
<h2>TraKic_Store_Count_Check</h2>
<p><b>Key Statistics</b></p>

<!-- Conditional block to display message if there are no results -->
{{^QUERY_RESULT}}
<p>No data available for the selected criteria.</p>
{{/QUERY_RESULT}}

<!-- Conditional block to render table only if data is present -->
{{#QUERY_RESULT}}
<table>
    <tr>
        <th>bm_reporting_region_nm</th>
        <th>DiKerence</th>
    </tr>
    <tbody>
        {{#QUERY_RESULT}}
        <tr>
            <td>{{bm_reporting_region_nm}}</td>
            <td>{{DiKerence}}</td>
        </tr>
        {{/QUERY_RESULT}}
    </tbody>
</table>
{{/QUERY_RESULT}}

</body>
</html>