Hey Snowflake, send me a <fancy> HTML email

Snowflake can now send HTML email notifications, so it’s time to explore how to make the most out of this new ability — including sending pre-rendered image charts with a Snowpark Python stored procedure. Let’s check out how.

Image generated by AI

A year ago I published “Hey Snowflake, send me an email”, showing off Snowflake’s new ability to send email notifications. However, those emails didn’t look too pretty. It’s now time to fix that.

Hey Snowflake, send me an email

For example, let’s say I want Snowflake to send me an email with the weekly top GitHub projects by number of stars. Following the previous instructions, the email notification would look like this:

Top GitHub projects email, formatted with Python `tabulate`

Now we can do better, as we can send an HTML table:

Top GitHub projects email, formatted as HTML

That looks decent, but it’s still not pretty enough. For example, we could give that plain HTML table a more modern look:

Top GitHub projects email, formatted as elegant HTML

And then for extra coolness, we could send any arbitrary pre-rendered plot as an image:

Top GitHub projects email, sent with a pre-rendered .png

Let’s find out how to send these new fancy emails.

How-to

Creating images that can be shared via email

Being able to publish images fully within Snowflake is cool, so let’s start with those steps:

The first thing we need is to CREATE STAGE in your Snowflake account to store the pngs:

create or replace stage pngstage 
directory = (enable = true)   
encryption=(type = 'snowflake_sse');

That looks like a normal create stage instruction, with two modifiers:

• With directory = (enable = true) we get the ability to list all the files on this new stage. We don’t need really need this, but a directory table will be helpful to keep track and delete any files that are too old.
• Setting encryption=(type = 'snowflake_sse') is important to set up “server side encryption”— otherwise the files we store in this stage won’t be readable outside your Snowflake account.

Then we can create a Snowflake Python stored procedure that:

• Receives the id of a query that had 2 columns as output (label and count), the labels for these 2 columns, and a title for the chart.
• Uses matplotlib to create an horizontal bar chart with those values.
• Outputs that chart to the stage we created to share these pictures.
• Returns a pre-signed URL that will allow others to view these PNGs from the web and email readers.

The code:

create or replace procedure create_png_web_image(
    query_id string
    , title string
    , x_label string
    , y_label string)
returns string
language python
runtime_version = '3.11'
packages = ('snowflake-snowpark-python', 'matplotlib')
handler = 'run'
execute as caller
as
$$
import matplotlib.pyplot as plt
import pandas as pd
import io

def save_file(session, buffer, path, dest_filename):
    session._conn.upload_stream(buffer, path, dest_filename, compress_data=False, overwrite=True)
    return path + '/' + dest_filename

def run(session, query_id, title, x_label, y_label):  
    df = session.sql("select * from table(result_scan(?))", params=[query_id]).to_pandas()

    plt.figure(figsize=(5, 3))
    plt.barh(df.iloc[:, 0], df.iloc[:, 1], color='skyblue')
    plt.xlabel(x_label)
    plt.ylabel(y_label)
    plt.title(title)
    plt.gca().invert_yaxis()  # Invert y-axis for better readability   
    plt.tight_layout()
 
    buffer = io.BytesIO()
    plt.savefig(buffer, format='png')
    buffer.seek(0)  # Rewind the buffer
    stage = '@pngstage'
    path = 'web_png'
    filename = 'png_%s.png' % (query_id)
    relative_path = save_file(session, buffer, stage + '/' + path, filename )
    return session.sql("select get_presigned_url(%s, '%s/%s', 604800) as url" % (stage, path, filename)).collect()[0]['URL']
$$
;

What’s interesting in this code:

• To make the plot available as a png, we need to save it to a buffer with plt.savefig(buffer, format='png').
• Then we can upload that buffer to the stage with the custom function save_file(), that uses the Snowpark internal method session._conn.upload_stream().
• To get a pre-signed URL for that image we run the query select get_presigned_url().
• These pre-signed URLs need an expiration time. The maximum is 1 week (604800 seconds). After that time the URLs will stop working.
• TO-DO: Since the URLs will only work for a week, you should automate deleting the generated files when they are not longer needed.
• TO-DO: The above function hard-codes the destination stage, you could make that more flexible.
• TO-DO: The above function creates horizontal bar graphs, but you could generate anything you want with matplotlib or other libraries.

A simple SQL stored procedure to send this URL as an image in an email:

execute immediate $$
declare
    signed_png_url string;
    formatted string;    
begin
    signed_png_url := (call create_png_web_image('01b077e2-0503-d084-001e-248301c57c26', 'Top weekly GitHub projects by stars', 'Stars', 'Project')); 
    formatted := '<b>The top GitHub projects by # of stars this week are:</b><br/><img src="' || signed_png_url || '""/>';
    call system$send_email(
        'my_email_int', 'felipe@example.com', 'Top GitHub weekly projects (png)', :formatted, 'text/html'
    );
    return 'sent: ' || :formatted;
    end;
$$
;

What’s interesting in this sample code:

• Playing with inline SQL stored procedure can help us iterate quickly over this code until we get exactly what we want.
• With this SQL stored procedure we call the Python stored procedure that generates the image, returns the URL, and we store that URL in a variable.
• With this URL, we create a bit of HTML with the <img src=''> tag.
• Then we call system$send_email() containing that bit of HTML, while making sure that the mime type is the newly supported text/html.
• Check my previous post or the docs to set up system$send_email() to send emails to your desired destinations.
• TODO: I hard-coded the query id while experimenting, should move to a general purpose stored procedure.

The query that provides the top weekly GitHub projects by # of stars is (as described in my previous post “Querying GitHub Archive with Snowflake: The Essentials”):

select any_value(repo_name) repo_name, count(distinct actor_id) stars
from cybersyn_github_archive.cybersyn.github_events
where type = 'WatchEvent'
and created_at_timestamp > dateadd(day, -7, current_date)
group by repo_id
order by stars desc
limit 10
-- 16s S-wh cold
-- 1s S-wh warm
;

Creating HTML tables that can be shared via email

Sending images is cool, but the links will expire in a week and some readers might choose to not display images. So they will see an empty email like this (unless you add more text to it):

An empty email without text because the user chose to not display images (or the URL expired)

Let’s explore an easy way to get an HTML table instead of an <img> tag as seen above:

create or replace procedure email_html_formatting(query_id string)
returns string
language python
runtime_version = '3.11'
packages = ('snowflake-snowpark-python')
handler = 'x'
execute as caller
as
$$

def x(session, query_id):    
    return session.sql(
      "select * from table(result_scan(?))",
      params=[query_id]).to_pandas().to_html()
$$
;

The quick solution above:

• Executes a query with Snowpark that brings back the result of the received query id.
• Calls to_pandas().to_html() on those results.

That gives us the “ugly” classic HTML table:

Classic HTML table

Creating a prettier HTML table

GMail will only apply CSS styles to a table if these styles are inlined. There are more sophisticated ways of doing this, but a simple replace() in Python did the job for me:

create or replace procedure email_html_elegant_table_formatting(query_id string)
returns string
language python
runtime_version = '3.11'
packages = ('snowflake-snowpark-python')
handler = 'x'
execute as caller
as
$$
def x(session, query_id):    
    html_table = session.sql("select * from table(result_scan(?))", params=[query_id]).to_pandas().to_html()
    # https://codepen.io/labnol/pen/poyPejO?editors=1000
    html_table = html_table.replace('class="dataframe"', 'style="border: solid 2px #DDEEEE; border-collapse: collapse; border-spacing: 0; font: normal 14px Roboto, sans-serif;"')
    html_table = html_table.replace('<th>', '<th style="background-color: #DDEFEF; border: solid 1px #DDEEEE; color: #336B6B; padding: 10px; text-align: left; text-shadow: 1px 1px 1px #fff;">')
    html_table = html_table.replace('<td>', '<td style="    border: solid 1px #DDEEEE; color: #333; padding: 10px; text-shadow: 1px 1px 1px #fff;">')
    return html_table
$$
;

Styled HTML table

• TO-DO: We could do more sophisticated styling and modifications with BeautifulSoup:

Untested code to play with an HTML table using BeautifulSoup.

Next steps: Making it production grade

This post so far has useful code to generate fancy e-mail notifications inside Snowflake — but more work is needed to make this ready for production.

For example, it would be great to have:

• A more solid stored-procedure.
• Error handling.
• Max email length awareness.
• Multiple destinataries.
• Leverage the new Snowflake alerts.

I’ve discussed many of these ideas with Ryan M Bacastow (who I’m grateful to for inspiring this post). Hopefully we will able to publish more about this soon.

Slack Notifications

Two different approaches:

• Sean Kim published “Send Slack Messages from Snowflake with Snowpark External Network Access” that uses a External Function to send messages directly to a custom Slack App. Btw, he also uses a similar mechanism to create plots to the one described in this post.
• TJ Murphy uses custom email addresses to send notifications to Slack, as described in his LinkedIn comment:

To set up Slack notifications from Snowflake:

1. Get the Slack notif email address. Right click the channel > View channel details > Integrations > Send emails to this channel

2. Create a new Snowflake user. Set the user's email to the Slack notif email address.

3. Log in as the user. Go to the user profile in Snowsight and click the "Send Verification Email" link.

4. Go to the Slack channel. You should see the validation email as a message in the channel (see image). Click the verification link.

5. Create or update a Snowflake Email Integration to add the Slack notif email address.

6. Call SYSTEM$SEND_EMAIL and get the notifications in Slack!

-- TJ Murphy

Want more?

Try this out with a Snowflake free trial account — you only need an email address to get started.

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Threads and LinkedIn (while increasingly less on Twitter). And subscribe to reddit.com/r/snowflake for the most interesting Snowflake news.