Using the command line Recommendation utility to get Analytical View recommendations

Note: Available to Enterprise version

A key feature of analytical view framework is the ability to accelerate sub-components (sub-queries) of the overall query.

If the sub-selects contain aggregates, Arcadia can take advantage of existing analytical views to answer them.

For example lets say you have a query which contains UNION such as the following:

SELECT sum(b) as ‘total’, a FROM shaun.events GROUP BY 2
UNION
SELECT sum(b) as ‘total’, a FROM shaun.action GROUP BY 2

Since the individual sub-selects contain aggregates (ie - sum(b) ) , it’s possible to create Analytical Views to match the legs of the union.

Let’s walk through how we would do that:

Data Setup

Let’s say we have 2 FACT TABLES

create table EVENTS (a string, b int, userid string);

create table ACTION (a string, b int, userid string);

And we want to create a Logical View / Dataset which contains Aggregates in SUBSELECT

create view ea_combined_2 as (
SELECT sum(b) as ‘total’, a FROM shaun.events GROUP BY 2
UNION
SELECT sum(b) as ‘total’, a FROM shaun.action GROUP BY 2

Our resulting LV would look like this:

describe ea_combined_2
+------+--------+---------+
| name | type   | comment |
+------+--------+---------+
| total| bigint |         |
| a    | string |         |
+------+--------+---------+

Command line Utility

The command line utility that allows you to obtain analytical view recommendation. Follow these links to learn how to access:

Get Analytical View Recommendation

Let’s use the command line utility to get recommendation:

./arcviz util recommendation --verbose --dcname=“Arcadia Enterprise” --query=“SELECT sum(b) as ‘total’, a FROM shaun.events GROUP BY 2 UNION SELECT sum(b) as ‘total’, a FROM shaun.action GROUP BY 2”

This will give an output

Using DJANGO_SETTINGS_MODULE: settings_cm
Using python: /opt/cloudera/parcels/ARCADIAENTERPRISE-4.5.0.0-1545268605.cdh5/lib/venv/bin/python3
------------------------------------------------------------------------------------------------------------------------------------------------------
Recommended Analytival Views: 

-- recommended_aview_1 : 

CREATE ANALYTICAL VIEW `shaun`.`recommended_aview_1` STORED AS PARQUET AS SELECT a, sum(b) FROM shaun.action GROUP BY a;

-- recommended_aview_2 : 

CREATE ANALYTICAL VIEW `shaun`.`recommended_aview_2` STORED AS PARQUET AS SELECT a, sum(b) FROM shaun.events GROUP BY a;

------------------------------------------------------------------------------------------------------------------------------------------------------

Create Analytical View

Once we have the recommended analytical views, we can use arcadia-shell command line to create them.

CREATE ANALYTICAL VIEW shaun.recommended_aview_1 STORED AS PARQUET AS SELECT a, sum(b) FROM shaun.action GROUP BY a;

CREATE ANALYTICAL VIEW shaun.recommended_aview_1 STORED AS PARQUET AS SELECT a, sum(b) FROM shaun.action GROUP BY a;;

1 Like