A technique for parallel query optimization using MapReduce framework and a semantic-based clustering method

Density-based clustering algorithm

DBSCAN is a key method for clustering unlabeled datasets. It is a density-based clustering method that can generate random shape clusters. The main idea is to generate a cluster from any point with a minimum number of points inside an assumed radius. A few crucial descriptions of DBSCAN are Ester et al. (1996):

• Eps neighborhood: The Eps neighborhood of a point p, expressed as N_Eps(p) = {q ∈ D|dis(p, q) ≪ Eps}, where D contains points within distance Eps from point p.

• Directly density-reachable: p is directly density-reachable from q if p is in the Eps neighborhood of q (i.e.,  p ∈ N_Eps(q)) and q is a core point (i.e.,  |N_Eps(q)| ≫ Minpts).

• Density-reachable: If a set of points exists $\left\{r_i|i=0,\dots ,n\right\}$ where each r_i is directly density-reachable from r_i+1, then r_i is density-reachable from a point o where o ∈ {p_j|j = i + 1, …, n}.

The algorithm begins by picking a random point p, then retrieving all density-reachable points from p. If p is a core point, the algorithm creates a cluster. If p is a border point and none of the points is density-reachable from p, then the algorithm picks the next point of the dataset. This procedure is repeated for all points.

MapReduce overview

MapReduce (Dean & Ghemawat, 2008) is a model for large-scale and distributed data processing. MapReduce can be scaled to thousands of nodes. The input data is divided into smaller chunks and kept on a distributed file system. In MapReduce, data should be indicated as (key, value) pairs. Figure 1 shows the MapReduce data flow. Map, Shuffle, and Reduce are three main phases of MapReduce (White, 2012). The map function takes the input pair (k1,v1) and produces one or more intermediate key/value pairs (k2, v2). The shuffle stage partitions the intermediate pairs and sends them to reduce functions. The “reduce function” groups pair values with an identical key (k2, list(v2)) and generate the ultimate output pair list (k3, v3) for all of them (Khezr & Navimipour, 2015).

Related work

Most studies have focused on query optimization methods for various systems due to the significance of the query optimization problems. In this section, access plan recommendation mechanisms are studied to optimize the query.

Query optimization is a method to provide low-cost answers to queries. The purpose of a query optimizer is to evaluate alternative query plans to determine the best query plan. The major query optimization techniques can be divided into seven main classes in the cloud: cost-based query plan enumeration, agent-based, schema-based, security-based, Multiple Query Optimization (MQO), adaptive query optimization, and access plan recommendation (Azhir et al., 2019b; Azhir et al., 2019a). The access plan recommendation mechanisms (Ghosh et al., 2002; Zahir & El Qadi, 2016; Zahir, El Qadi & Mouline, 2014) use similarity-based machine learning methods to recommend an old query execution plan to the optimizer. Here we describe work related to query optimization using access plan recommendation mechanisms.

Azhir et al. (2021) proposed an automatic hybrid query plan recommendation method based on incremental DBSCAN and NSGA-II. Dunn and Davies–Bouldin indices were used to evaluate the goodness of clusters. The results of the proposed algorithm were compared to incremental DBSCAN and K-means. According to the experimental results, the introduced algorithm outperforms the other well-known approaches in terms of accuracy.

Ghosh et al. (2002) developed a plan recycling tool, named Plan Selection Through Incremental Clustering (PLASTIC), to cluster queries using their structures and statistics. The PLASTIC was developed based on the Leader clustering algorithm presented by Hartigan (1975). Based on the trial outcomes, the proposed tool can predict the right query plan in the majority of cases. Furthermore, according to the experimental results, high precision, short time, and low space overhead are the advantages of PLASTIC. To enhance the usability of PLASTIC, Sarda & Haritsa (2004) improved the queries’ feature vector. Also, a decision-tree classifier is included in PLASTIC for effective cluster assignments.

An efficient query plan recommendation method was introduced by Zahir, El Qadi & Mouline (2014). The main aim of this approach is to reuse earlier query plans in executing future queries. The method identifies the likeness between the queries (Makiyama, Raddick & Santos, 2015; Aligon et al., 2014; Aouiche, Jouve & Darmont, 2006; Kul et al., 2018). The clustering technique is grounded on K-Means and Expectation-Maximization (EM) methods. The similarity of the queries was identified using the SQL queries semantics. The method can decrease the query optimization cost.

Finally, Zahir & El Qadi (2016) introduced a query plan prediction technique by identifying the similarity among the statements of the queries. Some primary classification techniques like Support Vector Machine (SVM), Association Rule (AR), and Naive Bayes (NB) were applied in this research. The outcomes revealed that the AR can offer more precise forecasts than SVM and NB techniques.

Access plan recommendation

The main purpose of the access plan recommendation approach is to reuse previously-used execution plans for new queries. It examines the textual similarity between the new incoming query and the earlier queries to use previously executed query plans for new queries. This method represents query statements as feature vectors and then compares them to compute the similarity between them. The query optimizer uses an old query plan for a new query if a similarity exists between them. Therefore, the proposed approach reduces the required cost of producing a new access plan for new incoming queries.

Figure 2 shows the access plan recommendation approach to optimize the query. The R DBSCAN package (Hahsler, Piekenbrock & Doran, 2019) is used to group the queries in different clusters. As shown in Fig. 2, query representation and clustering are the two main steps of the presented approach. In the query representation step, a tokenizer breaks the query text into tokens. Then, a weight is assigned to each token. Query normalization and feature weighting are performed in this phase. The assignment of queries to clusters and the recommendation of the access plans are done in the clustering phase.

Cosine similarity (Nguyen & Amer, 2019) and Jaccard coefficient (Jaccard, 1912) are the two most popular similarity measures used for text documents (Huang, 2008). The performance of these measures has been assessed through empirical experiments. As a result, the Cosine similarity is used to identify similar queries.

The cosine measure calculates the similarity between two vectors by calculating the cosine of the angle created by two vectors. The Cosine similarity is calculated by Eq. (1) (Nguyen & Amer, 2019). (1)${\displaystyle cos\left(\Theta \right)=\frac{\sum _{k=1}^n{x}_{1k}{x}_{2k}}{\sqrt{\sum _{k=1}^n{x}_{1k}^2}\sqrt{\sum _{k=1}^n{x}_{2k}^2}}}$

Furthermore, the Jaccard coefficient is applied to calculate the similarity of two queries based on the presence or absence of features. It is computed by dividing the total number of mutual features between two queries by the total number of features in at least one of the two queries. It is denoted as Jaccard (1912): (2)${\displaystyle J=\frac{|A\bigcap B|}{|\mathrm{A}\bigcup B|}}$

The similarity value is between 0 and 1. If the value is 1, the two queries are identical.

Parallel access plan recommendation

In this section, the primary scheme for parallel access plan recommendation is presented. In this regard, the parallel parts are analyzed, and how the needed processes can be formalized as map/reduce procedures is fully described.

As shown in Fig. 3, the input of the clustering method is the weight matrix of the queries to access plan recommendation. Each row indicates a query vector, and each value in the row specifies the weight of a feature. The main stage of the clustering procedure is the calculation of the distance of query vectors. Therefore, each query is calculated for similarity so that clustering can be easy. In this paper, the queries have been clustered using TF values and distance measured using Cosine distance.

As mentioned, the preparation of query vectors and the computation of Cosine similarity between the generated vectors can be performed in parallel with Map functions and Reduce functions in 4 stages to perform the clustering. The details of Map functions and Reduce ones are as follows (Fig. 3):

The JSQL parser (http://jsqlparser.sourceforge.net/) parses the SQL statements and provides the ability to manipulate them. First, a custom JSQL parser library and various standardization rules are employed to improve clustering quality (Azhir et al., 2021). The rewriting module parses the queries’ text to eliminate string literals, constants, temporary names of tables and columns, syntactic sugar, and database namespaces (Kul et al., 2018). In addition, the parser attempts to tokenize the query and qualify each token with the select, from, group-by, and order-by statements.

1. In the first step, the occurrences of each token in every query are calculated:

   • Map: in the Map phase, each query is converted into a key-value form. In this phase, the term and queryID are chosen as the key. Also, the number 1 is assigned to each term as the value.

   • Reduce: here, the sum of occurrences of each term is computed. The key is set as the term and queryID and the value is set as the number of occurrences.

2. In the second stage, the produced features are weighted according to their frequency. Therefore, the total number of terms of each query can be calculated by:

   • Map: all the terms of each query are grouped in this step. The queryID is assigned as the key and the term. The occurrence of each term in every query is assigned as the value.

   • Reduce: here, the sum of terms in each query is counted. The key-value sets are returned with the tuples (queryID, N) as the key, and the tuples (term, o) are returned as the value, where o is the total occurrence of each term in every query, and N is the sum of terms in the query.

3. In the third step, the frequency of each term in a query is computed. The TF method evaluates the term’s or phrase’s importance in an assumed query. This measure can be computed by Eq. (3) (Makiyama, Raddick & Santos, 2015). (3)${\displaystyle tf\left(q,f\right)\text{: Frequency of feature}f\text{in query}q;}$

   • Map: here, the term is set as the key and the tuple (queryID, o, N) as the value.

   • Reduce: the term frequency is calculated.

4. In the final stage, the Cosine similarity of query vectors is computed (Victor, Antonia & Spyros, 2014).

   • Map: in the Map phase, the key/value pair is produced. The query IDs are set for the key, and the term frequency vectors are set for the value.

   • Reduce: here, the Cosine similarity for each query pair is computed.

At last, the clustering segmentation of DBSCAN is performed based on the Cosine similarity results.

Performance metrics

It is problematic to state when a clustering result can be suitable. So, many clustering validation methods have been presented. The introduced technique’s results are confirmed using Dunn Index (DNI), Silhouette Coefficient, and the Adjusted Rand Index (ARI).

The Dunn Index calculates the smallest distance among clusters and the largest interval between data objects from a similar cluster. It recognizes compressed and distinct clusters. The Dunn index is defined by Eq. (4) (Zaki, Meira Jr & Meira, 2014): (4)${\displaystyle D_k=mi{n}_{i=1,\dots ,k}\left\{mi{n}_{j=i+1,\dots ,k}\left(\frac{d\left(c_i,c_j\right)}{ma{x}_{r=1,\dots ,k}diam\left(c_r\right)}\right)\right\}}$ where d(c_i, c_j) is the dissimilarity between clusters. c_i and c_j are defined as $d\left(c_i,c_j\right)=mi{n}_{x\in c_i,y\in c_j}\left(d\left(x,y\right)\right).diam\left(c\right)$ is the diameter of the cluster. It is defined as $diam\left(c\right)=ma{x}_{x,y\in c}\left(d\left(x,y\right)\right)$.

The Silhouette Coefficient (Rousseeuw, 1987) is another approach to assess the clustering. This index considers cohesion and separation in measuring the quality of clustering. The Silhouette Coefficient is defined as: (5)${\displaystyle s\left(i\right)=\frac{b\left(i\right)-a\left(i\right)}{max\left\{a\left(i\right),b\left(i\right)\right\}}}$ where for each point i, $a\left(i\right)$ indicates the average distance between the point and other points within a similar cluster, and b(i) is the minimum value concerning all other clusters.

Rand Index (RI) calculates the similarity of two solutions. This index has been designed to exploit the similarity of the partitioning with their original class labels. The RI is between 0 and 1. When two partitions are consistent, the RI reaches 1. This index can be calculated by Yeung & Ruzzo (2001): (6)${\displaystyle \mathrm{RI}=\frac{\mathrm{a}+\mathrm{d}}{\mathrm{a}+\mathrm{b}+\mathrm{c}+\mathrm{d}}}$ where,

• a: two data objects in both partitions are allocated to a similar cluster,

• b: two dissimilar data objects are allocated to the similar cluster,

• c: two similar data objects are allocated to different clusters,

• d: two different data objects are allocated to different clusters.

Hubert and Arabie Zaki, Meira Jr & Meira (2014) presented the ARI measure to enhance RI. We suggest ARI for measuring the correspondence between the two partitions. The ARI is calculated using Eq. (7). (7)${\displaystyle \mathrm{ARI}=\frac{\left(\genfrac{}{}{0.0pt}{}{n}{2}\right)\left(\mathrm{a}+\mathrm{d}\right)-\left[\left(a+b\right)\left(a+c\right)+\left(c+d\right)\left(b+d\right)\right]}{{\left(\genfrac{}{}{0.0pt}{}{n}{2}\right)}^2-\left[\left(a+b\right)\left(a+c\right)+\left(c+d\right)\left(b+d\right)\right]}}$