WO2023080804A1

WO2023080804A1 - Method for parallel execution of a join operation when processing highly active structured big data

Info

Publication number: WO2023080804A1
Application number: PCT/RU2021/000480
Authority: WO
Inventors: Виктор Иосифович МУНЕРМАН; Юрий Владимирович СИНЯВСКИЙ; Игорь Лазаревич ЧУКЛЯЕВ; Евгений Игоревич ЧУКЛЯЕВ; Кристина Сергеевна ЧУКЛЯЕВА
Original assignee: Смоленская Общественная Организация Содействия Науке И Образованию "Региональный Союз Ученых"
Priority date: 2021-11-02
Filing date: 2021-11-02
Publication date: 2023-05-11

Abstract

The invention relates to a method for the parallel execution of a JOIN operation when processing highly active structured big data. In the claimed method, once both operand tables and the conditions for selecting rows in the operation have been received, the rows of the operand tables are distributed among a plurality of processing modules in the form of table fragments, followed by parallel execution in the processing modules of a JOIN operation on said operand table fragments, with the results being simultaneously joined, wherein the distribution of the rows of the fragment tables among a plurality of processing modules is preceded by the generation of a metadata pivot table on the basis of a calculation of the frequency of occurrence of rows with each value of a JOIN operation key in the operand tables, and the subsequent distribution of the rows from each operand table into fragment tables is carried out on the basis of the metadata pivot table, wherein each pair of fragment tables in a processing module contains only those rows having a one-to-one correspondence between their key values.

Description

A way to execute the JOIN operation in parallel when processing large structured highly active data

Technical field

The invention relates to the field of data processing methods in

5 parts of obtaining information samples at the request of the user and can be used in data processing equipment.

The constant increase in the amount of data and the variety of formats for their presentation, the need for their application in various areas of society, necessitates the improvement of methods for their processing. One of the ways to increase the speed of processing 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 over fragments of tables from user query and then combining the results (https://www.teradata.com, htps://www.ibm.com/it-infrastructure/power/accelerated-computing). According to (Carlson, R., Steen, B., Tillman, A. M., & Lofgren, G. LCI data modeling and a database design. The International

20 Journal of Life Cycle Assessment, 3(2), PP. 106-113.), highly active data includes data whose use activity coefficient 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 (customers) (https://azure.microsoft.com/ru-ru/solutionsZ#solutions-by-industry, https://www.sas.com/nj_ru/software/

SUBSTITUTE SHEET (RULE 26) how-to-buy/request-price-quote.html, https://www.oracle.com/en/emerging- technologies/ ).

State of the art

The known method is described in the invention "Cardinality and Selectivity 5 Estimation Using Asingle Table JOIN Index" (IPC: G06F 17/30, patent for invention US 8914354 B2, publication date 12/16/2014), which includes a database query that contains one or more query predicates and refers to one or more database tables. The query identified one or more join indexes for the corresponding database tables on the database query. Join indexes contain join index predicates and include one or more join columns in the select list. The number of rows selected by the query predicates, 15, is calculated at least in part using the number of rows or statistics from one or more join indexes. The base table predicate selectivity is calculated at least in part from the calculated number of rows. The number of elements of the union is estimated, 20 at least in part from the number of rows and statistics of the identified union indexes. 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 values of 25 keys determined by the selection condition in both tables-operands of the JOIN operation.

There is a method described in the invention "JOIN Operation Partitioining" (IPC: G06F 7/00, G06F 17/30, patent for invention US9665624B2, publication date 12/18/2014), which consists in distributing 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

SUBSTITUTE SHEET (RULE 26) structured highly active data, which is due to an 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 rows of operand tables in the form of fragment tables between multiple processing modules, followed by parallel execution of the JOIN operation in processing modules and simultaneous merging of 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 rows not used during the JOIN operation.

Disclosure of invention

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

The technical result is achieved by the fact that in a known method, including 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 a plurality of processing modules, followed by parallel execution of the JOIN operation in the processing modules and simultaneous merging of the results , the distribution of rows of fragment tables between a plurality of processing modules is preceded by the formation of a summary table of metadata based on the calculation of the frequency of occurrence of rows with

SUBSTITUTE SHEET (RULE 26) each value of the key of the JOIN operation in the operand tables and the subsequent distribution of rows from both operand tables into fragment tables is carried out on the basis of the metadata summary table, and each pair of fragment tables in one processing module contains only those rows whose key values are mutually - one-to-one correspondence with each other.

The essence of the invention is illustrated by drawings, which introduced the following designations:

P, Q are operand tables of the JOIN operation indP, indQ are metadata tables for operand tables P and Q, respectively; indPQ - metadata summary table 123; PN i - auxiliary variable used to access the line from the metadata table 123; nLow is an auxiliary variable used to refer to a row from the metadata table 123; pRM - auxiliary variable corresponding to the sequence number of the processing module 110i...110z from the range 1...z;

Key(j) - the operation of getting the value of the key of the row with the number j; kHi, kLow - auxiliary variables used for the current key value; indPQ.Rowcount - operation to determine the number of rows in the IndPQ metadata table;

"-- the operation of assigning a value to a variable of an arbitrary

, including tabular

(pRM +1) MOD z - the operation of increasing by one an integer modulo z indP l indQ(indP.K, indP.MxindQ.M) - the result of the operation of the intersection of the tables indP and indQ with the calculation of the product of frequencies indP.Mx indQ.M use of keys To the JOIN operation in operand tables.

SUBSTITUTE SHEET (RULE 26) The method of parallel execution of the JOIN operation when processing large structured high-activity data is carried out using the system shown in FIG. 1 .

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

The client system 135 is an electronic device having hardware and software data manipulation capabilities using an SQL-based DBMS, such as a database server, a desktop computer, a mobile computer, a tablet, a mobile phone, and so on. The implementation of SQL-based data manipulation in electronic devices is known in the art and is not discussed in detail here.

Although the description refers to a single client system, 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 various 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, an Intel Xeon, a device for interacting with a global, local private network data transmission and high-speed local area network, and the software of which includes an operating system that provides multi-threaded execution of applications, a DBMS with a procedural data manipulation language, for example, Transact-SQL, PL/SQL, PL/pgSQL. The design and operation of the host processor 120 is known in the art and is not discussed in detail here. Host processor 120 is in communication with processing modules 105i... _105z . through a high speed local area network 130, such as Infiniband. Modules

SUBSTITUTE SHEET (RULE 26) processors 105i...105z are software and hardware systems that have an architecture similar to the host processor 120, but may differ from it in computing performance and the lack of means of interaction with global, local private data networks. Each of the processing modules 105i... _105z is associated with a storage device 110i...110z, which is at least one storage device such as a solid state drive. Methods for transferring data between elements of the data manipulation system 150 are known in the art and are not discussed in detail in the present description. The software of the host processor 120 includes an optimizer program 122 that implements the claimed method.

In FIG. Figure 2 shows how to execute the JOIN operation in parallel when processing large, structured, highly active data.

At step 201, operand tables 136 and 137 and row selection conditions for global, local, private data networks 125 are received from the client system 135 to the host processor 120, which executes the code of the optimizer program 122. The method of transmitting operand tables and conditions row selection over global, local private data networks is known from the technical field and is not considered in detail in the present description.

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

In step 203, rows are distributed from the operand tables P and Q to fragment tables 111i... _112z to each processing unit 105i...105z based on the metadata summary table indPQ. At step 204, a JOIN operation is performed on the fragment tables 111i...112 _z in each processing unit 105i...105 _z while concurrently merging the results. Execution method

SUBSTITUTE SHEET (RULE 26) the JOIN operation in processing modules is known in the art and is not discussed in detail in the present description.

Step 202 is implemented using the workflow shown in FIG. 3. Step 203 is implemented using the workflow shown in FIG. 4.

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

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

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

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

In step 303, the optimizer 122 performs an intersection operation on the indP and indQ index tables, resulting in a metadata summary table indPQ 123. The metadata summary table contains N rows by the number of key values involved in the JOIN operation. At the same time, for large structured highly active data, the number N is greater than the number of processing modules 105i...105z.

In step 304, the optimizer 122 sorts the summary metadata table indPQ 123 in descending order of the magnitude values of the products of the number of rows of the operand tables.

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

SUBSTITUTE SHEET (RULE 26) INSERT INTO IndPQ (K, M) SELECT indP.K, indP.MxindQ.M AS M FROM indP, IndQ WHERE indP.K=indQ.M ORDER BY M DESC Allocation of operand table rows 136 and 137 between fragment table pairs 111i and _112i , _. 4.

At step 401, the two auxiliary variables nHi and nLow are assigned values corresponding to the numbers of the first and last rows in the metadata table IndPQ 123, the auxiliary variable RRP is assigned the value one. At step 402, a check is made for the completion of the allocation operation of pairs of fragment tables 111i...112 _z , consisting in checking the condition nH nLow. If this condition is met, then proceed to step 403, otherwise, the allocation execution is completed.

At step 403, rows with numbers nHi and nLow are selected from the summary metadata table IndPQ, and auxiliary variables kH! and kLow are assigned the values of the keys corresponding to the given rows.

At step 404, the readiness of the processing module with the number of RX to receive data from the host processor 122 is checked.

At step 405, rows are copied from the operand tables P (136) and Q (137), the key values of which correspond to the values kHi and kLow in the fragment tables 111 _p rm and 112 _p rm on the data drive 110 _pr rm of the processing module 105 _pr rm via high speed LAN 130.

At step 406, the value of nHi is incremented by one and the value of nLow is decremented by one.

SUBSTITUTE SHEET (RULE 26) At step 407, the auxiliary variable rl is assigned a value increased by one modulo z, after which it returns to step 402.

The technical result of the invention is ensured by the use of the following theoretical prerequisites in the proposed method. The following type of JOIN operation has been chosen for the parallel execution of the operation of processing large structured highly active data:

P INNER JOIN Q ON l(R.K, Q.K);

The operand tables P and Q (136 and 137) are collections of equivalence classes that contain at least one row and are considered as factor sets of equivalences RK and QK generated by the key K. The predicate mp is defined on the sets of instances of the key K in the tables operands P and Q (136 and 137) and implements the selection condition. Ki, ..., K _p represent a set of key values K. In this case, the JOIN operation is defined as the union of the equivalence classes of the new operand tables

x Q _K _) obtained as a result of performing the group operation F(P _Ki x 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. At the same time, tables P and Q (136 and 137) are associated with index tables with schemas indP(K, M) and indQ(K, M), which are sets of rows indP={<Ki, Mi><K _n , Mn >} and indQ={<Ki, Mi>, ..., <K _n , M _m >}, where Kj is the value of the key K, Mj is the number of records in the j-th equivalence class, the lines of which contain Kj, M - 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 index tables indP • indQ. result

SUBSTITUTE SHEET (RULE 26) performing this operation is the indPQ 123 metadata summary table containing the composite key K and the M field, the value of which is indP.MxindQ.M and relative to which the indPQ 123 metadata summary table is sorted in descending order.

The use of the metadata summary table indPQ 123 makes it possible to distribute the operand tables P and Q (136 and 137) between independent processing modules 105i ... 105z in the form of pairs of fragment tables 1111 and 112i 111 _z and 112 _z stored on drives 110i ... 110z. In this case, the equivalence classes whose records contain the same key values K are located in one and only one pair of fragment tables 1111 and 1121 , ... , 111 _z and 112 _Z .

As a result of the execution of the operation P _p prm INNER JOIN O _p prm, a table is obtained containing

lines, where RPRM and O _p rm - tables -

/=1 fragments 111 _prm and 112 _prm respectively in the processing module with the serial number of the prm. At the same time, it is obvious that the smaller the difference between the largest Rmax and the smallest Rmn number of processed rows, the lower the total execution time of the JOIN operation when processing large structured highly active data contained in operand tables P and Q (136 and 137).

The reduction in 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 metadata table IndPQ 123 is the result of the operation indP • IndQ and a pair of fragment tables 111 i and 112 .... 111 _z and 112 _z are not contain rows that are not used in the JOIN operation. The method described in the prototype uses a fragment table, which is a complete operand table containing rows that are not used when performing the JOIN operation, which increases the execution time of the JOIN operation when processing large structured highly active data, compared with by the claimed method.

SUBSTITUTE SHEET (RULE 26) List of drawings

Fig.1. - A generalized block diagram of a system that implements a method for parallel execution of the JOIN operation when processing large structured highly active data

5 Fig. 2. Method for executing the JOIN operation in parallel when processing large structured high activity data FIG. 3. - Algorithm for the formation of a summary table of metadata based on the calculation of the frequency of using the keys of the JOIN operation in the operand tables u Fig.4. - Algorithm for distributing rows from both operand tables into fragment tables to each processing module based on the metadata summary table

Fig.5. - Experiment results

Implementation Options

15 The client system 135 may be a desktop computer with installed SQL-based DBMS software, such as PostgreSQL, connected via the global Internet (125) via TCP/IP to the host processor 120 of the data manipulation system, which may be a 20 general purpose server , equipped with a multi-core processor, an operating system that provides multi-threaded execution of applications, such as Windows 7, a DBMS with a procedural data manipulation language, such as Transact-SQL. The software includes 25 optimizer 122, which is a software system written in a high-level language. The host processor is connected by a high-speed local area network, preferably Infiniband, to several processing modules 105i...105z having a similar architecture, each of which includes at least one storage device 110i...110 _z , preferably a solid state drive.

SUBSTITUTE SHEET (RULE 26) The achievement of the claimed technical effect is confirmed by comparing the execution time of the JOIN operation on two tables 136 and 137 located in the user's system.

In FIG. 5 shows the dependence of the execution time of the JOIN operation on two tables containing from 4000000 to 25000000 rows in a system that included two processing modules and four processing modules that had the same parameters. Also for comparison, figure 5 shows the dependence of the execution time of the JOIN operation on the number of rows, provided that it is performed exclusively by the computing means of the system 135.

The above results demonstrate that the speed of the JOIN operation increases with the increase in the number of processing modules.

SUBSTITUTE SHEET (RULE 26)

Claims

Claim

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 row selection conditions in the operation, table rows are distributed - operands between a plurality of processing modules in the form of fragment tables, followed by parallel execution of the SQL JOIN operation in the processing modules on these fragments of operand tables with simultaneous merging of results, characterized in that the distribution of rows of fragment tables between a plurality of processing modules is preceded by the host processor formation of an index table for each table-operand, 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 table-operand corresponding to this key, the operation of crossing the formed index tables with the calculation of the product of the frequencies of using the keys of the SQL JOIN operation in tables -operands, ordering the resulting summary table of metadata in descending order of the value of the product of the frequencies of use of keys, selection of rows of tables-operands into tables-fragments is carried out according to the values of the keys corresponding to the rows of the summary table of metadata, so that each pair of tables-fragments in one processing module contains only those rows of operand tables that have the same key value.

SUBSTITUTE SHEET (RULE 26)