6 ms·
Run SQL on CSV, Parquet, JSON, Arrow, Unix Pipes and Google Sheet
- henrydark 4y agoIt is pretty cool. py-spy has also been doing this for a few years https://github.com/benfred/py-spy https://github.com/benfred/py-spy
- cube2222 4y agoThis 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 predicates to the database below, so if you're selecting 10 rows from a 1 billion row table, it'll just get those 10 rows instead of getting them all and filtering in memory. [0]: https://github.com/cube2222/octosql https://github.com/cube2222/octosql
- sakras 4y ago> 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/ https://andygrove.io/2019/04/datafusion-0.13.0-benchmarks/ [1] https://cs.stanford.edu/~matei/papers/2022/sigmod_photon.pdf https://cs.stanford.edu/~matei/papers/2022/sigmod_photon.pdf
- houqp 4y agoDatafusion 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 https://github.com/blaze-init/blaze.
- sakras 4y agoAh 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.
- houqp 4y agoHaha, 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.
- andygrove 4y agoI will update these old pages on my blog and redirect them!
- cube2222 4y agoOk, I have now actually benchmarked this roapi CLI on the Amazon Review Dataset and it's over 20x slower than OctoSQL. A simple group by time columnq sql --table books_10m.ndjson "SELECT AVG(overall) FROM books_10m" takes 66 seconds. The equivalent in OctoSQL takes less than 3 seconds. I retract my statement about this project being blazingly fast, though I imagine it's just the JSON parser that requires optimization.
- bachmeier 4y agoAs I commented on a recent similar discussion, these tools can't be used for update or insert. As useful as querying might be, it's terribly misleading to claim to "run SQL" if you can't change the data, since that's such a critical part of an SQL database.
- gavinray 4y agoQuestion: I've built something that supports full CRUD, and queries that span multiple data sources with optimization and pushdown What kind of headline would make you want to read/try such a thing? (I'm planning on announcing it + releasing code on HN but have never done so before)
- fshr 4y agoHi Gavin; that sounds interesting! I saw @eirikbakke make a comment about https://www.ultorg.com https://www.ultorg.com earlier. It appears to also support editing the underlying data. I'm curious to see how you've each tackled these tricky topics.
- porker 4y agoShow HN: Read and update Arrow, Parquet and xxxx files using SQL
- gavinray 4y agoIt works on databases and arbitrary data sources too though
- tomrod 4y ago90% of SQL usage, or more, is select in slowly changing data contexts.
- jaxn 4y agoMaybe in your database. Do you have any validation of that claim in a larger context?
- mmastrac 4y agoThe one thing everyone here is missing so far is that it's a Rust binary, distributed on PyPi. That's brilliant.
- samwillis 4y agoI’m all in on using PyPI for binary distribution. Couple that with Python Venv and you have a brilliant system for per project dependancies. I created this project for distributing Node via PyPI: https://pypi.org/project/nodejs-bin/ https://pypi.org/project/nodejs-bin/
- WHATDOESIT 4y agoWhy not rather distribute Python as NPM dependency?
- einpoklum 4y agoYou can get a statically-linked binary release from GitHub which depends on nothing (I think).
- jonahx 4y agoCan you explain the advantages of this vs cargo?
- proto_lambda 4y agocargo is not a binary distribution.
- houqp 4y agoMost users already have pip installed, so they won't need to install a rust toolchain.
- simonw 4y agoI wrote a bit about this pattern here: https://simonwillison.net/2022/May/23/bundling-binary-tools-in-python-wheels/ https://simonwillison.net/2022/May/23/bundling-binary-tools-...
- tootie 4y agoAWS Athena offers something similar. You can build tables off of structured text files (like log files) in S3 and run SQL queries.
- ramraj07 4y agoWhat’s the performance like though?
- tootie 4y agoI have not put it through any stress tests. I look at this kind of tool as a nice convenience. If I needed something high throughput I'd probably want a full baked data warehouse pipeline.
- atwebb 4y agoIt really, really, realllllyyyy, depends on how you setup your prefix/"folder" structure and the underlying file format. Though that's almost certainly true here.
- avereveard 4y agoResponse in seconds if you hit partitions (a version of index that only support equality and its implemented in folders in this case) and your storage system contains content metadata like parquet or orc. Is not a BI tool tho, if queries have high variability on the where clause and you can't leverage indexes then you're looking at minutes as response time. If the data is not in structures but plain csv/json, all bets are off. I've not yet tested it at terabyte scale albeit it should happily scale up there.
- gavinray 4y ago1) roapi is built with some wicked cool tech 2) the author once answered some questions I posted on Datafusion, so they're cool in my book Here are my anecdotes.
- playingalong 4y agoBye bye jq and your awful query syntax.
- franga2000 4y agoI 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?
- skybrian 4y agoLooks like it also supports SQLite for input, but not for output. That might be a nice addition.
- whimsicalism 4y agoTrino can do this as well.
- ebfe1 4y agoThis is cool...Totally reminded me about several tools pop up on HN every now and then in the past for similar task so i did a quick search: clickhouse-local - https://news.ycombinator.com/item?id=22457767 https://news.ycombinator.com/item?id=22457767 q - https://news.ycombinator.com/item?id=27423276 https://news.ycombinator.com/item?id=27423276 textql - https://news.ycombinator.com/item?id=16781294 https://news.ycombinator.com/item?id=16781294 simpql- https://news.ycombinator.com/item?id=25791207 https://news.ycombinator.com/item?id=25791207 We need a benchmark i think..;)
- tanin 4y agoShameless plug. A desktop app: https://superintendent.app https://superintendent.app
- ebfe1 4y agowe need more shameless plug on this thread ... the more benchmark, the better! I think this would be a fun weekend afternoon :)
- tstack 4y agoOh, okay... https://lnav.org https://lnav.org is a log file viewer for the terminal that integrates with SQLite so you can use SQL to query your log files. No benchmarks, though.
- eirikbakke 4y agoAnother shameless plug, then: https://www.ultorg.com https://www.ultorg.com This one is another desktop app, with a slightly different approach: in the CSV file case, the data is parsed and dumped into a bundled PostgreSQL database, which you can then query with a visual query interface.
- wodenokoto 4y agoIt looks really nice. I’ve spend what feels like too much time looking for a windows app that I can use to occasionally preview CSV files before moving them into WSL. I just think $50/year is too much for occasional use. Since this posts has initiated several other shameless plugs, maybe I can swoop in and ask for shameless recommendations for something cheap in this space.
- smugma 4y agoSQL on CSV (using preinstalled Mac tools) previously linked on HN: https://til.simonwillison.net/sqlite/one-line-csv-operations 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'
- Kalanos 4y agois there a pythonic api for scripting (not command line)? i was looking for a json query tool and couldn't find one.
- houqp 4y agoYes, 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 https://github.com/roapi/roapi/tree/main/columnq
- the_optimist 4y agoWhat’s the memory handling behavior here? Are CSVs read on query or at startup? What about Arrow? If read on startup, is there compression applied?
- johnnunn 4y agoI have a use case, where my company's application logs will be shipped to S3 in a directory structure such as application/timestamp(one_hour)_logs.parquet. We want to build a simple developer focussed UI, where we can query for a given application for a time range and retrieve a bunch of s3 blobs in that time range and brute force search for the desired string. I see that roapi offers a REST interface for a fixed set of files but I would like to dynamically glob newer files. Are there are alternatives that can be used too ? Thanks
- mkane 4y agoIf you're already using parquet, it might be worth looking at the concept of datasets e.g. https://arrow.apache.org/docs/python/generated/pyarrow.parquet.ParquetDataset.html https://arrow.apache.org/docs/python/generated/pyarrow.parqu...
- marek_leisk2 4y agoTrino can do this.
- cube2222 4y agoAmazon Athena + AWS Glue for schema discovery can do this.
- theGnuMe 4y agoThis is really cool and redefines ETL pipelines.