Add support for specifying the schema via config

[podung]

PR: Configure Snowflake Schema via Config

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.

Tasks left before PR should be accepted

• [ ] Make Snowflake_Schema configuration optional (how it is currently coded it will blow up if not supplied)
• [ ] Ensure snowflake schema can be supplied via CLI as well
• [ ] Modify Documentation
• [ ] Add / modify any tests as necessary

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-jhansen]

Thanks @podung, I think it's a good idea. If you have a complete PR for this, please let me know!

[podung]

Thanks @sfc-gh-jhansen. I'll be getting to this soon!

[sfc-gh-tmathew]

@podung Do you have an update for us ?

[podung]

Thanks for the follow up ping - I will attempt to get this ready this week.

[podung]

Oops sorry - doing some branch management and didn't realize I had made this PR with my forked master branch.

@sfc-gh-tmathew Not sure how to re-open this, but I'm prepping a new branch. Should I just open a new PR and mention this one?

[podung]

@sfc-gh-tmathew - sorry, I cannot get this PR to re-open after moving my forked master. I thought I could by pushing some new commits, but it's not picking it up.

I'll get a branch ready. Let me know if you'd like a new PR that just mentions this one or if you'd prefer something else.

Thanks

[sfc-gh-tmathew]

Bummer.

@podung Would you able to get your changes to baseline from 3.6.0 release ? If so, a new PR would be best.

[podung]

@sfc-gh-tmathew - Please see my new PR: https://github.com/Snowflake-Labs/schemachange/pull/210. I have completed what I believe are the required tasks and am rebased on latest master (which is just a few commits ahead of 3.6.0).

Let me know if you'd like any changes! Thanks!