执行 SQL 语句¶

The snow sql command lets you execute ad-hoc SQL queries or files containing SQL queries using the following options:

To execute an ad-hoc query, use the -q command-line option. For example, to execute a simple SQL SELECT query, as shown in the following example:
```
snow sql -q "SELECT * FROM FOO;"
```
To execute a file containing a SQL query, use the -f command-line option to specify the path to the file. For example, to execute a file containing a SQL query, as shown in the following example:
```
snow sql -f my_query.sql
```

The snow sql command also can execute multiple statements; in that case, multiple result sets are returned. For example running:

snow sql  -q "select 'a', 'b'; select 'c', 'd';"

结果为以下输出：

select 'a', 'b';
+-----------+
| 'A' | 'B' |
|-----+-----|
| a   | b   |
+-----------+

select 'c', 'd';
+-----------+
| 'C' | 'D' |
|-----+-----|
| c   | d   |
+-----------+

You can also execute scripting blocks in Snowflake CLI with a caveat relating to the $$ delimiter.

例如：

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

Some operating systems interpret $$, such as a process ID (PID), instead of recognizing it as a scripting block delimiter. 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.

For more information, see the snow sql command.

为 SQL 模板使用变量¶

In certain situations, you might want to change your SQL queries based on the context. The snow sql command supports client-side variable substitution that lets you use variables in the command that are resolved locally before submitting the query. Variables in the SQL string take the form <% variable_name %>, and the -D (or --variable) option specifies the value of the variable.

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

例如，要使用客户端变量指定数据库，可以输入类似下面的命令：

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 filename and then sends the select * from dev.logs SQL query to Snowflake for processing.

您还可以指定多个变量输入，如下所示：

snow sql \
-q "grant usage on database <% database %> to <% role %>" \
-D "database=dev" \
-D "role=eng_rl"

此示例生成以下 SQL 查询：

grant usage on database dev to eng_rl

The --enable-templating option lets you specify which templating syntaxes are resolved in a SQL query. Snowflake CLI supports the following syntaxes:

STANDARD: Support the standard Snowflake CLI variable syntax (<% variable_name %>). Enabled by default.
LEGACY: Support the SnowSQL variable syntax (&{ variable_name } or &variable_name). Enabled by default.
JINJA: Support the jinja variable syntax ({{ variable_name }}). Disabled by default.
ALL: Allow all supported syntaxes. Disabled by default.
NONE: Do not support templating. Disabled by default.

以下示例说明了支持模板的不同方法：

禁用模板，这样两个查询变量都无法解析：
```
snow sql --enable-templating NONE -q "select '<% not_resolved %> &not_resolved'"
```

允许 JINJA 和 STANDARD 模板，同时禁用 LEGACY 模板：

snow sql --enable-templating JINJA --enable-templating STANDARD -q "select '<% resolved %> {{ resolved }} &not_resolved'"

启用所有语法，因此 SQL 查询可以解析所有三种语法：

snow sql --enable-templating ALL -q "select '<% resolved %> {{ resolved }}'"
snow sql --enable-templating ALL -q "select '&resolved {{ resolved }}'"

Note

JINJA 变量（如果已启用）将在 STANDARD 和 LEGACY 变量之后解析。

Storing variables in the `snowflake.yml` project definition file¶

Specifying variables as snow sql command-line options might not always be practical, or perhaps you might not want to specify sensitive values on the command line. In such cases, you can define variables and values in the snowflake.yml project definition file. Then you can just specify the variable names in the form <% ctx.env.<variable_name> %> instead of using the -D "<variable> = <value>" option.

Using the example from the previous section, you could store the database and role variables in snowflake.yml file and change the query to:

snow sql -q "grant usage on database <% ctx.env.database %> to <% ctx.env.role %>"

In this example, the snow sql command looks for the variable definitions in the project definition file and extracts the values without making them visible on the command line. The snowflake.yml file should be located either in the current working directory or in the location specified with the -p option.

For more information about storing these values in the project definition file, see Use variables in SQL.

Executing SQL queries asynchronously¶

Snowflake CLI lets you execute one or more SQL queries asynchronously. Instead of waiting for a result, the snow sql command schedules the queries at Snowflake and returns a query ID. After a query finishes, you can get the result using the �!result query command or the SQL RESULT_SCAN command.

To execute a SQL query asynchronously, end the query with ;> instead of ;, as shown:

snow sql -q 'select "My async query" ;>'

The following example executes a single query asynchronously:

snow sql -q "select 'This is async query';>"

select 'This is async query'
+--------------------------------------+
| scheduled query ID                   |
%--------------------------------------%
| 01bc3011-080f-f2d7-0001-c1be14bae7c2 |
+--------------------------------------+

You can then use the returned query ID in the �!result query command to display the query result:

snow sql -q '!result 01bc3011-080f-f2d7-0001-c1be14bae7c2'

path-to-private-key-file
+-----------------------+
| 'THIS IS ASYNC QUERY' |
%-----------------------%
| This is async query   |
+-----------------------+

您还可以在查询字符串中异步和同步执行多项查询，如下所示：

snow sql -q "select 'This is async query';> select 'Not an async query'; select 'Another async query';>"

select 'This is async query'
+--------------------------------------+
| scheduled query ID                   |
%--------------------------------------%
| 01bc3b8c-0109-2e81-0000-0f2d0e5a4a32 |
+--------------------------------------+

select 'Not an async query';
+----------------------+
| 'NOT AN ASYNC QUERY' |
%----------------------%
| Not an async query   |
+----------------------+

select 'Another async query'
+--------------------------------------+
| scheduled query ID                   |
%--------------------------------------%
| 01bc3b8c-0109-2e81-0000-0f2d0e5a4a36 |
+--------------------------------------+

使用 SQL 查询命令¶

Snowflake CLI provides the following commands that you can use inside your SQL queries:

�!source, which executes SQL in local files or URLs.
�!queries, which lists all SQL queries.
�!result, which displays the result of a SQL query.
�!abort, which aborts an active SQL query.
�!edit, which opens an external editor to modify and execute SQL commands.

Tip

If you enclose your SQL query in double quotes ("") instead of single quotes (''), you might need to escape the exclamation point (!) based on which shell you use.

在本地文件或 URLs 中执行 SQL¶

You can use the !source query command in your SQL query to execute SQL in local files or a URL-based file. For example, the following command executes all SQL commands in a local file named my_sql_code.sql:

snow sql -q '!source my_sql_code.sql'

You can also nest !source commands in the SQL files, such as:

select emp_id FROM employees;
!source code_file_2.sql

In this example, the command executes the SELECT query and then executes the SQL commands in the code_file_2.sql file.

To execute multiple SQL files using !source, place each directive on a separate line in a wrapper file. For example, create a file named run_all.sql with the following contents:

!source script1.sql
!source script2.sql
!source script3.sql

Then execute the wrapper file:

snow sql -f run_all.sql

All three files are executed sequentially on a single connection. Alternatively, you can use multiple -f options to achieve the same result without a wrapper file, such as snow sql -f script1.sql -f script2.sql -f script3.sql.

Before executing !source queries, Snowflake CLI does the following:

评估变量替换和模板。
读取所有嵌套文件的内容，确保不发生递归。

当变量和模板解析完毕且未检测到递归时，命令会将代码发送到 Snowflake 以执行。

Note

If you use double quotes ("") instead of single quotes ('') around a !source query, you might need to escape the ! (\!) depending on which shell you use.

以下示例说明了执行源文件的不同方法。

执行本地文件中的代码

本示例假定您在本地 SQL 文件中有一个简单的查询。

cat code_to_execute.sql

select 73;

要执行文件中的代码，请输入以下命令：

snow sql -q '!source code_to_execute.sql'

select 73;
+----+
| 73 |
%----%
| 73 |
+----+

执行基于 URL 的文件中的代码。

本示例假定您在 URL 的 SQL 文件中拥有相同的简单查询。

要执行文件中的代码，请输入以下命令：

snow sql -q '!source https://trusted-host/trusted-content.sql'

select 73;
+----+
| 73 |
%----%
| 73 |
+----+

执行使用变量替换和模板的代码。

本示例假定您在本地 SQL 文件中有一个使用模板变量的查询。

cat code_with_variable.sql

select '<% ctx.env.Message %>';

要执行文件中的代码，请输入定义变量值的以下命令：

snow sql -q '!source code_&value.sql;' -D value=with_variable --env Message='Welcome !'

select 'Welcome !';
+-------------+
| 'WELCOME !' |
%-------------%
| Welcome !   |
+-------------+

Note

The !source command supports the legacy !load alias.

列出所有 SQL 查询¶

The !queries query command lists all queries for an account. By default, the command lists the 25 most recent queries executed in the current session.

For example, the following !queries query command returns the three most recent queries for a specific user:

snow sql -q '!queries user=user1 amount=3'

+-------------------------------------------------------------------------------------------------------------------------------------+
| QUERY ID                             | SQL TEXT                                                           | STATUS    | DURATION_MS |
|--------------------------------------+--------------------------------------------------------------------+-----------+-------------|
| 01bc3040-080f-f4f9-0001-c1be14bb603a | select current_version();                                          | SUCCEEDED | 3858        |
| 01bc303d-080f-f4e9-0001-c1be14bb1812 | SELECT SYSTEM$CANCEL_QUERY('01bc3011-080f-f2d7-0001-c1be14bae7c2') | SUCCEEDED | 564         |
| 01bc3011-080f-f2d7-0001-c1be14bae7c2 | select 'This is async query'                                       | SUCCEEDED | 931         |
+-------------------------------------------------------------------------------------------------------------------------------------+

您可以使用以下筛选器来缩小返回的查询列表：

Filter	Default	Description
amount (integer)	25	Number of recent queries to return (default: 25).
session (boolean)	N/A	If provided, return only queries executed in the current session.
warehouse (string)	None	Return queries executed only on the specified warehouse.
user (string)	None	Return queries executed only by the specified user.
duration (milliseconds)	0	Return only queries that took at least the specified number of milliseconds.
start_date (string)	None	Return only queries executed after the specified date. Date is expected to be provided in ISO format (for example `2025-01-01T09:00:00`)
end_date (string)	None	Return only queries executed before the specified date. Date is expected to be provided in ISO format (for example `2025-01-01T09:00:00`)
start (integer)	None	Return only queries executed after the specified Unix timestamp (in milliseconds).
end (integer)	None	Return only queries executed before the specified Unix timestamp (in milliseconds).
status (enum)	None	仅返回处于以下状态之一的查询： RUNNING SUCCEEDED FAILED BLOCKED QUEUED ABORTED
type	None	仅返回以下类型的查询： SELECT INSERT UPDATE DELETE MERGE MULTI_TABLE_INSERT COPY COMMIT ROLLBACK BEGIN_TRANSACTION SHOW GRANT CREATE ALTER

以下示例使用不同的筛选器返回查询：

返回在当前会话中最近执行的 25 项查询：
```
snow sql -q 'select 42; select 15; !queries session'
```
返回账户中最近执行的 20 项查询：
```
snow sql -q '!queries amount=20'
```
返回账户中最近执行且耗时超过 200 毫秒的 20 项查询：
```
snow sql -q '!queries amount=20 duration=200'
```
返回指定仓库中最近执行的 25 项查询：
```
snow sql -q '!queries warehouse=mywh'
```

返回已完成的 SQL 查询结果¶

The !result query command returns the result of a completed query, given its query ID. You can obtain the query ID in the following ways:

Check the Query History page in Snowsight.
Run the !queries SQL query command.
Use the ID returned by an asynchronous query.

snow sql -q '!result 01bc3011-080f-f2d7-0001-c1be14bae7c2'

+-----------------------+
| 'THIS IS ASYNC QUERY' |
%-----------------------%
| This is async query   |
+-----------------------+

中止活动 SQL 查询¶

The !abort query command aborts an active query, given its query ID. You can obtain the query ID in the following ways:

Check the Query History page in Snowsight.
Run the !queries SQL query command.
Use the ID returned by an asynchronous query.

snow sql -q '!abort 01bc3011-080f-f2d7-0001-c1be14bae7c2'

+-------------------------------------------------------------+
| SYSTEM$CANCEL_QUERY('01BC3011-080F-F2D7-0001-C1BE14BAE7C2') |
%-------------------------------------------------------------%
| Identified SQL statement is not currently executing.        |
+-------------------------------------------------------------+

Open an external editor to modify and execute SQL commands¶

The !edit query command opens an external editor where you can modify SQL commands to execute when you exit the editor. The editor is specified in the EDITOR environment variable or, if the environment variable is not set, the default system editor is used.

要在外部编辑器中输入命令，请按照以下步骤操作：

If not already defined in your shell, set the EDITOR environment variable to your preferred text editor.
Enter the snow sql command:
```
snow sql
```
At the > prompt, enter the !edit command:
```
> !edit
```

该命令打开指定的文本编辑器。

在编辑器中输入您的 SQL 命令，如下所示：
```
SELECT current_user() ;
```
保存文件并退出编辑器。

将显示您输入的命令，如下所示：

✓ Edited SQL loaded into prompt. Modify as needed or press Enter to execute.
> select current_user();

To execute the commands, select ENTER.

将显示命令输出，如下所示：

+----------------+
| CURRENT_USER() |
%----------------%
| USER1          |
+----------------+

在单个事务中输入多个命令

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 are completed 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 -c patcli -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        |
+----------+

在交互模式下输入 SQL 命令¶

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

语法突出显示
输入时代码补全
可搜索的历史记录

Pressing CTRL-R: lets you search your command 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 semicolon.

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.

执行 SQL 语句¶

为 SQL 模板使用变量¶

Storing variables in the snowflake.yml project definition file¶

Executing SQL queries asynchronously¶

使用 SQL 查询命令¶

在本地文件或 URLs 中执行 SQL¶

列出所有 SQL 查询¶

返回已完成的 SQL 查询结果¶

中止活动 SQL 查询¶

Open an external editor to modify and execute SQL commands¶

在单个事务中输入多个命令

在交互模式下输入 SQL 命令¶

Storing variables in the `snowflake.yml` project definition file¶