Specifying a Procedure Call in snowflake_task (Resource)

[sushmamagesh]

I am getting an error when specifying a CALL Procedure() with sql_statement within the snowflake_task..

resource "snowflake_task" "task_sp" {

  database  = db.db.name
  schema    = schema.schema.name
  warehouse = wh.wh.name

  name     = "test"
  schedule {
    minutes = 10
  }
  started  = true
  suspend_task_after_num_failures = 10
  user_task_timeout_ms            = 600000 # 10m

  sql_statement = "CALL ${snowflake_procedure_sql.proc.name}()"
} 

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 69 at position 17 unexpected 'CALL'.

Is there a fix for this?

[sfc-gh-jcieslak]

Hey @sushmamagesh 👋
What version of the provider you are using? Did you try to use the fully qualified name?
I tried the same setup with the 1.0.2 version, and it worked fine. We need more information to help.

[sushmamagesh]

Thanks for the response.

Version is 1.0.3
It was actually from this code
SELECT $$CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION( SNOWFLAKE.NOTIFICATION.TEXT_PLAIN( SNOWFLAKE.NOTIFICATION.SANITIZE_WEBHOOK_CONTENT( '${snowflake_database.main_db.name} (${var.environment}) (${var.region}) - Warehouse change\\n\\n$$ || LISTAGG('* Warehouse: ' || warehouse_name || ' Max query duration (minutes): ' || max_query_duration_minutes::text || ' Original size: ' || original_size || ' New size: ' || recommended_size, $$\\n$$) || $$' ) ), SNOWFLAKE.NOTIFICATION.INTEGRATION('${local.slack_webhook_name}') )$$ AS stmt FROM TABLE(RESULT_SCAN(:last_query_id))
Since the error had the mention of CALL, I misunderstood.
So now the question is since the documentation says '$$' cannot be used how can this be replaced.
https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/procedure_sql#procedure_definition-1
Wrapping $$ is what is needed.

This is the snowflake link - https://docs.snowflake.com/en/sql-reference/data-types-text#dollar-quoted-string-constants

[sfc-gh-jcieslak]

If you are wrapping the procedure in $$ you can't use it inside the definition. The provider internally wraps the sql_statement with double dollars. This is not the provider's limitation. It will also fail in Snowsight because it will be parsed till the first closing $$. With that in mind, the error makes sense because Snowflake thinks your statement ends after first select and right after you have CALL, which is unexpected. In this case, you have to adjust your select statement so that it works with double-dollar wrapping.

[sushmamagesh]

Understood. What is the best way then to encode single-quotes. The issue in this statement within Stored Procedure as part of Procedure_Definition is variable doesn't substitute the value, instead the name remains as is.
Example:
warehouse_name VARCHAR;
But the SQL from above stays 'warehouse_name' and not 'COMPUTE_WH'

[sfc-gh-jcieslak]

Example:
warehouse_name VARCHAR;
But the SQL from above stays 'warehouse_name' and not 'COMPUTE_WH'

Could you elaborate a bit on the issue here? I'm not exactly sure what the problem is.

[sushmamagesh]

Figured it. Below Formatting Worked. But Listagg or \n (newline) didn't work though.

FOR record IN output DO
warehouse_name := record.warehouse_name;
event_name := record.event_name;
CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message '||:warehouse_name),
SNOWFLAKE.NOTIFICATION.INTEGRATION('test')
);
END FOR;

Full Code:

DECLARE
    event_name VARCHAR;
    warehouse_name VARCHAR;
BEGIN
    LET output RESULTSET := (
        SELECT
            warehouse_name,event_name,
            max(timestamp) as last_resize
        FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY
        WHERE event_name = ''RESIZE_WAREHOUSE''
        and timestamp > TIMESTAMPADD(minutes, -10, current_timestamp())
        GROUP BY all
        limit 1
    );

    FOR record IN output DO
      warehouse_name := record.warehouse_name;
      event_name := record.event_name;
      CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
      SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message '||:warehouse_name),
      SNOWFLAKE.NOTIFICATION.INTEGRATION('test')
      );
    END FOR;

    RETURN ('SUCCESS');
END;