Terraform snowflake_procedure Resource Does not Import Python Stored Procedure Statements

[pmarquieolo]

Terraform CLI and Provider Versions

Terraform v1.5.5 on darwin_arm64

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

Terraform Configuration

resource "snowflake_procedure" "dev_public_filterbyrole_varchar_varchar_sp" {
     name = "FILTERBYROLE"
     database  = snowflake_database.dev.name
     schema = local.schemas.dev.schemas.public.name
     language = "PYTHON"
     packages = ["snowflake-snowpark-python"]
     handler = "filter_by_role"
     runtime_version = "3.8"
     arguments {
            name = "TABLENAME"
            type = "VARCHAR"
        }
        arguments {
            name = "ROLE"
            type = "VARCHAR"
        }
     comment = null
     return_type = "TABLE "
     execute_as = "OWNER"
     return_behavior = "VOLATILE"
     null_input_behavior = "CALLED ON NULL INPUT"
     provider = snowflake.securityadmin
     statement = <<EOT
from snowflake.snowpark.functions import col

def filter_by_role(session, table_name, role):
  df = session.table(table_name)
  return df.filter(col("role") == role)
EOT
  }

Expected Behavior

The stored procedure should be brought into the state with the procedure statement.

Actual Behavior

When I execute the Terraform Plan command, I see the stored procedure complete with the statement. However, when I import the procedure terraform import snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp 'DEV.PUBLIC.FILTERBYROLE(VARCHAR, VARCHAR)', the plug-in crashes several times, then "succeeds."

Terraform state show snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp

snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp: resource "snowflake_procedure" "dev_public_filterbyrole_varchar_varchar_sp" { comment = "user-defined procedure" database = "DEV" execute_as = "OWNER" handler = "filter_by_role" id = "DEV.PUBLIC.FILTERBYROLE(VARCHAR, VARCHAR)" language = "PYTHON" name = "FILTERBYROLE" null_input_behavior = "CALLED ON NULL INPUT" packages = [ "snowflake-snowpark-python", ] return_type = "TABLE ()" runtime_version = "3.8" schema = "PUBLIC" secure = false

arguments {
    name = "TABLENAME"
    type = "VARCHAR"
}
arguments {
    name = "ROLE"
    type = "VARCHAR"
}

}

Steps to Reproduce

1. terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

[sfc-gh-asawicki]

Hey @pmarquieolo. Thanks for reaching out to us.

Please share the debug logs with the errors mentioned by running terraform with TF_LOG=DEBUG environment variable.

[pmarquieolo]

Terraform-trace-log-dev_public_filterbyrole_varchar_varchar_sp.txt

[sfc-gh-asawicki]

@pmarquieolo I have quickly skimmed through the provided logs and I can't see the crashes you are referring to. Also, the import is successful from the logs provided. Can you point me to the log entry that worries you?

[pmarquieolo]

This log is more complete:
terraform_import_storedprocedure_fails_to_import_statements.txt

[pmarquieolo]

The real issue is that the imports will succeed but the stored procedure statements do not get imported.

[sfc-gh-asawicki]

The error that is inside the logs is not our provider specific. This is the error thrown by the terraform itself, stating that multiple users are trying to change the same state file (and it is prohibited).

The second question I have is, why are you doing terraform plan + terraform import, instead of terraform plan + terraform apply?

[pmarquieolo]

We have taken this approach to bring existing infra into state while making no changes to the infra.

I will look into the collision. I’m the only person who is working on this so it could be an orphaned session.

Get Outlook for iOShttps://aka.ms/o0ukef

---

From: Artur Sawicki @.> Sent: Monday, April 29, 2024 11:18:24 AM To: Snowflake-Labs/terraform-provider-snowflake @.> Cc: Peter Marquie @.>; Mention @.> Subject: Re: [Snowflake-Labs/terraform-provider-snowflake] Terraform snowflake_procedure Resource Does not Import Python Stored Procedure Statements (Issue #2750)

The error that is inside the logs is not our provider specific. This is the error thrown by the terraform itself, stating that multiple users are trying to change the same state file (and it is prohibited).

The second question I have is, why are you doing terraform plan + terraform import, instead of terraform plan + terraform apply?

— Reply to this email directly, view it on GitHubhttps://www.google.com/url?q=https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/2750%23issuecomment-2083017830&source=gmail-imap&ust=1715008707000000&usg=AOvVaw0DcfnFyeFKz1Sw0SHt6AQT, or unsubscribehttps://www.google.com/url?q=https://github.com/notifications/unsubscribe-auth/AZN52HJSQRNTGAF7Y5W4ZFDY7ZQEBAVCNFSM6AAAAABG3DHWO6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBTGAYTOOBTGA&source=gmail-imap&ust=1715008707000000&usg=AOvVaw36JAAQsgY_bFBg9Roy0THl. You are receiving this because you were mentioned.Message ID: @.***>

[pmarquieolo]

Update: Having the same issue in 0.89.0 --

1. I created the stored procedure resource but did not import:
2. Did Terraform Plan The Plan returned this, complete with the statement:

snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp will be created
+ resource "snowflake_procedure" "dev_public_filterbyrole_varchar_varchar_sp" {
    + comment             = "user-defined procedure"
    + database            = "DEV"
    + execute_as          = "OWNER"
    + handler             = "filter_by_role"
    + id                  = (known after apply)
    + language            = "PYTHON"
    + name                = "FILTERBYROLE"
    + null_input_behavior = "CALLED ON NULL INPUT"
    + packages            = [
        + "snowflake-snowpark-python",
      ]
    + return_behavior     = "VOLATILE"
    + return_type         = "TABLE "
    + runtime_version     = "3.8"
    + schema              = "PUBLIC"
    + secure              = false
    + statement           = <<-EOT
          from snowflake.snowpark.functions import col
          
          def filter_by_role(session, table_name, role):
            df = session.table(table_name)
            return df.filter(col("role") == role)
      EOT

    + arguments {
        + name = "TABLENAME"
        + type = "VARCHAR"
      }
    + arguments {
        + name = "ROLE"
        + type = "VARCHAR"
      }
  }

3. I ran the import statement successfully.

terraform import snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp 'DEV.PUBLIC.FILTERBYROLE(VARCHAR, VARCHAR)'

4. Then I ran

terraform state show snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp

Which returned the stored procedure without the statement.

snowflake_procedure.dev_public_filterbyrole_varchar_varchar_sp:
resource "snowflake_procedure" "dev_public_filterbyrole_varchar_varchar_sp" {
  comment             = "user-defined procedure"
  database            = "DEV"
  execute_as          = "OWNER"
  handler             = "filter_by_role"
  id                  = "DEV.PUBLIC.FILTERBYROLE(VARCHAR, VARCHAR)"
  language            = "PYTHON"
  name                = "FILTERBYROLE"
  null_input_behavior = "CALLED ON NULL INPUT"
  packages            = [
      "snowflake-snowpark-python",
  ]
  return_behavior     = "VOLATILE"
  return_type         = "TABLE ()"
  runtime_version     = "3.8"
  schema              = "PUBLIC"
  secure              = false

  arguments {
      name = "TABLENAME"
      type = "VARCHAR"
  }
  arguments {
      name = "ROLE"
      type = "VARCHAR"
  }
}

[sfc-gh-asawicki]

Hey. Terraform plan shows what will be the changes based on the configuration file provided. Import is made against existing infrastructure on Snowflake. Therefore please:

• share the result of SHOW PROCEDURES and DESCRIBE PROCEDURE (only the rows for the procedure you try to import) - before importing it
• run terraform plan + terraform apply and show the state after these actions

[pmarquieolo]

STORED PROCEDURE NAME: FILTERBYROLE

STORED PROCEDURE SIGNATURE: (TABLENAME VARCHAR, ROLE VARCHAR)

SHOW PROCEDURES

DESCRIBE PROCEDURE

TERRAFORM PLAN

[sfc-gh-asawicki]

Hey @pmarquieolo. Is the procedure SECURE (it should be the last column in SHOW, which, unfortunately, I can't see on the provided screenshot)?

[sfc-gh-asawicki]

Hey @pmarquieolo. Can you answer the question above?

[sfc-gh-asawicki]

Closing due to inactivity.