Upsert with Custom Primary Key for File Connector
Question
How can I setup a connector with custom primary key?
Answer:
1. Create a connector with custom primary key
NOTE: To build a connector with custom primary key enabled, the value of
pausedmust be set totrueandappend_file_optionmust be set toupsert_file_with_primary_keys.
Request
POST https://api.fivetran.com/v1/connectors
{
"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": "<secret-key-used>",
"pgp_pass_phrase": "<pass-phrase-used>",
"signer_public_key": "<signer-public-key-used>",
"line_separator": ";"
}
}
2. Reload the schema using API call
Request
POST http://api.fivetran.com/v1/connectors/{connector_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 connector 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/connectors/{connector_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"
}
}
NOTE: You can confirm your primary key selection on the Schema tab of your connector details page. This functionality is limited only to a few file connectors.