Sqitch - Snowflake : Sqitch deploy error

[RajanBabuS]

Hi ,

We are trying to use Sqitch for Snowflake. Followed the steps provided in the given reference implementation but not able to perform sqitch deploy .

Reference Link:

1. for downloads: https://sqitch.org/download/redhat/
2. few modules were missed, based on the error message we downloaded from cpan site itself
3. for configurations followed your site: https://metacpan.org/pod/distribution/App-Sqitch/lib/sqitchtutorial-snowflake.pod

Where we are now?

1. we installed PERL
2. we installed APP::Sqitch and dependent modules(odbcDev package installed, DBD installed)
3. installed Snowflake ODBC connector verified the Snowflake connectivity separately : no issue here
   4)installed Snowflake Snowsql and verified the Snowflake connectivity separately : no issue here
   6)able to perform sqitch init
   7)able to perform sqitch add : files are getting generated automatically
   8)when I perform sqitch deploy, got the error message
   9)I am using password stored in the ~/.snowsql/config file
4. DB URI "db:snowflake://dummy@dummy.ap-south-1.aws.snowflakecomputing.com/sf_test?Driver=Snowflake"

versions:

#######
OS: Red Hat 7
Perl: Tried 5.16 < Tried 5.10 .1 and 5.14.1 for compatibility> 
sqitch (App::Sqitch) v1.1.0

# perl -MDBI -e 'DBI->installed_versions'
 Perl            : 5.016003    (x86_64-linux-thread-multi)
  OS              : linux       (2.6.32-754.12.1.el6.x86_64)
  DBI             : 1.643
  DBD::Sponge     : 12.010003
  DBD::Proxy      : 0.2004
  DBD::ODBC       : 1.61
  DBD::Mem        : 0.001
  DBD::Gofer      : 0.015327
  DBD::File       : 0.44
  DBD::ExampleP   : 12.014311
  DBD::DBM        : 0.08

Error Message:

**_Undef did not pass type constraint "Str" (in $args->{"message"}) at /usr/local/share/perl5/Throwable.pm line 75
    "Str" is a subtype of "Value"
    "Value" is a subtype of "Defined"
    Undef did not pass type constraint "Defined" (in $args->{"message"})
    "Defined" is defined as: (defined($_))_**

There were no straight forward documentations(for manual installation + configuration) available for us to have a decent installation and configurations. I am being very skeptical to propose Sqitch with the level of configuration steps involved.

Could you please help here for any possible solutions

[theory]

Well now that's not an expected error. Seems like something is failing to pass a message to the Error that gets thrown. Please apply this patch and run your command again, then paste the output here.

--- a/lib/App/Sqitch/X.pm
+++ b/lib/App/Sqitch/X.pm
@@ -9,6 +9,8 @@ use Throwable 0.200009;
 use Sub::Exporter -setup => [qw(hurl)];
 use overload '""' => 'as_string';
 
+use Carp; BEGIN { $SIG{__DIE__} = \&Carp::confess }
+
 # VERSION
 
 has message => (

[RajanBabuS]

Appreciate your response here. Overcame the above after installing missing modules & updating the driver path
I referred the section - https://metacpan.org/pod/distribution/App-Sqitch/lib/sqitchtutorial-snowflake.pod

We are facing the below error message now.

[root@ac6731c sqitch]# sqitch deploy
Adding registry tables to sf_test
250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3
"snowsql" unexpectedly returned exit value 1

[root@ac6731c sqitch]# 

From the SnowFlake side, history of events:

##################################
USE ROLE accountadmin

ALTER WAREHOUSE sqitch RESUME IF SUSPENDED
USE WAREHOUSE sqitch
**USE SCHEMA sqitch -****Failed******
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING=TIMESTAMP_LTZ
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT='YYYY-MM-DD HH24:MI:SS'\
ALTER SESSION SET TIMEZONE='UTC'
**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)
SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)

Our Sqitch.conf entry below

[core]
        engine = snowflake
        plan_file = sqitch.plan
        top_dir = .
 [engine "snowflake"]
        target   = sf_test
        #registry = sqitch
        client   = snowsql
 [user]
       name     = ro
       email    = xxx@example.com
 [target "sf_test"]
        uri    = "db:snowflake://yyy@qv32XXX.ap-south-1.aws.snowflakecomputing.com/sf_test?Driver=snowflake;warehouse=sqitch"

If I am using snowsql to connect snowflake using the existing config file, it works perfect without any issues.

Snowsql Config
############

[connections]
accountname = 'qv32XXX.ap-south-1.aws'
username  = yyy
password = zzz
dbname = sf_test
schemaname = public
warehousename = sqitch
rolename = accountadmin
--------------------------

1)Is there any other command which helps to identify connectivity apart from "deploy" command
2)If you still want me to run the patch provided- let me know how to run it

[theory]

**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******

This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:

SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)

As called by this code:

sqitch/lib/App/Sqitch/Engine/snowflake.pm

Lines 287 to 293 in a2c9205

	return $self->dbh->selectcol_arrayref(q{
	SELECT true
	FROM information_schema.tables
	WHERE TABLE_CATALOG = current_database()
	AND TABLE_SCHEMA = UPPER(?)
	AND TABLE_NAME = UPPER(?)
	}, undef, $self->registry, 'changes')->[0];

But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:

250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3

It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:

--- a/lib/App/Sqitch.pm
+++ b/lib/App/Sqitch.pm
@@ -368,6 +368,7 @@ sub capture {
     };
     return capturex ( shift, $self->quote_shell(@_) )
         if ISWIN && IPC::System::Simple->VERSION <= 1.25;
+    use Data::Dump; Data::Dump::ddx(\@_);
     capturex @_;
 }
 

[theory]

Hi @RajanBabuS, have you had a chance to try this? TIA!

[PoojaSSahu]

**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******

This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:

SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)

As called by this code:

sqitch/lib/App/Sqitch/Engine/snowflake.pm

Lines 287 to 293 in a2c9205

	return $self->dbh->selectcol_arrayref(q{
	SELECT true
	FROM information_schema.tables
	WHERE TABLE_CATALOG = current_database()
	AND TABLE_SCHEMA = UPPER(?)
	AND TABLE_NAME = UPPER(?)
	}, undef, $self->registry, 'changes')->[0];

But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:

250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3

It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:

--- a/lib/App/Sqitch.pm
+++ b/lib/App/Sqitch.pm
@@ -368,6 +368,7 @@ sub capture {
     };
     return capturex ( shift, $self->quote_shell(@_) )
         if ISWIN && IPC::System::Simple->VERSION <= 1.25;
+    use Data::Dump; Data::Dump::ddx(\@_);
     capturex @_;
 }
 

where do we should apply this patch, I am also getting similar error, thought of trying this way out as well. Thanks

[theory]

To the file lib/App/Sqitch.pm, the location of which depends on how you installed Sqitch.

[PoojaSSahu]

Hi,

Thanks for your response.

I have used below command from Visual studio's Bash terminal on windows 10 machine.

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

docker pull sqitch/sqitch
curl -L https://git.io/JJKCn -o sqitch && chmod +x sqitch
./sqitch help

Should I try to look for the file lib/App/Sqitch.pm in docker image or my local machine?

Thanks,
Pooja

[theory]

Oh, yeah, for the Docker image you would have to create a new one with the patch. Kind of a pain.

[PoojaSSahu]

I got the location

however , not sure how to access image as root user, overwrite permission issue.

Other way I tried ,modfiying the docker file by adding below coomand
&& sed -i "3i \ use Data::Dump; Data::Dump::ddx(@_);" /lib/perl5/App/Sqitch.pm

however it didn't add it in image not sure about the reason.

log from image build command:

[theory]

Your screenshot truncates the output, so we can't see if it was part of the command.

[PoojaSSahu]

Hi,

I added sed command in Dockerfile to edit the file inline

after that I have run below command
env DIR=snowflake REGISTRY=sqitch ./build --build-arg sf_account=$ACCOUNT

The out put log does not show sed command execution and the image created by above command is not also added the desired line (use Data::Dump; Data::Dump::ddx(@_);) in Sqitch.pm file.

[RajanBabuS]

Hi David Wheeler/PoojaSahu,

Sorry for the delayed response.

I didn't worked on the docker image, rather i configured on a Redhat 7 node.

Error messages were not user friendly. But took these steps to fix my issue. Ensure these steps if you are facing issue

1. I verified the perl modules mentioned in the Sqitch https://metacpan.org/dist/App-Sqitch/view/lib/sqitch.pod
2. Next issue on the library file location. Check how it is installed in your node, double check whether the Driver=//lib/libSnowflake.so is correct and the .so file exists
3. Check DB URI. snowflakecomputing.com is not required
4. Ensure the odbc configuration is correct in odbc.ini file and the same odbc source name is mentioned in the DB URI.
5. In the .snowsql and config files, ensure you have the account name, server name , region name mentioned individually.
6. If any other isssues persists in connecting , make sure the application from where you are initiating the call is able to find the config file

*** you can get the installed perl modules in a list format and cross check against the link in pointer 1
Any other issue apart from this will be mostly dedicated to human errors and hope for the luck !

@theory You can close my request or keep it opened until Pooja S Sahu's issue is getting resolved

[PoojaSSahu]

To the file lib/App/Sqitch.pm, the location of which depends on how you installed Sqitch.

I added the suggested patch in docker file and build the image .
however I get error when I run below command

[PoojaSSahu]

**SQL compilation error: Object 'CHANGES' does not exist or not authorized. ****Failed******

This is expected, but it's supposed to detect that error, then looks to see if the registry exists, which is what this query is doing:

SELECT true FROM information_schema.tables WHERE TABLE_CATALOG = current_database() AND TABLE_SCHEMA = UPPER(?) AND TABLE_NAME = UPPER(?)

As called by this code:

sqitch/lib/App/Sqitch/Engine/snowflake.pm

Lines 287 to 293 in a2c9205

	return $self->dbh->selectcol_arrayref(q{
	SELECT true
	FROM information_schema.tables
	WHERE TABLE_CATALOG = current_database()
	AND TABLE_SCHEMA = UPPER(?)
	AND TABLE_NAME = UPPER(?)
	}, undef, $self->registry, 'changes')->[0];

But it's odd that there are no more queries after that. Up to this point, Sqitch is interacting with the database via ODBC, but to create the registry schema (and later deploy changes), it uses SnowSQL. That's what this error is about:

250001 (08001): Failed to connect to DB. Verify the account name is correct: qv3

It's worth looking to see how Sqitch is calling SnowSQL; Try applying this patch and running it again to see exactly what arguments it passes to SnowSQL:

--- a/lib/App/Sqitch.pm
+++ b/lib/App/Sqitch.pm
@@ -368,6 +368,7 @@ sub capture {
     };
     return capturex ( shift, $self->quote_shell(@_) )
         if ISWIN && IPC::System::Simple->VERSION <= 1.25;
+    use Data::Dump; Data::Dump::ddx(\@_);
     capturex @_;
 }
 

Hi David,
I have manged to add Data::DUMP in docker image. please see the result for sqitch deploy now.

./sqitch deploy 'db:snowflake://xxxxxxxxxx@BA05631.eu-west-2.aws/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=xxxxxxxx;UID=xxxxxxx'
Adding registry tables to db:snowflake://Pxxxxxx@BA05631.eu-west-2.aws.snowflakecomputing.com/flipr?Driver=Snowflake;warehouse=compute_wh;PWD=xxxxxx;UID=xxxxxx
# Sqitch.pm:371: (
#   "snowsql",
#   "--accountname",
#   "BA05631",          #### Account Number not coming complete here
#   "--username",
#   "XXXXXX",
#   "--dbname",
#   "flipr",
#   "--noup",
#   "--option",
#   "auto_completion=false",
#   "--option",
#   "echo=false",
#   "--option",
#   "execution_only=false",
#   "--option",
#   "friendly=false",
#   "--option",
#   "header=false",
#   "--option",
#   "exit_on_error=true",
#   "--option",
#   "stop_on_error=true",
#   "--option",
#   "output_format=csv",
#   "--option",
#   "paging=false",
#   "--option",
#   "timing=false",
#   "--option",
#   "results=true",
#   "--option",
#   "wrap=false",
#   "--option",
#   "rowset_size=1000",
#   "--option",
#   "syntax_style=default",
#   "--option",
#   "variable_substitution=true",
#   "--variable",
#   "registry=sqitch",
#   "--variable",
#   "warehouse=compute_wh",
#   "--option",
#   "quiet=true",
#   "--filename",
#   bless({
#     dir => bless({
#       dirs => ["", "bin", "..", "lib", "perl5", "App", "Sqitch", "Engine"],
#       file_spec_class => undef,
#       volume => "",
#     }, "Path::Class::Dir"),
#     file => "snowflake.sql",
#     file_spec_class => undef,
#   }, "Path::Class::File"),
# )

Any thoughts?

[theory]

# "BA05631", #### Account Number not coming complete here

What does this mean?

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

Do you have logging enabled in the .snowsql file in your home directory? Tryturning that off. The Docker image reads that config file, but since it's running inside Docker, it can't access files on your host.

[jay-brillio]

@theory @PoojaSSahu , I am having same error, can you guide me with the same

 Object 'CHANGES' does not exist or not authorized. (SQL-42S02)' (err#1)
    <- DESTROY(DBI::st=HASH(0x55e9606256b8))= ( undef ) [1 items] at ODBC.pm line 229
    !! ERROR: 1 'SQL compilation error:
Object 'CHANGES' does not exist or not authorized. (SQL-42S02)' (err#1)

[theory]

@jay-brillio Make sure the Snowflake user you're connecting as has permission to create the registry schema and the changes table.

[jay-brillio]

@theory User is having OWNERSHIP & USAGE of the Warehouse or any other permission needs to be added, can you please suggest any troubleshooting steps for the same?

[jay-brillio]

@theory from snowflake side it's showing same error, why it's trying to create, below query is causing, the issue but it's not I have run it's from sqitch side looks like

SELECT c.change_id
, c.script_hash
, c.change
, c.project
, c.note
, c.committer_name
, c.committer_email
, to_varchar(CONVERT_TIMEZONE('UTC', c.committed_at), '"year:"YYYY":month:"MM":day:"DD":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"') AS committed_at
, c.planner_name
, c.planner_email
, to_varchar(CONVERT_TIMEZONE('UTC', c.planned_at), '"year:"YYYY":month:"MM":day:"DD":hour:"HH24":minute:"MI":second:"SS":time_zone:UTC"') AS planned_at
, listagg(t.tag, ' ') WITHIN GROUP (ORDER BY t.tag) AS tags
FROM changes c
LEFT JOIN tags t ON c.change_id = t.change_id
WHERE c.project = ?
GROUP BY c.change_id
, c.script_hash
, c.change
, c.project
, c.note
, c.committer_name
, c.committer_email
, c.committed_at
, c.planner_name
, c.planner_email
, c.planned_at
ORDER BY c.committed_at DESC
LIMIT 1

[theory]

It sounds like for some reason Snowflake is not letting the user create the table. That query failing is expected; Sqitch catches that error and then knows to try to create the table.

[jay-brillio]

so what's next I can try I am blocked on this since long? @theory

[theory]

See if you can create a schema and a table with that user via SnowSQL. Copy the command line arguments from this example. If it fails, you'll need to figure out why. Does the user not have the access you think it has? Are you connecting as the user you think you are? Does that user, in fact, need to be granted more permissions via the Snowflake console?

HTH.