Triggering a Matillion ETL Job from your Google Home Device

Overview

Google Home and Google Home Mini is a popular smart speaker and the "OK Google" command is available on many mobile phones. In this article we investigate how to use your Google Home device to initiate a job in your Matillion ETL environment.

In order to connect the Google Home device with Matillion an API call needs to be made from Google Home which puts a message on an SQS Queue to be picked up by Matillion. The workflow is below.

Basic Workflow

1. User invokes the Google Home Agent to instruct Matillion to run a given job. This is done via Dialogflow

2. The Agent triggers a Lambda function using the AWS API

3. The Lambda function puts a message on SQS Queue

4. Matillion ETL picks up the message and starts the job

5. The job uses the Redshift COPY command to load data from S3 into Redshift

Dialogflow

The Google Home device can be controlled via Dialogflow which converts a user query into an Intent which can then trigger an External API call or run a piece of code.

To set this up, first create a new Agent in Dialogflow:

In order to connect to your Google Home device add an integration in the Agent:

As part of this set up you will be prompted to give an Intent to wake up the device and to link to your application. Create an Intent called "Start Job" for this:

In the Intent add some Training phrases, highlighting the job to run select @sys.any for the parameter set up called "Job":

In the Fulfillment enable a webhookcall and mark this as the end of the conversation:

The webhook this will be calling is in AWS.

API Gateway

The Amazon API Gateway allows users to create an endpoint which can act as an entrance point to an application. Here a Gateway is used to trigger a lambda function. In the AWS Console, create a new Gateway:

In the API create a new POST method to integrate to a Lambda function. This Lambda function will be used to put a message on an SQS Queue to start the Matillion job:

The Lambda Function will need to be left blank until one has been created.

Lambda

In a different tab, navigate to lambda functions and create a new function from scratch. This should be initiated from the API Gateway and simply takes the job parameter and puts a message target="blank">SQS Queue in the correct format. Further details on using a Lambda function to put a message on on SQS Queue is available here.

The code from this is available to download on the sidebar of this page.

Back in the API Gateway, add this Lambda function in the POST Setup. This is done by typing the name of the function into the box:

Deploy the API from the actions menu and create a new stage:

As part of the deployment an Invoke URL will be given. This is the URL which is called to trigger the lambda function to run.

This URL can be used back in the Dialogflow for the Fulfillment:

Now the setup is complete and the process can now be tested from the Dialogflow. This can be done either by voice or by typing:

The result is the Matillion job has been triggered to start: