Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

String escaping on snowflake_external_table.file_format prevents passing field_delimiter as a string literal #1046

Closed
glittershark opened this issue Jun 7, 2022 · 0 comments · Fixed by #1183, #1207 or #1332
Labels
bug Used to mark issues with provider's incorrect behavior

Comments

@glittershark
Copy link

glittershark commented Jun 7, 2022

Provider Version

0.34.0

Terraform Version

1.1.2

Describe the bug

If I try to pass field_delimiter to the file_format of a snowflake_external_table, the single quote is incorrectly escaped, giving the following error message:

snowflake_external_table.my_table: Creating...
╷
│ Error: error creating externalTable my_table: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 261 unexpected '\''.
│ parse error line 1 at position 267 near '39'.

Code samples and commands

resource "snowflake_external_table" "my_table" {
  # ...
  file_format       = "type = csv field_delimiter = '\t' compression = auto skip_header = 2"
  # ...
}

Additional context

Because it's not escaped, this can be worked around by using $$ for the field delimiter, but that's not awesome

@glittershark glittershark added the bug Used to mark issues with provider's incorrect behavior label Jun 7, 2022
sfc-gh-hachouraria added a commit to sfc-gh-hachouraria/terraform-provider-snowflake-fork that referenced this issue Aug 22, 2022
Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these usage require defining string literals with the single quote
character (') that should not be escaped.

This change removes the escaping of single quotes performed for the
file_format values.

Some examples have also been added to the field documentation
to make it easier to understand how values need to be passed to
external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Attempted an external table creation on a personal account with the
    changes included and observed it to execute a successful SQL

Fixes Snowflake-Labs#1046
sfc-gh-hachouraria added a commit to sfc-gh-hachouraria/terraform-provider-snowflake-fork that referenced this issue Aug 22, 2022
Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these usage require defining string literals with the single quote
character (') that should not be escaped.

This change removes the escaping of single quotes performed for the
file_format values.

Some examples have also been added to the field documentation
to make it easier to understand how values need to be passed to
external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Attempted an external table creation on a personal account with the
    changes included and observed it to execute a successful SQL

Fixes Snowflake-Labs#1046
sfc-gh-hachouraria added a commit to sfc-gh-hachouraria/terraform-provider-snowflake-fork that referenced this issue Aug 22, 2022
Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these require defining string literals with the single quote
character (') that should not be escaped (\\').

This change removes the escaping of single quotes performed for the
file_format values to allow them to be specified without failing
the query compilation.

Some examples have also been added to the documentation
to make it easier to understand how values for file_format
need to be passed for external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Setup a trial account, exported env-vars and ran 'make test-acceptance'
    and verified all tests passed
  - Attempted an external table creation on a personal account with the
    changes included through a local buildand observed it to execute a
    successful SQL. Tried with both FORMAT_NAME and FIELD_DELIMITER
    options with string literal values.

Fixes Snowflake-Labs#1046
sfc-gh-hachouraria added a commit to sfc-gh-hachouraria/terraform-provider-snowflake-fork that referenced this issue Aug 22, 2022
Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these require defining string literals with the single quote
character (') that should not be escaped (\\').

This change removes the escaping of single quotes performed for the
file_format values to allow them to be specified without failing
the query compilation.

Some examples have also been added to the documentation
to make it easier to understand how values for file_format
need to be passed for external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Setup a trial account, exported env-vars and ran 'make test-acceptance'
    and verified all tests passed
  - Attempted an external table creation on a personal account with the
    changes included through a local buildand observed it to execute a
    successful SQL. Tried with both FORMAT_NAME and FIELD_DELIMITER
    options with string literal values.

Fixes Snowflake-Labs#1046
sfc-gh-hachouraria added a commit to sfc-gh-hachouraria/terraform-provider-snowflake-fork that referenced this issue Sep 10, 2022
Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these require defining string literals with the single quote
character (') that should not be escaped (\\').

This change removes the escaping of single quotes performed for the
file_format values to allow them to be specified without failing
the query compilation.

Some examples have also been added to the documentation
to make it easier to understand how values for file_format
need to be passed for external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Setup a trial account, exported env-vars and ran 'make test-acceptance'
    and verified all tests passed
  - Attempted an external table creation on a personal account with the
    changes included through a local buildand observed it to execute a
    successful SQL. Tried with both FORMAT_NAME and FIELD_DELIMITER
    options with string literal values.

Fixes Snowflake-Labs#1046
sfc-gh-swinkler pushed a commit that referenced this issue Sep 16, 2022
…1183)

Common use of FILE_FORMAT under CREATE EXTERNAL TABLE includes specifying
a previously created format name (FORMAT_NAME = 'NAME') or the various
type options (TYPE=CSV FIELD_DELIMITER='|').

Both of these require defining string literals with the single quote
character (') that should not be escaped (\\').

This change removes the escaping of single quotes performed for the
file_format values to allow them to be specified without failing
the query compilation.

Some examples have also been added to the documentation
to make it easier to understand how values for file_format
need to be passed for external tables.

Testing:

  - Modified unit tests to exercise passing of typical file format values
  - Ran 'make test' to confirm existing tests continue to pass
  - Setup a trial account, exported env-vars and ran 'make test-acceptance'
    and verified all tests passed
  - Attempted an external table creation on a personal account with the
    changes included through a local buildand observed it to execute a
    successful SQL. Tried with both FORMAT_NAME and FIELD_DELIMITER
    options with string literal values.

Fixes #1046
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment