I have an personal pet peeve about Parquet that is solved, incompatibly, by basically every "data lake / lakehouse" layer on top, and I'd love to see it become compatible: ranged partitioning.
I have an application which ought to be a near-perfect match for Parquet. I have a source of timestamped data (basically a time series, except that the intervals might not be evenly spaced -- think log files). A row is a timestamp and a bunch of other columns, and all the columns have data types that Parquet handles just fine [0]. The data accumulates, and it's written out in batches, and the batches all have civilized sizes. The data is naturally partitioned on some partition column, and there is only one writer for each value of the partition column. So far, so good -- the operation of writing a batch is a single file creation or create call to any object store. The partition column maps to the de-facto sort-of-standard Hive partitioning scheme.
Except that the data is (obviously) also partitioned on the timestamp -- each batch covers a non-overlapping range of timestamps. And Hive partitioning can't represent this. So none of the otherwise excellent query tools can naturally import the data unless I engage in a gross hack:
I could also partition on a silly column like "date". This involves aligning batches to date boundaries and also makes queries uglier.
I could just write the files and import ".parquet". This kills performance and costs lots of money.
I could use Iceberg or Delta Lake or whatever for the sole benefit that their client tools can handle ranged partitions. Gee thanks. I don't actually need any of the other complexity.
It would IMO be really really nice if everyone could come up with a directory-name or filename scheme for ranged partitioning.
[0] My other peeve is that a Parquet row and an Arrow row and a Thrift message and a protobuf message, etc, are almost* but not quite the same thing. It would be awesome if there was a companion binary format for a single Parquet row or a stream of rows so that tools could cooperate more easily on producing the data that eventually gets written into Parquet files.
Why is the footer metadata not sufficient for this need? The metadata should contain the min and max timestamp values from the respective column of interest, so that when executing a query, the query tool should be optimizing its query by reading the metadata to determine if that parquet file should be read or not depending on what time range is in the query.
Hive supports 2 kinds of partitioning, injected and dynamic. You can totally use a partition key like the hour in UNIX time. It's an integer starting at some epoch and incrementing by 3600.
Now your query engine might require you to specify the partitions or range of partitions you want to query on; you absolutely can use datepartition >=a and datepartition<b in your query. Iceberg seems to fix that and just let you use the timestamp; presumably the metadata is smart enough to exclude the partitions you don't care about.
In the lower level arrow/parquet libraries you can control the row groups, and even the data pages (although it’s a lot more work). I have used this heavily with the arrow-rs crate to drastically improve (like 10x) how quickly data could be queried from files. Some row groups will have just a few rows, others will have thousands, but being able to bypass searching in many row groups makes the skew irrelevant.
Just beware that one issue you can have is the limit of row groups per file (2^15).
Time series data is naturally difficult to work with, but avoidable. One solution is to not query raw time series data files. Instead, segment your time series data before you store it, normalizing the timestamps as part of event processing. Sliding window observations will help you find where the event begins and then you adjust the offset until you find where the time series returns to its zero-like position. That's your event.
I think maybe this is a pet peeve of Hive and not of Parquet? Yes, it does require opening the Parquet file to look at the min, max range for the column, but only that data and if the data isn’t in range there shouldn’t be further requests.
That is the kind of metadata that is useful to push up, into something like DuckLake.
This looks awesome. One of my biggest gripe's personally with Iceberg (less-so Delta Lake, but similar) is how difficult it is to just try out on a laptop. Delta Lake has vanilla Python implementations, but those are fragemented and buggy IME. Iceberg has just never worked locally, you need a JVM cluster and a ton of setup. I went down a similar road of trying to use sqlite/postgres+duckdb+parquet files in blob storage, but it was a lot of work.
It seems like this will just work out of the box, and just scale up to very reasonable data sizes. And the work from the DuckDB folks is typically excellent. It's clear they understand this space. Excited to try it out!
Have you tried out PyIceberg yet? It's a pure Python implementation and it works pretty well. It supports a SQL Catalog as well as an In-Memory Catalog via a baked in SQLite SQL Catalog.
I was thinking of putting something together for this. Like a helm chart that works with k3s.
datapains has some good stuff to get trino running and you can get a hivemetastore running with some hacking. I dorked around with it and then got the iceberg connector working with trino and see how it all works. I load data in to a dumb hive with a trino table pointed at it and then insert from select ... in to iceberg.
If the duck guys have some simple to get running stuff, they could probably start to eat everyone else' lunch.
They make a really good criticism of Iceberg: if we have a database anyway, why are we bothering to store metadata in files?
I don’t think DuckLake itself will succeed in getting adopted beyond DuckDB, but I would not be surprised if over time the catalog just absorbs the metadata, and the original Iceberg format fades into history as a transitional form.
Hopefully this clarifies the value proposition for others:
Existing Lakehouse systems like Iceberg store crucial table information (like schema and file lists) as many small "metadata files" in cloud object storage (like S3). Accessing these files requires numerous network calls, making operations like query planning and updating tables inefficient and prone to conflicts. DuckLake solves this by putting all that metadata into a fast, transactional SQL database, using a single query to get what's needed, which is much quicker and more reliable.
They support syncing to Iceberg by writing the manifest and metadata files on demand, and they already have read support for Iceberg. They just fixed Iceberg's core issues but it's not a direct competitor as you can use DuckLake along with Iceberg in a very nice and bidirectional way.
metadata bloat can be due to a few things, but it’s manageable.
* number of snapshots
* frequent large schema changes
* lots of small files/row level updates
* lots of stats
The last one IIRC used to be pretty bad especially with larger schemas.
Most engines have ways to help with this - compaction, snapshot exportation, etc… Though it can still be up to the user. S3 tables is supposed to do some of this for you.
If metadata is below 1-5MB it’s really not an issue. Your commit rate is effectively limited by the size of your metadata and the number of writers you have.
I’ve written scripts to fix 1GB+ metadata files in production. Usually it was pruning snapshots without deleting files (relying on bucket policy to later clean things up) or removing old schema versions.
I’m building a poor man’s datalake at work, basically putting parquet files in blob storage using deltalake-rs’ python bindings and duck db for querying.
However, I constantly run in to problems with concurrent writes. I have a cloud function triggered ever x minutes to pull data from API and that’s fine.
But if I need to run a backfill I risk that that process will run at the same time as the timer triggered function. Especially if I load my backfill queue with hundreds of runs that needs to be pulled and they start saturating the workers in the cloud function.
For what it's worth, MotherDuck and DuckLake will play together very nicely. You will be able to have your MotherDuck data stored in DuckLake, improving scalability, concurrency, and consistency while also giving access to the underlying data to third-party tools. We've been working on this for the last couple of months, and will share more soon.
i think a way to see it is MotherDuck is a service to just throw your data at at they will sort it (using duckdb underneath) and you can use DuckDB to iterface with your data. But if you want to be more "lakehouse" or maybe down the line there are more integrations with DuckLake ir you want data to be stored in a blob storage, you can use DuckLake with MotherDuck as the metadata store.
There is a lot to like here, but once metadata is in the novel Ducklake format, it is hard to picture how you can get good query parallelism, which you need for large datasets. Iceberg already is well supported by lots of heavy-duty query engines and that support is important once you have lots and lots and lots of data.
My understanding was that MotherDuck was focusing on providing the "multiplayer mode" for DuckDB. It's interesting to see DuckDB Labs supporting data lakes natively. I guess MotherDuck is potentially moving to the UI layer by providing the notebook interface for DuckDB.
Good point! Anticipating official announcements I can confirm that MotherDuck is indeed intending to both: host DuckLake catalogs, and facilitate querying DuckLakes using DuckDB via its cloud-based DuckDB service.
I am a huge fan of what they are doing, particularly putting local compute front and center. However for “BigCorp”, it’s going to be an uphill battle. The incumbents are entrenched and many decision makers will make decisions based on non technical reasons (I.e did my sales exec get me to the F1 Grand Prix).
One thing I noticed is that the `table_stats` and `column_stats` tables aren't snapshot versioned. What are these used for and isn't that going to hurt timetravel queries (`SELECT COUNT(*) FROM tbl WHERE snapshot_id=<old_id>` as a simple example)?
It looks very promising, especially knowing DuckDB team is behind it. However I really don't understand how to insert data in it. Are we supposed to use DuckDB INSERT statement with any function to read external files or any other data ? Looks very cool though.
the manifesto [1] is the most interesting thing. I agree that DuckDB has the largest potential to disrupt the current order with Iceberg.
However, this mostly reads to me as thought experiment:
> what if the backend service of an Iceberg catalog was just a SQL database?
The manifesto says that maintaining a data lake catalog is easier, which I agree with in theory. s3-files-as-information-schema presents real challenges!
But, what I most want to know is what's the end-user benefit?
What does someone get with this if they're already using Apache Polaris or Lakekeeper as their Iceberg REST catalog?
it adds for users the following features to a data lake:
- multi-statement &
multi-table transactions
- SQL views
- delta queries
- encryption
- low latency: no S3 metadata &
inlining: store small inserts in-catalog
and more!
I love duckDB and this looks just absolutely brilliant!
One question for me is, lets say i want to start using this today and at work we are running snowflake. I get that each analytics person would have to run duckdb + this extension on their local machines and point to the blob store and the database that is running datalake extension, for now that would be say a VM running duckdb. When I run the actual query where does the computation happen? And what if I want a lot of computation?
Is the solution currently to host a huge duckdb VM that everyone ssh's into and run their queries or how does that part work?
Using SQL as catalog is not new (iceberg supports JDBC catalog from the very beginning).
The main difference is to store metadata and stats also directly in SQL databases, which makes perfect sense for smaller scale data. In fact we were doing something similar in https://github.com/Mooncake-Labs/pg_mooncake, metadata are stored in pg tables and only periodically flush to actual formats like iceberg.
My understanding is that DuckLake, while being open source format, is not compatible with Iceberg, since it addressees some of it’s shortcomings, such as metadata stored in blob storage.
[+] [-] amluto|9 months ago|reply
I have an application which ought to be a near-perfect match for Parquet. I have a source of timestamped data (basically a time series, except that the intervals might not be evenly spaced -- think log files). A row is a timestamp and a bunch of other columns, and all the columns have data types that Parquet handles just fine [0]. The data accumulates, and it's written out in batches, and the batches all have civilized sizes. The data is naturally partitioned on some partition column, and there is only one writer for each value of the partition column. So far, so good -- the operation of writing a batch is a single file creation or create call to any object store. The partition column maps to the de-facto sort-of-standard Hive partitioning scheme.
Except that the data is (obviously) also partitioned on the timestamp -- each batch covers a non-overlapping range of timestamps. And Hive partitioning can't represent this. So none of the otherwise excellent query tools can naturally import the data unless I engage in a gross hack:
I could also partition on a silly column like "date". This involves aligning batches to date boundaries and also makes queries uglier.
I could just write the files and import ".parquet". This kills performance and costs lots of money.
I could use Iceberg or Delta Lake or whatever for the sole benefit that their client tools can handle ranged partitions. Gee thanks. I don't actually need any of the other complexity.
It would IMO be really really nice if everyone could come up with a directory-name or filename scheme for ranged partitioning.
[0] My other peeve is that a Parquet row and an Arrow row and a Thrift message and a protobuf message, etc, are almost* but not quite the same thing. It would be awesome if there was a companion binary format for a single Parquet row or a stream of rows so that tools could cooperate more easily on producing the data that eventually gets written into Parquet files.
[+] [-] bitbang|9 months ago|reply
[+] [-] TheCondor|9 months ago|reply
Now your query engine might require you to specify the partitions or range of partitions you want to query on; you absolutely can use datepartition >=a and datepartition<b in your query. Iceberg seems to fix that and just let you use the timestamp; presumably the metadata is smart enough to exclude the partitions you don't care about.
[+] [-] hendiatris|9 months ago|reply
Just beware that one issue you can have is the limit of row groups per file (2^15).
[+] [-] Dowwie|9 months ago|reply
[+] [-] jonstewart|9 months ago|reply
That is the kind of metadata that is useful to push up, into something like DuckLake.
[+] [-] dkdcio|9 months ago|reply
It seems like this will just work out of the box, and just scale up to very reasonable data sizes. And the work from the DuckDB folks is typically excellent. It's clear they understand this space. Excited to try it out!
[+] [-] frisbm|9 months ago|reply
https://py.iceberg.apache.org/
[+] [-] mritchie712|9 months ago|reply
https://www.definite.app/blog/cloud-iceberg-duckdb-aws
[+] [-] akshayka|9 months ago|reply
(Disclosure, I am a developer of marimo.)
[+] [-] TheCondor|9 months ago|reply
datapains has some good stuff to get trino running and you can get a hivemetastore running with some hacking. I dorked around with it and then got the iceberg connector working with trino and see how it all works. I load data in to a dumb hive with a trino table pointed at it and then insert from select ... in to iceberg.
If the duck guys have some simple to get running stuff, they could probably start to eat everyone else' lunch.
[+] [-] wodenokoto|9 months ago|reply
https://delta-io.github.io/delta-rs/
[+] [-] loicalleyne|9 months ago|reply
[deleted]
[+] [-] georgewfraser|9 months ago|reply
I don’t think DuckLake itself will succeed in getting adopted beyond DuckDB, but I would not be surprised if over time the catalog just absorbs the metadata, and the original Iceberg format fades into history as a transitional form.
[+] [-] Dowwie|9 months ago|reply
Existing Lakehouse systems like Iceberg store crucial table information (like schema and file lists) as many small "metadata files" in cloud object storage (like S3). Accessing these files requires numerous network calls, making operations like query planning and updating tables inefficient and prone to conflicts. DuckLake solves this by putting all that metadata into a fast, transactional SQL database, using a single query to get what's needed, which is much quicker and more reliable.
[+] [-] kermatt|9 months ago|reply
[+] [-] jakozaur|9 months ago|reply
https://quesma.com/blog-detail/apache-iceberg-practical-limi...
Even Snowflake was using FoundationDB for metadata, whereas Iceberg attempts to use blob storage even for the metadata layer.
[+] [-] buremba|9 months ago|reply
They support syncing to Iceberg by writing the manifest and metadata files on demand, and they already have read support for Iceberg. They just fixed Iceberg's core issues but it's not a direct competitor as you can use DuckLake along with Iceberg in a very nice and bidirectional way.
[+] [-] prpl|9 months ago|reply
* number of snapshots
* frequent large schema changes
* lots of small files/row level updates
* lots of stats
The last one IIRC used to be pretty bad especially with larger schemas.
Most engines have ways to help with this - compaction, snapshot exportation, etc… Though it can still be up to the user. S3 tables is supposed to do some of this for you.
If metadata is below 1-5MB it’s really not an issue. Your commit rate is effectively limited by the size of your metadata and the number of writers you have.
I’ve written scripts to fix 1GB+ metadata files in production. Usually it was pruning snapshots without deleting files (relying on bucket policy to later clean things up) or removing old schema versions.
[+] [-] wodenokoto|9 months ago|reply
However, I constantly run in to problems with concurrent writes. I have a cloud function triggered ever x minutes to pull data from API and that’s fine.
But if I need to run a backfill I risk that that process will run at the same time as the timer triggered function. Especially if I load my backfill queue with hundreds of runs that needs to be pulled and they start saturating the workers in the cloud function.
[+] [-] isoprophlex|9 months ago|reply
[+] [-] ed_elliott_asc|9 months ago|reply
[+] [-] mehulashah|9 months ago|reply
[+] [-] nehalem|9 months ago|reply
[+] [-] nojvek|9 months ago|reply
Ducklake you can build petabyte scale warehouse with multiple readers and writer instances, all transactional on your s3, on your ec2 instances.
Motherduck has limitations like only one writer instance. Read replicas can be 1m behind (not transactional).
Having different instances concurrently writing to different tables is not possible.
Ducklake gives proper separation of compute and storage with a transactional metadata layer.
[+] [-] jtigani|9 months ago|reply
[+] [-] raihansaputra|9 months ago|reply
[+] [-] ChrisArchitect|9 months ago|reply
[+] [-] spenczar5|9 months ago|reply
[+] [-] buremba|9 months ago|reply
[+] [-] peterboncz|9 months ago|reply
[+] [-] a26z|9 months ago|reply
Same question for presto, trino, dremio, snowflake, bigquery, etc.
[+] [-] BewareTheYiga|9 months ago|reply
[+] [-] snthpy|9 months ago|reply
One thing I noticed is that the `table_stats` and `column_stats` tables aren't snapshot versioned. What are these used for and isn't that going to hurt timetravel queries (`SELECT COUNT(*) FROM tbl WHERE snapshot_id=<old_id>` as a simple example)?
[+] [-] formalreconfirm|9 months ago|reply
[+] [-] szarnyasg|9 months ago|reply
(diclaimer: I work at DuckDB Labs)
[+] [-] data_ders|9 months ago|reply
However, this mostly reads to me as thought experiment: > what if the backend service of an Iceberg catalog was just a SQL database?
The manifesto says that maintaining a data lake catalog is easier, which I agree with in theory. s3-files-as-information-schema presents real challenges!
But, what I most want to know is what's the end-user benefit?
What does someone get with this if they're already using Apache Polaris or Lakekeeper as their Iceberg REST catalog?
[1]: https://ducklake.select/manifesto/
[+] [-] peterboncz|9 months ago|reply
it adds for users the following features to a data lake: - multi-statement & multi-table transactions - SQL views - delta queries - encryption - low latency: no S3 metadata & inlining: store small inserts in-catalog and more!
[+] [-] anentropic|9 months ago|reply
[+] [-] RamtinJ95|9 months ago|reply
One question for me is, lets say i want to start using this today and at work we are running snowflake. I get that each analytics person would have to run duckdb + this extension on their local machines and point to the blob store and the database that is running datalake extension, for now that would be say a VM running duckdb. When I run the actual query where does the computation happen? And what if I want a lot of computation?
Is the solution currently to host a huge duckdb VM that everyone ssh's into and run their queries or how does that part work?
[+] [-] mritchie712|9 months ago|reply
And yes, to get more compute, you'd want to spin up a VM.
What's cool is you can do both (run locally for small stuff, run on VM for heavy workloads).
[+] [-] zhousun|9 months ago|reply
The main difference is to store metadata and stats also directly in SQL databases, which makes perfect sense for smaller scale data. In fact we were doing something similar in https://github.com/Mooncake-Labs/pg_mooncake, metadata are stored in pg tables and only periodically flush to actual formats like iceberg.
[+] [-] jamesblonde|9 months ago|reply
Do you need to put a REST API in front of it this duckdb instance to make it an Iceberg Catalog?
[+] [-] ivovk|9 months ago|reply