The show extended analytical views
command takes into account the
prioritized load of analytical views and their underlying base tables, and reports all the
information on analytical views created in the current database: their definitions, their
state, the SQL statement associated with the analytical view, the dimensions, aggregates,
partitioning columns, and so on.
show extended analytical views;
There are four distinct states possible in analytical views.
State | Description |
---|---|
UNUSABLE |
Analytical view is created, but has never been refreshed. |
INVALID |
Base tables(s) were subject to DDL operation after most recent refresh. The analytical view is not consistent with base table definitions, and cannot be used. |
STALE |
Base table data was changed after most recent refresh. The analytical view may be used, but the query results may be incomplete/inaccurate. |
FRESH |
The data in the analytical view is current, or up-to-date with the base table(s). The analytical view may be used to safely route the query, and will return accurate results. |
A sample output of the show analytical views
command looks something like this. Note that we made changes to the format to fit the table on the documentation page.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | name | base | select statement | state | size | aggregates | dimen- | aview | # | # added | # deleted | # updated | create | last | last | | | table | | | | | sions | part. | aview | base table | base table | base table | time | attempt. | success. | | | | | | | | | names | part. | partitions | partitions | partitions | | refresh | refresh | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | geo.av1 | geo.t_2 | SELECT | FRESH | 0.0 | ["count(a)"] | ["b"] | "" | N/A | N/A | N/A | N/A | Sep 13 | Sep 13 | Sep 13 | | | | count_no_finalize(a) | | | | | | | | | | 17:11:10 | 17:15:06 | 17:15:06 | | | | '_c0', b b FROM | | | | | | | | | | UTC 2016 | UTC 2016 | UTC 2016 | | | | geo.t_2 GROUP BY 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | geo.av2 | geo.t_2 | SELECT | UNUSA | 0.0 | ["sum(a)"] | ["b"] | "" | N/A | N/A | N/A | N/A | Sep 13 | | | | | | sum_no_finalize(a) | BLE | | | | | | | | | 17:13:30 | | | | | | '_c0', b b FROM | | | | | | | | | | UTC 2016 | | | | | | geo.t_2 GROUP BY 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | geo.av3 | geo.t_3 | SELECT | INVAL | 0.0 | "" | "" | "" | N/A | N/A | N/A | N/A | Sep 13 | Sep 13 | Sep 13 | | | | sum_no_finalize(a) | ID | | | | | | | | | 17:14:24 | 17:15:13 | 17:15:13 | | | | '_c0', b b FROM | | | | | | | | | | UTC 2016 | UTC 2016 | UTC 2016 | | | | geo.t_3 GROUP BY 2 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | geo.av4 | geo.t_4 | SELECT | STALE | -1.0 | ["sum(a)"] | "" | "" | N/A | N/A | N/A | N/A | Sep 13 | Sep 13 | Sep 13 | | | | sum_no_finalize(a) | | | | | | | | | | 17:14:49 | 17:15:22 | 17:15:22 | | | | '_c0' FROM geo.t_4 | | | | | | | | | | UTC 2016 | UTC 2016 | UTC 2016 | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Various database events can change the state of an analytical view. Some operations have no effect, while others trigger a state change to INVALID
.
Operations that have no effect include:
INVALID
.INVALID
.The following changes trigger an INVALID
state in analytical views:
ADD
, DROP
, or REPLACE
operation on the columns of the base table.