Snowflake SnowSQL For Scripts Execution

Today we will be discussing on how we can make use of snowSQL for running hundreds or thousands of nasty scripts from command line without making use of Snowflake UI.

Since you show interest in the following article, I would love to connect with you on LinkedIn so that we can learn and discuss more —

https://www.linkedin.com/in/divyanshsaxena/

Executive Summary

1. What Is SnowSQL
2. How to Install SnowSQL
3. Pro Tip to Connect SnowSQL
4. Running Scripts with SnowSQL

What Is Snowflake SnowSQL?

SnowSQL is the command line client for connecting to Snowflake to execute SQL queries and perform all DDL and DML operations, including loading data into and unloading data out of database tables.

SnowSQL is an example of an application developed using the Snowflake Connector for Python; however, the connector is not a prerequisite for installing SnowSQL. All required software for installing SnowSQL is bundled in the installers.

How To Install Snowflake SnowSQL?

If You wanted to know about the basics of SnowSQL, Snowflake provides a nice video which covers the baiscs of SnowSQL.

You can visit the link to download snowSQL:

SnowSQL - Snowflake Developers

Pro Tip to Connect SnowSQL

I would personally prefer to define and use the config instead of typing the connection string each time on snowSQL.

SnowSQL supports multiple configuration files that allow organizations to define base values for connection parameters, default settings, and variables while allowing individual users to customize their personal settings in their own <HOME_DIR>/.snowsql/config files. When SnowSQL starts, it loads configuration parameter values from the configuration file location.

In the config file, you can define you connection configuration as:

[connections.my_snowflake_connection]
accountname = myorganization-myaccount
username = jsmith
password = xxxxxxxxxxxxxxxxxxxx
dbname = mydb
schemaname = public
warehousename = mywh

Running Script Using SnowSQL

Now once you have configured the account details, and you have the script ready which you want to execute on snowflake, you can simply use the below command to run the file on command line.

snowsql –c my_snowflake_connection -f /tmp/sf_script.sql

To export data using SnowSQL use -o or –options connection parameter. An output file and format for the script can be specified using below parameters.

• -o output_file=<output_filename>
• -o output_format=<output_format>

snowsql -c my_snowflake_connection -f /tmp/sf_script.sql -o output_file=/tmp/sf_output.csv -o output_format=csv -o quiet=true

OptionsDescription

• -o quiet=true Removes standard output after executing the command
• -o friendly=false Removes the Good Bye message in standard output
• -o header=false Removes header in the output file
• -o timing=false Removes timing details in the standard output

Reference :

SnowSQL (CLI Client) - Snowflake Documentation

The Extras

Looking for a basic understanding and knowledge about Snowflake’s Dynamic Data Masking? Check out my article on Snowflake DDM:

Play With Snowflake Dynamic Data Masking

I have created a separate article that provides a complete guide on automating the masking policies without using tag-based masking policies implementation approach.

How I Automated Dynamic Data Masking In Snowflake

About me

I am Divyansh Saxena, a Cloud Data Engineer working at IBM. I am a data enthusiast and have knowledge of multiple cloud platforms such as Microsoft Azure, Amazon Web Services, Snowflake, and Google Cloud Storage.

If you like the blog, please share your views and visit https://beacons.ai/data.dude to know more about me ;)

Follow my Medium Channel for regular updates on similar topics