Snowflake and REST API

  • it is quite big (around 50 MB) and all imports take time to warm up my lambda function
  • it offers some support of async queries, but it adds a lot of abstraction to it, so all internal cloud benefits are removed

https://{account}.{region}.snowflakecomputing.com

Everything is hosted in some region and for some dedicated tenant. The address is very familiar to all Snowflake users. It is typical way how we access the console. Behind the address you can also find the REST API.

POST /session/v1/login-request?warehouse={warehouse}

As expected the very first activity is the authentication. Because I am accessing it with temporary username and password I need to provide them in this endpoint. The simplified request I managed to craft looks like this one:

{
"data": {
"CLIENT_APP_ID": "lightweight-client",
"CLIENT_APP_VERSION": "0.0.1",
"ACCOUNT_NAME": "...",
"LOGIN_NAME": "...",
"PASSWORD": "..."
}
}
{
"data": {
"masterToken": "ver:1-hint:30...90-ETM...iE",
"token": "ver:1-hint:30...90-ET...sI",
"validityInSeconds": 3600,
"masterValidityInSeconds": 14400,
"displayUserName": "Client 8...xC",
"serverVersion": "5.38.0",
"firstLogin": true,
"remMeToken": null,
"remMeValidityInSeconds": 0,
"healthCheckInterval": 45,
"newClientForUpgrade": null,
"sessionId": 30...62,
"parameters": [ ... ],
"sessionInfo": { ... },
"idToken": null,
"idTokenValidityInSeconds": 0,
"responseData": null,
"mfaToken": null,
"mfaTokenValidityInSeconds": 0
},
"code": null,
"message": null,
"success": true
}

POST /queries/v1/query-request?requestId={random-uuid}

Having a token I am able to issue SQL query passing the token in the Authorization header as Snowflake Token=”ver:1-hint:30..90-ET..l8" with the following payload. The important piece in the snippet is the asyncExec flag, which indicates we do not want to wait for a result.

{
"sqlText": "select t.index, t.value::text as country from table(flatten(input => parse_json(?))) t",
"asyncExec": true,
"sequenceId": 1,
"querySubmissionTime": 1635322866647,
"bindings": {
"1": {
"type": "ANY",
"value": "[\"DE\", \"PL\", \"US\"]"
}
}
}
{
"data": {
"queryId": "01a...197e",
"getResultUrl": "/queries/01a...197e/result",
"queryAbortsAfterSecs": 300,
"progressDesc": null
},
"code": "333334",
"message": "Asynchronous execution in progress. Use provided query id to perform query monitoring and management.",
"success": true
}

GET /monitoring/queries/{query-id}

The query was just issued. I can check only its status by calling another endpoint. I need to pass acquired token in the Authorization header. If everything is successful I should get something like the following response, which indicates that the query succeeded.

{
"data": {
"queries": [
{
"id": "01...7e",
"status": "SUCCESS",
"state": "SUCCEEDED",
"sqlText": "select t.index, t.value::text as country from table(flatten(input => parse_json(?))) t"
}
],
"sessionsShort": [ ... ]
},
"code": null,
"message": null,
"success": true
}

POST /queries/v1/query-request?requestId={random-uuid}

Knowing the query succeeded I will issue another synchronous query to the endpoint I already touched. This time I will query the result set by query id. It is very important to not forget about Authorization header and to adjust the Accept header to application/snowflake.

{
"sqlText": "select * from table(result_scan('01...7e'))",
"asyncExec": false,
"sequenceId": 1,
"querySubmissionTime": 1635066639000
}
{
"data": {
"parameters": [ ... ],
"rowtype": [
{ "name": "INDEX", ... },
{ "name": "COUNTRY", ... }
],
"rowset": [
[ "0", "DE" ],
[ "1", "PL" ],
[ "2", "US" ]
],
"total": 3,
"returned": 3,
"queryId": "01...62",
"queryResultFormat": "json"
},
"code": null,
"message": null,
"success": true
}
{
"data": {
"parameters": [ ... ],
"rowtype": [
{ "name": "INDEX", ... },
{ "name": "COUNTRY", ... }
],
"rowset": [],
"qrmk": "kg...uB0=",
"chunkHeaders": {
"x-amz-server-side-encryption-customer-key": "...",
"x-amz-server-side-encryption-customer-key-md5": "..."
},
"chunks": [
{
"url": "https://...",
"rowCount": 525,
"uncompressedSize": 100002,
"compressedSize": 22505
}
],
"total": 11785,
"returned": 11785,
"queryId": "01...a2",
},
"code": null,
"message": null,
"success": true
}

Actual implementation

By introduction I mentioned I needed REST approach to deal with the Snowflake in AWS Lambda. Actually I managed to create working implementation and currently it is in POC stage.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Adrian Macal

Adrian Macal

Software Developer, Data Engineer with solid knowledge of Business Intelligence. Passionate about programming.