When I run SELECT * FROM system.metadata.materialized_views; the freshness of all my materialized views is unknown, even though I just refreshed them. Why is that?
It actually MIGHT be working fine (i.e. “as designed”) as I think we’re getting the same thing. Here is my setup.
create materialized view test_mv
AS select * from tpch.sf1.nation;
select name, freshness, last_fresh_time
from system.metadata.materialized_views
where name = 'test_mv';
refresh materialized view test_mv;
Looking at the freshness column definition of the metadata.materialized_views table described in System connector — Trino 451 Documentation, it states that “Queries on the materialized view access the storage table if not STALE” so a bit like a boolean in that regards.
That coupled with the last_fresh_time should give you an indicator of how “fresh” the storage table is. You can always SEE if the actual storage table is read by looking into the query details UI.
In general, this is the case (especially on data lake tables) as we don’t really know (and usually don’t know how to “know”) if the data behind the backing query itself has changed. That was especially true in the Hive MV I tested with above.
BUT, if I had an Iceberg table and created an Iceberg MV on top of it, it should work as you expect since Iceberg manages snapshots/versions and the “freshness” can be determined.
I’m using Starburst galaxy which creates all MVs as Iceberg format (with SEP, it depends on if you are using the Hive or Iceberg connector) and for my tables, I can specifiy a type property to pick the underlying connector type. This all fundamentally works the same in SEP.
create table ice_nation
with (type='iceberg')
as select * from tpch.sf1.nation;
refresh materialized view ice_nation_mv;
update ice_nation
set comment = 'change all';
refresh materialized view ice_nation_mv;
I hope this helps.
1 Like
That makes sense. Thank you, Lester!
1 Like