Using SnowSQL¶

Defining variables¶

You can define variables for SnowSQL in several ways:

• Defining variables before connecting (configuration file)

• Defining variables while connecting (-D or --variable command-line flag)

• Defining variables within a session (!define command)

Enabling variable substitution¶

To enable SnowSQL to substitute values for the variables, you must set the variable_substitution configuration option to true in one of the following ways:

• To set this option before you start SnowSQL, open the SnowSQL configuration file in a text editor, and set this option in the [options] section:

  [options]
  variable_substitution = True
  

  Copy

• To set this option when you start SnowSQL, specify the -o command-line flag:

  $ snowsql ... -o variable_substitution=true
  

  Copy

• To set this option when in a SnowSQL session, execute the !set command in the session:

  user#> !set variable_substitution=true
  

  Copy

  Note

  There is currently no option to unset an option value, such as the variable_substitution option. If you need to disable variable substitution, execute the command !set variable_substitution=false.

Substituting variables in a session¶

After you enable variable substitution, you can use variables in SQL statements.

To use a variable in a statement, use the &variable_name syntax. Note that variable names are case-insensitive. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=true

user#> select '&snowshell';

+--------+
| 'BASH' |
|--------|
| bash   |
+--------+

Copy

If the variable_substitution option is not enabled, no variable substitution occurs. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=false

user#> select '&snowshell';

+--------------+
| '&SNOWSHELL' |
|--------------|
| &snowshell   |
+--------------+

Copy

If you refer to a variable that has not been defined, SnowSQL displays an error. For example:

select '&z';

Variable z is not defined

Copy

To combine a variable with text, enclose the variable reference in curly braces. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=true

select '&{snowshell}_shell';

+--------------+
| 'BASH_SHELL' |
|--------------|
| bash_shell   |
+--------------+

Copy

To use an ampersand sign without using substitution, escape the ampersand sign with a second ampersand sign:

&&variable

For example:

user#> !set variable_substitution=true

user#> select '&notsubstitution';

Variable notsubstitution is not defined

user#> select '&&notsubstitution';

+--------------------+
| '&NOTSUBSTITUTION' |
|--------------------|
| &notsubstitution   |
+--------------------+

Copy

Listing variables¶

To list variables, execute the !variables or !vars command in the session:

user#> !variables

+-----------+-------+
| Name      | Value |
|-----------+-------|
| snowshell | bash  |
+-----------+-------+

Copy

Using the built-in variables¶

SnowSQL includes a set of built-in variables that return metadata about statements executed in the current user session.

Each of these variable names begins with two underscore characters (“__”).

__rowcount

Returns the number of rows affected by the most recent DML statement executed by the user.

user#> insert into a values(1),(2),(3);

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
3 Row(s) produced. Time Elapsed: 0.950s

user#> !set variable_substitution=true

user#> select &__rowcount;

+---+
| 3 |
|---|
| 3 |
+---+

Copy

__sfqid

Returns the query ID for the most recent query executed by the user.

user#> !set variable_substitution=true

user#> select * from a;

user#> select '&__sfqid';

+----------------------------------------+
| 'A5F35B56-49A2-4437-BA0E-998496CE793E' |
|----------------------------------------|
| a5f35b56-49a2-4437-ba0e-998496ce793e   |
+----------------------------------------+

Copy

History file¶

The interactive command line history file is named history and is located in ~/.snowsql/history.

Running while connecting (-f connection parameter)¶

To execute a SQL script while connecting to Snowflake, use the -f <input_filename> connection parameter.

An output file for the script can be specified using -o output_file=<output_filename>. In addition, you can use -o quiet=true to turn off the standard output and -o friendly=false to turn off the startup and exit messages.

For example:

snowsql -a myorganization-myaccount -u jsmith -f /tmp/input_script.sql -o output_file=/tmp/output.csv -o quiet=true -o friendly=false -o header=false -o output_format=csv

Copy

For more information about all connection parameters, see Connection parameters reference.

Running in a session (!source or !load command)¶

To run a SQL script after connecting to Snowflake, execute the !source (or !load) command in the session.

For example:

user#> !source example.sql

Copy

Supported tokens¶

SnowSQL supports the following object types as tokens:

• user

• account

• role

• database

• schema

• warehouse

Default prompt¶

The SnowSQL default prompt uses the following tokens and structure:

[user]#[warehouse]@[database].[schema]>

Copy

For example:

jdoe#DATALOAD@BOOKS_DB.PUBLIC>

Copy

Prompt example¶

Continuing the example above, the following !set command executed in the command line adds the role token and changes the token order to user and role, database and schema, then warehouse. It also changes the delimiter for each token to a period (.) and sets the tokens to use different colors:

jdoe#DATALOAD@BOOKS_DB.PUBLIC> !set prompt_format=[#FF0000][user].[role].[#00FF00][database].[schema].[#0000FF][warehouse]>

Copy

This example results in the following prompt for the session:

Exit codes¶

There are several possible exit codes that are returned when SnowSQL quits/exits:

0:

Everything ran smoothly.

1:

Something went wrong with the client.

2:

Something went wrong with the command line arguments.

3:

SnowSQL could not contact the server.

4:

SnowSQL could not communicate properly with the server.

5:

The exit_on_error configuration option was set and SnowSQL exited because of an error.

!abort¶

Aborts a query (specified by query ID). The query ID can be obtained from the History page in the web interface.

For example:

user#> !abort 77589bd1-bcbf-4ec8-9ebc-6c949b00614d;

Copy

!connect¶

SnowSQL supports multiple sessions (i.e. connections) with !connect <connection_name>:

• The connection parameters/options associated with connection_name are stored in the corresponding [connections.<connection_name>] section in the SnowSQL configuration file.

• If a parameter/option is not specified in the [connections.<connection_name>] section, the unspecified parameter will default to the parameters under [connections].

When connecting, the connection is added to your connection stack, and exiting will return you to your previous connection. Quitting will exit all of your connections and quit, no matter how many connections you have.

For example:

Configuration file:

[connections.my_example_connection]
...

Copy

Command line:

user#> !connect my_example_connection

Copy

!define¶

Sets a variable to a specified value, using the following format:

!define <variable_name>=<variable_value>

The name and value must be separated by a single = with no spaces. Valid characters that can be used in the variable are:

0-9a-zA-Z_

For more information on defining and using variables, see Using variables.

!edit¶

Opens up the editor that was set using the editor connection parameter (if no editor was set, the default is vim). The command accepts a query as an argument. If no argument is passed, it opens up the last query that was run.

!exit , !disconnect¶

Drops the current connection and quits SnowSQL if it is the last connection.

!help , !helps , !h¶

Displays the help for SnowSQL commands.

!options , !opts¶

Returns a list of all the SnowSQL configuration options and their currently-set values. These options can be set using the !set command in the current SnowSQL session.

!pause¶

Pause running queries. Press the return key to continue.

!print¶

Prints the specified text to the screen and any files you are currently spooling to.

For example:

user#> !print Include This Text

Copy

!queries¶

Lists all queries that match the specified filters. The default filters are session and amount=25, which return the 25 most recent queries in the current session.

For example:

• Return the 25 most recent queries that ran in this current session:

  !queries session
  

  Copy

• Return the 20 most recent queries run in the account:

  !queries amount=20
  

  Copy

• Return the 20 most recent queries run in the account that took more than 200 milliseconds to run:

  !queries amount=20 duration=200
  

  Copy

• Return the 25 most recent queries that ran in the specified warehouse:

  !queries warehouse=mywh
  

  Copy

This command creates a variable for each query ID returned. Note that variable substitution must be enabled for you to use these variables. For example:

user#> !queries session

+-----+--------------------------------------+----------+-----------+----------+
| VAR | QUERY ID                             | SQL TEXT | STATUS    | DURATION |
|-----+--------------------------------------+----------+-----------+----------|
| &0  | acbd6778-c68c-4e79-a977-510b2d8c08f1 | select 1 | SUCCEEDED |       19 |
+-----+--------------------------------------+----------+-----------+----------+

user#> !result &0

+---+
| 1 |
|---|
| 1 |
+---+

user#> !result acbd6778-c68c-4e79-a977-510b2d8c08f1

+---+
| 1 |
|---|
| 1 |
+---+

Copy

!quit , !q (also CTRL + d)¶

Drops all connections and exits SnowSQL.

!rehash¶

Re-syncs the auto-complete tokens.

Normal use does not require re-syncing the auto-complete tokens. However, forcing an update to the server-side tokens could be useful in certain scenarios (e.g. if a new user-defined function is created in a different session).

!result¶

Returns the result of a completed query (specified by query ID). Query IDs can be obtained from the History page in the web interface or using the !queries command.

If the query is still running, the command waits until the query completes.

For example:

user#> !result 77589bd1-bcbf-4ec8-9ebc-6c949b00614d;

Copy

!set¶

Sets the specified SnowSQL configuration option to a given value using the form <option>=<value>.

Note that there is no option currently to unset an option value. To change the value for an option, run the !set command again with the desired value.

For example:

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 1000              |
 ...
+-----------------------+-------------------+

user#> !set rowset_size=500

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 500               |
 ...
+-----------------------+-------------------+

user#> !set rowset_size=1000

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 1000              |
 ...
+-----------------------+-------------------+

Copy

!source , !load¶

Executes SQL from a file. You can SQL from local files or a URL.

For example:

user#> !source example.sql

user#> !load /tmp/scripts/example.sql

user#> !load http://www.example.com/sql_text.sql

Copy

!spool¶

This command can be executed in two ways:

• Enable spooling and write the results of all subsequent statements/queries to the specified file:

  !spool <file_name>

• Turn off results spooling (if it is enabled):

  !spool off

For example:

user#> select 1 num;

+-----+
| NUM |
|-----|
|   1 |
+-----+

user#> !spool /tmp/spool_example

user#> select 2 num;

+---+
| 2 |
|---|
| 2 |
+---+

user#> !spool off

user#> select 3 num;

+---+
| 3 |
|---|
| 3 |
+---+

user#> !exit

Goodbye!

$ cat /tmp/spool_example

+---+
| 2 |
|---|
| 2 |
+---+

Copy

You can change the output format by first running the !set output_format=<format> command. The option supports the following values:

• expanded

• fancy_grid

• grid

• html

• latex

• latex_booktabs

• mediawiki

• orgtbl

• pipe

• plain

• psql

• rst

• simple

• tsv

Recommended value: psql, fancy_grid, or grid

For example, to output in CSV format:

user#> !set output_format=csv

user#> !spool /tmp/spool_example

Copy

!system¶

Executes a shell command.

!system <command>

The following example runs the ls command in the user’s home directory:

user#> !system ls ~

Copy

!variables , !vars¶

Lists all current variables. Returns each <variable_name>=<variable_value> pair currently defined.

Once a variable is assigned, it cannot be deleted, but its value can be removed by specifying the variable name with no value. For example:

user#> !set variable_substitution=true

user#> !define SnowAlpha=_ALPHA_

user#> !variables

+------------------+---------+
| Name             | Value   |
|------------------+---------|
| snowalpha        | _ALPHA_ |
+------------------+---------+

user#> !define SnowAlpha

user#> !variables

+------------------+-------+
| Name             | Value |
|------------------+-------|
| snowalpha        |       |
+------------------+-------+

user#> !define snowalpha=456

user#> select &snowalpha;

+-----+
| 456 |
|-----|
| 456 |
+-----+

Copy

For more information about setting variables, see Using variables (in this topic).

Error Message: Variable is not defined¶

Cause:

If you see this error message when running commands in SnowSQL, the cause might be an ampersand (&) inside a CREATE FUNCTION command. (The ampersand is the SnowSQL variable substitution character.) For example, executing the following in SnowSQL causes this error:

create function mask_bits(...)
    ...
    as
    $$
    var masked = (x & y);
    ...
    $$;

Copy

The error occurs when the function is created, not when the function is called.

Solution:

If you do not intend to use variable substitution in SnowSQL, you can explicitly disable variable substitution by executing the following command:

!set variable_substitution=false;

Copy

For more information about variable substitution, see Using variables.