Pass arguments to a sql file via command line arguments in shell script

Question

I am using SnowSQL to run a SQL script. I have command line parameters which needs to also be passed to the SQL script during my SnowSQL call. Here is what I have:

SQL script (file.sql):

select * from table where cli_id = '$1' and store_id = '$2'; 

Shell script (run.sh):

snowsql -c credentials -f file.sql

I run this in my terminal with the following command:

sh run.sh 123 555

This isn't working - how can I fix it to pass the parameters to the SQL file correctly?

Answer 1

#!/usr/bin/env bash
snowsql -c credentials -D COLOR="$1" -D SIZE="$2" -f file.sql

And in the SQL file:

SELECT id, name, description IN articles WHERE colour=&COLOR AND size=&SIZE;

See: Using SnowSQL — Snowflake Documentation / Using Variables / Defining While Connecting

Defining While Connecting (-D or --variable Connection Parameter)

To define variables while connecting to Snowflake, on the terminal command line, specify the -D or --variable connection parameters followed by the variable name and value, in the form of <variable_name>=<variable_value>.

For example:

Linux/macOS

$ snowsql ... -D tablename=CENUSTRACKONE --variable db_key=$DB_KEY

Windows

$ snowsql ... -D tablename=CENUSTRACKONE --variable db_key=%DB_KEY%

Answer 2

Try changing the script file.sql to:

select * from table where cli_id = '&cli_id' and store_id = '&store_id'; 

.. and run.sh should be:

snowsql -c credentials --variable cli_id=$1 --variable store_id=$2  -f file.sql