How to Trigger a Matillion ETL for BigQuery Job from your Google Home device

Editor note: Today’s blog post was prepared by work placement students from Saint Ambrose school in Altrincham, UK.

This blog will walk you through the configuration process for setting up an ‘OK Google’ command to run your Matillion ETL job from a Google Home smart device or ‘OK Google’ enabled mobile phone.

Basic Workflow

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. This blog will focus on steps 1-4 for set up and triggering. More details can be found in our Technical Article.

1. Invocation Command

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

Dialogflow is a voice and text-based conversational interface. 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:

Add an integration in the Agent to connect to your Google Home device:

Go through the configuration steps. For additional details see our Technical Article.

Note: In the Fulfillment enable a webhookcall and mark this as the end of the conversation. The webhook this will be calling a GCP Cloud function.

2. Google Cloud Function

The Agent triggers a Google Cloud Function using a Webhook call.

In the GCP console, navigate to Lambda functions and create a new function from scratch. The trigger on this function should be an HTTP trigger and the URL is given below the Trigger box. Save this URL for later.

Update the node.js and package.json code to put a message on the pub sub queue.

3. Google PubSub

The function puts a message on PubSub

In order for the Cloud Function to work it must invoke a valid Pub topic. Further details on initiating a Matillion job from PubSub is available here.

Set up the PubSub topic in the GCP Console and add a Subscription to configure Matillion to listen to:

4. Read Message and Trigger Job

Matillion ETL picks up the message and starts the job.

Once you add the URL from the Cloud Function to invoke the function in Dialogflow you can test your configuration. This can be done either by voice or by typing:

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

Useful Links

Dialogflow Service
Configure Dialogflow Agent
Use Matillion to initiate a PubSub 
Technical Article

Begin your data journey

Request a free 1-hour demonstration, or start a 14-day free trial of Matillion ETL for BigQuery to try it out.

Get Free Demo

Start Free Trial