snow sql¶

Syntax¶

snow sql
  --query <query>
  --filename <files>
  --stdin
  --variable <data_override>
  --retain-comments
  --single-transaction / --no-single-transaction
  --enable-templating <enabled_templating>
  --project <project_definition>
  --env <env_overrides>
  --connection <connection>
  --host <host>
  --port <port>
  --account <account>
  --user <user>
  --password <password>
  --authenticator <authenticator>
  --workload-identity-provider <workload_identity_provider>
  --private-key-file <private_key_file>
  --token <token>
  --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>
  --oauth-client-id <oauth_client_id>
  --oauth-client-secret <oauth_client_secret>
  --oauth-authorization-url <oauth_authorization_url>
  --oauth-token-request-url <oauth_token_request_url>
  --oauth-redirect-uri <oauth_redirect_uri>
  --oauth-scope <oauth_scope>
  --oauth-disable-pkce
  --oauth-enable-refresh-tokens
  --oauth-enable-single-use-refresh-tokens
  --client-store-temporary-credential
  --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.

--single-transaction / --no-single-transaction

Connects with autocommit disabled. Wraps BEGIN/COMMIT around statements to execute them as a single transaction, ensuring all commands complete successfully or no change is applied. Default: False.

--enable-templating [LEGACY|STANDARD|JINJA|ALL|NONE]

Syntax used to resolve variables before passing queries to Snowflake. Default: [<_EnabledTemplating.LEGACY: ‘LEGACY’>, <_EnabledTemplating.STANDARD: ‘STANDARD’>].

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

--workload-identity-provider TEXT

Workload identity provider (AWS, AZURE, GCP, OIDC). 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 TEXT

OAuth token to use when connecting to Snowflake.

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

--oauth-client-id TEXT

Value of client id provided by the Identity Provider for Snowflake integration.

--oauth-client-secret TEXT

Value of the client secret provided by the Identity Provider for Snowflake integration.

--oauth-authorization-url TEXT

Identity Provider endpoint supplying the authorization code to the driver.

--oauth-token-request-url TEXT

Identity Provider endpoint supplying the access tokens to the driver.

--oauth-redirect-uri TEXT

URI to use for authorization code redirection.

--oauth-scope TEXT

Scope requested in the Identity Provider authorization request.

--oauth-disable-pkce

Disables Proof Key for Code Exchange (PKCE). Default: False.

--oauth-enable-refresh-tokens

Enables a silent re-authentication when the actual access token becomes outdated. Default: False.

--oauth-enable-single-use-refresh-tokens

Whether to opt-in to single-use refresh token semantics. Default: False.

--client-store-temporary-credential

Store the temporary credential.

--format [TABLE|JSON|JSON_EXT|CSV]

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.

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

echo $?

2

snow sql

$ snow sql
  ╭───────────────────────────────────────────────────────────────────────────────────╮
  │ Welcome to Snowflake-CLI REPL                                                   │
  │ Type 'exit' or 'quit' to leave                                                  │
  ╰───────────────────────────────────────────────────────────────────────────────────╯
  >

> create table my_table (c1 int);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MY_TABLE successfully created.|
+-------------------------------------+

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