Using SnowSQL

This topic describes how to use SnowSQL, including starting/stopping the client, using commands and variables within the client, and other general usage information.

In this Topic:

Using Commands

While connected to Snowflake, you can issue commands to take specific actions. All commands in SnowSQL start with an exclamation point (!), followed by the command name.

For example:

user#> !help

+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+
| Command    | Use                                       | Aliases     | Description                                                                                |
|------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------|
| !abort     | !abort <query id>                         |             | Abort a query                                                                              |
| !connect   | !connect <connection_name>                |             | Create a new connection                                                                    |
| !define    | !define <variable>=<value>                |             | Define a variable as the given value                                                       |
| !edit      | !edit <query>                             |             | Opens up a text editor. Useful for writing longer queries. Defaults to last query          |
| !exit      | !exit                                     | !disconnect | Drop the current connection                                                                |
| !help      | !help                                     | !helps, !h  | Show the client help.                                                                      |
| !options   | !options                                  | !opts       | Show all options and their values                                                          |
| !print     | !print <message>                          |             | Print given text                                                                           |
| !queries   | !queries help, <filter>=<value>, <filter> |             | Lists queries matching the specified filters. Write <!queries> help for a list of filters. |
| !quit      | !quit                                     | !q          | Drop all connections and quit SnowSQL                                                      |
| !rehash    | !rehash                                   |             | Refresh autocompletion                                                                     |
| !result    | !result <query id>                        |             | See the result of a query                                                                  |
| !set       | !set <option>=<value>                     |             | Set an option to the given value                                                           |
| !source    | !source <filename>, <url>                 | !load       | Execute given sql file                                                                     |
| !spool     | !spool <filename>, off                    |             | Turn on or off writing results to file                                                     |
| !system    | !system <system command>                  |             | Run a system command in the shell                                                          |
| !variables | !variables                                | !vars       | Show all variables and their values                                                        |
+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+

For detailed descriptions of all commands, see Commands Reference (in this topic).

Using Variables

You can store and reuse values in variables. This feature enables you to use user-defined and database values in queries.

Defining Variables

There are several ways you can define variables:

  • Before connecting:

    1. Open the config configuration file in a text editor. The default location of the file is:

      Linux/Mac OS:~/.snowsql/
      Windows:%USERPROFILE%\.snowsql\

      Note

      You can change the default location by including the --config <path> connection parameter when starting SnowSQL.

    2. Add variables in the [variables] section:

      [variables]
      <variable_name>=<variable_value>
      

    Where:

    • variable_name is a string of alphanumeric characters (case-insensitive) representing the name of the variable.
    • variable_value is a string representing the value for the variable. If needed, the string can be enclosed by single or double quotes.

    For example:

    [variables]
    tablename=CENUSTRACKONE
    
  • While connecting to Snowflake:

    Specify the -D or --variable <string> connection parameters on the terminal command line:

    $ snowsql ... -D <variable_name>=<variable_value> --variable <variable_name>=<variable_value> ...
    

    For example:

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

    In the above example, --variable sets a Snowflake variable named db_key to the DB_KEY environment variable.

  • After connecting:

    Execute the !define command in a session:

    user#> !define <variable_name>=<variable_value>
    

    For example:

    user#> !define tablename=CENUSTRACKONE
    

Variable Substitution

If the variable_substitution configuration option is enabled, defined variables can be referenced in SnowSQL with their values substituted into queries by using the &<variable> syntax. Variables references are case-insensitive:

user#> !define snowshell=bash

user#> !set variable_substitution=true

user#> select '&snowshell';

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

If variable_substitution is disabled, no variable substitution occurs. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=false

user#> select '&snowshell';

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

If a variable is not defined and the variable_substitution option is enabled, SnowSQL displays an error.

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 '&notsubsitution';

Variable notsubsitution is not defined

user#> select '&&notsubsitution';

+-------------------+
| '&NOTSUBSITUTION' |
|-------------------|
| &notsubsitution   |
+-------------------+

Note

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

Using Auto-Complete

Various SQL functions, table names, and variables are stored in SnowSQL and are auto-completed in interactive mode. To select an auto-complete suggestion, press the [Tab] key. To choose a different suggestion, use the [up-arrow] and [down-arrow] keys to highlight the desired option, and then press [Tab].

To disable auto-complete interactively, set the auto_completion configuration option to False in the configuration file.

Viewing Your Command Line History

Your recent command line history can be recalled by using the [up-arrow] key. Press the key repeatedly to scroll through the buffer.

History File

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

Running Batch Scripts

Use the -f <input_filename> connection parameter to execute a SQL file. An output file can be specified using -o output_file=<output_filename>.

Use -o quiet=true to turn off the standard output and -o friendly=false to turn off the startup and exit messages. For more information about all connection parameters, see Connection Parameters Reference.

For example:

snowsql -a abc123 -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

Exporting Data

Combine the -o output_format=<output_format> and -o output_file=<output_filename> connection parameters to export query results to a file in a defined format.

Disconnecting from Snowflake and Stopping SnowSQL

SnowSQL provides separate commands for:

  • Exiting individual connections (i.e. sessions) without stopping SnowSQL.
  • Quitting SnowSQL, which also automatically terminates all connections.

To exit a connection/session, use the !exit command (or its alias, !disconnect). You can then connect again using !connect <connection_name> if you can defined multiple connections in the SnowSQL config file. Note that, if you only have one connection open, the !exit command also quits/stops SnowSQL.

To exit all connections and then quit/stop SnowSQL, use the !quit command (or its alias, !q). You can also type [CTRL]-d on your keyboard.

Exit Codes

There are several different 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.

Default Key Bindings

[Tab]
Accept the current auto-complete suggestion.
[CTRL]-d
Quit/stop SnowSQL.

Commands Reference

!abort

Aborts the specified query:

user#> !abort <query_id>;

query_id can be obtained from the History page in the web interface.

For example:

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

!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]
..

Command line:

user#> !connect my_example_connection

!define

Sets a variable to a given value:

!define <VARIABLE>=<VALUE>

Valid variable characters are: 0-9a-zA-Z_

The name and value must be separated by a single =.

!edit

Opens up an editor, determined by setting the editor option (default editor is vim). You must save before or while exiting, or else the text will not be saved. 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 options and their currently-set values. These options can be set using the using the !set command in the current SnowSQL session.

For more information, see SnowSQL Configuration Options Reference.

Note

These options can also be set in the configuration file for SnowSQL or as parameters in the command line when invoking SnowSQL.

!print

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

user#> !print Include This Text

!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:

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

    !queries session
    
  • Returns the 20 most recent queries run in the account:

    !queries amount=20
    
  • Returns the 20 most recent queries run in the account that took more than 200 milliseconds to run:

    !queries amount=20 duration=200
    
  • Returns the 25 most recent queries that ran in the testwarehouse warehouse:

    !queries warehouse=mywh
    

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 |
+---+

!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

Fetches the result of a finished query by ID. Query IDs can be obtained from the History page in the web interface or using the !queries command.

If the query is not finished, the command waits until the query completes.

user#> !result <query_id>;

For example:

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

!set

Sets the specified SnowSQL option to a given value:

!set <option>=<value>

For example:

user#> !options

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

user#> !set rowset_size=500

user#> !options

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

Important

Spaces are not allowed between an option and its value. Some options support a defined set of values; SnowSQL returns an error if the provided value is unsupported. You cannot create new options.

For a list of all the options you can set, use the !options command.

!source , !load

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

For example:

user#> !source example.sql

user#> !load example.sql

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

!spool

Writes the results of all queries to the specified file going forward:

!spool <FILENAME>

Turns off results spooling, if 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 |
+---+

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

  • custom
  • 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

!system

Executes a shell command.

!system <command>

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

user#> !system ls ~

!variables , !vars

Lists all current variables. Returns each <variable>=<value> pair currently defined.

Once a variable is assigned, it cannot be deleted, but its value can be removed. 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 |
+-----+

For more information about setting variables, see Using Variables.