snow sql¶

Syntax¶

snow sql
  --query <query>
  --filename <files>
  --stdin
  --variable <data_override>
  --retain-comments
  --project <project_definition>
  --env <env_overrides>
  --connection <connection>
  --host <host>
  --port <port>
  --account <account>
  --user <user>
  --password <password>
  --authenticator <authenticator>
  --private-key-file <private_key_file>
  --token-file-path <token_file_path>
  --database <database>
  --schema <schema>
  --role <role>
  --warehouse <warehouse>
  --temporary-connection
  --mfa-passcode <mfa_passcode>
  --enable-diag
  --diag-log-path <diag_log_path>
  --diag-allowlist-path <diag_allowlist_path>
  --format <format>
  --verbose
  --debug
  --silent
  --enhanced-exit-codes

Arguments¶

None

Options¶

--query, -q TEXT

Query to execute.

--filename, -f FILE

File to execute. Default: [].

--stdin, -i

Read the query from standard input. Use it when piping input to this command. Default: False.

--variable, -D TEXT

String in format of key=value. If provided the SQL content will be treated as template and rendered using provided data.

--retain-comments

Retains comments in queries passed to Snowflake. Default: False.

-p, --project TEXT

Path where the Snowflake project is stored. Defaults to the current working directory.

--env TEXT

String in the format key=value. Overrides variables from the env section used for templates. Default: [].

--connection, -c, --environment TEXT

Name of the connection, as defined in your config.toml file. Default: default.

--host TEXT

Host address for the connection. Overrides the value specified for the connection.

--port INTEGER

Port for the connection. Overrides the value specified for the connection.

--account, --accountname TEXT

Name assigned to your Snowflake account. Overrides the value specified for the connection.

--user, --username TEXT

Username to connect to Snowflake. Overrides the value specified for the connection.

--password TEXT

Snowflake password. Overrides the value specified for the connection.

--authenticator TEXT

Snowflake authenticator. Overrides the value specified for the connection.

--private-key-file, --private-key-path TEXT

Snowflake private key file path. Overrides the value specified for the connection.

--token-file-path TEXT

Path to file with an OAuth token to use when connecting to Snowflake.

--database, --dbname TEXT

Database to use. Overrides the value specified for the connection.

--schema, --schemaname TEXT

Database schema to use. Overrides the value specified for the connection.

--role, --rolename TEXT

Role to use. Overrides the value specified for the connection.

--warehouse TEXT

Warehouse to use. Overrides the value specified for the connection.

--temporary-connection, -x

Uses a connection defined with command line parameters, instead of one defined in config. Default: False.

--mfa-passcode TEXT

Token to use for multi-factor authentication (MFA).

--enable-diag

Whether to generate a connection diagnostic report. Default: False.

--diag-log-path TEXT

Path for the generated report. Defaults to system temporary directory. Default: <system_temporary_directory>.

--diag-allowlist-path TEXT

Path to a JSON file that contains allowlist parameters.

--format [TABLE|JSON]

Specifies the output format. Default: TABLE.

--verbose, -v

Displays log entries for log levels info and higher. Default: False.

--debug

Displays log entries for log levels debug and higher; debug logs contain additional information. Default: False.

--silent

Turns off intermediate output to console. Default: False.

--enhanced-exit-codes

Differentiate exit error codes based on failure type. Default: False.

--help

Displays the help text for this command.

Usage notes¶

You can specify the SQL query to execute using one of the following options

• Specify the query string using the --query option.

• Use the --filename option to execute one or more files containing a SQL query or queries. For example:

  • snow sql -f myfile.sql

  • snow sql -f file1.sql -f file2.sql

• Specify the query as stdin and pipe it to the snow sql command, such as cat my.sql | snow sql.

• If your query contains special characters, such as the dollar sign in SYSTEM functions, that you do not want the shell to interpret, you can do either of the following:

  • Enclose the query in single quotes instead of double quotes, as in:

    snow sql -q 'SELECT SYSTEM$CLIENT_VERSION_INFO()'

  • Escape the special character, as in:

    snow sql -q "SELECT SYSTEM\$CLIENT_VERSION_INFO()"

• Use variables for templating SQL queries with a combination of a <% variable_name %> placeholder in your SQL queries and a -D command-line option, in the form:

  snow sql -q "select * from my-database order by <% column_name %>" -D "column_name=Country"
  

  Copy

  Note

  You can currently use the SnowSQL &variable_name and <% variable_name %> syntax for templates. However, Snowflake recommends using the <% variable_name %> syntax.

• Specify a scripting block in queries. For example:

  EXECUTE IMMEDIATE $$
  -- Snowflake Scripting code
  DECLARE
    radius_of_circle FLOAT;
    area_of_circle FLOAT;
  BEGIN
    radius_of_circle := 3;
    area_of_circle := pi() * radius_of_circle * radius_of_circle;
    RETURN area_of_circle;
  END;
  $$
  ;
  

  Copy

  Note

  When specifying the scripting block directly on the Snowflake CLI command line, the $$ delimiters might not work for some shells because they interpret that delimiter as something else. For example, the bash and zsh shells interpret it as the process ID (PID). To address this limitation, you can use the following alternatives:

  • If you still want to specify the scripting block on the command line, you can escape the $$ delimiters, as in \$\$.

  • You can also put the scripting block with the default $$ delimiters into a separate file and call it with the snow sql -f <filename> command.

The --enhanced-exit-codes option provides information that helps identify whether problems result from query execution or from invalid command options. With this option, the snow sql command provides the following return codes:

• 0: Successful execution

• 2: Command parameter issues

• 5: Query execution issues

• 1: Other types of issues

After the command executes, you can use the echo $? shell command to see the return code.

In this example, the command contains both a query parameter (-q 'select 1') and a query file parameter (-f my.query), which is an invalid parameter combination:

snow sql --enhanced-exit-codes -q 'select 1' -f my.query

echo $?

2

The following examples show the effect of the --enhanced-exit-codes option when the command contains an invalid query (slect is misspelled):

• With the --enhanced-exit-codes option, the command returns a 5 exit code to indicate a query error:

  snow sql --enhanced-exit-codes -q 'slect 1'
  
  echo $?
  

  Copy

  5
  

• Without the --enhanced-exit-codes option, the command returns a 1 exit code to indicate a generic (other) error:

  snow sql --enhanced-exit-codes -q 'slect 1'
  
  echo $?
  

  Copy

  1
  

Alternatively, you can set the SNOWFLAKE_ENHANCED_EXIT_CODES environment variable to 1 to send the enhanced return codes for all snow sql commands.

Examples¶

• The following example uses the SQL SYSTEM$CLIENT_VERSION_INFO system function to return version information about the clients and drivers.

  snow sql --query 'SELECT SYSTEM$CLIENT_VERSION_INFO();'
  

  Copy

  select current_version();
  +-------------------+
  | CURRENT_VERSION() |
  |-------------------|
  | 8.25.1            |
  +-------------------+
  

• The following example shows how you can specify a database using a client-side variable:

  snow sql -q "select * from <% database %>.logs" -D "database=dev"
  

  Copy

  When executed, the command substitutes the value dev in the <% database %> variable to create the dev.logs identifier and then sends the select * from dev.logs SQL query to Snowflake for processing.

  Note

  You can currently use the SnowSQL &variable_name and &``{ variable_name }`` syntax for templates. However, Snowflake recommends using the <% variable_name %> syntax.

• This example shows how to pass in environment variables using the --env option:

  snow sql -q "select '<% ctx.env.test %>'" --env test=value_from_cli
  

  Copy

• By default, Snowflake CLI removes comments in SQL query from the output. The following example uses the --retain-comments option to include the comments in the query results.

  Assume the example.sql file contains the following statements and comment:

  select 'column1';
  -- My comment
  select 'column2';
  

  Copy

  When you execute the following command, -- My comment appears in the query results.

  snow sql -f example.sql --retain-comments
  

  Copy

  select 'column1';
  +-----------+
  | 'COLUMN1' |
  |-----------|
  | ABC       |
  +-----------+
  
  -- My comment
  select 'bar';
  +-----------+
  | 'COLUMN2' |
  |-----------|
  | 123       |
  +-----------+
  

  Copy