schema_configs

Creates, updates, deletes, gets or lists a schema_configs resource.

Overview

Name	`schema_configs`
Type	Resource
Id	`fivetran.connections.schema_configs`

Fields

The following fields are returned by SELECT queries:

Name	Datatype	Description
`enable_new_by_default`	`boolean`	The boolean value specifying whether to enable new schemas, tables, and columns by default
`row_filtering_supported`	`boolean`	A boolean value that specifies whether row filtering is available for the tables in this connection. It is `true` only when the row filtering feature is enabled for the connection, the connector type supports row filtering. It is `false` when the connector type does not support row filtering. This field is omitted from the response when the row filtering feature is not enabled for the connection.
`schema_change_handling`	`string`	The possible values for the schema_change_handling parameter are as follows: <br /> - `ALLOW_ALL` - all new schemas, tables, and columns which appear in the source after the initial setup are included in syncs <br /> - `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 <br /> - `BLOCK_ALL` - all new schemas, tables, and columns which appear in the source after the initial setup are excluded from syncs (ALLOW_ALL, ALLOW_COLUMNS, BLOCK_ALL) (example: ALLOW_ALL)
`schemas`	`object`	The set of schemas within your connection schema config. Each key is the schema name as stored in the connection schema config. Schema names are case-sensitive; an incorrect case results in an HTTP 404 error. (title: Schemas)

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Description
`get`	`select`	`connection_id`	<br />Returns the top-level schema configuration for an existing connection within your Fivetran account. The response includes global flags, every schema, each table, and only the columns that were explicitly overridden. <br /><br />Use this endpoint to read the current data-selection tree for a connection, to back up the schema before making edits, or to copy the configuration to another connection.<br /><br />> NOTE: To restore a backed-up schema or copy the configuration to another connection, use the [Update a Connection Schema Config](https://fivetran.com/docs/rest-api/api-reference/connection-schema/modify-connection-schema-config) endpoint.<br /><br />For more information, see the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial.<br /><br />> NOTE: Unedited columns (those following table defaults) are omitted from the response. For a read-only cataloging workflow, walk this response from schemas to tables, inspect each table's `supports_columns_config` field, and call the [Retrieve Source Table Columns Config](https://fivetran.com/docs/rest-api/api-reference/connection-schema/connection-column-config) endpoint only for tables where that field is `true`.<br /><br />For the NetSuite SuiteAnalytics, and Salesforce and Salesforce Sandbox connectors, the 'schemas' map field contains a single entry with the 'netsuite' or 'salesforce' key, respectively. For the 'schema.name_in_destination` name field, these connectors always return the destination schema name you set in the connection setup form.<br /><br />For more information on using this API endpoint with the the Oracle Fusion Cloud Applications connectors, see the [Schema information documentation](https://fivetran.com/docs/connectors/applications/oracle-fusion-cloud-applications#schemainformation).<br /><br />> IMPORTANT: This endpoint does not apply to [Magic Folder](https://fivetran.com/docs/connectors/files#magicfolder) connectors.<br />
`create`	`insert`	`connection_id`, `schemas`	Configures a Connection Schema for a new connection before the schema is captured from the source.<br /><br />> NOTE: The response returns the exact settings provided in the request.<br /><br />After the initial sync, when the connection captures the schema from the source, Fivetran attempts to apply the specified settings to the actual schema.<br />If certain tables or columns cannot be excluded, the settings for those entities are ignored.<br />
`update_table`	`update`	`connection_id`, `schema_name`, `table_name`, `enabled`	Updates the table config within your database schema for an existing connection within your Fivetran account.<br /><br />For the NetSuite SuiteAnalytics and Salesforce and Salesforce Sandbox connectors, the 'schemas' map field will always have a single entry with the 'netsuite' or 'salesforce' key, respectively.<br />
`update_schema`	`update`	`connection_id`, `schema_name`, `enabled`	Updates the database schema config for an existing connection within your Fivetran account (for a single schema within a connection with multiple schemas). <br /><br />> NOTE: The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial. <br /><br />In this API call, the NetSuite SuiteAnalytics, Salesforce and Salesforce Sandbox connectors always return the schema name as 'netsuite' and 'salesforce', respectively. <br /><br />For more information about this API call for the Oracle Fusion Cloud Applications connectors, see our [Schema information](https://fivetran.com/docs/connectors/applications/oracle-fusion-cloud-applications#schemainformation) documentation.<br />
`update`	`update`	`connection_id`	Updates the schema config for an existing connection within your Fivetran account.<br /><br />> NOTE: For backward compatibility, the response may contain the 'enable_new_by_default' boolean field. It defines whether new schemas and tables discovered in the source are synced. The value is 'true' if you specify 'ALLOW_ALL' as a value of 'schema_change_handling'. In the future API versions, we may remove this field.<br />><br />> The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial.<br />
`drop_columns`	`exec`	`connection_id`, `schemas`	Mark multiple blocked columns for deletion from your destination tables. The columns will be dropped during the next sync.
`reload`	`exec`	`connection_id`	Reloads the connection schema config for an existing connection within your Fivetran account.<br /><br />> NOTE: This method reloads the full schema from the connection'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.<br />><br />> The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial.<br />
`resync_tables`	`exec`	`connection_id`	Triggers a historical sync of all data for multiple schema tables within a connection. This action does not override the standard sync frequency you defined in the Fivetran dashboard.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
`connection_id`	`string`	The unique identifier of the connection. Retrieve it from the `id` field in the [List All Connections](https://fivetran.com/docs/rest-api/api-reference/connections/list-connections) response, or from the `id` field returned when you [Create a Connection](https://fivetran.com/docs/rest-api/api-reference/connections/create-connection).
`schema_name`	`string`	The schema name as stored in the connection schema config. This value is case-sensitive; an incorrect case results in an HTTP 404 error.
`table_name`	`string`	The table name as stored in the connection schema config. This value is case-sensitive; an incorrect case results in an HTTP 404 error.

`SELECT` examples

Returns the top-level schema configuration for an existing connection within your Fivetran account. The response includes global flags, every schema, each table, and only the columns that were explicitly overridden.

Use this endpoint to read the current data-selection tree for a connection, to back up the schema before making edits, or to copy the configuration to another connection.

> NOTE: To restore a backed-up schema or copy the configuration to another connection, use the Update a Connection Schema Config endpoint.

For more information, see the Connection Schema config tutorial.

> NOTE: Unedited columns (those following table defaults) are omitted from the response. For a read-only cataloging workflow, walk this response from schemas to tables, inspect each table's supports_columns_config field, and call the Retrieve Source Table Columns Config endpoint only for tables where that field is true.

For the NetSuite SuiteAnalytics, and Salesforce and Salesforce Sandbox connectors, the 'schemas' map field contains a single entry with the 'netsuite' or 'salesforce' key, respectively. For the 'schema.name_in_destination` name field, these connectors always return the destination schema name you set in the connection setup form.

For more information on using this API endpoint with the the Oracle Fusion Cloud Applications connectors, see the Schema information documentation.

> IMPORTANT: This endpoint does not apply to Magic Folder connectors.

SELECT
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas
FROM fivetran.connections.schema_configs
WHERE connection_id = '{{ connection_id }}' -- required
;

`INSERT` examples

create
Manifest

Configures a Connection Schema for a new connection before the schema is captured from the source. > NOTE: The response returns the exact settings provided in the request. After the initial sync, when the connection captures the schema from the source, Fivetran attempts to apply the specified settings to the actual schema. If certain tables or columns cannot be excluded, the settings for those entities are ignored.

INSERT INTO fivetran.connections.schema_configs (
schemas,
schema_change_handling,
connection_id
)
SELECT 
'{{ schemas }}' /* required */,
'{{ schema_change_handling }}',
'{{ connection_id }}'
RETURNING
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas
;

# Description fields are for documentation purposes
- name: schema_configs
props:
  - name: connection_id
    value: "{{ connection_id }}"
    description: Required parameter for the schema_configs resource.
  - name: schemas
    value: "{{ schemas }}"
    description: |
      The set of schemas within your connection schema config. Each key is the schema name as stored in the connection schema config. Schema names are case-sensitive; an incorrect case results in an HTTP 404 error.
  - name: schema_change_handling
    value: "{{ schema_change_handling }}"
    description: |
      The possible values for the schema_change_handling parameter are as follows: <br /> - `ALLOW_ALL` - all new schemas, tables, and columns which appear in the source after the initial setup are included in syncs <br /> - `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 <br /> - `BLOCK_ALL` - all new schemas, tables, and columns which appear in the source after the initial setup are excluded from syncs
    valid_values: ['ALLOW_ALL', 'ALLOW_COLUMNS', 'BLOCK_ALL']

`UPDATE` examples

update_table
update_schema
update

Updates the table config within your database schema for an existing connection within your Fivetran account. For the NetSuite SuiteAnalytics and Salesforce and Salesforce Sandbox connectors, the 'schemas' map field will always have a single entry with the 'netsuite' or 'salesforce' key, respectively.

UPDATE fivetran.connections.schema_configs
SET 
enabled = {{ enabled }},
columns = '{{ columns }}',
sync_mode = '{{ sync_mode }}',
row_filter = '{{ row_filter }}'
WHERE 
connection_id = '{{ connection_id }}' --required
AND schema_name = '{{ schema_name }}' --required
AND table_name = '{{ table_name }}' --required
AND enabled = {{ enabled }} --required
RETURNING
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas;

Updates the database schema config for an existing connection within your Fivetran account (for a single schema within a connection with multiple schemas). > NOTE: The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial. In this API call, the NetSuite SuiteAnalytics, Salesforce and Salesforce Sandbox connectors always return the schema name as 'netsuite' and 'salesforce', respectively. For more information about this API call for the Oracle Fusion Cloud Applications connectors, see our [Schema information](https://fivetran.com/docs/connectors/applications/oracle-fusion-cloud-applications#schemainformation) documentation.

UPDATE fivetran.connections.schema_configs
SET 
enabled = {{ enabled }},
tables = '{{ tables }}'
WHERE 
connection_id = '{{ connection_id }}' --required
AND schema_name = '{{ schema_name }}' --required
AND enabled = {{ enabled }} --required
RETURNING
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas;

Updates the schema config for an existing connection within your Fivetran account. > NOTE: For backward compatibility, the response may contain the 'enable_new_by_default' boolean field. It defines whether new schemas and tables discovered in the source are synced. The value is 'true' if you specify 'ALLOW_ALL' as a value of 'schema_change_handling'. In the future API versions, we may remove this field. > > The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial.

UPDATE fivetran.connections.schema_configs
SET 
schemas = '{{ schemas }}',
schema_change_handling = '{{ schema_change_handling }}',
is_type_locked = {{ is_type_locked }}
WHERE 
connection_id = '{{ connection_id }}' --required
RETURNING
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas;

Lifecycle Methods

EXEC variables use wire (API) names.

drop_columns
reload
resync_tables

Mark multiple blocked columns for deletion from your destination tables. The columns will be dropped during the next sync.

EXEC fivetran.connections.schema_configs.drop_columns 
@connection_id='{{ connection_id }}' --required 
@@json=
'{
"schemas": "{{ schemas }}"
}'
;

Reloads the connection schema config for an existing connection within your Fivetran account. > NOTE: This method reloads the full schema from the connection'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. > > The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the [Connection Schema config](https://fivetran.com/docs/rest-api/tutorials/connection-schema-configuration-use-cases) tutorial.

EXEC fivetran.connections.schema_configs.reload 
@connection_id='{{ connection_id }}' --required 
@@json=
'{
"exclude_mode": "{{ exclude_mode }}"
}'
;

Triggers a historical sync of all data for multiple schema tables within a connection. This action does not override the standard sync frequency you defined in the Fivetran dashboard.

EXEC fivetran.connections.schema_configs.resync_tables 
@connection_id='{{ connection_id }}' --required 
@@json=
'{
"schema": "{{ schema }}"
}'
;

Overview​

Fields​

Methods​

Parameters​

SELECT examples​

INSERT examples​

UPDATE examples​

Lifecycle Methods​