Triggering ETL from Amazon Alexa via AWS Lambda & Amazon SQS

Triggering ETL from Amazon Alexa via AWS Lambda & Amazon SQS



Overview

In this example we will trigger an existing orchestration job using the Amazon Alexa Developer Console. You do not need an Amazon Alexa device to complete the steps below but this advice is oriented toward those who would wish to use one.

 

Pre-requisites

  • A Matillion ETL instance configured to poll the SQS queue above. For more information on this, see Integration with Amazon SQS.
  • An Amazon Developer Account.
  • A Matillion ETL project with jobs ready to be run. For the sake of example, we will be using the sample jobs that come with each Matillion ETL instance.
 

Basic Workflow

  1. User invokes the Alexa skill and instructs which job to run.

  2. Alexa passes the job name to an AWS Lambda function.

  3. A Lambda function generates the JSON and puts a message to SQS.

  4. Matillion ETL consumes SQS message and starts the relevant job.

  5. Matillion ETL sends a COPY command from Redshift to S3

  6. Data is copied to a Redshift Staging Table

  7. Data is transformed and placed in the appropriate table and schema.


Example Steps


Note: An example python script is attached to this article should you wish to try this yourself. A download link is available at the bottom of this page.

  1. In Matillion create a copy of the sample job dim_airport_setup and give it a name we can invoke from a voice command e.g. “airport dimension”.

  2. Create an Alexa Skill via the Amazon Alexa Skills Kit.

    1. Give it a name, e.g. “Matillion ETL”.

    2. Select custom model.

  3. Enter an invocation name for your skill, e.g. “matillion etl for redshift”. Users can say a skill's invocation name to begin an interaction with a particular custom skill. For example:

    "Alexa, access Matillion ETL for Redshift"

  4. Create a custom intent.

    1. Give it a name, e.g. “StartOrchestrationJob”

    2. Create an intent slot, e.g. “jobName” and select any slot type for now. type.

    3. Create a sample utterance, e.g. “start the {jobName} job”.

    4. Optional: Check whether this intent requires confirmation.

  5. Add a new custom slot type.

    1. Give it a name, e.g. “jobs”.

    2. Add a slot value, e.g. “Airport Dimension”.

  6. Select the jobName Slot and configure it.

    1. Select the custom slot type you have just created “jobs”.

    2. Select Is this slot required.

    3. Enter a speech prompt, e.g. “Please say the name of your job”.

  7. Save & Build your Model

  8. Log into your AWS console and Create a new Lambda function

    1. Select Author from scratch

    2. Give it a name, e.g. “AlexaETLTrigger”

    3. Select the Python 2.7 runtime

    4. You can create a new role or use an existing role, it will require AmazonSQSFullAccess.

  9. Select Alexa Skills Kit.

  10. Optional: Copy the Skill ID (also called Application ID) of your skill located in your Alexa Skills Kit dashboard and paste it into the Skill ID Verification and click save.

  11. Select the your lambda function in the designer pane to switch to the code viewer.

  12. Copy the lambda function ARN from the top right of the screen.

  13. Paste the attached code into the endpoint section of your Alexa skill save & build the model.

  14. Return to the Lambda function and copy the attached sample code and and change the values in lines 74, 80,81, 82 ,83 to match your environment and save.

  15. Back in the Alexa console click on the Test tab.

  16. Here you can use your microphone or you can type your speech commands.

    1. Invoke your Matillion skills by using the invocation name “Matillion for redshift”.  If configured correctly Alexa will respond.

    2. Then we need to tell Alex our intent so say, “start the airport dimension job”. If all is well Alexa will say that she has started the orchestration job.

  17. In your Matillion interface you will see a task execution triggered by SQS

 

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