Input from File and Query

In the Command Line Utility, you can get analytical view recommendations by specifying a filename plus one or more queries in the command syntax.

To specify a filename and one or more queries, enter one of the commands with the following syntax. See Smart Acceleration Command Line Utility Options.

./arcviz util recommendation --dcname=<data_connection_name> --file=<file_path to the queries file> --query=<QUERY> 

./arcviz util recommendation --dcname=<data_connection_name> --file=<file_path to the queries file> --query=<QUERY> --query=<QUERY> 
In this example, we specify the following:
  • One file: text.txt.
  • One query: “select stddev(a), avg(f), c, e, b from test.recom1 group by 3,4, 5;” .

The output shows all the Recommended Analytical Views and the corresponding Recommendation Details.

Example: 'Input from a File and a Query
./arcviz util recommendation --dcname=Recommendation --file=/var/lib/arcadia/test.txt 
--query=“select stddev(a), avg(f), c, e, b from test.recom1 group by 3,4, 5;” 

Recommended Analytival Views: 
-- recommended_aview_1 : 
CREATE ANALYTICAL VIEW `test`.`recommended_aview_1` STORED AS PARQUET 
  AS SELECT c, count(a), d, sum(b) FROM test.recom1 GROUP BY c, d;
-- recommended_aview_2 : 
CREATE ANALYTICAL VIEW `test`.`recommended_aview_2` STORED 
  AS PARQUET AS SELECT avg(e), g, h, ndv(f) FROM test.recom1 GROUP BY g, h;
.....
-- recommended_aview_9 : 
CREATE ANALYTICAL VIEW `test`.`recommended_aview_9` STORED AS PARQUET 
  AS SELECT avg(e), h, i, ndv(f) FROM test.recom1 GROUP BY h, i;
----------------------------------------------------------------------------
Recommendation Details: 
-- Q1: 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; 
   -- RECOMMENDATION: recommended_aview_1 | recommended_aview_2 | recommended_aview_3
-- Q2:  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; 
  -- RECOMMENDATION: recommended_aview_4
......
-- Q9:  with t1 as (select avg(e), ndv(f), i, h from test.recom1 group by 3, 4) select * from t1; 
  -- RECOMMENDATION: recommended_aview_9
-------------------------------------------------------------------------------------------------

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.

Syntax Error

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