@anvanna @jitendra03 That's obvious and by design, an MV rebuild will take time depending on the changes in the underlying tables the MV is built on even incremental as it has to check each table to compare whether there was a change or not. Something else also you should look at tuning the memory remember behind the scenes it's Map Reduce working and you won't expect it to run in seconds with all the different map and reduces steps. You should have a good understanding of the MV rebuild mechanism below and take that into consideration. Hive supports incremental view maintenance, i.e., only refresh data that was affected by the changes in the original source tables. Incremental view maintenance will decrease the rebuild step execution time. In addition, it will preserve LLAP cache for existing data in the materialized view. By default, Hive will attempt to rebuild a materialized view incrementally, falling back to full rebuild if it is not possible. The current implementation only supports incremental rebuild when there were INSERT operations over the source tables, while UPDATE and DELETE operations will force a full rebuild of the materialized view. To execute incremental maintenance, the following conditions should be met: The materialized view should only use transactional tables, either micromanaged or ACID. If the materialized view definition contains a Group By clause, the materialized view should be stored in an ACID table, since it needs to support MERGE operation. For materialized view definitions consisting of Scan-Project-Filter-Join, this restriction does not exist. A rebuild operation acquires an exclusive write lock over the materialized view, i.e., for a given materialized view, only one rebuild operation can be executed at a given time. I would think you should trigger the rebuild during off-peak hours like when you have no users logged on and running queries in your DW I would think you should trigger the rebuild during off-peak hours like when you have no users logged on and running queries in your DWH @jitendra03 I think the initial problem of the MV rebuild failure has been achieved now we are talking of a performance problem, Since performance is a new issue unrelated to the question asked in the original subject, I kindly ask that you start a new question for performance-specific help I would be happy to help. Asking multiple questions in the same thread makes a thread harder to follow for other users of this community. Hope that gives you a better picture of the behind the scenes tasks Happy hadooping
... View more
