How to Execute Snowflake Commands from Shell Script?- Example

Post author:Vithal S
Post last modified:January 22, 2020
Post category:Snowflake
Reading time:5 mins read

Snowflake is one of the leading cloud data warehouse providers. Snowflake provides support for many leading programming languages either by providing JDBC, ODBC drivers, or language specific connectors (Python connector). In this article, we will check how to execute Snowflake commands from shell script with some examples.

Execute Snowflake Commands from Shell Script

The best part about Snowflake is that it provides an interactive terminal called SnowSQL. You can use it to execute queries, create database objects and perform some of the admin tasks. You can call the SnowSQL from within a shell script.

Following is the sample shell script to get a s_student table count from the test_db.

#!/bin/ksh

table=$1
database=$2
schema='public'

snowsql -c myconnection -q 'select count(*) from '$database.$schema.$table'';

Note that, in this shell script, we have used connection file. We have provided all connection information in that file. Optionally, you can provide the required details on command line as well.

For example, below snowsql command uses credential details from command line.

snowsql -a $SNOWSQL_ACCOUNT -u $SNOWSQL_USER -r $SNOWSQL_ROLE -d $SNOWSQL_DATABASE -s $SNOWSQL_SCHEMA -w $SNOWSQL_WAREHOUSE -q 'select count(*) from '$database.$schema.$table'';

Above example, uses SnowSQL environmental variables to store the Snowflake connection parameters.

Now, execute the ksh file by passing required parameters.

For example,

 $ sh shell_script_snowsql.sh S_STUDENT TEST_DB
* SnowSQL * v1.2.2
Type SQL statements or !help
+----------+
| COUNT(*) |
|----------|
|        4 |
+----------+

Advantages of using using Shell Script with Snowflake Commands

Following are some of the advantages of using a shell script to execute Snowflake commands.

Easy to use
You can automate frequently used commands using Shell script. For example, temp table clean up commands.
Shell scripts can be scheduled using schedulers such as crontab.
You can run a sequence of command as a single command. For example, set system variables and execute SQL in a single command.
Portable– It can be executed in any Unix-like operating systems without any modifications.