Is there any way to find out when exactly and at what time AV’s went to a stale mode?
@Purnesh it’s usually due to DDL operations on the base/source table. You can look at the “describe formatted xxtablename” to get the last DDL update on the table which would be indicative of the staleness of the Analytical View.
.... | spark.sql.sources.schema.partCol.0 | deposit_date | |` transient_lastDdlTime | 1567002768 | ` | # Storage Information | NULL | NULL ....
How to get all Invalid state analytical views from a database? Do we have any query to fire?
Below is also a more detailed enumeration of different altering operations that can impact Analytical View states.
|Operation||Type||AV in Invalid State?|
|Base Table is dropped||DDL||Yes|
|Base View is changed||DDL||Yes|
|Dataset from SQL is changed underneath AVs||DDL||Yes|
|Base Table is renamed||DDL||Yes|
|Base Table is dropped, and recreated||DDL||Yes|
|Base Table data is overwritten using INSERT OVERWRITE||Write||No. AVs will be in a STALE state and need refreshing.|
|Base Table data is appended using INSERT||Write||No. If table is not partitioned, the AV will perform a full-refresh next time it runs. However if the table is partitioned, the AV will perform an incremental refresh using the newly added, changed, or removed partitions.|
|Column is dropped||DDL||Yes if AV contains the column that was dropped. Otherwise AV will be still useable.|
|Column type is altered||DDL||Yes if AV contains the column that was altered. Otherwise AV will be still useable.|
|Column is renamed||DDL||Yes if AV contains the column that was altered. Otherwise AV will be still useable.|
|Column is added||DDL||No.|
@Venkatesh_Kumar you can run SHOW EXTENDED ALL ANALYTICAL VIEWS to get a list of Analytical Views in the system, but there’s no way currently to limit this on a query basis to just the INVALID state ones. Take a look at our documentation for more info on this command: