How to use Snowflake SQL API

SQL API is one of many Snowflake features which is essentially a REST API. A REST API(RESTful API) is an application programming interface(API or web API) that is driven by the REST framework/architectural style. If you are new to the concepts of REST API or API generally, please refer to What is a REST API?

Image shows a basic request/response flow of a typical REST API.

Snowflake SQL API can be used to access and update data in a Snowflake database. You can easily set up this API and use this to develop custom applications and integrations. Since this API is already hosted and managed by Snowflake itself, you need not worry about the deployment aspect of it.

With this API, you can:

• Submit SQL statements for execution.
• Check the status of the execution of a statement.
• Cancel the execution of a statement.

The instructions on how to set up and use the API on Snowflake official documentation is fairly straightforward although it might be too technical for someone who has not worked with APIs before. This blog breaks down the flow into simple steps, following which, any beginner can easily set up and start using the SQL API.

The flow involves 2 major steps:

1. Creating Security Integration Object on Snowflake.
2. Calling the OAuth endpoints to get tokens which is demonstrated in 2 ways:

(a) Calling individual OAuth endpoints.

(b) Calling via Postman Authorization tab.

Once tokens are received, you can test the SQL API using the tokens and once the connection is tested, the API is ready to be used.

For the sake of this blog, we will first create a table in Snowflake with dummy data before proceeding with the steps.

Let us first create a database “DEMO_DB” in Snowflake, a table called “MOVIES” inside the DB and then insert a few movie related data into it.

The table with data is now created:

STEP 1: Creating Security Integration Object on Snowflake:

Let us now create a Security Integration object. An integration is a Snowflake object that provides an interface between Snowflake and third-party services, such as a client that supports OAuth. ACCOUNTADMIN role is needed to create this object. Post creation of this object, we can fetch CLIENT_ID, OAUTH endpoints needed for authentication step.

Run the following command to fetch the following parameter and values:

OAUTH_CLIENT_ID, OAUTH_AUTHORIZATION_ENDPOINT, OAUTH_TOKEN_ENDPOINT which will be used for authentication.

DESC INTEGRATION <INTEGRATION_NAME>;

To fetch CLIENT_SECRET code, run the following code:

Security integration object has other optional parameters like “blocked_roles_list” which allows you to list Snowflake roles that a user cannot explicitly consent to using with the integration, “pre_authorized_roles_list” which allows certain roles to have the access.

Note: It is important to note that by default, the account administrator (i.e users with the ACCOUNTADMIN system role) and security administrator (i.e users with the SECURITYADMIN system role) roles are included in this list and cannot be removed. If you have a business need to allow users to use OAuth with these roles, and your security team is comfortable with allowing it, please contact Snowflake Support to request that these roles be allowed for your account.

STEP 2: Calling the OAuth endpoints to get tokens:

(a). Calling individual Oauth endpoints:

The OAuth that we would be dealing with is OAuth 2.0. Snowflake provides 2 endpoints for authorization and token requests:

AUTHORIZATION:

Endpoint: <snowflake_account_url>/oauth/authorize

Method: GET

Query Parameters: client_id, response_type, redirect_url

Response: code

Sample: https://<account-identifier>.snowflakecomputing.com/oauth/authorize?client_id=PDKjS%2Bqtl9t3boNBYJjaPAYlBUs%3D&response_type=code&redirect_uri=https%3A%2F%2Fgoogle.com

This particular GET call (when accessed in a browser) authenticates the client and gives oAuth_code in response ( appended as a query param to your callback url in the browser).

This code is a short-lived authorization code, which can be exchanged at the token endpoint for an access token. Use this code for the next POST call to the next token-request API endpoint.

TOKEN REQUEST

Endpoint: <snowflake_account_url>/oauth/token-request

Method: POST

Query Parameters: grant_type = ‘autherization_code’, redirect_url =same as used in the above GET call, client_id = same as used above, code = oAuth_code obtained from above GET call,

Headers: Content-type = application/x-www-form-urlencoded

Authorization: Basic Base64(<client_id>:<client_secret>)

Note: client_id & client_secret in the above colon separated format should be Base64 encoded and prefixed with ‘Basic’ like shown above. This is called Basic Authentication.

Sample: https://<account-idenifier>.snowflakecomputing.com/oauth/token-request?grant_type=authorization_code&redirect_url=https://google.com&client_id=PDKjS+qtl9t3boNBYJjaPAYlBUs=&code=7AF821B80811CE3DF03D320979AA68E190A4709D

Expected o/p = access_token, refresh token, expiry time etc.

(a). Calling via Postman Authorization tab:

Postman is an API platform that makes it easy for developers to create, share, test and document APIs. It simplifies each step of the API lifecycle and streamlines collaboration so you can create better APIs — faster. You can easily download to your local machine on any OS.

The application provides an easy to use interface for Authorization. All you need to do is provide the below details and you need not hit individual OAuth endpoints for token exchange. Once verified, the end tokens can be easily fetched which can then be used in the Snowflake SQL API. Details required for Authorization:

1. Callback URL (OAUTH_REDIRECT_URI )
2. Auth URL (OAUTH_AUTHORIZATION_ENDPOINT)
3. Access token URL (OAUTH_TOKEN_ENDPOINT)
4. Client ID
5. Client Secret

There are few other parameters that need to be filled like Scope, State, Client Authentication, Token Name etc, some of which are optional. The setup is shown below:

Note: Select OAuth 2.0 in the left panel if you’re using Snowflake provided OAuth.

After adding all the necessary parameter values, click on the “Get New Access Token” button.

On successful initialization, a Sign in window will appear which prompts you to enter your Snowflake login credentials to verify and Authenticate.

Post successful authentication, a confirmation message pops on the screen like below:

On clicking the “Proceed” button, Access token, Refresh token, expiry time details etc are shown on the screen.

You can use this Access token in the Snowflake SQL API in the Header section with a prefix “Bearer”, this token is valid for a short period of time. You can use this token with the API multiple times as long as the token is valid.

Below image shows the SQL API endpoint and Headers section:

The following objects can be structured in a JSON format in the API body for sending request:

This is how the API response looks like:

The API throws errors with proper error codes and error description whenever there is any authorization issue, object related issues, which can be easily traced back and solved. Apart from this, Snowflake has a good community where you can post your queries and expect a solution.

Happy learning : )