Snowflake-Labs / terraform-provider-snowflake

Terraform provider for managing Snowflake accounts
https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest
MIT License
552 stars 420 forks source link

support infer_schema for creating external table #1537

Open ilkkapeltola opened 1 year ago

ilkkapeltola commented 1 year ago

Is your feature request related to a problem? Please describe.

I have 400 tables in S3 stages as Parquet files that I need to create as external tables. I would like to avoid having to define the table structures.

Snowflake allows you to infer_schema on Parquet files conveniently, but the terraform snowflake provider doesn't support that.

Describe the solution you'd like

Snowflake allows you to do this:

CREATE OR REPLACE EXTERNAL TABLE users using template ( select array_agg(object_construct(*)) from table ( infer_schema( location=>'@my_stage/public.users/', file_format=>'MY_PARQUET_FORMAT' ) ) ) location=@my_stage/public.users/ file_format=MY_PARQUET_FORMAT;

This works like a charm and only requires me to know the location of the Parquet files. When I then query the table, I get all the columns already inferred automatically.

Describe alternatives you've considered

I'm left with bad options:

waichee commented 1 year ago

Interested in this feature too for snowflake table and not external table. Any updates on whether this can be added to the terraform provider?

oemergenc commented 1 year ago

Hi, what happened to this? I need it too.

russell commented 9 months ago

I think the only workaround to this is to use https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/unsafe_execute

armenavd commented 1 month ago

Hi guys, thank you russel for the link.

resource "snowflake_unsafe_execute" "create_table" for_each = local.table_names execute = <<-EOT CREATE OR REPLACE TABLE ${var.database}.${var.schema_name}.${each.key} USING TEMPLATE ( SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) FROM TABLE( INFER_SCHEMA( IGNORE_CASE => TRUE, LOCATION => '@${snowflake_stage.stage.name}/${each.value}', FILE_FORMAT => '${snowflake_file_format.parquet_format.name}' ) ) ) EOT revert = "DROP TABLE IF EXISTS ${var.database}.${var.schema_name}.${each.key}" query = "DESCRIBE TABLE ${var.database}.${var.schema_name}.${each.key}" }

This should probably work I think, I will try it at least and keep you posted