Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

github.com

Post reply on HN
Print view View original

31–40 of 64 posts

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#31

Post by franga2000 »

playingalong wrote: post #16

Bye bye jq and your awful query syntax.

I agree jq's syntax it doesn't make much sense for tables where the primary operations are filter and merge, but for deep tree-like datasets, which is what JSON is supposed to be used for, traversal and iteration are more important and the syntax makes perfect sense there.

I'd be willing to bet most programmers would instantly understand something like `.users[] | {email: .email, lastLogin: .logins[-1].date}`, even if they've never seen jq.

Now that I'm thinking about it, the kind of structures we often use JSON for are, in a way, a subset of what can be done with tables and pointers (foreign keys), so would it be possible to create a kind of jq to SQL compiler? Has anyone tried that?

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#32

Post by smugma »

SQL on CSV (using preinstalled Mac tools) previously linked on HN: https://til.simonwillison.net/sqlite/one-line-csv-operations

e.g.

sqlite3 :memory: -cmd '.mode csv' -cmd '.import royalties.csv Royalty' -cmd '.mode column' \

    'SELECT SUM(Royalty),Currency FROM Royalty GROUP BY Currency'

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#34

Post by tomrod »

jaxn wrote: post #29
tomrod wrote: post #19

Earlier quoted context omitted.

90% of SQL usage, or more, is select in slowly changing data contexts.

Maybe in your database. Do you have any validation of that claim in a larger context?

Purely the power law. That would be an interesting thing to figure out though. Maybe a github crawl.

EDIT: I stand corrected based on github code files (which might better represent application CRUD queries versus use by analysts, more thought required!)

SELECT: 7.3M code results [0]

INSERT: 8.9M code results [1]

UPDATE: 5.5M code results [2]

DELETE: 5.0M code results [3]

[0] https://github.com/search?q=select++extension%3Asql&type=Cod...

[1] https://github.com/search?q=insert++extension%3Asql&type=Cod...

[2] https://github.com/search?q=update++extension%3Asql&type=Cod...

[3] https://github.com/search?q=delete++extension%3Asql&type=Cod...

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#35

Post by sakras »

cube2222 wrote: post #2

This looks really cool! Especially using datafusion underneath means that it probably is blazingly fast. If you like this, I recommend taking a look at OctoSQL[0], which I'm the author of. It's plenty fast and easier to add new data sources for as external plugins. It can also handle endless streams of data natively, so you can do running groupings on i.e. tailed JSON logs. Additionally, it's able to push down predic…

> datafusion

> blazingly fast

I’m going to need to see a citation for that. Last I checked, it was being beaten by Apache Spark in non-memory constrained scenarios [0]. This may be “blazingly fast” compared to Pandas or something, but it’s still leaving a TON of room on the table performance-wise. There’s a reason why Databricks found it necessary to redirect their Spark backend to a custom native query engine [1].

[0] https://andygrove.io/2019/04/datafusion-0.13.0-benchmarks/

[1] https://cs.stanford.edu/~matei/papers/2022/sigmod_photon.pdf

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#36

Post by houqp »

Kalanos wrote: post #33

is there a pythonic api for scripting (not command line)? i was looking for a json query tool and couldn't find one.

Yes, I designed the code base so that the core of the IO and query logic are abstracted into a Rust library called columnq. My plan is to wrap it with pyo3 so the full API can be accessed as a Python package! If you are interested in helping with this, please feel free to submit a PR. The core library is located at https://github.com/roapi/roapi/tree/main/columnq

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#37

Post by houqp »

sakras wrote: post #35
cube2222 wrote: post #2

This looks really cool! Especially using datafusion underneath means that it probably is blazingly fast. If you like this, I recommend taking a look at OctoSQL[0], which I'm the author of. It's plenty fast and easier to add new data sources for as external plugins. It can also handle endless streams of data natively, so you can do running groupings on i.e. tailed JSON logs. Additionally, it's able to push down predic…

> datafusion > blazingly fast I’m going to need to see a citation for that. Last I checked, it was being beaten by Apache Spark in non-memory constrained scenarios [0]. This may be “blazingly fast” compared to Pandas or something, but it’s still leaving a TON of room on the table performance-wise. There’s a reason why Databricks found it necessary to redirect their Spark backend to a custom native query engine [1]. […

Datafusion out performs spark by a large margin. It is on par with photon based on my experiences, see benchmarks at https://github.com/blaze-init/blaze.

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#39

Post by sakras »

houqp wrote: post #37
sakras wrote: post #35

Earlier quoted context omitted.

> datafusion > blazingly fast I’m going to need to see a citation for that. Last I checked, it was being beaten by Apache Spark in non-memory constrained scenarios [0]. This may be “blazingly fast” compared to Pandas or something, but it’s still leaving a TON of room on the table performance-wise. There’s a reason why Databricks found it necessary to redirect their Spark backend to a custom native query engine [1]. […

Datafusion out performs spark by a large margin. It is on par with photon based on my experiences, see benchmarks at https://github.com/blaze-init/blaze .

Ah nice, thank you for sharing that. I hadn’t seen it before, and congrats on beating out Spark that hard, I hope it continues to improve!

As an aside, maybe it would make sense to publish a new blog post somewhere so that the top hit on Google for “DataFusion benchmark” isn’t that post I linked.

Re: Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet

#40

Post by houqp »

sakras wrote: post #39
houqp wrote: post #37

Earlier quoted context omitted.

Datafusion out performs spark by a large margin. It is on par with photon based on my experiences, see benchmarks at https://github.com/blaze-init/blaze .

Ah nice, thank you for sharing that. I hadn’t seen it before, and congrats on beating out Spark that hard, I hope it continues to improve! As an aside, maybe it would make sense to publish a new blog post somewhere so that the top hit on Google for “DataFusion benchmark” isn’t that post I linked.

Haha, yeah, we should definitely put a little bit more efforts into SEO :) Everyone is so focused on the hard-core engineering at the moment. I think Matthew from the community is actually working on a new comprehensive benchmark for us at the moment, which I hope will be published soon.
Post reply on HN