RU2755568C1 - Method for parallel execution of the join operation while processing large structured highly active data - Google Patents

Abstract

FIELD: data processing.

SUBSTANCE: invention relates to a method for parallel execution of the JOIN operation while processing large structured highly active data. In the method, in a computer-implemented data manipulation system using the SQL language, after the host processor receives both operand tables and the row selection conditions in the operation, the rows of the operand tables are distributed among a set of processing modules in the form of fragment tables, followed by parallel execution of the SQL JOIN operation in the processing modules over said fragments of operand tables while simultaneously combining the results, wherein the distribution of the rows of fragment tables among the set of processing modules is preceded by the host processor forming an index table for each operand table, wherein each row comprises the value of the key of the SQL JOIN operation and the value of the amount of rows in said operand table, corresponding to the key, the operation of intersection of the formed index tables is executed while calculating the product of the use frequencies of the keys of the SQL JOIN operation in the operand tables, the obtained summary metadata table is sorted in the descending order of the values of the product of the use frequencies of the keys, the rows of operand tables are selected for the fragment tables according to the values of the keys corresponding to the rows of the summary metadata table so that each pair of fragment tables in one processing module only comprises the rows of the operand tables with the same key value.

EFFECT: increasing productivity of data processing.

1 cl, 5 dwg

Description

The invention relates to the field of data processing methods in terms of obtaining information samples at the user's request and can be used in data processing equipment.

G. LCI data modelling and a database design. The International Journal of Life Cycle Assessment, 3(2), PP. 106-113), к высокоактивным данным относятся данные, коэффициент активности использования которых равен или стремится к единице, и характеризуется отношением их общего количества к количеству данных, задействованных при выполнении операций над ними. К числу отраслей, в которых используются такие данные, относятся государственный сектор, здравоохранение, производство, финансовые услуги, энергетика и добыча природных ресурсов, связь и мультимедиа, туризм и транспорт, направленные на финансовую трансформацию, оптимизацию активов, взаимодействие с потребителями (клиентами) (https://azure.microsoft.com/ru-ru/solutions/#solutions-by-industry, https://www.sas.com/ru_ru/software/how-to-buy/request-price-quote.html, https://www.oracle.com/ru/emerging-technologies/).The constant increase in the volume of data and the variety of formats for their presentation, the need for their use in various spheres of society's activities determine the need to improve the methods of their processing. One of the ways to increase the processing speed of large structured highly active data is to improve the ways of executing queries (https://www.oracle.com/ru/emerging-technologies/), including when performing the JOIN operation by means of its parallel execution on fragments of tables from user request followed by combining the results (https://www.teradata.com, https://www.ibm.com/it-infrastructure/power/accelerated-computing). According to (Carlson, R., Steen, B., Tillman, AM, &

G. LCI data modeling and a database design. The International Journal of Life Cycle Assessment, 3 (2), PP. 106-113), highly active data includes data, the activity rate of which is equal to or tends to one, and is characterized by the ratio of their total amount to the amount of data involved in performing operations on them. Industries that use such data include the public sector, healthcare, manufacturing, financial services, energy and natural resource extraction, communications and multimedia, tourism and transport aimed at financial transformation, asset optimization, interaction with consumers (clients) ( https://azure.microsoft.com/en-ru/solutions/#solutions-by-industry, https://www.sas.com/en_ru/software/how-to-buy/request-price-quote.html , https://www.oracle.com/ru/emerging-technologies/).

The known method described in the invention "Cardinality and Selectivity Estimation Using Asingle Table JOIN Index" (IPC: G06F 17/30, patent for invention US 8914354 B2, publication date 12/16/2014), including a database query that contains one or multiple query predicates and refers to one or more database tables. The query identified one or more join indexes for the corresponding database tables by the database query. Join indexes contain join index predicates and include one or more join columns in a picklist. The number of rows selected by the query predicates is calculated at least in part using the number of rows or statistics from one or more join indices. The selectivity of the base table predicate is calculated at least in part from the calculated number of rows. The cardinality of a join is estimated at least in part from the number of rows and statistics of the identified join indices. The disadvantage of this method is the low speed of the JOIN operation when processing large structured highly active data, which is due to the presence of almost all possible key values determined by the selection condition in both operand tables of the JOIN operation.

The known method described in the invention "JOIN Operation Partitioining" (IPC: G06F 7/00, G06F 17/30, patent for invention US 9665624 B2, publication date 12/18/2014), consisting in the distribution of tables based on the calculation of hash indexes of table keys ... The disadvantage of this method is the low speed of the JOIN operation when processing large structured highly active data, which is due to the increase in the distribution time of tables when using hash indexes.

The closest in technical essence to the claimed invention is the method described in the invention "System, Method, and Computer-readable Medium for Duplication Optimization for Parallel JOIN Operations on Similarly Large Skewed Tables" (IPC: G06F 17/30, patent for invention US 8099409 B2, publication date 01/17/2012), which consists in obtaining both operand tables and the conditions for selecting rows in the operation, distributing the rows of operand tables in the form of fragment tables between multiple processing modules, followed by the parallel execution of the JOIN operation in processing modules and simultaneously combining the results ... The disadvantage of this method is the low speed of the JOIN operation when processing large structured highly active data, which is due to the use of a fragment table, which is a complete operand table containing, among other things, rows not used when performing the JOIN operation.

The technical problem of the invention is to increase the processing speed of large structured highly active data.

The technical result of the invention is to reduce the execution time of the JOIN operation when processing large structured highly active data.

This is achieved by the fact that in the known method, which includes obtaining both operand tables and the conditions for selecting rows in the operation, the distribution of the rows of the operand tables in the form of table fragments between multiple processing units, followed by parallel execution of the JOIN operation in processing units and the simultaneous combination of the results, the distribution of the rows of the tables-fragments between the set of processing modules is preceded by the formation of a pivot table of metadata based on the calculation of the frequency of occurrence of rows with each value of the JOIN operation key in the operand tables and the subsequent distribution of rows from both tables-operands into tables-fragments is carried out on the basis of the pivot table of metadata, moreover, each pair of fragment tables in one processing unit contains only those rows whose key values are in one-to-one correspondence with each other.

The essence of the invention is illustrated by drawings, where FIG. 1 is a generalized block diagram of a system that implements a method for parallel execution of a JOIN operation when processing large structured highly active data; FIG. 2 shows a method for parallel execution of a JOIN operation when processing large structured highly active data; FIG. 3 shows an algorithm for generating a summary table of metadata based on calculating the frequency of using the keys of the JOIN operation in the operand tables; FIG. 4 shows an algorithm for distributing rows from both operand tables into fragment tables in each processing module based on the pivot metadata table.

The following designations are adopted in the graphic images:

Р, Q - tables-operands of the JOIN operation

indP, indQ - metadata tables for operand tables Ρ and Q, respectively;

indPQ - pivot table of metadata 123;

nHi is an auxiliary variable used to refer to a row from the 123 metadata table;

nLow is an auxiliary variable used to refer to a row from the 123 metadata table;

nPM - auxiliary variable corresponding to the serial number of the processing module 110 _{1 ...} 110 _Z from the range 1 ... z;

Key (j) - operation of obtaining the key value of the row number j;

kHi, kLow - auxiliary variables used to store the current key value;

indPQ.Rowcount - operation for determining the number of rows in the indPQ metadata table;

← - the operation of assigning a variable to a value of an arbitrary type, including a table value;

(nPM + 1) MOD z - operation of increasing by one integer modulo z;

indP ∩ indQ (indP.K, indP.M × indQ.M) is the result of performing the intersection operation of indP and indQ tables with calculating the product of frequencies indP.M × indQ.M using the keys K of the JOIN operation in the operand tables.

The method of parallel execution of the JOIN operation when processing large structured highly active data is carried out using the system shown in Fig. 1.

System 100 includes: client system 135, data network 125, and data manipulation system 150.

The client system 135 is an electronic device that has hardware and software capabilities for manipulating data using a SQL-based DBMS, such as a database server, desktop computer, mobile computer, tablet, mobile phone, and so on. Implementation of SQL-based data manipulation in electronic devices is known in the art and is not discussed in detail herein. Although a single client system is mentioned in the description, system 100 may include more than one client system 135.

The data network 125 can be represented as a global, local, private data network. In the network 125, communication occurs over different types of data lines, such as wireless or wired lines.

The data manipulation system 150 consists of a host processor 120, which is a software and hardware complex, the hardware of which includes at least one multi-core processor, for example, an Intel Core i7 CPU, Intel Xeon, a device for interacting with a global, local private network data transfer and a high-speed local area network, and the software of which includes an operating system that provides multithreaded execution of applications, a DBMS with a procedural data manipulation language, for example, Transact-SQL, PL / SQL, PL / pgSQL. The structure and operation of host processor 120 is known in the art and is not discussed in detail herein. Host processor 120 communicates with processing units 105 _{1 ...} 105 _z via a high speed local area network 130 such as Infiniband. Processing modules 105 _{1 ...} 105 _z are software and hardware systems that have an architecture similar to the host processor 120, but may differ from it in computing performance and lack of means of interaction with global, local private data transmission networks. Each of the processing modules 105 _{1 ...} l05 _z is associated with a data storage device 110 _{1 ...} 110 _z , which is at least one device for storing data, such as a solid state drive. Methods for transferring data between elements of data manipulation system 150 are known in the art and are not discussed in detail herein. The host processor 120 software includes an optimizer program 122 that implements the claimed method.

FIG. 2 shows a method of parallel execution of the JOIN operation when processing large structured highly active data.

At step 201, the operand tables 136 and 137 and the row selection conditions are obtained over the global, local, private data networks 125 from the client system 135 to the host processor 120, which executes the code of the optimizer program 122. The method of transferring the operand tables and conditions selection of rows over a wide area, local private data transmission network is known in the art and is not discussed in detail in the present description.

At step 202, when the code of the optimizer 122 is executed, the indPQ metadata pivot table is generated based on the calculation of the frequency of occurrence of rows with each JOIN key value in the operand tables.

At step 203, the distribution of rows from the operand tables Ρ and Q into the table fragments 111 _{1 ...} 112 _z to each processing module 105 _{1 ...} 105 _z based on the indPQ metadata pivot table.

At step 204, a JOIN operation is performed on the slice tables 111 _{1 ...} 112 _z in each processing unit 105 _{1 ...} 105 _z while concatenating the results. The method of performing a JOIN operation in processing units is known in the art and is not discussed in detail here.

Block 202 is implemented using the flowchart of FIG. 3. Step 203 is performed using the sequence of actions shown in FIG. 4.

At step 301, the optimizer 122, for each JOIN key value included in the selection condition, counts the number of rows in the operand table (136) corresponding to the given key value, with the key value and the frequency of occurrence of the rows in the indP index table. This stage can be performed, for example, using an SQL query:

INSERT INTO indP (K, M) SELECT PK, SUM (1) AS Μ FROM Ρ ORDER BY P.K GROUP BY P.K;

At step 302, the optimizer 122, for each JOIN key value included in the selection condition, counts the number of rows in the operand table Q (137) corresponding to the given key value, with the key value and the frequency of occurrence of the rows in the indQ index table. This stage can be performed, for example, using an SQL query:

INSERT INTO indQ (K, Μ) SELECT Q.K, SUM (1) AS Μ FROM Q ORDER BY Q.K GROUP BY Q.K;

In step 303, the optimizer 122 intersects the indP and indQ index tables, resulting in a metadata pivot table indPQ 123. The metadata pivot table contains N rows by the number of key values involved in the JOIN operation. In this case, for large structured highly active data, the number N is greater than the number of processing modules 105 _{1 ...} 105 _z .

In step 304, the optimizer 122 sorts the indPQ metadata pivot table 123 in descending order of magnitude values of the product numbers of the row numbers of the operand tables.

Steps 303 and 304 can be performed, for example, using an SQL query:

INSERT INTO indPQ (K, Μ) SELECT indPK, indP.M × indQ.M AS Μ FROM indP, indQ WHERE indP.K = indQ.M ORDER BY Μ DESC

Distribution of rows of operand tables 136 and 137 between pairs of fragment tables 111 ₁ and 112 ₁ , ..., 111 _z and 112 _{z is} performed, which, in step 203, is implemented by the algorithm for distributing rows from both operand tables into fragment tables in each processing unit based on the metadata pivot table shown in FIG. 4.

In step 401, the two auxiliary variables nHi and nLow are assigned values corresponding to the numbers of the first and last rows in the indPQ metadata table 123, the auxiliary variable nPM is assigned the value one.

At step 402, the completion of the operation of distributing pairs of table-fragments 111 ₁ ... 112 _z is checked, which consists in checking the condition nHi≤nLow. If this condition is met, then the flow goes to step 403, otherwise the distribution is completed.

In step 403, rows numbered nHi and nLow are selected from the indPQ metadata pivot table, and the auxiliary variables kHi and kLow are assigned the values of the keys corresponding to these rows.

At step 404, it is checked that the processing unit with the number nPM is ready to receive data from the host processor 122. If the processing unit with the sequence number nPM is ready to receive data, then it goes to step 405, otherwise - to step 407.

At step 405, from the operand tables (136) and Q (137), the rows are copied, the key values of which correspond to the values of kHi and kLow, into the fragment tables 111 _nPM and 112 _nPM on the data storage unit 110 _{nPM of} the processing unit 105 _nPM via a high-speed local network 130.

At 406, the nHi value is increased by one and the nLow value is decreased by one.

At step 407, the auxiliary variable nPM is assigned a value increased by one modulo z, and then returns to step 402.

The technical result of the invention is provided by using the following theoretical premises in the claimed method. For the parallel execution of the operation for processing large structured highly active data, the following type of JOIN operation was chosen:

Ρ INNER JOIN Q ON π (P.K, Q.K);

полученных в результате выполнения групповой операции F(P_Ki×Q_ki) на декартовом произведении соответствующих классов эквивалентности таблиц-операндов.The operand tables Ρ and Q (136 and 137) are collections of equivalence classes that contain at least one row, and are considered as the factor equivalence sets P _K and Q _K generated by the key K. The predicate π is defined on the sets of instances of the key K in operand tables P and Q (136 and 137) and implements the selection condition. Κ ₁ ,…, K _n represent the set of values of the key K. In this case, the JOIN operation is defined as the union of the equivalence classes of the new operand tables

obtained as a result of performing the group operation F (P _Ki × Q _ki ) on the Cartesian product of the corresponding equivalence classes of the operand tables.

For the parallel implementation of the JOIN operation, metadata is used that determines the distribution of table rows by equivalence classes. In this case, tables P and Q (136 and 137) are associated with index tables with schemes indP (K, Μ) and indQ (K, Μ), which are sets of rows indP = {<K ₁ , Μ ₁ >,…, <K _n , M _n >} and indQ = {<K ₁ , M ₁ >,…, <K _n , M _m >}, where K _j is the value of the key K, M _j is the number of records in the j-th class equivalence, the lines of which contain K _j , Μ is an integer field.

The result table of the parallel execution of the JOIN operation when processing large structured highly active data contains only those equivalence classes that belong to the intersection of indP ∩ indQ index tables. The result of this operation is the indPQ 123 metadata pivot table containing the composite key K and the M field, the value of which is indP.M × indQ.M and relative to which the indPQ 123 metadata pivot table is ordered in descending order.

Using the indPQ 123 metadata pivot table allows you to distribute the operand tables Ρ and Q (136 and 137) between independent processing modules 105 _{1 ...} 105 _z in the form of pairs of fragment tables 111 ₁ and 112 ₁ , 111 _z and 112 _z stored on drives 110 _{1 ...} 110 _Z. In this case, the equivalence classes, the records of which contain the same key values K, are located in one and only one pair of fragment tables 111 ₁ and 112 ₁ , 111 _z and 112 _z .

строк, где P_nPM и Q_nPM - таблицы-фрагменты 111_nPM и 112_nPM соответственно в модуле обработки с порядковым номером nPM. При этом очевидно, что чем меньше значение разности между наибольшим R_max и наименьшим количеством обрабатываемых строк, тем ниже общее время выполнения операции JOIN при обработке больших структурированных высокоактивных данных, содержащихся в таблицах-операндах P и Q (136 и 137).As a result of the operation P _nPM INNER JOIN Q _nPM , a table is obtained containing

rows, where P _nPM and Q _nPM are fragment tables 111 _nPM and 112 _nPM, respectively, in the processing module with sequence number nPM. It is obvious that the smaller the difference between the largest R _max and the smallest number of processed rows, the lower the total JOIN execution time when processing large structured highly active data contained in the P and Q operand tables (136 and 137).

Reducing the execution time of the JOIN operation when processing large structured highly active data in the claimed invention follows from the fact that the summary table of metadata indPQ 123 is the result of the operation indP ∩ indQ and the pair of fragment tables 111 ₁ and 112 ₁ , ..., 111 _z and 112 _{z are} not contain strings that are not used in the JOIN operation. In the method described in the prototype, a table-fragment is used, which is a complete table-operand containing, among other things, rows not used when performing a JOIN operation, which increases the execution time of a JOIN operation when processing large structured highly active data, compared to the claimed method.

The claimed technical result is confirmed by the result of a computational experiment, during which for the parallel execution of the JOIN operation when processing large structured highly active data, software and hardware complexes were used:

1. In the first case, the hardware and software complex consisted only of the client 135 system, which contained the client's database and tables 136 and 137, on which the JOIN operation was performed.

2. In the second case, the hardware-software complex consisted of the client system 135, which housed the client database and tables 136 and 137, the processing system, which included the host processor 120 and two processing modules 105 ₁ and 105 ₂ . The host processor 120 communicates with the client system 135 via the Internet and with the processing modules 105 ₁ and 105 ₂ through a high-speed local area network.

3. In the third case, the hardware and software complex consisted of the client system 135, which housed the client's database and tables 136 and 137, a processing system that included a host processor 120 and four processing modules 105 ₁ , ..., 105 ₄ . The host processor 120 is connected to the client system 135 via the Internet, and to the processing modules 105 ₁ , ..., 105 ₄ by a high-speed local area network.

The experiment was carried out for tables Ρ and Q (136, 137), the number of rows in which varied from 4,000,000 to 25,000,000.

The results of the experiment are shown in FIG. 5.

Shown in FIG. 5 graphs confirm the fact that the implementation of the claimed method of parallel execution of the JOIN operation when processing large structured highly active data provides a significant reduction in the execution time of the JOIN operation when processing large structured highly active data.

SOURCES OF INFORMATION

1. Modern technologies for business transformation / URL: https: // www.oracle.com/ru/emerging-technologies/, date of treatment 07/02/2020.

2. Premium features of a modern cloud analytical platform / URL: https://www.teradata.com, date of treatment 07/02/2020.

3. IMB Power Systems: accelerated computing servers / URL: https://www.ibm.com/ it-infrastructure / power / accelerated-computing, accessed 07/02/2020.

G. LCI data modelling and a database design./The International Journal of Life Cycle Assessment, 3(2), PP. 106-113.4. Carlson, R., Steen, B., Tillman, AM, &

G. LCI data modeling and a database design./The International Journal of Life Cycle Assessment, 3 (2), PP. 106-113.

5. Azure solutions / URL: https://azure.microsoft.com/ru-ru/solutions/#solutions-by-industry/, date of treatment 07/02/2020.

6. Companies that work with SAS / URL: https://www.sas.com/ru_ru/software/ how-to-buy / request-price-quote.html, date of treatment 07/02/2020.

7. New technologies provide competitive advantages / URL: https://www.oracle.com/ru/emerging-technologies/, date of treatment 07/02/2020.

8. US patent No. 8914354 B2 from 16.12.2014 Cardinality and Selectivity Estimation Using Asingle Table JOIN Index / Au Grace, Korlapati Rama Krishna, Chen Haiyan

9. US patent No. 9665624 B2 from 18.12.2014 JOIN Operation Partitioining / Gopi K. Attaluri, Vijayshankar Raman.

10. US patent No. 8099409 B2 from 17.01.2012 System, Method, and Computer-readable Medium for Duplication Optimization for Parallel JOIN Operations on Similarly Large Skewed Tables / Zhou Xin, Kostamaa Olli Pekka.

Claims (1)

A method of parallel execution of the JOIN operation when processing large structured highly active data, which consists in the fact that in a computer-implemented data manipulation system using the SQL language, after the host processor receives both operand tables and the conditions for selecting rows in the operation, the rows of the operand tables are distributed between a set of processing modules in the form of tables-fragments with the subsequent parallel execution of the SQL JOIN operation in processing modules on these fragments of the tables-operands with the simultaneous merging of the results, characterized in that the distribution of rows of table-fragments between the set of processing modules is preceded by the formation carried out by the host processor for of each operand table of the index table, in which each row contains the value of the key of the SQL JOIN operation and the value of the number of rows in this operand table corresponding to this key, performing the operation of intersection of the formed index tables with calculation It is the product of the frequencies of using the keys of the SQL JOIN operation in the operand tables, ordering the resulting pivot metadata table in descending order of the values of the product of the key frequencies, selecting the rows of the operand tables into fragment tables by the values of the keys corresponding to the rows of the pivot metadata table, thus, that each pair of fragment tables in one processing unit contains only those rows of the operand tables that have the same key value.

Images