4 ms·
Great to see SQL being used more for such use cases! (I really like SQL) This looks nice, especially with the charting! It's good to know though that there ar
by cube2222 5y ago
Great to see SQL being used more for such use cases! (I really like SQL)
This looks nice, especially with the charting!
It's good to know though that there are a few similar open source tools already:
- q[0] - SQL on CSV and TSV
- TextQL[1] - SQL on CSV and TSV
- Datasette[2] - Running SQL on various datasets in various formats, by (if I understand it correctly) importing it to sqlite and running the query there. This one's the closest one to the posted project I think, with interactive data exploration and publishing built in.
- OctoSQL[3] - SQL on files like CSV, JSON as well as non-file databases, endless streams with temporal SQL extensions and joining everything together in a single query.
The performance numbers of this tool however are impressive if they will check out with real data. 1GB in 10 seconds is fast. However, looking at the documentation it looks like most of the heavy lifting is done by SQLite under the hood. From the docs:
> Under the hood, Superintendent loads CSVs into a SQLite database.
Disclaimer: I'm the author of OctoSQL. Still actively developing a rewrite on the redesign branch.
[0]: https://github.com/harelba/q https://github.com/harelba/q
[1]: https://github.com/dinedal/textql https://github.com/dinedal/textql
[2]: https://datasette.io https://datasette.io
[3]: https://github.com/cube2222/octosql https://github.com/cube2222/octosql
- simonw 5y agoI've been looking at calling ".import" in SQLite directly as an optimization for my sqlite-utils tool - I'm seeing a big speed up in comparison to parsing the CSV in Python: https://github.com/simonw/sqlite-utils/issues/297 https://github.com/simonw/sqlite-utils/issues/297
- jolmg 5y agoShameless plug: https://github.com/jolmg/cq https://github.com/jolmg/cq
- gigatexal 5y agoImpressive work and clear readme with good usage examples. Kudos
- tanin 5y agoHi! I'm the OP here. The performance gain is mainly from reading/writing files in C and/or by Sqlite (which is also C). Any other tool that use dynamic languages like python will immediately takes minutes to handle any GB-sized file (initializing string is already slow). The main difference is essentially GUI vs command-line. Datasette is the closest but it seems to require command line (based on the usage page). I haven't tried it out yet, so I may be wrong here. OctoSQL looks very interesting. Initially, I was trying to use DuckDB but couldn't make it work on Windows. Sqlite's SQL dialect is somewhat lacking (hello WITH RECURSIVE). It is one of the gaps I would like to solve.
- legg0myegg0 5y agoOdd that DuckDB didn't work for you on Windows! I only use it on Windows and love it!
- tanin 5y agoMy use case is a bit more complex. I have to compile it with an electron app and etc. I filed a few GitHub issues with them as well. This has great potential. The dialect is much more comprehensive than the Sqlite one.