EricMatsonBDO
commented
10 months ago Was there ever a resolution to this?
This was working with version 3.4.2 of schema change but stopped working after that.
We were using the below for assigning the role that has - in the names
USE ROLE "{{AppAdmin_Role}}";
But in newer versions this is now failing and we do not see quotes around the role name in the logs as we did previously
krisztiansala
I am deploying schemachange in azure devops however stuck in execution of the repeatable script(very first step) Can you please help debugging this? its a simple deployment, using library for passing variables. Verified all variables in schemachange calling command and all look good.
Error log as below:
2023-02-21T09:29:39.8103382Z Applying change script R__version_update.sql 2023-02-21T09:29:39.8103737Z Traceback (most recent call last): 2023-02-21T09:29:39.8104077Z File "/opt/hostedtoolcache/Python/3.8.16/x64/bin/schemachange", line 8, in
2023-02-21T09:29:39.8104400Z sys.exit(main())
2023-02-21T09:29:39.8104868Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 865, in main
2023-02-21T09:29:39.8105243Z deploy_command(config)
2023-02-21T09:29:39.8105738Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 550, in deploy_command
2023-02-21T09:29:39.8106203Z session.apply_change_script(script, content, change_history_table)
2023-02-21T09:29:39.8106790Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 433, in apply_change_script
2023-02-21T09:29:39.8107180Z self.reset_session()
2023-02-21T09:29:39.8107675Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 415, in reset_session
2023-02-21T09:29:39.8108086Z self.execute_snowflake_query(reset_query)
2023-02-21T09:29:39.8108636Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 342, in execute_snowflake_query
2023-02-21T09:29:39.8109008Z raise e
2023-02-21T09:29:39.8109501Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/schemachange/cli.py", line 335, in execute_snowflake_query
2023-02-21T09:29:39.8109924Z res = self.con.execute_string(query)
2023-02-21T09:29:39.8110483Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/connection.py", line 675, in execute_string
2023-02-21T09:29:39.8111270Z ret = list(stream_generator)
2023-02-21T09:29:39.8111817Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/connection.py", line 693, in execute_stream
2023-02-21T09:29:39.8112270Z cur.execute(sql, _is_put_get=is_put_or_get, **kwargs)
2023-02-21T09:29:39.8113225Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/cursor.py", line 827, in execute
2023-02-21T09:29:39.8113694Z Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
2023-02-21T09:29:39.8114302Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/errors.py", line 275, in errorhandler_wrapper
2023-02-21T09:29:39.8114744Z handed_over = Error.hand_to_other_handler(
2023-02-21T09:29:39.8115315Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/errors.py", line 330, in hand_to_other_handler
2023-02-21T09:29:39.8115808Z cursor.errorhandler(connection, cursor, error_class, error_value)
2023-02-21T09:29:39.8116473Z File "/opt/hostedtoolcache/Python/3.8.16/x64/lib/python3.8/site-packages/snowflake/connector/errors.py", line 209, in default_errorhandler
2023-02-21T09:29:39.8116873Z raise error_class(
2023-02-21T09:29:39.8117209Z snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error:
2023-02-21T09:29:39.8117641Z syntax error line 1 at position 9 unexpected 'xxxx'. (xxxx is the first 4 char of the snowflake role passed in the variable group)
on verbose, I see the issue is that the sql generated like below : SQL query: USE ROLE xxxx-xxxx-xxxx; USE WAREHOUSE xx_xxx_xxx; USE DATABASE xxx_xxx_xxx; This is creating sql format issues since the values are not in quotes. correct format shall be like below - USE ROLE "xxxx-xxxx-xxxx"; USE WAREHOUSE "xx_xxx_xxx"; USE DATABASE "xxx_xxx_xxx";
This query is generated in function -
_q_set_sess_role = 'USE ROLE {role};' _q_set_sess_database = 'USE DATABASE {database};' _q_set_sess_warehouse = 'USE WAREHOUSE {warehouse};'
how do we handle this to pass on variables with double quotes? If I pass n variables with quotes in library, the schemachange command fails like - schemachange deploy: error: argument --vars: invalid loads value: '{"VAR_SF_ROLE": ""xxxx-xxx-xxx-""