Contents x
    Azure Queue Message Configuration
      • Dark
        Light
      Contents

      Azure Queue Message Configuration

        • Dark
          Light
        Article Summary

        Overview

        It is often desirable, especially with large workflows, to run Matillion ETL jobs in a remote, automated fashion and receive feedback in a similar manner. An excellent way to accomplish this is to use Azure's Queue Storage. Matillion ETL can post bespoke messages to this system (also see: Azure Queue Storage Message Component) as well as be configured to listen to messages posted there.

        By listening to a specific queue, a Matillion ETL client can read messages that trigger Matillion ETL jobs and can report back on whether the tasks were successfully run. Essentially, Queue Storage provides a simple method of integrating Matillion ETL into an automated workflow between other instances, programs, and systems.

        Setting up

        To begin, first create up to 3 Queues that will be used to listen, report successes and report failures. A summary of how to set up queues via the Azure Portal can be found in the Managing Queues on Azure section below.

        After setting up your Queues, you can find the menu via the Project menu in the Matillion ETL client.


        Listen
        • Enable Azure Queue Storage: This turns on or off the sub-system that listens to Azure Queues. (Note: This is global, and not Project-specific)
        • Credentials: Choose the credentials that you will be using to listen to Azure Queues. These credentials must give access to the desired Storage Containers and Queues therein.
        • Storage Account: The name of the Azure Storage Account that contains your desired Queues.
        • Listen Queue: The name of the Queue to listen for messages. These messages are in a set format as shown below.

        Success

        Determine a response for a successful listen.

        • Enable Success: Enable this if you wish to place a message on an Azure Queue when your orchestration job has completed.
        • Success Queue: The name of the Queue on which to place success messages.
        • Compress: When ticked the body of the message on the queue will be gzipped. Use this to avoid hitting Azure Queue Storage Limits.

        Failure

        Determine a response for a failure to listen (generally due to an inappropriate message format for Matillion ETL to act upon).

        • Enable Failure: Enable this if you wish to place a message on a Azure Queue when your orchestration job has failed.
        • Failure Queue: The name of the Queue on which to place failure messages.
        • Compress: When ticked the body of the message on the queue will be gzipped. Use this to avoid hitting Azure Queue Storage Limits.

        Clicking the Test button will ensure that the queues exist and can be read.


        Expected Listen Format

        Messages in the Listen Queue are automatically read by Matillion ETL and removed from the Queue. If the message is of the correct format, Matillion ETL can parse the message and take action in the form of running an appropriate Job.

        The expected format is given below:

        {
"group":"<Exactly matches the project group where your job resides>",
"project":"<Exactly matches the project name where your job resides>",
"version":"<Exactly matches the version Name>",
"environment":"<Exactly matches the environment Name>",
"job":"<Exactly matches the orchestration Job Name>",
"variables":  {
"<Name 1>": "<Value 1>",
"<Name 2>": "<Value 3>"
}
"gridVariables" : {
"<GridVar Name>": [["<R1C1 Value>", "<R1C2 Value>"], ["<R2C1 Value>", "<R2C2 Value>"]]
}
}

        Note 1: The variables and gridVariables fields are entirely optional. Variables and are passed to the orchestration job. Matching variable names must be declared in the project with default values set for each environment. If a variable is passed which is not defined in the project, an error is logged in Project → Task History


        Returned messages

        The messages that are returned on the success or fail queues are in the following format:

        {  
"type":"QUEUE_ORCHESTRATION",
"groupName":"<the group name that was executed>",
"projectName":"<the project name that was executed>",
"versionName":"<the version name that was executed>",
"environmentName":"<the version name that was executed>",
"state":"SUCCESS|FAILED", 
"enqueuedTime":<Time message placed on queue in unix epoc format>,
"startTime":<Time orchestration job began in unix epoc format>,
"endTime":<Time orchestration job ended in unix epoc format>,
"message":<contains error messages where applicable>,
"originator ID":"q_Queue",

        "tasks":[  <This is a list of tasks executed in the orchestration>
        
{
        
"type":"VALIDATE_ORCHESTRATION",
        
"jobName":"SimpleQueueJob",
        
"componentName":"Start 0",
        
"orchestrationJobName":"SimpleQueueJob",
        
"orchestrationComponentName":"Start 0",
        
"state":"SUCCESS",
        
"rowCount":0,
        
"startTime":1443526622364,
        
"endTime":1443526622364,
        
"message":""
        
},
        
{
        
"type":"VALIDATE_ORCHESTRATION",
        
"jobName":"SimpleQueueJob",
        
"componentName":"End Success 0",
        
"orchestrationJobName":"SimpleQueueJob",
        
"orchestrationComponentName":"End Success 0",
        
"state":"SUCCESS",
        
"rowCount":0,
        
"startTime":1443526622365,
        
"endTime":1443526622369,
        
"message":""
        
},
        
{
        
"type":"EXECUTE_ORCHESTRATION",
        
"jobName":"SimpleQueueJob",
        
"componentName":"End Success 0",
        
"orchestrationJobName":"SimpleQueueJob",
        
"orchestrationComponentName":"End Success 0",
        
"state":"SUCCESS",
        
"rowCount":0,
        
"startTime":1443526622369,
        
"endTime":1443526622369,
        
"message":""
        
}
        
],
        
"rowCount":0
        
}
        



        


        Managing Queues on Azure
        

        Queues to be used by Matillion ETL (either through the Manage Queue Configuration menu or through the Azure Queue Storage Message component) must be set up in advance via the Azure Portal.
        

        Azure Queue Storage Messages reside in Queues that in turn reside in Azure Storage accounts. These can be found through Azure Portal→Storage Accounts→<Storage Account>→Queues.
        

        From here, existing Queues can be clicked on to examine their contents (queued messages), created and deleted.
        

        Note: Only Standard and StandardV2 kinds of Storage Account can contain Queues.
        



        

        Granting Access from a Virtual Network
        

        It is likely that your Matillion ETL virtual machine will require the granting of access to any relevant storage accounts.
        

        For more information on setting up this access, please read Microsoft's "Grant access from a virtual network" from the page Configure Azure Storage firewalls and virtual networks.
        What's Next