terraform-provider-snowflake: snowflake_file_format update-in-place doesn't apply

Terraform CLI and Provider Versions

Terraform v1.6.5
on linux_amd64
+ provider registry.terraform.io/snowflake-labs/snowflake v0.82.0

Terraform Configuration

resource "snowflake_file_format" "csv" {
    binary_format                  = "HEX"
    compression                    = "AUTO"
    database                       = "RAW"
    date_format                    = "AUTO"
    empty_field_as_null            = true
    encoding                       = "UTF8"
    error_on_column_count_mismatch = true
    escape                         = "NONE"
    escape_unenclosed_field        = "NONE"
    field_delimiter                = ","
    field_optionally_enclosed_by   = "\""
    format_type                    = "CSV"
    id                             = "RAW|EXAMPLE_SCHEMA|PROCESS_CSV"
    name                           = "PROCESS_CSV"
    null_if                        = [
        "NULL",
    ]
    parse_header                   = false
    record_delimiter               = <<-EOT
        
    EOT
    replace_invalid_characters     = false
    schema                         = "EXAMPLE_SCHEMA"
    skip_blank_lines               = false
    skip_byte_order_mark           = true
    skip_header                    = 1
    time_format                    = "AUTO"
    timestamp_format               = "AUTO"
    trim_space                     = false
}

Expected Behavior

Updating a field in the file_format resource and re-applying updates the file format in snowflake

Actual Behavior

The update does not change anything in snowflake, and terraform picks up the change as needing to be applied every time

Steps to Reproduce

  1. Create a snowflake_file_format resource
  2. terraform apply
  3. Update the resource, change the field_optionally_enclosed_by field for instance (not specific to this field) to NONE
  4. terraform apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Workaround at the moment is to taint the resource in terraform and allow it to be destroyed and recreated.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 27 (8 by maintainers)

Commits related to this issue

Most upvoted comments

Thanks @raulbonet for helping!

0.87.1 has solved the issue, and I can apply file format updates successfully, including "NONE"!

image

Resolved by https://github.com/Snowflake-Labs/terraform-provider-snowflake/pull/2575 and released in 0.87.1

Thanks @alexmaras , I will have a look tomorrow (EMEA timezone)

thank you @raulbonet ;

I thought I had to escape the backslashes.

This is working like a charm since I configured the field to \\ ; the change is correctly propagated to the FILE_FORMAT.

@sfc-gh-asawicki version 0.87.1 fixed the issue we were having

Hey @raulbonet. My answers below:

ad 1 I will note it down, we should run it with NONE, not 'NONE'.

ad 2 This is one of the topics we are aware of and that we will address during resources redesign (https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#our-roadmap). One of the PRs that shows how we can handle this is #2502. We are of course limited only to objects where we can easily access the default snowflake value. For the cases where this is not possible, we will just encourage the user to fill in the value (or even force the user to do it by requiring it).

@sfc-gh-asawicki , I notice a couple of issues here, just bringing it to your attention:

  1. NONE is a Snowflake reserved keyword that afaik, currently the SQLBuilder does not support. So, the statement executed by Snowflake is:

ALTER ...... = 'NONE', when it should be ALTER ...... = NONE

It seems that both seem to work in this case, but not sure if this is the case for absolutely all resources in Snowflake.

  1. I am noticing a non-idempotent operation here: if the resource is being created, Snowflake will default to whatever default value it has. However, if the resource is being altered, Snowflake will just keep the existing value. The only way to make this idempotent would be recreate the resource when a value is set to null, so it defaults to Snowflake’s default value.

I guess we do not want to address this in this PR, but this probably affects all resources. I wonder if we want to account for this use-case or at least track it in the backlog.

Hello @benriou I had a look. I think that the problem is just that \\\\ is not a valid value for this field, but Snowflake instead of raising an exception, does nothing. If you read the documentation here , section ESCAPE_UNENCLOSED_FIELD:

A singlebyte character string used as the escape character for unenclosed field values only. An escape character invokes an alternative interpretation on subsequent characters in a character sequence. You can use the ESCAPE character to interpret instances of the FIELD_DELIMITER or RECORD_DELIMITER characters in the data as literals. The escape character can also be used to escape instances of itself in the data.

Accepts common escape sequences, octal values, or hex values.

But \\\\ cannot be expressed as a single byte sequence, nor is a “common escape sequence”.

If you manually try to execute the statement in Snowflake GUI: ALTER FILE FORMAT "DEV"."PUBLIC"."LOADBALANCER_LOGS" SET ESCAPE_UNENCLOSED_FIELD = '\\\\'

And then query the file format: SHOW FILE FORMATS LIKE 'LOADBALANCER_LOGS' IN SCHEMA "DEV"."PUBLIC";

You will see that, indeed, the character has not changed.

I tried with other characters, like “A” or “a” and in this case, it gets replaced by \n, and still not raising an exception.

Hello @benriou Can you please post the terraform configuration and the plan so I can debug?

We are having a similar issue: Terraform v1.7.0 on darwin_arm64

  • provider registry.terraform.io/snowflake-labs/snowflake v0.84.0

These are included on the plan every time: Screenshot 2024-01-22 at 1 45 46 PM

However the state does not show any change after this plan is applied: image image

To add to the complexity of this issue, this problem only persists in our staging and testing workspaces but not in Production. We speculated that this was because we imported these in production and the values were already set to true.