There are various methods using which we can get data from snowflake. Either it is to satisfy business needs or to get data for custom scripts in CLI, snowflake does a great job. Today we are going to learn how to query data from snowflake using custom curl statements and Postman. Creating Custom API calls in postman are fairly simple. That's why we are going to cover both Postman and Curl.

Understanding basics of Curl

CURL stands for Client URL. It is a command line tool/library that allows us to transfer data over network protocols. It supports various protocols such as HTTP, HTTPS, FTP, SMB, etc. We can send custom requests such as GET, POST, PUSH, DELETE, etc. 

Pre-requisites

We are going to use Oauth to get data from snowflake. Here is the detailed Guide on how to set up Oauth in Snowflake. After successful configuration of Oauth in Snowflake we should be having the following parameters:

1. CLIENT_ID

2. CLEINT_SECRET

3. REDIRECT_URI

4. ACCESS_TOKEN_URL

5. OAUTH_REDIRECT_URI

Next we need a code to get our refresh token. Please use this link and follow the steps till Step - 2 to get code.

Curl request to get Refresh Token

Before proceeding further please make sure that we are having all prerequisites mentioned above along with the code.

Curl uses fairly simple syntax. We will be using the following arguments:

1. --user <client_id:client_secret> (used for Basic AUTH)

2. -H (to specify header)

3. --data-urlencode (to specify additional arguments)

and finally we required a URL to send a request i.e.  ACCESS_TOKEN_URL.

Curl Request to get Access Token via Refresh Token

In Oauth we need access token so that we can query data from snowflake. But we got access token with refresh token in step above . Can't we use that access token? Well, technically we can use that but snowflake's access token has a lifespan of just 599 secs (10 mins approx). So instead of repeating the entire process every 10 mins, we can use a refresh token. We can generate a new access token via refresh token.

We can create a custom API call to generate a new access token via refresh token.

Preparing Curl Request

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" \

--user "CLIENT_ID not encoded>:<CLIENT_SECRET>" \

--data-urlencode "grant_type=refresh_token" \

--data-urlencode "refresh_token="<REFRESH_TOKEN>" \

<ACCESS_TOKEN_URL>

In our case, the request will look something like this:

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" \

--user "XXXXXXXXXXXXXXXXXXXLBQh8=:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXT7+Yw=" \

--data-urlencode "grant_type=refresh_token" \

--data-urlencode "refresh_token=ver:2-hint:1111320986XXXXX VERY LONG REFRSH TOKEN HERE" \

https://XXXXXXX.ap-southeast-1.snowflakecomputing.com/oauth/token-request

NOTE: If you get curl error in above syntax in cmd then remove all '\' and  write entire command in one line like this

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" --user "XXXXXXXXXXXXXXXXXXXLBQh8=:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXT7+Yw=" --data-urlencode "grant_type=refresh_token" --data-urlencode "refresh_token=ver:2-hint:1111320986XXXXX VERY LONG REFRSH TOKEN HERE" https://XXXXXXX.ap-southeast-1.snowflakecomputing.com/oauth/token-request

We will get output something like this

{

  "access_token" : "ver:1-hint:XXXXX ACCESS TOKEN",

  "token_type" : "Bearer",

  "expires_in" : 600,

  "idpInitiated" : false

}

Query Snowflake Using access token

We can generate curl statement that can query data from snowflake. If our Oauth access token expires, we need to get a new access token again before proceeding. I recommend using a Linux shell to run this request.

Preparing Curl Request

curl --location --request POST 'https://XXXXXX.ap-southeast-1.snowflakecomputing.com/api/v2/statements/' \

-H "Content-Type: application/json" \

-H "Authorization: Bearer <ACCESS_TOKEN>" \

--data-raw '{

    "statement":"SQL QUERY HERE",

    "timeout": 60,

    "resultSetMetaData": {"format":"json"},

    "database":"DATABASE",

    "schema":"SCHEMA",

    "warehouse":"COMPUTE_WH"

}'

In our case, the request will look something like this:

curl --location --request POST 'https://XXXXXXX.ap-southeast-1.snowflakecomputing.com/api/v2/statements/' \

-H "Content-Type: application/json" \

-H "Authorization: Bearer ver:1-hint:<Access token>" \

--data-raw '{

    "statement":"select * from customer limit 5",

    "timeout": 60,

    "resultSetMetaData": {"format":"json"},

    "database":"DB",

    "schema":"RAW",

    "warehouse":"COMPUTE_WH"

}'

We will get the output of the query in JSON format as a result.

Understanding basics of Postman

Postman is a platform for API Development. It enables us to explore, debug and test our API endpoints. It supports various types of HTTP requests such as GET, POST, PUT, PATCH. It can also convert the API to code for languages like Python, JS etc. 

Pre-requisites

We are going to use Oauth to get data from snowflake. Here is the detailed Guide on how to setup Oauth in Snowflake. After successful configuration of Oauth in Snowflake we should be having the following parameters:

1. CLIENT_ID

2. CLEINT_SECRET

3. REDIRECT_URI

4. ACCESS_TOKEN_URL

5. OAUTH_REDIRECT_URI

After successful sign in you will be propted in postman like this.

NOTE : If you get an "Invalid consent request" error while logging in, make sure that user has permission to operate on the Storage Integration Object. Also the user must not be an ACCOUNTADMIN, ORGADMIN or SECURITYADMIN. These roles are by default added to BLOCKED_ROLES_LIST.

Oauth access Token expired

Get New Access Token via Refresh Token

If we get the above error we need to generate a new access token by using refresh token.

NOTE: Access Token will expire in 10 mins so if we need to get data after 10  mins then we will require to get a new access token every time via refresh token.