在单个请求中提交多个 SQL 语句

本主题说明如何向 Snowflake SQL API 提交包含多个语句的请求。

Note

Executing multiple statements in a single query requires that a valid warehouse is available in a session.

简介

在某些情况下，可能需要在请求中指定多个 SQL 语句。例如，您可能需要执行以下操作：

  • 定义显式事务
  • 在请求中的语句中设置和使用会话变量
  • 在请求中的语句中创建和使用临时表
  • 更改请求中语句的数据库、架构、仓库或角色

以下各部分说明如何提交包含多个 SQL 语句的请求。

在请求中指定多个 SQL 语句

要在单个请求中提交多个 SQL 语句，请执行以下操作：

  • In the statement field, use a semicolon (;) between each statement.
  • In the parameters field, set the MULTI_STATEMENT_COUNT field to the number of SQL statements in the request.

例如：

POST /api/v2/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

{
  "statement": "alter session set QUERY_TAG='mytesttag'; select count(*) from mytable",
  ...
  "parameters": {
      "MULTI_STATEMENT_COUNT": "2"
  }
}

In this example MULTI_STATEMENT_COUNT is set to 2 which corresponds to the number of SQL statements being submitted.

To submit a variable number of SQL statements in the statement field, set MULTI_STATEMENT_COUNT to 0. This is useful in an application where the number of SQL statements submitted is not known at runtime.

If the value of MULTI_STATEMENT_COUNT does not match the number of SQL statements specified in the statement field, the SQL API returns the following error:

Actual statement count <actual_count> did not match the desired statement count <desired_count>.

其中

  • actual_count is the number of statements specified in the statement field.
  • desired_count is the value of MULTI_STATEMENT_COUNT.

If you specify multiple SQL statements in the statement field, but do not specify the MULTI_STATEMENT_COUNT field, the SQL API returns the following error:

Actual statement count 3 did not match the desired statement count 1.

Note

Snowflake 目前不支持多语句 SQL 请求中的变量绑定。

获取请求中每个 SQL 语句的结果

If a request that contains multiple SQL statements is processed successfully, the response does not include the data returned from executing the individual statements. Instead, the response contains a statementHandles field that contains an array of the handles for the individual statements.

Note

The statementHandles field is different from the statementHandle field:

  • The statementHandle field specifies the handle for the set of SQL statements in the request.
  • The statementHandles field is an array of the handles of the individual SQL statements in the request.

例如，假设发送了一个指定了两个要执行的 SQL 语句的请求：

POST /api/v2/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

{
  "statement": "select * from A; select * from B",
  ...
}

The response contains a statementHandles field that contains an array of the handles for the individual statements.

HTTP/1.1 200 OK
...
{
  ...
  "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ],
  ...
}

To check the status and retrieve the data for the individual statements, send a GET request to the /api/v2/statements/ endpoint and append the handle for each statement to the URL path. See Checking the status of the statement execution and retrieving the data for details.

GET /api/v2/statements/019c9fce-0502-f1fc-0000-438300e02412
...
GET /api/v2/statements/019c9fce-0502-f1fc-0000-438300e02416
...

处理在请求中指定多个语句时的错误

If you specified multiple SQL statements in the request and an error occurred when executing any of the statements, Snowflake returns the HTTP response code 422 with a QueryFailureStatus object.

You can get details about the error from this object.

例如，假设请求指定了以下语句，其中第二个 INSERT 语句包含错误：

{
  "statement": "create or replace table table1 (i int); insert into table1 (i) values (1); insert into table1 (i) values ('This is not a valid integer.'); insert into table1 (i) values (2); select i from table1 order by i",
  ...
}

Snowflake returns a response with the HTTP response code 422 and with a QueryFailureStatus object that contains the details about the error:

HTTP/1.1 422 Unprocessable Entity
Content-Type: application/json
...
{
  "code" : "100132",
  "message" : "JavaScript execution error: Uncaught Execution of multiple statements failed on statement \"insert into table1 (i) values ...\" (at line 1, position 75).\nDML operation to table TABLE1 failed on column I with error: Numeric value 'This is not a valid integer.' is not recognized in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4\nstackstrace: \nSYSTEM$MULTISTMT line: 10",
  "sqlState" : "P0000",
  "statementHandle" : "019d6e97-0502-317e-0000-096d0041f036"
}

In the example above, the INSERT statement with the error starts at the character position 75 in the value of the statement field.

出错语句之前的语句执行成功（在本例中为 CREATE TABLE 和第一个 INSERT 语句）。不执行出错语句之后的语句。