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
2. Make sure you have the appropriate IAM policies and roles
3. Create the function
4. Copy and paste or upload the Python code
5. 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
2. Add a connector of the type AWS Lambda
3. Get the Role ARN from the IAM role that you specified earlier on AWS
4. Enter the name of the Lambda Function
5. Enter your API key, API secret and group ID into “Secrets”
6. Save and test
7. 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.