Creating a stage with file_format

[ahaffar]

Provider Version

0.16.0

Terraform Version
0.13.0

Describe the bug

Not able to create a stage when providing file_format name - the error mentioning a file format name which is not created by me

Expected behavior

the stage should be created

Code samples and commands

Please add code examples and commands that were run to cause the problem.

resource "snowflake_stage" "external_storage" {
  name = "RDTExternalStage"
  database = snowflake_database._.name
  schema = snowflake_schema.schema["RAW"].name
  url = join("/", [
    data.terraform_remote_state.aws_qa.outputs.snowflake_bucket["url"],
    "crawler"])
  storage_integration = "STINT"
  file_format = "RAW_JSON_FF"
}

error

# module.snowflake_resources.snowflake_stage.external_storage will be created
  + resource "snowflake_stage" "external_storage" {
      + aws_external_id     = (known after apply)
      + database            = "qa-rdt-x-db"
      + file_format         = "RAW_JSON_FF"
      + id                  = (known after apply)
      + name                = "RDTExternalStage"
      + schema              = "RAW"
      + snowflake_iam_user  = (known after apply)
      + storage_integration = "STINT"
      + url                 = "s3://xyz/x"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

module.snowflake_resources.snowflake_stage.external_storage: Creating...

Error: error creating stage RDTExternalStage: 002003 (02000): SQL compilation error:
File format 'TOK_CONSTANT_LIST' does not exist or not authorized.

  on ../../../stacks/snowflake/snowflake-resources/stages.tf line 22, in resource "snowflake_stage" "external_storage":
  22: resource "snowflake_stage" "external_storage" {

[ahaffar]

hello, any update here ,
can you please check

[ryanking]

@ahaffar that you for reporting this, we will look into it when we have time. Note that issues that fall outside the scope of things we use at CZI often take awhile to address. In the meantime, I am happy to look at an PRs that address the issue.

[dimon37]

You can try something like this:

file_format = " type = 'JSON'"

as a workaround

[andybeeswax]

@ahaffar try adding "FORMAT_NAME =" and giving the full path to the file_format. Like this:
file_format = "FORMAT_NAME = DB_NAME.SCHEMA_NAME.RAW_JSON_FF"

[ahaffar]

Hello @andybeeswax , thank you
Will try it and update you

[mubeta06]

Full path to the file_format should do the trick.

[aidan-melen]

I ran into the same issue. I was able to get the stage to apply successful using @ahaffar recomendation i.e. `file_format = "TYPE = JSON". This however had some unexpected behavior on subsequent runs. e.g.

resource "snowflake_stage" "stage" {
  name                = "BLARG_JSON"
  database            = "MYDB"
  schema              = "PUBLIC"
  file_format         = "TYPE = JSON"
  url                 = "s3://blarg/blargy"
  storage_integration = "blarg"
}

state after first apply

resource "snowflake_stage" "stage" {
    aws_external_id     = "BLARG=BLARG"
    database            = "MYDB"
    file_format         = "TYPE = JSON NULL_IF = []"
    id                  = "MYDB|PUBLIC|BLARG_JSON"
    name                = "BLARG"
    schema              = "PUBLIC"
    snowflake_iam_user  = "arn:aws:iam::1234567890000/blarg"
    storage_integration = "BLARG_JSON"
    url                 = "s3://blarg/blargy"
}

so on next run it tries to change from TYPE = JSON to TYPE = JSON NULL_IF = [] which produces an error.

I was able to work around this issue by setting the file_format = "TYPE = JSON NULL_IF = []"

Hope that helps

[chris922]

It looks like you can't refer to an existing file format by its name. As you can see in the code it always puts the value of file_format into brackets:

https://github.com/chanzuckerberg/terraform-provider-snowflake/blob/0503e51122bde81bf40313f4e54d5598098d62d8/pkg/snowflake/stage.go#L146

If you want to refer to an existing file format it shouldn't be in brackets and the SQL statement should just be like e.g. FILE_FORMAT = RAW_JSON_FF and not FILE_FORMAT = (RAW_JSON_FF)

Ideas:

1. Analyze the given value of file_format and decide if brackets should be used or not (maybe it's enough to check if it's just one value given without any = or spaces or similar)
2. Introduce second parameter file_format_name that can be used to refer to an existing one - this parameter shouldn't be used together with file_format
3. Don't put brackets around it by default, let the user do it if file:_format will be used -> breaking change :/

I personally would prefer to go with option 2. What do you guys think? I could try to file in a PR for this.

@andybeeswax : Thanks for the workaround - works like a charm!

[mehdi-infostrux]

Is there any change in the status, I'm trying to refer to an existing file format by name and none of the solution above has worked.
So:
file_format = "DB.SCHEMA.formatname"
file_format="FORMAT_NAME = 'formatname'"
file_format="FORMAT_NAME = DB.SCHEMA.formatname"
file_format="(FORMAT_NAME = DB.SCHEMA.formatname)"
file_format="FORMAT_NAME = 'DB.SCHEMA.formatname'"

and more, didn't work with no more indications

[stackoverjoe]

Is there any change in the status, I'm trying to refer to an existing file format by name and none of the solution above has worked.
So:
file_format = "DB.SCHEMA.formatname"
file_format="FORMAT_NAME = 'formatname'"
file_format="FORMAT_NAME = DB.SCHEMA.formatname"
file_format="(FORMAT_NAME = DB.SCHEMA.formatname)"
file_format="FORMAT_NAME = 'DB.SCHEMA.formatname'"

and more, didn't work with no more indications

@mehdi-infostrux
file_format="FORMAT_NAME = DB.SCHEMA.formatname" This from your list of attempts is what worked for me. With provider version 0.55.1.

If your DB and SCHEMA are referring to their respective names, and that file format indeed exists in the schema and db then I am truly stumped. Are you creating the file_format via terraform as well?

[maximilianeber]

file_format="FORMAT_NAME = DB.SCHEMA.formatname" worked for us as well. Thanks everyone.

[kvanbrabant]

I'm using this line to inject DB, schema and file format:
file_format = "FORMAT_NAME = ${snowflake_database.db.name}.${snowflake_schema.events.name}.${snowflake_file_format.events_json.name}"
which resolves to the same syntax.
Thanks, this post helped me out too. I did not put FORMAT_NAME= in my value at first, which is not clearly documented in the terraform registry.
Nevertheless, the provider should render a more detailed error message. All I saw was:

 Error: error creating stage EVENTS_STAGE
│ 
│   with snowflake_stage.events_stage,
│   on main.tf line 115, in resource "snowflake_stage" "events_stage":
│  115: resource "snowflake_stage" "events_stage" {

[wanisfahmyDE]

had the same issue with parquet format, the below workaround did the trick:
`file_format = format("FORMAT_NAME =%s.%s.PARQUET", var.db_name, each.value.schema_name)