Distributed database data table processing method, device, electronic device, and computer program

Abstract

The data table processing method for a distributed database includes the steps of: acquiring a first data table and a second data table to be joined in the distributed database, wherein the first data in the first data table and the second data in the second data table are stored in a distributed manner across a plurality of nodes; acquiring a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table; performing partial replication, partial redistribution, and partial retention as is operations on all data consisting of the first data and the second data based on the first data distribution and the second data distribution to acquire the corresponding operated first data and operated second data; and performing a join operation on the operated first data and operated second data to obtain a join result.

Description

[Technical Field]

[0001] (CROSS-REFERENCE TO RELATED APPLICATIONS) This application claims priority from a Chinese patent application bearing application number 202310473395.6, filed with the China Patent Office on April 25, 2023, the entire contents of which are incorporated herein by reference.

[0002] The present application relates to the field of data processing and database technology, and in particular to a method, apparatus, electronic device, computer-readable storage medium, and computer program product for processing data tables in a distributed database. [Background technology]

[0003] A distributed database typically uses small computer systems, where each computer (also called a node) can be located in a different location, and each computer has its own local database, with a full or partial copy of a database management system (DBMS). Many computers in different locations can connect to each other over a network and jointly form a complete, global, logically centralized, physically distributed, large database.

[0004] Data skew is a drawback of distributed databases. In certain data distributions, data skew can cause the amount of data processed by certain nodes in the distributed database to be hundreds to thousands of times larger than that of other nodes, resulting in slower overall processing. However, there is no effective solution in the related art for solving the problem of data skew in a distributed database. Summary of the Invention [Problem to be solved by the invention]

[0005] The embodiments of the present application provide a method and apparatus for processing data tables in a distributed database, an electronic device, a computer-readable storage medium, and a computer program product, which can solve the problem of data skew and further improve the efficiency of data processing. [Means for solving the problem]

[0006] The technical solution of the embodiment of the present application is realized as follows: An embodiment of the present application provides a method for processing data tables in a distributed database executed by an electronic device, the distributed database including a plurality of nodes, the method including: a step of acquiring a first data table and a second data table to be joined in the distributed database, wherein the first data in the first data table and the second data in the second data table are stored in a distributed manner among the plurality of nodes; obtaining a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table; performing partial replication, partial redistribution, and partial retention on all data consisting of the first data and the second data based on the first data distribution and the second data distribution, to obtain corresponding post-operation first data and post-operation second data; and performing a join operation on the manipulated first data and the manipulated second data to obtain a join result.

[0007] An embodiment of the present application provides a data table processing device for a distributed database, the distributed database including a plurality of nodes, the device comprising: an acquisition module, an operation module, and a combination module; the acquisition module is configured to acquire a first data table and a second data table to be joined in the distributed database, and the first data in the first data table and the second data in the second data table are stored in a distributed manner among the plurality of nodes; the acquiring module is further configured to acquire a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table; the operation module is configured to perform partial replication, partial redistribution, and partial retention operations on all data consisting of the first data and the second data based on the first data distribution and the second data distribution, to obtain corresponding operated first data and operated second data; The merging module is configured to perform a merging operation on the manipulated first data and the manipulated second data to obtain a merging result.

[0008] An embodiment of the present application provides an electronic device, the electronic device comprising: a memory for storing executable instructions; and a processor that, when executing executable instructions stored in the memory, implements the distributed database data table processing method provided in the embodiments of the present application. An embodiment of the present application provides a computer-readable storage medium having computer-executable instructions stored thereon, which, when executed by a processor, realizes a method for processing data tables in a distributed database provided in an embodiment of the present application. An embodiment of the present application provides a computer program product including a computer program or computer-executable instructions, which, when executed by a processor, implements the data table processing method of a distributed database provided in the embodiment of the present application.

[0009] The embodiments of the present application have the following beneficial effects: For a first data table and a second data table that are join targets in a distributed database, partial replication, partial re-distribution, and partial keep-as-is operations are performed on all data consisting of the first data and the second data based on a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table. As such, compared to the related art's method of only re-distributing or replicating when re-distributing all data, the technical solution provided in the embodiments of the present application re-distributes all data by adopting partial replication, partial re-distribution, and partial keep-as-is operations according to different data distribution methods, so that the all data after the operation can be distributed almost evenly among multiple nodes, thereby avoiding the data skew problem caused by replicating a large amount of data to the same node when re-distributing all data in the related art and further improving data processing efficiency. [Brief explanation of the drawings]

[0010] [Figure 1] 1 is a schematic diagram of the architecture of a data table processing system 100 of a distributed database provided in an embodiment of the present application. [Figure 2] 1 is a schematic diagram showing the configuration of an electronic device 600 provided in an embodiment of the present application. [Figure 3] 1 is a flowchart of a data table processing method for a distributed database provided in an embodiment of the present application; [Figure 4] 1 is a flowchart of a data table processing method for a distributed database provided in an embodiment of the present application; [Figure 5] FIG. 10 is a schematic diagram illustrating the correspondence between execution time length and data skew provided in an embodiment of the present application. [Figure 6] FIG. 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database according to related art. [Figure 7] 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 8]1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 9] FIG. 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database according to related art. [Figure 10] 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 11] 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 12] 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 13] 1 is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. [Figure 14] FIG. 1 is a comparison diagram of the effects provided by the examples of the present application. DETAILED DESCRIPTION OF THE INVENTION

[0011] In order to make the objectives, technical solutions and advantages of the present application clearer, the present application will be further described in detail below with reference to the drawings. The described embodiments do not limit the present application, and all other embodiments that can be obtained by those skilled in the art without any creative efforts are included in the protection scope of the present application.

[0012] In the following description, the term "some embodiments" describes a subset of all possible embodiments, but it is understood that "some embodiments" may be the same or different subsets of all possible embodiments and may be combined with each other without conflict.

[0013] It is to be understood that embodiments of the present application involve related data such as user information, and where embodiments of the present application are applicable to a particular product or technology, user permission or consent may be required, and the collection, use, and processing of related data may need to comply with relevant laws, regulations, and standards of the relevant countries and regions. The use of terms such as "first / second / ..." in the examples of the present application does not limit the particular order of objects but distinguishes between similar objects, and it will be understood that "first / second / ..." can be used to interchange particular orders or sequences of events where appropriate, such that the examples of the present application described herein can be implemented in orders other than those shown or described herein.

[0014] In the embodiments of the present application, the term "module" or "unit" refers to a computer program or a portion of a computer program having a certain function, working together with other related portions to achieve a certain goal, and can be implemented in whole or in part by using software, hardware (e.g., processing circuitry or memory), or a combination thereof. Similarly, one or more modules or units can be implemented using a processor (or multiple processors or memories). Furthermore, each module or unit may be part of an overall module or unit that includes the functionality of that module or unit.

[0015] Unless otherwise specified, all technical and scientific terms used in the examples of this application have the same meaning as commonly understood by those skilled in the art. The terms used in this application are used only to describe the examples of this application and are not intended to limit the scope of this application.

[0016] Before describing the embodiments of the present application in detail, the nouns and terms related to the embodiments of the present application will be explained. The explanation of the nouns and terms related to the embodiments of the present application is as follows.

[0017] 1) The distribution key is a column (or set of columns) that determines the database partition in which a particular data row is stored. Typically, a column that is a query condition is selected as the distribution key, allowing node pruning using the distribution key. If no distribution key is specified when creating a table, the primary key of the data table becomes the distribution key by default. If the data table does not have a primary key, the first column is used as the distribution key by default.

[0018] 2) A join key is a key used to join data between two data tables. For example, in the case of an order table and an order detail table, the order data in the two data tables can be joined by using the order ID as the join key between the two data tables.

[0019] 3) Data tables are the basic units of data in a distributed database, and a distributed database can include multiple data tables.

[0020] 4) The phrase "in response to" refers to a condition or state on which the operation to be performed depends, and when the dependent condition or state is met, the one or more operations to be performed may be in real time or may have a set delay, and unless otherwise specified, there is no restriction on the order in which the multiple operations to be performed may be performed.

[0021] 5) Data skew: In a distributed database, data is not distributed evenly, causing some nodes to process excessive amounts of data.

[0022] 6) A distributed database (DDB) typically uses small computer systems, each of which can be located in a different location. Each computer has a full or partial copy of a database management system (DBMS) and its own local database. Many computers in different locations can be interconnected via a network to jointly form a complete, global, logically centralized, and physically distributed large-scale database.

[0023] 7) Data Table is a grid virtual table (a table that represents data in memory) that temporarily stores data. Data Table is a core object in the database, which can easily bind to the database without code and store specific data.

[0024] Data skew is a drawback of distributed databases. Given a particular data distribution, data skew can cause certain nodes in a distributed database to process hundreds or thousands of times more data than other nodes, resulting in slower overall processing. Data skew can occur for a variety of reasons, including uneven distribution of data within a data table, the presence of frequent values ​​in values ​​corresponding to distribution keys or join keys, data association after data joins or grouping, and data skew caused by certain predicates, such as equality conditions. Data skew can also have serious consequences, such as a long tail where a node fails to complete and slows down the entire query; resource shortages on certain nodes while other nodes are unable to process data, revealing a query bottleneck and consuming large amounts of resources; and a lack of content on some nodes, potentially causing the entire query to fail.

[0025] In view of the above, embodiments of the present application provide a method and apparatus for processing data tables in a distributed database, an electronic device, a computer-readable storage medium, and a computer program product, which can solve the problem of data skew and further improve the efficiency of data processing. The electronic device provided in the embodiments of the present application will be described below. The electronic device provided in the embodiments of the present application may be implemented as a server, or may be implemented cooperatively by a terminal device and a server. The following describes an example in which the method for processing data tables in a distributed database provided in the embodiments of the present application is implemented cooperatively by a terminal device and a server. 1, which is a schematic diagram of the architecture of a distributed database data table processing system 100 provided in an embodiment of the present application. In order to solve the data skew problem and support applications that improve the efficiency of data processing, as shown in FIG. 1, the distributed database data table processing system 100 includes a server 200, a network 300, a terminal device 400, and a distributed database 500. The network 300 may be a local area network or a wide area network, or a combination of both. The terminal device 400 is a terminal device associated with a user. A client 410 runs on the terminal device 400. The client 410 may be various types of clients, including, for example, a database application, a data table application, a browser, etc. The distributed database 500 includes multiple nodes, such as node 510, node 520, and node 530.

[0026] In some embodiments, when a user needs to join two data tables in the distributed database 500, the user can input identifiers (such as the names and numbers of the data tables) corresponding to the two data tables into the human-computer interaction interface of the client 410. After receiving the data table identifiers entered by the user, the client 410 can transmit the received data table identifiers to the server 200 via the network 300. Based on the received data table identifiers, the server 200 can then retrieve the first data table (e.g., data table A) and the second data table (e.g., data table B) corresponding to the data table identifiers from the distributed database 500. Next, the server 200 can also obtain a first data distribution of the first data in the first data table (e.g., whether the shared key of the first data table is a combined key, whether the data corresponding to the combined key in the first data table includes high-frequency data, etc.) and a second data distribution of the second data in the second data table (e.g., whether the shared key of the second data table is a combined key, whether the data corresponding to the combined key in the second data table includes high-frequency data, etc.). Then, based on the first data distribution and the second data distribution, the server 200 performs partial replication, partial redistribution, and partial retention operations on the entire amount of data consisting of the first data in the first data table and the second data in the second data table. Finally, the server 200 performs a join operation on the operated first data and the operated second data to obtain the join result. The join result is returned to the terminal device 400 via the network 300, and the terminal device 400 invokes and displays the human-computer interaction interface of the client 410.

[0027] It should be noted that the data table processing method for a distributed database provided in the embodiments of the present application may be implemented by a distributed database, for example, when combining a first data table and a second data table, the data redistribution operation and combination operation may be realized by the basic functions of the distributed database, and the embodiments of the present application are not particularly limited thereto. In some other embodiments, the embodiments of the present application may be realized by cloud technology, which is a hosting technology that integrates a set of resources such as hardware, software, and networks into a WAN or LAN to realize computing, storage, processing, and sharing of data.

[0028] Cloud technology is a collective term for network technology, information technology, integration technology, management platform technology, and application technology based on the cloud computing business model, which forms a resource pool that can be used flexibly and conveniently according to requirements. Cloud computing technology is an important support. The background services of technical network systems require large amounts of computing and storage resources.

[0029] 1 may be an independent physical server, a server cluster or a distributed system configured of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDNs), big data, and artificial intelligence platforms. The terminal device 400 may be, but is not limited to, a smartphone, a tablet, a laptop, a desktop computer, a smart speaker, a smart watch, an in-vehicle terminal, etc. The terminal device 400 and the server 200 may be directly or indirectly connected via wired or wireless communication, and the embodiments of the present application are not limited thereto.

[0030] In some embodiments, the data table processing method of a distributed database provided in the embodiments of the present application can also be realized using blockchain technology. For example, the first data in the first data table and the second data in the second data table can be distributed and stored in different nodes of a blockchain network, and data security can be ensured based on the immutable properties of the blockchain.

[0031] The configuration of an electronic device provided in an embodiment of the present application will be described below. Taking a case where the electronic device is a server as an example, FIG. 2 is a schematic diagram showing the configuration of an electronic device 600 provided in an embodiment of the present application. The electronic device 600 shown in FIG. 2 includes at least one processor 610, a memory 640, and at least one network interface 620. The components of the electronic device 600 are coupled via a bus system 630. It should be understood that the bus system 630 is used to realize connection and communication between these components. The bus system 630 includes a power bus, a control bus, and a status signal bus in addition to a data bus. However, for clarity of explanation, all of the various buses are represented as the bus system 630 in FIG. 2.

[0032] The processor 610 may be an integrated circuit chip with signal processing capabilities, such as a general purpose processor, a digital signal processor (DSP), a programmable logic device, a discrete gate or transistor logic device, a discrete hardware component, etc., where the general purpose processor may be a microprocessor or any conventional processor, etc.

[0033] Memory 640 may be removable, non-removable, or a combination thereof. Exemplary hardware devices include solid-state memory, hard drives, optical drives, etc. Memory 640 includes one or more storage devices located physically separate from processor 610.

[0034] The memory 640 may include volatile memory or non-volatile memory, or may include both volatile and non-volatile memory. The non-volatile memory may be read-only memory (ROM), and the volatile memory may be random access memory (RAM). The memory 640 described in the embodiments herein may include any suitable type of memory.

[0035] In some embodiments, memory 640 may store data, such as programs, modules, and data structures, or a subset or superset thereof, to support various operations, as illustrated below.

[0036] The operating system 641 includes system programs, such as a frame layer, a core library layer, a drive layer, etc., for processing various basic system services and performing hardware-related tasks, to realize various basic services and process hardware-based tasks.

[0037] The network communication module 642 is configured to reach other computing devices via one or more (wired or wireless) network interfaces 620, example network interfaces 620 include Bluetooth, Wireless Compatibility Certified (WiFi), and Universal Serial Bus (USB).

[0038] In some embodiments, the apparatus provided in the embodiments of the present application may be realized by software, and FIG. 2 shows a distributed database data table processing device 643 stored in memory 640. The distributed database data table processing device 643 may be software in the form of a program or plug-in, and includes an acquisition module 6431, an operation module 6432, a combination module 6433, and a determination module 6434. These modules are logical and can be arbitrarily combined or further divided according to the functions to be realized. It should be noted that, although FIG. 2 shows all the above modules collectively for the sake of convenience, this should not be considered to exclude an implementation in which the distributed database data table processing device 643 may include only the acquisition module 6431, the operation module 6432, and the combination module 6433, and the functions of each module will be described later.

[0039] In the following, the data table processing method of the distributed database provided in the embodiment of the present application will be specifically described in relation to the exemplary application and implementation of the server provided in the embodiment of the present application.

[0040] For example, refer to FIG. 3, which is a flowchart of a method for processing data tables in a distributed database provided in an embodiment of the present application, which will be described with reference to the steps shown in FIG.

[0041] In step 101, the first and second data tables to be joined in the distributed database are obtained.

[0042] Here, the first data in the first data table and the second data in the second data table can be stored in a distributed manner across a plurality of nodes of the distributed database.

[0043] For example, taking the first data in the first data table as an example, if the first data is sales data for Supermarket A, the sales data for different products from Supermarket A can be distributed and stored across multiple nodes of the distributed database. For example, assuming that the sales data for Supermarket A includes sales data for fresh produce, electrical appliances, and clothing, the sales data for fresh produce from Supermarket A can be stored in node 1 of the distributed database, the sales data for electrical appliances from Supermarket A can be stored in node 2 of the distributed database, and the sales data for clothing from Supermarket A can be stored in node 3 of the distributed database. In other words, the distributed storage of the first data may be based on different types of data. Of course, the distributed storage of the first data may also be based on different physical locations of computer rooms. For example, assuming that the first data is data for Group Company A, which includes multiple subsidiaries located in different physical locations, the data for the different subsidiaries can be distributed and stored across different nodes of the distributed database. For example, the data for Subsidiary 1 can be stored in node 1 of the distributed database, and the data for Subsidiary 2 can be stored in node 2 of the distributed database. Alternatively, the distributed storage of the first data may be achieved by dividing the first data according to a predetermined data amount. For example, if the data amount of the first data is greater than a data amount threshold, the first data may be divided according to the predetermined data amount, and the divided sub-data may be distributed and stored in different nodes of the distributed database; however, the embodiments of the present application are not particularly limited thereto.

[0044] In some embodiments, taking a first data table (e.g., data table A) as an example, one or more columns from the first data table can be selected as a distribution key, and then a hash operation can be performed on the data column using the distribution key, and the column data can be stored in a node corresponding to the calculated hash value. For example, a CdbHash structure can be created using CdbHash *makeCdbHash(int numsegs, int natts, Oid *hashfuncs), and the following operations can be performed for each column (tuple) to calculate the hash value corresponding to the column and determine which node (segment) the data of the column should be distributed to: First, an initialization operation is performed to initialize the hash initial value, then different preprocessing operations are performed for different types by calling the hashDatum() function, then the processed column value is entered into the hash calculation, and finally the hash value is mapped to a node.

[0045] It should be noted that in addition to distributing and storing the first data in the first data table to multiple nodes of the distributed database using hash distribution, the first data in the first data table can also be distributed and stored to multiple nodes using random distribution. For example, the first data in the first data table can be randomly distributed to different nodes of the distributed database, in which case the same data will be stored in different nodes. However, the embodiments of the present application are not particularly limited thereto.

[0046] It should be further noted that the first and second data tables in the embodiments of the present application may be the join results obtained by joining multiple (two or more) data tables. For example, if data table A, data table B, and data table C need to be joined sequentially, data table A and data table B can be joined first to obtain the join result (corresponding to the first data table), and then the join result can be subsequently joined with data table C (corresponding to the second data table), and the embodiments of the present application are not particularly limited thereto.

[0047] In step 102, a first data distribution of first data in a first data table and a second data distribution of second data in a second data table are obtained.

[0048] In some embodiments, the first data distribution of the first data in the first data table may mean whether the share key of the first data table is a combined key or not, and whether the data corresponding to the combined key in the first data table includes high-frequency data or not; similarly, the second data distribution of the second data in the second data table may mean whether the share key of the second data table is a combined key or not, and whether the data corresponding to the combined key in the second data table includes high-frequency data or not.

[0049] For example, if the first data table is data table A and the second data table is data table B, it is possible to obtain the data distribution of data table A and the data distribution of data table B. For example, it is determined whether the shared key of data table A is a combined key and whether the data corresponding to the combined key in data table A includes high-frequency data, and it is determined whether the shared key of data table B is a combined key and whether the data corresponding to the combined key in data table B includes high-frequency data.

[0050] It should be noted that when joining a first data table (e.g., data table A) and a second data table (e.g., data table B), a join key must be specified, and it can be determined whether the join key specified by the user is the same as the shared key used when distributing and storing the data. For example, if they are the same key, it means that the shared key is the join key, and if they are not the same key, it means that the shared key is not the join key. Furthermore, the process of determining whether high-frequency data is included in a data table may be to determine whether the number of occurrences of data corresponding to the join key in the data table is greater than a frequency threshold (e.g., 5,000 times). If it is greater than the frequency threshold, it means that high-frequency data is included in the data table, and if it is not, it means that high-frequency data is not included in the data table.

[0051] For example, let's assume that data table A is an order table and data table B is an order detail table. Assume that the order table stores data distributed across multiple nodes using the order number as the distribution key, and the order detail table stores data distributed across multiple nodes using the product name as the distribution key. At the same time, if the order table and the order detail table are currently joined according to the product name (i.e., the join key), it can be determined that the distribution key of the order table is not the join key, but the distribution key of the order detail table is the join key. Taking the order table as an example, if the join key is the product name, it is possible to determine whether high-frequency data exists in the order table based on the number of times data corresponding to the join key (e.g., purchase records corresponding to different products) appears in the order table. For example, if the number of times purchase records for product A appear in the order table is greater than a frequency threshold (e.g., 5,000 times) and there are 6,000 purchase records for product A in order table A, it can be determined that the order table contains high-frequency data.

[0052] In step 103, based on the first data distribution and the second data distribution, partial replication, partial redistribution, and partial retention are performed on the entire data consisting of the first data and the second data to obtain the corresponding post-operation first data and post-operation second data.

[0053] In some embodiments, referring to FIG. 4, FIG. 4 is a flowchart of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in FIG. 4, step 103 shown in FIG. 3 can be realized by step 1031 and step 1032 shown in FIG. 4, which will be described with reference to the steps shown in FIG. 4.

[0054] In step 1031, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data consisting of the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data are determined.

[0055] It should be noted that the third data, fourth data, and fifth data in the embodiments of the present application do not refer to specific data, but are general terms for certain types of data; that is, in the embodiments of the present application, multiple data for replication operations are collectively referred to as third data, multiple data for redistribution operations are collectively referred to as fourth data, and multiple data whose distribution method is maintained as is are collectively referred to as fifth data.

[0056] In some embodiments, step 1031 can be implemented as follows: In response to the fact that neither the shared keys of the first data table nor the second data table is a combined key, and the data corresponding to the combined key in one of the data tables contains high-frequency data, where the high-frequency data is data whose occurrence count is greater than a frequency threshold (e.g., 5,000 times), the following steps are performed: a process of setting data matching the high-frequency data in a non-target data table (e.g., data identical to the high-frequency data) as third data for a replication operation, where the non-target data table is one of the first data table and the second data table that does not contain the high-frequency data; a process of setting data other than the high-frequency data in the target data table and data other than the data matching the high-frequency data in the non-target data table as fourth data for a redistribution operation, where the target data table is one of the first data table and the second data table that contains the high-frequency data; and a process of setting the high-frequency data as fifth data, where the high-frequency data is retained as is.

[0057] As an example, assuming that the first data table is data table A and the second data table is data table B, and neither of the distributed keys of data table A nor data table B is a join key, and the data corresponding to the join key in data table A contains high-frequency data, the data matching the high-frequency data in data table B can be used as the third data for the replication operation. For example, assuming that the high-frequency data in data table A is (1, 3, 5), and "1" appears 5,100 times in data table A, "3" appears 5,500 times in data table A, and "5" appears 5,400 times in data table A, (1, 3, 5) in data table B can be used as the data that matches the high-frequency data in data table A, i.e., the third data for the duplication operation, and data other than the high-frequency data in data table A and data other than the data that matches the high-frequency data in data table B can be used as the fourth data for the re-distribution operation, i.e., data other than (1, 3, 5) in data table A and data other than (1, 3, 5) in data table B can be used as the fourth data for the re-distribution operation, and (1, 3, 5) in data table A can be used as the fifth data of the distribution method, which is retained as is by the distribution method. In other words, if neither of the distribution keys for two data tables is a join key, and the join key for one of the data tables contains high-frequency data, the distribution key for the data table containing the high-frequency data is not a join key, so the high-frequency data is considered to be evenly distributed among multiple nodes, and as a result, there is no need to redistribute the high-frequency data; it is sufficient to simply perform a replication operation (for example, broadcast to all nodes) on data that matches the high-frequency data in another data table. In this way, because no redistribution is performed on the high-frequency data, it is possible to avoid redistributing large amounts of data all to the same node, and at the same time, because the amount of high-frequency data is large and no redistribution is performed on the high-frequency data, data migration is also reduced.

[0058] In some other embodiments, the above step 1031 can also be realized in the following manner. That is, in response to the fact that neither of the distributed keys of the first data table nor the second data table is a combined key, the data corresponding to the combined key in the first data table includes first high-frequency data, and the data corresponding to the combined key in the second data table includes second high-frequency data, the first high-frequency data is data whose number of occurrences in the first data table is greater than a count threshold, the second high-frequency data is data whose number of occurrences in the second data table is greater than a count threshold, and there is no overlap between the first high-frequency data and the second high-frequency data, the data processing device executes the following processes: designating the data in the first data table that matches the second high-frequency data and the data in the second data table that matches the first high-frequency data as third data for a duplication operation; designating the data in the first data table excluding the first high-frequency data and the data that matches the second high-frequency data and the data in the second data table excluding the second high-frequency data and the data that matches the first high-frequency data as fourth data for a redistribution operation; and designating the first high-frequency data and the second high-frequency data as fifth data, which is retained as is.

[0059] As an example, assuming that the first data table is data table A and the second data table is data table B, and that neither of the distributed keys of data table A and data table B is a combined key, that the data corresponding to the combined key in data table A includes first high-frequency data, and that the data corresponding to the combined key in data table B includes second high-frequency data, and that there is no overlap between the first high-frequency data and the second high-frequency data, the data in data table A that matches the second high-frequency data and the data in data table B that matches the first high-frequency data can be set as third data for the duplication operation, the data in data table A excluding the first high-frequency data and the data that matches the second high-frequency data and the data in data table B excluding the second high-frequency data and the data that matches the first high-frequency data can be set as fourth data for the re-distribution operation, and the first high-frequency data and the second high-frequency data can be set as fifth data of the distribution method that is retained as is by the distribution method. For example, assuming that the first high frequency data in data table A is (1, 3, 5) and the second high frequency data in data table B is (2, 4), (2, 4) in data table A can be set as the data that matches the second high frequency data, (1, 3, 5) in data table B can be set as the data that matches the first high frequency data, data other than (1, 2, 3, 4, 5) in data table A and data other than (1, 2, 3, 4, 5) in data table B can be set as the fourth data for the redistribution operation, and (1, 3, 5) in data table A and (2, 4) in data table B can be set as the fifth data that retains them as they are.In other words, if neither of the distribution keys of the two data tables is a join key, both of the join keys of the two data tables contain high-frequency data, and the high-frequency data does not overlap, then for the high-frequency data, the join key is not a distribution key, and there is no overlap between the high-frequency data, so it is considered that the high-frequency data on one side is evenly distributed among multiple nodes, and therefore there is no need to redistribute it; it is sufficient to simply replicate the data that matches the high-frequency data on the other side to all nodes.In contrast, when it comes to processing non-high-frequency data, since the non-high-frequency data is evenly distributed, an average distribution can be guaranteed by redistributing the non-high-frequency data to all nodes according to the join key.

[0060] In some embodiments, step 1031 can also be implemented as follows: In response to the fact that the share key of one of the first and second data tables is a join key, the share key of the other data table is not a join key, and data corresponding to the join key in the non-target data table includes high-frequency data, the non-target data table is one of the first and second data tables whose share key is not a join key, and the high-frequency data is data whose appearance count in the non-target data table is greater than a frequency threshold, the following steps are performed: set data matching the high-frequency data in the target data table as third data for a duplication operation, and the target data table is one of the first and second data tables whose share key is the join key; set data other than the data matching the high-frequency data in the non-target data table as fourth data for a redistribution operation; and set the high-frequency data and data other than the data matching the high-frequency data in the target data table as fifth data, retaining them as they are.

[0061] Taking the case where the first data table is data table A and the second data table is data table B as an example, assuming that the shared key of data table A is a combined key, the shared key of data table B is not a combined key, and the data in data table B corresponding to the combined key includes high-frequency data, the data matching the high-frequency data in data table A can be set as third data for the replication operation, the data other than the high-frequency data in data table B can be set as fourth data for the re-distribution operation, and the high-frequency data and the data other than the data matching the high-frequency data in data table A can be set as fifth data in the distribution scheme that is retained as is by the distribution scheme. For example, assuming that the high-frequency data is (1, 3, 5), (1, 3, 5) in data table A can be set as data matching the high-frequency data, the data other than (1, 3, 5) in data table B can be set as fourth data for the re-distribution operation, and the data other than (1, 3, 5) in data table B and (1, 3, 5) in data table A can be set as fifth data in the distribution scheme that is retained as is by the distribution scheme. In other words, if the distribution key of one data table is a join key and the distribution key of the other data table is not a join key, and the data table whose joining key is not a distribution key contains high-frequency data, from the perspective of the data table whose joining key is a distribution key, the high-frequency data is considered to be evenly distributed across multiple nodes and can therefore be held locally; in contrast, data that matches the high-frequency data in the data table whose joining key is not a distribution key needs to be replicated to all nodes; and for non-high-frequency data, since the non-high-frequency data is evenly distributed, the non-high-frequency data in the data table whose distribution key is a join key can be held locally and the non-high-frequency data in the other data table can be redistributed according to the join key.

[0062] In some other embodiments, step 1031 can also be implemented by the following method: That is, in response to a case where neither the shared keys of the first data table nor the second data table is a combined key, the data corresponding to the combined key in the first data table includes first high-frequency data, the data corresponding to the combined key in the second data table includes second high-frequency data, and there is overlap between the first high-frequency data and the second high-frequency data, the following steps are performed: a process of designating data that matches the non-overlapping portion of the high-frequency data in the first data table and the second data table as third data for a duplication operation, a process of designating the overlapping portion of the high-frequency data and the remaining data in the first data table and the second data table as fourth data for a redistribution operation, where the remaining data is data in the first data table and the second data table excluding the first high-frequency data, the second high-frequency data, and the data that matches the non-overlapping portion of the high-frequency data, and a process of designating the non-overlapping portion of the high-frequency data as fifth data, retaining it as is.

[0063] For example, in the case where the first data table is data table A and the second data table is data table B, neither of the shared keys of data table A and data table B is a join key, and the data corresponding to the join key in data table A includes first high-frequency data (1, 3, 5), and the data corresponding to the join key in data table B includes second high-frequency data (2, 3, 4), and there is overlap between the first high-frequency data and the second high-frequency data (data (3)). Then, the data matching the non-overlapping high-frequency data in data table A and data table B is used as the third data for the duplication operation, i.e., the data table The (2, 4) in data table A and the (1, 5) in data table B can be used as the third data for the replication operation, the high-frequency data in the overlapping part and the remaining data in data table A and data table B can be used as the fourth data for the re-distribution operation, that is, the high-frequency data (3) in the overlapping part, data other than (1, 2, 3, 4, 5) in data table A, and data other than (1, 2, 3, 4, 5) in data table B can be used as the fourth data for the re-distribution operation, and the high-frequency data in the non-overlapping part can be used as the fifth data that is retained as is, that is, (1, 5) in data table A and (2, 4) in data table B can be used as the fifth data of the distribution method that is retained as is by the distribution method. In other words, if neither of the distribution keys of the two data tables is a join key, and the join keys of the two data tables contain high-frequency data and the high-frequency data is duplicated, then for the high-frequency data, the join key is not a distribution key and there is duplication, so there is a limit to how much redistribution or duplication can be performed on the high-frequency data. Therefore, by redistributing the duplicated high-frequency data, the high-frequency data can be migrated to different nodes, and the basic average amount of data processed by each node can be guaranteed.

[0064] It should be noted that the high frequency data in the embodiments of the present application does not refer to specific data, but is a general term for a certain type of data. For example, data whose occurrence count in a data table is greater than a count threshold is collectively called high frequency data. In other words, high frequency data is a collection of data whose occurrence count is greater than a count threshold.

[0065] In some other embodiments, step 1031 can also be implemented by the following method: In response to the fact that the share key of one of the first and second data tables is a join key and the share key of the other data table is not the join key, and data corresponding to the join key in the target data table includes high-frequency data, the target data table is one of the first and second data tables whose share key is the join key, and the high-frequency data is data whose appearance count in the target data table is greater than a frequency threshold, the following steps are performed: the high-frequency data and data in a non-target data table that matches the high-frequency data are set as third data for a duplication operation, and the non-target data table is one of the first and second data tables whose share key is not the join key; the data in the non-target data table other than the data that matches the high-frequency data are set as fourth data for a redistribution operation; and the data in the target data table other than the high-frequency data are retained as is as fifth data.

[0066] As an example, assuming that the first data table is data table A and the second data table is data table B, the shared key of data table A is a join key, the shared key of data table B is not a join key, and the data in data table A corresponding to the join key includes high-frequency data, the high-frequency data and the data in data table B that matches the high-frequency data can be set as third data for the duplication operation, the data in data table B other than the data that matches the high-frequency data can be set as fourth data for the re-distribution operation, and the data in data table A other than the high-frequency data can be set as fifth data that is retained as is. For example, if the high-frequency data is (1, 3, 5), (1, 3, 5) in data table A and (1, 3, 5) in data table B can be set as third data for the duplication operation, the data in data table B other than (1, 3, 5) can be set as fourth data for the re-distribution operation, and the data in data table A other than (1, 3, 5) can be set as fifth data of the distribution scheme that is retained as is by the distribution scheme. That is, if the distribution key of one data table is a join key and the distribution key of the other data table is not a join key, and the data table with the join key contains frequent data, polling replication can be performed for the frequent data. For example, the first data is sent to the first node, the second data is sent to the second node, and thus an even distribution of data is ensured. Meanwhile, data that matches the frequent data in the data table with the non-join key can be replicated to all nodes. For non-frequent data, since the non-frequent data is evenly distributed, the non-frequent data in the data table with the join key can be kept locally, and the non-frequent data in the other data table can be redistributed according to the join key.

[0067] In step 1032, a replication operation is performed on the third data, a redistribution operation is performed on the fourth data, and the fifth data is kept as is.

[0068] In some embodiments, if neither of the shared keys of the first data table nor the second data table is a binding key, and data corresponding to the binding key in one of the data tables contains high-frequency data, or if neither of the shared keys of the first data table nor the second data table is a binding key, and data corresponding to the binding key in the first data table contains first high-frequency data, and data corresponding to the binding key in the second data table contains second high-frequency data, and there is no overlap between the first high-frequency data and the second high-frequency data, or if the shared key of one of the first data table and the second data table is a binding key, and the shared key of the other data table is not a binding key, and data corresponding to the binding key in a non-target data table (i.e., a data table whose shared key is not a binding key) contains high-frequency data, the above step 1032 can be implemented as follows: That is, the third data is replicated to each node, the fourth data is re-distributed to multiple nodes according to the binding key, and the distribution method of the fifth data among multiple nodes is maintained as is.

[0069] For example, for the third data identified from the entire data, the third data can be sent to all nodes by broadcasting, and the complete third data can be stored in each node of the distributed database; for the fourth data identified from the entire data, the fourth data can be re-distributed to multiple nodes according to the combined key; of course, the data in one data table can be retained as is, and the data in the other data table can be replicated to all nodes; for the fifth data, the distribution method of the fifth data among multiple nodes can be retained as is.

[0070] In some other embodiments, when neither of the shared keys of the first data table nor the second data table is a combined key, the data corresponding to the combined key in the first data table includes first high-frequency data, the data corresponding to the combined key in the second data table includes second high-frequency data, and there is overlap between the first high-frequency data and the second high-frequency data, step 1032 can be implemented as follows: The third data is replicated to each node, and the overlapping high-frequency data in the fourth data is redistributed across multiple nodes, so that the overlapping high-frequency data is evenly distributed across multiple nodes, and the remaining data in the fourth data is redistributed across multiple nodes according to the combined key, while maintaining the distribution method of the fifth data across multiple nodes.

[0071] For example, for the third data identified and obtained from the total amount of data, the third data can be sent by broadcast to all nodes of the distributed database so that the complete third data is stored in each node.

[0072] For example, the fourth data identified from the entire data includes the high-frequency data of the overlapping portion and the remaining data (i.e., data excluding the first high-frequency data, the second high-frequency data, and data matching the high-frequency data of the non-overlapping portion in the first data table and the second data table). For the high-frequency data of the overlapping portion, the high-frequency data of the overlapping portion can be evenly distributed among the multiple nodes by redistributing the high-frequency data of the overlapping portion to multiple nodes according to a predetermined rule. For example, the high-frequency data of the overlapping portion can be redistributed among the multiple nodes by polling replication. For example, assuming that the high-frequency data of the overlapping portion includes 500 pieces of data and the multiple nodes are 10 nodes, the first piece of data among the 500 pieces of data can be sent to the first node (i.e., the first piece of data is stored in the first node), the second piece of data can be sent to the second node (i.e., the second piece of data is stored in the second node), and so on. The tenth piece of data can be sent to the tenth node, while the eleventh piece of data can be retransmitted to the first node. In this way, it is possible to ensure that the overlapping high frequency data is evenly distributed among multiple nodes, and avoid the problem of data skew.

[0073] On the other hand, the remaining data included in the fourth data can be redistributed to multiple nodes according to the binding key. For example, the remaining data can be classified using the binding key as a classification criterion, and different types of data can be stored in different nodes, i.e., only one type of data can be stored in one node. For example, if the binding key is a product name, the remaining data (e.g., purchase records corresponding to multiple products) can be first classified according to the product name, and purchase records for different types of products can be stored in different nodes. For example, purchase records for fresh produce products can be stored in a first node, purchase records for clothing products can be stored in a second node, and purchase records for electrical appliances can be stored in a third node, i.e., only purchase records for one type of product can be stored in one node.

[0074] For example, for the fifth data obtained by identifying the entire amount of data, the distribution method of the fifth data among the plurality of nodes is maintained as is.

[0075] In some embodiments, if the share key of one of the first and second data tables is a binding key and the share key of the other data table is not a binding key, and the data corresponding to the binding key in the target data table includes high-frequency data, step 1032 can be implemented as follows: perform polling replication on the high-frequency data in the third data, replicate data that matches the high-frequency data in the third data to each node, redistribute the fourth data to multiple nodes according to the binding key, and maintain the distribution method of the fifth data among multiple nodes.

[0076] For example, the step of performing polling replication on the high-frequency data in the third data can be realized as follows: n is an integer variable that gradually increases from 1, the maximum value of n is N, N is the total number of data included in the high-frequency data, n is incremented, and n is divided by M to determine the remainder, and the nth data is replicated to a node among the plurality of nodes corresponding to the remainder, where M is the total number of the plurality of nodes, and when the remainder is 0, the data corresponding to the remainder is replicated to the Mth node.

[0077] In some other embodiments, in response to the amount of data in one of the first data table and the second data table being greater than a data amount threshold and the amount of data in the other data table being less than the data amount threshold, a process can be executed to replicate data in the data table whose data amount is less than the data amount threshold to each node.

[0078] For example, if the first data table is data table A and the second data table is data table B, and the amount of data in data table A is greater than the data amount threshold and the amount of data in data table B is less than the data amount threshold, i.e., data table A is a large table and data table B is a small table, then the data in data table B can be replicated to all nodes of the distributed database, i.e., the complete data in data table B can be stored in each node. In step 104, a join operation is performed on the first data after the operation and the second data after the operation to obtain a join result.

[0079] It should be noted that data merging is the process of combining data from different data sources, and the main purpose of merging is to merge and integrate information from different data sources for deeper analysis and decision-making. Here, merging methods mainly include direct merging (i.e., using a programming language to directly access and operate on data sources and execute appropriate query statements or written code to obtain and merge data), database merging (when multiple databases are involved, data from different databases can be merged using a merging tool or application programming interface (API) provided by the database management system), file merging (data from different files can be merged by reading and analyzing files in the file system), and external system merging (merging with external systems and calling the functions or data of these systems to obtain required information).

[0080] Illustratively, the first manipulated data can be merged with the second manipulated data by different join methods (e.g., inner join, outer join, etc.). Here, the join operation can establish logical associations between different data sources based on one or more common fields or keys. The result of the data join can be a new data set with different columns or rows that reflects the relationships and interactions between the original data. Data join is a common operation in data processing and analysis, helping users discover associations and patterns between data and supporting deeper analysis and decision-making.

[0081] In some embodiments, after performing partial replication, partial redistribution, and partial retention operations on the entire data based on the first data distribution and the second data distribution, a join operation can be performed on the first data after the operation in the first data table and the second data after the operation in the second data table to obtain a join result. Then, if it is necessary to join with another data table (e.g., a third data table), the join result can be joined with the third data table. The specific join process can be realized by referring to the above description, and the embodiments of the present application will not be repeated here.

[0082] According to the data table processing method for a distributed database provided in the embodiments of the present application, for a first data table and a second data table that are to be joined in a distributed database, operations of partial duplication, partial redistribution, and partial retention as is are performed on all data based on the first data distribution of the first data table and the second data distribution of the second data table, thereby making it possible to distribute all data after the operations almost evenly across multiple nodes, thereby avoiding the data skew problem that occurs when large amounts of data are duplicated on the same node when all data is redistributed in related technologies, and further improving the efficiency of data processing.

[0083] In the following, an exemplary application of the embodiments of the present application in a practical application scenario will be described.

[0084] Data skew is a drawback of distributed databases. For certain data distributions, data skew can cause the amount of data processed by certain nodes in the distributed database to be hundreds or thousands of times larger than that of other nodes, resulting in slower overall processing. For example, a common database test set (e.g., TPCDS) was tested, and the detection results for data skew are shown in Figure 5. Here, the left graph shows the distribution of query data skew in TPCDS, and the right graph shows the correspondence between data skew and execution time. As can be seen from Figure 5, queries with long execution times are largely related to data skew. Therefore, by effectively addressing the data skew issue, query efficiency can be effectively improved.

[0085] In view of the above, an embodiment of the present application provides a data table processing method for a distributed database, which solves the problem of data skew by redistributing the data in two data tables that need to be joined using partial replication, partial redistribution, and partial keep local methods based on different data distributions, while maintaining the distribution method for most of the data as is.

[0086] The data table processing method of the distributed database provided in the embodiment of the present application will be specifically described below.

[0087] In some embodiments, the data skew situation is very complicated, so the embodiments of the present application handle the data skew situation through the following several cases. 1) Neither of the shared keys of the two data tables (or join results) to be joined is a join key, and the join key of one of the data tables (or join results) contains a most common value (MCV).

[0088] 2) Neither of the distributed keys of the two data tables (or join results) to be joined is a join key, and both of the join keys of the two data tables (or join results) contain a high-frequency value (i.e., a value whose occurrence count in one column is greater than the count threshold, and corresponds to the above-mentioned high-frequency data), and the high-frequency values ​​do not overlap.

[0089] 3) Neither of the shared keys of the two data tables (or join results) to be joined is a join key, and both of the join keys of the two data tables (or join results) contain frequent values, resulting in overlapping frequent values.

[0090] 4) The shared key of one of the data tables (or the join result) is a join key, and the shared key of the other data table (or the join result) is not a join key, and the data table (or the join result) whose shared key is the join key contains a high-frequency value.

[0091] 5) The shared key of one of the data tables (or the join result) is a join key, the shared key of the other data table (or the join result) is not a join key, and the data table (or the join result) whose shared key is not a join key contains a high-frequency value.

[0092] Below, we will explain each of the above five cases in detail.

[0093] In some embodiments, refer to Fig. 6, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database according to the related art. As shown in Fig. 6, if neither of the distribution keys that join the data tables (or join results) on both sides is a join key and the join key of one data table (or join result) contains a high-frequency value, the high-frequency value will be distributed to the same node when distributed according to the hash function.

[0094] For example, refer to FIG. 7, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. Assuming that the left table contains frequent values ​​as shown in FIG. 7, the improvement provided in the embodiment of the present application for the above case includes the following parts. That is, when processing frequent values, since the distribution key for the data table containing the frequent values ​​is not a join key, it can be assumed that the frequent values ​​are essentially evenly distributed among multiple nodes. Therefore, data matching the frequent values ​​in the other database can be broadcast to all nodes without redistributing the frequent values. For example, if the left data table locally stores frequent values ​​(1, 3, 5), then (1, 3, 5) in the right data table must be replicated to all nodes. This has the following advantages. First, because frequent values ​​are not redistributed, data skew caused by redistributing large amounts of frequent data to the same node is avoided. Second, because the data volume of frequent values ​​is large and no redistribution is required, data migration can be reduced. Also, for the processing of non-frequent values, since the non-frequent values ​​are evenly distributed, they can be redistributed according to the combined key, or kept as is on one side and replicated to all nodes on the other side to ensure an average distribution.

[0095] It should be noted that identical values ​​on either side must correspond to all matching values ​​on the other side, and when one side polls and distributes, it is possible to guarantee that the data is distributed evenly. On the other side, data is replicated, ensuring that all identical values ​​are matched only once, thereby ensuring data accuracy. Furthermore, since all data with a join relationship must wait for a join opportunity, frequent values ​​are stored locally on one side and replicated across all nodes on the other side, ensuring that all identical values ​​in the join column have a join opportunity from the perspective of the frequent values. For other values, all values ​​are redistributed to the same node, so identical values ​​in the join column also have a join opportunity. Therefore, even though duplicate values, locally stored values, and distributed values ​​may appear simultaneously in the join column, it is possible to guarantee that all identical values ​​in the join column have a join opportunity, thereby ensuring the accuracy of the result set.

[0096] In some other embodiments, refer to FIG. 8, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in FIG. 8, when neither of the shared keys used to join the data tables (or the join results) on both sides is a join key, and the join keys of the data tables (or the join results) on both sides contain frequent values ​​and no overlapping frequent values, the improvement provided in the embodiment of the present application includes the following parts: When processing frequent values, because the join keys are not shared keys and there are no overlapping frequent values, the frequent values ​​on both sides can be stored locally as well, and data matching the frequent values ​​on the other side need to be replicated to all nodes. For example, assuming that the frequent values ​​in the left data table are (1, 3, 5) and the frequent values ​​in the right data table are (2, 4), the frequent values ​​(1, 3, 5) can be stored locally in the left data table, and at the same time, the frequent values ​​(1, 3, 5) in the right data table can be replicated to all nodes. Similarly, for the right data table, after keeping the frequent value (2, 4) locally, (2, 4) in the left data table needs to be replicated to all nodes. Regarding the processing of non-frequent values, since the non-frequent values ​​are evenly distributed, the non-frequent values ​​can be redistributed to all nodes according to the join key, and the average distribution can be guaranteed.

[0097] In some embodiments, refer to Figure 9, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database according to related art. As shown in Figure 9, if neither of the distribution keys that join the data tables (or join results) on both sides is a join key, and the join keys of the data tables (or join results) on both sides contain high-frequency values ​​and the high-frequency values ​​are duplicated, when the high-frequency values ​​are redistributed, the data of some nodes will expand, causing a typical problem of data skew, which will cause the amount of data to be replicated to be excessive when replicating the high-frequency values, and will also increase the amount of data processed by each node.

[0098] For example, refer to FIG. 10, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in FIG. 10, for the above case, the improvement provided in the embodiment of the present application includes the following parts: When processing high-frequency values, the high-frequency values ​​on both sides have overlapping join keys that are not distributed keys. Therefore, there are limitations on redistribution or data duplication. Therefore, data redistribution is performed for the overlapping high-frequency values. For example, the overlapping high-frequency values ​​are redistributed to different nodes according to rules, so that the high-frequency values ​​are migrated to different nodes for processing. This ensures that, when formulating an execution plan, after the high-frequency values ​​are redistributed, the data distribution is basically balanced and the amount of data processed by each node is approximately average. That is, the advantage of redistribution is that after the data distribution is redistributed, the amount of data processed by each node is as balanced as possible, reducing the impact of data skew.

[0099] In some other embodiments, refer to FIG. 11, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in FIG. 11, when the distribution key of one data table (or join result) is the join key and the data table (or join result) where the join key is not the distribution key contains frequent values, the improvement provided in the embodiment of the present application mainly includes the following parts. That is, when processing frequent values, the frequent values ​​in the data table (or join result) where the join key is the distribution key must be stored locally, and data matching the frequent values ​​in the data table (or join result) where the join key is not the distribution key must be replicated to all nodes. When processing non-frequent values, since non-frequent values ​​are evenly distributed, the non-frequent values ​​in the data table where the distribution key is the join key can be stored locally, and the non-frequent values ​​in the data table on the other side can be redistributed according to the join key.

[0100] In some embodiments, refer to FIG. 12, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in FIG. 12, when the distribution key of one data table (or join result) is a join key and the data table (or join result) for which the distribution key is the join key contains frequent values, the improvement provided in the embodiment of the present application mainly includes the following parts. That is, for processing frequent values, polling replication is performed for the frequent values ​​in the data table (or join result) for which the distribution key is the join key, i.e., the first data is sent to the first node, and the second data is sent to the second node. In this way, an even distribution of data is ensured, and data matching frequent values ​​in the data table (or join result) for which the join key is not the distribution key needs to be replicated to all nodes. For processing non-frequent values, since they are evenly distributed, the non-frequent values ​​in the data table for which the distribution key is the join key can be stored locally, and the non-frequent values ​​in the other data table can be redistributed according to the join key.

[0101] In some other embodiments, refer to Fig. 13, which is a schematic diagram illustrating the principle of a data table processing method for a distributed database provided in an embodiment of the present application. As shown in Fig. 13, when one data table is particularly large and the other data table is particularly small, if the cost of replicating the data in the smaller table to all nodes is not high, the data in the smaller table can be directly replicated to all nodes. The beneficial effects of the distributed database data table processing method provided in the embodiment of the present application will be described below with reference to experimental data.

[0102] For example, refer to Figure 14, which is a comparison diagram of the effects provided by the embodiments of the present application. As shown in Figure 14, the embodiments of the present application were tested on a typical scenario of TPCDS. When data skew is present, the technical solutions provided by the embodiments of the present application (i.e., partial replication, partial redistribution, and partial retention) are significantly improved compared to the technical solutions in the related art (i.e., redistribution or replication). For example, in the case of SQL78, the execution time is reduced from 3666 milliseconds to 2482 milliseconds, and in the case of SQL16, the execution time is reduced from 2416 milliseconds to 2182 milliseconds.

[0103] The following continues to describe a configuration example in which the distributed database data table processing device 643 provided in the embodiments of the present application is implemented as a software module. In some embodiments, as shown in FIG. 2, the software modules in the distributed database data table processing device 643 stored in memory 640 may include an acquisition module 6431, an operation module 6432, and a combination module 6433.

[0104] The acquisition module 6431 is configured to acquire a first data table and a second data table to be joined in a distributed database, wherein the first data in the first data table and the second data in the second data table are stored in a distributed manner across multiple nodes; the acquisition module 6431 is further configured to acquire a first data distribution of the first data and a second data distribution of the second data; the operation module 6432 is configured to perform partial replication, partial redistribution, and partial retention as is operations on the entire data consisting of the first data and the second data based on the first data distribution and the second data distribution, to acquire corresponding operated first data and operated second data; and the join module 6433 is configured to perform a join operation on the operated first data and the operated second data to obtain a join result.

[0105] In some embodiments, the data table processing device 643 of the distributed database further includes a determination module 6434, which is configured to determine, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data consisting of the first data and the second data, fourth data for a re-distribution operation within the entire data, and fifth data to be retained as is within the entire data, and the operation module 6432 is further configured to perform a replication operation on the third data, perform a re-distribution operation on the fourth data, and retain the fifth data as is.

[0106] In some embodiments, in response to the fact that neither of the distribution keys of the first data table nor the second data table is a combined key, and the data corresponding to the combined key in one of the data tables includes high-frequency data, and the high-frequency data is data whose occurrence count is greater than a count threshold, the determination module 6434 is further configured to perform the following operations: set data matching the high-frequency data in the non-target data table as third data for a replication operation, where the non-target data table is a data table of the first data table or the second data table that does not include the high-frequency data; set data other than the high-frequency data in the target data table and data other than the data matching the high-frequency data in the non-target data table as fourth data for a redistribution operation, where the target data table is a data table of the first data table or the second data table that includes the high-frequency data; and set the high-frequency data as fifth data that retains it as is.

[0107] In some embodiments, the determination module 6434 further determines that neither of the shared keys of the first data table and the second data table is a combined key, the data corresponding to the combined key in the first data table includes first high-frequency data, and the data corresponding to the combined key in the second data table includes second high-frequency data, wherein the first high-frequency data is data whose occurrence count in the first data table is greater than a count threshold, the second high-frequency data is data whose occurrence count in the second data table is greater than a count threshold, and there is no overlap between the first high-frequency data and the second high-frequency data. In response to this, the data processing device is configured to execute the following processes: a process of setting the data in the first data table that matches the second high-frequency data and the data in the second data table that matches the first high-frequency data as third data for a replication operation; a process of setting the data in the first data table excluding the first high-frequency data and the data that matches the second high-frequency data and the data in the second data table excluding the second high-frequency data and the data that matches the first high-frequency data as fourth data for a redistribution operation; and a process of setting the first high-frequency data and the second high-frequency data as fifth data that is retained as is.

[0108] In some embodiments, the determination module 6434 is further configured to, in response to the fact that the distribution key of one of the first data table and the second data table is a combined key and the distribution key of the other data table is not a combined key, and data corresponding to the combined key in the non-target data table includes high-frequency data, the non-target data table being a data table of the first data table and the second data table whose distribution key is not a combined key, and the high-frequency data being data whose number of occurrences in the non-target data table is greater than a frequency threshold, perform the following operations: set data matching the high-frequency data in the target data table as third data for a duplication operation, and the target data table being a data table of the first data table and the second data table whose distribution key is the combined key; set data other than the data matching the high-frequency data in the non-target data table as fourth data for a redistribution operation; and set the high-frequency data and data other than the data matching the high-frequency data in the target data table as fifth data, which are retained as they are.

[0109] In some embodiments, the operation module 6432 is further configured to replicate the third data to each node, re-distribute the fourth data among the multiple nodes according to the combined key, and maintain the distribution method of the fifth data among the multiple nodes.

[0110] In some embodiments, in response to a determination that neither of the distribution keys of the first data table and the second data table is a combined key, that the data corresponding to the combined key in the first data table includes first high-frequency data, that the data corresponding to the combined key in the second data table includes second high-frequency data, and that there is an overlap between the first high-frequency data and the second high-frequency data, the determination module 6434 is further configured to perform the following operations: set data that matches the non-overlapping portion of the high-frequency data in the first data table and the second data table as third data for a duplication operation; set the overlapping portion of the high-frequency data and the remaining data in the first data table and the second data table as fourth data for a redistribution operation, where the remaining data is data in the first data table and the second data table excluding the first high-frequency data, the second high-frequency data, and the data that matches the non-overlapping portion of the high-frequency data; and set the non-overlapping portion of the high-frequency data as fifth data, retaining it as is.

[0111] In some embodiments, the operation module 6432 is further configured to replicate the third data to each node, and re-distribute the overlapping high-frequency data in the fourth data among multiple nodes, thereby evenly distributing the overlapping high-frequency data among multiple nodes, and re-distribute the remaining data in the fourth data among multiple nodes according to the combined key, and keep the distribution manner of the fifth data among the multiple nodes unchanged.

[0112] In some embodiments, in response to the fact that the distribution key of one of the first data table and the second data table is a combined key and the distribution key of the other data table is not the combined key, and data corresponding to the combined key in the target data table includes high-frequency data, the target data table is a data table of the first data table and the second data table whose distribution key is the combined key, and the high-frequency data is data whose number of occurrences in the target data table is greater than a frequency threshold, the determination module 6434 is further configured to:

[0113] In some embodiments, the operation module 6432 is further configured to: perform polling replication on the high-frequency data in the third data, and replicate data in the third data that matches the high-frequency data to each node; and re-distribute the fourth data to multiple nodes according to the combined key, and maintain the distribution method of the fifth data in the multiple nodes.

[0114] In some embodiments, the operation module 6432 is further configured to perform the following operations: n is an integer variable that increases from 1, the maximum value of n is N, N is a total number of data included in the high-frequency data, increment n, determine a remainder when n is divided by M, and replicate the nth data to a node among the plurality of nodes corresponding to the remainder, where M is the total number of the plurality of nodes; and when the remainder is 0, replicate the data corresponding to the remainder to the Mth node.

[0115] In some embodiments, the operation module 6432 is further configured to replicate, to each node, the data in the data table whose data volume is less than the data volume threshold, in response to the data volume of one of the first data table and the second data table being greater than the data volume threshold and the data volume of the other data table being less than the data volume threshold. It should be noted that the description of the apparatus according to the embodiment of the present application is similar to the description of the method embodiment above, and has similar beneficial effects to the method embodiment, so it will not be repeated here. The technical details not yet described in the data table processing apparatus for a distributed database provided in the embodiment of the present application can be understood by referring to the description of either FIG. 3 or FIG. 4.

[0116] An embodiment of the present application provides a computer program product, the computer program product including a computer program or computer-executable instructions, the computer program or computer-executable instructions being stored in a computer-readable storage medium, a processor of a computer device reading the computer-executable instructions from the computer-readable storage medium, and the processor executing the computer-executable instructions to cause the computer device to perform the method for processing data tables in a distributed database provided in the embodiment of the present application.

[0117] An embodiment of the present application provides a computer-readable storage medium having computer-executable instructions stored thereon, which, when executed by a processor, causes the processor to execute a data table processing method for a distributed database provided in an embodiment of the present application, for example, the data table processing method for a distributed database shown in FIG. 3 or FIG. 4.

[0118] In some embodiments, the computer-readable storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, flash memory, magnetic memory, an optical disk, or a CD-ROM, or may be a variety of devices including one or any combination of the above memories.

[0119] In some embodiments, the executable instructions take the form of a program, software, software module, script, or code, which may be written in any type of programming language (including compiled or interpreted, declarative or procedural languages), and may be arranged in any form, including as an independently deployed program or as a module, component, subroutine, or other unit suitable for use in a computing environment.

[0120] As an example, the executable instructions may be arranged to be executed on a single electronic device, may be arranged to be executed on multiple electronic devices located at a single location, or may be arranged to be executed on multiple electronic devices distributed across multiple locations and interconnected via a communications network.

[0121] The above is merely an example of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent replacements, improvements, etc. made within the spirit and scope of the present application shall be included in the scope of protection of the present application.

Claims

1. A method for processing data tables in a distributed database, executed by an electronic device, comprising: The distributed database includes a plurality of nodes, and the data table processing method for the distributed database includes: a step of acquiring a first data table and a second data table to be joined in the distributed database, wherein the first data in the first data table and the second data in the second data table are stored in a distributed manner among the plurality of nodes; obtaining a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table; performing partial replication, partial redistribution, and partial retention as is operations on all data consisting of the first data and the second data based on the first data distribution and the second data distribution, to obtain corresponding post-operation first data and post-operation second data; performing a join operation on the manipulated first data and the manipulated second data to obtain a join result.

2. The step of performing partial replication, partial redistribution, and partial retention on all data consisting of the first data and the second data based on the first data distribution and the second data distribution includes: determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data consisting of the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data within the entire data to be retained as is; performing the replication operation on the third data, performing the redistribution operation on the fourth data, and retaining the fifth data as is; 2. The method for processing data tables in a distributed database according to claim 1.

3. determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data configured from the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data, In response to the fact that neither of the shared keys of the first data table nor the second data table is a join key, and the data corresponding to the join key in one of the data tables includes high-frequency data, the high-frequency data being data whose appearance count is greater than a count threshold, A process of setting data that matches the high-frequency data in a non-target data table as third data for a replication operation, wherein the non-target data table is a data table that does not include the high-frequency data from the first data table and the second data table; A process of setting data other than the high-frequency data in a target data table and data other than data matching the high-frequency data in the non-target data table as fourth data for a redistribution operation, wherein the target data table is a data table that includes the high-frequency data of the first data table and the second data table; and performing a process of retaining the high frequency data as fifth data.

3. The method for processing data tables in a distributed database according to claim 2.

4. determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data configured from the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data, In response to the fact that neither of the shared keys of the first data table and the second data table is a combined key, the data corresponding to the combined key in the first data table includes first high-frequency data, and the data corresponding to the combined key in the second data table includes second high-frequency data, the first high-frequency data is data whose number of occurrences in the first data table is greater than a count threshold, the second high-frequency data is data whose number of occurrences in the second data table is greater than a count threshold, and there is no overlap between the first high-frequency data and the second high-frequency data, A process of setting data in the first data table that matches the second high-frequency data and data in the second data table that matches the first high-frequency data as third data for a replication operation; a process of setting data in the first data table excluding the first high-frequency data and data matching the second high-frequency data, and data in the second data table excluding the second high-frequency data and data matching the first high-frequency data, as fourth data for a redistribution operation; and performing a process of treating the first high-frequency data and the second high-frequency data as fifth data that is retained as is.

3. The method for processing data tables in a distributed database according to claim 2.

5. determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data configured from the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data, In response to the fact that a shared key of one of the first data table and the second data table is a join key, a shared key of the other data table is not a join key, and data corresponding to the join key in a non-target data table includes high-frequency data, the non-target data table being a data table of the first data table and the second data table whose shared key is not a join key, and the high-frequency data is data whose appearance count in the non-target data table is greater than a frequency threshold, A process of setting data that matches the high-frequency data in a target data table as third data for a replication operation, wherein the target data table is a data table of the first data table and the second data table, the share key of which is a join key; A process of setting data other than the high-frequency data in the non-target data table as fourth data for a redistribution operation; a process of retaining the high frequency data and data other than the data matching the high frequency data in the target data table as fifth data, 3. The method for processing data tables in a distributed database according to claim 2.

6. performing the replication operation on the third data, performing the redistribution operation on the fourth data, and retaining the fifth data as is; replicating the third data to each of the nodes; redistributing the fourth data among the plurality of nodes according to the combined key; maintaining the distribution method of the fifth data among the plurality of nodes as it is, The method for processing a data table of a distributed database according to any one of claims 2 to 5.

7. determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data configured from the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data, In response to a situation where neither the shared keys of the first data table nor the second data table is a join key, the data corresponding to the join key in the first data table includes first high-frequency data, the data corresponding to the join key in the second data table includes second high-frequency data, and there is overlap between the first high-frequency data and the second high-frequency data, A process of setting data that matches non-overlapping high-frequency data in the first data table and the second data table as third data for a replication operation; A process of treating the high-frequency data of the overlapping portion and the remaining data in the first data table and the second data table as fourth data for a redistribution operation, wherein the remaining data is data in the first data table and the second data table excluding the first high-frequency data, the second high-frequency data, and data that matches the high-frequency data of the non-overlapping portion; and performing a process of retaining the non-overlapping high frequency data as fifth data.

3. The method for processing data tables in a distributed database according to claim 2.

8. performing the replication operation on the third data, performing the redistribution operation on the fourth data, and retaining the fifth data as is; replicating the third data to each of the nodes; a step of evenly distributing the high-frequency data of the overlapping portion in the fourth data to the plurality of nodes by redistributing the high-frequency data of the overlapping portion to the plurality of nodes; redistributing the remaining data in the fourth data among the plurality of nodes according to the combined key; maintaining the distribution method of the fifth data among the plurality of nodes as it is, 8. The method for processing data tables in a distributed database according to claim 7.

9. determining, based on the first data distribution and the second data distribution, third data for a replication operation within the entire data configured from the first data and the second data, fourth data for a redistribution operation within the entire data, and fifth data to be retained as is within the entire data, In response to the fact that the shared key of one of the first data table and the second data table is a join key and the shared key of the other data table is not a join key, and data corresponding to the join key in a target data table includes high-frequency data, the target data table is a data table in which the shared key of one of the first data table and the second data table is a join key, and the high-frequency data is data whose number of occurrences in the target data table is greater than a count threshold, A process of setting the high-frequency data and data in a non-target data table that matches the high-frequency data as third data for a replication operation, wherein the non-target data table is one of the first data table and the second data table whose share key is not a join key; A process of setting data other than the data that matches the high-frequency data in the non-target data table as fourth data for a redistribution operation; and performing a process of setting data other than the high-frequency data in the target data table as fifth data that is retained as is.

3. The method for processing data tables in a distributed database according to claim 2.

10. performing the replication operation on the third data, performing the redistribution operation on the fourth data, and retaining the fifth data as is; performing polling replication on the high frequency data in the third data, and replicating data that matches the high frequency data in the third data to each of the nodes; redistributing the fourth data among the plurality of nodes according to the combined key; maintaining the distribution method of the fifth data among the plurality of nodes as it is, 10. The method for processing data tables in a distributed database according to claim 9.

11. The step of performing polling replication on the high frequency data in the third data includes: n is an integer variable that gradually increases from 1, the maximum value of n is N, N is the total number of data included in the high frequency data, and n is incremented, a step of determining a remainder when n is divided by M, and replicating the n-th data to a node among the plurality of nodes corresponding to the remainder, where M is the total number of the plurality of nodes, and if the remainder is 0, replicating the data corresponding to the remainder to the M-th node; The method for processing data tables in a distributed database according to claim 10.

12. The data table processing method for the distributed database includes: and further comprising the step of replicating, to each of the nodes, data in a data table whose data amount is less than the data amount threshold in response to the data amount in one of the first data table and the second data table being greater than a data amount threshold and the data amount in the other data table being less than the data amount threshold. The method for processing a data table of a distributed database according to any one of claims 1 to 11.

13. A data table processing device for a distributed database, The distributed database includes a plurality of nodes, and the data table processing device of the distributed database includes an acquisition module, an operation module, and a combination module; the acquisition module is configured to acquire a first data table and a second data table to be joined in the distributed database, and the first data in the first data table and the second data in the second data table are stored in a distributed manner among the plurality of nodes; the acquiring module is further configured to acquire a first data distribution of the first data in the first data table and a second data distribution of the second data in the second data table; the operation module is configured to perform partial replication, partial redistribution, and partial retention on all data consisting of the first data and the second data based on the first data distribution and the second data distribution, to obtain corresponding operated first data and operated second data; The data table processing device for a distributed database, wherein the join module is configured to perform a join operation on the manipulated first data and the manipulated second data to obtain a join result.

14. a memory for storing executable instructions; An electronic device comprising: a processor that, when executing executable instructions stored in the memory, implements the method for processing data tables in a distributed database according to any one of claims 1 to 12.

15. A computer-readable storage medium having stored thereon computer-executable instructions that, when executed by a processor, cause the processor to perform the method for processing data tables of a distributed database according to any one of claims 1 to 12.

16. A computer program product comprising a computer program or computer executable instructions which, when executed by a processor, causes the processor to perform the method for processing data tables of a distributed database according to any one of claims 1 to 12.

Images