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>
“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.
./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.
Recommended Analytival Views:
---------------------------------------------------------------------
Recommendation Details:
-- Q1: elect count(a), avg(f), c, e from test.recom1 group by 3,4;
-- NO RECOMMENDATION
====================================================================