23 ms·

The fastest tool for querying large JSON files is written in Python (benchmark)

175 points by dmoura 5y ago

benibela 4y ago
You could also try my Xidel. With the dev build 0.9.9, not the last release 0.9.8, because recently I have implemented my own hash map and JSON parser to make it faster than before. Although it is not fast nor supports large files (it loads everything in memory)
procrastinatus 4y ago
Daniel Lemire’s simdjson probably belongs in this discussion and I would be surprised if it is not the fastest tool by some margin. https://github.com/simdjson/simdjson https://github.com/simdjson/simdjson
- dmoura 4y ago
  Wow, I will check it out, thanks!!
- peterohler 4y ago
  For some specific cases simdjson might be the fastest but it does fare as well in other cases as seen here: https://github.com/ohler55/compare-go-json https://github.com/ohler55/compare-go-json or here: https://github.com/ohler55/ojc/blob/master/misc/compete.md https://github.com/ohler55/ojc/blob/master/misc/compete.md. Parsing the JSON file is only part of the solution though. There needs to be an efficient and easy way to extract the data of interest. OjG (https://github.com/ohler55/ojg https://github.com/ohler55/ojg) has a JSONPath implementation for that. Written in go is a high performer.
Dowwie 4y ago
Actually, it's written in Rust and wrapped by python.
orlp 4y ago
Allow me a moment to hijack this thread. I'm a PhD student looking to make analytics of JSON data a first-class citizen in https://duckdb.org/ https://duckdb.org/. I'm still very much in a literature study stage, so I'd love to hear your success (or failure) stories about analyzing data stored in JSON. E.g. I'd love to hear about: * Which dialect for querying worked the best for you? * Which tools do you enjoy the most? This could be for any reason: convenience of install/availability, expressivity, raw speed, other niche features, etc. * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you. * What do you feel is the biggest gap/missing feature in currently available tools?
- sandGorgon 4y ago
  Store the json in postgresql as JSONB. Not trying to flame - I do realise that duckdb is an alternative, but everyone in this space does this Especially for data scientists, etc. The tooling flow around dbt, pandas, jupyter, postgresql and json is rock solid.
  - PaulHoule 4y ago
    arangodb for the win. I've done data analytics in it and OLTP. You never hear hype about arangodb because arangodb users see it as a "secret weapon" and want their competitors to use mongo.
    - The_rationalist 4y ago
  - boredumb 4y ago
    I can second that using JSONB with postgres is fast and easy, the features and operators to query json structures are also about as extensive as you'll ever need.
chewbacha 4y ago
Not sure the HN title is that great. The actual title is “ The fastest tools for querying large JSON datasets” and the bit about it being written in python is editorialized. The analysis includes products written in go, python, and Java but doesn’t appear to care about the implementation language at all as you must click through to the GitHub to see the language. I’m also not confident that the list was exhaustive.
- dmoura 4y ago
  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...
PaulHoule 4y ago
That SpyQL is pretty cute... I like how it imports Python modules directly. It's a lot like list comprehensions on steroids but it does lack the high end features of SQL like joins and it has a simple query processing strategy that doesn't support query optimization like SQL. It is convenient to incorporate the Python ecosystem (functions) but in the end it is not that strong because the query language doesn't understand Python. It reminds me of the Java Streams in the API which can represent all the blocks of the processing pipeline before you collect it, yet it doesn't buy very much because the map, filter, group, etc. functions are black boxes that it can't think globally about. The Expression Tree version of LINQ in C# can do better (like compile what looks like a C# function to SQL) but it's disappointing that very few people really did anything with expression trees. (Give programmers the power of LISP and they say... Meh.)
PaulHoule 4y ago
Tools like https://www.alteryx.com/ https://www.alteryx.com/ are really limited in my view because they are based on a relational model that requires joins to do things that are easily expressed without joins in the object-relational (basically JSON) model. "Boxes and lines" data pipelines that involve joins are a bear to maintain because of the structural instability: what looks like a little change to management can turn a very simple pipeline that goes in one direction to a pipeline that splits into four streams that have to get reassembled at the end. If you were passing JSON documents between the nodes you can keep the stuff that would have been split out and then joined inside the document and the pipelines are a lot easier to maintain. The people who develop those tools, however, are in love with the relational model because it is SIMDable, not branchy, easy to implement at hyper-speed so there is going to be an opportunity for people to make one that gets much better customer satisfaction. To do that though somebody has to ask the question of "what algebra does this thing work over?"
flurly 4y ago
I personally tend to put all my data for analysis into clickhouse (which is written in C++ FWIW). My side project actually is all about ingesting JSON data into clickhouse and making it super easy to query, visualize, monitor and alert on JSON data. You certainly pay a computation cost everytime you want to scan the data, but I think the flexibility more than makes up for it. You can take a read about how and why I built GraphJSON here if you're interested https://www.graphjson.com/guides/about https://www.graphjson.com/guides/about
- qoega 4y ago
  Have you tried ClickHouse for dataset above? Interesting if it is faster or not
  - zX41ZdbW 4y ago
    It is appeared that clickhouse-local is faster than all of the rest, with a huge margin: https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSSiRgxQy9bcinGZ?usp=sharing https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSS...
    - dmoura 4y ago
      Yes, it is much faster, I will be updating the benchmark and reposting
- mritchie712 4y ago
  This is awesome. Assuming you saw that 22.3 has a JSON column type? https://clickhouse.com/blog/clickhouse-22-3-lts-released/ https://clickhouse.com/blog/clickhouse-22-3-lts-released/ ps - we're built on Clickhouse at Luabase (https://luabase.com/ https://luabase.com/) and hiring across all role, send me a note (mike@luabase.com) if you're into web3+analytics.
bob1029 4y ago
Potentially relevant here... https://lemire.me/blog/2018/05/03/how-fast-can-you-parse-json/ https://lemire.me/blog/2018/05/03/how-fast-can-you-parse-jso... http://www.vldb.org/pvldb/vol10/p1118-li.pdf http://www.vldb.org/pvldb/vol10/p1118-li.pdf "We experimentally evaluate Mison using representative real-world JSON datasets and the TPC-H benchmark, and show that Mison produces significant performance benefits over the best existing JSON parsers; in some cases, the performance improvement is over one order of magnitude."
irrational 4y ago
Just this week I has to query a JSON file that is 13-14 MB in size. I already had Node installed, so that it what I used. I was surprised that it was able to do the processing so fast that it was done as soon as I hit the enter key in the terminal window. And the processing wasn't trivial, it had to touch every single element of the entire JSON file and put the entire thing into a new data structure. I was quite impressed at how fast it was. It took my IDE about 5 minutes just to open the JSON file so I could take a look at it to see how it was structured.
- jamal-kumar 4y ago
  Sounds like your IDE is a bit bloated. Not to tell you to stop using it or anything, that's a tall order - but next time you have a sufficiently large file (15mb shouldn't choke an editor?), try head/tail/less/more and you'll save those 5 minutes too
gourneau 4y ago
TIL that time can also output max memory use, and IO use very useful! Like they do here: /usr/bin/time -o performance.log -a -f"%e\t%M\t%x\t%C" %e (Not in tcsh.) Elapsed real time (in seconds). %M Maximum resident set size of the process during its lifetime, in Kbytes. %x (Not in tcsh.) Exit status of the command. %C (Not in tcsh.) Name and command-line arguments of the command being timed.
pmdulaney 5y ago
Cool! Let's port it to C and make it really fast!
- dmoura 5y ago
  :-D out of curiosity, jq, one of the tools in the benchmark, is written in C
davidatbu 5y ago
I asked about this on the Github issue regarding these benchmarks as well. I'm curious as to why libraries like ultrajson[0] and orjson[1] weren't explored. They aren't command line tools, but neither is pandas (which is benchmarked) right? Is it perhaps because the code required to implement the challenges is large enough that they are considered too inconvenient to use through the same way pandas was used (ie, `python -c "..."`)? [0] https://github.com/ultrajson/ultrajson https://github.com/ultrajson/ultrajson [1] https://github.com/ijl/orjson https://github.com/ijl/orjson
- dmoura 5y ago
  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.
  - jammycrisp 4y ago
    > I should mention that spyql leverages orjson, which has a considerable impact on performance Even with orjson, you're still paying the cost of creating a new PyObject for every node in the JSON blob. orjson is well engineered (as is the backing serde-json decoder), but any JSON decoder that isn't using naive algorithms is mostly bound by the cost of creating PyObjects. Allocating in Python is _slow_. I wrote a quick benchmark (https://gist.github.com/jcrist/de29815389eaed4eaf5b24fbcfdab5f0 https://gist.github.com/jcrist/de29815389eaed4eaf5b24fbcfdab...) showing a handwritten query that accesses only a few fields in a 13 MiB JSON file. The same query is repeated with a number of different Python JSON libraries. Results: $ python bench_repodata_query.py msgspec: 45.018014032393694 ms simdjson: 61.94157397840172 ms orjson: 105.34720402210951 ms ujson: 121.9699690118432 ms json: 113.79130696877837 ms While `orjson`, is faster than `ujson`/`json` here, it's only ~6% faster (in this benchmark). `simdjson` and `msgspec` (my library, see https://jcristharif.com/msgspec/ https://jcristharif.com/msgspec/) are much faster due to them avoiding creating PyObjects for fields that are never used. If spyql's query engine can determine the fields it will access statically before processing, you might find using `msgspec` for JSON gives a nice speedup (it'll also type check the JSON if you know the type of each field). If this information isn't known though, you may find using `pysimdjson` (https://pysimdjson.tkte.ch/ https://pysimdjson.tkte.ch/) gives an easy speed boost, as it should be more of a drop-in for `orjson`.
gabrielsroka 4y ago
Focusing on ease of use instead of performance (I'm usually looking at small files) but I found jq's syntax to be really difficult, so I wrote my own teeny tiny tool in Python: https://replit.com/@gabrielsroka/Bash#pj.py https://replit.com/@gabrielsroka/Bash#pj.py Obviously it only does a fraction of what jq does, but it does what I need and I can expand it in a fraction of the time it would take to learn jq. Inspired by https://docs.python.org/3/library/json.html#module-json.tool https://docs.python.org/3/library/json.html#module-json.tool
- dmoura 4y ago
  Loved the simplicity of your solution :-) Congrats!
- soheilpro 4y ago
  My main problem with jq was finding the paths to the nodes, so I wrote catj (https://github.com/soheilpro/catj https://github.com/soheilpro/catj) to help with that.
benob 4y ago
orjson, the fast underlying json parser, is written in rust.
- shepardrtc 4y ago
  As soon as I saw Python, I knew they were using orjson. The title of this post is incorrect. It should simply be the title of the Colab notebook, "The fastest tools for querying large JSON datasets". While SPyQL is the fastest tool, it's not written entirely in Python.
fbwaters 4y ago
So for large files the bottleneck is encoding/decoding, which is done by orjson and not Python. If you wrap orjson in C++, the results would be even faster.
1vuio0pswjnm7 4y ago
I think this title says more about JSON than it does about Python.
dmoura 4y ago
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)
- Olreich 4y ago
  simdjson can load the json into memory in a queryable form in ~1/3 of a second. So you can save yourself basically 40% of the runtime right there. Computing average should take less than 1/2 a second on modern hardware (assumes <10 million books). So back-of-envelope target speed should be less than 1 second for this benchmark. 11.7s puts you at one order of magnitude off, which could be a fair price to pay if you never need this for large datasets (100s of GB or TB of data you want to query). And the reason we use wrapped libraries in Python so often is because it’s abysmally slow to do anything in the interpreter. The average loop is 100x slower than it should be. The more math you do the worse it gets too. Most pure Python code is 1000x slower than it should be.
  - iknownothow 4y ago
cube2222 4y ago
Hey, OctoSQL author here. Great to see such benchmarks and another dataset to test with! Just wanted to add a small explanation for the interested to why at least OctoSQL[0] falls behind in the first and last test. OctoSQL supports retractions. That means an input for OctoSQL can not only be producing records, but also saying "I'm taking this record back", and OctoSQL will calculate the correct output based on that with minimal processing overhead required. It's a dataflow system in that sense. To achieve that, it has to buffer data at the output to be able to display the current output state (which might have rows removed from it). It’s the same case for LIMIT which is right now very unoptimal in OctoSQL. Because the top 100 rows can actually change if you have an ORDER BY clause specified and new rows/retractions arrive - it buffers the whole output, and then displays the top 100 rows. Running with the stream_native output format should drastically reduce the memory overhead and possibly processing time (I’ll post an update when the data is downloaded and I can compare), at least in the first case. Obviously, OctoSQL could be much smarter about detecting whether a source can even produce retractions, and optimize it’s behavior based on that. No point in buffering if retractions are not possible (which is the case for i.e. a JSON file). This would make LIMIT and the output table much faster and lighter. It’s definitely a big priority for me for future development to fix this (and make LIMIT and the table output format usable with big outputs), so stay tuned! :) And congrats to SPyQL for the first place here, its design is awesome. EDIT: Running the first test with `--output stream_native` (which just sends the productions and retractions as they come to the output, instead of buffering and sorting them) does indeed run 2-3x faster and use 50 MB RAM - instead of GB's. [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql
- dmoura 4y ago
  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?
  - cube2222 4y ago
    No worries! I think it'll be most fair if I just let you know when these are fixed, and you can properly use them, performantly, with a sane output option (that's not stream native, but something like JSON). I.e. LIMIT won't work with stream_output. If you want to add it to the first one, I won't oppose, but don't feel pressured to do it. Especially the third query does highlight a very realistict limitation of OctoSQL as it works right now, so it's only fair as it is.
wdroz 4y ago
Why they didn't compare with Polars [0]? According to the h2oai benchmark [1], Polars is fast. [0] -- https://www.pola.rs/ https://www.pola.rs/ [1] -- https://h2oai.github.io/db-benchmark/ https://h2oai.github.io/db-benchmark/
- Tarq0n 4y ago
  Polars is for tabular data, a different kind of task.
  - wdroz 4y ago
    But you can read json from polars (or from pandas first then polars) then you can do the map/reduce/filter like in the benchmark.
- dmoura 4y ago
  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)
eatonphil 4y ago
Thanks for including dsq! Its focus is always going to be flexibility and UX over performance since it's just a thin wrapper over the internals of a GUI data integration tool [0]. For example many other tools operate on only one or two file types and have optimized for querying on those types. Some like octosql and spyql IIRC have implemented their own sql parsing layer and engine. So if full sql support is necessary you may not be able to use them. In contrast dsq supports excel, avro, parquet, json, csv/tsv, nginx logs, apache logs, json newline, concatenated json, etc. And this list keeps growing. Dsq stores data in sqlite under the hood so you have access to the full sqlite dialect. Textql and q also do it this way. That said, despite performance not being the major I goal I think it will get better over time. There's already an OSS contributor who happens to be thinking about perf. If you're interested in getting started with OSS contributions btw I keep a page of good first projects [0]. If you know Go you're in a good place to contribute. I've already had a number of people take me up on this and merged a number of PRs by first-time OSS contributors. [0] https://github.com/multiprocessio/datastation https://github.com/multiprocessio/datastation [1] https://github.com/multiprocessio/datastation/blob/main/GOOD_FIRST_PROJECTS.md https://github.com/multiprocessio/datastation/blob/main/GOOD...
- dmoura 4y ago
  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 ;-)
dmoura 4y ago
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 https://github.com/dcmoura/spyql/issues/72 Thank you!

175 points by dmoura 5y ago

You could also try my Xidel. With the dev build 0.9.9, not the last release 0.9.8, because recently I have implemented my own hash map and JSON parser to make it faster than before. Although it is not fast nor supports large files (it loads everything in memory)

procrastinatus 4y ago

Daniel Lemire’s simdjson probably belongs in this discussion and I would be surprised if it is not the fastest tool by some margin. https://github.com/simdjson/simdjson https://github.com/simdjson/simdjson

dmoura 4y ago
Wow, I will check it out, thanks!!
peterohler 4y ago
For some specific cases simdjson might be the fastest but it does fare as well in other cases as seen here: https://github.com/ohler55/compare-go-json https://github.com/ohler55/compare-go-json or here: https://github.com/ohler55/ojc/blob/master/misc/compete.md https://github.com/ohler55/ojc/blob/master/misc/compete.md. Parsing the JSON file is only part of the solution though. There needs to be an efficient and easy way to extract the data of interest. OjG (https://github.com/ohler55/ojg https://github.com/ohler55/ojg) has a JSONPath implementation for that. Written in go is a high performer.

Dowwie 4y ago

Actually, it's written in Rust and wrapped by python.

orlp 4y ago

Allow me a moment to hijack this thread. I'm a PhD student looking to make analytics of JSON data a first-class citizen in https://duckdb.org/ https://duckdb.org/. I'm still very much in a literature study stage, so I'd love to hear your success (or failure) stories about analyzing data stored in JSON. E.g. I'd love to hear about: * Which dialect for querying worked the best for you? * Which tools do you enjoy the most? This could be for any reason: convenience of install/availability, expressivity, raw speed, other niche features, etc. * What sort of JSON "meta-formats" are the most important/common for you? E.g. in a file you could have object-per-line, object-of-arrays, array-of-objects, or in an SQL context you could have object-per-row or object-of-arrays-as-table, etc). I'd love to hear about others that are important to you. * What do you feel is the biggest gap/missing feature in currently available tools?

sandGorgon 4y ago
Store the json in postgresql as JSONB. Not trying to flame - I do realise that duckdb is an alternative, but everyone in this space does this Especially for data scientists, etc. The tooling flow around dbt, pandas, jupyter, postgresql and json is rock solid.
- PaulHoule 4y ago
  arangodb for the win. I've done data analytics in it and OLTP. You never hear hype about arangodb because arangodb users see it as a "secret weapon" and want their competitors to use mongo.
  - The_rationalist 4y ago
- boredumb 4y ago
  I can second that using JSONB with postgres is fast and easy, the features and operators to query json structures are also about as extensive as you'll ever need.

chewbacha 4y ago

Not sure the HN title is that great. The actual title is “ The fastest tools for querying large JSON datasets” and the bit about it being written in python is editorialized. The analysis includes products written in go, python, and Java but doesn’t appear to care about the implementation language at all as you must click through to the GitHub to see the language. I’m also not confident that the list was exhaustive.

dmoura 4y ago
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...

PaulHoule 4y ago

That SpyQL is pretty cute... I like how it imports Python modules directly. It's a lot like list comprehensions on steroids but it does lack the high end features of SQL like joins and it has a simple query processing strategy that doesn't support query optimization like SQL. It is convenient to incorporate the Python ecosystem (functions) but in the end it is not that strong because the query language doesn't understand Python. It reminds me of the Java Streams in the API which can represent all the blocks of the processing pipeline before you collect it, yet it doesn't buy very much because the map, filter, group, etc. functions are black boxes that it can't think globally about. The Expression Tree version of LINQ in C# can do better (like compile what looks like a C# function to SQL) but it's disappointing that very few people really did anything with expression trees. (Give programmers the power of LISP and they say... Meh.)

PaulHoule 4y ago

Tools like https://www.alteryx.com/ https://www.alteryx.com/ are really limited in my view because they are based on a relational model that requires joins to do things that are easily expressed without joins in the object-relational (basically JSON) model. "Boxes and lines" data pipelines that involve joins are a bear to maintain because of the structural instability: what looks like a little change to management can turn a very simple pipeline that goes in one direction to a pipeline that splits into four streams that have to get reassembled at the end. If you were passing JSON documents between the nodes you can keep the stuff that would have been split out and then joined inside the document and the pipelines are a lot easier to maintain. The people who develop those tools, however, are in love with the relational model because it is SIMDable, not branchy, easy to implement at hyper-speed so there is going to be an opportunity for people to make one that gets much better customer satisfaction. To do that though somebody has to ask the question of "what algebra does this thing work over?"

flurly 4y ago

I personally tend to put all my data for analysis into clickhouse (which is written in C++ FWIW). My side project actually is all about ingesting JSON data into clickhouse and making it super easy to query, visualize, monitor and alert on JSON data. You certainly pay a computation cost everytime you want to scan the data, but I think the flexibility more than makes up for it. You can take a read about how and why I built GraphJSON here if you're interested https://www.graphjson.com/guides/about https://www.graphjson.com/guides/about

qoega 4y ago
Have you tried ClickHouse for dataset above? Interesting if it is faster or not
- zX41ZdbW 4y ago
  It is appeared that clickhouse-local is faster than all of the rest, with a huge margin: https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSSiRgxQy9bcinGZ?usp=sharing https://colab.research.google.com/drive/1pGmfpNdGyzE1KEXDnSS...
  - dmoura 4y ago
    Yes, it is much faster, I will be updating the benchmark and reposting
mritchie712 4y ago
This is awesome. Assuming you saw that 22.3 has a JSON column type? https://clickhouse.com/blog/clickhouse-22-3-lts-released/ https://clickhouse.com/blog/clickhouse-22-3-lts-released/ ps - we're built on Clickhouse at Luabase (https://luabase.com/ https://luabase.com/) and hiring across all role, send me a note (mike@luabase.com) if you're into web3+analytics.

bob1029 4y ago

Potentially relevant here... https://lemire.me/blog/2018/05/03/how-fast-can-you-parse-json/ https://lemire.me/blog/2018/05/03/how-fast-can-you-parse-jso... http://www.vldb.org/pvldb/vol10/p1118-li.pdf http://www.vldb.org/pvldb/vol10/p1118-li.pdf "We experimentally evaluate Mison using representative real-world JSON datasets and the TPC-H benchmark, and show that Mison produces significant performance benefits over the best existing JSON parsers; in some cases, the performance improvement is over one order of magnitude."

irrational 4y ago

Just this week I has to query a JSON file that is 13-14 MB in size. I already had Node installed, so that it what I used. I was surprised that it was able to do the processing so fast that it was done as soon as I hit the enter key in the terminal window. And the processing wasn't trivial, it had to touch every single element of the entire JSON file and put the entire thing into a new data structure. I was quite impressed at how fast it was. It took my IDE about 5 minutes just to open the JSON file so I could take a look at it to see how it was structured.

jamal-kumar 4y ago
Sounds like your IDE is a bit bloated. Not to tell you to stop using it or anything, that's a tall order - but next time you have a sufficiently large file (15mb shouldn't choke an editor?), try head/tail/less/more and you'll save those 5 minutes too

gourneau 4y ago

TIL that time can also output max memory use, and IO use very useful! Like they do here: /usr/bin/time -o performance.log -a -f"%e\t%M\t%x\t%C" %e (Not in tcsh.) Elapsed real time (in seconds). %M Maximum resident set size of the process during its lifetime, in Kbytes. %x (Not in tcsh.) Exit status of the command. %C (Not in tcsh.) Name and command-line arguments of the command being timed.

pmdulaney 5y ago

Cool! Let's port it to C and make it really fast!

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

davidatbu 5y ago

I asked about this on the Github issue regarding these benchmarks as well. I'm curious as to why libraries like ultrajson[0] and orjson[1] weren't explored. They aren't command line tools, but neither is pandas (which is benchmarked) right? Is it perhaps because the code required to implement the challenges is large enough that they are considered too inconvenient to use through the same way pandas was used (ie, `python -c "..."`)? [0] https://github.com/ultrajson/ultrajson https://github.com/ultrajson/ultrajson [1] https://github.com/ijl/orjson https://github.com/ijl/orjson

dmoura 5y ago
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.
- jammycrisp 4y ago
  > I should mention that spyql leverages orjson, which has a considerable impact on performance Even with orjson, you're still paying the cost of creating a new PyObject for every node in the JSON blob. orjson is well engineered (as is the backing serde-json decoder), but any JSON decoder that isn't using naive algorithms is mostly bound by the cost of creating PyObjects. Allocating in Python is _slow_. I wrote a quick benchmark (https://gist.github.com/jcrist/de29815389eaed4eaf5b24fbcfdab5f0 https://gist.github.com/jcrist/de29815389eaed4eaf5b24fbcfdab...) showing a handwritten query that accesses only a few fields in a 13 MiB JSON file. The same query is repeated with a number of different Python JSON libraries. Results: $ python bench_repodata_query.py msgspec: 45.018014032393694 ms simdjson: 61.94157397840172 ms orjson: 105.34720402210951 ms ujson: 121.9699690118432 ms json: 113.79130696877837 ms While `orjson`, is faster than `ujson`/`json` here, it's only ~6% faster (in this benchmark). `simdjson` and `msgspec` (my library, see https://jcristharif.com/msgspec/ https://jcristharif.com/msgspec/) are much faster due to them avoiding creating PyObjects for fields that are never used. If spyql's query engine can determine the fields it will access statically before processing, you might find using `msgspec` for JSON gives a nice speedup (it'll also type check the JSON if you know the type of each field). If this information isn't known though, you may find using `pysimdjson` (https://pysimdjson.tkte.ch/ https://pysimdjson.tkte.ch/) gives an easy speed boost, as it should be more of a drop-in for `orjson`.

gabrielsroka 4y ago

Focusing on ease of use instead of performance (I'm usually looking at small files) but I found jq's syntax to be really difficult, so I wrote my own teeny tiny tool in Python: https://replit.com/@gabrielsroka/Bash#pj.py https://replit.com/@gabrielsroka/Bash#pj.py Obviously it only does a fraction of what jq does, but it does what I need and I can expand it in a fraction of the time it would take to learn jq. Inspired by https://docs.python.org/3/library/json.html#module-json.tool https://docs.python.org/3/library/json.html#module-json.tool

dmoura 4y ago
Loved the simplicity of your solution :-) Congrats!
soheilpro 4y ago
My main problem with jq was finding the paths to the nodes, so I wrote catj (https://github.com/soheilpro/catj https://github.com/soheilpro/catj) to help with that.

benob 4y ago

orjson, the fast underlying json parser, is written in rust.

shepardrtc 4y ago
As soon as I saw Python, I knew they were using orjson. The title of this post is incorrect. It should simply be the title of the Colab notebook, "The fastest tools for querying large JSON datasets". While SPyQL is the fastest tool, it's not written entirely in Python.

fbwaters 4y ago

So for large files the bottleneck is encoding/decoding, which is done by orjson and not Python. If you wrap orjson in C++, the results would be even faster.

1vuio0pswjnm7 4y ago

I think this title says more about JSON than it does about Python.

dmoura 4y ago

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)

Olreich 4y ago
simdjson can load the json into memory in a queryable form in ~1/3 of a second. So you can save yourself basically 40% of the runtime right there. Computing average should take less than 1/2 a second on modern hardware (assumes <10 million books). So back-of-envelope target speed should be less than 1 second for this benchmark. 11.7s puts you at one order of magnitude off, which could be a fair price to pay if you never need this for large datasets (100s of GB or TB of data you want to query). And the reason we use wrapped libraries in Python so often is because it’s abysmally slow to do anything in the interpreter. The average loop is 100x slower than it should be. The more math you do the worse it gets too. Most pure Python code is 1000x slower than it should be.
- iknownothow 4y ago

cube2222 4y ago

Hey, OctoSQL author here. Great to see such benchmarks and another dataset to test with! Just wanted to add a small explanation for the interested to why at least OctoSQL[0] falls behind in the first and last test. OctoSQL supports retractions. That means an input for OctoSQL can not only be producing records, but also saying "I'm taking this record back", and OctoSQL will calculate the correct output based on that with minimal processing overhead required. It's a dataflow system in that sense. To achieve that, it has to buffer data at the output to be able to display the current output state (which might have rows removed from it). It’s the same case for LIMIT which is right now very unoptimal in OctoSQL. Because the top 100 rows can actually change if you have an ORDER BY clause specified and new rows/retractions arrive - it buffers the whole output, and then displays the top 100 rows. Running with the stream_native output format should drastically reduce the memory overhead and possibly processing time (I’ll post an update when the data is downloaded and I can compare), at least in the first case. Obviously, OctoSQL could be much smarter about detecting whether a source can even produce retractions, and optimize it’s behavior based on that. No point in buffering if retractions are not possible (which is the case for i.e. a JSON file). This would make LIMIT and the output table much faster and lighter. It’s definitely a big priority for me for future development to fix this (and make LIMIT and the table output format usable with big outputs), so stay tuned! :) And congrats to SPyQL for the first place here, its design is awesome. EDIT: Running the first test with `--output stream_native` (which just sends the productions and retractions as they come to the output, instead of buffering and sorting them) does indeed run 2-3x faster and use 50 MB RAM - instead of GB's. [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql

dmoura 4y ago
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?
- cube2222 4y ago
  No worries! I think it'll be most fair if I just let you know when these are fixed, and you can properly use them, performantly, with a sane output option (that's not stream native, but something like JSON). I.e. LIMIT won't work with stream_output. If you want to add it to the first one, I won't oppose, but don't feel pressured to do it. Especially the third query does highlight a very realistict limitation of OctoSQL as it works right now, so it's only fair as it is.

wdroz 4y ago

Why they didn't compare with Polars [0]? According to the h2oai benchmark [1], Polars is fast. [0] -- https://www.pola.rs/ https://www.pola.rs/ [1] -- https://h2oai.github.io/db-benchmark/ https://h2oai.github.io/db-benchmark/

Tarq0n 4y ago
Polars is for tabular data, a different kind of task.
- wdroz 4y ago
  But you can read json from polars (or from pandas first then polars) then you can do the map/reduce/filter like in the benchmark.
dmoura 4y ago
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)

eatonphil 4y ago

Thanks for including dsq! Its focus is always going to be flexibility and UX over performance since it's just a thin wrapper over the internals of a GUI data integration tool [0]. For example many other tools operate on only one or two file types and have optimized for querying on those types. Some like octosql and spyql IIRC have implemented their own sql parsing layer and engine. So if full sql support is necessary you may not be able to use them. In contrast dsq supports excel, avro, parquet, json, csv/tsv, nginx logs, apache logs, json newline, concatenated json, etc. And this list keeps growing. Dsq stores data in sqlite under the hood so you have access to the full sqlite dialect. Textql and q also do it this way. That said, despite performance not being the major I goal I think it will get better over time. There's already an OSS contributor who happens to be thinking about perf. If you're interested in getting started with OSS contributions btw I keep a page of good first projects [0]. If you know Go you're in a good place to contribute. I've already had a number of people take me up on this and merged a number of PRs by first-time OSS contributors. [0] https://github.com/multiprocessio/datastation https://github.com/multiprocessio/datastation [1] https://github.com/multiprocessio/datastation/blob/main/GOOD_FIRST_PROJECTS.md https://github.com/multiprocessio/datastation/blob/main/GOOD...

dmoura 4y ago
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 ;-)

dmoura 4y ago

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 https://github.com/dcmoura/spyql/issues/72 Thank you!