Materialize: Function to report last update time of a view

[krishmanoh2]

A simple function to retrieve last update time of an mv - (this would be equivalent to SCN/LSN in other systems) can let the end user know the freshness of the data. It also allows for quick way to check and see if anything broken.

[ruchirK]

I think we have mz_materialization_frontiers that indicates the timestamp a materialized view is materialized as of (roughly - i forget the exact semantics). I think that should be what you want - I can double check in a bit

[frankmcsherry]

Further discussion suggests that the more specific ask is a way to connect the most recent update timestamp from mz_materialization_frontiers back to a source-specific "progress", e.g. Kafka offsets or source DB transaction identifiers.

[elindsey]

The main piece of data we're missing today is a historical offset/timestamp mapping. We should tackle this after Ruchir's timestamper work (unifying the offset/ts mapping across multiple source instantiations and persisting it to disk). I think the main challenge at that point will be figuring out how to structure this such that we don't need to keep a full ts to offset map in memory (we can drop all pairs less than the min ts across dependent views).

[umanwizard]

@krishmanoh2 you were explaining why this is useful but I was having trouble hearing you due to my laptop speakers' sound quality ... can you repeat it here?

[krishmanoh2]

This is a frequent ask so as to be able to associate a kafka offset with the data in a view. Allows to validate the results and also make sure the view is up-to date.

[frankmcsherry]

I think the main challenge at that point will be figuring out how to structure this such that we don't need to keep a full ts to offset map in memory (we can drop all pairs less than the min ts across dependent views).

I think one starting point could be the collection of (topic, part, offset) that evolves with timestamps, in correspondence with the assignment of timestamps to prefixes of each topic and part thereof. This will have bounded size and not retain history, yet be sufficient for joining with results to surface the (topic, part, offset) through which the results are current.

[elindsey]

@ruchirK spent some time scoping this down.

The current customer ask doesn't require tracking offsets through the end of a dataflow - all that's necessary right now is a way to view offsets for each row in a materialized source. Currently we do track offsets (see https://github.com/MaterializeInc/materialize/blob/main/src/dataflow-types/src/types.rs#L549), but it doesn't appear to be exposed for Kafka, and Kafka further requires a partition ID for it to be useful.

So the concrete task is to:

1. Expose the mz_offset column that we are already tracking (oddly we do expose mz_line_no for materialized file sources, but don't appear to expose mz_offset for materialized Kafka sources)
2. Wire a partition ID through in the same way that offsets are currently tracked

At the end of it, this query should have two new columns - one tracking offset and one tracking partition per row:

materialize=> create materialized source k from kafka broker 'localhost:9092' topic 'input-sink-u37-1612550827-616864841296588674' format avro using confluent schema registry 'http://localhost:8081';
CREATE SOURCE
materialize=> select * from k;
 before | after | transaction 
--------+-------+-------------
        | (1,1) | (1)
        | (1,7) | (3)
        | (2,2) | (1)
        | (3,1) | (2)
        | (4,2) | (2)

This is an excellent onboarding task because it's reasonably small scope but will require getting familiar with sources, how tables are represented, and some amount of dataflow.

[ruchirK]

I'm assigning this to myself as I will be the onboarding buddy for a new engineering joining soon and I think this is a good ticket