I recently received the following question: “How do we enable generic access to data in Snowflake that we want to expose to external partners? We’d like them to be able to filter all the fields, but we can’t spend too much time on it either”. I didn’t have a response ready and knew I’d have to do some research before replying. And that’s where the Generic OData API popped up.
What did I do first, intuitively?
I looked at the standard API Development lifecycle proposed by MuleSoft.
But then I wondered: “Why would I want to write the entire API contract from scratch, listing out all of the individual parameters, knowing I’d have to do it all over again for future data sources?” I didn’t believe it would be scalable to have to write the API contract for each ‘simple table’ that needed to be accessible.
Let’s try something else!
The Generic OData API
I heard about MuleSoft’s APIKit for OData before. I had also consumed OData APIs in the past, but I had never implemented it myself. What a great opportunity to experiment with something that could actually be useful!
Looking at the documentation, the process seemed relatively simple:
- Describe the data that needs to be accessible, providing the datatypes for all fields (a timestamp is a date time, a speed is a number, a description is a string…).
- Use APIKit for OData in Anypoint Studio to scaffold the basic application structure.
- Work your magic to connect to the underlying database (Snowflake in this case).
- Check if everything works.
I won’t bother you with the details on the definition of the data, but it’s a very simple process that can easily be automated. Once the data definition was ready, I could scaffold the application structure. I connected all the Dots with some Arrows and got my connection to Snowflake working soon after.
APIKit @ work
Looking at the generated flows, it was clear that 95% of the work was already done for me. The APIKit for OData module adds a transformer that automatically looks for the provided input parameters (the filters and pagination* parameters in this case) and writes an SQL Select query that you can simply execute against a database.
The principle is quite simple. The ‘Transform to SQL select’ looks for any parameters that were provided with the API call and transforms these parameters into an SQL query. That SQL query can be executed towards the database. The ‘Query Snowflake’ does exactly what it says, and the ‘Serialize entity collection’ simply transforms the data into a readable format.
I’m happy to say that there’s not much more to it. With this in place, I could perform OData API calls towards Snowflake with most of the work going into describing the data. I could now query the data with any filter I’d like, without having to specifically implement each one.
An example. The APIKit module takes into account that the speed is a number and that we can easily filter for it being above or below a certain value, without having to manually implement the syntactical notation and the interpretation of this to get a valid SQL statement.
/odata4/MY_COLLECTION?$filter=SPEED gt 100&$top=3&$skip=2
So…
With this, I achieved what I wanted to achieve: providing a read API with a minimal amount of work. The remainder of the work can likely be automated easily, based on the schemas of the data source. This is an ideal scenario for exposing pure read operations on data sources that allow SQL querying.
* But… (there is always something) a slight issue, which was related to the LIMIT and OFFSET parameters acting differently in Snowflake (compared to most databases), popped up when testing. I won’t get into too much detail, but feel free to reach out if you’d like to know more.
