In the Command Line Utility, use the verbose
output mode to view
detailed information on analytical view recommendations.
In the verbose
output mode you can view detailed information on analytical
view recommendations, syntax of the query, and statistics of the base tables. Information in
this output can help the admin to debug and update the query.
To view a brief summary of the analytical view recommendations, analytical view create statements, and syntax of the input query, use the Default Output Mode.
To view only the analytical view create statements and syntax of the input query, use the Recommendation-Only Output Mode.
To display analytical view recommendations in verbose
mode, follow these
instructions:
To see the
output in Verbose
mode, enter a command with the following syntax, either
the long or short form. See Getting Started with the Command Line Utility.
./arcviz util recommendation --dcname=<data_connection_name> --file=<file_path to the queries file> --verbose
./arcviz util recommendation --dcname=<<data_connection_name> --file=<file_path to the queries file> -v
Recommended Analytival Views:
-- <unique aview name 1> : <aview create statement>
-- <unique aview name 1> : <aview create statement>
.....
-- <unique aview name n> : <aview create statement>
------------------------------------------------------
Recommendation Details:
-- Q1: <input query 1>
-- BT: <name of base table> is a base table in <format of the table> format.
<Stats of the table separated by |> [Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0]
-- <Status of recommendation [RECOMMENDATION or NO RECOMMENDATION or ROUTES TO AN AVIEW]
<Stats of the aview in case of recommendation or routng to an existing aview>
<Exact query block that accelerated>
-- Q2: <input query 2>
....
-- Qn: <input query n>
In our example, name of the data connection is Recommendation and queries are accessed from the text.txt file.
The information in the output is displayed in the following two sections:
CREATE ANALYTICAL VIEW
statements, and has the
following information: Table Name
Name of the base table that appears in the form
database_name.table_name
.
Names of the recommended analytical view. The name of the analytical view appears in
the form database_name.aview_name
.
Format of the analytical view in PARQUET
.
Syntax of the select query.
Recommended Details section shows all the queries that are executed. Each query displays the following information:
Q1Number and syntax of the select query.
Base TableName of the base table that appears in the form
database_name.table_name
.
Logical view based on one or more base tables. It appears in the form
database_name.table_name
.
Format of the base table. For example, PARQUET
or
Text
.
Number of rows in the table.
Memory SizeThe size of the analytical view when stored in memory, in Bytes; on recommended analytical views, this number is an estimation.
On Disk SizeThe size of the analytical view when stored on disk, in Bytes; on recommended analytical views, this number is not available, so we report as To Be Determined (TBD).
PartitionsThe names of the columns that partition the analytical view; can be none or several.
Recommended Analytical ViewNames of the recommended analytical views. The name of the analytical view appears in
the form database_name.aview_name
.
Names of the existing analytical views. The name of the analytical view appears in the
form database_name.aview_name
If there are no recommendations, a message appears highlighting the reason why there are no recommendations.
Query BlockOne or more independent valid select statement.
Note that you need to compute the statistics of data tables to be able to view statistics such asRow Count
, Row
Size
, etc in the output. Otherwise, system throws the following error:
ATTENTION: Table metadata information is missing for test.view_1_7, test.tab1, test.tab7, test.recom1 because compute statistics has not been run.
Here is a sample output of verbose
mode:
./arcviz util recommendation --dcname=Recommendation --file=/var/lib/arcadia/test.txt —verbose
----------------------------------------------------------------------------------------------
Recommended Analytival Views:
-- recommended_aview_1 : CREATE ANALYTICAL VIEW `test`.`recommended_aview_1` STORED
AS PARQUET AS SELECT avg(e), h, i, ndv(f) FROM test.recom1 GROUP BY h, i;
-- recommended_aview_2 : CREATE ANALYTICAL VIEW `test`.`recommended_aview_2` STORED
AS PARQUET AS SELECT c, count(a), d, sum(b) FROM test.recom1 GROUP BY c, d;
.....
-- recommended_aview_n : CREATE ANALYTICAL VIEW `test`.`recommended_aview_8` STORED AS PARQUET
AS SELECT b, c, count, sum FROM test.view_1_7;
-----------------------------------------------------------------------------------------------
Recommendation Details:
-- Example of an output with one recommendation.
-- Q1: select count(a), sum(b), c, d from test.view1 group by 3,4;
-- LV: test.view1 is a logical view on top of test.recom1
Row Count: 0 | Row Size: 16.0 | In Memory Size: 0.0 | On Disk Size: 0.0
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0
-- Recommended analytical view: recommended_aview_7
Row Count: 0 | Row Size: 24.0 | In Memory Size: 0.0 | On Disk Size: -1.0 | Partitioned: False
Query Block: SELECT count(a), sum(b), c, d FROM test.view1 GROUP BY 3, 4
-- Example of an output with one recommendation, and a 'no-recommendation' error, highlighting the reason for no recommendation.
-- Q2: select count(a), sum(b), c, d from test.recom1 group by 3,4;
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0
-- Recommended analytical view: recommended_aview_2
Row Count: 0 | Row Size: 24.0 | In Memory Size: 0.0 | On Disk Size: -1.0 | Partitioned: False
Query Block: SELECT count(a), sum(b), c, d FROM test.recom1 GROUP BY 3, 4-- Q1: select j, k, l from test.recom1;
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0
-- No recommendation: The (sub) query does not have aggregation, grouping or distinct operation, recommendation is not provided.
Query Block: SELECT j, k, l FROM test.recom1
-- Example of an output with multiple recommendations, two 'no-recommendation' errors, and one recommendation from an existing aview.
-- Q3: with t1 as (select avg(e) avg, ndv(f) ndv, i, n from test.recom1 group by 3, 4), t2
as (select sum(a) sum, c, h from test.recom1 group by 2, 3)
select t1.avg, t1.ndv, t2.sum from t1, t2 where t1.i = t2.c;
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0
-- No recommendation: The query must have exactly one table reference in it to get a recommendation.
Query Block: WITH t1 AS (SELECT avg(e) avg, ndv(f) ndv, i, n FROM test.recom1 GROUP BY 3, 4),t2
AS (SELECT sum(a) sum, c, h FROM test.recom1 GROUP BY 2, 3)
SELECT t1.avg, t1.ndv, t2.sum FROM t1, t2 WHERE t1.i = t2.c
-- Query routes to an existing analytical view: test.av_12
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0 | Partitioned: False
Query Block: SELECT sum(a) sum, c, h FROM test.recom1 GROUP BY 2, 3
-- Recommended analytical view: recommended_aview_6
Row Count: 0 | Row Size: 24.0 | In Memory Size: 0.0 | On Disk Size: -1.0 | Partitioned: False
Query Block: SELECT avg(e) avg, ndv(f) ndv, i, n FROM test.recom1 GROUP BY 3, 4-- Q2: with t1
as (select avg(e), ndv(f), i, h from test.recom1 group by 3, 4) select * from t1;
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0
-- No recommendation: Currently recommendation engine does not provide recommendations for complex queries.
Query Block: WITH t1 AS (SELECT avg(e), ndv(f), i, h FROM test.recom1 GROUP BY 3, 4) SELECT * FROM t1
-- Recommended analytical view: recommended_aview_1
Row Count: 0 | Row Size: 24.0 | In Memory Size: 0.0 | On Disk Size: -1.0 | Partitioned: False
Query Block: SELECT avg(e), ndv(f), i, h FROM test.recom1 GROUP BY 3, 4
-- Example of an output with recommendated aviews, and a 'no-recommendation' error related to a union query.
-- Q4: select count(a), sum(b), c, d from test.recom1 group by 3,4 union
select avg(e), ndv(f), g, h from test.recom1 group by 3,4 union
select count(a), avg(f), c, e from test.recom1 group by 3,4;
-- BT: test.recom1 is a base table in TEXT format.
Row Count: -1 | Row Size: -1.0 | In Memory Size: -1.0 | On Disk Size: 0.0 B
-- No recommendation: Recommendations are only available for legs of a Union Statement.
Query Block: SELECT count(a), sum(b), c, d FROM test.recom1 GROUP BY 3, 4 UNION
SELECT avg(e), ndv(f), g, h FROM test.recom1 GROUP BY 3, 4 UNION
SELECT count(a), avg(f), c, e FROM test.recom1 GROUP BY 3, 4
-- Recommended analytical view: recommended_aview_1
Row Count: 0 | Row Size: 24.0 B | In Memory Size: 0.0 B | Partitioned: False
Query Block: SELECT count(a), sum(b), c, d FROM test.recom1 GROUP BY 3, 4
-- Recommended analytical view: recommended_aview_2
Row Count: 0 | Row Size: 24.0 B | In Memory Size: 0.0 B | Partitioned: False
Query Block: SELECT avg(e), ndv(f), g, h FROM test.recom1 GROUP BY 3, 4
-- Recommended analytical view: recommended_aview_3
Row Count: 0 | Row Size: 24.0 B | In Memory Size: 0.0 B | Partitioned: False
Query Block: SELECT count(a), avg(f), c, e FROM test.recom1 GROUP BY 3, 4
-- Example of an output with one recommendated aview and a 'no-recommendation' error. Query is based on an Eager-Aggregation table.
-- Q5: select count(m1), eager_agg_rec.dim1.c2, eager_agg_rec.dim1.c3
from eager_agg_rec.fact, eager_agg_rec.dim1 where f1=pk1 group by 2, 3;
-- BT: eager_agg_rec.fact is a base table in TEXT format.
Row Count: 20 | Row Size: 27.200000047683716 B | In Memory Size: 544.0000009536743 B | On Disk Size: 350.0 B
-- BT: eager_agg_rec.dim1 is a base table in TEXT format.
Row Count: 16 | Row Size: 19.75 B | In Memory Size: 316.0 B | On Disk Size: 220.0 B
-- No recommendation: The query must have exactly one table reference in it to get a recommendation.
Query Block: SELECT count_int_finalize(`_arcadia_builtins.count_no_finalize(m1)`) `count(m1)`,
eager_agg_rec.dim1.c2 c2, eager_agg_rec.dim1.c3 c3
FROM (SELECT count_no_finalize(m1) `_arcadia_builtins.count_no_finalize(m1)`,
f1 f1 FROM eager_agg_rec.fact GROUP BY f1) aggregated_fact__, eager_agg_rec.dim1 WHERE f1 = pk1 GROUP BY 2, 3
-- Recommended analytical view: recommended_aview_4
Row Count: 2 | Row Size: 12.0 B | In Memory Size: 24.0 B | Partitioned: False
Query Block: SELECT count_no_finalize(m1) `_arcadia_builtins.count_no_finalize(m1)`,
f1 f1 FROM eager_agg_rec.fact GROUP BY f1
-- Example of a message to compute statistics of data tables.
ATTENTION: Table metadata information is missing for test.view_1_7, test.tab1, test.tab7, test.recom1 because compute statistics has not been run.
Contact your database administrator to schedule this to happen automatically. In the interim, you can run it yourself once manually.
---------------------------------------------------------------------------------------------------------------------------------------