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 guide is aimed at anyone who might wish to use one.

Prerequisites

• A Matillion ETL instance configured to poll the SQS queue above. For more information on this, read Manage SQS Configuration.
• 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 the 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 into the appropriate table and schema.

Example Steps

1. In Matillion ETL, create a copy of the sample job dim_airport_setup and give it a name that you 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.
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 for "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, and 83 to match your environment, and then 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 ETL skills by using the invocation name "Matillion ETL for redshift". If configured correctly, Alexa will respond.
2. Then, you need to tell Alex your intent, so say, "start the airport dimension job". If all is well, Alexa will say that they have started the orchestration job.

17. In your Matillion ETL instance, 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.