Support Questions
Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Materialized view not supported in Hive 1.1, so creating a staging table

Hello Team,
Previously, we used to query the view to fetch required output from hive tables but the performance was very poor hence we came up with the idea of Materialized view but unfortunately, that is also not supported in CDH 5.14.4 of hive version 1.1.
We have second approach to overcome the challenge of view and I need any suggestions if below approach supported in hive and what all hive properties  need to be changed in order to implement this.
We thought of creating a staging table and use it to fetch the report rather than using a view. The required indexes can be added into the staging table. So that the report generation can be done quickly.
Below are the actions given by our Developer:-
1. Create a table which is a union of multiple base tables.
2. Create a non-unique index on one of the key columns in the table.
3. Generate a report via front end which should call the staging table instead of a view.
4. Data will regularly be inserted into the base tables on a daily basis.
5. We need to identify those new records and insert them into a staging table once in a day .
6. Plan for rebuilding the index on a staging table once a month.
As per our Developer this is the only option we left with and based on our knowledge hopefully this should work but want to check if this can help.