25 Feb 2019 | Analyst Recipe

Build Connector Status Dashboards With the Fivetran REST API

Charles Wang
Charles Wang
Stephen Young
Stephen Young
Build Connector Status Dashboards With the Fivetran REST API
Learn how to combine AWS Lambda and the new Fivetran REST API to build connector status dashboards.

Fivetran is currently in beta with a REST API. We built this API so that enterprising data engineers at your organization could programmatically manage users, groups and connectors, and automate their workflows.

There is an analytics use case for the API, too. By using an AWS Lambda function to request a list of all connectors within a group, you can easily assemble a table with the status of every connector syncing to that group’s warehouse. If you sync it every five minutes and connect the data to Looker, you can have a near real-time visualization of every connector’s status without entering your Fivetran dashboard.

Per the code below, the data from this API endpoint is split into three tables:

  1. Items – every connector
  2. Tasks – tasks, each of which is attributed to a connector
  3. Warnings – warnings, each of which is attributed to a connector

You can, of course, designate your tables however you like. One basic view you can create with the data is:

All connectors with current status and last successful sync:

CREATE VIEW connector_status 
AS 
  WITH max_date 
       AS (SELECT service               AS Connection_Type, 
                  Max(_fivetran_synced) AS max 
           FROM   items 
           GROUP  BY 1) 
  SELECT SCHEMA       AS Connector, 
         sync_state   AS Status, 
         update_state AS Status_Detail, 
         succeeded_at AS Last_Success 
  FROM   items 
         join max_date 
           ON items.service = max_date.connection_type 
              AND items._fivetran_synced = max_date.max 
  ORDER  BY connector ASC; 

The following is a plug-and-play Python script that you can use as an AWS Lambda function. You can find it in our GitHub repo, too.

import datetime
from botocore.vendored import requests
import time


def lambda_handler(request, context):

   # Fivetran API URL
   api_key = request['secrets']['api_key']
   group_id = request['secrets']['group_id']
   api_secret = request['secrets']['api_secret']

   url = 'http://api.fivetran.com/v1/groups/{group_id}/connectors'.format(group_id=group_id)

   # Make a request to the endpoint using the correct auth values

   auth_values = (api_key, api_secret)
   response = requests.request('GET', url, auth=auth_values)

   timestamp = datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')

   # Convert JSON to dict
   response_json = response.json()
   item_list = response_json['data']['items']

   # These will be put into 'insert' in the response
   result_items = []
   result_tasks = []
   result_warnings = []

   # Iterate through the items returned by the API
   for entry in item_list:
       entry_item = {}
       for k, v in entry.items():
           if k != 'status':
               entry_item[k] = v
           else:

               result_tasks += [{'id': entry['id'], 'message': task['message'], 'code': task['code']} for task in
                                v['tasks']]
               result_warnings += [{'id': entry['id'], 'message': task['message'], 'code': task['code']} for task in
                                   v['warnings']]

               for sub_key in ['sync_state', 'setup_state', 'is_historical_sync', 'update_state']:
                   entry_item[sub_key] = v[sub_key]

       result_items.append(entry_item)

   result = {}
   result['state'] = {timestamp: timestamp}
   result['insert'] = {'items': result_items,
                       'tasks': result_tasks,
                       'warnings': result_warnings}

   return result

The API key and API secret can be found via your dashboard. Go to the upper left menu, select “Manage Account,” and enter the “Settings” tab. You can retrieve the names of your groups with a GET request to the groups API.

The steps to configuring your Lambda connector are fairly similar to those of our Redshift migration script. You will need to:

  1. Set up your Lambda function on AWS via the AWS dashboard
    1. Make sure you have the appropriate IAM policies and roles
    2. Create the function
    3. Copy and paste or upload the Python code
    4. Make sure to configure a test event in the following JSON format with your API key, API secret and group ID, and test it.
{
  "secrets": {
    "api_key": "s4Mpl3APIk3Y",
    "api_secret": "53cr374p1",
    "group_id": "coding_crisis"
  }
}
  1. Set up your connector via the Fivetran dashboard
    1. Add a connector of the type AWS Lambda
    2. Get the Role ARN from the IAM role that you specified earlier on AWS
    3. Enter the name of the Lambda Function
    4. Enter your API key, API secret and group ID into “Secrets”
    5. Save and test
    6. Set the sync frequency to your preference
{"api_key": "s4Mpl3APIk3Y", "api_secret": "53cr374p1",  "group_id": "coding_crisis"}

You should be able to see the function’s output in your connector group’s data warehouse.

Feel free to contact Charles (charles@fivetran.com) or Stephen (stephen@fivetran.com) with any additional questions. If you don’t currently use Fivetran but are interested in what you see, feel free to request a demo here.

Are You A Data Expert?

Start a free trial today.

Discover the smartest solution for data-driven results.