Today, we are featuring a blog from one of our consulting partners, One Six Solutions, a data technology firm based in Chicago, Ill. This blog originally appeared in the One Six Solutions blog, and we thank them for sharing it with us.
One of the common problems faced when using Matillion ETL hosted on an Azure virtual machine is the inability to send failure emails when a scheduled job does not run successfully. Without a solution outside of Matillion, users would need to start the virtual machine to check the status of a job. Depending on the number of jobs and their scheduled run times, this could become a time-consuming manual process of starting and stopping the virtual machine.
The solution to this problem combines the Azure Queue Message component within Matillion ETL, Azure Queues within an Azure Storage Account, and an Azure function using an Azure Queue Trigger written using .Net Core 3.1. When a job fails, the Queue Message component will send a message to the Queue listening for failures. This triggers the Azure Function, which sends an email notification of failure, including the job title, to any email address added in the function.
Setting up your Azure Storage account
To get started, ensure that there is an Azure Storage Account created. There is likely one created for the resource group that the Matillion VM is using but a new one can be created as well if there is a desire to keep messages in a separate location.
Within the created storage account, there is an option for ‘Queues’ under ‘Queue Service’. Selecting the ‘+ Queue’ button at the top of the pane will bring up an option for entering the Queue name. This is the only setting that is needed. Keep in mind that Queues can be used for both success and failure emails if desired and this name will be entered in the Matillion component.
For Matillion to be able to send messages to the queue, access needs to be given to the storage account. There are multiple ways to provide access to the storage account with the easiest of these options being to provide the VM owner access to the account. To do this, navigate to the ‘access control (IAM)’ pane within the storage account. Clicking the ‘+ Add’ button will bring up an ‘Add Role’ pane allowing the user to add the owner access to the VM. For more options on granting permission, check the Matillion documentation here.
Setting Azure credentials within Matillion
Within Matillion, right clicking on the selected environment will allow the user to edit the environment setup. Select ‘Instance Credentials’ for the ‘Azure Credentials’. This will allow Matillion to access the storage account and send messages to the Queue.
Creating the Azure Queue Storage Message component
For this component, we are using an environment variable called ‘AuditJobName’ as the message to be sent. This variable is set using the ‘Set Scalar Variables’ setting of the orchestration job. This variable is populated with the automatic variable ‘job_name’. While any text can be entered as the message, using a variable for the job name allows for the same component to be used in multiple different jobs or within an audit framework without the need for multiple different messages. For more information on the automatic variables that can be used in a Matillion audit process, check out the Matillion documentation here.
Create the Azure function
To create the function app, search for ‘Function Apps’ within the Azure portal. This will show the above pane allowing the settings to be chosen for all apps within this Function App container. Choose the necessary subscription and resource group for the project. Choose .Net Core as the runtime stack which will default the version to 3.1 or above depending on the most recent version. This allows for the function to be written using C# code. Select ‘Review + Create’ to complete the setup.
After the function is created, navigate to the configuration settings within the Function App. Application settings need to be added for the function to read the proper values. The settings that need to be added are EmailSubject, SmtpHost, SmtpPassword, SmtpPort and SmtpUser. The Smtp settings may vary depending on email provider. This example is using an Office365 email. The SmtpHost is ‘smtp.office365.com’ and the SmtpPort is ‘587’. The SmtpUser and SmtpPassword is the email and password that will be used for sending the failure email.
Above is an example of the code that needs to be inserted within the Azure Function. This may be modified to get the necessary result. Ensure that the environment variables that we set earlier exactly match the variables used within the code. Multiple emails may be added using ‘mail.To.Add(new MailAddress());’ multiple times. The best way to test the function is using the Visual Studio Code extension that can be found here. The result of a successful run is the email shown below.
Learn more about Matillion ETL on Microsoft Azure
That’s how to set up Matillion failure notifications in Azure Queue Message. To learn more about what Matillion ETL can do within your Microsoft Azure environment using either Snowflake or Azure Synapse Analytics, request a demo.
About the author
Paul Narup is a consultant at One Six Solutions.