Snowflake-Labs / schemachange

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

Is there a way to output script execution in a formatted way? #112

Closed kjn70 closed 1 year ago

kjn70 commented 2 years ago

I'm still testing is schemachange can be a replacement for how we deploy code using snowsql today. In snowsql when code is run, our process automatically prefixes the code with the following snowsql commands so that we get a pretty, easy to audit log of what executed and the results of each executions.

!set exit_on_error=true !set output_format=simple !set remove_comments=False !set timing=true !set timing_in_output_file=true !set echo=true !set quiet=false

With schemachange currently, the output is harder to audit. For example:

Found Versioned file /builds/BPS_Database_DevOps/snowflake/cicdtesting-declarative/migrations/deploy/V2022.04.18.1__Deploy ingest objects.sql
Applying change script V2022.04.18.1__Deploy ingest objects.sql
Proceeding with private key authentication
SQL query: use schema ingest;
create or replace table test1 (col1 varchar);
use schema ingest;
create or replace table test2 (col1 varchar);
use schema ingest;
create or replace view view1
as select * from ingest.test1
Proceeding with private key authentication
SQL query: INSERT INTO CICD.DEV_CICDTESTING (VERSION, DESCRIPTION, SCRIPT, SCRIPT_TYPE, CHECKSUM, EXECUTION_TIME, STATUS, INSTALLED_BY, INSTALLED_ON) values ('2022.04.18.1','Deploy ingest objects','V2022.04.18.1__Deploy ingest objects.sql','V','99bd0eb49355f1c2c9fcc94b707c2b6d88a4582268ef6[189](https://---removed----)f5935c90',2,'Success','DEV_CICDTESTING_CICD_SVC',CURRENT_TIMESTAMP);
Successfully applied 1 change scripts (skipping 0)
Completed successfully

Is there a way to configure schemachange to adjust the formatting of the SQL execution and to return execution results, such as execution time and results?

An example of the output I get now is:

Executing from directory: D:\\GitLab-Runner\\builds\\VHxrjKBa\\0\\mitg_it\\mitg-bia\\script\\SNF\\BPS
/* ========= Place cut script below this line =============================================== */
/* === Build PUBLISH === */
-- Tables
!source DBObjects\3.PUBLISH\Tables\DEMO.sql
use database test_bps_db;
status                          
--------------------------------
Statement executed successfully.
1 Row(s) produced. Time Elapsed: 0.240s
use schema publish;
status                          
--------------------------------
Statement executed successfully.
1 Row(s) produced. Time Elapsed: 0.255s
create or replace table demo (col1 text);
status                          
--------------------------------
Table DEMO successfully created.
1 Row(s) produced. Time Elapsed: 0.237s
/* === END OF DATABASE CUT === */
!spool off
Goodbye!
============================================================================================
SUCCESS: Execution completed successfully
============================================================================================
sfc-gh-tmathew commented 1 year ago

Hello @kjn70, Thank you for reaching out. The snowsql output looks great. There is no formatted output for schemachange at this time. However, I would track your request for formatted outputs in #104 to incorporate a formatted logging option.

For now, I will close this issue and track your request in issue #104.

Please reopen this one if you were looking for something else.