Input from a Query

In the Command Line Utility, you can get analytical view recommendations from a single query.

You can get analytical view recommendations from a single query in two ways. See Smart Acceleration Command Line Utility Options.

To debug syntax errors in the query, see Example: Syntax Error.

Input from one query

To get analytical view recommendations from one query, enter a command with the following syntax:

./arcviz util recommendation --dcname=Recommendation --query=<QUERY>  

In our example, we specified the following query: "select count(a), avg(f), c, e from test.recom1 group by 3,4;”

The output shows one analytical view recommendation.

./arcviz util recommendation --dcname=Recommendation 
    --query="select count(a), avg(f), c, e from test.recom1 group by 3,4;” 
Recommended Analytival Views:  
-- recommended_aview_1 : 
CREATE ANALYTICAL VIEW `test`.`recommended_aview_1` STORED AS PARQUET 
    AS SELECT avg(f), c, count(a), e FROM test.recom1 GROUP BY c, e;   
-------------------------------------------------------------------------- 
Recommendation Details: 
-- Q1: select count(a), avg(f), c, e from test.recom1 group by 3,4; 
-- RECOMMENDATION: recommended_aview_1
==========================================================================

Input from more than one query

To get analytical view recommendations from more than one query, enter a command with the following syntax:

./arcviz util recommendation --dcname=<data_connection_name> --file=<file_path to the queries file> 
    --query=<QUERY> --query=<QUERY>  
In our example, we specified the following two queries in the commmand. Similarly, you can specify more queries in the command.
  • "select count(a), avg(f), c, e from test.recom1 group by 3,4;”
  • “select count(a), avg(f), c, e from test.recom1 group by 3,4;”
The output shows two analytical view recommendations:
  • recommended_aview_1.
  • recommended_aview_2.
./arcviz util recommendation --dcname=curly --query=“WITH t1 
as (select avg(e), ndv(f), i, h from test.recom1 group by 3, 4) select * from t1;” 
--query=“select count(a), avg(f), c, e from test.recom1 group by 3,4;”
----------------------------------------------------------------------------------
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 avg(f), c, count(a), e FROM test.recom1 GROUP BY c, e;
------------------------------------------------------------------------------------------------
Recommendation Details:
 -- Q1: with t1 as (select avg(e), ndv(f), i, h from test.recom1 group by 3, 4) select * from t1;
 -- RECOMMENDATION: recommended_aview_1
 -- Q2: select count(a), avg(f), c, e from test.recom1 group by 3,4;
 -- RECOMMENDATION: recommended_aview_2
 ================================================================================================

Example: Syntax Error

If the file is empty or the query has a syntax error, there are no recommended analytical views.

For instance, in the following example, you get an error because the word select is spelled incorrectly. To debug errors, view the statistics in Verbose Output Mode.

Recommended Analytival Views:
        
---------------------------------------------------------------------
Recommendation Details:
-- Q1: elect count(a), avg(f), c, e from test.recom1 group by 3,4;
-- NO RECOMMENDATION
=====================================================================