"Insert data into a SQLite database from JSON, CSV or TSV, automatically creating tables with the correct schema or altering existing tables to add missing columns."
There's a bunch of plugins (mix of official and user-developed) that might add other formats too, I'm not 100% sure:
I looked into Datasette before starting ROAPI. From a product/use-case point of view, to me Datasette focuses more on quick and easy ad-hoc data exploration type of work. ROAPI focuses more production ready online serving of static datasets. So I would expect users to use ROAPI to power micro-services in production with high QPS.
From a technical design point of view, ROAPI authors owns the full stack end to end from query parsing, data format parsing to query execution because I am also a maintainer of Apache arrow and it's sub-project datafusion. The whole project is built with Rust end to end from scratch. Datasette is mostly a wrapper around sqlite. It translates user actions into SQL queries, then execute them on sqlite. In ROAPI, we work at a lower level. We translate REST APIs, GraphQL and SQLs into datafusion logical plans and execute them. Datafusion is also a analytical compute engine optimized for columnar data, so it will be a lot faster for OLAP workload, while sqlite is optimized for OLTP. I also plan to add other type of query capabilities like nearest neighbor vector search for ML applications, etc.
Thank you for a genuine, well-thought answer. Much appreciated.
I'm not in the Data Science space so I only know of Datasette, but maybe worth copy-pasting that on a FAQ page under "How does ROAPI compare to X?" to avoid repeating it.
Similar question: how does it compare to using ClickHouse with http interface (which supports a number of output formats including Arrow and ArrowStream).
It does take a few more commands to start a ClickHouse server, create table and load data in... a friendly loader program could probably get pretty close to Roapi interface.
Both Roapi and Datafusion look very cool! Excited to learn about them.
I think these two systems explore different design spaces, the biggest difference I would say is Roapi can apply more read optimizations by exploiting the fact that it doesn't need to support frequent online updates from the client. Most of the datasets it serves will be static. For data-sources that supports streaming updates like delta tables, the update frequency will be much lower than what clickhouse supports.
"ROAPI is made up of 4K lines of Rust. This line count is low due to the intense use of 3rd party libraries."
This actually seems like a high line count, and that's not counting all the 3rd party libraries.
While not sexy, this is the sort of thing that PHP can do fairly easily in a much smaller # of lines of code and operate with lower resource requirements. I venture that this could be done using CSV parse, json decode, and other built-in PHP functions and use small infrastructure to make it work. I know PHP doesn't have a lot of love, but isn't this the sort of thing PHP is made for? Simple processing and hosting for API-based access to static file information? Is there a reason why Rust is needed with all the baggage?
The Parquet support, with the ability to handle row groups, skip over data not of interest to a query and aggregations that will likely be 3 orders of magnitude quicker are a pretty unique feature.
Also, there's no need to write any code here, it's a CLI app. There's much less that could go wrong versus rolling your own.
This is true, the core of it is Apache arrow datafusion query engine, which is also a project I help maintain. I doubt you will be able to beat it with PHP though ;) The VM overhead alone will cause a big hit to your performance even if we can get JIT to work.
According to linked in the author worked on Apache Arrow, so I suspect they knew about it. It feels like this is intended as a much more lightweight option, but would be interested to hear their take.
That's right, it's intended to be more lightweight since it's built with only Rust from the ground up. Apache Drill also only focuses on serving SQL as the user interface while ROAPI wants to provide a pluggable interface to support all use-cases. For example, we can plan graphql and rest api calls into query plan and efficiently execute them using Datafusion.
I always love when people realize the power and simplicity that can come with static serving of mostly unchanging datasets. It can vastly speed up development in many cases.
If you need a simple version of this, Hugo is a great and well proven option. Push onto your CDN of choice and you’ve got a blazing fast static json api.
Fine to use whatever tool that works for you.
Using the words “clients” here with reference to Hugo is confusing though, as clients would never be effected by it. The way I read that bug, it would be Hugo itself that is effected and crashes at generation time.
In its current form, the main use-case is to load data into memory first then serve them through query apis. Thomas has made some effort to support querying data directly from remote source without loading them into memory: https://github.com/roapi/roapi/pull/71. The underlying query engine, Apache Arrow Datafusion, supports running query on data stream on the granularity of partitions. This is not heavily used in roapi at the moment because I want to nail the in memory serving use-case first.
very cool!
For a project I used
https://github.com/ranaroussi/pystore
to stored TS(time series) data. As TS are mostly read-only so I try to avoid OLTP for certain use case that it's perfect for Parquet/DASK type of storage. I used Pandas to explore the data. This seems to be suited for high volume, query rate.
This looks really awesome, I need to explore this project more!
Here's a project which in part is built to fulfill a similar need: https://github.com/tobgu/qocache (I'm the author).
Most of the background/rationale for it and example usage can be found in the README of the original QCache project, linked from the above repo.
There are of course big differences between the projects but I find that they share the same goal of making random, file based, datasets easily accessible for querying.
I'll definitely let myself be inspired by Roapi, thanks!
29 comments
[ 3.3 ms ] story [ 92.5 ms ] threadSorry if it's a dumb question but I skimmed the docs and sample and it looks similar + didn't see any mention:
https://github.com/simonw/datasette
There's a bunch of plugins (mix of official and user-developed) that might add other formats too, I'm not 100% sure:
https://datasette.io/plugins?sort=downloads-this-week
From a technical design point of view, ROAPI authors owns the full stack end to end from query parsing, data format parsing to query execution because I am also a maintainer of Apache arrow and it's sub-project datafusion. The whole project is built with Rust end to end from scratch. Datasette is mostly a wrapper around sqlite. It translates user actions into SQL queries, then execute them on sqlite. In ROAPI, we work at a lower level. We translate REST APIs, GraphQL and SQLs into datafusion logical plans and execute them. Datafusion is also a analytical compute engine optimized for columnar data, so it will be a lot faster for OLAP workload, while sqlite is optimized for OLTP. I also plan to add other type of query capabilities like nearest neighbor vector search for ML applications, etc.
I'm not in the Data Science space so I only know of Datasette, but maybe worth copy-pasting that on a FAQ page under "How does ROAPI compare to X?" to avoid repeating it.
It does take a few more commands to start a ClickHouse server, create table and load data in... a friendly loader program could probably get pretty close to Roapi interface.
Both Roapi and Datafusion look very cool! Excited to learn about them.
You can load data into MergeTree table that support streaming data ingestion or into Memory table (that is read/write as well).
Also you can run queries on the data without any preprocessing (with `file`, `url` table functions or similar).
See also https://clickhouse.com/docs/en/operations/utilities/clickhou...
"ROAPI is made up of 4K lines of Rust. This line count is low due to the intense use of 3rd party libraries."
This actually seems like a high line count, and that's not counting all the 3rd party libraries.
While not sexy, this is the sort of thing that PHP can do fairly easily in a much smaller # of lines of code and operate with lower resource requirements. I venture that this could be done using CSV parse, json decode, and other built-in PHP functions and use small infrastructure to make it work. I know PHP doesn't have a lot of love, but isn't this the sort of thing PHP is made for? Simple processing and hosting for API-based access to static file information? Is there a reason why Rust is needed with all the baggage?
Also, there's no need to write any code here, it's a CLI app. There's much less that could go wrong versus rolling your own.
But if you assume JIT, you can also compile queries into native code on the fly. That should at the very least offset some of the difference.
If you need a simple version of this, Hugo is a great and well proven option. Push onto your CDN of choice and you’ve got a blazing fast static json api.
https://gohugo.io/templates/data-templates/
I fail to see how this method is not "simple enough"
Does Roapi needs to load the data file into memory first, and then answer the queries? Or does it handle the query while streaming through the data?
Here's a project which in part is built to fulfill a similar need: https://github.com/tobgu/qocache (I'm the author). Most of the background/rationale for it and example usage can be found in the README of the original QCache project, linked from the above repo.
There are of course big differences between the projects but I find that they share the same goal of making random, file based, datasets easily accessible for querying.
I'll definitely let myself be inspired by Roapi, thanks!
I checked the code. Is it loading data as "map[string]*list.Element"?
Yes, in the abstract sense, which I guess you mean. QFrame (https://github.com/tobgu/qframe), the underlying dataframe used, is column oriented.