Summing columns in remote Parquet files using DuckDB

Re:Summing columns in remote Parquet files using DuckDB by simonw on Thursday November 16, 2023 @07:43PM (#38297760)

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

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by ies7 on Thursday November 16, 2023 @09:09PM (#38298425)

TIL. Usually I just went with SELECT * FROM 'folder/prefix*.parquet'

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by simonw on Thursday November 16, 2023 @09:41PM (#38298700)

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

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by pests on Thursday November 16, 2023 @10:24PM (#38299092)

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

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by booi on Friday November 17, 2023 @12:55AM (#38300079)

HTTP 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

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by chrisjc on Friday November 17, 2023 @01:50AM (#38300372)

Actually...

(sorry, not picking on you Simon! Awesome post and I just love reading and talking about this stuff)

With duckdb running on python you can register your own file-system adapters. This means that you can do things like intercept globbing, transform urls/path or physically getting files.

This means that you could inject whatever listing/lookup that might be needed for your read_{format}() table function use case.

http://duckdb.org/docs/guides/python/filesystems

However, it is true that there is no standard way to glob over HTTP unless you're using something like S3.

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by ies7 on Friday November 17, 2023 @08:14AM (#38303025)

Yeah my bad. I only used it with S3 and somehow my brain think http will be the same.

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by BohuTANG on Monday November 20, 2023 @09:29PM (#38358198)

For http(s) remote file, we can support glob pattern, for example: https://<thehfurl>/resolve/main/data/0000[00-55].parquet'

Databend supports this pattern: https://databend.rs/doc/load-data/load/http#loading-with-glo...

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by notachatbot123 on Friday November 17, 2023 @03:22AM (#38300913)

Twitter isn't a person, it would be nice to credit the actual human who provided this to you.

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by vdm on Friday November 17, 2023 @04:15AM (#38301231)

ok notachatbot123

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by MF-DOOM on Friday November 17, 2023 @04:26AM (#38301299)

I mean, it’s not like he created this feature, or that it’s a “weird undocumented trick” he found out…

Do you have to credit every person who shows you anything on the internet nowadays? Or is it just the common twitter-hate on HN?

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by notachatbot123 on Friday November 17, 2023 @06:56AM (#38302314)

It's still an uncredited effort by another person, copied and posted elsewhere as magic trick. I just think it is nice to publicly credit the people via whom one builds one's wisdom.

No Twitter hate at all here, on the contrary.

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by simonw on Friday November 17, 2023 @04:53AM (#38301488)

I did. Read my article. https://til.simonwillison.net/duckdb/remote-parquet#user-con...

I directly name and link to four people who contributed tips that helped me expand it.

[ Parent | on HN ]

Re:Summing columns in remote Parquet files using DuckDB by notachatbot123 on Friday November 17, 2023 @06:54AM (#38302284)

Cheers and thank you! I had read the article in an earlier stage and thought you had copied that trick only here (where I would still think it would be nice to cite or link the person directly here on HN).

[ Parent | on HN ]