SHOW ANALYTICAL VIEWS

The show analytical views command lists all analytical views created in the current database, their definitions, and their state.

show analytical views;

There are four distinct states possible in analytical views.

The Four States of 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.

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aview   | base    | select statement     | state | size | aggregates   | dimen- | aview | #     | # added    | # deleted  | # updated  | create   | last     | last     |
| name    | 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:

  • Adding or dropping a partition in the base table does not effect the analytical views based on that table, and does not cause analytical views to become INVALID.
  • Changes in location, server properties, or table properties do not effect analytical views and do not cause them to become INVALID.

• The following changes trigger an INVALID state in analytical views:

  • Change to the name of the base table.
  • Change to the base table column name.
  • Change to the base table column type.
  • ADD, DROP, or REPLACE operation on the columns of the base table.