Snowflake SnowSQL Configuration Options Reference
In this article, we will be learning how we can make use of various SnowSQL Configuration Options Reference, and how we can make use of them to modify the default SnowSQL behavior.
Executive Summary:
- auto_completion
- client_session_keep_alive
- echo
- editor
- empty_for_null_in_tsv
- environment_variables
- execution_only
- fix_parameter_precedence
- force_put_overwrite
- friendly
- header
- insecure_mode
- log_level
- login_timeout
- noup
- output_file
- output_format
- paging
- progress_bar
- prompt_format
- quiet
- remove_comments
- remove_trailing_semicolons
- results
- rowset_size
- sfqid
- sfqid_in_error
- stop_on_error
- syntax_style
- timing
- timing_in_output_file
- variable_substitution
- wrap
Before we discuss more, we should know that The option names and values are case-insensitive.
You can set these options using any of the following methods:
- In the configuration file
- Using the
-oor--optionparameter when connecting to Snowflake. - Using the
!setcommand once connected to Snowflake.
auto_completion
Enables context-sensitive auto-completion. If enabled, functions, table names, and variables stored in SnowSQL are auto-completed in interactive mode.
client_session_keep_alive
Indicates whether to force a user to log in again after a period of inactivity in a JDBC or ODBC session. When set to True, Snowflake keeps the session active indefinitely, even if there is no activity from the user. When set to False, the user must log in again after four hours of inactivity.
The default value is False, But I prefer to keep it True since I work on snowSQL throughout the day and do not want to keep the session expire after 4 hours 😅
echo
Echoes local input. When set to True, echoes to both stdout and the output file.
editor
Specifies the editor to invoke when the !edit command is issued in SnowSQL. Supported values:
- emacs
- vi
- vim
empty_for_null_in_tsv
If enabled, when output_format is set to TSV, SnowSQL outputs an empty string for each NULL value.
You can see that the none is replaced with an empty string while setting this parameter to true.
environment_variables
Specifies the environment variables to be set in the SnowSQL variables.
execution_only
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.
fix_parameter_precedence
Controls the precedence among the possible sources of the password, proxy password, and private key phrase parameters.
If the value is True, the precedence (from highest to lowest) is:
- The environment variable or the SnowSQL command-line parameter.
- The connection-specific connection parameters, which are the parameters in the config file’s named connection section, e.g. the section
[connections.myconnection]. - The default connection parameters, which are the parameters in the
[connections]section of the config file.
If the value is False, the precedence (from highest to lowest) is:
- The connection-specific connection parameters, which are the parameters in the config file’s named connection section, e.g. the section
[connections.myconnection]. - The environment variable or the SnowSQL command-line parameter.
- The default connection parameters, which are the parameters in the
[connections]section of the config file.
force_put_overwrite
If enabled, SnowSQL forces the PUT command to upload (i.e. stage) data files from a local directory/folder on a client machine to the specified internal (i.e. Snowflake) stage without checking whether the files already exist in the stage. If the files are already present in the destination stage, the PUT command overwrites the existing files.
friendly
If disabled, SnowSQL suppresses the startup and exit messages.
header
Displays the header in the results table rendered by SnowSQL. Disabling this option is useful when you want to retrieve data-only in the results.
Can be used with output_format and timing to produce data-only output.
insecure_mode
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.
log_level
Default log level. Possible values: CRITICAL, ERROR, WARNING, INFO, DEBUG.
login_timeout
Login timeout in seconds.
noup
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.
output_file
Writes output to the specified file in addition to the terminal output.
output_format
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
paging
When enabled, pauses output per screen height. This feature is useful for browsing large result sets. To scroll down, press the [ENTER]/[RETURN] key.
progress_bar
Shows progress bar while transferring data.
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:
quiet
Removes all output data from the terminal, but continues to display error messages and diagnostic data.
remove_commments
Removes comments from the output.
remove_trailing_semicolons
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.
results
Returns the query results. If False,the query is executed asynchronously, no result including any error messages is returned.
NOTE: The difference between results and quiet is that the results does not shows any result even for the error messages.
rowset_size
Number of rows to fetch at once in interactive mode. Results are then fetched for output one rowset at a time.
sfqid
Includes the Snowflake query ID in error messages.
sfqid_in_error
Includes the Snowflake query ID in error messages.
NOTE: The difference between sfqid and sfqid_in_error is that the qid does not shows any successful query execution.
stop_on_error
When an error is encountered, stops query execution, but does not exit.
syntax_style
Sets the text colors for SnowSQL. Currently, the only supported value is default.
timing
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.
timing_in_output_file
Specifies whether to include the execution time details in the output file, if the output_file option is configured. Requires also setting the timing option to True.
If set to False, the line of text under the results table is not included in the output file.
variable_substitution
Substitutes variables with the values. See Using Variables.
wrap
Wraps the output by the terminal width. If False, the outputs are truncated.
Interested to read similar articles on SnowSQL? Visit my SnowSQL list on medium:
#SnowflakeShorts:
This List contains many snowflake short and crisp articles which can be used to revisit some interesting topics in less time. The articles contain blogs related to best features, performance optimizations, interview-based summary knowledge & more Follow my medium for more content
About me
I am Divyansh Saxena, a Cloud Data Engineer working at IBM. I am a data enthusiast and have knowledge of multiple cloud platforms such as Microsoft Azure, Amazon Web Services, Snowflake, and Google Cloud Storage.
If you like the blog, please share your views and visit https://beacons.ai/data.dude to know more about me ;)
Follow my Medium Channel for regular updates on similar topics
