5 ms·

Since many people are sharing one-liners with various tools... OctoSQL[0]: octosql 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP

Since many people are sharing one-liners with various tools... OctoSQL[0]: octosql 'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi.csv GROUP BY passenger_count' It also infers everything automatically and typechecks your query for errors. You can use it with csv, json, parquet but also Postgres, MySQL, etc. All in a single query! [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql Disclaimer: author of OctoSQL

elmomle 4y ago
I love the simplicity. Is there support for joins / use of multiple tables?
- cube2222 4y ago
  Yes, certainly! And those multiple tables can come from different data sources, so files or databases.
eatonphil 4y ago
Heads up: cube2222 is the original author of this benchmark. :) I copied it and Simon copied my copy of it.
- cube2222 4y ago
  Thanks for the acknowledgement! Though btw., I think I personally prefer the SPyQL benchmarks[0], as they test a bigger variety of scenarios. This benchmark is mostly testing CSV decoding speed - because the group by is very simple, with just a few keys in the grouping. [0]:https://colab.research.google.com/github/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb https://colab.research.google.com/github/dcmoura/spyql/blob/...
  - eatonphil 4y ago
    Actually I have a big issue with that benchmark in that it doesn't ORDER BY. I don't believe all those tools will produce the same result and it's not required by SQL for them to do so. That doesn't change the poor performance of dsq but it does change the relative and absolute scores in that benchmark.
aargh_aargh 4y ago
Hi cube2222! Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations. Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". It took me a while to figure out how to quote CSV column names with non-ASCII characters and spaces. It's not documented as far as I've seen (please document quoting rules). This worked: octosql 'SELECT `tablename.Mötley Crüe` FROM tablename.csv' replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text) (postgresql equivalent: position ( substring text IN string text ) → integer) octosql "SELECT index('y', 'Mötley Crüe')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' octosql "SELECT index('Mötley Crüe', 'y')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' Hope this helps and I wish you all the best. [1] https://github.com/cube2222/octosql/wiki/Function-Documentation#replace https://github.com/cube2222/octosql/wiki/Function-Documentat...
- cube2222 4y ago
  Hey! Thanks a lot for this writeup! > but I immediately ran into many rough edges OctoSQL is definitely not in a stable state yet, so depending on the use case, there definitely are rough edges and occasional regressions. > Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". Indeed, right now only inner join is implemented. The others should be available soon. > replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text) I've removed the offending docs pages, they were documenting a very old version of OctoSQL. The way to browse the available functions right now is built-in to OctoSQL: octosql "SELECT * FROM docs.functions" > invalid argument syntax error at position 13 near 'index' Looks like a parser issue which I can indeed replicate, will look into it. Thanks again, cheers!
  - cube2222 4y ago
    Just to update this response, I've just released a new version that contains a new `position` function, as well as the capability to execute SELECT statements without a FROM part. So i.e. the above octosql "SELECT position('hello', 'ello')"

elmomle 4y ago

I love the simplicity. Is there support for joins / use of multiple tables?

cube2222 4y ago
Yes, certainly! And those multiple tables can come from different data sources, so files or databases.

eatonphil 4y ago

Heads up: cube2222 is the original author of this benchmark. :) I copied it and Simon copied my copy of it.

cube2222 4y ago
Thanks for the acknowledgement! Though btw., I think I personally prefer the SPyQL benchmarks[0], as they test a bigger variety of scenarios. This benchmark is mostly testing CSV decoding speed - because the group by is very simple, with just a few keys in the grouping. [0]:https://colab.research.google.com/github/dcmoura/spyql/blob/master/notebooks/json_benchmark.ipynb https://colab.research.google.com/github/dcmoura/spyql/blob/...
- eatonphil 4y ago
  Actually I have a big issue with that benchmark in that it doesn't ORDER BY. I don't believe all those tools will produce the same result and it's not required by SQL for them to do so. That doesn't change the poor performance of dsq but it does change the relative and absolute scores in that benchmark.

aargh_aargh 4y ago

Hi cube2222! Just today I tried octosql for the first time when I wanted to correlate a handful of CSV files based on identifiers present in roughly equal form. Great idea but I immediately ran into many rough edges in what I think was a simple use case. Here are my random observations. Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". It took me a while to figure out how to quote CSV column names with non-ASCII characters and spaces. It's not documented as far as I've seen (please document quoting rules). This worked: octosql 'SELECT `tablename.Mötley Crüe` FROM tablename.csv' replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text) (postgresql equivalent: position ( substring text IN string text ) → integer) octosql "SELECT index('y', 'Mötley Crüe')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' octosql "SELECT index('Mötley Crüe', 'y')" Error: couldn't parse query: invalid argument syntax error at position 13 near 'index' Hope this helps and I wish you all the best. [1] https://github.com/cube2222/octosql/wiki/Function-Documentation#replace https://github.com/cube2222/octosql/wiki/Function-Documentat...

cube2222 4y ago
Hey! Thanks a lot for this writeup! > but I immediately ran into many rough edges OctoSQL is definitely not in a stable state yet, so depending on the use case, there definitely are rough edges and occasional regressions. > Missing FULL JOIN (this was a dealbreaker for me). LEFT/RIGHT join gave me "panic: implement me". Indeed, right now only inner join is implemented. The others should be available soon. > replace() is documented [1] as replace(old, new, text) but actually is replace(text, old, new) just like in postgres and mysql. index() is documented [1] as index(substring, text) I've removed the offending docs pages, they were documenting a very old version of OctoSQL. The way to browse the available functions right now is built-in to OctoSQL: octosql "SELECT * FROM docs.functions" > invalid argument syntax error at position 13 near 'index' Looks like a parser issue which I can indeed replicate, will look into it. Thanks again, cheers!
- cube2222 4y ago
  Just to update this response, I've just released a new version that contains a new `position` function, as well as the capability to execute SELECT statements without a FROM part. So i.e. the above octosql "SELECT position('hello', 'ello')"