Best setup instructions - Sqitch for Snowflake?

[ian chotakoo]

I’m struggling with the installation and configuration and was hoping you could point me at the right sequence in which things should be done?  I’ve looked at the Snowflake tutorial, but feel there’s a bit of a gap between that, and the download of the docker container.  I've also looked at the GitHub repo that we create docker images from

• I’m working on a Mac locally, but wanted to understand the docker container approach so it could be used in a more enterprise environment
• I’ve cloned the docker-sqitch github repo and followed the instructions for the Snowflake Dockerfile
  • I’m not sure how to amend snowsql connection information
  • I’m struggling with what I should be amending locally on my machine (as the directory is mounted) vs whether I should be trying to change anything / add to the docker container?
  • Anything more explicit about what I should be configuring?
• I know my snowflake details and how to configure snowsql
• Where drivers are downloaded automatically
• I feel like I'm struggling with paths, permissions, and just the initial setup

 Any pointers to the best documentation / a canonical / logical explanation of the sequence in which things should be done would be really helpful

[David E. Wheeler]

Hi Ian,

On Feb 17, 2021, at 16:57, 'ian chotakoo' via Sqitch Users <sqitch...@googlegroups.com> wrote:

> I’m struggling with the installation and configuration and was hoping you could point me at the right sequence in which things should be done? I’ve looked at the Snowflake tutorial, but feel there’s a bit of a gap between that, and the download of the docker container. I've also looked at the GitHub repo that we create docker images from

So you cloned the the docker-sqitch repo and ran

env DIR=snowflake ./build --build-arg sf_account=$ACCOUNT

Yes?

> • I’m working on a Mac locally, but wanted to understand the docker container approach so it could be used in a more enterprise environment

It’s also the easiest way to get things working on a Mac; ODBC is a pain in my experience.

> • I’ve cloned the docker-sqitch github repo and followed the instructions for the Snowflake Dockerfile

> • I’m not sure how to amend snowsql connection information
> • I’m struggling with what I should be amending locally on my machine (as the directory is mounted) vs whether I should be trying to change anything / add to the docker container?
> • Anything more explicit about what I should be configuring?
> • I know my snowflake details and how to configure snowsql
> • Where drivers are downloaded automatically

Using the docker image, you don’t have to change any ODBC config on your Mac. If you’re using the docker-sqitch script (https://git.io/JJKCn), when it runs it will mount the current directory, assuming it is the sqitch project root, and your home directory, so that Sqitch (and SnowSQL) running inside the container can read the contents of ~/.snowsql/config. The variables it reads are described here;

https://sqitch.org/docs/manual/sqitch-authentication/

And in more detail here:

https://metacpan.org/pod/App::Sqitch::Engine::snowflake

You can also set environment variables as described here:

https://sqitch.org/docs/manual/sqitch-environment/#snowflake

You can also put all the connection info in your target URIs; in fact, aside from passwords, I recommend it. The format is:

db:snowflake://$username@$password/$dbname?Driver=Snowflake;warehouse=$warehouse

> • I feel like I'm struggling with paths, permissions, and just the initial setup

> Any pointers to the best documentation / a canonical / logical explanation of the sequence in which things should be done would be really helpful

Hopefully the above helps, and be sure to always include `Driver=Snowflake` in your target URIs so that it uses the proper driver inside the Docker container. If you’re having issues not covered here, please reply with error messages and we’ll try to help you out.

Best,

David

[ian chotakoo]

On Wednesday, 17 February 2021 at 22:57:24 UTC justa...@gmail.com wrote:

Hi Ian,

On Feb 17, 2021, at 16:57, 'ian chotakoo' via Sqitch Users <sqitch...@googlegroups.com> wrote:

> I’m struggling with the installation and configuration and was hoping you could point me at the right sequence in which things should be done? I’ve looked at the Snowflake tutorial, but feel there’s a bit of a gap between that, and the download of the docker container. I've also looked at the GitHub repo that we create docker images from

So you cloned the the docker-sqitch repo and ran

env DIR=snowflake ./build --build-arg sf_account=$ACCOUNT

Yes?

>> Confirmed, I set ACCOUNT=xxxxx.eu-west-1, exported the variable, then ran the command above.  That has resulted in this:

 

 

 I then created a directory in my home directory called sqitch and ran:

Incidentally, I already have snowsql installed locally on my Mac (so have a .snowsql/config in my home directory), so wondered if sqitch picks up the .snowsql/config locally, or does it use the version in the docker container?  My local .snowsql/config doesn't have details of the snowflake account that I want sqitch to connect to.  Does my local machine need entries also (and if I'm using Jenkins, would that need snowsql and to have the confidence details also)?

When I connect to the docker container and check what's in the .snowsql/config I see:

 I log into my snowflake account, and create a user, warehouse and an empty database that I'll use for my test

I set the following environment variables up to reflect these (and export them for my test):

SNOWSQL_USER=SQITCHUSER

SNOWSQL_WAREHOUSE=SQITCH_WH

SNOWSQL_ROLE=SYSADMIN

For my test I have specified the password in SNOWSQL_PWD, although this is only to test connectivity and will not be how I do it once access is proven

I then switch to start following the tutorial for snowflake making the directory for flipr etc:

https://sqitch.org/docs/manual/sqitchtutorial-snowflake/

I set SQITCH_IMAGE=sqitch/sqitch:snowflake as per GitHub repo instruction (I think the tutorial should mention this as it's specific to Snowflake)

I ignore these steps because I want details from the docker container to be used:

I follow steps in tutorial amending files that will create / revert schema flipr

I then run the command to deploy (I do specify values at the prompt, even though I'd defined variables, the only value I don't pass in is the password, which should get picked up from the variable):

I get the Failed to connect to DB error message.

Checking Snowflake's history, I can see that something has connected as the SQITCHUSER via ODBC:

The failures inside snowflake are:

Should something have created the SQITCH schema and objects (have I missed a step)??

Thanks

Ian

> • I’m working on a Mac locally, but wanted to understand the docker container approach so it could be used in a more enterprise environment

It’s also the easiest way to get things working on a Mac; ODBC is a pain in my experience.

> • I’ve cloned the docker-sqitch github repo and followed the instructions for the Snowflake Dockerfile
> • I’m not sure how to amend snowsql connection information
> • I’m struggling with what I should be amending locally on my machine (as the directory is mounted) vs whether I should be trying to change anything / add to the docker container?
> • Anything more explicit about what I should be configuring?
> • I know my snowflake details and how to configure snowsql
> • Where drivers are downloaded automatically

Using the docker image, you don’t have to change any ODBC config on your Mac. If you’re using the docker-sqitch script (https://git.io/JJKCn), when it runs it will mount the current directory, assuming it is the sqitch project root, and your home directory, so that Sqitch (and SnowSQL) running inside the container can read the contents of ~/.snowsql/config. The variables it reads are described here;

https://sqitch.org/docs/manual/sqitch-authentication/

And in more detail here:

https://metacpan.org/pod/App::Sqitch::Engine::snowflake

>> this page was great because it helped focus core parameters I needed to consider

[David E. Wheeler]

On Feb 17, 2021, at 20:20, 'ian chotakoo' via Sqitch Users <sqitch...@googlegroups.com> wrote:

> >> Confirmed, I set ACCOUNT=xxxxx.eu-west-1, exported the variable, then ran the command above. That has resulted in this:
>

> <Screenshot 2021-02-17 at 23.08.08.png>

Great, looks like it built!

> I then created a directory in my home directory called sqitch and ran:

> <Screenshot 2021-02-17 at 23.23.06.png>

So far so good.

> Incidentally, I already have snowsql installed locally on my Mac (so have a .snowsql/config in my home directory), so wondered if sqitch picks up the .snowsql/config locally, or does it use the version in the docker container?

The sqitch-docker.sh script you downloaded above mounts your home directory inside the container so that, yes, Sqitch and SnowSQL running inside the container can read the config on your host.

> My local .snowsql/config doesn't have details of the snowflake account that I want sqitch to connect to. Does my local machine need entries also (and if I'm using Jenkins, would that need snowsql and to have the confidence details also)?

It can be handy, especially for passwords, yes. But you can also put everything you need into the target URI and use environment variables.

> When I connect to the docker container and check what's in the .snowsql/config I see:

> <Screenshot 2021-02-17 at 23.32.31.png>

Yes, it sets a default value for your accountname in /var. If I recall correctly, this is the fallback config after any config it finds in the home directory, which in this image is /home (or /root if you run the script as root).

> I then switch to start following the tutorial for snowflake making the directory for flipr etc:
>
> https://sqitch.org/docs/manual/sqitchtutorial-snowflake/
>
> I set SQITCH_IMAGE=sqitch/sqitch:snowflake as per GitHub repo instruction (I think the tutorial should mention this as it's specific to Snowflake)

That tells the docker-sqitch.sh shell script which image to use. In this case you’re telling it to use sqitch/sqitch:snowflake, which is indeed an image on your local host, to judge from your first screenshot.

> I ignore these steps because I want details from the docker container to be used:
>

> <Screenshot 2021-02-18 at 00.37.40.png>
>
> I follow steps in tutorial amending files that will create / revert schema flipr
>

> <Screenshot 2021-02-18 at 01.02.37.png>

Yeah, you mustn’t set engine.snowflake.client, since that location does not exist inside the container, but it’s still a good idea to set user.name and user.email.

> I then run the command to deploy (I do specify values at the prompt, even though I'd defined variables, the only value I don't pass in is the password, which should get picked up from the variable):

> <Screenshot 2021-02-18 at 01.13.00.png>

So Sqitch successfully connects via ODBC from insider the container, which is how it knows that the registry does not exist and needs to be created. To create it, it runs SnowSQL against this file:

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

Note that `&registry` is a variable, and Sqitch will set it to `sqitch` by default. The Sqitch user will need permission to create that schema and all the objects in it.

> I get the Failed to connect to DB error message.
>
> Checking Snowflake's history, I can see that something has connected as the SQITCHUSER via ODBC:

> <Screenshot 2021-02-18 at 01.09.29.png>
> The failures inside snowflake are:

> <Screenshot 2021-02-18 at 01.14.40.png><Screenshot 2021-02-18 at 01.15.12.png>

Yes, the error won’t be from the ODBC connection, but from the SnowSQL connection. Are there any more entries in this log? The error on `USE SCHEMA sqitch` is expected, since it doesn’t exist.

> Should something have created the SQITCH schema and objects (have I missed a step)??

Perhaps something has changed, such that the `USE SCHEMA sqitch` is more fatal than it used to be? It should be that Sqitch ignores it and keeps going to create it, but maybe not now. I wonder what would happen if this line was commented out, just for the creation of the registry?

https://github.com/sqitchers/sqitch/blob/a2c920545cdba64367c88b77a6268b0fc503f0d3/lib/App/Sqitch/Engine/snowflake.pm#L256

Would e tricky to change that in a container, though. So what happens if you create that schema (using the same username as you’re telling Sqitch to use) and try again?

Anyone else here who regularly uses Sqitch with Snowflake know what’s happening here? It sounds similar to issue #564:

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

Best,

David

[Pooja Sahu]

Hi There,

I am also getting "Failed to connect to DB error message " .

Could you please let me know how did you resolve it. any pointers are very much appreciated.

Thanks,

Pooja

[David E. Wheeler]

On Jul 15, 2021, at 05:42, Pooja Sahu <pooja....@gmail.com> wrote:

> I am also getting "Failed to connect to DB error message " .
>
> Could you please let me know how did you resolve it. any pointers are very much appreciated.

Can you post the exact command you ran and the exact output?

Thanks,

David

[pooja sahu]

Hi,

After many attempt of resolving the issue, now the error has to below:

$ ./sqitch deploy 'db:snowflake://$USR@$ACCOUNT/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=$PWD;UID=$USR'

Adding registry tables to db:snowflake://$USR@$ACCOUNT.snowflakecomputing.com/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=$PWD;UID=$USER

Failed to initialize log. No logging is enabled: [Errno 13] Permission denied: '/snowsql_rt.log_bootstrap'

Failed to initialize log. No logging is enabled: [Errno 13] Permission denied: '/snowsql_rt.log'

251006 (n/a): Password is empty

"snowsql" unexpectedly returned exit value 5

sanjeev@DESKTOP-KUC4VRH MINGW64 ~/ps_sqitch/docker-sqitch (main)

[pooja sahu]

Please see the command, I am running in attachment.

Thanks,

Pooja

On Thursday, 15 July 2021 at 20:07:47 UTC+1 justa...@gmail.com wrote: