Connector SDK - Detailed Guide

Fivetran Connector SDK

The Fivetran Connector SDK is available as a GitHub repository that provides a basic example and additional examples that we recommend you consider and try out.

Building your first connector

Review our examples and find one that is a good fit for the connector you want to write. Review our technical reference for details of each method and operation.

1. Set up or go to your Python development environment and create a directory for the new connector project. Use the IDE you are most familiar with as our Connector SDK works in any Python IDE. You can also create a virtual environment if you wish. NOTE: We support Python versions ≥3.9 and ≤3.12.

2. Install the Fivetran Connector SDK:

   pip install fivetran-connector-sdk
   

3. Select your preferred example as a template. Use one that closely resembles the connector you want to write. Copy its code and paste it into a file in your directory called connector.py.

   NOTE: When you create your new connector project, your IDE might create a main.py file for you. If so, rename that file to connector.py so that the Connector SDK can find it.

4. The connector.py file is where you write your custom connector. Modify the code from the example you copied to write your connector. You can create connectors that use multiple files as long as all the files you need are present in your project directory and are called from your connector.py file. Any Python library that can be installed with pip can also be used, and will need to be declared in your requirement.txt file.

   TIP: We encourage you to deploy and run one of our examples as a first connector to get familiar with the Connector SDK.

5. Test the connector by running it locally using the following command:

   fivetran debug
   

   If you have included the following code in your connector.py file, you can test your connector by running your file directly from your IDE:

   if __name__ == "__main__":
      connector.debug()
   

   This test creates a local warehouse.db file, which is a Duck DB representation of the data that the connector delivers to your destination. This file is located in <project_directory>/files/warehouse.db.

   You can explore our state management and configuration to control exactly what is tested with each test run.

   IMPORTANT: Use fivetran debug to test and troubleshoot your connector's behavior with your source's actual data. The tester works by emulating Fivetran's core. However, it is running on your local machine, so it isn't as performant as you will experience when running your connector in production. We recommend running fivetran debug on multiple small samples of your data to fully test your connector's logic. Deploy to production once you are ready to test with larger data sets. State management and configuration can be used to control exactly what is tested with each run.

6. You can connect to the Duck DB warehouse.db file using DBeaver or using the DuckDB CLI commands. Once you are happy with the connector and the Fivetran Local Tester output, proceed to the next step.

7. Deploy the connector by running the following command from the root directory of your project:

   fivetran deploy --api-key <FIVETRAN-API-KEY> --destination <DESTINATION-NAME> --connection <CONNECTION-NAME>
   

   Your <FIVETRAN-API-KEY> is the base64-encoded {API-key}:{API-secret} used in all other Fivetran API calls. See our Scoped API key documentation to learn how to obtain your API key.

   The <DESTINATION-NAME> identifies the destination you want your new connector to deliver to. It is the name of your destination in the Fivetran dashboard.

   

   NOTE: <DESTINATION-NAME> is required only if multiple destinations are created for the account. If there is only a single destination, it can be omitted, as it will be automatically identified.

   The <CONNECTION_NAME> identifies the connection both in Fivetran’s Dashboard and for future updates. The connection name must conform to our naming conventions defined by the renaming rules, ensuring it matches the pattern and character set of transformed names.

   

   TIP: Use environment variables to avoid having to enter your API key repeatedly in your terminal.

Examples

We maintain a public repository of examples that we are continuously updating.

• The Quickstart examples show the simplest implementations of the Fivetran Connector SDK.

• The Common patterns for connectors examples show commonly used patterns which you can copy paste for use in your code.

• The Source examples show working examples for syncing records from real-world databases like dynamodb, redshift, etc.

SDK runtime environment

The Connector SDK runtime environment executes your submitted Python script in an isolated environment. It installs the libraries listed in the requirements.txt file, and after installation, it runs the provided Python script.

Python version support

We support Python versions ≥3.9 and ≤3.12.

Pre-installed DB drivers

We pre-install the msodbcsql17 and msodbcsql18 drivers for connecting to Microsoft SQL server. We also pre-install libpq5 and libpq-dev, which is required to support psycopg and psycopg2 respectively.

Pre-installed packages

We pre-install the fivetran_connector_sdk:latest and requests:2.32.3 package when running your code.

System resources

The environment running your code has:

• 1 GB of RAM
• Less than 0.5 vCPUs of an 8 Core N2 or equivalent machine

Our production infrastructure allows for some variation in resources available to your connections at any given moment, load balancing across multiple connections.

Python virtual environments

A Python virtual environment is an isolated environment for a Python project. It allows you to manage dependencies for your projects separately, ensuring that packages required for one project don’t interfere with packages required for another. This is particularly useful when different projects require different versions of the same package.

Creating a virtual environment

In Windows, MacOS, or Linux, run the following command:

python -m venv myenv

This creates a directory called myenv, which contains your virtual environment.

Activating your environment

1. Depending on your environment, run either of the following commands:

   • In Windows, run the following command:

     myenv\Scripts\activate
     

   • In MacOS and Linux, run the following command:

     source myenv/bin/activate
     

2. Once your Virtual Environment is active, you can install Fivetran's Connector SDK in it ready to build your connector using the following command:

   pip install fivetran-connector-sdk
   

Deactivating your environment

When you are done or when you want to switch projects, in Windows, MacOS or Linux, run the following command to deactivate the virtual environment:

deactivate

Working with requirements.txt file

Usually, your connector's code will need to import additional Python libraries. To ensure the correct libraries are installed before we run your code in Fivetran, you must include a standard pip requirements.txt file in the root directory of your connector project. In your requirements.txt file, you must list all the Python libraries your connector uses along with any required versions. The weather example provides an example.

You can also quickly install all the needed libraries when setting up your environment using the following command:

pip install -r requirements.txt

NOTE: fivetran_connector_sdk:latest and requests:2.32.3 is available when executing your code. Please do not declare them in your requirements.txt to avoid dependency conflicts.

Working with configuration.json file

When debugging or deploying a connector, you can pass specific configuration values to your code. Configuration values are stored securely and encrypted in the same way as other connectors' credentials. See our Data Credential Encryption documentation page for more details.

The easiest way to pass values is as follows:

1. Create a temporary configuration.json file in the root directory of your connector project.
2. Use it for the initial deployment.
3. Delete the temporary file. We do not store this file. Do not check it into your code repository.

Your JSON file should be valid JSON containing key-value pairs with only STRING values. The following is the required format:

{
  "key1" : "value1",
  "key2" : "value2"
}

Only configuration keys are visible in your Fivetran dashboard after your connector is deployed and running.

Configuration values are fully or partially obfuscated depending on their length as this process is designed for managing sensitive credentials.

The values are only ever available to your code at runtime. You can be confident using this configuration to pass any credentials required by your connector, like a required key to make an API call. The configuration example provides an example.

To test your code using a configuration.json file in your project's root directory, use the following command:

fivetran debug --configuration configuration.json

To deploy your connector.py using a configuration.json file in your project's root directory, use the following command:

fivetran deploy --api-key <FIVETRAN-API-KEY> --destination <DESTINATION-NAME> --connection <CONNECTION-NAME> --configuration configuration.json

NOTE: Any time you provide a --configuration argument and associated information, it completely replaces what was there before. If you omit --configuration, no changes are made to the previously provided configuration.

Working with state.json file

When running a connector locally that uses the state variable, a state.json is created in <project_directory>/files/state.json. This file can be edited locally and is used as the starting state for the next local run of the connector. You can also manually create this file to start a connector Debug() run from a particular state.

You can manage the state of Connector SDK connectors already deployed to Fivetran by using our API endpoints:

• Retrieve Connector State
• Update Connector State

Code upload guidelines

When using fivetran deploy, we support only .py files and a requirements.txt file as part of the code upload. No other code files are supported or uploaded during the deployment process. Ensure that your code is structured accordingly and all dependencies are listed in requirements.txt.

Working with environment variables

When developing a connector, it is much more convenient to use environment variables for parameters you would otherwise have to enter repeatedly into the command line. We recommend using an environment variable for your FIVETRAN_API_KEY to avoid having to find and copy it every time you deploy your connector. To use an environment variable, create an .env file in the root directory of your connector project. Your file should be in the following format:

FIVETRAN_API_KEY=<your 64 bit encoded Fivetran API key>

Load your .env file for use in your terminal by running the following command:

export $(grep -v '^#' .env | xargs)

Check that your environment variables are now available for use by running the following command:

echo $FIVETRAN_API_KEY 

You should use configuration files for any configuration used within your connector.py code so that it will be available when the code is deployed and running in Fivetran and stored securely within Fivetran.

NOTE: .env files are not deployed to Fivetran with the rest of your code.

To deploy your connector.py using an .env file to set a local environment variable for your FIVETRAN_API_KEY, use the following command:

fivetran deploy --destination <DESTINATION-NAME> --connection <CONNECTION-NAME>

You can use .env files to set both your destination and connection names, and to allow to quickly switch between different connections.

For example, create another test.env file that contains the parameters in the following format:

FIVETRAN_API_KEY=<your 64 bit encoded Fivetrna API key>
FIVETRAN_DESTINATION_NAME=<your destination name>
FIVETRAN_CONNECTION_NAME=<your connection name>

Load it by running the following command:

export $(grep -v '^#' test.env | xargs)

Deploy this connector by running the following command:

fivetran deploy

or, alternatively, if your connector needs configuration, by running the following command:

fivetran deploy --configuration configuration.json

TIP: Make sure to include your .env files in your .gitignore file to ensure you don't accidentally check your FIVETRAN_API_KEY into your code repository.

Working with warehouse.db

Our testers support only basic operations. Functionalities like schema operations are not supported by fivetran debug. In case you see failures while using fivetran debug and your code has schema definition changes, use the fivetran reset command to reset the warehouse.db and state files:

fivetran reset

If the error persists, then most likely there is an error in your code.

Working with yield and child functions

The yield keyword in a generator function is a standard Python code pattern. The Fivetran Connector SDK uses it to efficiently deliver data to our core platform.

There are two ways that yield can be used with a child function. See the examples below.

Let's assume we have a child function:

def child_function(data):
   for item in data:
      yield item

You can call the function from update() in two ways:

• Using yield from:

  def update(data):
     yield from child_function(data)
  

• Using yield: In this case, you need to iterate over the generation returned from child_function:

  def update(data):
     for item in child_function(data):
        yield item
  

Our pagination examples use the first approach.

Connecting to Duck DB

The Fivetran Local Tester saves the data sent to Fivetran by you connector code into a DuckDB warehouse.db file stored at the <project directory>/files/warehouse.db path:

Using Duck DB CLI

1. Install Duck DB CLI.

2. Run Duck DB CLI:

   duckdb files/warehouse.db
   

3. Once running, you can see the tables and their schema as well as query the warehouse with SQL:

   .tables
   .schema
   SELECT * FROM default_schema.hello_world;
   

4. To stop Duck DB, run the following command:

   .exit
   

Using DBeaver

1. Install DBeaver.

2. Connect to a database and select DuckDB.

   

3. Click Open to navigate to and select the warehouse.db file created by running fivetran debug.

   

4. Dbeaver may need to download updated drivers, then should then show that it is connected to the local warehouse.db file and enable you to explore it using regular SQL.

   

fivetran debug issue with open DBeaver connection

If you use fivetran debug while a connection is already open in DBeaver, the command doesn't work until the connection is closed. The command fails with the following error message:

IO Error: Could not set lock on file "/Users/john.smith/connector/files/warehouse.db": Conflicting lock is held in /Applications/DBeaver.app/Contents/MacOS/dbeaver (PID 28552) by user john.smith. 

This occurs because DuckDB does not support concurrent connections to the same database file, as explained in their concurrency documentation. To resolve this issue, close the connection in DBeaver before running fivetran debug.

Connector SDK logs

You can directly access your SDK connectors' logs using the Fivetran Platform Connector. Your connectors' logs are available in the CONNECTOR_SDK_LOG table within the associated Fivetran destination. The logs provide in-depth event data, including timestamps, log levels, and messages.