[Proposal] Iceberg Materialized View Spec

[JanKaul]

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
    }

Loading

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:

1. 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”.

[JanKaul]

MV concept discussion:
https://docs.google.com/document/d/1QAuy-meSZ6Oy37iPym8sV_n7R2yKZOHunVR-ZWhhZ6Q/

[nastra]

@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]

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:

v1	Snapshot property	Description
required	view_version_id	version ID of the view that was materialized
required	table.<identifier>	table UUID and snapshot ID for the table identified by that was read

In the table, I've also cut out a few of the base table properties...

• Rather than type, just rely on everything being an Iceberg table upstream. We may not want to do this, but it makes everything simple
• Rather than having a type for Hadoop vs Metastore tables, this makes no distinction. We should not design much for Hadoop tables because they are not recommended.
• Removed properties. We can include a catalog name in the table identifier, and adding the table UUID ensures that we always use the same upstream table (or have to recompute a full refresh).

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:

v1	Table property	Description
required	materialized_view_format_version	The MV spec version used
required	view_identifier	Identifier for the view that is materialized
optional	refresh_strategy	full or incremental, default: full

We 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]

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.

1. To make sure I understood you correctly: you prefer "Design 2: Table + attached common view"?

2. How exactly would you store the metadata in the snapshot? Would you store the metadata as properties of the summary field? If the metadata is supposed to be stored as snapshot properties directly, a new ``format-version` has to be introduced.

3. 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 type field of the base_table record. 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]

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)

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

+1, should be added right away so it could be referenced by 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

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]

Here is the link to the google doc: https://docs.google.com/document/d/1QAuy-meSZ6Oy37iPym8sV_n7R2yKZOHunVR-ZWhhZ6Q/

[jackye1995]

Thanks, added some comments there to kick start discussion

[JanKaul]

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]

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-data could be enhanced to allow-stale-data-within-ms.

[jackye1995]

while underlying table snapshot information are stored in storage table snapshot properties

+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]

So just want to push the progress forward, I think we have some kind of loose consensus that:

1. view + storage table is likely the general approach to go
2. view stores pointer to the storage table, or potentially multiple storage tables
3. storage table should store the information about each refresh as table snapshot properties, some reverse pointer to the view is also good to have

Is that the right understanding? @rdblue @JanKaul @wmoustafa

[wmoustafa]

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]

Not sure if there is a strong use case for multiple tables for the same view version.

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]

I am thinking about the case where based on the predicate operating on the view, we can choose intelligently what storage table to use.

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.