Snowflake DB: REST API guide for the masses

 I've recently been experimenting with establishing a connection to a snowflake db instance via their Snowflake Query REST API. Little did I realize that this would be a minor odyssey in my life, so I'll do my best here to collect the important bits that made it possible for me. 

I jumped into the free account with Snowflake, which only lasts for a month, so I'll be very interested to see if there are any viable local testing options like what I see at https://blog.localstack.cloud/2024-05-22-introducing-localstack-for-snowflake/. I just wasn't getting any warm fuzzy feelings when I was trying to figure out whether they'd be supporting the same query REST API. So, I definitely setup the trial account in their hosted app.snowflake.com, to try out the real deal, to start with. I'll definitely want to circle back to that localstack container once I see everything working end to end.

I need to follow a well-known Oauth path to acquire an API token to make authenticated requests, as this will be used in a machine-to-machine (M2M) integration. Turns out this is no easy feat, even when following the documentation (surprise!).

Their Oauth docs do seem kind of all over the place, as you may be using your own IDP to establish a session as seen in their Introduction to OAuth. However, I do not see anything related to a good old web-based oauth token connection in their list of clients and connectors. Luckily, I did find a random article in their knowledge base to Generate and use an OAuth token using Snowflake OAuth for custom clients, so finally - we're getting the goods!

So, basically you'll follow that guide to create a Security Integration in your snowflake console with their steps below, and I'll add additional pointers which were key for me:

1. Create Security Integration
2. Get your client_id, client_secret, as documented
3. Request an Auth Code Grant
• Prepare the URL as shown, and you'll copy it to your browser for manual login
• Note: Only url encode the params and not the entire URL, that will not work 
• REALLY NOTE: there is a problem with the url they give you here, in that you must also include the "role" info (SYSADMIN did work for me). There is another clutch community guide which outlines the "Invalid Consent Request" error you'll run into, if you don't follow their example format:
• https://orgname-acountname.snowflakecomputing.com/oauth/authorize?client_id=URL-ENCODED-CLIENT-ID&response_type=code&redirect_uri=URL-ENCODED-REDIRECT-URI&scope=session%3Arole%3Asysadmin 
• prepare some tea, this is going to take a while
4. Use auth code to grant request to access token
• If you got this far, you are really cookin' - but you're not there, YET
• I highly recommend you keep using the Refresh token, so you can keep it fresh. Otherwise, when the access token expires, you'll need to make that really slow call to get Auth Code Grant once more.

Now, you're bopping along, got your bearer token and can call their sweet REST API. I recommend loading their sample set(s) if you don't have data of your own to verify these.  After trying things out in the dev console to make sure i know what i'm doing, i excitedly load up my query for the API: 

curl --location 'https://myguy.snowflakecomputing.com/api/v2/statements?requestId=9ace15ff-c4a9-4491-8226-2d25d77bd529' \

--header 'Content-Type: application/json' \

--header 'Authorization: ••••••' \

--data '{

"statement": "select * from CUSTOMER limit 10",

"timeout": 60,

"database": "SNOWFLAKE_SAMPLE_DATA",

"schema": "TPCH_SF1"

}'

Only to run into this beauty:

"message": "SQL compilation error:\nObject 'CUSTOMER' does not exist or not authorized."

 Well, to make a long, painful story short, it turns out you need to GRANT that SYSADMIN role access to your warehouse, your db, your table, EVERYTHING, or you WILL see this gem. I'll leave that to you, but you'll want to figure out how to poke around in their dev tool to see roles on different objects. Here were some key queries I was using in diagnosing my permission woes:  

show roles;

SHOW GRANTS TO USER myself;

GRANT ROLE SYSADMIN TO USER myself;

GRANT USAGE

  ON WAREHOUSE COMPUTE_WH

  TO ROLE SYSADMIN;

And for sanity sake, when you want to debug the queries intended for REST API, make sure you're running them in console with SYSADMIN (or whatever role you picked) to verify execution:

Lastly, if you're still running into issues with "'Thing' does not exist", be aware that property values in REST may be CASE SENSITIVE!! So, you can write your query in console with lower case names, but in REST call your "database", "schema" other attributes better be upper case, or more likely, match the case shown in the object explorer (which seems to default to upper case in the samples).

Here's an example of a successful query once things are set correctly: 

•  and don't forget the "requestId=UUID()", else you may never see new results, after making changes 👀

curl --location 'https://myguy.snowflakecomputing.com/api/v2/statements?requestId=a1b5a492-a627-4a9b-8838-e19e5f556fdd' \

--header 'Content-Type: application/json' \

--header 'Authorization: ••••••' \

--data '{

"statement": "select * from CUSTOMER limit 10",

"timeout": 60,

"database": "SNOWFLAKE_SAMPLE_DATA",

"schema": "TPCH_SF1"

}'

# resp:

{

"resultSetMetaData": {

"numRows": 10,

....

"data": [

[

"60001",

"Customer#000060001",

"9Ii4zQn9cX",

"14",

"24-678-784-9652",

"9957.56",

"HOUSEHOLD",

"l theodolites boost slyly at the platelets: permanently ironic packages wake slyly pend"

],

...

Holy Mother of Pearl - I should get paid to work like this!