how to create Javascript stored procedure in Schemachange

[rvishnupriyasiemens]

I have a stored procedure as given below in Snowflake. When I try to deploy using Schemachange it is throwing "KeyError: 'sqlText ' "

[rvishnupriyasiemens]

create or replace procedure $database.$schemaname.LOAD_DATA_1() RETURNS STRING LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$
var sql_command1 = "TRUNCATE TABLE $database.$schemaname.table1"; var rs1 = snowflake.execute({sqlText : sql_command1});

$$;

[sfc-gh-jhansen]

Hey there @rvishnupriyasiemens, please check out this solution here: https://github.com/Snowflake-Labs/schemachange/issues/124#issuecomment-1369710053

[rvishnupriyasiemens]

thank you @sfc-gh-jhansen I tried with the solutions using single-quote and '$$'. however, there is an keyword error . Could you please help me here ? I have given the procedure and the error here in the image.

[MACKAT05]

what version of schema change are you using? the filenames and line numbers don't line up with anything... try the latest version....
it would seem like this is a bug with the jinja implementation but i don't see the code "content = content_replace(..." anywhere in the repository.... and i cant tell because of the mark up if this was part of custom code that you added on top of things.

instead of using whatever custom procedure to replace $DATABASE use the vars feature and make a var called DATABASE and replace the raw code "$DATABASE" with "{{ DATABASE }}" to get to the same end...

[rvishnupriyasiemens]

hi @MACKAT05 There is an issue only when we try to deploy a stored procedure. The variable declaration in schemachange script and the variable replacement/substitution in the deployment script is not causing any issue actually. It is replacing the values in the deployment script properly. And, we are able to create/deploy other snowflake objects properly after replacing $DATABASE value .
"sqlText" is a keyword in our snowflake stored procedure. During deployment of this procedure, it is causing this error.

Kindly let me know if more information is required.

the schemachange version that is used is version = '2.8.0'

[MACKAT05]

it looks like it is the bug is on the line: content = content.replace("$DATABASE", "{0}").format(_metadata_database_name) Whats happening is the .format function is complaining since there is a positional argument supplied for the {0} tag it sees but the additional tag with {sqlText...} and it does not have a named argument like so .format(_metadata_database_name, sqlText = SomeVar)

If that code is under your control try modifying it like so content = content.replace("{", "{{").replace("}", "}}").replace("$DATABASE", "{0}").format(_metadata_database_name)

This will double the curly braces in the string before adding the positional ("{0}") target for use with the format function. This allows the format function to "ignore" those targets. see link for additional explanation. https://stackoverflow.com/questions/5466451/how-do-i-print-curly-brace-characters-in-a-string-while-using-format

Optionally try this less complicated code content = content.replace("$DATABASE", _metadata_database_name)

[rvishnupriyasiemens]

hi @MACKAT05 thanks so much for the details.

I am sure that the code _content = content.replace("$DATABASE", "{0}").format(_metadata_databasename) is not giving the error. I tested with two deployment scripts one for creating stage and another one for the stored procedure. Both the scripts will have $DATABASE and it is getting replaced successfully when the pipeline is running. the create stage is script is successful, however, the stored procedure script giving me the same error. when I try a deployment script without "sqlText" key word, it works fine. I checked the stackoverflow page as well and the options given there, but the same error. Could you help me here how to deploy the procedure ?

Please find the screenshots for both the scripts, contents and the contents after replacing the variables.

[MACKAT05]

@rvishnupriyasiemens I read through the history of cli.py: here is a link to the version 2.8.0 https://github.com/Snowflake-Labs/schemachange/blob/d1d275b2ccad9770715bd56981b50e1515fd2a04/snowchange/cli.py

/builds/snowflake/snowflake_projects/SMARTSALES/schemachange-SMARTSALES/CICD_Setup/schemachange.py appears to be a modified version of the above link since the failing procedure is from lines 312-337 and not in 397 as recorded in the error.

please execute ' {0} {sqlText:otherstuff} '.format('Database') in a local environment to replicate the error you are having . then execute ' {0} {{sqlText:otherstuff}} '.format('Database') in a local environment to show it working.

key error is coming from 'content = content.replace("$DATABASE", "{0}").format(_metadata_database_name)' format is seeing the '{sqlText' and expecting a named argument 'sqlText' to be supplied. they key error is that it tries to retrieve the value for the key 'sqlText' from the argument dictionary passed to it and fails. The double '{' acts like an escape sequence to avoid this issue.

Contact whomever in your organization is the maintainer of this Fork and have them update to a later version of schemachange. your version is 2.8.0 the latest version 3.4.2 or if they cannot do that feasibly within the process due to breaking changes. please supply them with the corrections to line 397 suggested earlier to remediate this issue: content = content.replace("$DATABASE", _metadata_database_name)

[rvishnupriyasiemens]

hi @MACKAT05 thanks so much for all the details and your support.

I'm able to replicate the issue. The '{' brackets was causing the issue. When I use "{{" as a escape sequence it worked. we will try to update the code as the permanent fix. content = content.replace("{", "{{").replace("}", "}}").replace("$DATABASE", "{0}").format(_metadata_database_name)

[sfc-gh-jhansen]

Thanks for the help here @MACKAT05. At this point @MACKAT05 and @rvishnupriyasiemens, where do we stand? Are there changes needed to schemachange to support javascript stored procedures?

[MACKAT05]

The failing statement currently does not appear in the repository. The error originates in a file called schemachange.py instead of the current cli.py per the screenshots above. The issue appears to be with a Fork of schemachange, @rvishnupriyasiemens can you confirm that the issue was in custom code in a fork?

[sfc-gh-tmathew]

Hello @rvishnupriyasiemens

Have you used the any schemachange versions after 2.8. We are currently on 3.6.0. Can you confirm if we need to do anything on our end ?

Thank you @MACKAT05 for the troubleshooting and finding the root cause.

[2023ASharathSJ]

Hi All,

We have the same issue with SnowFlake pipeline unable to execute the Java procedure. We have tried to use the double flower brackets. This is failining. Our pipelines are designed such a way that everytime latest version of SchemaChange is installed for deploying the code.

Any suggestion on resolving this issue?

I have attached the error screenshot.

Warm Regards, Sharath

[2023ASharathSJ]

Hi Team,

We are kind of stuck with this issue and are unable to proceed. Can you please suggest something on this?

Warm Regards, Sharath

[MACKAT05]

@2023ASharathSJ this looks like a different issue, have you been able to run this on a local machine and get an actual traceback call? the warnings above don't look like they are coming from schemachange. nor is the error 'name not supplied'