Verbose Output Mode

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:

Command Syntax and Output Form

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
Your output will have the following form:
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>
Example: Output in Verbose Mode

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:

  • Recommended Analytical Views section shows the recommended analytical views with the 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.

    Recommended Analytical Views

    Names of the recommended analytical view. The name of the analytical view appears in the form database_name.aview_name.

    File Format

    Format of the analytical view in PARQUET.

    SELECT

    Syntax of the select query.

  • Recommended Details section shows all the queries that are executed. Each query displays the following information:

    Q1

    Number and syntax of the select query.

    Base Table

    Name of the base table that appears in the form database_name.table_name.

    Logical View

    Logical view based on one or more base tables. It appears in the form database_name.table_name.

    File Format

    Format of the base table. For example, PARQUET or Text.

    Row Count

    Number of rows in the table.

    Memory Size

    The size of the analytical view when stored in memory, in Bytes; on recommended analytical views, this number is an estimation.

    On Disk Size

    The 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).

    Partitions

    The names of the columns that partition the analytical view; can be none or several.

    Recommended Analytical View

    Names of the recommended analytical views. The name of the analytical view appears in the form database_name.aview_name.

    Query routes to an existing analytical view

    Names of the existing analytical views. The name of the analytical view appears in the form database_name.aview_name

    No recommendation

    If there are no recommendations, a message appears highlighting the reason why there are no recommendations.

    Query Block

    One or more independent valid select statement.

    Note that you need to compute the statistics of data tables to be able to view statistics such as Row 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.
---------------------------------------------------------------------------------------------------------------------------------------