Execute the Snowflake SQLs from a file and store output in different CSVs

Question

Thanks in advance. I am very new to Python & Python snowflake connector and need help to understand how it can be done.

Scenario: I have list of SQLS that i have put in a text file. Ex.. 5 SQL queries. I want to execute one by one in the Snowflake and store result of each SQL in a different Outputfile.

Please let me know your ideas and thoughts.

Thanks in advance.

Answer 1

You can try using CLI SnowSQL for this.

This is your SQL file my_example.sql you need to run

> cat my_example.sql
select current_database() as database, 
    current_timestamp() as timestamp, 
    current_warehouse() as warehouse;

Configure SnowSQL to connect to your account. You can refer this to do it.

> snowsql
* SnowSQL * v1.2.21
Type SQL statements or !help
USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>

USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>!set output_format=csv

USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>!spool my_example.log

USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>!source my_example.sql
"DATABASE","TIMESTAMP","WAREHOUSE"
"SF_DEMO_DB","2022-08-10 03:37:12.236 -0700","COMPUTE_WH"
1 Row(s) produced. Time Elapsed: 0.116s

USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>!spool off

USER1#COMPUTE_WH@SF_DEMO_DB.SF_DEMO_SCHEMA>!exit
Goodbye!

The spool file my_example.log is the CSV output of your SQL file my_example.sql

> cat my_example.log
"DATABASE","TIMESTAMP","WAREHOUSE"
"SF_DEMO_DB","2022-08-10 03:37:12.236 -0700","COMPUTE_WH"