11 ms·
Summing columns in remote Parquet files using DuckDB
- simonw 3y agoIncludes a bonus TIL on nettop - today I learned that on macOS you can run this to see exactly how much bandwidth a specific process is using: nettop -p $PID
- heresie-dabord 3y agoAccording to this discussion [1], a variety of tools are available to Linux distros (including nettop), but the Apple OS version that is baked-in may have more features. [1] _ https://askubuntu.com/questions/257263/how-to-display-network-traffic-in-the-terminal https://askubuntu.com/questions/257263/how-to-display-networ...
- richraposa 3y agoNice. I did the same thing on ClickHouse - took 11 seconds: SELECT sum(size) FROM url('https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/0000{01..55}.parquet') Query id: 9d145763-0754-4aa2-bb7d-f6917690f704 ┌───────sum(size)─┐ │ 159344011148016 │ └─────────────────┘ 1 row in set. Elapsed: 11.615 sec. Processed 54.08 million rows, 8.50 GB (4.66 million rows/s., 731.83 MB/s.) Peak memory usage: 458.88 KiB.
- simonw 3y agoDo you know how much data it had to download to run that query? Did it pull all 8GB?
- zX41ZdbW 3y agoThe same query takes only 1.4 seconds on my server, so I assume that the query does not read all 8 GB.
- richraposa 3y agoClickHouse just reads the values from the one column being summed: https://www.markhneedham.com/blog/2023/11/15/clickhouse-summing-columns-remote-files/ https://www.markhneedham.com/blog/2023/11/15/clickhouse-summ...
- deleted 3y ago[deleted]
- hipadev23 3y agoI ran a network monitor while running that query, it pulled down ~290MB
- simonw 3y agoAlmost exactly the same as DuckDB then - I measured ~287MB.
- riku_iki 3y agoProbably compression
- deleted 3y ago[deleted]
- daveguy 3y agoIf that's raw network transfer, it's probably just a difference in headers or MTU size. Larger MTU -> fewer headers required. Maybe a difference in network configuration that requires more or less data in the header.
- qxip 3y agoI ran the same queries and got similar results but the bandwidth utilization I measured was significantly different. On the same fly.io instance with 1vCPU/256MB both queries completed successfully but ClickHouse/chdb reached 10MB/s (max) and logically completed the count faster, while DuckDB only peaked at around 2.5MB/s. This might be due to the tiny resources but I like rock bottom measurements. Did anyone else notice a similar bandwidth utilization gap?
- zxexz 3y agoI love and use both clickhouse and duckdb all the time, but it's become almost comical how senior clickhouse leadership comment on any duckdb-related HN thread like clockwork. And only very rarely bring up their affiliation :)
- chatmasta 3y agoYou mean this username composed of a random assortment of Latin characters isn't an unaffiliated, independent commentator?!
- richraposa 3y ago...says the completely anonymous internet guy. I'm laughing as much as you are. I wasn't trying to imply anything negative about DuckDB with my post - was just sharing how ClickHouse does the same thing. FWIW: the blog author added my query to his blog, so my non-combative comment was politely received.
- swasheck 3y agobut what the person said is true: seems like clickhouse comments descend upon every recent duckdb post as if it’s some sort of competition or born out of inferiority complex. clickhouse is really cool tech. duckdb is really cool tech. i grow weary of the CH infiltration to the point where it’s working against the intent to make me love CH
- datadeft 3y agoIs it so annoying? I am happy that we have at least two projects that can query big datasets with very reasonable performance.
- cmdlineluser 3y agoIt's "interesting". https://news.ycombinator.com/item?id=34772367 https://news.ycombinator.com/item?id=34772367
- 3y ago
- simonw 3y agoThe real magic is this query here (which I got via Twitter after posting this article): SELECT SUM(size) AS size FROM read_parquet( list_transform( generate_series(0, 55), n -> 'https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/' || format('{:06d}', n) || '.parquet' ) ); Turns out DuckDB has a lambda function feature these days! The n -> syntax.
- ies7 3y agoTIL. Usually I just went with SELECT * FROM 'folder/prefix*.parquet'
- BohuTANG 3y agoFor http(s) remote file, we can support glob pattern, for example: https://<thehfurl>/resolve/main/data/0000[00-55].parquet https://<thehfurl>/resolve/main/data/0000[00-55].parquet' Databend supports this pattern: https://databend.rs/doc/load-data/load/http#loading-with-glob-patterns https://databend.rs/doc/load-data/load/http#loading-with-glo...
- simonw 3y agoThat works for files on disk but not for files fetched via HTTP - though apparently DuckDB can do that for some situations, eg if they are in an S3 bucket that it can list files in.
- pests 3y agoYep - HTTP has no native support for file listings. In the old days it would be served at the index of a url path if no actual file was available - but that was always a feature of the http server and not anything unique to HTTP. Protocols like S3 and FTP have listings built in.
- booi 3y agoHTTP doesn't but I think you can in WebDAV, HTTP's long lost "file management" extension which actually has great support from most HTTP servers
- wizwit999 3y agoyou know there's this great abstraction over files we came up with in the data world called 'tables'..
- deleted 3y ago[deleted]
- simonw 3y agoIf you want something that looks like a table, while still benefiting from not having to download 8GB of Parquet in order to run the query, you can get one using a CTE: with midjourney_messages as ( select * from read_parquet( list_transform( generate_series(0, 2), n -> 'https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/' || format('{:06d}', n) || '.parquet' ) ) ) select sum(size) as size from midjourney_messages; Or you can create a view: create view midjourney_messages as select * from read_parquet( list_transform( generate_series(0, 2), n -> 'https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/' || format('{:06d}', n) || '.parquet' ) ); select sum(size) as size from midjourney_messages;
- pests 3y agoAre there any pros/cons to the raw query, CTE, and view approches?
- simonw 3y agoFrom my experience with other databases my assumption for DuckDB is: - Using a raw query, a CTE or a view will have no impact at all on query performance - they'll be optimized down to exactly the same operations (or to a query plan that's similar enough that the differences are negligible) - CTEs are mainly useful for breaking down more complicated queries - so not great for this example, but really useful the moment you start doing anything more complicated. - Views are effectively persistent CTEs - they're great if you want to permanently "bookmark" more complex pieces of queries to use later. I wrote a bit more about CTEs here: https://datasette.io/tutorials/data-analysis#ctes https://datasette.io/tutorials/data-analysis#ctes
- smabie 3y agoHe did all this work and wrote a blog post bc he didn't want to download... 8gb of data?
- chrisjc 3y agoHere is some other interesting and related duckdb SQL that you all might find helpful. inspect the parquet metadata: SELECT * FROM parquet_metadata('https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/000000.parquet'); If the data was on blob storage you could use a glob instead of a generator: SELECT sum(*) AS TOTAL_SIZE FROM read_parquet('https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/*.parquet'); You can use the huggingface API to list and then read with duckdb: SELECT concat('https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/', path) as parquet_file FROM read_json_auto('https://huggingface.co/api/datasets/vivym/midjourney-messages/tree/main/data'); So this means we can combine the list files and read files SQL into a single statement!!! Error: Binder Error: Table function cannot contain subqueries :( No Want the query in a more concise, reusable form? CREATE MACRO GET_MJ_TOTAL_SIZE(num_of_files) AS TABLE ( SELECT SUM(size) AS size FROM read_parquet( list_transform( generate_series(0, num_of_files), n -> 'https://huggingface.co/datasets/vivym/midjourney-messages/resolve/main/data/' || format('{:06d}', n) || '.parquet' ) ) ); You can simply query table function: SELECT * FROM GET_MJ_TOTAL_SIZE(55); You don't need to run nettop: EXPLAIN ANALYZE SELECT * FROM GET_MJ_TOTAL_SIZE(55); ┌─────────────────────────────────────┐ │┌───────────────────────────────────┐│ ││ HTTP Stats: ││ ││ ││ ││ in: 295.5MB ││ ││ out: 0 bytes ││ ││ #HEAD: 55 ││ ││ #GET: 166 ││ ││ #PUT: 0 ││ ││ #POST: 0 ││ │└───────────────────────────────────┘│ └─────────────────────────────────────┘
- simonw 3y agoNeat, thank you. I added the parquet_metadata() tip to my article: https://til.simonwillison.net/duckdb/remote-parquet#user-content-parquet_metadata https://til.simonwillison.net/duckdb/remote-parquet#user-con...
- didip 3y agoI still don’t understand DuckDB use case. To handle huge amount of data, you need a large database that’s connected to the network. DuckDB will eventually hit a ceiling.
- icyfox 3y agoIt fits a pretty nice niche between small data (32GB in-memory, pandas level) and truly huge data where the disk files can't even fit on one machine (2TB+). Most data that people want to process on a daily cadence fit somewhere in between those ranges. As for whether to use a database or not, I think that's a more fundamental question about where your different read and write loads are coming from. Scaling a database to support billions of rows and have high query performance is not trivial; if your use-case fits more in a write-infrequently, read-often then OLAP is a pretty good choice of index structure.
- riku_iki 3y ago> It fits a pretty nice niche between small data (32GB in-memory, pandas level) and truly huge data where the disk files can't even fit on one machine (2TB+) you can build/rent server with say 50TB nvme raid and run duckdb on it?..
- simonw 3y agoIt's for analytics. It's a tool that can run on my laptop that means I can run analytical queries against potentially TBs of data hosted in an S3 bucket, without needing to spin up an expensive data warehouse.
- swasheck 3y agosimilar use case to sqlite as an embedded database system with a columnar/analytics focus.
- isoprophlex 3y agoNo no it is insanely useful and I wish I had a job where I could use it more! Noone really has big big data, they only think they do. You can do the analytics that Databricks and Snowflake are trying to sell your boss for $100.000 a month, only then on an 80$ vps and a couple of S3 buckets full of parquet files. It's a fantastic tool to run analytics. Lean, purposeful. I hope they can manage to stay out of the clutches of corporate enshittification for a while longer.
- skadamat 3y agoThis is one of my favorite features of DuckDB and Parquet files! I combined these capabilities with mounting via NFS and documented in the last part of my post here: https://about.xethub.com/blog/version-control-machine-learning-workflow-pyxet-part-1 https://about.xethub.com/blog/version-control-machine-learni...
- jwmoz 3y agoUse polars it’s faster
- simonw 3y agoCan it run aggregate queries against 8 GiB of remotely hosted data while only pulling down the 287 MiB of columns it needs?
- chrisjc 3y agoAs much as I love to tout the benefits of duckdb, the ability to only pull down the columns that you need is not really functionality that is enabled by or exclusive to duckdb. It really has to do with the parquet file format. And it gets even more powerful than just being able to download specific columns. To a certain degree, you can also limit rows at the same time. http://peter-hoffmann.com/2020/understand-predicate-pushdown-on-rowgroup-level-in-parquet-with-pyarrow-and-python.html http://peter-hoffmann.com/2020/understand-predicate-pushdown... All that being said... yes, as far as I know, polars can do all of that. Probably because it has the necessary parquet dependencies. Now whether you should use polars instead of duckdb or vise-versa? Is polars "faster"? It depends
- cmdlineluser 3y agoLooks like somebody requested it after reading your TIL. https://github.com/pola-rs/polars/issues/12493#issuecomment-1814763393 https://github.com/pola-rs/polars/issues/12493#issuecomment-... It will be in the next release. (later today?)
- simonw 3y agoWow that was fast! https://github.com/pola-rs/polars/pull/12517 https://github.com/pola-rs/polars/pull/12517
- cmdlineluser 3y agoNice :-D Do you happen to know what happened to this thread? (I recall reading a previous post of yours about tracking HN positions?) I also asked as a top-level comment: https://news.ycombinator.com/item?id=38304938 https://news.ycombinator.com/item?id=38304938
- isuckatcoding 3y agoOk that seems like magic. How is it able to only read a subset of the data like that? Is this because of the parquet file format? Also can the same thing work for files hosted on s3?
- simonw 3y agoYes, this works for S3 files as well. The trick it's using is the HTTP Range header, which lets a client request eg bytes 4500-4900 of an HTTP file. Most static file hosting platforms - S3, GCS, nginx, Apache etc - support Range headers. They're most commonly used for streaming video and audio. The Parquet file format is designed with this in mind. You can read metadata at the start of the file and use it to figure out which ranges to fetch. Columns are grouped together, so sum() against a column can be handled by fetching a subset of the file.
- mosselman 3y agoThank you for explaining. I never really looked into this to understand it and because of that it felt like magic, which is always in indicator that you just don’t understand something. I was going to add “in tech”, but it is an indicator of that with anything in life.
- BenoitP 3y agoParquet is columnar, you can read each column independently, and a different compression scheme can be used for each column. You first have to read the beginning of the file to get the layout though (And the end of it as well, according to a library I have been using. Don't know why) . The http server must of course support range queries.
- choppaface 3y agoRight, there's all sorts of metadata and often stats included in any parquet file: https://github.com/apache/parquet-format#file-format https://github.com/apache/parquet-format#file-format The offsets of said metadata are well-defined (i.e. in the footer) so for S3 / blob storage so long as you can efficiently request a range of bytes you can pull the metadata without having to read all the data.
- IanCal 3y agoParquet files are also /can also be split into different row groups. By ordering the contents by whatever you're most likely to filter on you can cut down on accessed data even more. E.g if you filter on creation time order the data by that. Then when you query it, duckdb/similar should read the metadata for each row groups and ignore any that cannot contain the data you want. Ordering by user id or country, etc. are common other ones. Order by the biggest group first (so country then timestamp if you often filter on both). You can tune the size of the row groups depending on your access pattern.
- cmdlineluser 3y agoDoes anybody know why this thread appears to have just vanished? It was on page 2 a moment ago, and now it's not listed anywhere (pages 1-14)