Configuring SnowSQL

This topic describes how to configure SnowSQL using connection parameters, configuration options, and variables.

In this Topic:

SnowSQL config File

The SnowSQL configuration file, named config, stores connection parameters, default settings, and variables. Text is stored in UTF-8 encoding.

Modify the configuration file as follows:

  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. Modify settings in the following sections:

Attention

  • The password is stored in plain text in the config file. You must explicitly secure the file to restrict access. For example, in Linux or Mac OS, you can set the read permissions to you alone by running chmod:

    $ chmod 700 ~/.snowsql/config
    

Note

  • If a value contains special characters (other than single or double quotes), you must enclose it in either single quotes or double quotes, e.g.:

    password = "my$^pwd"
    
  • If a value contains quote characters in addition to other special characters, escape these quotes using the backslash (\) character, e.g.:

    password = "my$^\"\'pwd"
    

    Note that escaping quote characters is optional in unenclosed values (i.e. values that do not contain other special characters).

  • Multiline values must be enclosed in three single or double quotes (''' or `"""), e.g.:

    prompt_format='''[#FFFF00]‌[user]‌@[account]
    [#00FF00]> '''
    

Connection Parameters Section

In the [connections] section of the config file, optionally set the default connection parameters for SnowSQL, e.g. account name, region ID, user login credentials, and the default database and warehouse. You can also define named connections to make multiple simultaneous connections to Snowflake or store different sets of connection configurations.

For more information, see Connecting Through SnowSQL.

Configuration Options Section

Configure the behavior of SnowSQL by adding settings in the [options] section of the config file:

[options]
<option_name> = <option_value>

Where:

  • <option_name> is the name of the option (case-insensitive). If an invalid name is specified, SnowSQL displays an error.
  • <option_value> specifies a supported value (case-insensitive) for the option, as described below.
+----------------------------+--------------------+----------------------------------------------------------------------------------+
| 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                                  |
| empty_for_null_in_tsv      | False              | Outputs an empty string for NULL values in TSV format                            |
| environment_variables      | ['PATH']           | Specifies the environment variables that can be referenced as SnowSQL variables. |
|                            |                    | The variable names should be comma separated.                                    |
| execution_only             | False              | Executes queries only                                                            |
| exit_on_error              | False              | Quits when SnowSQL encounters an error                                           |
| friendly                   | True               | Shows the splash text and goodbye messages                                       |
| 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)                    |
| login_timeout              | 120                | Login timeout in seconds.                                                        |
| 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. Experimental feature, currently not documented           |
| paging                     | False              | Enables paging to pause output per screen height.                                |
| 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                              |
+----------------------------+--------------------+----------------------------------------------------------------------------------+

See SnowSQL Configuration Options Reference (in this topic) for descriptions of all valid options.

Note

In additon to setting the configuration options in the config file, you can set the options using either of the following methods:

  • While connecting to Snowflake, you can use the -o or --option connection parameter to set these options. For more information, see Connection Parameters Reference.
  • After connecting to Snowflake, you can use the !set command to set these options for the session. For more information, see Commands Reference.

Variables Section

In the [variables] section of the config file, you can store values as variables for reuse. This feature enables you to use user-defined and database values in queries.

For more information, see Using Variables.

SnowSQL Configuration Options Reference

Options modify the default SnowSQL behavior. You can set these options using any of the following methods:

  • In the configuration file (as described in this topic).
  • Using the -o or --option parameter when connecting to Snowflake.
  • Using the !set command once connected to Snowflake.

Note

The option names and values are case-insensitive.

auto_completion

Type:Boolean
Description:Enables context-sensitive auto-completion. If enabled, functions, table names, and variables stored in SnowSQL are auto-completed in interactive mode.
Default:auto_completion=True

echo

Type:Boolean
Description:Echoes local input. When set to True, echoes to both stdout and the output file.
Default:echo=False

editor

Type:

String (constant)

Description:

Specifies the editor to invoke when the !edit command is issued in SnowSQL. Supported values:

  • emacs
  • vi
  • vim
Default:

editor=vim

empty_for_null_in_tsv

Type:Boolean
Description:If enabled, when output_format is set to TSV, SnowSQL outputs an empty string for each NULL value.
Example:empty_for_null_in_tsv=True

environment_variables

Type:List
Description:Specifies the environment variables to be set in the SnowSQL variables.
Example:environment_variables=PATH,USER,AWS_ACCESS_KEY_ID,AWS_SECRET_ACCESS_KEY

execution_only

Type:Boolean
Description:If enabled, SnowSQL executes queries without fetching data. This option is useful when you only want to measure execution times. Note that returned values include any network latency and are not pure server-side execution times.
Example:execution_only=True

exit_on_error

Type:Boolean
Description:If enabled, SnowSQL exits when an error occurs. This behavior is useful to stop running queries when an error is encountered.
Example:exit_on_error=True

friendly

Type:Boolean
Description:If disabled, SnowSQL suppresses the startup and exit messages.
Default:friendly=True

insecure_mode

Type:Boolean
Description:Skips the certificate revocation checks using the Online Certificate Status Protocol (OCSP). This option could be used in an emergency situation in which no OCSP service is accessible. Snowflake strongly recommends that you do not enable this option unless directed by Snowflake Support.
Default:insecure_mode=False

key_bindings

Type:

String (constant)

Description:

Key bindings to use. Possible values:

  • emacs: [CTRL]-a is home, [CTRL]-e is end. All Emacs key bindings for the REPL environment are available.
  • vi: You can use all modal editing features offered by vi in the REPL environment.
Note:

The value cannot be changed by !set command during the SnowSQL session. Instead, set the value in the configuration file or on the command line when connecting to Snowflake.

Default:

key_bindings=vi

log_bootstrap_file

Type:String (path)
Description:Bootstrap log file location. If not specified, log_file is used as the base name followed by _bootstrap. For exampel, by default, the log file name is log_bootstrap.
Default:log_bootstrap_file=~/.snowsql/bootlog

log_file

Type:String (path)
Description:log_file location.
Default:log_file=~/.snowsql/log

log_level

Type:String (constant)
Description:Default log level. Possible values: CRITICAL, ERROR, WARNING, INFO, DEBUG.

login_timeout

Type:Number
Description:Login timeout in seconds.
Default:login_timeout=120

noup

Type:Boolean
Description:Prevents SnowSQL from downloading and installing a new version if True. By default, SnowSQL auto-upgrades to the latest version if no version is specified.
Default:noup=False

output_file

Type:String (path and file name)
Description:Writes output to the specified file in addition to the terminal output.
Default:None

output_format

Type:

String (constant)

Description:

Specifies the format of the results displayed in the terminal. Possible values:

  • csv
  • expanded
  • fancy_grid
  • grid
  • html
  • json
  • latex
  • latex_booktabs
  • mediawiki
  • orgtbl
  • pipe
  • plain
  • psql
  • rst
  • simple
  • tsv

Recommended values for tabular results: psql , grid, or fancy_grid

Recommended values for data-only results (used in combination with header and timing set to False): plain , csv, or tsv

Default:

output_format=psql

paging

Type:Boolean
Description:When enabled, pauses output per screen height. This feature is useful for browsing large result sets. To scroll down, press the [ENTER]/[RETURN] key.
Default:paging=False

prompt_format

Type:

string

Description:

Changes the SnowSQL prompt format.

The SnowSQL prompt dynamically displays the current user, warehouse, database, and schema by default. Dynamic tokens are written as [<token>], e.g. [user] or [warehouse].You can change the Snowflake object order, delimiter, and color. Change the object color by defining a pygments token in brackets.

For example, change the object order to user, database and schema, then warehouse. Change the delimiter to a period. Change the <user> object name to red, the <database> and <schema> names to green, and the <warehouse> name to blue:

prompt_format=[#FF0000][user]@[#00FF00][database][schema][#0000FF][warehouse]
Default:

None

quiet

Type:Boolean
Description:Removes all output data from the terminal, but continues to display error messages and diagnostic data.
Default:quiet=True

remove_comments

Type:Boolean
Description:Removes comments from the output.
Default:remove_comments=False

remove_trailing_semicolons

Type:Boolean
Description:Removes trailing semicolons from SQL text before sending queries to Snowflake. Note that removing the semicolons can prevent Snowflake from using cached results from different clients when the USE_CACHED_RESULT session parameter is enabled.
Default:remove_trailing_semicolons=True

results

Type:Boolean
Description:Returns the query results. If False, no query result is returned except for the execution status.
Default:results=True

rowset_size

Type:Number
Description:Number of rows to fetch at once in interactive mode. Results are then fetched for output one rowset at a time.
Default:rowset_size=1000

sfqid_in_error

Type:Boolean
Description:Includes the Snowflake query ID in error messages.
Default:sfqid_in_error=False

stop_on_error

Type:Boolean
Description:When an error is encountered, stops query execution, but does not exit.
Default:stop_on_error=False

syntax_style

Type:String (constant)
Description:Sets the text colors for SnowSQL. Currently, the only supported value is default.
Default:syntax_style=default

timing

Type:

Boolean

Description:

Specifies whether to display the number of rows produced and elapsed time for SQL statements that have executed. This information is displayed as a line of text under the results table rendered by SnowSQL. If set to False, the line of text under the results table is not displayed.

Can be used in conjunction with header and output_format to produce data-only output.

Default:

timing=True

variable_substitution

Type:Boolean
Description:Substitutes variables with the values.
Default:variable_substitution=False

wrap

Type:Boolean
Description:Wraps the output by the terminal width. If False, the outputs are truncated.
Default:wrap=True