使用 SnowSQL¶

本主题介绍如何使用 SnowSQL，包括启动/停止客户端、使用客户端内的命令和变量以及其他常规用法信息。

执行命令

In a Snowflake session, you can issue commands to take specific actions. All commands in SnowSQL start with an exclamation point (!), followed by the command name.

例如：

user#> !help

+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+
| Command    | Use                                       | Aliases     | Description                                                                                |
|------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------|
| !abort     | !abort <query id>                         |             | Abort a query                                                                              |
| !connect   | !connect <connection_name>                |             | Create a new connection                                                                    |
| !define    | !define <variable>=<value>                |             | Define a variable as the given value                                                       |
| !edit      | !edit <query>                             |             | Opens up a text editor. Useful for writing longer queries. Defaults to last query          |
| !exit      | !exit                                     | !disconnect | Drop the current connection                                                                |
| !help      | !help                                     | !helps, !h  | Show the client help.                                                                      |
| !options   | !options                                  | !opts       | Show all options and their values                                                          |
| !pause     | !pause                                    |             | Pauses running queries.                                                                    |
| !print     | !print <message>                          |             | Print given text                                                                           |
| !queries   | !queries help, <filter>=<value>, <filter> |             | Lists queries matching the specified filters. Write <!queries> help for a list of filters. |
| !quit      | !quit                                     | !q          | Drop all connections and quit SnowSQL                                                      |
| !rehash    | !rehash                                   |             | Refresh autocompletion                                                                     |
| !result    | !result <query id>                        |             | See the result of a query                                                                  |
| !set       | !set <option>=<value>                     |             | Set an option to the given value                                                           |
| !source    | !source <filename>, <url>                 | !load       | Execute given sql file                                                                     |
| !spool     | !spool <filename>, off                    |             | Turn on or off writing results to file                                                     |
| !system    | !system <system command>                  |             | Run a system command in the shell                                                          |
| !variables | !variables                                | !vars       | Show all variables and their values                                                        |
+------------+-------------------------------------------+-------------+--------------------------------------------------------------------------------------------+

For a detailed description of each command, see 命令参考 (in this topic).

使用变量

您可以使用变量在 Snowflake 会话中存储和重用值。变量使您能够在查询中使用用户定义值和数据库值。

接下来的部分将介绍如何定义和使用变量：

定义变量
启用变量替换
列出变量
使用内置变量

定义变量

您可以通过多种方式为 SnowSQL 定义变量：

在连接之前定义变量（配置文件）
Defining variables while connecting ( or command-line flag)
Defining variables within a session ( command)

在连接之前定义变量（配置文件）

To define variables before connecting to Snowflake, add the variables in the config configuration file:

Open the SnowSQL configuration file (named config) in a text editor. The default location of the file is:

Linux/macOS:
~/.snowsql/
Windows:
%USERPROFILE%\.snowsql\

Note
You can change the default location by specifying the --config path command-line flag when starting SnowSQL.

In the [variables] section, define any variables that you plan to use:
```
[variables]
<variable_name>=<variable_value>
```

其中：

variable_name is a string of alphanumeric characters (case-insensitive) representing the name of the variable.
variable_value is a string representing the value for the variable. If needed, the string can be enclosed by single or double quotes.

例如：

[variables]
tablename=CENUSTRACKONE

Defining variables while connecting (`-D` or `--variable` command-line flag)¶

To define variables while connecting to Snowflake, on the terminal command line, specify the -D or --variable command-line flag. For the argument to this flag, specify the variable name and value in the form of variable_name=variable_value.

例如：

Linux/macOS:

$ snowsql ... -D tablename=CENUSTRACKONE --variable db_key=$DB_KEY

Windows:

$ snowsql ... -D tablename=CENUSTRACKONE --variable db_key=%DB_KEY%

在上面的例子中：

-D sets a variable named tablename to CENUSTRACKONE.
--variable assigns a Snowflake variable named db_key to the DB_KEY environment variable.

Defining variables within a session (`!define` command)¶

To define a variable after connecting to Snowflake, execute the !define command in the session.

例如：

user#> !define tablename=CENUSTRACKONE

启用变量替换

To enable SnowSQL to substitute values for the variables, you must set the variable_substitution configuration option to true in one of the following ways:

To set this option before you start SnowSQL, open the SnowSQL configuration file in a text editor, and set this option in the [options] section:
```
[options]
variable_substitution = True
```
To set this option when you start SnowSQL, specify the -o command-line flag:
```
$ snowsql ... -o variable_substitution=true
```
To set this option when in a SnowSQL session, execute the !set command in the session:
```
user#> !set variable_substitution=true
```
Note
There is currently no option to unset an option value, such as the variable_substitution option. If you need to disable variable substitution, execute the command !set variable_substitution=false.

替换会话中的变量

After you enable variable substitution, you can use variables in SQL statements.

To use a variable in a statement, use the &variable_name syntax. Note that variable names are case-insensitive. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=true

user#> select '&snowshell';

+--------+
| 'BASH' |
%--------%
| bash   |
+--------+

If the variable_substitution option is not enabled, no variable substitution occurs. For example:

user#> !define snowshell=bash

user#> !set variable_substitution=false

user#> select '&snowshell';

+--------------+
| '&SNOWSHELL' |
%--------------%
| &snowshell   |
+--------------+

如果引用尚未定义的变量，SnowSQL 会显示错误。例如：

select '&z';

Variable z is not defined

要将变量与文本组合在一起，请将变量引用括在花括号中。例如：

user#> !define snowshell=bash

user#> !set variable_substitution=true

select '&{snowshell}_shell';

+--------------+
| 'BASH_SHELL' |
%--------------%
| bash_shell   |
+--------------+

要在不使用替换的情况下使用 & 号，请使用第二个 & 号将 & 号进行转义：

&&variable

例如：

user#> !set variable_substitution=true

user#> select '&notsubstitution';

Variable notsubstitution is not defined

user#> select '&&notsubstitution';

+--------------------+
| '&NOTSUBSTITUTION' |
%--------------------%
| &notsubstitution   |
+--------------------+

列出变量

To list variables, execute the !variables or !vars command in the session:

user#> !variables

+-----------+-------+
| Name      | Value |
|-----------+-------|
| snowshell | bash  |
+-----------+-------+

使用内置变量

SnowSQL 包含一组内置变量，这些变量返回有关在当前用户会话中执行的语句的元数据。

这些变量名称中的每一个都以两个下划线字符（“__”）开头。

__rowcount

返回受用户最近执行的 DML 语句影响的行数。

user#> insert into a values(1),(2),(3);

+-------------------------+
| number of rows inserted |
%-------------------------%
|                       3 |
+-------------------------+
3 Row(s) produced. Time Elapsed: 0.950s

user#> !set variable_substitution=true

user#> select &__rowcount;

+---+
| 3 |
%---%
| 3 |
+---+

__sfqid

返回用户最近执行的查询的查询 ID。

user#> !set variable_substitution=true

user#> select * from a;

user#> select '&__sfqid';

+----------------------------------------+
| 'A5F35B56-49A2-4437-BA0E-998496CE793E' |
%----------------------------------------%
| a5f35b56-49a2-4437-ba0e-998496ce793e   |
+----------------------------------------+

使用自动补全

Various SQL functions, table names, and variables are stored in SnowSQL and are auto-completed in interactive mode. To select an auto-complete suggestion, press the Tab key. To choose a different suggestion, use the ↑ and ↓ keys to highlight the desired option, and then press Tab.

To disable auto-complete interactively, set the auto_completion configuration option to False in the configuration file.

Viewing your command-line history¶

Your recent command-line history can be recalled by using the ↑ key. Press the key repeatedly to scroll through the buffer.

历史记录文件

The interactive command-line history file is named history and is located in ~/.snowsql/history.

运行批处理脚本

您可以通过两种方式运行批处理脚本：

使用连接参数（连接到 Snowflake 时）
执行命令（在 Snowflake 会话的命令行上）

Running while connecting (`-f` connection parameter)¶

To execute a SQL script while connecting to Snowflake, use the -f <input_filename> connection parameter.

An output file for the script can be specified using -o output_file=<output_filename>. In addition, you can use -o quiet=true to turn off the standard output and -o friendly=false to turn off the startup and exit messages.

例如：

snowsql -a myorganization-myaccount -u jsmith -f /tmp/input_script.sql -o output_file=/tmp/output.csv -o quiet=true -o friendly=false -o header=false -o output_format=csv

For more information about all connection parameters, see Connection parameters reference.

Running in a session (`!source` or `!load` command)¶

To run a SQL script after connecting to Snowflake, execute the !source (or !load) command in the session.

例如：

user#> !source example.sql

导出数据

Output query results to a file in a defined format using the following configuration options:

output_format=output_format
output_file=output_filename

若要从输出中移除初始文本、标题文本、计时和告别消息，也请设置以下选项：

friendly=false
header=false
timing=false

与所有配置选项一样，您可以使用以下任一方法设置这些选项：

在配置文件中（连接到 Snowflake 之前）。
Using the -o or --options connection parameter (while connecting to Snowflake).
Executing the !set command (on the command line in the Snowflake session).

Note that consecutive queries are appended to the output file. Alternatively, to redirect query output to a file and overwrite the file with each new statement, use the greater-than sign (>) in a script.

In the following example, SnowSQL connects to an account using a named connection and queries a table. The output is written to a CSV file named output_file.csv in the current local directory:

Linux/macOS:

snowsql -c my_example_connection -d sales_db -s public -q 'select * from mytable limit 10' -o output_format=csv -o header=false -o timing=false -o friendly=false  > output_file.csv

Windows:

snowsql -c my_example_connection -d sales_db -s public -q "select * from mytable limit 10" -o output_format=csv -o header=false -o timing=false -o friendly=false  > output_file.csv

更改 SnowSQL 提示格式¶

SnowSQL 提示会动态显示有关当前会话的上下文信息：

登录 Snowflake 时，提示将显示您的用户名，以及默认仓库、数据库和架构（如果已设置默认值）。
如果在会话中使用 USE 命令来设置或更改仓库、数据库或架构，则提示将会更改以反映上下文。

You can control the appearance and structure of the prompt using the prompt_format configuration option and a Pygments token in brackets for each object type, in the form of [token] (e.g. [user] or [warehouse]).

此令牌会影响之后的提示。可以更改每个令牌的顺序和颜色，以及令牌之间的分隔符。

与所有配置选项一样，您可以使用以下任一方法设置提示：

在配置文件中（连接到 Snowflake 之前）。
Using the -o or --options connection parameter (while connecting to Snowflake).
Executing the !set command (on the command line in the Snowflake session).

Note

如果使用连接参数或直接在命令行上更改提示，则更改仅适用于当前会话。若要在将来的会话中保留更改，请在配置文件中设置相应选项。

支持的令牌

SnowSQL 支持以下对象类型作为令牌：

user
account
role
database
schema
warehouse

默认提示

SnowSQL 的默认提示使用以下令牌和结构：

[user]#[warehouse]@[database].[schema]>

例如：

jdoe#DATALOAD@BOOKS_DB.PUBLIC>

提示示例

Continuing the example above, the following !set command executed in the command line adds the role token and changes the token order to user and role, database and schema, then warehouse. It also changes the delimiter for each token to a period (.) and sets the tokens to use different colors:

jdoe#DATALOAD@BOOKS_DB.PUBLIC> !set prompt_format=[#FF0000][user].[role].[#00FF00][database].[schema].[#0000FF][warehouse]>

此示例将导致会话中出现以下提示：

断开与 Snowflake 的连接并停止 SnowSQL¶

SnowSQL 为以下操作提供单独的命令：

离开单个连接（即会话）而不停止 SnowSQL。
退出 SnowSQL，这也会自动终止所有连接。

To exit a connection/session, use the !exit command (or its alias, !disconnect). You can then connect again using !connect <connection_name> if you can defined multiple connections in the SnowSQL config file. Note that, if you only have one connection open, the !exit command also quits/stops SnowSQL.

To exit all connections and then quit/stop SnowSQL, use the !quit command (or its alias, !q). You can also type CTRL + d on your keyboard.

离开代码

SnowSQL 退出/离开时会返回几种可能的离开代码：

0:: 一切正常。
1:: 客户端出了点问题。
2:: Something went wrong with the command-line arguments.
3:: SnowSQL 无法联系服务器。
4:: SnowSQL 无法与服务器正常通信。
5:: The exit_on_error configuration option was set and SnowSQL exited because of an error.

默认按键绑定

Tab: 接受当前的自动补全建议。
CTRL + d: 退出/停止 SnowSQL。

命令参考

`!abort`¶

Aborts a query (specified by query ID). The query ID can be obtained from the History page in the web interface.

例如：

user#> !abort 77589bd1-bcbf-4ec8-9ebc-6c949b00614d;

`!connect`¶

SnowSQL supports multiple sessions (i.e. connections) with !connect <connection_name>:

The connection parameters/options associated with connection_name are stored in the corresponding [connections.<connection_name>] section in the SnowSQL configuration file.
If a parameter/option is not specified in the [connections.<connection_name>] section, the unspecified parameter will default to the parameters under [connections].

连接时，连接将添加到连接堆栈中，而离开时将返回到以前的连接。退出将离开您的所有连接并退出，无论您有多少连接。

例如：

配置文件：

[connections.my_example_connection]
...

命令行：

user#> !connect my_example_connection

`!define`¶

使用以下格式将变量设置为指定值：

!define <variable_name>=<variable_value>

The name and value must be separated by a single = with no spaces. Valid characters that can be used in the variable are:

0-9a-zA-Z_

For more information on defining and using variables, see 使用变量.

`!edit`¶

Opens up the editor that was set using the editor connection parameter (if no editor was set, the default is vim). The command accepts a query as an argument. If no argument is passed, it opens up the last query that was run.

Note

您必须在离开编辑器之前或之时进行保存，否则不会保存在编辑器中输入/修改的任何文本。

`!exit` , `!disconnect`¶

如果当前连接是最后一个连接，则断开当前连接并且退出 SnowSQL。

`!help` , `!helps` , `!h`¶

显示 SnowSQL 命令的帮助。

`!options` , `!opts`¶

Returns a list of all the SnowSQL configuration options and their currently-set values. These options can be set using the !set command in the current SnowSQL session.

Note

These options can also be set in the configuration file for SnowSQL or as connector parameters in the command line when invoking SnowSQL.

`!pause`¶

暂停正在运行的查询。按回车键继续。

`!print`¶

将指定的文本打印到屏幕和当前假脱机到的任何文件。

例如：

user#> !print Include This Text

`!queries`¶

Lists all queries that match the specified filters. The default filters are session and amount=25, which return the 25 most recent queries in the current session.

例如：

返回此当前会话中最近运行的 25 个查询：
```
!queries session
```
返回账户中最近运行的 20 个查询：
```
!queries amount=20
```
返回账户中最近运行且耗时超过 200 毫秒的 20 个查询：
```
!queries amount=20 duration=200
```
返回指定仓库中最近运行的 25 个查询：
```
!queries warehouse=mywh
```

此命令为返回的每个查询 ID 创建一个变量。请注意，必须启用变量替换才能使用这些变量。例如：

user#> !queries session

+-----+--------------------------------------+----------+-----------+----------+
| VAR | QUERY ID                             | SQL TEXT | STATUS    | DURATION |
|-----+--------------------------------------+----------+-----------+----------|
| &0  | acbd6778-c68c-4e79-a977-510b2d8c08f1 | select 1 | SUCCEEDED |       19 |
+-----+--------------------------------------+----------+-----------+----------+

user#> !result &0

+---+
| 1 |
%---%
| 1 |
+---+

user#> !result acbd6778-c68c-4e79-a977-510b2d8c08f1

+---+
| 1 |
%---%
| 1 |
+---+

`!quit` , `!q` (also `CTRL` + `d`)¶

断开所有连接并离开 SnowSQL。

`!rehash`¶

重新同步自动补全令牌。

正常使用并不需要重新同步自动补全令牌。但是，在某些情况下（例如，如果在另一个会话中创建了新的用户定义函数），强制更新服务器端令牌可能很有用。

`!result`¶

Returns the result of a completed query (specified by query ID). Query IDs can be obtained from the History page in the web interface or using the !queries command.

如果查询仍在运行，此命令将等待查询完成。

例如：

user#> !result 77589bd1-bcbf-4ec8-9ebc-6c949b00614d;

`!set`¶

Sets the specified SnowSQL configuration option to a given value using the form <option>=<value>.

Note that there is no option currently to unset an option value. To change the value for an option, run the !set command again with the desired value.

例如：

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 1000              |
 ...
+-----------------------+-------------------+

user#> !set rowset_size=500

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 500               |
 ...
+-----------------------+-------------------+

user#> !set rowset_size=1000

user#> !options

+-----------------------+-------------------+
| Name                  | Value             |
|-----------------------+-------------------|
 ...
| rowset_size           | 1000              |
 ...
+-----------------------+-------------------+

Important

Spaces are not allowed between an option and its value. Some options support a defined set of values; SnowSQL returns an error if the provided value is unsupported. You cannot create new options.

For a list of all the configuration options you can set, use the !options command.

`!source` , `!load`¶

Executes SQL from a file. You can SQL from local files or a URL.

例如：

user#> !source example.sql

user#> !load /tmp/scripts/example.sql

user#> !load http://www.example.com/sql_text.sql

`!spool`¶

可以通过两种方式执行此命令：

启用假脱机，并将所有后续语句/查询的结果写入指定文件： !spool <file_name>
关闭结果假脱机（如果已启用）： !spool off

例如：

user#> select 1 num;

+-----+
| NUM |
%-----%
|   1 |
+-----+

user#> !spool /tmp/spool_example

user#> select 2 num;

+---+
| 2 |
%---%
| 2 |
+---+

user#> !spool off

user#> select 3 num;

+---+
| 3 |
%---%
| 3 |
+---+

user#> !exit

Goodbye!

$ cat /tmp/spool_example

+---+
| 2 |
%---%
| 2 |
+---+

You can change the output format by first running the !set output_format=<format> command. The option supports the following values:

expanded
fancy_grid
grid
html
latex
latex_booktabs
mediawiki
orgtbl
pipe
plain
psql
rst
simple
tsv

Recommended value: psql, fancy_grid, or grid

例如，要以 CSV 格式输出：

user#> !set output_format=csv

user#> !spool /tmp/spool_example

`!system`¶

执行 shell 命令。

!system <command>

The following example runs the ls command in the user’s home directory:

user#> !system ls ~

`!variables` , `!vars`¶

Lists all current variables. Returns each <variable_name>=<variable_value> pair currently defined.

变量在分配后无法删除，但可以通过指定不带值的变量名来移除变量值。例如：

user#> !set variable_substitution=true

user#> !define SnowAlpha=_ALPHA_

user#> !variables

+------------------+---------+
| Name             | Value   |
|------------------+---------|
| snowalpha        | _ALPHA_ |
+------------------+---------+

user#> !define SnowAlpha

user#> !variables

+------------------+-------+
| Name             | Value |
|------------------+-------|
| snowalpha        |       |
+------------------+-------+

user#> !define snowalpha=456

user#> select &snowalpha;

+-----+
| 456 |
%-----%
| 456 |
+-----+

For more information about setting variables, see 使用变量 (in this topic).

故障排除

Error Message: `Variable is not defined`¶

Cause:

If you see this error message when running commands in SnowSQL, the cause might be an ampersand (&) inside a CREATE FUNCTION command. (The ampersand is the SnowSQL variable substitution character.) For example, executing the following in SnowSQL causes this error:

create function mask_bits(...)
    ...
    as
    $$
    var masked = (x & y);
    ...
    $$;

The error occurs when the function is created, not when the function is called.

Solution:

If you do not intend to use variable substitution in SnowSQL, you can explicitly disable variable substitution by executing the following command:

!set variable_substitution=false;

For more information about variable substitution, see Using variables.

使用 SnowSQL¶

执行命令

使用变量

定义变量

在连接之前定义变量（配置文件）

Defining variables while connecting (-D or --variable command-line flag)¶

Defining variables within a session (!define command)¶

启用变量替换

替换会话中的变量

列出变量

使用内置变量

使用自动补全

Viewing your command-line history¶

历史记录文件

运行批处理脚本

Running while connecting (-f connection parameter)¶

Running in a session (!source or !load command)¶

导出数据

更改 SnowSQL 提示格式¶

支持的令牌

默认提示

提示示例

断开与 Snowflake 的连接并停止 SnowSQL¶

离开代码

默认按键绑定

命令参考

!abort¶

!connect¶

!define¶

!edit¶

!exit , !disconnect¶

!help , !helps , !h¶

!options , !opts¶

!pause¶

!print¶

!queries¶

!quit , !q (also CTRL + d)¶

!rehash¶

!result¶

!set¶

!source , !load¶

!spool¶

!system¶

!variables , !vars¶

故障排除

Error Message: Variable is not defined¶

Defining variables while connecting (`-D` or `--variable` command-line flag)¶

Defining variables within a session (`!define` command)¶

Running while connecting (`-f` connection parameter)¶

Running in a session (`!source` or `!load` command)¶

`!abort`¶

`!connect`¶

`!define`¶

`!edit`¶

`!exit` , `!disconnect`¶

`!help` , `!helps` , `!h`¶

`!options` , `!opts`¶

`!pause`¶

`!print`¶

`!queries`¶

`!quit` , `!q` (also `CTRL` + `d`)¶

`!rehash`¶

`!result`¶

`!set`¶

`!source` , `!load`¶

`!spool`¶

`!system`¶

`!variables` , `!vars`¶

Error Message: `Variable is not defined`¶