columns
Creates, updates, deletes, gets or lists a columns resource.
Overview
| Name | columns |
| Type | Resource |
| Id | fivetran.connections.columns |
Fields
The following fields are returned by SELECT queries:
- get
| Name | Datatype | Description |
|---|---|---|
columns | object | The set of columns within your table schema config. Each key is the column name as stored in the connection schema config. Column names are case-sensitive; an incorrect case results in an HTTP 404 error. The columns object in the response contains the columns whose enabled value has been set by the user after the initial sync. (title: Columns) |
Methods
The following methods are available for this resource:
| Name | Accessible by | Required Params | Optional Params | Description |
|---|---|---|---|---|
get | select | connection_id, schema_name, table_name | Returns the real-time column list for one source table by querying the source. The response includes the current enabled and hashed flags, and the patchable fields.<br /><br />To determine whether this endpoint is available for a table, first [retrieve the connection schema config](https://fivetran.com/docs/rest-api/api-reference/connection-schema/connection-schema-config) and check the table's public [supports_columns_config response field](https://fivetran.com/docs/rest-api/api-reference/connection-schema/connection-schema-config#supports_columns_config). Column-level schema metadata support is reported per table rather than as a static connector-wide list. If supports_columns_config is false, column metadata is not available for that table.<br /><br />> NOTE: This endpoint works only for an existing connection that is in a 'Connected' state.<br /><br />> NOTE: The connection schema config response includes every schema and table, but includes only columns that were explicitly overridden. Use this endpoint when you need the exhaustive real-time column list for a table that supports column-level configuration.<br /> | |
update | update | connection_id, schema_name, table_name, column_name, enabled | Updates the column config within your table 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 />> 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 /> | |
delete | delete | connection_id, schema_name, table_name, column_name | Marks a blocked column for deletion from your destination table. The column will be dropped during the next sync.<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 /> |
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 |
|---|---|---|
column_name | string | The column name as stored in the connection schema config. This value is case-sensitive; an incorrect case results in an HTTP 404 error. |
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
- get
Returns the real-time column list for one source table by querying the source. The response includes the current enabled and hashed flags, and the patchable fields.<br /><br />To determine whether this endpoint is available for a table, first [retrieve the connection schema config](https://fivetran.com/docs/rest-api/api-reference/connection-schema/connection-schema-config) and check the table's public [supports_columns_config response field](https://fivetran.com/docs/rest-api/api-reference/connection-schema/connection-schema-config#supports_columns_config). Column-level schema metadata support is reported per table rather than as a static connector-wide list. If supports_columns_config is false, column metadata is not available for that table.<br /><br />> NOTE: This endpoint works only for an existing connection that is in a 'Connected' state.<br /><br />> NOTE: The connection schema config response includes every schema and table, but includes only columns that were explicitly overridden. Use this endpoint when you need the exhaustive real-time column list for a table that supports column-level configuration.<br />
SELECT
columns
FROM fivetran.connections.columns
WHERE connection_id = '{{ connection_id }}' -- required
AND schema_name = '{{ schema_name }}' -- required
AND table_name = '{{ table_name }}' -- required
;
UPDATE examples
- update
Updates the column config within your table 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 />> 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 />
UPDATE fivetran.connections.columns
SET
enabled = {{ enabled }},
hashed = {{ hashed }},
is_primary_key = {{ is_primary_key }},
target_data_type = '{{ target_data_type }}'
WHERE
connection_id = '{{ connection_id }}' --required
AND schema_name = '{{ schema_name }}' --required
AND table_name = '{{ table_name }}' --required
AND column_name = '{{ column_name }}' --required
AND enabled = {{ enabled }} --required
RETURNING
enable_new_by_default,
row_filtering_supported,
schema_change_handling,
schemas;
DELETE examples
- delete
Marks a blocked column for deletion from your destination table. The column will be dropped during the next sync.<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 />
DELETE FROM fivetran.connections.columns
WHERE connection_id = '{{ connection_id }}' --required
AND schema_name = '{{ schema_name }}' --required
AND table_name = '{{ table_name }}' --required
AND column_name = '{{ column_name }}' --required
;