Execute multiple SQL statements in a single Snowflake API request

Introduction

In our previous article we have discussed on overview of Snowflake SQL REST API and how to submit a SQL API request to execute a SQL statement. This method allows submitting only one SQL statement for execution.

In this article let us understand how to submit a request containing multiple statements to the Snowflake SQL API.

Submitting multiple statements in a single Snowflake SQL REST API request

The process to submit multiple statements in a single request is similar to submitting a single statement in a request except that in the body part of the request

• In the statement field, enter multiple statements separated using semicolon (;)
• In the parameters field, set the MULTI_STATEMENT_COUNT field to the number of SQL statements in the request.

The Snowflake SQL REST API request for executing multiple SQL statements in a single request is as follows.

HTTP Method: POST

EndPoint URL: 
https://<account_identifier>.snowflakecomputing.com/api/v2/statements

Headers:
Authorization: Bearer <jwt_token>
Content-Type: application/json
Accept: application/json
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

Body:
{
  "statement": "select * from table1;selct * from table2;",
  "timeout": 60,
  "database": "<your_database>",
  "schema": "<your_schema>", 	
  "warehouse": "<your_warehouse>",
  "role": "<your_role>"
  "parameters": {
      "MULTI_STATEMENT_COUNT": "<statements_count>"
  }
}

To learn more about how to generate a JWT token, refer our previous article.

For example, below is the body part of the request submitting two SQL statements for execution.

{
  "statement": "select * from employees where employee_id=101;select * from employees where employee_id=102;",
  "timeout": 60,
  "database": "DEMO_DB",
  "schema": "PUBLIC",
  "warehouse": "COMPUTE_WH",
  "role": "ACCOUNTADMIN",
  "parameters": {
      "MULTI_STATEMENT_COUNT": "2"
  }
}

In the above 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 error.

The below image shows the HTTP Method, EndPoint URL, Headers configured in the API request to execute a SQL statement in Postman.

The below image shows the Body part of API request configured to execute a SQL statement in Postman.

Extracting Results of each SQL Statement in the API request

The response of the request submitting multiple SQL statements for execution do not include the output of the individual statements. Instead the response contains a statementHandles field which holds the list of statement handles of each statement.

The statementHandle can be used in a GET request to /api/v2/statements/ endpoint to get the execution status and output of individual statements.

The response of the above the discussed example is as follows which contains statementHandle information of individual statements in a statementHandles field.

{
    "resultSetMetaData": {
        "numRows": 1,
        "format": "jsonv2",
        "partitionInfo": [
            {
                "rowCount": 1,
                "uncompressedSize": 57
            }
        ],
        "rowType": [
            {
                "name": "multiple statement execution",
                "database": "",
                "schema": "",
                "table": "",
                "nullable": false,
                "scale": null,
                "collation": null,
                "byteLength": 16777216,
                "precision": null,
                "type": "text",
                "length": 16777216
            }
        ]
    },
    "data": [
        [
            "Multiple statements executed successfully."
        ]
    ],
    "code": "090001",
    "statementHandles": [
        "01abf778-3200-b817-0003-c9860001d03e",
        "01abf778-3200-b817-0003-c9860001d042"
    ],
    "statementStatusUrl": "/api/v2/statements/01abf778-3200-b817-0003-c9860001d03a?requestId=7e5c7836-a8bd-4765-b9f9-cbaf1b96a2e5",
    "requestId": "7e5c7836-a8bd-4765-b9f9-cbaf1b96a2e5",
    "sqlState": "00000",
    "statementHandle": "01abf778-3200-b817-0003-c9860001d03a",
    "message": "Statement executed successfully.",
    "createdOn": 1682833476276
}

The Snowflake SQL REST API request for checking the execution status of an individual SQL statement is as follows.

HTTP Method: GET

EndPoint URL: https://<account_identifier>.snowflakecomputing.com/api/v2/statements/<statementHandle>

Headers:
Authorization: Bearer <jwt_token>
Content-Type: application/json
Accept: application/json
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

Subscribe to our Newsletter !!

Related Articles: