3 ms·
My Datasette project has given me a slightly different perspective on API standards - at least in terms of things like shared definitions of XML, JSON shapes, R
by simonw 6y ago
My Datasette project has given me a slightly different perspective on API standards - at least in terms of things like shared definitions of XML, JSON shapes, RDF schemas etc.
Once you've loaded your data into a SQLite database and put Datasette in front of it, your audience can get the data out in any shape that they like.
Consider the museum data in my https://www.niche-museums.com/ https://www.niche-museums.com/ website.
Using a SQL query, I can get it out as an array of objects with name, url, address, latitude, longitude: https://www.niche-museums.com/browse.json?sql=select+name%2C+url%2C+address%2C+latitude%2C+longitude+from+museums+limit+10&_shape=array https://www.niche-museums.com/browse.json?sql=select+name%2C...
Or maybe I want to get back the name as "title" and the latitude longitude as lat / lng. I can do that with a different SQL query:
select name as title, latitude as lat, longitude as lng from museums limit 10
Here's that as JSON: https://www.niche-museums.com/browse.json?sql=select+name+as+title%2C+latitude+as+lat%2C+longitude+as+lng+from+museums+limit+10&_shape=array https://www.niche-museums.com/browse.json?sql=select+name+as...
Let's say I want it as an Atom feed. Datasette plugins can add different output renderers, so I can get back an Atom feed like this:
https://www.niche-museums.com/browse.atom?sql=SELECT%0D%0A++%27tag%3Aniche-museums.com%2C%27+%7C%7C+substr(m.created%2C+0%2C+11)+%7C%7C+%27%3A%27+%7C%7C+m.id+as+atom_id%2C%0D%0A++m.name+as+atom_title%2C%0D%0A++m.created+as+atom_updated%2C%0D%0A++%27https%3A%2F%2Fwww.niche-museums.com%2Fbrowse%2Fmuseums%2F%27+%7C%7C+m.id+as+atom_link%2C%0D%0A++coalesce(%0D%0A++++%27%3Cimg+src%3D%22%27+%7C%7C+m.photo_url+%7C%7C+%27%3Fw%3D800%26amp%3Bh%3D400%26amp%3Bfit%3Dcrop%26amp%3Bauto%3Dcompress%22%3E%27%2C%0D%0A++++%27%27%0D%0A++)+%7C%7C+render_markdown(%0D%0A++++m.description+%7C%7C+%27%0D%0A%0D%0A%27+%7C%7C+coalesce(%0D%0A++++++(%0D%0A++++++++select%0D%0A++++++++++group_concat(%0D%0A++++++++++++%27*+%5B%27+%7C%7C+json_extract(p.value%2C+%27%24.title%27)+%7C%7C+%27%5D(%27+%7C%7C+json_extract(p.value%2C+%27%24.url%27)+%7C%7C+%27)+%27+%7C%7C+json_extract(p.value%2C+%27%24.author%27)+%7C%7C+%27%2C+%27+%7C%7C+json_extract(p.value%2C+%27%24.publication%27)+%7C%7C+%27%2C+%27+%7C%7C+json_extract(p.value%2C+%27%24.date%27)%2C%0D%0A++++++++++++%27%0D%0A%27%0D%0A++++++++++)%0D%0A++++++++from%0D%0A++++++++++json_each(coalesce(m.press%2C+%27%5B%7B%7D%5D%27))+as+p%0D%0A++++++)%2C%0D%0A++++++%27%27%0D%0A++++)%0D%0A++)+%7C%7C+coalesce(%0D%0A++++(%0D%0A++++++select%0D%0A++++++++group_concat(%0D%0A++++++++++%27%3Cp%3E%3Cimg+src%3D%22%27+%7C%7C+json_extract(ph.value%2C+%27%24.url%27)+%7C%7C+%27%3Fw%3D400%26auto%3Dcompress%22%3E%3C%2Fp%3E%27%2C%0D%0A++++++++++%27%27%0D%0A++++++++)%0D%0A++++++from%0D%0A++++++++json_each(coalesce(m.photos%2C+%27%5B%7B%7D%5D%27))+as+ph%0D%0A++++)%2C%0D%0A++++%27%27%0D%0A++)+as+atom_content_html%2C%0D%0A++%27Simon+Willison%27+as+atom_author_name%2C%0D%0A++%27https%3A%2F%2Fsimonwillison.net%2F%27+as+atom_author_uri%0D%0AFROM%0D%0A++museums+m%0D%0Aorder+by%0D%0A++m.created+desc%0D%0Alimit%0D%0A++15 https://www.niche-museums.com/browse.atom?sql=SELECT%0D%0A++...
That's using a convoluted SQL query to define exactly what I want back in that feed.
The point is: if you have a sufficiently flexible API gateway in front of your data, the people consuming your data can define the "standard" that they want to retrieve it in at runtime.
I think that's a really interesting characteristic. It's made me less excited about boil-the-ocean attempts at defining a single standard and getting everyone to support it at once.
[ This thread may not be the best place to raise this idea, since the Python Data API Standards project isn't really in the same realm as the web API formats I'm talking about here ]
- travisoliphant 6y agoI agree that interfaces like Datasette are a fantastic idea. But, it's not the same thing. Think of this more like the SQL standard. What would you do if SQL did not exist and there were no common ways to discuss querying databases?
- simonw 6y ago100% agree, this isn't the right thread for my random thoughts on web APIs!
- sradman 6y agoI don’t think your ideas are random nor out of place. Durable data accessible by different clients is an important property that bleeds into the data science realm. Apache Arrow seems to partially tackle this problem by moving the data to shared memory that can be accessed by different runtimes like Python and R. There are a couple of problems I see with SQLite used as a common data format: 1. SQLite is a state machine that only stores the most recent version of mutable (binary) tables so change control is non-trivial. 2. Matrices/Tensors are not naturally stored in relational tables.