findinpath commented on code in PR #11041:
URL: https://github.com/apache/iceberg/pull/11041#discussion_r4539283728
##########
format/view-spec.md:
##########
@@ -322,3 +432,182 @@
s3://bucket/warehouse/default.db/event_agg/metadata/00002-(uuid).metadata.json
} ]
}
```
+
+### Materialized View Example
+
+Imagine the following operation, which creates a materialized view that
precomputes daily event counts:
+
+```sql
+USE prod.default
+```
+
+```sql
+CREATE MATERIALIZED VIEW event_agg_mv (
+ event_count COMMENT 'Count of events',
+ event_date)
+COMMENT 'Precomputed daily event counts'
+AS
+SELECT
+ COUNT(1), CAST(event_ts AS DATE)
+FROM events
+GROUP BY 2
+```
+
+The writer creates the storage table `event_agg_mv__storage` and then commits
the materialized view metadata, which looks as follows:
+
+```
+s3://bucket/warehouse/default.db/event_agg_mv/metadata/00001-(uuid).metadata.json
+```
+
+```json
+{
+ "view-uuid": "b2a12651-3038-4a72-8a31-5027ab84da35",
+ "format-version" : 1,
+ "location" : "s3://bucket/warehouse/default.db/event_agg_mv",
+ "current-version-id" : 1,
+ "properties" : {
+ "comment" : "Precomputed daily event counts"
+ },
+ "versions" : [ {
+ "version-id" : 1,
+ "timestamp-ms" : 1573518431292,
+ "schema-id" : 1,
+ "default-catalog" : "prod",
+ "default-namespace" : [ "default" ],
+ "summary" : {
+ "engine-name" : "Spark",
+ "engine-version" : "3.4.1"
+ },
+ "representations" : [ {
+ "type" : "sql",
+ "sql" : "SELECT\n COUNT(1), CAST(event_ts AS DATE)\nFROM
events\nGROUP BY 2",
+ "dialect" : "spark"
+ } ],
+ "storage-table" : {
+ "namespace" : [ "default" ],
+ "name" : "event_agg_mv__storage"
+ }
+ } ],
+ "schemas": [ {
+ "schema-id": 1,
+ "type" : "struct",
+ "fields" : [ {
+ "id" : 1,
+ "name" : "event_count",
+ "required" : false,
+ "type" : "int",
+ "doc" : "Count of events"
+ }, {
+ "id" : 2,
+ "name" : "event_date",
+ "required" : false,
+ "type" : "date"
+ } ]
+ } ],
+ "version-log" : [ {
+ "timestamp-ms" : 1573518431292,
+ "version-id" : 1
+ } ]
+}
+```
+
+After a refresh operation, the storage table's snapshot summary contains the
`refresh-state` property.
+The following is an example of the `refresh-state` JSON value stored in the
snapshot summary of the storage table:
+
+```json
+{
+ "view-version-id" : 1,
+ "refresh-start-timestamp-ms" : 1573518435000,
+ "source-states" : [ {
+ "type" : "table",
+ "catalog" : "prod",
+ "namespace" : [ "default" ],
+ "name" : "events",
+ "uuid" : "d4a10b5c-1e8a-4b72-9d67-3f4a8c9e1b2d",
+ "snapshot-id" : 6148331192489823102,
+ "ref" : "main"
+ } ]
+}
+```
+
+## Appendix B: Example freshness criteria and recorded dependencies
+
+Each engine decides when it can read a materialized view's storage table
instead of evaluating the view query. This appendix calls that decision the
engine's freshness criteria. The spec does not define freshness criteria, but
they determine what a writer records in the refresh state. During a refresh,
the engine applies its criteria to each materialized view the query reads and
records the dependencies that a reader applying the same criteria needs to
check. Each example below states its freshness criteria and the refresh state
that follows from them.
+
+Both examples refresh `event_agg_30_day_mv`, which reads the materialized view
`event_agg_mv` from [Appendix A](#materialized-view-example). `event_agg_mv` in
turn reads the Iceberg table `events`:
+
+```sql
+USE prod.default
+```
+
+```sql
+CREATE MATERIALIZED VIEW event_agg_mv (
+ event_count COMMENT 'Count of events',
+ event_date)
+COMMENT 'Precomputed daily event counts'
+AS
+SELECT
+ COUNT(1), CAST(event_ts AS DATE)
+FROM events
+GROUP BY 2
+```
+
+```sql
+CREATE MATERIALIZED VIEW event_agg_30_day_mv (
+ event_count_30_day COMMENT 'Count of events in the 30 days up to the date',
+ event_date)
+COMMENT 'Precomputed 30-day event counts'
+AS
+SELECT
+ SUM(event_count) OVER (
+ ORDER BY event_date
+ RANGE BETWEEN INTERVAL 29 DAYS PRECEDING AND CURRENT ROW),
+ event_date
+FROM event_agg_mv
+```
+
+### Example 1: Fresh if refreshed recently
+
+Freshness criteria: an MV is fresh if its last refresh started within a
user-defined staleness window, regardless of changes to its sources. PostgreSQL
materialized views follow this approach.
+
+Recorded state: the criteria never inspect sources, so `source-states` is
empty.
+
+```json
+{
+ "view-version-id": 1,
+ "refresh-start-timestamp-ms": 1736899200000,
+ "source-states": []
+}
+```
+
+### Example 2: Fresh if dependencies are unchanged
+
+Freshness criteria: an MV is fresh if none of its dependencies changed since
its last refresh. A reader serves a fresh MV from its storage table and
evaluates a stale one, applying the same test to each nested MV.
Proof-of-concept implementations in Spark and Trino follow this approach.
Review Comment:
IIUC, the scenario exposed here is
```
CREATE MATERIALIZED VIEW event_agg_mv
...
CREATE MATERIALIZED VIEW event_agg_30_day_mv
...
REFRESH MATERIALIZED VIEW event_agg_30_day_mv
```
In this case, the event_agg_30_day_mv is "stale" because it hasn't been
refreshed yet.
Things would be different if we'd be dealing with
```
CREATE MATERIALIZED VIEW event_agg_mv
...
CREATE MATERIALIZED VIEW event_agg_30_day_mv
...
REFRESH MATERIALIZED VIEW event_agg_mv
REFRESH MATERIALIZED VIEW event_agg_30_day_mv
```
Maybe it would be worthwhile to mention this situation as well for clarity
sake when does a storage table land in the source states.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
