The Snowflake SQL API is a REST API that you can use to access and update data in a Snowflake database. You can use this API to execute standard queries and statements.

This getting started guide will walk through the basics of executing a SQL statement with the Snowflake SQL API and constructing an API workflow in Postman.

logos

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Automate an API workflow

Fork the collection

In Postman, fork the following collection to your own workspace.

Postman creates your fork in the selected workspace. You can now update this version of the collection as you wish. If you optionally chose to watch the collection, you can pull changes to your own collection when the main collection is updated.

For each folder and request, review the companion documentation in the context bar to the right.

collection documentation

Select the sample collection in the sidebar, and update the baseUrl collection variable under the Variables tab with your version of the following URL, replacing *account_locator* with the account locator for your own Snowflake account:

https://*account_locator*.snowflakecomputing.com/api/v2

In Postman, your baseUrl should look like this.

baseUrl collection variable

Now let's break down the parts of the API before we begin using it. You can follow along more closely by inspecting each request within the Quickstart folder.

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

In the steps to come, you shall use all these endpoints to familiarize yourself with the API.

When you send a request, the request must include authorization information. There are two options for providing authorization: OAuth and JWT key pair authorization. You can use whichever one you have previously implemented or whichever one you are most comfortable with. This example will be detailing authorization with JWT.

If you haven't done so already, make sure you have key pair authentication working with Snowflake already.

You can test to make sure you can successfully connect to Snowflake Key Pairs using the following command in your terminal:

$ snowsql -a <account> -u <user> --private-key-path <path to private key>

After you've verified you can connect to Snowflake using key-pair authentication, you'll need to generate a JWT token. 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.

$ snowsql -a <account> -u <user> --private-key-path <path to private key> --generate-jwt
<returns JWT token>

Here is an example of what that might look like in your terminal.

generate JWT token

You'll need the JWT token generated to use the Snowflake SQL API. The following headers need be set in each API request that you send within your application code:

In Postman, once again select the sample collection. Under the Authorization tab, notice the authorization method is set to Bearer Token so that Postman will automatically add an Authorization header formatted properly with the variable {{token}} to each request within this collection.

authorization helper

Let's update our token variable value next. Tab over to the Variables tab, and update the token variable with the JWT token generated with the previous SnowSQL command, and save your changes.

current value variables

Once you set up these authorization details for the collection, select one of the API requests. The Authorization tab indicates the request inherits the authorization method that we just set up for the collection.

inherit authorization method

Under the Headers tab of the request, find the Authorization key. You may need to expand the auto-generated headers. Hover over the Bearer {{token}} to see how Postman is updating the header with your credentials saved in a variable.

authorization header

In some cases, it might not be clear if Snowflake executed the SQL statement in an API request (e.g. due to a network error or a timeout). You might choose to resubmit the same request to Snowflake again in case Snowflake did not execute the statement.

If Snowflake already executed the statement in the initial request and you resubmit the request again, the statement is executed twice. For some types of requests, repeatedly executing the same statement can have unintended consequences (e.g. inserting duplicate data into a table).

To prevent Snowflake from executing the same statement twice when you resubmit a request, you can use a request ID to distinguish your request from other requests. Suppose you specify the same request ID in the initial request and in the resubmitted request. In that case, Snowflake does not execute the statement again if the statement has already executed successfully.

To specify a request ID, generate a universally unique identifier (UUID) and include this identifier in the requestId query parameter:

POST /api/v2/statements?requestId={{uuid}} HTTP/1.1

In Postman, select the first POST request in the Quickstart folder. Then select the Pre-request Scripts tab that contains JavaScript to execute before the primary API request. On row 2, the code randomly generates a unique identifier and saves the data to a variable called uuid.

generate random uuid

This data can then be used in the primary request as a query parameter variable for a unique request ID. If Snowflake fails to process a request, you can submit the same request again with the same request ID. Using the same request ID indicates to the server that you are submitting the same request again. This data can also be used in subsequent requests, like the next GET request in the folder to check the status of the execution of your statement.

Under the first POST request in the Quickstart folder, look under the Tests tab to see similar code to parse the statementHandle from the anticipated server response, and save that data as a variable to be used in subsequent requests.

statement handle

Now that you have been introduced to authorization and unique request IDs, let's submit a request to execute a SQL statement.

To submit a SQL statement for execution, send a POST request to the /api/v2/statements/ endpoint:

POST /api/v2/statements?requestId=<UUID> HTTP/1.1
Authorization: Bearer <jwt_token>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

(request body)

In Postman, select the first POST request in the Quickstart folder. Review the various elements of the request, including request URL, parameters, authorization method, headers, and request body.

In the request URL, you can set query parameters to:

For the body of the request, set the following fields:

request body

In Postman, Send the API request. If the statement was executed successfully, Snowflake returns the HTTP response code 200 and the first results in a ResultSet object. Review the server response on the bottom.

response body

Select the collection, and tab over to Variables to verify that your uuid and statementHandle are stored as variables.

set variables

Now let's check the status of the execution of the statement.

When you submit a SQL statement for execution, Snowflake returns a 202 response code if the execution of the statement has not yet been completed or if you submitted an asynchronous query.

In the body of the response, Snowflake includes a QueryStatus object. The statementStatusUrl field in this object specifies the URL to the /api/v2/statements/{{statementHandle}}endpoint that you can use to check the execution status:

{
"code": "333334",
"message": "Asynchronous execution in progress. Use provided query id to perform query monitoring and management.",
"statementHandle": "01a89ea6-0000-d838-0002-fce60001c006",
"statementStatusUrl": "/api/v2/statements/01a89ea6-0000-d838-0002-fce60001c006"
}

statement status URL

As illustrated by the URL above, in requests to check the status of a statement and cancel the execution of a statement, you specify the statement handle (a unique identifier for the statement) as a path parameter in order to identify the statement to use.

Note that the QueryStatus object also provides the statement handle as a separate value in the statementHandle field.

To check the status of the execution of the statement, send a GET request using this URL:

GET /api/v2/statements/{{statementHandle}}

For example, the following request checks the execution status of the statement with the handle 01a89ea6-0000-d838-0002-fce60001c006:

query by statement handle

In Postman, select the GET request in the Quickstart folder. Under the Params tab, hover over the uuid and statementHandle variables to ensure you are submitting the same unique request ID and statement handle from the previous API call. Then Send the API request.

check status of query

If the statement has finished executing successfully, Snowflake returns the HTTP response code 200 and the first 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.

Cancel the Execution of a SQL Statement

To cancel the execution of a statement, send a POST request to the cancel endpoint.

POST /api/v2/statements/{{statementHandle}}/cancel

In the next step, let's walk through an example of automating an API workflow.

Walk through an API workflow in Postman

In Postman, the folder Polling contains an example workflow of a long-running query and periodically checking if the process is completed.

The first request contains a long-running SQL query intended to take 20 seconds in duration. The second request checks if the query is completed, and will continue to check the status. Once a server response indicates the query is complete, the third request then runs, to simulate a notification about the query completion.

For each request, review the data under the following tabs to better understand how each API call works.

Step through each call, as we did in the previous sections, and click Send to observe the response from the server. You can manually check the status of the long-running query by repeatedly sending the Checks the status API request and inspecting the response.

Automate an API workflow

Or you can automate the checking and run the folder in its entirety.

run API requests manually

automate API workflow

Generate code snippets for your application

Once you have your API workflow running the way you want, you can schedule it to run periodically on Postman servers. Or you can replicate the API calls to run from your own applications and integrations.

Update the SQL statement and parameters to your liking, and then find the Code icon on the right context bar. Postman generates code snippets to replicate the API requests. Select from several frameworks and languages, and then copy and paste the code into your own applications.

generate code samples

This tutorial was a hands-on introduction to the Snowflake SQL API. To see what else you can do with the API, check out the Snowflake SQL API Reference and the Snowflake public workspace in Postman.

If you want more hands-on learning, check out this in-depth lab to dig deeper into the Snowflake SQL API, and learn about the following topics:

If you've completed this lab using the Snowflake free trial, continue exploring what Snowflake can help you accomplish. There are several ways Snowflake can help you with this: