Most of our ingestion is s3 connectors and we have several hundred and growing. In a subset of our files, we have to set the custom escape character. In one such case, the files use a single quote to escape fields.

In the UI, this is done by clicking the toggle for "non-standard" and then selecting the option that appears from the drop-down ("Custom Escape Character"). This was the feature released in December.

In other cases, we set it to "Delimited Only" and specify a delimiter.

For reference, this is the toggle I'm referring to:

And the options to choose when selecting:

We don't see Terraform support for this toggle. What we currently do to work around it is we save the terraform state as a JSON file along with our yml config specifying our delimeter and escape options, then have Python parse them both to issue calls to the Fivetran API to set this (as the API supports it).

In Python, using the API, we're handling it like:

                for c in yaml.safe_load(stream)["connector_group"]["connectors"]:
                    connector_id = get_connector_id(c['schema_name'], c['table_name'])
                    payload = None
                    if connector_id == "":
                        next
                    if c.get('escape_char', "") != '':    
                        payload = {
                            "config": {
                                "non_standard_escape_char": "true",
                                "escape_char_options": "CUSTOM_ESCAPE_CHAR"
                            }
                        }
                    if c.get('delimited_only', "false") == "true":    
                        payload = {
                            "config": {
                                "non_standard_escape_char": "true",
                                "escape_char_options": "DELIMITED_ONLY",
                                "escape_char": ""
                            }
                        }

We'd like to be able to do this natively in Terraform, as it is an extra step to the deployment process and more code to manage.

We're setting this for each now (setting non_standard_escape_char to true and escape_char_options to custom_escape_char or delimited_only) to be consistent. Setting it to delimited_only is the same effect as not toggling the above option or setting it to false.

Hi Aaron,

Appreciate the detailed explanation. We will update the Terraform module to include the non-standard character escaping options and let you know when done.

Separately,

• I don't see escape_char being set in the first if. Is that intentional?

if c.get('escape_char', "") != '':

• The DELIMITED_ONLY option is for when your file doesn't use an escape character to escape quotation marks, and you want your file to be processed with delimiter only. See https://fivetran.com/docs/files/amazon-s3/setup-guide#finishfivetranconfiguration. From your message it seems like you are selecting it when you have a standard escape character? In case of a standard escape character, you can leave the "non-standard" toggle unchecked. 

Best,
Parmeet

Hi Parmeet,

Regarding 

I don't see escape_char being set in the first if. Is that intentional?

Yes, that setting is currently being set via Terraform, so we're not resetting it with the API call, only setting what doesn't seem possible from Terraform.

Thanks for the additional details, let me re-read and check our assumptions on that second point.