snow sql

Executes Snowflake query. Use either query, filename or input option. 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.

语法

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
  --decimal-precision <decimal_precision>

实参

无

选项

--query, -q TEXT

要执行的查询。

--filename, -f FILE

要执行的文件。默认值：[]。

--stdin, -i

从标准输入读取查询。在管道输入此命令时使用它。默认值：False。

--variable, -D TEXT

key=value 格式的字符串。如果提供，SQL 内容将被视为模板并使用所提供的数据渲染。

--retain-comments

保留传递给 Snowflake 的查询中的注释。默认值：False。

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

连接时禁用自动提交。将语句放入 BEGIN/COMMIT 内可将语句作为单个事务执行，并确保所有命令成功完成或不应用更改。默认值：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

Snowflake 项目的存储路径。默认为当前工作目录。

--env TEXT

格式为 key=value 的字符串。替换用于模板的 env 部分的变量。默认值：[]。

--connection, -c, --environment TEXT

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

--host TEXT

连接的主机地址。替换为连接指定的值。

--port INTEGER

连接的端口。替换为连接指定的值。

--account, --accountname TEXT

分配给 Snowflake 账户的名称。替换为连接指定的值。

--user, --username TEXT

连接到 Snowflake 的用户名。替换为连接指定的值。

--password TEXT

Snowflake 密码。替换为连接指定的值。

--authenticator TEXT

Snowflake 身份验证器。替换为连接指定的值。

--workload-identity-provider TEXT

工作负载身份提供商（AWS、AZURE、GCP、OIDC）。替换为连接指定的值。

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

Snowflake 私钥文件路径。替换为连接指定的值。

--token TEXT

连接到 Snowflake 时使用的 OAuth 令牌。

--token-file-path TEXT

连接到 Snowflake 时使用的带有 OAuth 令牌的文件路径。

--database, --dbname TEXT

要使用的数据库。替换为连接指定的值。

--schema, --schemaname TEXT

要使用的数据库架构。替换为连接指定的值。

--role, --rolename TEXT

要使用的角色。替换为连接指定的值。

--warehouse TEXT

要使用的仓库。替换为连接指定的值。

--temporary-connection, -x

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

--mfa-passcode TEXT

用于多重身份验证的令牌 (MFA)。

--enable-diag

是否生成连接诊断报告。默认值：False。

--diag-log-path TEXT

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

--diag-allowlist-path TEXT

包含允许列表参数的 JSON 文件的路径。

--oauth-client-id TEXT

身份提供商为 Snowflake 集成提供的客户端 ID 的值。

--oauth-client-secret TEXT

身份提供商为 Snowflake 集成提供的客户端密钥的值。

--oauth-authorization-url TEXT

向驱动程序提供授权码的身份提供商端点。

--oauth-token-request-url TEXT

向驱动程序提供访问令牌的身份提供商端点。

--oauth-redirect-uri TEXT

URI 用于授权码重定向。

--oauth-scope TEXT

身份提供商授权请求中请求的范围。

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

存储临时凭据。

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

指定输出格式。默认：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

关闭到控制台的中间输出。默认值：False。

--enhanced-exit-codes

根据错误类型区分退出错误代码。默认值：False。

--decimal-precision INTEGER

Number of decimal places to display for decimal values. Uses Python’s default precision if not specified. [env var: SNOWFLAKE_DECIMAL_PRECISION].

--help

Displays the help text for this command.

使用说明

您可以使用以下选项之一指定要执行的 SQL 查询：

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

  • Use the --filename option to execute one or more files containing a SQL query or queries. When you specify multiple files, all files are executed sequentially on a single connection. For example:

    • snow sql -f myfile.sql
    • snow sql -f file1.sql -f file2.sql -f file3.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:

    • 将查询放到单引号而不是双引号里，如：

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

    • 转义特殊字符，如：

      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"

    Note

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

  • 在查询中指定脚本块。例如：

    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;
$$
;

    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.

设置 JSON 输出格式

The --format option provides two ways to display JSON:

  • JSON: Returns JSON as quoted strings, similar to the following:

    snow sql --format json -q "SELECT PARSE_JSON('{"name": "Alice", "age": 30}') as json_col"
    [
  {
   "JSON_COL": "{\"name\": \"Alice\", \"age\": 30}"
  }
]

  • JSON_EXT: Returns JSON as JSON objects, similar to the following:

    snow sql --format JSON_EXT -q "SELECT PARSE_JSON('{"name": "Alice", "age": 30}') as json_col"
    [
  {
 "JSON_COL": {
 "name": "Alice",
 "age": 30
  }
]

增强的错误代码

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

交互模式

The snow sql command supports an interactive mode that lets you enter SQL commands one at a time. Interactive mode provides the following features:

  • 语法突出显示

    Interactive mode syntax highlighting

  • 输入时代码补全

    Interactive mode code completion

  • 可搜索的历史记录

    To search your command history, press CTRL-R:

    Interactive mode searchable history

  • 多行输入

    Pressing ENTER on a line that does not end with a semicolon (;) moves the cursor to the next line for more commands until a statement ends with a semi-colon.

    Interactive mode multi-line input

To use interactive mode, enter the snow sql command followed by ENTER, as shown:

snow sql

The command opens a sub-shell with a > prompt where you can enter SQL commands interactively:

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

然后，您可以输入 SQL 命令，如下所示：

> create table my_table (c1 int);
+-------------------------------------+
| status                              |
%-------------------------------------%
| Table MY_TABLE successfully created.|
+-------------------------------------+

Note

You must end each SQL statement with a semicolon (;).

To exit interactive mode, enter exit, quit, or CTRL-D.

在单个事务中使用多个命令

The --single-transaction option lets you enter multiple SQL commands to execute as an all-or-nothing set of commands. By executing commands in a single transaction, you can ensure that all of the commands complete successfully before committing any of the changes. If any of the commands fail, none of the changes from the successful commands persist.

以下示例展示了事务执行成功和失败的情况：

  • 命令执行成功

    snow sql -q "insert into my_tbl values (123); insert into my_tbl values (124);" --single-transaction
    BEGIN;
+----------------------------------+
| status                           |
%----------------------------------%
| Statement executed successfully. |
+----------------------------------+

insert into my_tbl values (123);
+-------------------------+
| number of rows inserted |
%-------------------------%
| 1                       |
+-------------------------+

insert into my_tbl values (124);
+-------------------------+
| number of rows inserted |
%-------------------------%
| 1                       |
+-------------------------+

COMMIT
+----------------------------------+
| status                           |
%----------------------------------%
| Statement executed successfully. |
+----------------------------------+

然后，您可以验证命令是否已提交到数据库中：

snow sql -q "select count(*) from my_tbl"
select count(*) from my_tbl
+----------+
| COUNT(*) |
%----------%
| 2        |
+----------+

  • 单个事务失败

    snow sql -q "insert into my_tbl values (123); insert into my_tbl values (124); select BAD;" --single-transaction
    BEGIN;
+----------------------------------+
| status                           |
%----------------------------------%
| Statement executed successfully. |
+----------------------------------+

insert into my_tbl values (123);
+-------------------------+
| number of rows inserted |
%-------------------------%
| 1                       |
+-------------------------+

insert into my_tbl values (124);
+-------------------------+
| number of rows inserted |
%-------------------------%
| 1                       |
+-------------------------+

select BAD;
╭─ Error ───────────────────────────────────────────────────────────────────────────────╮
│ 000904 (42000): 01bc3b84-0810-0247-0001-c1be14ee11ce: SQL compilation error: error    │
│ line 1 at position 7                                                                  │
│ invalid identifier 'BAD'                                                              │
╰───────────────────────────────────────────────────────────────────────────────────────╯

然后，您可以验证命令是否未提交到数据库中：

snow sql -q "select count(*) from my_tbl"
select count(*) from my_tbl
+----------+
| COUNT(*) |
%----------%
| 0        |
+----------+

示例

  • 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 current_version();
+-------------------+
| CURRENT_VERSION() |
%-------------------%
| 8.25.1            |
+-------------------+

  • 下面的示例展示了如何使用客户端变量指定数据库：

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

  • 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';

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

    snow sql -f example.sql --retain-comments
    select 'column1';
+-----------+
| 'COLUMN1' |
%-----------%
| ABC       |
+-----------+

-- My comment
select 'bar';
+-----------+
| 'COLUMN2' |
%-----------%
| 123       |
+-----------+