Azure Queue Message configuration

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, Matillion ETL can read messages that trigger Matillion ETL jobs and can report back on whether the tasks were successfully run. Queue Storage provides a simple method of integrating Matillion ETL into an automated workflow between other instances, programs, and systems.

Note

You must first enable schedules at the project level before the Azure Queue listener can receive messages from the queue. Open the Project menu and select Manage Schedules and tick the Schedules are enabled for project checkbox.

Setting up

To begin, first create up to three 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.

The Manage Queue Configuration window is split into three sections, as detailed below:

Listen

Enable Azure Queue Storage: This turns on or off the sub-system that listens to Azure queues.

Note

Enable Azure Queue Storage 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

The variables and gridVariables fields are entirely optional. Variables 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 that 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 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's 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, read Microsoft's "Grant access from a virtual network" from the page Configure Azure Storage firewalls and virtual networks.