Enable BI mode to rewrite queries automatically
You must enable BI mode to combine the BI capability with the power of transparent materialized view rewritings.
If you want your incoming queries, at different granularity levels, to be answered directly from the materialized view rather than being computed from the source tables, you must enable BI mode. BI mode is disabled by default.
To enable BI mode, execute the following statement from Hue or your preferred
Create a materialized view that stores one of the supported DataSketches (for
example, HLL or KLL) on the columns for which the aggregations will be computed
during query processing.
The following example shows creating a materialized view for computing the number of unique visitors per region for a given website.
CREATE MATERIALIZED VIEW AS SELECT country, state, city, ds_hll_sketch(userid) FROM visitors GROUP BY country, state, city;
SELECT country, count(distinct userid) FROM visitors GROUP BY country; SELECT country, state, count(distinct userid) FROM visitors GROUP BY country, state; SELECT country, state, count(distinct userid) FROM visitors WHERE country = ‘USA’ GROUP BY country, state;