snow sql¶
Executes Snowflake query.
Query to execute can be specified using query option, filename option (all queries from file will be executed)
or via stdin by piping output from other command. For example cat my.sql | snow sql -i
.
The command supports variable substitution that happens on client-side. Both $VARIABLE
or ${ VARIABLE }
syntax are supported.
Syntax¶
snow sql
--query <query>
--filename <file>
--stdin
--variable <data_override>
--connection <connection>
--account <account>
--user <user>
--password <password>
--authenticator <authenticator>
--private-key-path <private_key_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
Arguments¶
None
Options¶
--query, -q TEXT
Query to execute.
--filename, -f FILE
File to execute.
--stdin, -i
Read the query from standard input. Use it when piping input to this command.
--variable, -D TEXT
String in format of key=value. If provided the SQL content will be treated as template and rendered using provided data.
--connection, -c, --environment TEXT
Name of the connection, as defined in your
config.toml
. Default:default
.--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-path TEXT
Snowflake private key path. Overrides the value specified for the connection.
--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 connection defined with command line parameters, instead of one defined in config.
--mfa-passcode TEXT
Token to use for multi-factor authentication (MFA).
--enable-diag
Run python connector diagnostic test.
--diag-log-path TEXT
Diagnostic report path.
--diag-allowlist-path TEXT
Diagnostic report path to optional allowlist.
--format [TABLE|JSON]
Specifies the output format.
--verbose, -v
Displays log entries for log levels
info
and higher.--debug
Displays log entries for log levels
debug
and higher; debug logs contains additional information.--silent
Turns off intermediate output to console.
--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 specify a file that contains the SQL query or queries to execute.Specify the query as
stdin
and pipe it to thesnow sql
command, such ascat 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"
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();'
SELECT SYSTEM$CLIENT_VERSION_INFO();
+----------------------------------------------------------------------------------------------------------------------+
| SYSTEM$CLIENT_VERSION_INFO() |
|----------------------------------------------------------------------------------------------------------------------|
| [ { "clientId": "DOTNETDriver", "minimumSupportedVersion": "2.0.7", "minimumNearingEndOfSupportVersion": |
| "2.0.9", "recommendedVersion": "2.1.2", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { |
| "clientId": "GO", "minimumSupportedVersion": "1.6.3", "minimumNearingEndOfSupportVersion": "1.6.6", |
| "recommendedVersion": "1.6.25", "deprecatedVersions": [], "_customSupportedVersions_": ["1.1.5"] }, { |
| "clientId": "JDBC", "minimumSupportedVersion": "3.13.10", "minimumNearingEndOfSupportVersion": "3.13.14", |
| "recommendedVersion": "3.14.3", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { |
| "clientId": "JSDriver", "minimumSupportedVersion": "1.6.4", "minimumNearingEndOfSupportVersion": "1.6.6", |
| "recommendedVersion": "1.9.0", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { "clientId": |
| "ODBC", "minimumSupportedVersion": "2.24.2", "minimumNearingEndOfSupportVersion": "2.24.5", |
| "recommendedVersion": "3.1.1", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { "clientId": |
| "PHP", "minimumSupportedVersion": "1.2.0", "minimumNearingEndOfSupportVersion": "1.2.0", |
| "recommendedVersion": "2.1.0", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { "clientId": |
| "PythonConnector", "minimumSupportedVersion": "2.7.0", "minimumNearingEndOfSupportVersion": "2.7.3", |
| "recommendedVersion": "3.4.1", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { "clientId": |
| "SnowSQL", "minimumSupportedVersion": "1.2.20", "minimumNearingEndOfSupportVersion": "1.2.21", |
| "recommendedVersion": "1.2.29", "deprecatedVersions": [], "_customSupportedVersions_": [] }, { |
| "clientId": "SQLAPI", "minimumSupportedVersion": "1.0.0", "minimumNearingEndOfSupportVersion": "", |
| "recommendedVersion": "", "deprecatedVersions": [], "_customSupportedVersions_": [] }] |
+----------------------------------------------------------------------------------------------------------------------+
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"
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
syntax for templates. However, Snowflake recommends using the &{ variable_name }
syntax.