SHOW ANALYTICAL VIEWS

The SHOW ANALYTICAL VIEW and SHOW EXTENDED ANALYTICAL VIEW commands list the names of all existing analytical views in the current database.

This article describes how to show analytical views.

This topic contains the following sections:

Syntax

SHOW [EXTENDED] [ALL] ANALYTICAL VIEWS [IN database_name] [LIKE pattern];

Parameters

database_name
The name of the database
pattern
This is a quoted string literal, using * (wildcards) and | (pipes) for alternation. The LIKE keyword is optional. The system stores object names in lowercase.

Notes

EXTENDED

This reports all the information for analytical views created in the current database: their definitions, their state, the SQL statement associated with the analytical view, and so on.

This option provides information on Analytical View States and Effect of Changes to Tables:

  • Analytical View States

    This operation shows the state of each analytical view. There are four distinct states possible in analytical views: UNUSABLE, INVALID, STALE, and FRESH.

  • Effect of Changes to Tables

    To understand why the states of analytical views change, see Effect of Changes to Tables.

ALL

The keyword ALL affects all analytical views in the cluster.

IN
The optional IN database_name clause lets you specify the database of the search.
LIKE
The optional LIKE pattern clause lets you match the naming pattern of the analytical view.

Examples

Show All Analytical Views in Cluster
SHOW ALL ANALYTICAL VIEWS
+--------------------------+
| name                     |
+--------------------------+
| complex_sales.av_1_ds_12 |
| complex_sales.av_2_ds_12 |
| complex_sales.av_3_ds_12 |
| default.av_1_ds_782      |
| default.av_2_ds_782      |
| e8.camping_sales_av_1    |
| geo.av1                  |
| geo.av2                  |
| geo.av3                  |
+--------------------------+
Show All Analytical Views in Specified Database
SHOW ANALYTICAL VIEWS IN geo
+--------------------------+
| name                     |
+--------------------------+
| geo.av1                  |
| geo.av2                  |
| geo.av3                  |
+--------------------------+
Show All Analytical Views with Names that Match the Specified Pattern
SHOW ALL ANALYTICAL VIEWS LIKE '*sales*'
+--------------------------+
| name                     |
+--------------------------+
| complex_sales.av_1_ds_12 |
| complex_sales.av_2_ds_12 |
| complex_sales.av_3_ds_12 |
| e8.camping_sales_av_1    |
+--------------------------+
Show Extended

A sample output of this command looks something like this:

SHOW EXTENDED ANALYTICAL VIEWS;
+-----------------+-----------------------------------------+-------+-------+---------+---------+---------+-----------+--------+--------+-------+----------+-----------+------------+---------------------------+
|                 |                                         |       |       | in      | size    |         | incremen- | base   | base   |       |          | last      | last       |                           |
|                 |                                         | row   | row   | mem     | on      | file    | tally re- | lview  | table  |       | create-  | attempted | successful |                           |
| name            | definition                              | count | size  | size    | disk    | format  | freshable | name   | name   | state | time     | refresh   | refresh    | error_message             |
+-----------------+-----------------------------------------+-------+-------+---------+---------+---------+-----------+--------+--------+-------+----------+-----------+------------+---------------------------+
| event_data.     | CREATE ANALYTICAL VIEW                  | 137   | 36.02 |  4935.0 |  5864.0 | PARQUET | false     |        | event_ | FRESH | Nov 12   | Nov 14    | Nov 14     | NULL                      |
| av_dist_app_    | event_data.av_dist_app_id_platform_cnt  |       |       |         |         |         |           |        | data.  |       | 22:06:23 | 05:10:09  | 05:10:09   |                           |
| id_platform_cnt | STORED AS PARQUET AS SELECT             |       |       |         |         |         |           |        | events |       | UTC 2018 | UTC 2018  | UTC 2018   |                           |
|                 | app_id av_app_id, platform av_platform, |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | count_no_finalize(*) `_c2` FROM         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | event_data.events GROUP BY 1, 2         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | CREATE ANALYTICAL VIEW                  | 25    |  9.40 |   235.0 |  1927.0 | PARQUET | false     |        | event_ | FRESH | Nov 12   | Nov 14    | Nov 14     | NULL                      |
| av_dist_sdk_    | event_data.av_dist_sdk_version_platform |       |       |         |         |         |           |        | data.  |       | 22:06:25 | 05:10:27  | 05:10:27   |                           |
| version_        | STORED AS PARQUET AS SELECT DISTINCT    |       |       |         |         |         |           |        | events |       | UTC 2018 | UTC 2018  | UTC 2018   |                           |
| platform        | sdk_version av_sdk_version, platform    |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | av_platform FROM event_data.events      |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | CREATE ANALYTICAL VIEW                  | 1314  | 37.66 | 49485.0 | 28385.0 | PARQUET | true      |        | event_ | FRESH | Nov 14   | Nov 14    | Nov 14     | NULL                      |
| av_event_counts | (year,month,day) event_data.av_event_   |       |       |         |         |         |           |        | data.  |       | 17:17:42 | 17:18:35  | 17:18:35   |                           |
|                 | counts PARTITIONED BY STORED AS PARQUET |       |       |         |         |         |           |        | events |       | UTC 2018 | UTC 2018  | UTC 2018   |                           |
|                 | AS SELECT name name, sum_no_finalize(1) |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | `_c1`, year year, month month, day day  |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | FROM event_data.events                  |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | GROUP BY 1, 3, 4, 5                     |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | NULL                                    | 0     |   0.0 |     0.0 |     0.0 | NULL    | false     | NULL   | NULL   | INVA- | NULL     | NULL      | NULL       | org.apache.impala.common. |
| av_history      |                                         |       |       |         |         |         |           |        |        | LID   |          |           |            | AnalysisException: Could  |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            | not resolve table         |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            | reference: 'event_data.   |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            | events_history'           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | CREATE ANALYTICAL VIEW                  | -1    |  15.0 |   -15.0 |    -1.0 | KUDU    | true      |        | kudu_  | FRESH | Dec 05   | Dec 05    | Dec 05     | NULL                      |
| av_kudu_seq     | event_data.av_kudu_seq PRIMARY KEY      |       |       |         |         |         |           |        | aviews.|       | 17:30:39 | 17:30:52  | 17:31:46   |                           |
|                 | ( c1_r_a_n_g_e9999,   c1,   c2 )        |       |       |         |         |         |           |        | base_  |       | UTC 2018 | UTC 2018  | UTC 2018   |                           |
|                 | PARTITION BY HASH (c1) PARTITIONS 10,   |       |       |         |         |         |           |        | tab4   |       |          |           |            |                           |
|                 | RANGE (c1_r_a_n_g_e9999, c1) (...)      |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | SEQUENCE COLUMN c1 STEP SIZE 2          |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | STORED AS KUDU AS SELECT c1 c1, c2 c2,  |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | sum_no_finalize(1) `_c2` FROM           |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 | kudu_aviews.base_tab4 GROUP BY c1, c2   |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | CREATE ANALYTICAL VIEW                  | -1    |   0.0 |    -0.0 |     0.0 | PARQUET | false     |        | event_ | STALE | Dec 05   | Dec 05    | Dec 05     | NULL                      |
| av_platform_    | event_data.av_platform_like_linux       |       |       |         |         |         |           |        | data.  |       | 17:26:24 | 17:27:07  | 17:27:07   |                           |
| like_linux      | STORED AS PARQUET AS SELECT name name,  |       |       |         |         |         |           |        | plat-  |       | UTC 2018 | UTC 2018  | UTC 2018   |                           |
|                 | descr descr FROM event_data.            |       |       |         |         |         |           |        | form_  |       |          |           |            |                           |
|                 | platform_details WHERE name             |       |       |         |         |         |           |        | details|       |          |           |            |                           |
|                 | LIKE '*linux*'                          |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
| event_data.     | CREATE ANALYTICAL VIEW                  | -1    |   7.0 |    -7.0 |     0.0 | PARQUET | false     |        | event_ | UNU-  | Dec 05   |           |            | NULL                      |
| platform_av     | event_data.platform_av STORED AS        |       |       |         |         |         |           |        | data.  | SABLE | 17:25:15 |           |            |                           |
|                 | PARQUET AS SELECT name name,            |       |       |         |         |         |           |        | plat-  |       | UTC 2018 |           |            |                           |
|                 | sum_no_finalize(1) `_c1` FROM           |       |       |         |         |         |           |        | form_  |       |          |           |            |                           |
|                 | event_data.platform_details GROUP BY 1  |       |       |         |         |         |           |        | details|       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
|                 |                                         |       |       |         |         |         |           |        |        |       |          |           |            |                           |
+-----------------+-----------------------------------------+-------+-------+---------+---------+---------+-----------+--------+--------+-------+----------+-----------+------------+---------------------------+
Fetched 7 row(s) in 0.22s

Some analytical tables, such as event_data.av_kudu_seq, show negative row counts. Currently, Arcadia Engine does not collect statistical information on kudu tables and analytical views.

The analytical views event_data.av_platform_like_linux and event_data.platform_av show negative row counts because the base table event_data.platform_details does not have statistics collected on it. After running the COMPUTE STATS command on the event_data.platform_details table, run SHOW EXTENDED ANALYTICAL VIEWS again to see the missing values.

The av_history analytical view is invalid, and many of its values are NULL. The error message indicates the event_data.events_history table is missing; this is the original base table for the analytical view.