Issues getting Sqitch to run on Windows 10 with a Snowflake target

[Nick white]

Hi - I installed Sqitch using Strawberry Perl and am really struggling to get it to work. Having finally got "deploy" to actually run, it was failing as the CHANGES table didn't exist and the deploy wasn't creating the registry tables (which I understand it should do the first time it is run).

I found on GIT the script for manually creating these tables on Snowflake but had to edit it before it would run e.g.

- variables were prefixed with £ and Snowflake expects $

- variables referencing objects need to use IDENTIFER($variable), not just $variable

Having run this edited script, I have then run deploy again and this time it is trying to upgrade these tables, I think:

Upgrading the Sqitch registry from {old} to 1.1

  * From {old} to 1.0

However this just hangs - possibly because the upgrade script has the same issues as the script I used to create the tables originally?

Does anyone know how to fix this and/or where I can find the script that is running for this upgrade action?

Thanks

[David E. Wheeler]

On Mar 26, 2021, at 09:47, Nick white <nrhw...@gmail.com> wrote:

> I found on GIT the script for manually creating these tables on Snowflake but had to edit it before it would run e.g.
> - variables were prefixed with £ and Snowflake expects $
> - variables referencing objects need to use IDENTIFER($variable), not just $variable

That doesn’t seem right. Here’s the script:

https://github.com/sqitchers/sqitch/blob/develop/lib/App/Sqitch/Engine/snowflake.sql

It uses `&` as documented here:

https://docs.snowflake.com/en/user-guide/snowsql-use.html#using-variables

Note that Sqitch uses the SnowSQL CLI to do deplyments, including the registry schema, so it may be you were thinking of SQL variables rather than SnowSQL variables.

> Having run this edited script, I have then run deploy again and this time it is trying to upgrade these tables, I think:
>
> Upgrading the Sqitch registry from {old} to 1.1
> * From {old} to 1.0
>
> However this just hangs - possibly because the upgrade script has the same issues as the script I used to create the tables originally?

Probably. Weird that it hangs, though. Is `snowsql` in your path? If not you can set it with

sqitch config --user engine.snowflake.client C:\path\to\snowsql

HTH,

David

[Nick white]

Hi

apologies, forgot the SQL was running via the snowsql command so the use of the & variables is correct.

I took the sql file you linked to and managed to get it to run directly using snowsql. One issue that I found was that in my snowflake config file I didn't have "variable_substitution = True" set in the options section - once I set this then the file ran fine and created the schema and tables.

However, sqitch is still not running...

1. I manually dropped the sqitch registry schema (so that sqitch had a clean DB to work with)

2. I ran the command "sqitch deploy"

3. It showed "Adding registry tables to ...." and then hung

4. In the Snowflake history I can see that it has run the following SQL successfully (so it is connecting and running something):

SELECT true

          FROM information_schema.tables

         WHERE TABLE_CATALOG = current_database()

           AND TABLE_SCHEMA  = UPPER(?)

           AND TABLE_NAME    = UPPER(?)

Any thoughts? Is there anything I can do to investigate what is happening e.g. are there logs and/or can I turn up the logging level?

Thanks

[David E. Wheeler]

On Mar 29, 2021, at 04:45, Nick white <nrhw...@gmail.com> wrote:

> However, sqitch is still not running...
> 1. I manually dropped the sqitch registry schema (so that sqitch had a clean DB to work with)
> 2. I ran the command "sqitch deploy"
> 3. It showed "Adding registry tables to ...." and then hung
> 4. In the Snowflake history I can see that it has run the following SQL successfully (so it is connecting and running something):
> SELECT true
> FROM information_schema.tables
> WHERE TABLE_CATALOG = current_database()
> AND TABLE_SCHEMA = UPPER(?)
> AND TABLE_NAME = UPPER(?)

That’s the last query Sqitch runs over ODBC before using SnowSQL to deploy the registry (assuming that query finds the registry does not exist, of course).

> Any thoughts? Is there anything I can do to investigate what is happening e.g. are there logs and/or can I turn up the logging level?

I have seen a few reports of hanging fixed by setting the SNOWSQL_WAREHOUSE environment variable, as described here:

https://github.com/sqitchers/sqitch/issues/520

It should not be an issue, but apparently there is a bug where it is not set and no error, or perhaps it is set but hangs when it doesn’t exist?

HTH,

David

[David E. Wheeler]

Adding sqitch-users back.

On Apr 6, 2021, at 05:13, Nick white <nrhw...@gmail.com> wrote:

> after some trial-and-error it looks like the following environment variable needs to be set, even though it is also included in the URI: SNOWSQL_PRIVATE_KEY_PASSPHRASE
>
> Obviously this is only relevant if the connection to Snowflake is using private key encryption

Oh, interesting. And it was just freezing? I wonder if there is a prompt waiting for a passphrase somewhere.

There’s documentation on using key pair authentication for Snowflake here, BTW:

https://sqitch.org/docs/manual/sqitch-authentication/#dont-use-passwords

Best,

David