How to Select Columns to Sync and Block New Tables and Columns for New Connectors

Using our REST API, you can define a set of columns in newly-created connectors. You can also set up Schema Change Handling to automatically exclude any new columns and tables that appear in the source.

This guide refers only to database connectors and the following connectors, which behave similarly to databases:

Use our schema status guide for the Schema Change Handling setup for other connectors.

Create new connector

Create a connector in the paused state, using the Create a Connector endpoint.

Request

POST https://api.fivetran.com/v1/connectors

{
  "service": "postgres",
  "group_id": "your_group",
  "paused": "true",
  "config": {
    "schema_prefix": "your_prefix",
    "host": "localhost",
    "port": 7005,
    "user": "your_username",
    "password": "your_password",
    "database": "your_database",
    "update_method": "WAL",
    "replication_slot": "fivetran_replication_slot"
  }
}

NOTE: The actual values of the config section depend on the service you use.

Response

{
  "code": "Success",
  "message": "Connector has been created",
  "data": {
    "id": "culminating_dignitaries",
    "group_id": "your_group",
    "service": "postgres",
    "service_version": 3,
    "schema": "postgres_from_rest",
    "connected_by": "your_username",
    "created_at": "2021-07-29T09:51:03.338595Z",
    "succeeded_at": null,
    "failed_at": null,
    "paused": true,
    "pause_after_trial": false,
    "sync_frequency": 360,
    "schedule_type": "auto",
    "status": {
      "setup_state": "incomplete",
      "sync_state": "paused",
      "update_state": "on_schedule",
      "is_historical_sync": true,
      "tasks": [],
      "warnings": []
    },
    "Setup_tests": [{
        "title": "Validating certificate",
        "status": "FAILED",
        "message": "No valid or non-expired trust anchors for this connection. Found 0 expired and 0 invalid certificates."
      },
      {
        "title": "Connecting to SSH tunnel",
        "status": "SKIPPED",
        "message": ""
      },
      {
        "title": "Connecting to host",
        "status": "PASSED",
        "message": ""
      }
    ],
    "config": {
      "database": "your_database",
      "password": "********",
      "latest_version": "3",
      "port": "7005",
      "host": "localhost",
      "service_version": "3",
      "incremental_update_method": "WAL",
      "replication_slot": "fivetran_replication_slot",
      "user": "your_username"
    }
  }
}

(Optional) Do additional configuration

Depending on the service type, you may need to do some additional configuration. For example, for PostgreSQL, you may need to approve certificates.

Request

POST https://api.fivetran.com/v1/certificates

{
  "connector_id": "your_connector_id",
  "hash": "your_certificate_hash",
  "encoded_cert": "your_encoded_certificate"
}

Response

{
  "code": "Success",
  "message": "The certificate has been approved"
}

Reload schema config

Reload the schema config using the Reload a Connector Schema Config endpoint. In the payload, specify EXCLUDE as a value for exclude_mode. This excludes all schemas and tables from syncs.

Request

POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/reload

{
  "exclude_mode": "EXCLUDE"
}

Response

{
  "code": "Success",
  "data": {
    "schemas": {
      "public": {
        "name_in_destination": "public",
        "enabled": false,
        "tables": {
          "test_rows": {
            "name_in_destination": "test_rows",
            "enabled": false,
            "columns": {
              "id": {
                "name_in_destination": "id",
                "enabled": false,
                "hashed": false,
                "enabled_patch_settings": {
                  "allowed": true
                }
              },
              "name": {
                "name_in_destination": "name",
                "enabled": false,
                "hashed": false,
                "enabled_patch_settings": {
                  "allowed": true
                }
              }
            },
            "enabled_patch_settings": {
              "allowed": true
            }
          }
        }
      }
    },
    "schema_change_handling": "ALLOW_ALL"
  }
}

Review source schema

Review the source schema that was returned in the response from the previous step. Identify the schemas, tables, and columns that you want to sync.

NOTE: Some connectors do not provide the column information by default. If you don’t know which columns you are interested in, you can use the Retrieve Source Table Columns Config endpoint to get the column list.

Modify connector schema config

Perform a request to Modify a Connector Schema Config endpoint. Don’t specify the value for enable_new_by_default in the payload because this parameter is not relevant to the Schema Change Handling feature. Use schema_change_handling instead.

The possible values for the schema_change_handling parameter are as follows:

ALLOW_ALL - all new schemas, tables, and columns which appear in the source after the initial setup are included in syncs;
ALLOW_COLUMNS - all new schemas and tables which appear in the source after the initial setup are excluded from syncs, but new columns are included;
BLOCK_ALL - all new schemas, tables, and columns which appear in the source after the initial setup are excluded from syncs.

In this use case, you need to use BLOCK_ALL. For all objects that you want to be synced, use "enabled": "true".

IMPORTANT: If you enable some columns, you must enable both the table that contains these columns and the schema that contains this table.

Request

PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas

{
  "schema_change_handling": "BLOCK_ALL",
  "schemas": {
    "public": {
      "enabled": "true",
      "tables": {
        "test_rows": {
          "enabled": "true",
          "columns": {
            "name": {
              "enabled": "true"
            }
          }
        }
      }
    }
  }
}

Response

{
  "code": "Success",
  "data": {
    "schemas": {
      "public": {
        "name_in_destination": "public",
        "enabled": true,
        "tables": {
          "test_rows": {
            "name_in_destination": "test_rows",
            "enabled": true,
            "columns": {
              "id": {
                "name_in_destination": "id",
                "enabled": false,
                "hashed": false,
                "enabled_patch_settings": {
                  "allowed": true
                }
              },
              "name": {
                "name_in_destination": "name",
                "enabled": true,
                "hashed": false,
                "enabled_patch_settings": {
                  "allowed": true
                }
              }
            },
            "enabled_patch_settings": {
              "allowed": true
            }
          }
        }
      }
    },
    "schema_change_handling": "BLOCK_ALL"
  }
}

Unpause connector

Unpause the connector using the Modify a Connector endpoint, and then set the sync frequency and other sync parameters.

Request

PATCH https://api.fivetran.com/v1/connectors/{connector_id}

{
  "paused": "false",
  "sync_frequency": "1440",
  "daily_sync_time": "10:00"
}

Response

{
  "code": "Success",
  "message": "Connector has been updated",
  "data": {
    "id": "culminating_dignitaries",
    "group_id": "your_group",
    "service": "postgres",
    "service_version": 3,
    "schema": "postgres_from_rest",
    "connected_by": "your_username",
    "created_at": "2021-07-29T09:51:03.338595Z",
    "succeeded_at": null,
    "failed_at": null,
    "paused": false,
    "pause_after_trial": false,
    "sync_frequency": 1440,
    "daily_sync_time": "10:00",
    "schedule_type": "auto",
    "status": {
      "setup_state": "connected",
      "sync_state": "scheduled",
      "update_state": "on_schedule",
      "is_historical_sync": true,
      "tasks": [],
      "warnings": []
    }
  }
}

The Schema Change Handling setup is complete.