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:

Starting SnowSQL and Connecting to Snowflake

Note

Passwords (and proxy passwords) cannot be passed as a connection parameter. Passwords must be specified in one of the following ways:

  • Defined in the SnowSQL configuration file using the password or proxy_password option.
  • Specified using the SNOWSQL_PWD or SNOWSQL_PROXY_PWD environment variables.
  • Entered via interactive prompt in SnowSQL (passwords only; SnowSQL does not prompt for proxy passwords).

Also, on Windows, the Cygwin terminal doesn’t prompt for your account name, username, or password. This is because SnowSQL cannot enable TTY mode in Cygwin terminal.

Connection Syntax

$ snowsql <connection_parameters>

Where connection_parameters are as follows. For descriptions of the parameters, see Connection Parameters Reference.

-a, --accountname TEXT          Account name to connect to Snowflake. Honors $SNOWSQL_ACCOUNT.
-u, --username TEXT             User login name to connect to Snowflake. Honors $SNOWSQL_USER.
-d, --dbname TEXT               Default database to use in the session. Honors $SNOWSQL_DATABASE.
-s, --schemaname TEXT           Default schema name to use in the session. Honors $SNOWSQL_SCHEMA.
-r, --rolename TEXT             Default role to use in the session. Honors $SNOWSQL_ROLE.
-w, --warehouse TEXT            Default warehouse to use in the session. Honors $SNOWSQL_WAREHOUSE.
-h, --host TEXT                 Host address for the Snowflake account. Honors $SNOWSQL_HOST.
-p, --port INTEGER              Port number to use for connection. Honors $SNOWSQL_PORT.
    --region TEXT               Snowflake Region where Snowflake account is located. Honors $SNOWSQL_REGION.
-m, --mfa-passcode TEXT         Token to use for multi-factor authentication (MFA).
    --mfa-passcode-in-password  Specify that the MFA token is appended to the end of the password.
    --abort-detached-query      Abort a query if connection is lost. By default, it won't abort even if the connection is lost.
    --proxy-host TEXT           Proxy server hostname. Honors $SNOWSQL_PROXY_HOST.
    --proxy-port INTEGER        Proxy server port number. Honors $SNOWSQL_PROXY_PORT.
    --proxy-user TEXT           Proxy server username. Honors $SNOWSQL_PROXY_USER.
    --authenticator TEXT        Authenticator: snowflake, externalbrowser (to use any IdP and a web browser), or https://<your_okta_account_name>.okta.com (the URL prefix for Okta).
-v, --version                   Shows version of snowsql or, if specified with a version, that version will be used.
    --noup                      Disable auto-upgrade for this run. If no version is specified, the latest snowsql in ~/.snowsql/ will be used.
-D, --variable TEXT             Set a variable for the session, which can be referenced using $<var>.
-o, --option TEXT               Set an option.
-f, --filename PATH             File to execute.
-q, --query TEXT                Query to execute.
    --config PATH               Path and name of the config file. By default, ~/.snowsql/config is referenced.
-P, --prompt                    Force password prompt. By default, $SNOWSQL_PWD is used to set the password.
-M, --mfa-prompt                Force passcode prompt.
-c, --connection TEXT           Name of connection parameters to use.
-?, --help                      Show this message and exit.

Connection Error Handling

Cannot open self /usr/bin/snowsql or archive /usr/bin/snowsql.pkg (Linux Only)

Due to a limitation in pyinstaller, the program that packages SnowSQL into a stand-alone executable from Python source code, prelink mistakenly strips parts of the snowsql executable and causes the error.

To avoid this issue, the SnowSQL installer attempts to update the prelink configuration file in /etc/prelink.conf.d/snowsql.conf for the snowsql executable such that prelink does not alter the file. Unfortunately, this configuration update cannot be made by the Auto-upgrade process.

Work with your system administrator to run the following command on your workstation:

$ sudo bash -c "echo '-b snowsql' > /etc/prelink.conf.d/snowsql.conf"

If you install snowsql in your user home directory, the issue is less likely to occur because prelink is, by default, configured to scan the shared binary directories, e.g., /usr/bin or /bin, and does not alter programs in your home directory.

Connection Parameters Reference

-a <string> , --accountname <string>

Required

Specifies the name of your Snowflake account, where string is the name assigned to your account by Snowflake. In the URL you received from Snowflake, your account name is the first segment in the domain (e.g. abc123 in https://abc123.snowflakecomputing.com or https://abc123.eu-central-1.snowflakecomputing.com).

string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_ACCOUNT
Windows:%SNOWSQL_ACCOUNT%

For example, in Linux or Mac OS:

$ export SNOWSQL_ACCOUNT=abc123

$ snowsql -a $SNOWSQL_ACCOUNT

This connection parameter can also be set in the configuration file.

-u <string> , --username <string>

Specifies the login name of the user with whom you connect to the specified account. string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_USER
Windows:%SNOWSQL_USER%

This connection parameter can also be set in the configuration file.

--region <string>

Specifies the ID for the Snowflake Region where your account is located (use only if your account is not in US West):

Snowflake Region Region ID
US East us-east-1
EU (Frankfurt) eu-central-1
Asia Pacific (Sydney) ap-southeast-2

string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_REGION
Windows:%SNOWSQL_REGION%
-d <string> , --dbname <string>

Specifies the database to use by default in the client session (can be changed after login). string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_DATABASE
Windows:%SNOWSQL_DATABASE%

This connection parameter can also be set in the configuration file.

-s <string> , --schemaname <string>

Specifies the database schema to use by default in the client session (can be changed after login). string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_SCHEMA
Windows:%SNOWSQL_SCHEMA%

This connection parameter can also be set in the configuration file.

-r <string> , --rolename <string>

Specifies the role to use by default for accessing Snowflake objects in the client session (can be changed after login). string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_ROLE
Windows:%SNOWSQL_ROLE%

This connection parameter can also be set in the configuration file.

-w <string> , --warehouse <string>

Specifies the virtual warehouse to use by default for queries, loading, etc. in the client session (can be changed after login). string can also be an environment variable:

Linux/Mac OS:$SNOWSQL_WAREHOUSE
Windows:%SNOWSQL_WAREHOUSE%

This connection parameter can also be set in the configuration file.

-h <string> , --host <string>

For internal use only

Specifies the address of the host to which you connect in Snowflake. This option does not need to be specified because the host address is determined automatically by concatenating the account name you specified and the rest of the Snowflake domain snowflakecomputing.com.

-p <string> , --port <string>

For internal use only

Specifies the port number to use for connection. This option does not need to be specified because the port number is always 443.

-m <string> , --mfa-passcode <string>
Specifies the second token for MFA if you pass in the passcode in the command line.
--mfa-passcode-in-password

Appends the MFA passcode to the end of the password.

You can force the password prompt and type the password followed by the MFA passcode. For example if the MFA token was 123456 and the password was PASSWORD:

snowsql ... -P ...

Password: PASSWORD123456
--abort-detached-query TRUE | FALSE
Aborts a query if the connection between the client and server is lost. By default, an active query does not abort if the connection is lost.
--proxy-host <string>

Specifies a proxy server hostname.

This connection parameter can also be set in the configuration file.

--proxy-port <number>

Specifies the port number for the proxy server.

This connection parameter can also be set in the configuration file.

--proxy-user <string>
Specifies the name of the user used to connect to the proxy server. The proxy password for this user must be set in the config file or using the environment variable SNOWSQL_PROXY_PWD.
--authenticator <string>

Specifies the authenticator to use for verifying user login credentials:

  • snowflake (Default) to use the internal Snowflake authenticator.
  • externalbrowser to authenticate using a SAML 2.0-compliant identify provider (IdP) that has been defined for your account. SnowSQL launches a web browser to allow users to enter IdP credentials.
  • https://<your_okta_account_name>.okta.com (URL endpoint for Okta) to authenticate programmatically (only supported if your IdP is Okta).

For more information, see Managing/Using Federated Authentication.

Note

The externalbrowser authenticator is only supported in terminal windows that have web browser access. For example, a terminal window on a remote machine accessed through a SSH (Secure Shell) session may require additional setup to open a web browser.

-v <string> , --version <string>
Uses the specified SnowSQL version. If no version is specified, displays the latest SnowSQL version installed.
--noup
Disables auto-upgrade for this run. If this option is not included and a newer version is available, SnowSQL automatically downloads and installs the new version. The next time you run SnowSQL, the new version is used.
--versions
Lists all available versions of SnowSQL that can be installed and run. To install an earlier SnowSQL version from the list, use the -v option and specify the version you want to install.
-D <string> , --variable <string>

Defines SnowSQL variables on the command line. This option can be used to set specific variables to use in Snowflake. For example:

... -D tablename=CENUSTRACKONE --variable db_key=$DB_KEY ...
-o <string> , --option <string>
Defines SnowSQL options on the command line. These options override any options that have been set in the SnowSQL configuration file. For descriptions of the options you can set/override, see SnowSQL Options Reference.
-f <string> , --filename <string>
Specifies a SQL file to execute in batch mode.
-q <string> , --query <string>
Specifies a SQL query to execute.
--config <string>

Specifies the location of the SnowSQL configuration file. Include this command line option if you want to move or copy the configuration file from the default location.

Default value is:

Linux/Mac OS:~/.snowsql/
Windows:%USERPROFILE%\.snowsql\
-P , --prompt
Forces a password prompt when the password is stored in the SnowSQL configuration file.
-M, --mfa-prompt
Forces a prompt for the second token for MFA. Alternatively use --mfa-passcode <string> if you want to pass in to the command line.
-c <string> , --connection <string>
Specifies a connection to use, where string is the name of a connection defined in the SnowSQL configuration file. See Connecting Using a Named Connection.
-? , --help
Shows the command line quick usage guide.

Connecting Using a Named Connection

Use the -c <string> (or --connection <string>) connection parameter to specify a named connection, where string is the name of a connection defined in the configuration file.

For example, your configuration file is configured as follows:

[connections.my_example_connection]
accountname = abc123
username = jsmith
password = xxxxxxxxxxxxxxxxxxxx
dbname = mydb
schemaname = public
warehousename = mywh
region = eu-central-1

To connect using the my_example_connection connection, type the following on the command line:

snowsql -c my_example_connection

Connecting Through a Proxy Server

To configure your proxy settings, complete either of the following steps:

  • Configure the environment variables:

    • HTTP_PROXY
    • HTTPS_PROXY

    Note

    Requires SnowSQL 1.1.20 or later. To determine your current version, see Understanding SnowSQL Versioning.

    For example:

    • Linux or Mac OS X:

      export HTTP_PROXY='http://username:password@proxyserver.company.com:80'
      export HTTPS_PROXY='http://username:password@proxyserver.company.com:80'
      
    • Windows:

      set HTTP_PROXY='http://username:password@proxyserver.company.com:80'
      set HTTPS_PROXY='http://username:password@proxyserver.company.com:80'
      

    Note

    Snowflake’s security model does not allow Secure Sockets Layer (SSL) proxies (using an HTTPS certificate). Your proxy server must use a publicly-available Certificate Authority (CA), reducing potential security risks such as a MITM (Man In The Middle) attack through a compromised proxy.

    If you must use your SSL proxy, we strongly recommend that you update the server policy to pass through the Snowflake certificate such that no certificate is altered in the middle of communications.

  • Specify the proxy parameters for SnowSQL:

    For example:

    snowsql.exe -a <account_name> -u <username> --proxy-host <proxy_host> --proxy-port <proxy_port>
    

    The proxy username can optionally be set using the --proxy-user <string> parameter. The proxy password for the user must be set in the config file or using the environment variable SNOWSQL_PROXY_PWD.

    For information about the proxy parameters and all other connection parameters, see Connection Parameters Reference.

Connecting Using a Web Browser for Federated Authentication

To use browser-based authentication for SnowSQL, add --authenticator externalbrowser to your SnowSQL connection parameters:

snowsql -a <accountname> -u <username> --authenticator externalbrowser

Here are the steps:

  1. Connect to Snowflake using SnowSQL. The default web browser in your operation system launches or opens a new tab or window, displaying the IdP authentication page.
  2. Enter your IdP username and password. If multi-factor authentication is enabled for your account, you may need to type a passcode from another device or confirm the login for authentication.
  3. When the IdP has authenticated your credentials, the browser displays a success message. Return to the terminal window and use the Snowflake session that has started.

Using Environment Variables

Currently, environment variables can only be used to pre-specify some command line parameter values such as password, host, and database. Environment variables are not available to use in SnowSQL variable substitution unless they are explicitly specified on the command line when starting SnowSQL, using either the -D or --variable connection parameter, e.g.:

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.

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.

Using Variables

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

Variable Definition

There are several ways you can define variables:

  • Before connecting:

    In the [variables] section of the configuration file:

    [variables]
    <variable_name>=<variable_value>
    

    Where:

    • variable_name is a string of case-insensitive alphanumeric characters.
    • variable_value is a string value and may be enclosed by single quotes or double quotes.

    For example:

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

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

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

user#> !options

+----------------------------+-------------------+-------------------------------------------------------------------------------+
| Name                       | Value             | Help                                                                          |
|----------------------------+-------------------+-------------------------------------------------------------------------------|
| auto_completion            | True              | Displays auto-completion suggestions for commands and Snowflake objects       |
| echo                       | False             | Outputs the SQL command to the terminal when it is executed                   |
| editor                     | vim               | Changes the editor to use for the !edit command                               |
| execution_only             | False             | Executes queries only                                                         |
| exit_on_error              | False             | Quits when SnowSQL encounters an error                                        |
| friendly                   | True              | Hides the splash text and goodbye message                                     |
| header                     | True              | Outputs the header in query results                                           |
| insecure_mode              | False             | Turns off OSCP certificate checks                                             |
| key_bindings               | emacs             | Changes keybindings for navigating the prompt to emacs or vi                  |
| log_bootstrap_file         | ~/.snowsql/log_.. | SnowSQL bootstrap log file location                                           |
| log_file                   | ~/.snowsql/log    | SnowSQL main log file location                                                |
| log_level                  | CRITICAL          | Changes the log level (critical, debug, info, error, warning)                 |
| noup                       | False             | Turns off auto upgrading Snowsql                                              |
| output_file                | None              | Writes output to the specified file in addition to the terminal               |
| output_format              | psql              | Sets the output format for query results.                                     |
| prompt_format              | [user]#[warehou.. | Sets the prompt format. For help, see the documention                         |
| sfqid_in_error             | False             | Turns on/off Snowflake query id in the error message                          |
| quiet                      | False             | Hides all output                                                              |
| remove_comments            | False             | Removes comments before sending query to Snowflake                            |
| remove_trailing_semicolons | True              | Removes trailing semicolons from SQL text before sending queries to Snowflake |
| results                    | True              | If set to off, queries will be sent, but no results will be fetched.          |
| rowset_size                | 1000              | Sets the size of rowsets to fetch from the server.                            |
|                            |                   | Set the option low for smooth output, high for fast output.                   |
| stop_on_error              | False             | Stops all queries yet to run when SnowSQL encounters an error                 |
| syntax_style               | default           | Sets the colors for the text of SnowSQL.                                      |
| timing                     | True              | Turns on/off timing for each query                                            |
| variable_substitution      | False             | Substitutes variables (starting with '&') with values                         |
| wrap                       | True              | Truncates lines at the width of the terminal screen                           |
+----------------------------+-------------------+-------------------------------------------------------------------------------+

For more information about these options, see SnowSQL Options Reference.

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

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

  • psql
  • plain
  • simple
  • grid
  • fancy_grid
  • pipe,
  • orgtbl
  • rst
  • mediawiki
  • html
  • latex
  • latex_booktabs
  • tsv
  • expanded
  • custom

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.