using partition_by for external table fails

[oemergenc]

Terraform CLI and Provider Versions

❯ terraform --version
Terraform v1.5.5
on darwin_arm64

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

Terraform Configuration

resource "snowflake_external_table" "external_table" {
  database     = "MY_DB"
  schema       = "MY_SCHEMA"
  name         = "market_v1"
  comment      = "External table"
  file_format  = "TYPE = PARQUET"
  location     = "@stage/abd/abd"
  auto_refresh = false
  partition_by = ["dt"]

  column {
    name = "dt"
    type = "DATE"
    as   = "to_date(split_part(split_part(METADATA$FILENAME, '=', 2), '/', 1))"
  }
}

Expected Behavior

It should be possible to use the partition_by feature and the external table should be successfully be created.

Actual Behavior

The terraform apply command fails with the following exception:

snowflake_external_table.external_table: Creating...
╷
│ Error: Plugin did not respond
│ 
│ The plugin encountered an error, and failed to respond to the plugin6.(*GRPCProvider).ApplyResourceChange call. The plugin logs may contain more details.
╵
Releasing state lock. This may take a few moments...

Stack trace from the terraform-provider-snowflake_v0.81.0 plugin:

panic: interface conversion: interface {} is []interface {}, not []string

goroutine 81 [running]:
github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources.CreateExternalTable(0x0?, {0x102a84820?, 0x14000cb7110?})
        github.com/Snowflake-Labs/terraform-provider-snowflake/pkg/resources/external_table.go:181 +0xb04
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).create(0x102ac6018?, {0x102ac6018?, 0x14000dd1200?}, 0xd?, {0x102a84820?, 0x14000cb7110?})
        github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/resource.go:766 +0x134
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*Resource).Apply(0x14000ad07e0, {0x102ac6018, 0x14000dd1200}, 0x14000978c30, 0x14000975580, {0x102a84820, 0x14000cb7110})
        github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/resource.go:909 +0x86c
github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema.(*GRPCProviderServer).ApplyResourceChange(0x14000a76210, {0x102ac6018?, 0x14000dd1050?}, 0x1400093b720)
        github.com/hashicorp/terraform-plugin-sdk/[email protected]/helper/schema/grpc_provider.go:1072 +0xb08
github.com/hashicorp/terraform-plugin-mux/tf5to6server.v5tov6Server.ApplyResourceChange({{0x102adac78?, 0x14000a76210?}}, {0x102ac6018, 0x14000dd1050}, 0x0?)
        github.com/hashicorp/[email protected]/tf5to6server/tf5to6server.go:37 +0x58
github.com/hashicorp/terraform-plugin-mux/tf6muxserver.(*muxServer).ApplyResourceChange(0x102ac6050?, {0x102ac6018?, 0x14000dd0d50?}, 0x1400093b6d0)
        github.com/hashicorp/[email protected]/tf6muxserver/mux_server_ApplyResourceChange.go:36 +0x184
github.com/hashicorp/terraform-plugin-go/tfprotov6/tf6server.(*server).ApplyResourceChange(0x140008c9c20, {0x102ac6018?, 0x14000dd0360?}, 0x14000dca5b0)
        github.com/hashicorp/[email protected]/tfprotov6/tf6server/server.go:857 +0x3c0
github.com/hashicorp/terraform-plugin-go/tfprotov6/internal/tfplugin6._Provider_ApplyResourceChange_Handler({0x102a2b300?, 0x140008c9c20}, {0x102ac6018, 0x14000dd0360}, 0x14000974d80, 0x0)
        github.com/hashicorp/[email protected]/tfprotov6/internal/tfplugin6/tfplugin6_grpc.pb.go:467 +0x164
google.golang.org/grpc.(*Server).processUnaryRPC(0x14000142d20, {0x102ac6018, 0x14000e42720}, {0x102ad6300, 0x140006829c0}, 0x14000e31c20, 0x14000ba75c0, 0x103926a38, 0x0)
        google.golang.org/[email protected]/server.go:1343 +0xb8c
google.golang.org/grpc.(*Server).handleStream(0x14000142d20, {0x102ad6300, 0x140006829c0}, 0x14000e31c20)
        google.golang.org/[email protected]/server.go:1737 +0x990
google.golang.org/grpc.(*Server).serveStreams.func1.1()
        google.golang.org/[email protected]/server.go:986 +0x88
created by google.golang.org/grpc.(*Server).serveStreams.func1 in goroutine 22
        google.golang.org/[email protected]/server.go:997 +0x160

Error: The terraform-provider-snowflake_v0.81.0 plugin crashed!

This is always indicative of a bug within the plugin. It would be immensely
helpful if you could report the crash with the plugin's maintainers so that it
can be fixed. The output above should help diagnose the issue.

Steps to Reproduce

1. add a terraform configuration for an external table
2. use the partition_by field
3. run terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

[sfc-gh-asawicki]

Hey @oemergenc. Thanks for reporting the issue.

There is already an incoming fix: #2284. It will be released with the next version.

cc: @sfc-gh-jcieslak

[oemergenc]

@sfc-gh-asawicki I was able to update the provider and test the feature. The terraform apply now succeeds. Unfortunately the external table is not partitioned.
When I execute the following query on my external table created using terraform the DDL does not include the partition by part. Here the SQL I use:

SELECT GET_DDL('TABLE', 'MY_DATABASE.MY_SCHEMA.MY_TABLE_TERRAFORM');

I also cannot see the partition by part in the table definition in the Snowflake UI.
To counter check I created an idential external table manually and for this table I am getting a correct DDL.

To further clarify:

external table created manually

#Query
SELECT GET_DDL('TABLE', 'MY_DATABASE.MY_SCHEMA.MY_TABLE_MANUAL');

#Result
create or replace external table MY_TABLE_MANUAL(
    KEY VARCHAR(16777216) AS (CAST(GET($1, 'key') AS VARCHAR(16777216))),    
    DT DATE AS (CAST(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '=', 2), '/', 1) AS DATE))) 
    partition by (DT) 
    location=@GCS_LANDING_ZONE_STAGE/topics/customer_segments_v1/ 
    auto_refresh=false 
    file_format=(TYPE=PARQUET NULL_IF=());

external table created with this Snowflake Terraform provider version 0.82

#Query
SELECT GET_DDL('TABLE', 'MY_DATABASE.MY_SCHEMA.MY_TABLE_TERRAFORM');

#Result
create or replace external table MY_TABLE_TERRAFORM(
	KEY VARCHAR(16777216) AS (CAST(GET($1, 'key') AS VARCHAR(16777216))),	
	DT DATE AS (CAST(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '=', 2), '/', 1) AS DATE)))
    location=@GCS_LANDING_ZONE_STAGE/topics/customer_segments_v1/
    auto_refresh=false
    file_format=(TYPE=PARQUET NULL_IF=());

As you can see from this example the partition by part it not included in the Terraform created table.
Can you please have a look into this? If you need more infos let me know.

[oemergenc]

From what I can see the partitionBy field is not included here in the CreateExternalTableOptions:

https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/6941023f95230a14f92dd099c1a6375129ee4bfb/pkg/sdk/external_tables_dto.go#L266:L281

[oemergenc]

@sfc-gh-asawicki
Hi, I wanted to ask wether there is any chance that we can get this fixed? I think this is very confusing, that one is able to specify this setting but that it is actually ignored for the table.
I dont want to rush, it is just that external tables are very unusable without partitioning, the query performance is very bad and takes a lot of time.

[sfc-gh-asawicki]

@oemergenc AFAIK it's next item @sfc-gh-jcieslak will work on.

[oemergenc]

seems to work with 0.83.1. I am closing this issue.