search

Snowflake-Labs / schemachange

A Database Change Management tool for Snowflake
Apache License 2.0
502 stars 224 forks source link

Configure Snowflake Schema via Config #210

Closed podung closed 10 months ago

podung commented 10 months ago

NOTE: Original PR and discussion is here: https://github.com/Snowflake-Labs/schemachange/pull/180. The following description is pasted from that original PR

I'd like the ability to optionally specify the snowflake_schema via config.

Example of Problematic Script

CREATE OR REPLACE FUNCTION {{ db_name }}.{{ schema_name }}.Get_Customer_Name_By_ID(
  ID integer
)
RETURNS varchar
LANGUAGE SQL
as
$$
  SELECT FIRST_NAME || ' ' || LAST_NAME
  FROM CUSTOMER
  WHERE ID = ID
$$;

Schemachange Error

snowflake.connector.errors.ProgrammingError: 090106 (22000): Cannot perform SELECT. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

Explanation

The schemachange docs specify

schemachange is designed to be very lightweight and not impose to many limitations. Each change script can have any number of SQL statements within it and must supply the necessary context, like database and schema names

For most objects, it is sufficient to just declare the db name and schema name in the proc / function / table declaration. However some objects (namely SQL UDTFs in this example) in snowflake seem to ensure the object can compile. Since a schema is not provided on the SELECT ... FROM CUSTOMER statement, and no USE SCHEMA directive has been set, the error occurs.

Work-Arounds

  1. In the UDTF, a fully qualified name can be given (e.g. Select ... FROM {{ db_name }}.{{ schema_name }}.CUSTOMER).

    • This is less-than-desirable for our use case, as it causes the resultant object DDL to include the db/schema in the UDTF body. We have a project requirement to keep object DDLs identical between databases (dev/test/prod/etc).

  2. In the migration, before the CREATE OR REPLACE directive, we can simply add the statement USE SCHEMA {{ schema_name }};

    • This work-around is acceptable, but we have to reach for it in many of our migrations

Why this change is useful

Our team has a requirement to keep the object DDLs identical in each environment (Dev, test, prod, etc). There are a few instances where snowflake requires fully qualified object names to verify compilation during object creation (namely: SQL UDTFs). Instead of having to add the USE SCHEMA {{ schema_name }}; directive in many of our migration files, we'd like to just supply the schema up front and have schemcachange execute the statement for us at the beginning of the run.

PR Tasks

If you are interested in this PR I would be happy to complete the above tasks. Thanks for the library and for the consideration.

sfc-gh-tmathew commented 10 months ago

@podung we will review it and run some tests before merging the PR for the next release. Thank you for your contribution.

podung commented 10 months ago

@podung we will review it and run some tests before merging the PR for the next release. Thank you for your contribution.

Appreciate your time in review and being willing to accept this PR. Thanks for the library.

sfc-gh-tmathew commented 10 months ago

@podung Could you please include updates to the following files

podung commented 10 months ago

@podung Could you please include updates to the following files

* [Setup.cfg](https://github.com/Snowflake-Labs/schemachange/blob/master/setup.cfg): Update the version value to 3.6.1

* [cli.py](https://github.com/Snowflake-Labs/schemachange/blob/master/schemachange/cli.py): Update the version to 3.6.1

* [CHANGELOG](https://github.com/Snowflake-Labs/schemachange/blob/master/CHANGELOG.md): Include the change summary entry for 3.6.1

Done. Thanks again and let me know if I can do anything else

sfc-gh-tmathew commented 10 months ago

I haven't tested the changes, but the core changes look good. We do need to update the version number and the CHANGELOG still.

We will test before merging but the changes so far look good.