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:
SHOW [EXTENDED] [ALL] ANALYTICAL VIEWS [IN database_name] [LIKE pattern];
*
(wildcards) and
|
(pipes) for alternation. The LIKE
keyword is
optional. The system stores object names in lowercase.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:
This operation shows the state of each analytical view. There are four distinct
states possible in analytical views: UNUSABLE
, INVALID
,
STALE
, and FRESH
.
To understand why the states of analytical views change, see Effect of Changes to Tables.
The keyword ALL
affects all analytical views in the cluster.
IN database_name
clause lets you specify the database of
the search.LIKE pattern
clause lets you match the naming pattern of the analytical view.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 ANALYTICAL VIEWS IN geo
+--------------------------+
| name |
+--------------------------+
| geo.av1 |
| geo.av2 |
| geo.av3 |
+--------------------------+
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 |
+--------------------------+
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.