Upsert with Custom Primary Key for File Connector
Question
How can I setup a connection with custom primary key?
Answer:
1. Create a connection with custom primary key
To build a connection with custom primary key enabled, the value of paused must be set to true and append_file_option must be set to upsert_file_with_primary_keys.
Request
POST https://api.fivetran.com/v1/connections
{ "service": "s3", "group_id": "target_group_id", "paused": "true", "config": { "schema": "test_s3", "table": "table_name", "bucket": "bucket_name", "auth_type": "IAM_ROLE", "external_id": "group_id", "role_arn": "arn::your_role_arn", "access_key_id":"access_key_id_value", "access_key_secret":"access_key_secret_value", "prefix": "folder_path", "pattern": "file_pattern", "file_type": "csv", "json_delivery_mode": "Packed", "compression": "infer", "on_error": "skip", "append_file_option": "upsert_file_with_primary_keys", "archive_pattern": "regex_pattern", "non_standard_escape_char": "false", "escape_char_options": "CUSTOM_ESCAPE_CHAR", "escape_char": "", "delimiter": "", "null_sequence": "", "skip_before": "0", "skip_after": "0", "empty_header": "false", "list_strategy": "complete_listing", "use_pgp_encryption_options": "true", "pgp_secret_key": "", "pgp_pass_phrase": " ", "signer_public_key": " ", "line_separator": ";" } }
2. Reload the schema using API call
Request
POST http://api.fivetran.com/v1/connections/{connection_id}/schemas/reload
Response
{ "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, "columns": { "column_1": { "name_in_destination": "column_1", "enabled": true, "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 } }, "column_4": { "name_in_destination": "column_4", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_file": { "name_in_destination": "_file", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_line": { "name_in_destination": "_line", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_modified": { "name_in_destination": "_modified", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } } }, "enabled_patch_settings": { "allowed": true }, "supports_columns_config": true } } } }, "schema_change_handling": "ALLOW_ALL" } }
3. Modify the connection schema config
To assign a column as the primary key, copy the schema details from the response and set the is_primary_key value as true.
Request
PATCH http://api.fivetran.com/v1/connections/{connection_id}/schemas
{ "schema_change_handling": "ALLOW_ALL", "schemas": { "schema_1": { "enabled": true, "tables": { "table_1": { "enabled": true, "columns": { "column_1": { "is_primary_key": true }, "column_3":{ "is_primary_key": true } } } } } } }
Response
{ "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, "columns": { "column_1": { "name_in_destination": "column_1", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": false, "reason_code": "SYSTEM_COLUMN", "reason": "Column is selected as a Primary Key" }, "is_primary_key": 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": false, "reason_code": "SYSTEM_COLUMN", "reason": "Column is selected as a Primary Key" }, "is_primary_key": true }, "column_4": { "name_in_destination": "column_4", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_file": { "name_in_destination": "_file", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_line": { "name_in_destination": "_line", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } }, "_modified": { "name_in_destination": "_modified", "enabled": true, "hashed": false, "enabled_patch_settings": { "allowed": true } } }, "enabled_patch_settings": { "allowed": true }, "supports_columns_config": true } } } }, "schema_change_handling": "ALLOW_ALL" } }
You can confirm your primary key selection on the Schema tab of your connection details page. This functionality is limited only to a few file connectors.