I would like to include the result of a SELECT statement (SELECT LoadedFileName, NumRecords) into an email. There are several rows and two columns in the select. How can this result set be included in a single email's body?
Hi @Aristotle,
This is definitely possible but isn't a trivial task and has a lot of variables involved. For example, are you looking to generate an HTML or Rich Text bodied email with the table of information within it or? Or perhaps are you wanting to export some results to a CSV file and then attach it to an email that is sent? These answers drastically change the approach.
All of these situations would writing Python in some fashion. Just thinking through the HTML table to email approach. The flow would be something like Query Result to Grid to get the table content, Python script that uses the Pandas module which will allow you to create a dataframe from the grid variable content and then output it as HTML table which would be stored in a variable. The idea is to build an Email Body variable with the contents by adding the necessary format and info it. The easiest way I know of generating an HTML Table based on data is the Python Pandas module. Here is a recent and simple example of how that is done: https://www.geeksforgeeks.org/how-to-render-pandas-dataframe-as-html-table/
I hope this helps in some way.
Bryan,
Thank you so much for your help. I think the Python Pandas approach is exactly what I was looking for. To be formatted properly, it has to be some sort of HTML message.
I am migrating from an SSIS/MS SQL environment where I had created a similar stored procedure (in T-SQL) that would produce the contents of the message. Now that I am thinking of it - triggered by your response - I suppose I can create a stored proc in Snowflake that would similarly return the contents of the email in a string. There has to be a way to call that stored proc and return the results to a variable that would become the email body in Matillion?
I will try both approaches, although it seems that Python may be the more structured way to go.
Thank you again for your help.
I know exactly what you are talking about as it pertains outputting tables in SQL Server using sprocs. We also do the same thing. You could likely write this same thing in a Snowflake sproc but you would most definitely need to be fluent in Javascript to make this happen. I don't know of a function in Snowflake that will output html from results which means it would be a combo of SQL and Javascript.
I personally like Python over Javascript when dealing with data. It's much more robust and has greater support from an examples and documentation perspective than Javascript (particularly Snowflake's implementation of Javascript in sprocs). Just my 2 cents. 😀