SQL compilation error when using transformation in snowflake_pipe COPY INTO statement.

[chriselion]

Provider Version

v0.75.0

Terraform Version

1.4.6

Describe the bug

When trying to use the col_name parameters in the COPY INTO statement in a snowflake_pipe resource, I get a "Error: 000904 (42000): SQL compilation error". A similar snowflake_pipe resource setup works without the destination columns, and a similar statement works when run in a worksheet.

Expected behavior

The provider should support the table_name (column_name) syntax.

Code samples and commands

Pipe definition:

resource "snowflake_pipe" "pipe" {
  database    = var.database
  schema      = var.schema
  name        = var.pipe_name
  comment     = "Snowpipe. Managed by Terraform (${local.module_name})"
  auto_ingest = true

  copy_statement = <<-EOT
    COPY INTO ${snowflake_table.snowpipe_raw_results_table.qualified_name} (fileName, fileContent)
    FROM (
      SELECT metadata$filename, $1
      FROM @${local.snowpipe_stage_fqname} ( FILE_FORMAT => ${local.snowpipe_file_format_fqname} )
    )
  EOT
}

which results in the proposed action:

  # module.terraform-aws-snowpipe["my-kinesis-firehose"].snowflake_pipe.pipe will be created
  + resource "snowflake_pipe" "pipe" {
      + auto_ingest          = true
      + comment              = "Snowpipe. Managed by Terraform (terraform-aws-snowpipe)"
      + copy_statement       = <<-EOT
            COPY INTO "MY_DATABASE"."PUBLIC"."my_pipe_raw_results_TF" (fileName, fileContent)
            FROM (
              SELECT metadata$filename, $1
              FROM @"MY_DATABASE"."PUBLIC"."my_pipe_stage_TF" ( FILE_FORMAT => "MY_DATABASE"."PUBLIC"."my_pipe_file_format_TF" )
            )
        EOT
      + database             = "MY_DATABASE"
      + id                   = (known after apply)
      + name                 = "my_pipe"
      + notification_channel = (known after apply)
      + owner                = (known after apply)
      + schema               = "PUBLIC"
    }

and then produces the error

│ Error: 000904 (42000): SQL compilation error: error line 1 at position 220
│ invalid identifier 'FILENAME'
│ 
│   with module.terraform-aws-snowpipe["my-kinesis-firehose"].snowflake_pipe.pipe,
│   on ../modules/terraform-aws-snowpipe/main.tf line 208, in resource "snowflake_pipe" "pipe":
│  208: resource "snowflake_pipe" "pipe" {

Additional context
If I use a simpler table, and don't use the "Data load with transformation" syntax, like

  copy_statement = <<-EOT
    COPY INTO ${snowflake_table.snowpipe_raw_results_table.qualified_name}
    FROM @${local.snowpipe_stage_fqname}
    FILE_FORMAT = (TYPE = JSON)
EOT

then the pipe is created without any problems.

I was following this guide for setting up the pipe, and was able to create the pipe using the columns in a worksheet.

I see that there are two different syntaxes for COPY INTO - is it possible that only the "Standard data load" one is supported?

[sfc-gh-asawicki]

Hey, @chriselion. Thanks for creating the issue.

The provider itself does not interfere with the copy statement you are providing; it just passes it over to Snowflake. So, I would assume that this statement is not 100% correct.

[chriselion]

OK, I'll take another look at this today. Is there any way to enable output of the SQL that's being executed, so I can see whether it's getting altered somewhere along the way?

[sfc-gh-asawicki]

@chriselion you can run terraform commands with TF_LOG=debug to show (much) more output.

[leonard-henriquez]

I have the exact same problem.
Did you find a solution @chriselion ?

the error:

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 101 unexpected '@BUZ_S3_PIPE_2123'.
│
│   with snowflake_pipe.buz_s3_pipe,
│   on buz.tf line 41, in resource "snowflake_pipe" "buz_s3_pipe":
│   41: resource "snowflake_pipe" "buz_s3_pipe" {
│

my code:

resource "snowflake_pipe" "buz_s3_pipe" {
  depends_on  = [snowflake_table.buz_s3, snowflake_stage.buz_s3_stage]
  name        = local.buz_pipe_name
  schema      = local.buz_schema
  database    = local.buz_database
  auto_ingest = true

  copy_statement = "COPY INTO '${local.buz_table_name}' from @${local.buz_database}.${local.buz_schema}.${local.buz_pipe_name} file_format = (type = 'JSON')"
}

detailed error logs from @sfc-gh-asawicki 's technique

snowflake_pipe.buz_s3_pipe: Creating...
2024-01-01T04:46:51.900+0100 [INFO]  Starting apply for snowflake_pipe.buz_s3_pipe
2024-01-01T04:46:51.900+0100 [DEBUG] snowflake_pipe.buz_s3_pipe: applying the planned Create change
2024-01-01T04:46:51.900+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: 2024/01/01 04:46:51 [DEBUG] sql-conn-query: [query SELECT CURRENT_SESSION() as CURRENT_SESSION err <nil> duration 43.251416ms args {}] (XXXXXX)
2024-01-01T04:46:51.900+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: 2024/01/01 04:46:51 [DEBUG] connection success! Account: XXXXXX, Session identifier: 58508622145123
2024-01-01T04:46:51.956+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: 2024/01/01 04:46:51 [DEBUG] sql-conn-exec: [query CREATE PIPE "bronze"."snowplow_buz"."BUZ_S3_PIPE" AUTO_INGEST = true AS COPY INTO 'BUZ_S3' from @bronze.snowplow_buz.BUZ_S3_PIPE file_format = (type = 'JSON') err 001003 (42000): SQL compilation error:
2024-01-01T04:46:51.956+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: syntax error line 1 at position 96 unexpected '@bronze.snowplow_buz.BUZ_S3_PIPE'. duration 55.059792ms args {}] ()
2024-01-01T04:46:51.956+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: 2024/01/01 04:46:51 [DEBUG] err: 001003 (42000): SQL compilation error:
2024-01-01T04:46:51.956+0100 [DEBUG] provider.terraform-provider-snowflake_v0.82.0: syntax error line 1 at position 96 unexpected '@bronze.snowplow_buz.BUZ_S3_PIPE'.
2024-01-01T04:46:51.958+0100 [DEBUG] State storage *remote.State declined to persist a state snapshot
2024-01-01T04:46:51.958+0100 [ERROR] vertex "snowflake_pipe.buz_s3_pipe" error: 001003 (42000): SQL compilation error:
syntax error line 1 at position 96 unexpected '@bronze.snowplow_buz.BUZ_S3_PIPE'.
2024-01-01T04:46:51.958+0100 [DEBUG] states/remote: state read serial is: 40; serial is: 40
2024-01-01T04:46:51.958+0100 [DEBUG] states/remote: state read lineage is: 2123aa2e-eaa2-5ef8-a5d9-bf2a02590065; lineage is: 2123aa2e-eaa2-5ef8-a5d9-bf2a02590065
2024-01-01T04:46:51.958+0100 [ERROR] provider.terraform-provider-snowflake_v0.82.0: Response contains error diagnostic: @module=sdk.proto diagnostic_detail= diagnostic_severity=ERROR tf_resource_type=snowflake_pipe tf_rpc=ApplyResourceChange @caller=github.com/hashicorp/[email protected]/tfprotov6/internal/diag/diagnostics.go:62 diagnostic_summary="001003 (42000): SQL compilation error:
syntax error line 1 at position 96 unexpected '@bronze.snowplow_buz.BUZ_S3_PIPE'." tf_proto_version=6.4 tf_provider_addr=registry.terraform.io/Snowflake-Labs/snowflake tf_req_id=33400ff3-5b26-43e8-2a22-154cb2cd4450 timestamp=2024-01-01T04:46:51.956+0100

[chriselion]

In my case, it turned out that I needed to quote the column names, but you're not using that form of COPY INTO.

My guess is that you need quotes around the from @${local.buz_database}.${local.buz_schema}.${local.buz_pipe_name} part of your statement. You can see in the output, it's trying to execute

... from @bronze.snowplow_buz.BUZ_S3_PIPE ...

but that might need to be

... from @"bronze"."snowplow_buz"."BUZ_S3_PIPE" ...

[sfc-gh-asawicki]

@chriselion, can you close the issue then?

@leonard-henriquez, the suggestion above is a valid one. The provider itself does not interfere with the statements provided in places like the copy_statement like it does when constructing the usual SQL statements. It means the provider is not adjusting the provided identifiers in this case. The Snowflake identifiers are a tricky topic. Please read https://docs.snowflake.com/en/sql-reference/identifiers-syntax for the reference.

[chriselion]

Closed.

Just a thought: providing qualified_name (as requested in #2035) would make it easier for people to get the identifier right the first time.

[sfc-gh-asawicki]

@chriselion, that's right. We have it high on our list of improvements. Hopefully, we will visit this in the upcoming months.