dmoura

Thank you for sharing your learnings and for your transparency! Congrats!

I prefer a SQL-like format. It’s not as complete but it cover most of the day-to-day use cases. Take a look at https://github.com/dcmoura/spyql (I am the author). Congrats on fq!

DuckDB is great! I love what you guys are building. The main gap for me is native support of JSON (lines), like you have for CSV and Parquet.

updated, thank you

Things you can do with SPyQL CLI that you can't with clickhouse local (AFAIK, top of my mind, not exhaustive):

- use python code in your queries

- import python libs (just install them with pip/conda)

- write your one UDFs in Python

- run OS commands from within the query (using os.system)

- have guaranty of row order (like in grep, sed, etc)

And there is more, please take a look at: https://spyql.readthedocs.io/en/latest/distinctive.html

Author of the benchmark and of SPyQL here. ClickHouse is fantastic. Amazing performance. SPyQL is built on top of Python but still can be faster than jq and several other tools as shown in the benchmark. SPyQL can handle large datasets but Clickhouse local should always show better performance.

SPyQL CLI is more oriented to work in harmony with the shell (piping), to be very simple to use and to leverage the Python ecosystem (you can import Python libs and use them in your queries).

This is great!

I am the author of SPyQL [1]. Combining JC with SPyQL you can easily query the json output and run python commands on top of it from the command-line :-) You can do aggregations and so forth in a much simpler and intuitive way than with jq.

I just wrote a blogpost [2] that illustrates it. It is more focused on CSV, but the commands would be the same if you were working with JSON.

[1] https://github.com/dcmoura/spyql [2] https://danielcmoura.com/blog/2022/spyql-cell-towers/

Thank you all for your feedback. The benchmark was updated and the fastest tool is NOT written in Python. Here are the highlights:

* Added ClickHouse (written in C++) to the benchmark: I was unaware that the clickhouse-local tool would handle these tasks. ClickHouse is now the fastest (together with OctoSQL);

* OctoSQL (written in Go) was updated as a response to the benchmark: updates included switching to fastjson, short-circuiting LIMIT, and eagerly printing when outputting JSON and CSV. Now, OctoSQL is one of the fastest and memory is stable;

* SPyQL (written in Python) is now third: SPyQL leverages orjson (Rust) to parse JSONs, while the query engine is written in Python. When processing 1GB of input data, SPyQL takes 4x-5x more time than the best, while still achieving up to 2x higher performance than jq (written in C);

* I removed Pandas from the benchmark and focused on command-line tools. I am planning a separate benchmark on Python libs where Pandas, Polars and Modin (and eventually others) will be included.

This benchmark is a living document. If you are interested in receiving updates, please subscribe to the following issue: https://github.com/dcmoura/spyql/issues/72

Thank you!

Are you able to calculate aggregates, like an average?

The initial idea was to focus on cmd line tools... I added pandas for comparison, as it is one of the most used libs to work with datasets. I will either remove Pandas from the equation or add Polars. By the way, I run some benchmarks and polars seems a bit faster than spyql for the aggregation challenge, but does not scale (loads everything into memory)

Yes, it is much faster, I will be updating the benchmark and reposting

Thank you!! :-D

Thank you Eaton! The truth is, if I was seeking to have the best performant tool I would not choose Python to start with... so performance is only a part of tools like the ones we are writing offer. Thank you for you tip, I will look into it ;-)

Thank you!!

Yes, I agree with you. I guess the title is a bit too provocative... still trying to understand what tools I might have missed, and this seemed a good way of doing that... I do not want to give wrong impressions to people that only read the title, so I might have been too impulsive when choosing the title...

Wow, I will check it out, thanks!!

Thanks @cube2222! Great! Sorry for overlooking that option. I will definitely add that option to octosql to make the comparison fair. Should I add it to the 3 queries?

Please take this claim and these results with a pinch of salt. spyql was not created with the goal of being the fastest tool for querying data, and it might be the case that the same tools with different datasets or in different use-cases outperform spyql. There might also be other tools that I was not aware when I wrote the benchmark (I just learned about a new one that we will be adding to the benchmark).

For me the lesson was that in certain problems (e.g. I/O intensive) the architecture/design might have a higher impact than the choice of the programming language.

spyql can both leverage the python standard lib for parsing json (written in C) as well as orjson (written in Rust). In this benchmark we used the later, which shows considerable performance improvements. Still, query processing (expression evaluation, filtering, aggregations, etc) are implemented in Python. I guess it's in the nature of Python to leverage internal/external modules written in a statically-typed compiled language to deliver high perfomance on core functionalities.

Here is a simple experiment with a 1GB file that shows that JSON decoding takes less than 40% of the processing time:

    !spyql "SELECT avg_agg(json->overall) FROM orjson" < books.json

    avg_agg_overall
    4.31181166791025 
    time: 11.7 s (started: 2022-04-13 23:37:07 +00:00)


    import orjson as json
    acc = 0 
    cnt = 0 
    with open('books.json') as f: 
      for line in f: 
        acc += json.loads(line)['overall'] 
        cnt += 1 
    print(acc/cnt)

    4.31181166791025
    time: 4.55 s (started: 2022-04-13 23:37:19 +00:00)

Loved the simplicity of your solution :-) Congrats!

Thank you for your feedback! I understand your point of view, let me share mine.

spyql is 100% Python code and it is not a thin layer over something else. Every row of data goes through a query engine built in python that takes care of evaluating the query, filtering and aggregating data, among other stuff. The only part that is offloaded to standard or external modules is the decoding and encoding from/to specific data formats. In the case of this benchmark, spyql uses the orjson module to convert each input json object into a python dict, one at a time.

Due to the nature of Python as an interpreted language, it is natural that python modules leverage C (or Rust) to provide highly efficient implementations of core functionalities. For instance, the json module of the standard library is implemented in C. If we would use the json module in the benchmark instead of orjson, spyql would remain as one of the fastest and lightest tools for querying json data. Using orjson, gives an extra boost of performance.

If you think it is worthwhile, I can add another benchmark entry where spyql uses the standard json lib. The queries would be exactly the same, I just need to use in the query `FROM json` instead of `FROM orjson`.

The idea was to focus on querying tools. ujson and orjson (as well as the json module from python's standard library) offer json decoding and decoding but not a querying language: you need to implement the query logic in Python, resulting in large programs with lots of boilerplate. Still, I agree that Pandas is an outlier... it was included due to its popularity for querying datasets.

I should mention that spyql leverages orjson, which has a considerable impact on performance. spyql supports both the json module from the standard library as well as orjson as json decoder/encoder. Performance wise, for 1GB of input data, orjson allows to decrease processing time by 20-30%. So, orjson is part of the reason why a python-based tool outperforms tools written in C, Go, etc and deserves credit.

:-D out of curiosity, jq, one of the tools in the benchmark, is written in C

yep, unfortunately due to security concerns is not supported everywhere (DB as a service): `ERROR: Extension "plpythonu" is not supported by Amazon RDS`

Demo available here: https://vimeo.com/danielcmoura/spyqldemo

:-) It’s an open-source tool, so it should be community-driven. If you can share an example I can try to understand if spyql can make your life easier.

Thank you :-) You can use https://github.com/mikefarah/yq to convert yaml to json on the fly and pipe it into spyql. There’s an example in the Readme.

Thank you! One of my main goals was making data processing in the command-line more accessible and intuitive. If you use a shell you can leverage an extensive array of tools. please take a look at the recipes in the Readme. The shell is many times underrated for data processing!

Right now you can use it in Jupiter Notebooks using a shell kernel like: https://github.com/takluyver/bash_kernel

On the mid-term, developing a spyql kernel is appealing because of syntax highlighting, code autocompleting, and more. But unless several people show interest on this, I should tackle other features first.

Nice!

It would perfectly fit my use-case if it supports the following flow:

1. Person A makes a research and collects and ranks a set of products

2. When done, person A sends to person B

3. Person B looks at the result and provides feedback, which might include dropping items or making questions, or asking to add a column assessing a given feature of the product

4. repeat until converging and then archive

Very cool!

Yes, column storage is great, specially when you only need a small subset of columns as there much less reading operations. I have experience working with Parquet files, so I can see the benefits of CTFs, even if we don't take into account summary metadata and so forth.

In its current version, SPyQL only reads from the stdin so it could only read a single column (a single file), unless you had an util merging the required columns beforehand (which could very well eliminate any performance gains of CTFs). I am planning to support reading from files/directories (as an argument) and in that case it's a matter of extending the processor class to deal with CTFs (column names = file names, etc). For taking full advantage of CTFs we would need to only open/read the required columns/files, but that seems very doable.

How often with CTFs would you have your column files (individually) compressed? What would be the most popular compression formats? Thanks!

BTW, nice R lib, I also love R :-)

Agree! There’s a lot of code you don’t see behind a simple SELECT statement. I would also say that when writing SQL you start thinking on the end result, describing what you want to get and not so much how you want it done. This is also part of the declarative nature of SQL.