3 ms·

Nice work! I have a similar tool built on SQLite [0] and there are a number of similar tools (compared in my readme) like octosql, q, textql, datasette, etc. I

Nice work! I have a similar tool built on SQLite [0] and there are a number of similar tools (compared in my readme) like octosql, q, textql, datasette, etc. Implementation-wise, is there a reason you wrote your own SQL parser rather than using an existing library? I ask not because I'd discourage handwritten SQL parsers but that I think the particular approach you took is going to suffer when it comes to error messages and lexical edge cases like `SELECT(SELECT 1)`. Not to mention that it introduces a completely new SQL dialect users would have to learn. (I think even SQLite is a bit risky since most people know MySQL or PostgreSQL dialects.) But I can appreciate you wanting your own syntax especially for nested objects. In dsq I solved that by giving nested fields a literal column name with a dot in it. But that means you have to quote the column name (e.g. `SELECT "city.address" FROM {}`). The additional penalty you'd pay not backing this by SQLite or DuckDB or some other in-memory database is in query performance as the input grows. Among other existing tools like this octosql has its own query engine but cube2222 has put a lot of time into optimizing it and it's also written in Go. Whatever the case, competition is good! Happy to chat more if you want. I love database projects! [0] https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq

dmoura 5y ago
Thank you! I know DSQ, great work! Unlike q and others it manages JSONs, which is great. Yes, there are a number of tools that use an in-memory DB. I work with JSONs and CSVs with several GBs, and loading them to sqlite is not an option, it is too slow and it uses too much memory. Sometimes I just need an average or a sum of a coupple of columns (eventually grouped by another column) and there is no need to load the entire dataset into memory. Also, this way you can tackle streaming data. I have compared performance with q for CSVs and spyql is several times faster. I have compared with jq for JSONs and performance is head to head, with spyql typically requiring less memory. I will be presenting SPyQL at FOSDEM22 where I will show performance comparisons against awk, jq, pandas and "pure" python implementations from scratch (https://fosdem.org/2022/schedule/event/python_spyql/ https://fosdem.org/2022/schedule/event/python_spyql/). I can put some numbers here if you are interested. Regarding the parser. First, I am not properly proud of the parser of spyql, it's a mean to an end. I started with a standard SQL parser but it was becoming too difficult conciliating with the python syntax. I could follow an AST approach but due to lack of experience I was unable to estimate the effort and eventual hurdles. I also want spyql to be compatible with different python versions that can have different ASTs. So I went with a basic approach based on regex and alike. It's definitely an area for improvement and if anyone finds this challenge interesting then please hop in!! I had to avoid collisions with already existing functions of python... `sum` in Python sums lists/iterators, while sum in SQL is an aggregation function. I am adding the `_agg` suffix to aggregations to avoid collisions, but I confess that I forget to add them many times when writing my own queries... I have included a PARTIALS modifier that simplifies window/analytical functions based on the premise that the window is defined by the order of the input. It makes so much easier writing running sums, differences between consecutive rows, etc. This is also not standard in SQL. It is also super useful for stats on streaming data. There's a lot of space for debate... I am super happy to hear feedback from you! Great constructive feedback, would love to chat more :-) Thanks

dmoura 5y ago

Thank you! I know DSQ, great work! Unlike q and others it manages JSONs, which is great. Yes, there are a number of tools that use an in-memory DB. I work with JSONs and CSVs with several GBs, and loading them to sqlite is not an option, it is too slow and it uses too much memory. Sometimes I just need an average or a sum of a coupple of columns (eventually grouped by another column) and there is no need to load the entire dataset into memory. Also, this way you can tackle streaming data. I have compared performance with q for CSVs and spyql is several times faster. I have compared with jq for JSONs and performance is head to head, with spyql typically requiring less memory. I will be presenting SPyQL at FOSDEM22 where I will show performance comparisons against awk, jq, pandas and "pure" python implementations from scratch (https://fosdem.org/2022/schedule/event/python_spyql/ https://fosdem.org/2022/schedule/event/python_spyql/). I can put some numbers here if you are interested. Regarding the parser. First, I am not properly proud of the parser of spyql, it's a mean to an end. I started with a standard SQL parser but it was becoming too difficult conciliating with the python syntax. I could follow an AST approach but due to lack of experience I was unable to estimate the effort and eventual hurdles. I also want spyql to be compatible with different python versions that can have different ASTs. So I went with a basic approach based on regex and alike. It's definitely an area for improvement and if anyone finds this challenge interesting then please hop in!! I had to avoid collisions with already existing functions of python... `sum` in Python sums lists/iterators, while sum in SQL is an aggregation function. I am adding the `_agg` suffix to aggregations to avoid collisions, but I confess that I forget to add them many times when writing my own queries... I have included a PARTIALS modifier that simplifies window/analytical functions based on the premise that the window is defined by the order of the input. It makes so much easier writing running sums, differences between consecutive rows, etc. This is also not standard in SQL. It is also super useful for stats on streaming data. There's a lot of space for debate... I am super happy to hear feedback from you! Great constructive feedback, would love to chat more :-) Thanks