Blog| Matillion ETL for Amazon Redshift

    Triggering Matillion ETL Jobs from Amazon Alexa via AWS Lambda & Amazon SQS

    In this blog we will show you how to take advantage of Matillion ETL integration with a range of Amazon Web Services including, lambda, SQS and Alexa. Follow our step-by-step configuration guide so you can trigger Matillion ETL jobs!

    For further details, check out our Technical Article for a full step by step guide, with sample code.

    Step 1 – Name your job

    Give your job a pronounceable name. The name of you job will passed from Alexa to Matillion in a variable. Make this something you can pronounce and Alexa will be able to consistently interpret. In my example I have a job called “airport dimension”.

     

    Triggering Matillion ETL Jobs from Amazon Alexa - Step 1

     

    Step 2 – Create your Alexa skill

    Use the Amazon Developer console to create your skill. You will need to give it an invocation command such as “Alexa, start Matillion” or “Matillion for Redshift”. Then you will need to create an intent, this is what you say to Alexa to actually carry out your commands. This could be something like “Run my orchestration job” you can specify variables called slots so you can say things like “run my sales fact job” or “start my airport dimension job”.

     

    Triggering Matillion ETL Jobs from Amazon Alexa - Step 2

     

    Step 3 – Create a lambda function

    Alexa posts and receives a JSON message to your lambda function. This lambda function will interpret the message from alexa, carry out some tasks, and generate a response.

    The lambda function will take the invocation command and slot, or job name variable, and put this metadata in a message to SQS so that matillion can consume and kick off the orchestration job.

    Some sample code for a lambda function that does this can be found in the linked Technical Article.

    Step 4 – Configure SQS

    Configure Matillion SQS integration. We recommend orchestration of jobs by SQS as a best practice to loosely couple the integration and protect against API/interface updates in future. So we have implemented this method rather than have Alexa call the API directly.

     

    Triggering Matillion ETL Jobs from Amazon Alexa - Step 4

     

    Step 5 – Test your skill

    Use the Alexa emulator to test your skill. You can use your attached microphone or you can type the text to Alexa. With either method, Alexa will tell you the response message the lambda function passed back if the test was a success.

     

    Triggering Matillion ETL Jobs from Amazon Alexa - Step 5

     

    Switch to your Matillion ETL interface and you will see a Task in the task history where a job has been started via the SQS message we put on the queue.

     

    Triggering Matillion ETL Jobs from Amazon Alexa - Matillion run

     

    Congratulations, you have just triggered your Matillion ETL job from Amazon Alexa! If you wanted to publish the skill you could follow the steps in the “Launch” tab in the Alexa console.

    Want to learn about Amazon Web Services? Read up on Amazon Redshift in our free downloadable eBook.

    redshift-best-practice-ebook
    By: Paul Johnson, Solution Architect