REST API supports the following connector management actions:
- Retrieve source metadata
- Retrieve connector configuration metadata
- Create a connector
- Retrieve connector details
- Modify a connector
- Sync connector data
- Re-sync Connector Data (Historical Sync)
- Re-sync connector table data
- Run connector setup tests
- Delete a connector
- Retrieve a connector schema config
- Retrieve source table columns config
- Reload a connector schema config
- Modify a connector schema config
- Modify a connector database schema config
- Modify a connector table config
- Modify a connector column config
- Use Case: How to select only the columns you want to sync and block new tables and columns for new connectors
- Use Case: How to enable sync only for columns that you need
- Use case: How to set a custom sync frequency
- Use case: How to set a time for your daily syncs
- Use case: How to trigger syncs manually only when you need them
Retrieving the exact names of schemas, tables, and columnslink
In order for the request to succeed, you must specify the correct parameters. When you call endpoints that require passing schema, table, or column names as path or payload parameters, specify the names exactly as they appear in the source.
IMPORTANT: Do not rely on the example requests and responses in our REST API documentation or on how the names appear in your destination. Fivetran transforms schema, table, and column names according to our naming conventions.
We recommend that you retrieve the exact schema, table, and column names of your connector before you call endpoints that require passing schema
, table
, or column
as path or payload parameters.
You can use either of the following methods:
-
In your Fivetran dashboard: Go to the Schema tab of your connector and use the schema, table, or column names displayed there. See the following example:
-
Using Fivetran REST API: Call the Retrieve a Connector Schema Config endpoint.
NOTE: The connector schemas define what tables and columns present in the source your connector syncs. Most Fivetran REST API endpoint requests and responses use the connector schema names as path or payload parameters. On the other hand, the destination schema that we use as a name of your connector defines how the data is stored in your destination. You specify the destination schema name either in the connector setup form or as a value of the
schema
payload parameter in the Create a Connector endpoint request when creating a connector.
Retrieve source metadatalink
Returns all available source types within your Fivetran account. This endpoint makes it easier to display Fivetran connectors within your application because it provides metadata including the proper source name (‘Facebook Ad Account’ instead of facebook_ad_account), the source icon, and links to Fivetran resources. As we update source names and icons, that metadata will automatically update within this endpoint.
Requestlink
GET https://api.fivetran.com/v1/metadata/connector-types
Query parameters
Name | Description |
---|---|
cursor |
The paging cursor. Read more about pagination in our documentation |
limit |
The number of records to fetch per page, accepts a number in the range 1…1000. The default value is 100 |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"items": [
{
"id": "facebook_ad_account",
"name": "Facebook Ad Account",
"type": "Marketing",
"description": "Facebook Ad Account provides attribute data on Facebook Ad Accounts",
"icon_url": "https://fivetran.com/integrations/facebook/resources/facebook.svg",
"link_to_docs": "https://fivetran.com/docs/applications/facebook-ad-account",
"link_to_erd": "https://fivetran.com/docs/applications/facebook-ad-account#schemainformation"
}
],
"next_cursor": "eyJza2lwIjoxMDB9"
}
}
Name | Description |
---|---|
items |
The collection of connector types describing their metadata. |
id |
The connector type identifier within the Fivetran system. |
name |
The connector service name within the Fivetran system. |
type |
The connector service type |
description |
The description characterizing the purpose of the connector. |
icon_url |
The icon resource URL |
link_to_docs |
The link to the connector documentation. |
link_to_erd |
The link to the connector ERD (entity–relationship diagram). |
next_cursor |
The value of the cursor parameter for the next page. |
Retrieve connector configuration metadata BETAlink
Returns metadata of configuration parameters and authorization parameters for a specified connector type.
Requestlink
GET api.fivetran.com/v1/metadata/connector-types/{connector_type}
Query parameters
Name | Description |
---|---|
connector_type |
The connector type identifier within the Fivetran system. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"id": "connector_type",
"name": "Service Display Name",
"type": "Marketing",
"description": "Service detailed description",
"icon_url": "https://fivetran.com/integrations/link/to/icon.svg",
"link_to_docs": "https://fivetran.com/docs/service-docs",
"link_to_erd": "https://fivetran.com/docs/link/to/service#schemainformation",
"auth": {
"type": "object",
"properties": {
"access_token": {
"type": "string",
"description": "The long-lived Access token carries the information necessary to access API resources."
},
...
}
}
"config": {
"type": "object",
"properties": {
"schema": {
"type": "string",
"description": "Destination schema. Schema is permanent and cannot be changed after connection creation"
},
"sync_mode": {
"description": "",
"one_of": [
{
"type": "string",
"description": "Automatically sync all existing and new client accounts",
"const": "AllAccounts"
},
{
"type": "string",
"description": "Select client accounts for sync",
"const": "SpecificAccounts"
},
{
"type": "string",
"description": "Select manager accounts whose clients you want to sync",
"const": "ManagerAccounts"
}
]
},
...
},
"required": [
"schema",
...
]
}
}
}
Name | Description |
---|---|
id |
The connector type identifier within the Fivetran system. |
name |
The connector service name within the Fivetran system. |
type |
The connector service type |
description |
The description characterizing the purpose of the connector. |
icon_url |
The icon resource URL |
link_to_docs |
The link to the connector documentation. |
link_to_erd |
The link to the connector ERD (entity–relationship diagram). |
auth |
Metadata for authorization fields (optional). |
config |
Metadata for configuration fields. |
Schema format is compatible with JSON-Schema Draft-06.
Create a Connectorlink
Creates a new connector within a specified group in your Fivetran account. Runs setup tests and returns testing results.
Requestlink
POST https://api.fivetran.com/v1/connectors
{
"service": "criteo",
"group_id": "projected_sickle",
"trust_certificates": true,
"run_setup_tests": true,
"paused": true,
"pause_after_trial": true,
"sync_frequency": 1440,
"daily_sync_time": "03:00",
"config": {
"schema": "criteo",
"username": "myuser",
"password": "mypassword",
"app_token": "myapptoken"
},
"auth": {
"client_access": {
"client_id": "my_client_id",
"client_secret": "my_client_secret"
},
"refresh_token": "my_refresh_token"
}
}
Payload parameters
Name | Description |
---|---|
group_id (required) |
The unique identifier for the group within the Fivetran system. Find you group_id by fetching your account’s group list. |
service (required) |
The name for the connector type within the Fivetran system. |
config (required) |
The connector setup configuration. The format is specific for each connector. |
auth |
The connector authorization settings. Can be used to authorize a connector using your external client credentials. The format is specific for each connector. |
paused |
Specifies whether the connector is paused. |
pause_after_trial |
Specifies whether the connector should be paused after the free trial period has ended. If this parameter is omitted in the request, then we use the default value false . |
trust_certificates |
Specifies whether we should trust the certificate automatically. Applicable only for database connectors. The default value is FALSE . If a certificate is not trusted automatically, it has to be approved with Certificates Management API Approve a connector certificate. |
trust_fingerprints |
Specifies whether we should trust the SSH fingerprint automatically. Applicable only for database connectors. The default value is FALSE . If a fingerprint is not trusted automatically, it has to be approved with Certificates Management API Approve a connector fingerprint. |
run_setup_tests |
Specifies whether the setup tests should be run automatically. The default value is TRUE . |
sync_frequency |
The connector sync frequency in minutes. The supported values are: 5 , 15 , 30 , 60 , 120 , 180 , 360 , 480 , 720 , 1440 . |
daily_sync_time |
The optional parameter that defines the sync start time when the sync frequency is already set or being set by the current request to 1440 . It can be specified in one hour increments starting from 00:00 to 23:00 . If not specified, we will use the baseline sync start time. This parameter has no effect on the 0 to 60 minutes offset used to determine the actual sync start time. |
Responselink
HTTP 201 Created
{
"code": "Success",
"message": "Connector has been created",
"data": {
"id": "speak_inexpensive",
"group_id": "projected_sickle",
"service": "criteo",
"service_version": 0,
"schema": "criteo",
"paused": true,
"pause_after_trial": true,
"connected_by": "interment_burdensome",
"created_at": "2018-12-01T15:43:29.013729Z",
"succeeded_at": null,
"failed_at": null,
"sync_frequency": 1440,
"daily_sync_time": "03:00",
"status": {
"setup_state": "incomplete",
"sync_state": "scheduled",
"update_state": "on_schedule",
"is_historical_sync": true,
"tasks": [],
"warnings": []
},
"setup_tests": [{
"title": "Validate Login",
"status": "FAILED",
"message": "Invalid login credentials"
}],
"config": {
"username": "myuser",
"password": "******",
"api_token": "******",
"service_version": "0"
}
}
}
If run_setup_tests
is set to FALSE
in the request, the response will not contain the setup_tests
field.
See how to create a new connector based on an existing connector’s parameters.
Note that the config
parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.
Retrieve Connector Detailslink
Returns a connector object if a valid identifier was provided.
Currently, we support two formats for the connector configuration response payload. The first is the default legacy format of the configuration. The second one is a new configuration format (v2) we introduced in January 2021.
IMPORTANT: All accounts created after September 9, 2021 receive the connector configuration response in the v2 format by default.
To retrieve the connector configuration info in the v2 format, you should add version=2
to the Accept
request header:
--header 'Accept: application/json;version=2'
The v2 config format is consistent with the configuration payload parameter used for Modifying a connector.
The connector configuration response depends on the connector type.
Below are examples of the legacy and new v2 connector configuration request and response that have different Accept
header values, respectively. These examples are based on the Google Ads connector:
Legacy request:
GET https://api.fivetran.com/v1/connectors/{connector_id} --header 'Accept: application/json'
Legacy response:
{
"code": "Success",
"data": {
...
"config": {
"sync_mode": "Selected Accounts",
"latest_version": "4",
"configuration": "Custom Report",
"accounts": "0987654321",
"report_type": "AD_PERFORMANCE_REPORT",
"service_version": "4",
"customer_id": "123-456-7890",
"fields": "PolicySummary, AdType, Date",
"conversion_window_size": 30,
"historical_sync_timeframe": "12 months"
}
}
}
New v2 request:
GET https://api.fivetran.com/v1/connectors/{connector_id} --header 'Accept: application/json;version=2'
New v2 response:
{
"code": "Success",
"data": {
...
"config": {
"sync_mode": "SpecificAccounts",
"customer_id": "123-456-7890",
"accounts": [
"0987654321"
],
"conversion_window_size": 30,
"report_type": "AD_PERFORMANCE_REPORT",
"fields": [
"PolicySummary",
"AdType",
"Date"
]
},
"source_sync_details": {
"accounts": [
"0987654321"
]
}
}
}
The exact set of fields we return when the source_sync_details
parameter is specified depends on the connector type. The source_sync_details
section is not included in the response if the parameter is omitted in the request. The source_sync_details
section in the above example contains the information about synced accounts.
We return sync details for the following connectors in V2 configuration mode:
- Asana
- Facebook Ads
- Facebook Ad Insights
- Google Ads
- Google Analytics
- Google Analytics (MCF)
- Google Campaign Manager 360
- Google Sheets
- Microsoft Advertising
- Salesforce
Asana source_sync_details
fields
Name | Description |
---|---|
projects |
Map of project cursors representing synced projects. |
Facebook Ads source_sync_details
fields
Name | Description |
---|---|
account_ids |
List of synced accounts. |
Facebook Ad Insights source_sync_details
fields
Name | Description |
---|---|
account_ids |
List of synced accounts. |
Google Ads source_sync_details
fields
Name | Description |
---|---|
accounts |
List of synced accounts. |
Google Analytics source_sync_details
fields
Name | Description |
---|---|
profile_ids |
List of synced profiles. |
Google Analytics MCF source_sync_details
fields
Name | Description |
---|---|
profile_ids |
List of synced profiles. |
Google Campaign Manager 360 source_sync_details
fields
Name | Description |
---|---|
user_profiles |
List of synced user profiles. |
Google Sheets source_sync_details
fields
Name | Description |
---|---|
last_synced |
Last sync time. |
Microsoft Advertising source_sync_details
fields
Name | Description |
---|---|
account_ids |
List of synced accounts. |
Salesforce source_sync_details
fields
Name | Description |
---|---|
connecting_user_email |
Salesforce user email. |
organization_id |
Salesforce organization ID. |
username |
Salesforce user name. |
Requestlink
GET https://api.fivetran.com/v1/connectors/{connector_id}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"id": "connector_id",
"group_id": "group_id",
"service": "adwords",
"service_version": 4,
"schema": "adwords.schema",
"paused": true,
"pause_after_trial": true,
"connected_by": "monitoring_assuring",
"created_at": "2020-03-11T15:03:55.743708Z",
"succeeded_at": "2020-03-17T12:31:40.870504Z",
"failed_at": "2021-01-15T10:55:00.056497Z",
"sync_frequency": 360,
"schedule_type": "auto",
"status": {
"setup_state": "broken",
"sync_state": "scheduled",
"update_state": "delayed",
"is_historical_sync": false,
"tasks": [
{
"code": "reconnect",
"message": "Reconnect"
}
],
"warnings": []
},
"config": {
"sync_mode": "SpecificAccounts",
"customer_id": "XXX-XXX-XXXX",
"accounts": [
"1234567890"
],
"conversion_window_size": 30,
"report_type": "AD_PERFORMANCE_REPORT",
"fields": [
"PolicySummary",
"AdType",
"Date"
]
},
"source_sync_details": {
"accounts": [
"1234567890"
]
}
}
}
Fields
Name | Description |
---|---|
id |
The unique identifier for the connector within the Fivetran system. |
group_id |
The unique identifier for the group within the Fivetran system. |
service |
The connector type name within the Fivetran system. |
service_version |
The connector type version within the Fivetran system. |
schema |
The name used both as the connector’s name within the Fivetran system and as the source schema’s name within your destination. |
paused |
Specifies whether the connector is paused. |
pause_after_trial |
Specifies whether the connector should be paused after the free trial period has ended. |
connected_by |
The unique identifier of the user who has created the connector in your account. |
created_at |
The timestamp of the time the connector was created in your account. |
succeeded_at |
The timestamp of the time the connector sync succeeded last time. |
failed_at |
The timestamp of the time the connector sync failed last time. |
sync_frequency |
The connector sync frequency in minutes. |
daily_sync_time |
The connector daily sync start time that we return only when the sync frequency is set to 1440 (which means 24 hours) and the daily_sync_time parameter was set using the Create a Connector or Modify a Connector request. |
schedule_type |
The connector schedule configuration type. Supported values: auto , manual . |
status.setup_state |
The current setup state of the connector. The available values are:incomplete - the setup config is incomplete, the setup tests never succeeded;connected - the connector is properly set up;broken - the connector setup config is broken. |
status.sync_state |
The current sync state of the connector. The available values are:scheduled - the sync is waiting to be run;syncing - the sync is currently running;paused - the sync is currently paused;rescheduled - the sync is waiting until more API calls are available in the source service. |
update_state |
The current data update state of the connector. The available values are:on_schedule - the sync is running smoothly, no delays;delayed - the data is delayed for a longer time than expected for the update. |
is_historical_sync |
The boolean value specifying whether the connector is at the historical data sync stage (also true for resyncing connectors). |
tasks |
The collection of tasks for the connector. |
warnings |
The collection of warnings for the connector. |
config |
The connector setup configuration. The format is specific for each connector. |
source_sync_details |
(Only for the new v2 configuration format) The additional information about the connector’s state. The format of this parameter is specific for each connector type. |
Modify a Connectorlink
Updates the information for an existing connector within your Fivetran account.
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}
{
"paused": false,
"pause_after_trial": true,
"sync_frequency": 60,
"trust_certificates": true,
"run_setup_tests": true,
"config": {
"username": "newuser",
"password": "newpassword"
},
"auth": {
"client_access": {
"client_id": "my_client_id",
"client_secret": "my_client_secret"
},
"refresh_token": "my_refresh_token"
},
"schedule_type": "manual"
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload parameters
Name | Description |
---|---|
paused |
The boolean specifying whether the connector should be paused |
pause_after_trial |
Specifies whether the connector should be paused after the free trial period has ended. |
sync_frequency |
The connector sync frequency in minutes. The supported values are: 5 , 15 , 30 , 60 , 120 , 180 , 360 , 480 , 720 , 1440 . |
daily_sync_time |
The optional parameter that defines the sync start time when the sync frequency is already set or being set by the current request to 1440 . It can be specified in one hour increments starting from 00:00 to 23:00 . If not specified, we will use the baseline sync start time. This parameter has no effect on the 0 to 60 minutes offset used to determine the actual sync start time. |
config |
The connector setup configuration. The format is specific for each connector. |
auth |
The connector authorization settings. Can be used to authorize a connector using your external client credentials. The format is specific for each connector. |
trust_certificates |
Specifies whether we should trust the certificate automatically. Applicable only for database connectors and when the config section is present and defined. The default value is FALSE . If a certificate is not trusted automatically, it has to be approved using the Certificates Management API Approve a connector certificate. |
trust_fingerprints |
Specifies whether we should trust the SSH fingerprint automatically. Applicable only for database connectors. The default value is FALSE . If a fingerprint is not trusted automatically, it has to be approved with Certificates Management API Approve a connector fingerprint. |
is_historical_sync |
The boolean specifying whether the connector should be triggered to re-sync all historical data. If you set this parameter to TRUE , the next scheduled sync will be historical. If the value is FALSE or not specified, the connector will not re-sync historical data.NOTE: When the value is TRUE , only the next scheduled sync will be historical, all subsequent ones will be incremental. This parameter is set to FALSE once the historical sync is completed. |
schedule_type |
The connector schedule config type. Supported values: auto , manual . Lets you disable or enable an automatic data sync on a schedule. If you set this parameter to manual , the automatic data sync will be disabled, but you will be able to trigger the data sync using the Sync Connector Data endpoint. |
run_setup_tests |
Specifies whether the setup tests should be run automatically. The default value is TRUE . |
None of the paused
, sync_frequency
, daily_sync_time
, config
, auth
, is_historical_sync
, schedule_type
parameters is required, but at least one of them has to be present and defined for a request of this type to succeed.
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Connector has been updated",
"data": {
"id": "speak_inexpensive",
"group_id": "projected_sickle",
"service": "criteo",
"service_version": 0,
"schema": "criteo",
"paused": true,
"pause_after_trial": true,
"connected_by": "interment_burdensome",
"created_at": "2018-12-01T15:43:29.013729Z",
"succeeded_at": null,
"failed_at": null,
"sync_frequency":60,
"status": {
"setup_state": "incomplete",
"sync_state": "scheduled",
"update_state": "on_schedule",
"is_historical_sync": true,
"tasks": [],
"warnings": []
},
"setup_tests": [{
"title": "Validate Login",
"status": "FAILED",
"message": "Invalid login credentials"
}],
"config": {
"username": "newuser",
"password": "******",
"api_token": "******",
"service_version": "0"
}
}
}
If the run_setup_tests
parameter is set to FALSE
in the request, then the response will not contain the setup_tests
field.
Note that the config
parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.
Sync Connector Datalink
Triggers a data sync for an existing connector within your Fivetran account without waiting for the next scheduled sync. This action does not override the standard sync frequency you defined in the Fivetran dashboard.
If there is a data sync already in progress, that sync is immediately stopped and restarted. If the connector is paused, the data sync will be scheduled to be performed when the connector is re-enabled.
Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/sync
{
"force": true
}
Payload parameters
Name | Description |
---|---|
force |
If force is true and the connector is currently syncing, it will stop the sync and re-run it. If force is false , the connector will sync only if it isn’t currently syncing. The default value is false . |
NOTE: If connector is paused it won’t be triggered to sync.
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload
The request body can be empty, since the payload is optional. If the payload wasn’t passed and the connector is currently syncing, nothing would be performed.
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Sync has been successfully triggered for connector with id 'connector_id1'"
}
Legacy Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/force
NOTE: We renamed this endpoint and now use
sync
as an action term to make our API contract match the terms on the Fivetran dashboard. This legacy endpoint is available for backward compatibility.
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload
The request body must be empty.
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Sync has been successfully triggered for connector with id 'connector_id1'"
}
Re-sync Connector Data (Historical Sync)link
Triggers a full historical sync of a connector or multiple schema tables within a connector.
Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/resync
{
"scope": {
"schema1": ["table1", "table2"],
"schema2": ["table3", "table4", "table5"],
...,
"schemaN": ["table1", "table2", ..., "tableM"]
}
}
Path parameterslink
Name | Description |
---|---|
connector_id |
The unique identifier for the connector within the Fivetran system. |
Payload
Call the endpoint without any payload to trigger a full historical re-sync for the connector.
The scope
payload parameter is optional. It defines the scope of re-sync request. If you provide the scope
parameter in the payload, the endpoint behaves exactly like the Re-sync connector table data endpoint.
NOTE: If you provide the
scope
parameter, it cannot be empty.
Payload parameters
Name | Description |
---|---|
scope |
A map containing an array of tables to re-sync for each schema, must be non-empty. The parameter is optional. |
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Re-sync has been triggered successfully"
}
Re-sync Connector Table Datalink
Triggers a historical sync of all data for multiple schema tables within a connector. This action does not override the standard sync frequency you defined in the Fivetran dashboard.
If the connector is paused, the table sync will be scheduled to be performed when the connector is re-enabled. If there is a data sync already in progress, we will try to complete it. If it fails, the request will be declined and the HTTP 409 Conflict error will be returned.
NOTE: If you want to trigger a historical sync for the entire connector, use the Re-sync Connector Data (Historical Sync) endpoint instead.
Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/tables/resync
{
"schema1": ["table1", "table2"],
"schema2": ["table3", "table4", "table5"],
...,
"schemaN": ["table1", "table2", ..., "tableM"]
}
Payload
Payload parameters contain an array of tables for each schema.
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
schema (required) |
The database schema name within your destination. NOTE: The database schema is a different entity from the connector schema. |
table (required) |
The table name within your database schema. |
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Re-sync has been triggered successfully"
}
Run connector setup testslink
Runs the setup tests for an existing connector within your Fivetran account.
Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/test
{
"trust_certificates": true,
"trust_fingerprints": true
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload parameters
Name | Description |
---|---|
trust_certificates |
Specifies whether we should trust the certificate automatically. Applicable only for database connectors. The default value is FALSE . If a certificate is not trusted automatically, it has to be approved using the Certificates Management API Approve a connector certificate. |
trust_fingerprints |
Specifies whether we should trust the SSH fingerprint automatically. Applicable only for database connectors. The default value is FALSE . If a fingerprint is not trusted automatically, it has to be approved with Certificates Management API Approve a connector fingerprint. |
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Setup tests were completed",
"data": {
"id": "speak_inexpensive",
"group_id": "projected_sickle",
"service": "criteo",
"service_version": 0,
"schema": "criteo",
"paused": false,
"pause_after_trial": true,
"connected_by": "interment_burdensome",
"created_at": "2018-12-01T15:43:29.013729Z",
"succeeded_at": null,
"failed_at": null,
"sync_frequency":60,
"status": {
"setup_state": "incomplete",
"sync_state": "scheduled",
"update_state": "on_schedule",
"is_historical_sync": true,
"tasks": [],
"warnings": []
},
"setup_tests": [{
"title": "Validate Login",
"status": "FAILED",
"message": "Invalid login credentials"
}],
"config": {
"username": "newuser",
"password": "******",
"api_token": "******",
"service_version": "0"
}
}
}
Note that the config
parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.
Delete a Connectorlink
Deletes a connector from your Fivetran account.
Requestlink
DELETE https://api.fivetran.com/v1/connectors/{connector_id}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Responselink
HTTP 200 OK
{
"code": "Success",
"message": "Connector with id 'speak_inexpensive' has been deleted"
}
Retrieve a Connector Schema Configlink
Returns the connector schema config for an existing connector within your Fivetran account.
Requestlink
GET https://api.fivetran.com/v1/connectors/{connector_id}/schemas
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": true,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"name_in_destination": "column_1",
"column_1": {
"enabled": false
}
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "OTHER",
"reason": "Permission denied"
}
}
}
}
}
}
}
Fields
Name | Description |
---|---|
enable_new_by_default |
The boolean value specifying whether to enable new schemas and tables by default. |
schema.name_in_destination |
The schema name within your destination in accordance with Fivetran conventional rules. |
schema.enabled |
The boolean value specifying whether the sync for the schema into the destination is enabled. |
table.name_in_destination |
The table name within your destination in accordance with Fivetran conventional rules. |
table.enabled |
The boolean value specifying whether the sync of table into the destination is enabled. |
table.enabled_patch_settings.allowed |
The boolean value specifying whether the table.enabled property can be modified. |
table.enabled_patch_settings.reason_code |
The reason code indicating the reason why the table.enabled property cannot be modified: SYSTEM_TABLE - the table is a system table; DELETED - the table was deleted in the source; OTHER - the table was excluded by the system for some reason. This field appears in the response when the table.enabled_patch_settings.allowed property value is FALSE . |
table.enabled_patch_settings.reason |
The additional message indicating the reason why the table.enabled property cannot be modified. This field appears in the response only when the table.enabled_patch_settings.reason_code value is OTHER . |
column.name_in_destination |
The column name within your destination in accordance with Fivetran conventional rules. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
column.enabled_patch_settings.allowed |
The boolean value specifying whether the column.enabled property can be modified. |
column.enabled_patch_settings.reason_code |
The reason code indicating the reason why the column.enabled property cannot be modified: SYSTEM_COLUMN - the column is a system column; DELETED - the column was deleted in the source; OTHER - the column was excluded by the system for some reason. |
column.enabled_patch_settings.reason |
The additional message indicating the reason why the column.enabled property cannot be modified. This field only appears in the response when the column.enabled_patch_settings.reason_code property value is SYSTEM_COLUMN or OTHER . |
column.hashed |
The boolean value specifying whether a column is hashed or not. |
The response contains the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Connector Schema Configlink
The connector schema config stores the settings for schema, table, and column objects, and allows you to enable or disable it. If a schema, table, or column is enabled, Fivetran syncs the data for those objects to your destination.
A newly created connector doesn’t have a schema config. Fivetran generates the schema config after the initial connector sync or after triggering the Reload a connector schema config endpoint. The config generated by default contains only schemas and tables.
There are the following possible modes depending on the enable_new_by_default
flag:
enable_new_by_default = true
- (allow all) new schemas and tables that appear in the connector source are added to the config in the enabled state.enable_new_by_default = false
- (allow columns) new schemas and tables that appear in the connector source are added to the config in the disabled state.
Information about schemas and tables is always present in the config, but if the source produces new columns, they are not excluded from sync by default for both modes described above. All columns under enabled schemas and tables are considered to be enabled even though they are not present in the config.
If you need to block a particular column from sync you will have to PATCH the connector config. The patched column is guaranteed to be returned to the API response no matter column is enabled or disabled.
Let’s have a look at examples of different use-cases:
Event 1
You created a new connector and triggered the Reload a connector schema config endpoint.
Suppose a connector source contains schema_1
and table_1
with column_1
and column_2
.
The connector generates the following default config:
{
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"enabled": true,
"hashed": false,
"tables": {
"table_1": {
"enabled": true
}
}
}
}
}
Event 2
A new table, table_2
with column_1
, column_2
appears in the source.
What happens after the next connector sync:
The table_2
is added to the config in the enabled state.
column_1
, column_2
of table_2
are not added to the config.
Data for column_1
, column_2
of table_1
is synced to the destination.
Data for column_1
, column_2
of table_2
is synced to the destination.
{
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"enabled": true,
"tables": {
"table_1": {
"enabled": true
},
"table_2": {
"enabled": true
}
}
}
}
}
Event 3
PATCH
the connector config to disable the enable_new_by_default = false
property
{
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"enabled": true
"tables": {
"table_1": {
"enabled": true
},
"table_2": {
"enabled": true
}
}
}
}
}
Event 4
A new table, table_3
, with column_1
, column_2
appears in the source.
What happens after the next connector sync:
table_3
is added to the config in the disabled state.
column_1
, column_2
of table_3
are not added to the config.
Data for column_1
, column_2
of table_1
is synced to the destination.
Data for column_1
, column_2
of table_2
is synced to the destination.
Data for table_3
with all its columns is blocked and excluded from sync.
{
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"enabled": true,
"tables": {
"table_1": {
"enabled": true
},
"table_2": {
"enabled": true
},
"table_3": {
"enabled": false
}
}
}
}
}
Event 5
PATCH
the connector config to disable column_1
of table_1
{
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"enabled": true,
"hashed": false,
"tables": {
"table_1": {
"enabled": true,
"columns": {
"column_1": {
"enabled": false,
"hashed": false
}
}
},
"table_2": {
"enabled": true
},
"table_3": {
"enabled": false
}
}
}
}
}
Event 6
New column_3
of table_1
appears in the source.
What happens after the next connector sync:
column_3
of table_1
is not added to the config.
Data for column_2
, column_3
of table_1
is synced to the destination.
Data for column_1
of table_1
is blocked and excluded from sync.
Data for column_1
, column_2
of table_2
is synced to the destination.
Data for table_3
with all its columns is blocked and excluded from sync.
{
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"enabled": true,
"hashed": false,
"tables": {
"table_1": {
"enabled": true,
"columns": {
"column_1": {
"enabled": false,
"hashed": false
}
}
},
"table_2": {
"enabled": true
},
"table_3": {
"enabled": false
}
}
}
}
}
Retrieve Source Table Columns Configlink
Returns the source table columns config for an existing connector within your Fivetran account.
Requestlink
GET https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}/columns
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
schema (required) |
The database schema name within your source. NOTE: The database schema is a different entity from the connector schema. |
table (required) |
The table name within your source schema. |
Responselink
HTTP 200 OK
{
"code": "Success",
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": false,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
The response contains an array of columns with the Fivetran sync settings for each column.
Fields
Name | Description |
---|---|
name_in_destination |
The column name within your destination in accordance with Fivetran conventional rules. |
enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
hashed |
The boolean value specifying whether a column should be hashed. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
enabled_patch_settings.allowed |
The boolean value specifying whether the column.enabled property can be modified. |
enabled_patch_settings.reason_code |
The reason code indicating the reason why the column.enabled property cannot be modified: SYSTEM_COLUMN - the column is a system column; DELETED - the column was deleted in the source; OTHER - the column was excluded by the system for some reason. This field appears in the response when the enabled_patch_settings.allowed field value is FALSE . |
Fetching the data may take long time because we interact with the source. The response time depends on the connection to the source.
Reload a Connector Schema Configlink
Reloads the connector schema config for an existing connector within your Fivetran account.
We advise that you use this method if the connector was recently created or its schema is outdated.
Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/reload
{
"exclude_mode":"PRESERVE"
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload
Name | Description | Possible Values |
---|---|---|
exclude_mode |
Specifies whether all schemas and tables will be enabled or disabled in the standard config. NOTE: This does not exclude columns by default. | EXCLUDE , INCLUDE and PRESERVE . The default value is PRESERVE . |
The request body can be empty because it contains only one optional parameter exclude_mode
that has value PRESERVE
set by default.
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": false,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": true,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": false
}
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "OTHER",
"reason": "Permission denied"
}
}
}
}
}
}
}
Fields
Name | Description |
---|---|
enable_new_by_default |
The boolean value specifying whether to enable new schemas and tables by default. |
schemas |
The set of schemas within your connector schema config that will be synced into the destination. |
schema.name_in_destination |
The schema name within your destination in accordance with Fivetran conventional rules. |
schema.enabled |
The boolean value specifying whether the sync of the schema into the destination is enabled. |
tables |
The set of tables within your database schema config that will be synced into the destination. |
table.name_in_destination |
The table name within your destination in accordance with Fivetran conventional rules. |
table.enabled |
The boolean value specifying whether the sync of table into the destination is enabled. |
table.enabled_patch_settings.allowed |
The boolean value specifying whether the property table.enabled can be modified. |
table.enabled_patch_settings.reason_code |
The reason code indicating the reason why the property table.enabled cannot be modified: SYSTEM_TABLE - the table is a system table; DELETED - the table was deleted in the source; OTHER - the table was excluded by the system for some reason. This field only appears in the response when the table.enabled_patch_settings.allowed property value is FALSE . |
table.enabled_patch_settings.reason |
The additional message indicating the reason why the property table.enabled cannot be modified. This field only appears in the response when the table.enabled_patch_settings.reason_code property value is OTHER . |
columns |
The set of columns within your table schema config that will be synced into the destination. |
column.name_in_destination |
The column name within your destination in accordance with Fivetran conventional rules. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
column.enabled_patch_settings.allowed |
The boolean value specifying whether the column.enabled property can be modified. |
column.enabled_patch_settings.reason_code |
The reason code indicating the reason why the column.enabled property cannot be modified: SYSTEM_COLUMN - the column is a system column; DELETED - the column was deleted in the source; OTHER - the column was excluded by the system for some reason. |
column.enabled_patch_settings.reason |
The additional message indicating the reason why the column.enabled property cannot be modified. This field only appears in the response when the column.enabled_patch_settings.reason_code property value is SYSTEM_COLUMN or OTHER . |
column.hashed |
The boolean value specifying whether the column is hashed or not. |
This method reloads the full schema from the connector’s data source. It may take a long time to complete the request. The method execution speed depends on the schema size and the number of databases, tables and columns. To retrieve the connector schema config without reload, use the Retrieve a Connector Schema Config endpoint.
By specifying the exclude_mode
parameter value, you can enable or disable syncing for all schemas and tables when reloading the connector schema config.
When the default PRESERVE
value is set, all selected schemas and tables in the standard config preserve their state.
When the EXCLUDE
value is set, the columns that haven’t been modified by user yet are not excluded from the sync. Such columns may be present in the source and not displayed in the response, but they are considered to be included in the sync by default. If you enable syncing of their table, then all of these columns from the source will be synced in the next sync cycle. See also Use Case: How to enable sync only for columns that you need.
The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Modify a Connector Schema Configlink
Updates the schema config for an existing connector within your Fivetran account (for a single schema for a connector with multiple schemas).
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas
{
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"enabled": true,
"tables": {
"table_1": {
"enabled": true
},
"table_2": {
"enabled": false,
"columns": {
"column_2": {
"enabled": true,
"hashed": false
},
"column_3": {
"hashed": true
}
}
}
}
},
"schema_2": {
"enabled": false
}
}
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
Payload parameters
Name | Description |
---|---|
enable_new_by_default |
The boolean value specifying whether to enable new schemas, tables, and columns by default. |
schema.enabled |
The boolean value specifying whether the sync for the schema into the destination is enabled. |
table.enabled |
The boolean value specifying whether the sync of table into the destination is enabled. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
column.hashed |
The boolean value specifying whether a column should be hashed. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": false,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
}
}
}
}
The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Modify a Connector Database Schema Configlink
Updates the database schema config for an existing connector within your Fivetran account (for a single schema within a connector with multiple schemas).
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}
{
"enabled": true,
"tables": {
"table_1": {
"enabled": true
},
"table_2": {
"enabled": false,
"columns": {
"column_2": {
"enabled": true,
"hashed": false
},
"column_3": {
"hashed": true
}
}
}
}
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
schema (required) |
The database schema name within your destination (different from the connector schema). |
Payload parameters
Name | Description |
---|---|
enabled |
The boolean value specifying whether the sync for the schema into the destination is enabled. |
table.enabled |
The boolean value specifying whether the sync of table into the destination is enabled. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
column.hashed |
The boolean value specifying whether the column should be hashed. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": false,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
}
}
}
}
The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Modify a Connector Table Configlink
Updates the table config within your database schema for an existing connector within your Fivetran account.
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}
{
"enabled": false,
"columns": {
"column_2": {
"enabled": true,
"hashed": false
},
"column_3": {
"hashed": true
}
}
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
schema (required) |
The database schema name within your destination (different from the connector schema). |
table (required) |
The table name within your database schema. |
Payload parameters
Name | Description |
---|---|
enabled |
The boolean value specifying whether the sync for the table into the destination is enabled. |
column.enabled |
The boolean value specifying whether the sync of the column into the destination is enabled. |
column.hashed |
The boolean value specifying whether the column should be hashed. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": false,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
}
}
}
}
The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Modify a Connector Column Configlink
Updates the column config within your table for an existing connector within your Fivetran account.
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}/columns/{column}
{
"enabled": true,
"hashed": false
}
Path parameters
Name | Description |
---|---|
connector_id (required) |
The unique identifier for the connector within the Fivetran system. |
schema (required) |
The database schema name within your destination (different from the connector schema). |
table (required) |
The table name within your database schema. |
column (required) |
The column name within your table. |
Payload parameters
Name | Description |
---|---|
enabled |
The boolean value specifying whether the sync for the column into the destination is enabled. |
hashed |
The boolean value specifying whether a column should be hashed. |
Responselink
HTTP 200 OK
{
"code": "Success",
"data": {
"enable_new_by_default": true,
"schemas": {
"schema_1": {
"name_in_destination": "schema_1",
"enabled": true,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": true
},
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_COLUMN",
"reason": "The column does not support exclusion as it is a Primary Key"
}
},
"column_2": {
"name_in_destination": "column_2",
"enabled": true,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
},
"column_3": {
"name_in_destination": "column_3",
"enabled": true,
"hashed": true,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
}
},
"schema_2": {
"name_in_destination": "schema_2",
"enabled": false,
"tables": {
"table_1": {
"name_in_destination": "table_1",
"enabled": true,
"enabled_patch_settings": {
"allowed": true
}
},
"table_2": {
"name_in_destination": "table_2",
"enabled": false,
"enabled_patch_settings": {
"allowed": false,
"reason_code": "SYSTEM_TABLE"
}
}
}
}
}
}
}
The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.
Use Case: How to select only the columns you want to sync and block new tables and columns for new connectorslink
Using our REST API, you can define a set of columns in newly created connectors that you want to sync and set up the Schema Change Handling in such a way that all new columns and tables that may appear in the source in the future are excluded from syncs.
-
Create a connector in the paused state, using the Create a Connector endpoint.
Requestlink
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.Responselink
{ "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" } } }
-
Depending on the service type, you may need to do some additional configuration. For example, for PostgreSQL, you may need to approve certificates.
Requestlink
POST https://api.fivetran.com/v1/certificates
{ "connector_id": "your_connector_id", "hash": "your_certificate_hash", "encoded_cert": "your_encoded_certificate" }
Responselink
{ "code": "Success", "message": "The certificate has been approved" }
-
Reload the schema config using the Reload a Connector Schema Config endpoint and specify
EXCLUDE
as the payload. This excludes all schemas and tables from syncs.Requestlink
POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/reload
{ "exclude_mode": "EXCLUDE" }
Responselink
{ "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 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.
-
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. Useschema_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.
Requestlink
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" } } } } } } }
Responselink
{ "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 the connector, then set the sync frequency and other sync parameters.
Requestlink
PATCH https://api.fivetran.com/v1/connectors/{connector_id}
{ "paused": "false", "sync_frequency": "1440", "daily_sync_time": "10:00" }
Responselink
{ "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.
Use Case: How to enable sync only for columns that you needlink
The connector standard config only stores the information on the difference between the schema config in the source and the changes made to it by the user. To clarify this, let’s have a look at this example:
Requestlink
GET https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1/columns
Responselink
HTTP 200 OK
{
"code": "Success",
"columns": {
"column_1": {
"name_in_destination": "column_1",
"enabled": false,
"hashed": false,
"enabled_patch_settings": {
"allowed": true
}
}
}
}
This example shows that we have only one disabled sync for column_1
, but it does not necessarily mean we do not have other columns in the source.
For example, the source might have column_2
and column_3
in this table. Syncing is enabled for all table columns by default, they will be synced in the next connector sync cycle.
Assume we want to enable sync only for column column_3
.
First of all, to exclude other unwanted columns you need to know all column names of your table:
-
We can use the Retrieve source table columns config endpoint for this purpose.
Requestlink
GET https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1/columns
Responselink
HTTP 200 OK
{ "code": "Success", "columns": { "column_1": { "name_in_destination": "column_1", "enabled": false, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "column_2": { "name_in_destination": "column_2", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "column_3": { "name_in_destination": "column_3", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } } } }
-
Once we know column names, we can explicitly exclude from the sync the columns we do not want to be synced and include the ones we need through the Modify a connector table config endpoint.
Requestlink
PATCH https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1
{ "enabled": true, "columns": { "column_1": { "enabled": false, "hashed": false }, "column_2": { "enabled": false, "hashed": false }, "column_3": { "enabled": true, "hashed": false } } }
Responselink
HTTP 200 OK
{ "code": "Success", "data": { "enable_new_by_default": false, "schemas": { "schema_1": { "name_in_destination": "schema_1", "enabled": true, "tables": { "table_1": { "name_in_destination": "table_1", "enabled": true, "enabled_patch_settings": { "allowed": true }, "columns": { "column_1": { "name_in_destination": "column_1", "enabled": false, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "column_2": { "name_in_destination": "column_2", "enabled": false, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "column_3": { "name_in_destination": "column_3", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } } } } } } } } }
Use case: How to set a custom sync frequencylink
You can override Fivetran’s default sync frequency for a connector. You set your custom sync frequency by specifying your chosen time interval between syncs in minutes as a value of the sync_frequency
parameter in the Modify a Connector endpoint request.
Assume we want to set a custom frequency for connector connector_id_1
to be synced once every hour.
PATCH https://api.fivetran.com/v1/connectors/connector_id_1
{
"sync_frequency": 60
}
To learn how the sync scheduling works in Fivetran, read our sync frequency and scheduling documentation.
Use case: How to set a time for your daily syncslink
You can choose what time your daily syncs start if you don’t want to use Fivetran’s default midnight starting time.
Assume we want to set a 03:00
starting time for connector connector_id_1
.
There are two ways to do this:
-
Make sure that
connector_id_1
already has its sync frequency set to1440
(expressed in minutes, which makes 24 hours) and send the following request:PATCH https://api.fivetran.com/v1/connectors/connector_id_1
{ "daily_sync_time": "03:00" }
-
Set both the
sync_frequency
anddaily_sync_time
parameters in one request:PATCH https://api.fivetran.com/v1/connectors/connector_id_1
{ "sync_frequency": 1440, "daily_sync_time": "03:00" }
Use case: How to trigger syncs only when you need themlink
You can enable manual sync scheduling and trigger data syncs only when you want them.
To enable manual sync scheduling for a connector, specify manual
as a value of the schedule_type
parameter using the Modify a Connector endpoint. The manual sync scheduling ignores the set sync_frequency
.
IMPORTANT: You need to manually trigger your syncs after you make this change.
Assume we want to enable manual sync scheduling for connector connector_id_1
and trigger its sync.
-
Use the example request below to switch your connector’s
schedule_type
tomanual
:PATCH https://api.fivetran.com/v1/connectors/connector_id_1
{ "schedule_type": "manual", "run_setup_tests": false }
-
Next, use the Sync Connector Data endpoint to trigger a data sync according to your own schedule. Alternatively, you can trigger a sync in your Fivetran dashboard.
POST https://api.fivetran.com/v1/connectors/connector_id_1/force
NOTE: The payload for this request should be empty.