Dsq: Commandline tool for running SQL queries against JSON, CSV, Parquet, etc.
datastation.multiprocess.io
datastation.multiprocess.io
The repo is here [1] and the README has a comparison against some of the other great tools in the space like q, octosql, and textql.
My version of this is `sqlite-utils memory`, but it only handles JSON, newline-delimited JSON and CSV/TSV at the moment: https://simonwillison.net/2021/Jun/19/sqlite-utils-memory/
However, octosql's GH repo claims otherwise.
Does anyone have any real world experience that they can share on these tools?
The biggest risk (both in terms of SQL compliance and performance) I think with octosql (and cube2222 is here somewhere to disagree with me if I'm wrong) is that they have their own entire SQL engine whereas textql, q and dsq use SQLite. But q is also in Python whereas textql, octosql, and dsq are in Go.
In the next few weeks I'll be posting some benchmarks that I hope are a little fairer (or at least well-documented and reproducible). Though of course it would be appropriate to have independent benchmarks too since I now have a dog in the fight.
On a tangent, once the go-duckdb binding [0] matures I'd love to offer duckdb as an alternative engine flag within dsq (and DataStation). Would be neat to see.
I've released OctoSQL v0.4.0 recently[0] which is a grand rewrite and is 2-3 orders of magnitude faster than OctoSQL was before. It's also much more robust overall with static typing and the plugin system for easy extension. The q benchmarks are over a year old and haven't been updated to reflect that yet.
Take a look at the README[1] for more details.
My benchmarks should be reproducible, you can find the script in the benchmarks/ repo directory.
Btw if we're already talking @eatonphil I'd appreciate you updating your benchmarks to reflect these changes.
As far as the custom query engine goes - yes, there are both pros and cons to that. In the case of OctoSQL I'm building something that's much more dynamic - a full-blown dataflow engine - and can subscribe to multiple datasources to dynamically update queries as source data changes. This also means it can support streaming datasources. That is not possible with the other systems. It also means I don't have to load everything into a SQLite database before querying - I can optimize which columns I need to even read.
OctoSQL also let's you work with actual databases in your queries - like PostgreSQL or MySQL - and pushes predicates down to them, so it doesn't have to dump your whole database tables. That's useful if you need to do cross-db joins, or JSON-file-with-database joins.
As far as SQL compliance goes it gets hairy in the details - as usual. The overall dialect is based on MySQL as I'm using a parser based on vitess's one, but I don't support some syntax, and add original syntax too (type assertions, temporal extensions, object and type notation).
Stay tuned for a lot more supported datasources, as the plugin system lets me work on that much quicker.
https://altinity.com/blog/2019/6/11/clickhouse-local-the-pow...
The big issue with ClickHouse is the incredibly non-standard SQL dialect and the random bugs that remain. It's an amazing project for analytics but you definitely have to be willing to hack around its SQL language (I say this as a massive fan of ClickHouse).
I wonder: does this mean I can embed ClickHouse in arbitrary software as a library? I'd be curious to provide that as an option in dsq.
Not sure, but it's Apache licensed, so you likely can make it work if you want to. But realize that clickhouse(-local) is much heavier than sqlite / duckdb based solutions: the compiled binary is around 200mb iirc.
https://github.com/dinedal/textql/
Also SQLite itself can query CSV files IIRC.
dsq is a totally standalone binary.
It seems a few people here also built a similar app...
Imagine when you don't have to dump/convert data because you can always open it with OpenOffice.
Column 1 is the creation timestamp
Column 2 is the modification timestamp
On read, you update if the creation timestamp exists but the modification timestamp is later, otherwise you insert. Your app(s) can do a simple file append for writes. You even have the full version history at every point in time.
I did a lot of looking but didn't find a command-line tool that automated this process. It works fine for small projects of e.g. 100,000 records. Wouldn't work well for things like a notes app, because you'd be storing every modification as a new entry.
Personally I use sqlite for smaller datasets and wrap that around a CSV importer.
S3 is cloud storage on AWS. Athena can work directly off the CSVs stored on S3.
Where I said “dump” it was just a colourful way of saying “transfer your files to…”. I appreciate “dump” can also mean different things with databases so maybe that wasn’t the best choice of word in my part. Sorry for any confusion there.
CSV is so horribly non-standardized and horrible to parse. JSON appears a much more suitable candidate
[1] https://www.gnu.org/software/recutils/manual/recutils.html#I... [2] https://www.visidata.org/
Bonus: A nice concise intro that someone wrote last week: https://chrismanbrown.gitlab.io/28.html
Wonder how, say, jq would look like working with tables in RDBMS.