Welcome! 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 most DDL and DML statements.

This getting started guide will walk you through executing a SQL statement with the API and retrieving the results.

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

Head to the SQL API by navigating to your version of the following URL, replacing *account_locator* with the account locator for your own Snowflake account:

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

Now let's break down the parts of the API before we begin using it. The API consists of the /api/statements/ resource and provides the following endpoints:

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

Limitations of the SQL API

It's important to be aware of the limitations that the SQL API currently has:

The following statements are also not supported:

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/statements?requestId=<UUID> HTTP/1.1

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.

Now let's move on to additional information you need to include in requests: authentication parameters.

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

If you haven't already done so, set up OAuth for authentication and get an OAuth token.

Once you have a token, you can use SnowSQL to verify that you can use a generated OAuth token to connect to Snowflake:

$ snowsql -a <account> -u <user> --authenticator=oauth --token=<oauth_token>

After you've verified you can connect to Snowflake with the token, you'll need to set the following headers in each API request that you send within your application code:

Altogether, your request query and header will take the following form:

POST /api/statements?requestId=<UUID> HTTP/1.1
Authorization: Bearer <oauth_token>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

Now that you have been introduced to authentication and unique request IDs, you can now move to actually making a request to execute a SQL statement.

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

POST /api/statements?requestId=<UUID> HTTP/1.1
Authorization: Bearer <oauth_token>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

(request body)

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

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

For example, the following request sends a SQL statement for execution. The request specifies that the results should be paginated (10 results per page).

POST /api/statements?pageSize=10 HTTP/1.1
Authorization: Bearer <oauth_token>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

{
"statement": "select * from T",
"timeout": 60,
"resultSetMetaData": {
"format": "json"
},
"database": "<your_database>",
"schema": "<your_schema>",
"warehouse": "<your_warehouse>",
"role": "<your_role>"
}

Let's go over some specific fields in this request:

If the statement was executed successfully, Snowflake returns the HTTP response code 200 and the first results in a ResultSet object. We'll go over how to check the status and retrieve the results after we look at including bind variables.

Now we'll look at how you can include bind variables (? placeholders) in the statement and set the bindings field to an object that specifies the corresponding Snowflake data types and values for each variable.

If you want to use bind variables (? placeholders) in the statement, use the bindings field to specify the values that should be inserted.

Set this field to a JSON object that specifies the Snowflake data type and value for each bind variable.

...
"statement": "select * from T where c1=?",
...
"bindings": {
"1": {
"type": "FIXED",
"value": "123"
}
},
...

Choose the binding type that corresponds to the type of the value that you are binding. For example, if the value is a string representing a date (e.g. 2021-04-15) and you want to insert the value into a DATE column, use the TEXT binding type.

The following table specifies the values of the type field that you can use to bind to different Snowflake data types for this preview release.

binding types

For additional information on binding specific data types, see the documentation's section on Using Bind Variables in a Statement

If the value is in a format not supported by Snowflake, the API returns an error:

{
code: "100037",
message: "<bind type> value '<value>' is not recognized",
sqlState: "22018",
statementHandle: "<ID>"
}

Whether you use bind variables or not, you'll want to check the status of your statements. Let's look at that next.

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 /statements/ endpoint that you can use to check the execution status:

{
"code": "090001",
"sqlState": "00000",
"message": "successfully executed",
"statementHandle": "e4ce975e-f7ff-4b5e-b15e-bf25f59371ae",
"statementStatusUrl": "/api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae"
}

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/statements/{statementHandle}

For example, the following request checks the execution status of the statement with the handle e4ce975e-f7ff-4b5e-b15e-bf25f59371ae:

GET /api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae HTTP/1.1
Authorization: Bearer <oauth_token>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0

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.

Once the statement has executed successfully, you can then retrieve the results, detailed in the next step.

Cancelling the Execution of a SQL Statement

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

POST /api/statements/{statementHandle}/cancel

If you submit a SQL statement for execution or check the status of statement execution, Snowflake returns a ResultSet object in the body of the response if the statement was executed successfully.

The following is an example of a ResultSet object that is returned for a query. The query specifies that the results should be paginated with 10 results per page. The numPages field in the resultSetMetaData object indicates that there are 10 pages of results, and the numRows field indicates that the query finds a total of 100 rows.

{
"code": "090001",
"statementHandle": "536fad38-b564-4dc5-9892-a4543504df6c",
"sqlState": "00000",
"message": "successfully executed",
"createdOn": 1597090533987,
"statementStatusUrl": "/api/statements/536fad38-b564-4dc5-9892-a4543504df6c",
"resultSetMetaData": {
"page": 1,
"pageSize": 10,
"numPages": 10,
"numRows": 100,
"format": "json",
"rowType": [
{
"name":"ROWNUM",
"type":"FIXED",
"length":0,
"precision":38,
"scale":0,
"nullable":false
}, {
"name":"ACCOUNT_NAME",
"type":"TEXT",
"length":1024,
"precision":0,
"scale":0,
"nullable":false
}, {
"name":"ADDRESS",
"type":"TEXT",
"length":16777216,
"precision":0,
"scale":0,
"nullable":true
}, {
"name":"ZIP",
"type":"TEXT",
"length":100,
"precision":0,
"scale":0,
"nullable":true
}, {
"name":"CREATED_ON",
"type":"TIMESTAMP_NTZ",
"length":0,
"precision":0,
"scale":3,
"nullable":false
}
]
},
"data": [
["0","customer1","1234 A Avenue","98765","1565481394123000000"],
["1","customer2","987 B Street","98765","1565516712912012345"],
["2","customer3","8777 C Blvd","98765","1565605431999999999"],
["3","customer4","64646 D Circle","98765","1565661272000000000"]
...
]
}

Determining If the Result Set Page Size Exceeds the Limit

The SQL API can return a result set page that has a maximum size of approximately 10 MB. If the result set page exceeds this size, the endpoint returns an HTTP response with a truncated result set in the body and the code field set to 391908:

HTTP/1.1 200 OK
...
{
"code": "391908",
...

If this occurs, send the request again with the pageSize parameter set to a smaller value that fits within the maximum size of a page.

Getting Metadata About the Results

In the ResultSet object returned in the response, the resultSetMetaData field contains a ResultSet_resultSetMetaData object that describes the result set (for example, the format of the results, the number of pages of results, etc.).

In this object, the rowType field contains an array of ResultSet_resultSetMetaData_rowType objects. Each object describes a column in the results. The type field specifies the Snowflake data type of the column.

{
"resultSetMetaData": {
"rowType": [
{
"name":"ROWNUM",
"type":"FIXED",
"length":0,
"precision":38,
"scale":0,
"nullable":false
}, {
"name":"ACCOUNT_NAME",
"type":"TEXT",
"length":1024,
"precision":0,
"scale":0,
"nullable":false
}, {
"name":"ADDRESS",
"type":"TEXT",
"length":16777216,
"precision":0,
"scale":0,
"nullable":true
}, {
"name":"ZIP",
"type":"TEXT",
"length":100,
"precision":0,
"scale":0,
"nullable":true
}, {
"name":"CREATED_ON",
"type":"TIMESTAMP_NTZ",
"length":0,
"precision":0,
"scale":3,
"nullable":false
}
]
},
}

Getting the Data From the Results

In the ResultSet object in the response, the results are in the data field. The data field contains an array of arrays in JSON. For example:

{
"data": [
["0","customer1","1234 A Avenue","98765","1565481394123000000"],
["1","customer2","987 B Street","98765","1565516712912012345"],
["2","customer3","8777 C Blvd","98765","1565605431999999999"],
["3","customer4","64646 D Circle","98765","1565661272000000000"]
],
}

Each array within the array contains the data for a row:

The data in the result set is encoded in JSON v1.0, which means that all data is expressed as strings, regardless of the Snowflake data type of the column.

For example, the value 1.0 in a NUMBER column is returned as the string " 1.0". As another example, timestamps are returned as the number of nanoseconds since the epoch. For example, the timestamp for Thursday, January 28, 2021 10:09:37.123456789 PM is returned as "1611871777123456789".

You are responsible for converting the strings to the appropriate data types.

Retrieving Additional Pages of Results

If you set the pageSize request parameter to paginate the results, Snowflake returns the first page of results in the response. You can use the numPages field in the ResultSet_resultSetMetaData object in the ResultSet object to determine the total number of pages of results.

To get the next page of results or other pages of results, use the URLs provided in the Link header in the HTTP response. The Link header specifies the URLs for retrieving the first, next, previous, and last page of the results:

HTTP/1.1 200 OK
Link: </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="last",
</api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="next",
</api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840512c?page=0>;rel="first"
...

Each URL in the header has a rel attribute with one of the following values:

With the knowledge of retrieving results, you can now begin to use the SQL API for your own use cases.

This tutorial was designed as 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.

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