Introduction to Snowflake SQL REST API using Postman

Introduction

Snowflake provides multiple ways to manage data efficiently in its database. Snowflake SQL REST API is one such feature which allows users to interact with Snowflake through HTTP requests, making it easy to integrate with other systems.

Before we jump into understanding the capabilities of Snowflake SQL REST API and how to access it, let us quickly understand what an API is.

What is REST API?

API stands for Application Programming Interface, which is a software intermediary provided by an application to other application that allows two applications to talk to each other.

A real time example of APIs is the Weather apps on your mobile. These apps do not use their own weather forecasting system of their own. Instead, they provide weather information accessing the API of a third party weather provider. Apple, for instance, uses The Weather Channel’s API.

REST stands for REpresentational State Transfer which is an architectural style. REST defines a set of principles and standards using which APIs can be built and REST is the widely accepted architectural style of building APIs.

REST API request is generally made up of four parts – HTTP Method, Endpoint URL, Headers and Body

We will discuss more about the making a Snowflake SQL REST API request in the subsequent sections of the article.

Snowflake SQL REST API capabilities

The operations that can be performed using Snowflake SQL REST API are

• Submit SQL statements for execution.
• Check the status of the execution of a statement.
• Cancel the execution of a statement.
• Fetch query results concurrently.

This API can be used to execute standard queries and most DDL and DML statements.

Snowflake SQL REST API Endpoints

The Snowflake SQL REST API can be accessed using the following URL.

https://<account_identifier>.snowflakecomputing.com/api

The <account_identifier> can be obtained easily from your login URL.

The API consists of the /api/v2/statements/ resource and provides the following endpoints:

The following endpoint is used to submit a SQL statement for execution.

/api/v2/statements

The following endpoint is used to to check the status of the execution of a statement.

/api/v2/statements/<statementHandle>

The following endpoint is used to to cancel the execution of a statement.

/api/v2/statements/<statementHandle>/cancel

In the steps to come, we shall learn how to access all these endpoints using Postman.

Steps to access Snowflake SQL REST API using Postman

Postman is a powerful tool for testing APIs, and it allows us to easily make HTTP requests and view responses. You can either download and install desktop application of Postman or use its web version from any device by creating an account.

Following below steps to access Snowflake SQL REST API using Postman.

Authentication

Every API request we make must also include the authentication information. There are two options for providing authentication: OAuth and JWT key pair authentication. In this article we will use JWT key pair authentication for demonstration purpose.

Follow below steps to use JWT Key Pair Authentication.

1. Configure Key-Pair Authentication by performing below actions.

• Generate Public-Private Key pair using OpenSSL.
• Assign the generated public key to your Snowflake user.
• The generated private key should be stored in a file and available locally on machine where JWT is generated.

Refer our previous article for more details on configuring Key Pair authentication.

2. Once Key Pair Authentication for your Snowflake account is set, JWT token should be generated.

This JWT token is time limited token which has been signed with your key and Snowflake will know that you authorized this token to be used to authenticate as you for the SQL API.

Below is the command to generate JWT token using SnowSQL.

snowsql --generate-jwt -a <account_identifier> -u <username> --private-key-path <path>/rsa_key.pem

The below image shows generating JWT using SnowSQL command line tool.

3. The generated JWT token is used as part of Header in the API request you are making in the Postman.

Authorization: Bearer <jwt_token>

<jwt_token> is the token that you generated.

Submitting a Request to Execute a SQL Statement

To execute a SQL statement using Snowflake SQL REST API, we have to send a POST request to the /api/v2/statements/ endpoint.

The Snowflake SQL REST API request for executing a SQL statement 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 table",
"timeout": 60,
"database": "<your_database>",
"schema": "<your_schema>",
"warehouse": "<your_warehouse>",
"role": "<your_role>"
}

In the body part of the above request

• The statement field specifies the SQL statement to execute.
• The timeout field specifies that the server allows 60 seconds for the statement to be executed.
• The other fields are self-explanatory.

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.

Reading response of the API request.

If the submitted SQL statement through API request is successfully executed, Snowflake returns the HTTP response code 200 and returns the rows in a JSON array object.

Below is the response of the API request we submitted earlier.

{
    "resultSetMetaData": {
        "numRows": 3,
        "format": "jsonv2",
        "partitionInfo": [
            {
                "rowCount": 3,
                "uncompressedSize": 59
            }
        ],
        "rowType": [
            {
                "name": "EMPLOYEE_ID",
                "database": "DEMO_DB",
                "schema": "PUBLIC",
                "table": "EMPLOYEES",
                "nullable": true,
                "scale": 0,
                "collation": null,
                "byteLength": null,
                "precision": 38,
                "type": "fixed",
                "length": null
            },
            {
                "name": "EMPLOYEE_NAME",
                "database": "DEMO_DB",
                "schema": "PUBLIC",
                "table": "EMPLOYEES",
                "nullable": true,
                "scale": null,
                "collation": null,
                "byteLength": 200,
                "precision": null,
                "type": "text",
                "length": 50
            }
        ]
    },
    "data": [
        [
            "100",
            "Tony"
        ],
        [
            "101",
            "Steve"
        ],
        [
            "102",
            "Bruce"
        ]
    ],
    "code": "090001",
    "statementStatusUrl": "/api/v2/statements/01abf1da-3200-b819-0003-c9860001c04a?requestId=cf3656ec-15c7-42c2-9033-8405fb7e26bb",
    "requestId": "cf3656ec-15c7-42c2-9033-8405fb7e26bb",
    "sqlState": "00000",
    "statementHandle": "01abf1da-3200-b819-0003-c9860001c04a",
    "message": "Statement executed successfully.",
    "createdOn": 1682747204706
}

The response of the API request consists of

• Number of rows returned as output.
• The rowType array object which gives additional metadata information about the datatypes returned from the query.
• The actual row data information is available under the data array.
• A QueryStatus object which includes information about the status of the execution of the statement.

Checking the Status of Execution of the Statement

If the execution of the statement takes longer than 45 seconds or if you submitted an asynchronous query, Snowflake returns a 202 response code. In such cases you must send a request to check the execution status of the statement.

To get the execution status of the statement, you can send GET request to /api/v2/statements/ endpoint and append the statementHandle to the end of the URL path as a path parameter.

The statementHandle is a unique identifier of a statement submitted for execution. The statementHandle information is present in the output response of the request in the QueryStatus object( highlighted in the response above ).

The Snowflake SQL REST API request for checking the execution status of  a 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

If the statement has finished executing successfully, Snowflake returns the HTTP response code 200 and the results in a ResultSet object. However, if an error occurred when executing the statement, Snowflake returns the HTTP response code 422 with a QueryFailureStatus object.

Cancelling the Execution of a SQL statement

To cancel the execution of a statement, send a POST request to the /api/v2/statements/ endpoint and append the statementHandle to the end of the URL path followed by cancel as a path parameter.

The Snowflake SQL REST API request to cancel the execution of a SQL statement is as follows.

HTTP Method: POST

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

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: