What're the different types of Analytical View states and what do they mean?

Hi Tadd,

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:

http://documentation.arcadiadata.com/latest/#pages/topics/op-aview-show_2.html