Blog| Matillion ETL for BigQuery

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.

 

Matillion ETL for BigQuery Job from your Google Home device - diagram

 

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:

 

Matillion ETL for BigQuery Job from your Google Home device - Agent

 

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

 

Matillion ETL for BigQuery Job from your Google Home device - Add integration to agent

 

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.

 

Matillion ETL for BigQuery Job from your Google Home device - Cloud function

 

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:

 

Matillion ETL for BigQuery Job from your Google Home device - pubsub

 

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:

 

Matillion ETL for BigQuery Job from your Google Home device - trigger job

 

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

 

Matillion ETL for BigQuery Job from your Google Home device - job running

 

Useful Links

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

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