Repository navigation
[Proposal] Iceberg Materialized View Spec #6420
Description
Iceberg Materialized View Spec
Materialized View Spec google doc:
https://docs.google.com/document/d/1UnhldHhe3Grz8JBngwXPA6ZZord1xMedY5ukEhZYF-A/edit?usp=sharing
Background and Motivation
A materialized view is a database entity that stores a query definition as a logical table. The query is precomputed and the resulting data is served when the materialized view is queried. The cost of query execution is pushed to the precomputation step and is amortized over the query executions.
Goal
A common metadata format for materialized views enabling materialized views to be created, read and updated by different query engines.
Overview
The metadata of a MV (materialized view) consists of a query definition, a pointer to the precomputed data and lineage information to check if the MV is outdated. Iceberg materialized views are realized as a combination of an iceberg view with an underlying iceberg table that stores the precomputed data. The iceberg view is referred to as the “common view”, while the table is labeled “storage table”. The query definition of the materialized view is stored in the common view. The precomputed data is stored in the storage table. The storage table can have the states “fresh”, “outdated” or “invalid”. The lineage information is composed of data about the so-called “source tables”, which are the tables referenced in the query definition of the materialized view.
classDiagram
View --|> Table
class View{
+query definition
+storage table pointer
}
class Table{
+precomputed data
+lineage information
}
Operations on Materialized Views
Creation of materialized views
When a materialized view is created, an iceberg view with the query definition and an iceberg table to store the precomputed data is created. The storage table pointer of the view is set to the storage table.
Determining the storage table state
To determine the storage table state the current-version-id of the view is compared to the refresh-version-id in the lineage struct of the current storage table snapshot. If the versions don’t match, the storage table state is invalid.
If the versions match, the snapshot ids of the last refresh operation, which are stored in the lineage information, are compared to the current snapshot ids of the source tables. If the snapshot_id’s match for all source tables, the storage table state is fresh, otherwise it is outdated.
Refresh of materialized views
The exact mechanism for triggering a refresh is left up to individual systems. It might be triggered in a variety of ways. Some examples:
- Event driven if a change is detected to a source table
- At query time if a the precomputed data is determined to be stale
- On a regular schedule specified by a user.
- By a manual operation invoked by a user
The mechanism to perform the refresh operation uses the storage table state, which is determined by the procedure described above, to decide which action to take.
- If the storage table state is fresh, no action is required
- If the storage table state is invalid, a full refresh is required
- If the storage table state is outdated, it is left to the query engine to decide whether to perform a full or incremental refresh. The query engines specifies which refresh strategy was used in the snapshot summary of the storage table.
Query of materialized views
As a first step the storage table state is determined by following the procedure described above.
- If the storage table state is fresh, the precomputed data in the storage table is returned.
- If the storage table state is invalid, the precomputed data must not be used
- If the materialized view is outdated, the view property materialization.data.allow-stale decides whether a refresh operation has to be performed before the precomputed data is returned.
Commit Procedure
The catalog provides the basis for making atomic changes to the view metadata. Readers use the version of the view that was current when they loaded the view metadata and are not affected by changes until they refresh and pick up the new metadata.
Writers distinguish between changing the view or the storage table state.
Writers changing the view state, perform an “update view” operation using the catalog, optimistically assuming the view hasn’t changed since the metadata was loaded.
Writers changing the storage table state perform the commit in two steps. First, the writer creates a new table metadata file optimistically, assuming that the storage-table-pointer of the view will not be changed before the writer’s commit. It then updates the storage-table-pointer to the new location of table metadata. Second, the new view metadata gets committed to the catalog, requiring that the current storage-table-pointer of the catalog is equal to the previous storage-table-pointer. The commit is only successful when the second step succeeds.
Specification (Draft)
Terms
- Common view - Iceberg view that stores the query definition and a pointer to the precomputed data. It represents the main entity for the materialized view.
- Storage table - Iceberg table that stores the precomputed data of the materialized view.
- Storage table pointer - Reference to the metadata.json file of the storage table. It is stored as part of the common view and is used to obtain the metadata of the storage table.
- Source table - A table reference that occurs in the query definition of the materialized view. The materialized view depends on the data from the source table.
Metadata
The metadata for a materialized view can be considered an extension to the view metadata. To account for possible precomputed data a materialization field is added to the view spec. The materialization field stores the “storage table pointer” which references the location of the metadata.json file of the storage table. The following table shows the view metadata according to the v1 view spec compared to this proposal.
| v1 | materialized view | Field name | Description |
|---|---|---|---|
| required | required | view-uuid |
A UUID that identifies the view, generated when the view is created. Implementations must throw an exception if a view's UUID does not match the expected UUID after refreshing metadata |
| required | required | format-version |
An integer version number for the view format; must be 1 |
| required | required | location |
The view's base location; used to create metadata file locations |
| required | required | schemas |
A list of known schemas |
| required | required | current-version-id |
ID of the current version of the view (version-id) |
| required | required | versions |
A list of known versions of the view |
| required | required | version-log |
A list of version log entries with the timestamp and version-id for every change to current-version-id |
| optional | required | properties |
A string to string map of view properties |
| optional | materialization |
The storage table pointer[1]; used to retrieve the precomputed data from the storage table. If the value is null the entity is a common view, otherwise it is a materialized view |
Notes:
- Storage table pointer: The metadata of the storage table is stored in a metadata.json file and must not be stored in a catalog. The storage table pointer contains the location of the metadata.json file.
Storage Table Metadata
The lineage information, that is required to determine whether the storage table contains fresh data, is stored in the form of a lineage record in each storage table snapshot. The following table shows the snapshot metadata according to the v2 table spec compared to this proposal.
| v2 | materialized view | Field | Description |
|---|---|---|---|
| required | required | snapshot-id |
A unique long ID |
| optional | optional | parent-snapshot-id |
The snapshot ID of the snapshot's parent. Omitted for any snapshot with no parent |
| required | required | sequence-number |
A monotonically increasing long that tracks the order of changes to a table |
| required | required | timestamp-ms |
A timestamp when the snapshot was created, used for garbage collection and table inspection |
| required | required | manifest-list |
The location of a manifest list for this snapshot that tracks manifest files with additional metadata |
| required | required | summary |
A string map that summarizes the snapshot changes, including operation (see below) |
| optional | optional | schema-id |
ID of the table's current schema when the snapshot was created |
| optional | lineage |
A lineage record containing freshness information for materialized views. Optional field that only applies if the table is a storage table for a materialized view. |
Lineage record
The lineage record has the following fields:
| v1 | Field Name | Description |
|---|---|---|
| required | refresh-version-id |
Version id of the materialized view when the refresh operation was performed. |
| required | source-tables |
A List of source-table records. |
Source table record
The source table record has the following fields:
| v1 | Field Name | Description |
|---|---|---|
| required | uuid |
Uuid of the source table. |
| required | identifier |
A full identifier record containing catalog, namespace and table-name fields. |
| required | snapshot-id |
Snapshot id of the source table when the refresh operation was performed. |
Full Identifier record
The source table record has the following fields:
| v1 | Field Name | Description |
|---|---|---|
| required | catalog |
Catalog of the source table |
| required | namespace |
Namespace of the source table |
| required | table-name |
Name of the source table |
View Properties
Additional view properties that allow the configuration of the materialized view:
| Property | Default | Description |
|---|---|---|
materialization.data.allow-stale |
false |
Boolean that defines the query engine's behavior in case the source tables indicate the precomputed data isn't fresh. If set to false, a refresh operation has to be performed before the query results are returned. If set to true the data in the storage table gets returned without performing a refresh operation. |
Query engine metadata
To enable query engines to write metadata for logging and debugging, the query engine can write the following fields to the snapshot summary of the storage table:
| Property | Description |
|---|---|
materialization-refresh-strategy |
Which refresh strategy was used to perform the refresh operation. Can either be “FULL” or “INCREMENTAL”. |
Activity
JanKaul commentedon Dec 13, 2022
MV concept discussion:
https://docs.google.com/document/d/1QAuy-meSZ6Oy37iPym8sV_n7R2yKZOHunVR-ZWhhZ6Q/
nastra commentedon Dec 14, 2022
@JanKaul I think it would be great to get this out to the DEV mailing list to get more attention and input from people
rdblue commentedon Jan 3, 2023
Thanks for writing this up, @JanKaul! It's a good idea to specify how to maintain metadata for materialized views.
I think that the approach, to associate a view with some table that stores the materialized version, is a good design choice. And the metadata you have is a great start as well, although I think we can simplify or improve a couple of things.
First, I think we want to avoid keeping much state information in complex table properties. Those aren't designed for the purpose and make the table a bit difficult to use. What I recommend instead reusing the existing snapshot metadata structure to store what you need as snapshot properties. This approach has some really nice features in addition to being a bit simpler.
Each materialized view version is going to be stored in a snapshot, so I think it makes sense to take your idea of a "refresh" and simply store that metadata in snapshot properties. Then we don't need a "current" refresh ID, we can just reuse the current snapshot. Similarly, we wouldn't need a new ID, we could just use the snapshot ID, and the sequence number is automatically associated.
The metadata in snapshot properties would be very similar, but much smaller:
view_version_idtable.<identifier>In the table, I've also cut out a few of the base table properties...
type, just rely on everything being an Iceberg table upstream. We may not want to do this, but it makes everything simplefullrefresh).The nice thing about keeping upstream table UUIDs and snapshot IDs in the snapshot metadata is that it allows us to roll back the state of the view along with the upstream tables. For example, if we have an hourly job that produces bad data and an agg MV based on it, it is possible to roll back both the table and the MV to the matching state. We can also do incremental refresh based on the closest materialized snapshot, not just the latest.
I think we would still want some MV metadata in table properties:
materialized_view_format_versionview_identifierrefresh_strategyfullorincremental, default:fullWe may want additional metadata as well, like a UUID to ensure we have the right view. I don't think we have a UUID in the view spec yet, but we could add one.
I also moved the refresh strategy from the view to the MV table. I think we want to keep as much config on the table as possible, if it may differ between views. I could imagine a case where you might keep both incremental and full materialized versions or might want to have different partitioning specs for materialization, in which case you'd want that set on the table. I think the only thing I'd add to the view itself is the identifier for a materialized table.
The last thing I think we may want to change is to add a section of the proposal for view invalidation. Your property to allow stale data is on the right track, but we can actually detect when a table has not been updated in a way that affects the view query in a lot of cases. For example, if you plan the final query and get input splits for the tables in the view, you can check whether the input is based on a snapshot newer than the MV's base snapshot. If it isn't, then it is safe to use the materialized version. This is a little tricky since you have to account for whether files matching the final filter were deleted, but it should be entirely a metadata operation. I think it would be great to document this as part of a spec.
JanKaul commentedon Jan 3, 2023
Thank you @rdblue for your detailed comments. I really like your idea about storing the refresh metadata in the snapshot. It really simplifies the design. I have a couple of questions to better understand your proposal.
To make sure I understood you correctly: you prefer "Design 2: Table + attached common view"?
How exactly would you store the metadata in the snapshot? Would you store the metadata as properties of the
summaryfield? If the metadata is supposed to be stored as snapshot properties directly, a new ``format-version` has to be introduced.How are multiple upstream table references stored in the metadata? Is the second entry (
table.<identifier>) a list of table UUIDs and snapshot IDs? Because generally the materialized view definition can reference multiple upstream tables.Regarding the
typefield of thebase_tablerecord. I thought it might makes sense to allow future extensions. But you are right that only supporting "iceberg-metastore" tables would allow to further simplify the design.jackye1995 commentedon Jan 9, 2023
Thanks for the detailed proposal! Trying to catch up with the conversation here. (btw it would probably be more organized to move this to a google doc or a PR that updates the spec so we can have different threads of discussions, instead of nesting conversations here)
+1, should be added right away so it could be referenced by the MV table
Why do we really need to have a specific field for refresh method? To me full refresh is always a limitation when incremental cannot be performed. Is there any specific use case where a full refresh is preferred when incremental refresh could be done?
JanKaul commentedon Jan 10, 2023
Here is the link to the google doc: https://docs.google.com/document/d/1QAuy-meSZ6Oy37iPym8sV_n7R2yKZOHunVR-ZWhhZ6Q/
jackye1995 commentedon Jan 10, 2023
Thanks, added some comments there to kick start discussion
JanKaul commentedon Jan 13, 2023
As @jackye1995 said, the first thing we need to do is to decide on a general design. I therefore simplified the section on the design comparison in the google docs. Please feel free to add any points to the pros and cons.
wmoustafa commentedon Jan 19, 2023
I think refresh strategy is up to the engine to decide, and it could very well depend on some runtime factors/stats. So same view could leverage different refresh methods over time.
I think there could be a hybrid design between Design 1 and Design 2, where some of the MV props (e.g.,
allow-stale-data) and pointer to storage table are kept in common view (as it is the case now in Design 1), while underlying table snapshot information are stored in storage table snapshot properties as @rdblue suggested (as it is the case now in Design 2). Reasoning for the pointer direction (view to table or table to view is discussed in this thread in the design doc).allow-stale-datacould be enhanced toallow-stale-data-within-ms.jackye1995 commentedon Jan 19, 2023
+1, I think we are on the same page on this.
In my view it's still design 1. To me the difference of design 1 and 2 is what you read as the entry point, is that the view, or is that the storage table. So to me it's always the view. Maybe that's the confusion point that I totally missed, what you guys have been saying design 2 is just storing some information in storage table.
Information about refresh definitely makes sense to be stored in the storage table corresponding to each snapshot.
jackye1995 commentedon Jan 19, 2023
So just want to push the progress forward, I think we have some kind of loose consensus that:
Is that the right understanding? @rdblue @JanKaul @wmoustafa
wmoustafa commentedon Jan 19, 2023
Agreed. Reverse pointer to the view will be hard to maintain, so I am inclined to not having it.
I would say each view version could optionally map to a new storage table (say the view evolved in a backward incompatible way). Not sure if there is a strong use case for multiple tables for the same view version.
jackye1995 commentedon Jan 19, 2023
I am thinking about the case where based on the predicate operating on the view, we can choose intelligently what storage table to use.
wmoustafa commentedon Jan 19, 2023
I think this is potentially a generic use case for all tables, not only MV storage tables. Generically speaking, a table (MV or not), identified by a UUID, could have multiple storage layouts, and execution engines can choose the best storage layout. MV still points to a single UUID.
jackye1995 commentedon Jan 19, 2023
That's correct, but technically it could be argued that we can model everything as a view, backed by multiple storage tables, and we don't need to add more complexity into the table spec to support multiple storage layouts.
31 remaining items
[-]Iceberg Materialized View Spec[/-][+][Proposal] Iceberg Materialized View Spec[/+]szehon-ho commentedon Jan 27, 2024
Hi @JanKaul . Thanks for putting this together. I went through the detailed discussion and left my votes. I see there is general consensus to the "Open Questions" in the design docs, which are:
Is that correct? Then given that, as it takes a bit of time to parse the discussion to see the actual changes, I went ahead and summarized the additions we are making to the current metadata spec below (taken from your doc).
View Metadata
materializationstruct. If the value is null the entity is a common view, otherwise it is a materialized viewMaterialization Struct
Snapshot
source-tablerecords.Source Table Struct
Let me know if that looks right.
My 2c on this are:
If there is general consensus on the direction, it'd be great to move to on the actual spec pr change and discuss specifics there, as seems like this proposal has been sitting awhile? I can also help with that, if needed. Thanks.
JanKaul commentedon Jan 27, 2024
Hi @szehon-ho, thanks for trying to move the process of reaching consensus along. To be honest, I don't know how the community normally reaches consensus on these kinds of topics. But I still have the feeling that we are lacking the feedback from some key stakeholders. Until now I was waiting to get more feedback but it seems like creating the PRs is the right thing to move the proposal forward.
In any case it might be good to bring this up at a community sync.
I would argue for 2 PRs, one for the changes to the view metadata and one for the changes to the table metadata.
Regarding the open questions
Regarding your 2cents
szehon-ho commentedon Jan 30, 2024
Great, this all makes sense to me except following points:
As we clarified in the google doc, it seems (at least among the local thread) we are moving to Option 2: https://docs.google.com/document/d/1UnhldHhe3Grz8JBngwXPA6ZZord1xMedY5ukEhZYF-A/edit?pli=1&disco=AAABFByy0mU
Did we consider making this property configurable? We should at least store what the materialized view refresh strategy was used in the snapshot for debugging? We can discuss on the google doc.
So Im open to either, we can make a PR, or fill out the Specification Draft part of the doc ? The main barrier right now to more folks reviewing is that the doc is just a list of options and long threads that makes it really hard to see where the consensus is, it took me quite some time to follow. Having a central section of what concrete additions we are proposing will greatly streamline the review experience. Feel free to take my summarized spec changes in my previous comment as a starting point.
Let me know if you need help with it. Thanks
JanKaul commentedon Jan 30, 2024
Sorry, for Question 3 I mixed up Options 1 & 2. You are right there is consensus on 2.
Great, it seems like all open questions have been answered. I will update the Specification Draft accordingly. Additionally we can create the PRs to make the spec changes. If you are interested you can create the PRs otherwise I will do it tomorrow.
szehon-ho commentedon Jan 30, 2024
Sounds great, let's put the current consensus on the specification draft first then
JanKaul commentedon Jan 31, 2024
I've updated the Specification Draft. Please let me know if you think certain changes are in order.
szehon-ho commentedon Jan 31, 2024
Thanks its a lot clearer now. I guess we still have some open questions that came up, which can talk there. I will try to get some other folks to look at it there as well.
wmoustafa commentedon Jan 31, 2024
The specification draft as in the PR or the Google doc? If there are open questions, let us continue them on the Google doc instead of creating a third place for discussion? It is already somewhat hard to track.
szehon-ho commentedon Feb 1, 2024
Yea , we mean , I think we will continue on the google doc until the open questions addressed. @JanKaul made the specification draft section over there, it should be clearer now.
szehon-ho commentedon Feb 16, 2024
I do realize we are still ironing out the details, but as a data point, doing a quick sanity check with @marton-bod of Trino , it looks like the general proposal here of the metadata to be added is in line with what has been added to Trino's native implementation of Iceberg materialized view.
wmoustafa commentedon Mar 13, 2024
Linking the implementation PR: #9830 for one of the options. It uses the option of view + separate Iceberg table linked from the view metadata. Storage table is represented by its catalog identifier and managed independently by the catalog.
manuzhang commentedon Mar 14, 2024
Thanks @wmoustafa. It does show the simplicity of this option. Would you mind rebasing on latest main branch such that people can try out?
JanKaul commentedon May 21, 2024
Closed in favor of the new issue: #10043