Tuesday, October 1, 2024

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!

 

 

 

 



Wednesday, February 13, 2013

Vaadin: Master-Details View

As part of our post-Flex migration at Ideas, we are currently evaluating the Vaadin framework. A key feature of Flex for us was their nifty data binding mechanism, so we are taking a look at binding UI elements to our data models. Vaadin does include a basic master-details view implementation in their wiki, but they have not supplied the full source. I wanted to compile the example myself, for experimentation, so I've posted what I have for now, below.

Will keep updated as I make improvements. Would like to see a better way to share code, short of new Git repo!

This version points to custom theme, and is also missing a simple Hobby and Person entity.

Src: https://vaadin.com/wiki/-/wiki/Main/Creating%20a%20master-details%20view%20for%20editing%20persons

Thanks to the magic of the interwebs, I've discovered the beauty of Gist - tiny snippet repos on github. Github is the gift that keeps on giving!

Sunday, February 3, 2013

On Being Certifiable

Long before I got my foot in the door as a "real" developer, I'd always had a desire to get a passing mark on the Java Sun certification (SCJP). I even picked up a monstrous tome for Java 5 to help prep, but never made much progress through it. Now that I've been looking at the transition from Flex to a more java-centric approach, I'm finding the fires rekindled. Now that Oracle has taken the reins from Sun, I've started looking into the updated Java certification requirements. It looks like they've split the cert into at least two levels, with an Associate and Professional, respectively.

I've started compiling some resources that may prove useful:

Oracle certifaction

Certification tracks

I can live with the fact that the cert exam will cost a bit. At $350, it's expensive enough that I'd not approach the exam lightly, which I'm sure is the intention. However, I find it a bit chafing that the prep materials are also fairly steep, so it's obvious that there is a bit of an economy built up around these certifications, which I guess should come as no surprise.

The one bone they've thrown is a small set of sample questions:

Sample questions

I suppose now I'll have to start hunting down some free resources, as I doubt my employer will be buying me the required training materials.

Luckily for me, our parent company SAS does provide access to a Safari books subscription, where I did find a couple titles.

This one seemed promising - however, Amazon reviews did not have the kindest words for it.

Oracle Certified Associate Programmer Study Guide

While looking on Amazon, I did recognize this title, which their readers do review well. Sounds like a title that does cover the topic well - also sounds like it does a fair job of preparing readers for the certification.

Java 7 SE Programming Essentials, John Wiley

Digging some more into their results turned up this interesting title, which seems right up my alley. However, I'm not sure if it would be quite as beneficial for the certification prep.

The Well-Grounded Java Developer: Vital techniques of Java 7 and polyglot programming

I'll try and give some feedback on whichever titles or resources I may find valuable in this process.

Followup:

While looking for code samples for the Programming Essentials book, I did run across Oracles own tutorial resources for both Exam I & II:
Tutorials: Programmer Level I Exam
  

Sunday, January 20, 2013

Vaadin Interactive Turtle v1


So - Amazon cloud services didn't work out so well for me. Was unpleasantly surprised to see a bill for about $18 show up in my inbox after only a few days of playing about with their service. I hadn't even gotten to actually deploying any code at all, and was still getting hit with nearly $20 in charges. Turns out they will start charging you for total run time of any instances, regardless of whether they are doing anything, or hosting anything. Guess I'd been spoiled by previous experiences where these platform services only billed for actual processing time, or would simply gate-keep you to limit the available resources, or a combination thereof. So, I've gone back to trying CloudBees, which is where I'm currently hosting my sample code. Has been working fine so far, but I'm running a Vaadin app, which is completely server-side, and CloudBees only gives you 128MB RAM for their free app instances. Guess I'll see how long it takes for Vaadin to blow out that restriction.

Here my turtle:

http://vaadonis.lci.cloudbees.net/

Wednesday, January 16, 2013

Back in the saddle again

I want to thank FlashDan aka Vaadan for pointing out the lovely services available via Blogspot and Amazon's EC2 / Elastic Beanstalk. He's helped me find new motivation to get back into the blogging / demo scene.

I'm looking at using Blogspot to host these blog entries. If all goes well, I'll be hosting some example applications on the Amazon infrastructure. Currently, my primary focus has been with Vaadin for Java-based front-end development. Beyond Java solutions, I'm also hoping to spend some time looking at Javascript in the front end as well as some interesting Node.js solutions. Of those, meteor.js definitely looks like it may hold some features and capabilities of interest.