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.
Related Articles,
- Access Snowflake using Snowsql without Password Prompt – Snowsql Environment Variables
- Snowsql Command Line Options and Examples
- Execute SQL Script File using Snowflake Snowsql Variable Substitution
- Export Snowflake Table Data to Local CSV format – SnowSQL example
Hope this helps 🙂