0

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?

Hana
  • 1,330
  • 4
  • 23
  • 38
  • Not easilly, since SQL escaping rules for single quotes, double quotes or other special characters vary between implementations. There is no universal shell interface with SQL grammar that can reliably pass arguments, especially with the unsafe string composition you use here. You need something to binds the parameters with a prepared statement. – Léa Gris Oct 08 '20 at 16:53

2 Answers2

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%
Léa Gris
  • 17,497
  • 4
  • 32
  • 41
  • This isn't working @Lea - I get a syntax error: syntax error line 14 at position 28 unexpected '&'. – Hana Oct 08 '20 at 17:31
  • 1
    @Hana Make sure [variable_substition](https://docs.snowflake.com/en/user-guide/snowsql-config.html#variable-substitution) is set to `True`. It is `False` by default. You can set that in your config file or on the command line, like `-o variable_substitution=True`. – nofinator Dec 23 '20 at 05:02
0

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
marcothesane
  • 6,192
  • 1
  • 11
  • 21
  • with double quotes around shell variables expansion, to prevent globbing an word splitting. – Léa Gris Oct 08 '20 at 17:11
  • thank you for answer! this is actually not working for me. The sql is running as "select * from table where cli_id = '&cli_id' and store_id = '&store_id';" It doesn't look like its taking in the arguments – Hana Oct 08 '20 at 17:25
  • @Hana Make sure [variable_substition](https://docs.snowflake.com/en/user-guide/snowsql-config.html#variable-substitution) is set to `True`. It is `False` by default. You can set that in your config file or on the command line, like `-o variable_substitution=True`. – nofinator Dec 23 '20 at 05:01