Creating a materialized view

After creating managed tables and inserting the data, you can join the tables using a materialized view. You run the query, and the optimizer takes advantage of the precomputation performed by the materialized view for a faster response time.

Log in to the CDP web interface.
Go to Data Warehouse.
Locate your Impala Virtual Warehouse and click on Hue.
Open the Unified Analytics editor from the left assist panel.

Run the following query to create materialized view:

CREATE MATERIALIZED VIEW sales_weather_join 
STORED AS PARQUET AS
SELECT  w.local_date, 
        w.min_temp_f, 
        w.min_temp_k, 
        w.max_temp_f, 
        w.max_temp_k, 
        w.avg_temp_k, 
        w.avg_temp_f,
        w.min_rain_1h, 
        w.max_rain_1h, 
        w.avg_rain_1h, 
        w.min_snow_1h, 
        w.max_snow_1h,
        w.avg_snow_1h, 
        w.city, 
        s.item_name, 
        s.units, 
        s.city_name AS city_name 
FROM    weather_forecast w INNER JOIN sales s 
ON      w.city = s.city_name 
AND     w.local_date = s.local_date;

ANALYZE TABLE weather COMPUTE STATISTICS;

ANALYZE TABLE weather_forecast COMPUTE STATISTICS;

ANALYZE TABLE sales COMPUTE STATISTICS;

ALTER MATERIALIZED VIEW sales_weather_join REBUILD;