datafusion

Rust has amazing integration with Python through PyO3 [1] so see it like a safe alternative for high performance calculations and operations. The rust data science ecosystem itself is starting to come together with projects like Polars [2] (Pandas alternative), nalgebra [3], Datafusion [4] and Ballista [5]. Of course nowhere near Python but that is hard to beat today. [1] https://github.com/PyO3/pyo3 [2] https://github.com/pola-rs/polars/ [3] https://github.com/dimforge/nalgebra [4] https://github.com/apache/arrow-datafusion [5] https://github.com/apache/arrow-ballista

It could be the NDJSON parser (DF source: [0]) or could be a variety of other factors. Looking at the ROAPI release archive [1], it doesn't ship with the definitive `columnq` binary from your comment (EDIT: it does, I was looking in the wrong place! https://github.com/roapi/roapi/releases/tag/columnq-cli-v0.3...), so it could also have something to do with compilation-time flags. FWIW, we use the Parquet format with DataFusion and get very good speeds similar to DuckDB [2], e.g. 1.5s to run a more complex aggregation query `SELECT date_trunc('month', tpep_pickup_datetime) AS month, COUNT(*) AS total_trips, SUM(total_amount) FROM tripdata GROUP BY 1 ORDER BY 1 ASC)` on a 55M row subset of NY Taxi trip data. [0]: https://github.com/apache/arrow-datafusion/blob/master/dataf... [1]: https://github.com/roapi/roapi/releases/tag/roapi-v0.8.0 [2]

SPyQL is really cool and its design is very smart, with it being able to leverage normal Python functions! As far as similar tools go, if you're interested, I recommend taking a look at DataFusion[0], dsq[1], and OctoSQL[2]. DataFusion is a very (very very) fast command-line SQL engine but with limited support for data formats. dsq is based on SQLite which means it has to load data into SQLite first, but then gives you the whole breath of SQLite, it also supports many data formats, but is slower at the same time. OctoSQL is faster, extensible through plugins, and supports incremental query execution, so you can i.e. calculate and display a running group by + count while tailing a log file. It also supports normal databases, not just file formats, so you can i.e. join with a Postgres table. [0]: https://github.com/apache/arrow-datafusion [

Just wanted to also give a shout out to Apache DataFusion[0] that IOx relies on a lot (and contributes to as well!). It's a framework for writing query engines in Rust that takes care of a lot of heavy lifting around parsing SQL, type casting, constructing and transforming query plans and optimizing them. It's pluggable, making it easy to write custom data sources, optimizer rules, query nodes etc. It's has very good single-node performance (there's even a way to compile it with SIMD support) and Ballista [1] extends that to build it into a distributed query engine. Plenty of other projects use it besides IOx, including VegaFusion, ROAPI, Cube.js's preaggregation store. We're heavily using it to build Seafowl [2], an analytical database that's optimized for running SQL queries directly from the user's browser (caching, CDNs, low latency,

Another "database toolkit" project that I've recently learned about is Apache DataFusion, also written in rust and uses Arrow memory format: https://github.com/apache/arrow-datafusion/blob/master/READM...

To add somewhat of a counterpoint to the other response, I've tried the Steampipe CSV plugin and got 50x slower performance vs OctoSQL[0], which is itself 5x slower than something like DataFusion[1]. The CSV plugin doesn't contact any external API's so it should be a good benchmark of the plugin architecture, though it might just not be optimized yet. That said, I don't imagine this ever being a bottleneck for the main use case of Steampipe - in that case I think the APIs themselves will always be the limiting part. But it does - potentially - speak to what you can expect if you'd like to extend your usage of Steampipe to more than just DevOps data. I've used the benchmark available in the OctoSQL README. [0]: https://github.com/cube2222/octosql [1]: https://github.com/apache/arrow-datafusion Disclaimer: author of OctoSQL

Rust is also being used a lot in that space: https://github.com/MaterializeInc/materialize https://github.com/apache/arrow-datafusion https://github.com/pola-rs/polars

Slightly off-topic, but I appreciate any pointers: In a project, I want to offer an SQL interface for data analysts similar to Stripe Sigma[1]. The tricky thing is not to parse the query but to map the public schema to the private schema, add authorization, and distribute the query across data stores. So, I am looking for a customizable query parser, planner, and execution engine. I briefly looked into Apache Calcite and DataFusion[2], but I am unsure if I am on the right track. If someone has any ideas about where to look, please let me know. [1] https://stripe.com/en-gb/sigma [2] https://github.com/apache/arrow-datafusion

Have fun with in-memory columnar databases or SQL engines and see how fast they are (the ones that use CPU-efficient data structures and data element access, plus SIMD processing). For example Apache Arrow datafusion [1] Edit: Also, run a cluster of anything (in VMs or containers) and muck around killing individual cluster nodes or just suspending them/throttling them to be extremely slow to simulate a brownout/straggling cluster node. [1] https://github.com/apache/arrow-datafusion