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 client. set hive.optimize.bi.enabled=true;
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;                  
```

With the BI mode enabled, any incoming queries will be sliced and diced at different granular levels to be answered directly from the materialized view.

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;