Materialized View Selection Based on Adaptive Genetic Algorithm and Its Implementation with Apache Hive

Abstract

Frequently accessed views in data warehouses are usually materialized in order to accelerate the speed of querying big data. However, the view materialization itself incurs huge costs. Moreover, some latest products of non-traditional data warehouse software, such as Apache Hive, still lack the support of materialized views. In order to select the appropriate views to be materialized with the possible minimized cost, we propose a novel approach to the materialized view selection problem based on an adaptive genetic algorithm. We establish a cost model that integrates the query, maintenance and storage costs to evaluate the performance of approaches and measure the fitness of an individual in the genetic algorithm. In addition, we introduce the adjustable factors for crossover probability and mutation probability, allowing the genetic algorithm to run quickly and avoid premature convergence. We also conduct extensive experiments for its implementation with Apache Hive, which query and manage large datasets residing in distributed storage. Both the simulation results and experiments on Apache Hive show that the approximately optimal solution for selecting materialized views can be obtained effectively using the approach presented.

Selection of materialized views using stochastic ranking based Backtracking Search Optimization Algorithm

“…Figures 7, 8 and 9 depicts the correlative results of SRBSAMVS with EA proposed in Yu et al (2003), EGTMVS (Sohrabi and Azgomi 2019) and AGA (Yu et al 2015). Our method yields better results than EA (Yu et al 2003) EGTMVS (Sohrabi and Azgomi 2019) and AGA (Yu et al 2015) intensive of minimum query processing cost. It is observed that our suggested method catches feasible results sooner and will not block in unsatisfactory solutions.…”

“…We have used MATLAB implementing SRBSAMVS algorithm and conducted various experiments using the datasets from TPC-H benchmark (O'Neil et al 2007). We have compared it with EA proposed by Yu et al (2003), AGA (Yu et al 2015) and EGTMVS (Sohrabi and Azgomi 2019). Assumed parameters for SRBSAMVS are: size of population l = 50, problem dimension as dim = 3, mr as 0.5, probability factor P f = 0.4.…”

“…Therefore, lattice was chosen for representing the problem space of MVS problem. Further, researchers have implemented numerous evolutionary algorithms (EA's) like Particle Swarm Optimization (PSO) (Sun and Ziqiang 2009;Gosain and Heena 2016), Adaptive Genetic algorithm (AGA) (Yu et al 2015), Bee Colony Optimization (BCO) (Kumar and Arun 2015), evolutionary game theory based MVS (Sohrabi and Azgomi 2019) etc., for lessening the total cost of query processing in selecting the materialized views. Our proposed work, chooses Backtracking Search Optimization Algorithm (BSA), for reducing the total processing cost of query.…”

“…In the present study, stochastic ranking based BSA algorithm is proposed for appropriate selection of views, under the space and cost constraints, for minimizing the processing cost of queries, using lattice framework. The results generated by our proposed algorithm are compared with EA proposed by Yu et al (2003), adaptive genetic algorithm AGA (Yu et al 2015) and evolutionary game theory based materialized view selection (EGTMVS) (Sohrabi and Azgomi 2019) to prove the effectiveness of our method, SRBSAMVS.…”

TSGV: a table-like structure-based greedy method for materialized view selection in data warehouses

“…Since a very large number of intermediate views can be constructed in processing several queries of a data warehouse, it is impossible to store all of these views. Furthermore, when modifying the base tables, it is necessary to update materialized intermediate views, which causes another time overhead [2]. These issues lead to an important problem called view selection for materialization, or materialized view selection, which aims at selecting a (quasi-)optimal subset of intermediate views for storage in the available memory space.…”