Matillion Failure Notifications using Azure Queue Message
Published
July 28, 2020
One of the common problems faced when using Matillion 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, 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 a failure, including the job title, to any email address added in the function.
Setting Up 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 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 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.
OneSix is a Premier Snowflake Partner
We help companies solve their most complex problems with cloud technology and yield incredible results.