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):

- pipe data into (e.g. from another CLI util)

- 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`.