Partition table grouping and aggregation query method, device, computer equipment and medium

Abstract

The present disclosure relates to a method, apparatus, computer equipment, and medium for grouped aggregation query of a partitioned table; wherein the method comprises: when determining that the grouped aggregation query of the partitioned table meets preset conditions, pushing down the grouped aggregation operation to each partition of the partitioned table to generate a corresponding grouped aggregation execution plan; sending the grouped aggregation execution plan to all computing nodes so that each computing node executes the grouped aggregation execution plan in parallel on each partition to obtain corresponding execution results, and sending the execution results to the master node; aggregating all received execution results, and returning the aggregated total execution result to the corresponding client. The embodiments of the present disclosure can improve the execution efficiency of grouped aggregation queries and enhance database performance by optimizing the execution plan through grouped aggregation pushdown and executing the grouped aggregation operations of each partition in parallel during the execution process.

Description

Technical Field

[0001] The present disclosure relates to the field of database technology, and in particular to a method, apparatus, computer equipment, and medium for grouping and clustering queries on a partitioned table. Background Art

[0002] With the rapid development of information technology, the amount of data that needs to be processed is increasing. Especially for some analytical scenarios, more and more people are beginning to use distributed database clusters based on nothing sharing to store data and query analysis. Therefore, the storage capacity and query analysis performance of distributed databases are particularly critical.

[0003] Query analysis is a crucial operation in distributed databases, and its performance impacts the efficiency of business statistics and analysis. When the data volume is large and has specific characteristics, data partitioning can be used to create partitioned tables based on these characteristics for data storage. For grouping and aggregation of partitioned tables, the traditional approach is to first append all partition data in the partitioned table, logically treating it as a single table, and then perform grouping and aggregation operations. While this approach is relatively common, it serially scans all partitions before performing grouping and aggregation operations, resulting in low query efficiency, slow speed, and poor query performance. Summary of the Invention

[0004] In order to solve the above technical problems, the present disclosure provides a method, apparatus, computer device and medium for grouping and clustering queries on a partitioned table.

[0005] In a first aspect, the present disclosure provides a grouping and aggregation query method for a partition table, applied to a master node, the method comprising:

[0006] When it is determined that the grouping and aggregation query of the partition table meets the preset conditions, the grouping and aggregation operation is pushed down to each partition of the partition table, and a corresponding grouping and aggregation execution plan is generated;

[0007] Sending the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains corresponding execution results, and sends the execution results to the master node;

[0008] All received execution results are summarized and the total execution result obtained after summary is returned to the corresponding client.

[0009] Optionally, when it is determined that the group-by-group aggregation query of the partition table meets a preset condition, the group-by-group aggregation operation is pushed down to each partition of the partition table, and before generating a corresponding group-by-group aggregation execution plan, the method further includes:

[0010] Obtaining the grouping condition corresponding to the group aggregation query;

[0011] It is determined according to the grouping condition that the group aggregation query meets a preset condition.

[0012] Optionally, the group aggregation query is obtained by:

[0013] Receive a query request sent by a client, parse the query request, and generate a corresponding syntax tree;

[0014] If the target command included in the syntax tree is a data manipulation language command, performing semantic analysis and query rewriting on the syntax tree to obtain a corresponding query tree;

[0015] The query tree is extracted to obtain the grouping and aggregation query.

[0016] Optionally, the method further includes:

[0017] If the target command contained in the syntax tree is not a data manipulation language command, the target command is sent to all computing nodes through the local command program, so that each computing node executes the target command respectively, obtains the corresponding execution result, and sends the execution result to the master node.

[0018] In a second aspect, the present disclosure provides a grouping and aggregation query method for a partition table, which is applied to each computing node. The method includes:

[0019] receiving a group-and-aggregate execution plan sent by a master node, wherein the group-and-aggregate execution plan is generated after the master node determines that a group-and-aggregate query of a partition table meets a preset condition and pushes the group-and-aggregate operation down to each partition of the partition table;

[0020] The group aggregation execution plan is executed in parallel on each partition to obtain corresponding execution results, and the execution results are sent to the master node so that the master node summarizes all received execution results and returns the total execution result obtained after summarization to the corresponding client.

[0021] Optionally, executing the group-and-aggregate execution plan in parallel on each partition to obtain a corresponding execution result includes:

[0022] According to the grouping and aggregation execution plan, obtaining the original partition data to be aggregated corresponding to each partition;

[0023] Performing a partial aggregation operation on the original partition data to obtain a first aggregation result;

[0024] Performing a data redistribution operation on the first aggregation result to obtain a second aggregation result;

[0025] performing a final aggregation operation on the second aggregation result to obtain a third aggregation result;

[0026] Perform an append operation on all third aggregation results to obtain corresponding execution results.

[0027] In a third aspect, the present disclosure provides a partition table group aggregation query device, which is applied to a master node, and includes:

[0028] A generation module, configured to push down the grouping and aggregation operation to each partition of the partition table and generate a corresponding grouping and aggregation execution plan when determining that the grouping and aggregation query of the partition table meets the preset conditions;

[0029] a sending module, configured to send the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains a corresponding execution result, and sends the execution result to the master node;

[0030] The summary module is used to summarize all received execution results and return the total execution result obtained after summary to the corresponding client.

[0031] Optionally, the above device further includes: a determination module, specifically configured to:

[0032] When it is determined that the grouped aggregation query of the partition table meets the preset conditions, the grouped aggregation operation is pushed down to each partition of the partition table, and before the corresponding grouped aggregation execution plan is generated, the grouping conditions corresponding to the grouped aggregation query are obtained;

[0033] It is determined according to the grouping condition that the group aggregation query meets a preset condition.

[0034] Optionally, the group aggregation query is obtained by:

[0035] Receive a query request sent by a client, parse the query request, and generate a corresponding syntax tree;

[0036] If the target command included in the syntax tree is a data manipulation language command, performing semantic analysis and query rewriting on the syntax tree to obtain a corresponding query tree;

[0037] The query tree is extracted to obtain the grouping and aggregation query.

[0038] Optionally, the above device further includes: a sending module, specifically configured to:

[0039] If the target command contained in the syntax tree is not a data manipulation language command, the target command is sent to all computing nodes through the local command program, so that each computing node executes the target command respectively, obtains the corresponding execution result, and sends the execution result to the master node.

[0040] In a fourth aspect, the present disclosure provides a partition table grouping and aggregation query device, which is applied to each computing node, and includes:

[0041] a receiving module, configured to receive a group aggregation execution plan sent by a master node, wherein the group aggregation execution plan is generated after the master node determines that a group aggregation query of a partition table meets a preset condition and pushes the group aggregation operation down to each partition of the partition table;

[0042] The execution module is used to execute the group aggregation execution plan in parallel on each partition, obtain the corresponding execution result, and send the execution result to the master node so that the master node can summarize all the received execution results and return the total execution result obtained after summary to the corresponding client.

[0043] Optional execution module, specifically used to:

[0044] According to the grouping and aggregation execution plan, obtaining the original partition data to be aggregated corresponding to each partition;

[0045] Performing a partial aggregation operation on the original partition data to obtain a first aggregation result;

[0046] Performing a data redistribution operation on the first aggregation result to obtain a second aggregation result;

[0047] performing a final aggregation operation on the second aggregation result to obtain a third aggregation result;

[0048] An append operation is performed on all third aggregation results to obtain corresponding execution results, and the execution results are sent to the master node, so that the master node summarizes all received execution results and returns the total execution result obtained after summarization to the corresponding client.

[0049] In a fifth aspect, the present disclosure further provides a computer device, comprising:

[0050] one or more processors;

[0051] a storage device for storing one or more programs,

[0052] When the one or more programs are executed by the one or more processors, the one or more processors implement any one of the partition table grouping and aggregation query methods described in the embodiments of the present disclosure.

[0053] In a sixth aspect, the present disclosure further provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the grouping and aggregation query method for a partition table described in any one of the embodiments of the present disclosure.

[0054] Compared with the prior art, the technical solution provided by the embodiments of the present disclosure has the following advantages: when it is determined that the group aggregation query of the partition table meets the preset conditions, the group aggregation operation is pushed down to each partition of the partition table to generate a corresponding group aggregation execution plan; the group aggregation execution plan is sent to all computing nodes, so that each computing node executes the group aggregation execution plan in parallel on each partition to obtain a corresponding execution result, and the execution result is sent to the master node; all received execution results are summarized, and the total execution result obtained after the summary is returned to the corresponding client. By optimizing the execution plan through group aggregation push-down and executing the group aggregation operation of each partition in parallel during the execution process, the execution efficiency of the group aggregation query can be improved, and the database performance can be improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0056] In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0057] Figure 1 This is a flowchart of a partition table grouping and aggregation query method provided by an embodiment of the present disclosure;

[0058] Figure 2A This is a flow chart of another partition table grouping and aggregation query method provided by an embodiment of the present disclosure;

[0059] Figure 2B This is a schematic diagram of the query process before optimizing the grouped aggregation query provided by an embodiment of the present disclosure;

[0060] Figure 2C This is a schematic diagram of a query process after optimizing a grouped aggregate query provided by an embodiment of the present disclosure;

[0061] Figure 2D This is a schematic diagram of the execution results before optimization of the grouped aggregation query provided by an embodiment of the present disclosure;

[0062] Figure 2EThis is a schematic diagram of the execution result after optimizing the grouped aggregation query provided by an embodiment of the present disclosure;

[0063] Figure 3 Schematic diagram of the interaction process between the client, master node and computing node provided by the embodiment of the present disclosure;

[0064] Figure 4 This is a structural diagram of a partition table grouping and aggregation query device provided by an embodiment of the present disclosure;

[0065] Figure 5 It is a structural diagram of another partition table grouping and aggregation query device provided by an embodiment of the present disclosure;

[0066] Figure 6 It is a structural diagram of a computer device provided by an embodiment of the present disclosure. DETAILED DESCRIPTION

[0067] In order to more clearly understand the above-mentioned objectives, features and advantages of the present disclosure, the scheme of the present disclosure will be further described below. It should be noted that the embodiments of the present disclosure and the features therein can be combined with each other in the absence of conflict.

[0068] In the following description, many specific details are set forth to facilitate a full understanding of the present disclosure, but the present disclosure may also be implemented in other ways different from those described herein; it is obvious that the embodiments in the specification are only part of the embodiments of the present disclosure, rather than all of the embodiments.

[0069] Figure 1 This is a flow chart of a method for grouping and gathering queries on partitioned tables provided by an embodiment of the present disclosure. This embodiment is applicable to a situation where a master node in a distributed database performs a grouping and gathering query on a partitioned table, wherein the distributed database may include a master node and a plurality of computing nodes connected to the master node. The method for grouping and gathering queries on partitioned tables applied to a master node provided by this embodiment can be executed by a device for grouping and gathering queries on partitioned tables applied to a master node, which can be implemented in hardware and / or software and can be configured in a computer device. Figure 1 As shown, the method specifically includes the following:

[0070] S110 , when it is determined that the group-and-aggregate query of the partition table meets the preset conditions, the group-and-aggregate operation is pushed down to each partition of the partition table, and a corresponding group-and-aggregate execution plan is generated.

[0071] Among them, the partition table can be understood as multiple tables obtained by dividing the data in a large table into many small subsets called partitions according to certain rules. Each partition in the partition table stores data within a certain range. The rules can be set by the designer or determined according to specific circumstances. This embodiment does not impose specific restrictions. In Structured Query Language (SQL), group by is usually used for grouping. Grouped aggregation query can be understood as a query method in the database. The preset conditions can be conditions pre-set in the optimizer of the master node. For example, the grouped aggregation query uses the partition key of the partition table as the grouping condition, and the partition key is not the distribution key. It can also be determined according to specific circumstances. This embodiment does not impose specific restrictions. The partition key is used to determine the data range of each partition data in the same node. The distribution key is used to distribute data to each node and is located between nodes. The grouped aggregation execution plan can be understood as the execution plan for performing grouped aggregation queries on each partition in the computing node.

[0072] When the optimizer determines that a grouped-aggregate query on a partitioned table meets the preset conditions, specifically when the query uses the partition key of the partitioned table as the grouping condition and the partition key is not the distribution key, the query can ensure that the grouped data is only within one of the partitions of the partitioned table. In other words, once each partition is grouped, the entire partitioned table is grouped, avoiding the need to merge all partition data before the operation can be performed. In this case, the optimizer can optimize the grouped-aggregate query that meets the preset conditions, that is, push the grouped-aggregate operation down to each partition of the partitioned table and generate a corresponding grouped-aggregate execution plan, which executes the grouped-aggregate operation in parallel on each partition.

[0073] S120, sending the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains corresponding execution results, and sends the execution results to the master node.

[0074] After the optimizer generates the corresponding group-aggregate execution plan, it can execute the group-aggregate execution plan in a distributed manner for the distributed database in this embodiment. Therefore, the master node's scheduler sends the group-aggregate execution plan to all compute nodes. After receiving the group-aggregate execution plan, the compute nodes use their own executors to execute the plan in parallel on each partition. After execution, the corresponding execution results are obtained. After obtaining the execution results, the executors send them to the scheduler. Because the group-aggregate execution plan is executed in parallel on each partition, execution speed is accelerated.

[0075] S130: Summarize all received execution results and return the aggregated total execution result to the corresponding client.

[0076] The client corresponds to the initiator of the group aggregation query of the partition table.

[0077] After receiving the execution results sent by the executors of each computing node, the scheduler summarizes all the execution results and returns the total execution results obtained after summarization to the corresponding client, so that the client can receive the total execution results corresponding to the grouped aggregation query.

[0078] In this embodiment, when it is determined that the group aggregation query of the partition table meets the preset conditions, the group aggregation operation is pushed down to each partition of the partition table to generate a corresponding group aggregation execution plan; the group aggregation execution plan is sent to all computing nodes, so that each computing node executes the group aggregation execution plan in parallel on each partition to obtain a corresponding execution result, and the execution result is sent to the master node; all received execution results are summarized, and the total execution result obtained after the summary is returned to the corresponding client. The execution efficiency of the group aggregation query can be improved by pushing down the optimized execution plan of the group aggregation and executing the group aggregation operation of each partition in parallel during the execution process, thereby improving the database performance.

[0079] In this embodiment, optionally, when determining that the group-by-group aggregation query of the partitioned table meets a preset condition, pushing the group-by-group aggregation operation down to each partition of the partitioned table, and generating a corresponding group-by-group aggregation execution plan, may further specifically include:

[0080] Obtaining the grouping condition corresponding to the group aggregation query;

[0081] It is determined according to the grouping condition that the group aggregation query meets a preset condition.

[0082] Specifically, the optimizer obtains the grouping condition corresponding to the grouped aggregation query, compares the grouping condition with the preset condition, and can determine whether the grouped aggregation query meets the preset condition. If the grouping condition is consistent with the preset condition, it means that the grouped aggregation query meets the preset condition.

[0083] In this embodiment, the above method is used to determine whether the grouped aggregation query meets the preset conditions, which makes it easier for the optimizer to push the grouped aggregation operation down to each partition of the partition table and generate a corresponding grouped aggregation execution plan, so that the grouped aggregation of each partition can be executed in parallel, which can speed up the execution speed, improve the efficiency of the grouped aggregation query, and save time.

[0084] In this embodiment, optionally, the group aggregation query is obtained by:

[0085] Receive a query request sent by a client, parse the query request, and generate a corresponding syntax tree;

[0086] If the target command included in the syntax tree is a data manipulation language command, performing semantic analysis and query rewriting on the syntax tree to obtain a corresponding query tree;

[0087] The query tree is extracted to obtain the grouping and aggregation query.

[0088] The target command can be understood as the query command contained in the syntax tree. The Data Manipulation Language (DML) command can be understood as a command for database operations, such as select, insert, update, and delete.

[0089] Specifically, when a user wants to query a distributed database, he or she will send an SQL request (i.e., a query request) to the master node through the client. After the master node receives the query request sent by the client, it needs to perform an authentication operation on the query request. When the query request is successfully authenticated and a connection is established, the client can send a query to the database. In this case, the database receives the query request sent by the client. The parser of the master node parses the query request, mainly performing lexical analysis and grammatical analysis on the received query SQL string to generate a corresponding syntax tree. Then, when the parser determines that the target command contained in the syntax tree is a DML command, it performs semantic analysis and query rewriting on the syntax tree to obtain the corresponding query tree. The parser extracts the query tree to obtain the corresponding grouped aggregation query.

[0090] In this embodiment, obtaining the grouped aggregate query by the above method can ensure security on the one hand, and facilitate subsequent determination of whether the grouped aggregate query can be optimized on the other hand.

[0091] In this embodiment, optionally, the method may further specifically include:

[0092] When it is determined that the grouping and aggregation query of the partitioned table does not meet the preset conditions, the query tree is logically and physically optimized to generate a corresponding query plan; the query plan is sent to all computing nodes so that each computing node executes the query plan separately, obtains the corresponding execution results, and sends the execution results to the master node; all received execution results are summarized, and the total execution result obtained after summary is returned to the corresponding client.

[0093] Specifically, when the optimizer determines that the grouped aggregation query of the partitioned table does not meet the preset conditions, it cannot optimize the grouped aggregation query, that is, it cannot group the grouped aggregation operation into the partitions of the partitioned table. However, by performing logical and physical optimization on the query tree, it can select an optimal or near-optimal plan from all possible query plans to generate a corresponding query plan. The query plan usually describes how to execute a query in a tree structure. After the optimizer generates the query plan, it sends the query plan to the executors of all computing nodes through the scheduler. After the executors of each computing node receive the query plan sent by the scheduler, they start to execute the relevant plans they are responsible for to obtain the corresponding execution results, and send the execution results to the scheduler of the master node. The scheduler summarizes all the execution results received and returns the total execution results obtained after summarization to the corresponding client.

[0094] It should be noted that the grouped aggregation query on the partitioned table in this embodiment can be replaced with other query operations, such as data scans, hash joins, and sorts. Other query operations can also be performed by performing logical and physical optimization on the query tree to generate a corresponding query plan. The query plan is then sent to all computing nodes, causing each computing node to execute the query plan, obtain the corresponding execution results, and send the execution results to the master node. All received execution results are then aggregated, and the aggregated execution result is returned to the corresponding client.

[0095] In this embodiment, when the group aggregation query of the partition table does not meet the preset conditions, the above method is used to process it, so that each computing node can process the query plan in parallel, thereby improving the query execution efficiency and enhancing the database performance.

[0096] In this embodiment, optionally, the method may further specifically include:

[0097] If the target command contained in the syntax tree is not a data manipulation language command, the target command is sent to all computing nodes through the local command program, so that each computing node executes the target command respectively, obtains the corresponding execution result, and sends the execution result to the master node.

[0098] Specifically, if the parser determines that the target command contained in the syntax tree is not a data manipulation language command, but other practical commands that cannot be optimized, such as database schema definition language (DDL) commands such as creating a table, it enters the local command program of the master node, and then distributed execution sends the target command to the executors of all computing nodes through the scheduler. After receiving the target command sent by the scheduler, the executor of the computing node executes the target command, obtains the corresponding execution result, and sends the execution result to the scheduler of the master node, so that the scheduler can summarize all the received execution results and return the total execution result to the corresponding client.

[0099] The scheduler in this embodiment is mainly responsible for issuing grouped and aggregated execution plans or issuing target commands, as well as collecting and aggregating execution results.

[0100] In this embodiment, when the target command included in the syntax tree is not a data manipulation language command, processing is performed using the above method, which is beneficial to improving query execution efficiency, saving time, and improving user experience.

[0101] Figure 2A This is a flow chart of another partition table grouping and aggregation query method provided by an embodiment of the present disclosure. This embodiment is applicable to the case where each computing node in a distributed database performs a grouping and aggregation query on a partition table. The partition table grouping and aggregation query method provided by this embodiment applied to each computing node can be executed by a partition table grouping and aggregation query device applied to each computing node. The device can be implemented in hardware and / or software and can be configured in a computer device. Figure 2A As shown, the method specifically includes the following:

[0102] S210, receiving a group aggregation execution plan sent by the master node, wherein the group aggregation execution plan is generated after the master node determines that the group aggregation query of the partition table meets the preset conditions and pushes the group aggregation operation down to each partition of the partition table.

[0103] When the master node determines that a partitioned table's group-and-aggregate query meets the preset conditions, it pushes the group-and-aggregate operation down to each partition of the partitioned table and generates a corresponding group-and-aggregate execution plan. The master node then sends the group-and-aggregate execution plan to all compute nodes, which then receive the group-and-aggregate execution plan sent by the master node.

[0104] S220, executing the group aggregation execution plan in parallel on each partition to obtain the corresponding execution result, and sending the execution result to the master node, so that the master node summarizes all the received execution results and returns the total execution result after summarization to the corresponding client.

[0105] After receiving the grouped aggregation execution plan sent by the master node, the computing node executes the grouped aggregation execution plan in parallel on each partition through its own executor. After the execution is completed, the corresponding execution result can be obtained and sent to the scheduler of the master node, so that the scheduler can summarize all the received execution results and return the total execution result after summary to the corresponding client.

[0106] In this embodiment, a group aggregation execution plan sent by a master node is received, wherein the group aggregation execution plan is generated after the master node determines that the group aggregation query of the partition table meets the preset conditions, and pushes down the group aggregation operation to each partition of the partition table; the group aggregation execution plan is executed in parallel on each partition to obtain the corresponding execution result, and the execution result is sent to the master node, so that the master node summarizes all the received execution results and returns the total execution result obtained after the summary to the corresponding client. The execution efficiency of the group aggregation query can be improved by the execution plan optimized by the group aggregation push-down and the parallel execution of the group aggregation operation of each partition during the execution process, thereby improving the database performance.

[0107] In this embodiment, optionally, executing the group-and-aggregate execution plan in parallel on each partition to obtain a corresponding execution result may specifically include:

[0108] According to the grouping and aggregation execution plan, obtaining the original partition data to be aggregated corresponding to each partition;

[0109] Performing a partial aggregation operation on the original partition data to obtain a first aggregation result;

[0110] Performing a data redistribution operation on the first aggregation result to obtain a second aggregation result;

[0111] performing a final aggregation operation on the second aggregation result to obtain a third aggregation result;

[0112] Perform an append operation on all third aggregation results to obtain corresponding execution results.

[0113] The master node sends a group aggregation execution plan containing the group aggregation operation of the partition table to all compute nodes. The compute nodes execute the received group aggregation execution plan in parallel on each partition. The specific execution process is as follows:

[0114] 1. Each computing node obtains the original partition data to be aggregated corresponding to each partition according to the grouping and aggregation execution plan, for example, obtaining the original partition data corresponding to each partition through a sequential scan (Seq Scan).

[0115] 2. Perform a partial aggregation operation on the original partitioned data to obtain a first aggregation result. When performing the partial aggregation operation, the grouped aggregation execution plan can be split into upper and lower execution plan slices or more execution plan slices, with each data motion node in the grouped aggregation execution plan as the boundary. Each execution plan slice will start a separate process for execution, so the partial aggregation operation on each partition is executed in parallel. At the same time, this embodiment adopts a shared-nothing distributed database architecture, and the computing nodes are also executed in parallel. This multi-level parallel approach within and between nodes improves performance and execution speed.

[0116] 3. This embodiment mainly adopts a shared-nothing distributed database architecture. The first aggregation result is the local calculation result of each computing node. In this case, the same group will be on each node, but the distributed database ultimately needs to obtain a global group aggregation result. Therefore, each computing node needs to perform a data redistribution (Redistribute Motion) operation on the first aggregation result with the value of the group column to obtain a second aggregation result, that is: each computing node re-hash the data of the first aggregation result to the corresponding node, thereby ensuring that the same group is only on one computing node, so that each computing node can obtain the global data of the group, so as to perform the final aggregation (Finalize Aggregate) operation later (Final aggregation is the global aggregation stage of group aggregation).

[0117] The second aggregation result can be understood as data obtained after performing a data redistribution operation on the first aggregation result.

[0118] 4. After receiving the second aggregation result of data redistribution, the partition of each computing node performs a final aggregation operation on the second aggregation result, that is, Finalize Aggregate of the global data of each group in the partition to obtain the third aggregation result.

[0119] 5. Finally, each node performs an Append operation on the third aggregation results of all partitions of the partition table, obtains the corresponding execution results, and sends the execution results to the master node.

[0120] Specifically, Figure 2B This is a schematic diagram of the query process before optimizing the grouped aggregate query provided by the embodiment of the present disclosure. Figure 2B As shown:

[0121] Figure 2BThe following example illustrates the query process for a non-optimized group-and-aggregate query using two compute nodes: First, each compute node performs a sequential scan on the table partitions, including Seq Scan on table partition 1, Seq Scan on table partition 1, and so on, to Seq Scan on table partition n (where n is a positive integer greater than 2), obtaining the raw data corresponding to each partition. An Append operation is then performed on each raw data item to obtain the total raw data. Group-and-aggregate operations are then performed on the total raw data, including the PartialAggregate, Redistribute Motion, and FinalizeAggregate operations to obtain the aggregated results, which are then sent to the master node. The master node then performs GatherMotion to obtain the aggregated results, i.e., the query results. The master node also returns the query results to the client.

[0122] In the above method, the grouping and aggregation operations between partitions cannot be executed in parallel, so the query performance is poor.

[0123] Specifically, Figure 2C Schematic diagram of the query process after optimizing the grouped aggregate query provided by the embodiment of the present disclosure. Figure 2C As shown:

[0124] Figure 2C In this paper, two computing nodes are used as an example to illustrate the query process after optimizing the group aggregation query: First, a sequential scan is performed on the table partitions in each computing node to obtain the original partition data to be aggregated corresponding to each partition. This process is the same as Figure 2B The operations are similar and will not be repeated here. Next, group aggregation is performed on each original partition data, including the PartialAggregate operation, the Redistribute Motion operation, and the Finalize Aggregate operation, to obtain the aggregation results corresponding to each partition. The aggregation results for each partition are then appended to obtain the corresponding execution results, which are sent to the master node. The master node then performs the Gather Motion operation to obtain the consolidated results, i.e., the query results. The master node also returns the query results to the client.

[0125] When the grouped aggregation query of the partition table meets the preset conditions, Figure 2C The method shown pushes the grouping and aggregation operations down to the partitions of the partition table (i.e., pushes the grouping and aggregation operations down). Figure 2BIn the above example, the original data corresponding to each partition is appended to obtain the total original data, and then the execution plan of group aggregation is modified to: first perform group aggregation on the original partition data to be aggregated corresponding to each partition of the partitioned table, and then append the aggregation results corresponding to each partition to obtain the execution plan corresponding to the execution result of the entire partitioned table. Because the group aggregation query of the partitioned table uses the partition key as the grouping condition, the data of a group only appears in one partition, that is, the partitions are unrelated, so the merging of the group aggregation results of each partition is the final execution result of the partitioned table. Among them, for the case where the group aggregation query of the partitioned table does not meet the preset conditions, the above method cannot be used due to correctness issues, and the above optimization is not performed in this case. Figure 2C The method shown can significantly improve the query execution efficiency, enhance the query performance of the database and enhance the user experience.

[0126] For example, Figure 2D This is a schematic diagram of the execution result before optimizing the grouped aggregation query provided by the embodiment of the present disclosure. Figure 2D To explain:

[0127] Suppose a grouped aggregation query is performed on a partitioned table distributed across two compute nodes, where the query is grouped by the partition key, and the distribution key is not the partition key. First, create the partitioned table and each partition, and insert data into the partitioned table. agg_partition is the partition base table, and agg_partition_p1, agg_partition_p2, and agg_partition_p3 are partitions. The SQL statement is as follows:

[0128] CREATE TABLE agg_partition(a int,b int,c text)PARTITION BY RANGE(a)distributed by(c);

[0129] CREATE TABLE agg_partition_p1 PARTITION OF agg_partition FOR VALUESFROM(0)TO(10);

[0130] CREATE TABLE agg_partition_p2 PARTITION OF agg_partition FOR VALUESFROM(10)TO(20);

[0131] CREATE TABLE agg_partition_p3 PARTITION OF agg_partition FOR VALUESFROM(20)TO(30);

[0132] INSERT INTO agg_partition SELECT i%30,i%50,to_char(i%30,'FM0000')FROM generate_series(1,30000000)i;

[0133] Group aggregation execution results of the partition table before optimization:

[0134] SELECT a,sum(b)FROM agg_partition GROUP BY a ORDER BY 1;

[0135] from Figure 2D It can be seen that the grouping and aggregation time of the partition table before optimization is 11707.352ms.

[0136] For example, Figure 2E This is a schematic diagram of the execution result after optimizing the group aggregation query provided by the embodiment of the present disclosure. Figure 2E To explain:

[0137] The same grouping and aggregation query is performed on the partition table distributed on two computing nodes. The optimized grouping and aggregation execution results of the partition table are as follows:

[0138] SELECT a,sum(b)FROM agg_partition GROUP BY a ORDER BY 1;

[0139] from Figure 2E It can be seen that the time for grouping and aggregation of the optimized partitioned table is 3601.263ms. By pushing the grouping and aggregation operations down to each partition for execution, the query performance is significantly improved.

[0140] In this embodiment, when it is determined that the group aggregation query of the partition table meets the preset conditions, the group aggregation on the partition table is pushed down to each partition for parallel execution, which speeds up the execution efficiency and improves the performance. The performance improvement is related to the number of partitions used in the partition table for the group aggregation operation. The more partitions there are, the higher the parallelism and the greater the improvement.

[0141] Figure 3 This is a schematic diagram of the interaction process between the client, the master node, and the computing node provided by the embodiment of the present disclosure. Figure 3 As shown:

[0142] Figure 3 The following describes how a client sends an SQL request to the master node. The master node parses the SQL request, generates a corresponding syntax tree, and then performs the corresponding operation based on whether the target command contained in the syntax tree is a DML command. The master node sends an execution plan or execution command to each compute node (compute node 1, compute node 2, ..., compute node N). The executor in each compute node executes the query or command and returns the execution result to the master node, which then returns the execution result to the client.

[0143] Figure 3 The interaction process has been described in detail in the previous embodiments and will not be repeated here.

[0144] Figure 4 This is a schematic diagram of the structure of a partition table grouping and aggregation query device provided by an embodiment of the present disclosure; the device is configured in a computer device and can implement the partition table grouping and aggregation query method applied to the master node described in any embodiment of the present application. The device specifically includes the following:

[0145] A generation module 410 is configured to push down the grouping and aggregation operation to each partition of the partition table and generate a corresponding grouping and aggregation execution plan when determining that the grouping and aggregation query of the partition table meets a preset condition;

[0146] A sending module 420 is configured to send the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains corresponding execution results, and sends the execution results to the master node;

[0147] The summarizing module 430 is used to summarize all received execution results and return the total execution result obtained after summarization to the corresponding client.

[0148] In this embodiment, optionally, the apparatus further includes: a determination module, specifically configured to:

[0149] When it is determined that the grouped aggregation query of the partition table meets the preset conditions, the grouped aggregation operation is pushed down to each partition of the partition table, and before the corresponding grouped aggregation execution plan is generated, the grouping conditions corresponding to the grouped aggregation query are obtained;

[0150] It is determined according to the grouping condition that the group aggregation query meets a preset condition.

[0151] In this embodiment, optionally, the group aggregation query is obtained by:

[0152] Receive a query request sent by a client, parse the query request, and generate a corresponding syntax tree;

[0153] If the target command included in the syntax tree is a data manipulation language command, performing semantic analysis and query rewriting on the syntax tree to obtain a corresponding query tree;

[0154] The query tree is extracted to obtain the grouping and aggregation query.

[0155] In this embodiment, optionally, the apparatus further includes: a sending module, specifically configured to:

[0156] If the target command contained in the syntax tree is not a data manipulation language command, the target command is sent to all computing nodes through the local command program, so that each computing node executes the target command respectively, obtains the corresponding execution result, and sends the execution result to the master node.

[0157] By using the partition table grouping and aggregation query device provided by the embodiment of the present disclosure, when it is determined that the partition table grouping and aggregation query meets the preset conditions, the grouping and aggregation operation is pushed down to each partition of the partition table to generate a corresponding grouping and aggregation execution plan; the grouping and aggregation execution plan is sent to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition to obtain a corresponding execution result, and the execution result is sent to the master node; all received execution results are summarized, and the total execution result obtained after the summary is returned to the corresponding client. By using the execution plan optimized by grouping and aggregation push-down and executing the grouping and aggregation operation of each partition in parallel during the execution process, the execution efficiency of the grouping and aggregation query can be improved, and the database performance can be improved.

[0158] The grouping and aggregation query device for the partition table applied to the master node provided by the embodiment of the present disclosure can execute the grouping and aggregation query method for the partition table applied to the master node provided by any embodiment of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method.

[0159] Figure 5 This is a schematic diagram of the structure of another partition table grouping and aggregation query device provided by an embodiment of the present disclosure. This device is configured in a computer device and can implement the partition table grouping and aggregation query method applied to each computing node described in any embodiment of the present application. The device specifically includes the following:

[0160] A receiving module 510 is configured to receive a group-and-aggregate execution plan sent by a master node, wherein the group-and-aggregate execution plan is generated by the master node after pushing down the group-and-aggregate operation to each partition of the partition table when the master node determines that the group-and-aggregate query of the partition table meets a preset condition;

[0161] The execution module 520 is used to execute the group aggregation execution plan in parallel on each partition, obtain the corresponding execution result, and send the execution result to the master node so that the master node can summarize all the received execution results and return the total execution result after summary to the corresponding client.

[0162] In this embodiment, optionally, the execution module 520 is specifically configured to:

[0163] According to the grouping and aggregation execution plan, obtaining the original partition data to be aggregated corresponding to each partition;

[0164] Performing a partial aggregation operation on the original partition data to obtain a first aggregation result;

[0165] Performing a data redistribution operation on the first aggregation result to obtain a second aggregation result;

[0166] performing a final aggregation operation on the second aggregation result to obtain a third aggregation result;

[0167] An append operation is performed on all third aggregation results to obtain corresponding execution results, and the execution results are sent to the master node, so that the master node summarizes all received execution results and returns the total execution result obtained after summarization to the corresponding client.

[0168] The partition table grouping and aggregation query device provided by the embodiment of the present disclosure receives a grouping and aggregation execution plan sent by a master node, wherein the grouping and aggregation execution plan is generated after the master node determines that the grouping and aggregation query of the partition table meets the preset conditions, pushes down the grouping and aggregation operation to each partition of the partition table; executes the grouping and aggregation execution plan, obtains the corresponding execution result, and sends the execution result to the master node, so that the master node summarizes all the received execution results and returns the total execution result obtained after the summary to the corresponding client. The execution efficiency of the grouping and aggregation query can be improved by pushing down the optimized execution plan of the grouping and aggregation and executing the grouping and aggregation operations of each partition in parallel during the execution process, thereby improving the database performance.

[0169] The grouped aggregation query device for partition tables applied to each computing node provided in the embodiment of the present disclosure can execute the grouped aggregation query method for partition tables applied to each computing node provided in any embodiment of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method.

[0170] Figure 6 This is a schematic diagram of the structure of a computer device provided by an embodiment of the present disclosure. Figure 6 As shown, the computer device includes a processor 610 and a storage device 620; the number of processors 610 in the computer device can be one or more. Figure 6In the figure, a processor 610 is used as an example; the processor 610 and the storage device 620 in the computer device can be connected via a bus or other means. Figure 6 The bus connection is taken as an example.

[0171] Storage device 620, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and modules, such as the program instructions / modules corresponding to the grouped aggregate query method for partitioned tables applied to the master node in the embodiments of the present disclosure; or the program instructions / modules corresponding to the grouped aggregate query method for partitioned tables applied to each computing node in the embodiments of the present disclosure. Processor 610 executes the software programs, instructions, and modules stored in storage device 620 to execute various functional applications and data processing of the computer device, thereby implementing the grouped aggregate query method for partitioned tables provided in the embodiments of the present disclosure.

[0172] The storage device 620 may primarily include a program storage area and a data storage area. The program storage area may store an operating system and at least one application required for a function; the data storage area may store data created based on the use of the terminal, etc. Furthermore, the storage device 620 may include high-speed random access memory and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state memory device. In some instances, the storage device 620 may further include memory remotely located relative to the processor 610, and such remote memory may be connected to the computer device via a network. Examples of such networks include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0173] The computer device provided in this embodiment can be used to execute the grouping and aggregation query method of the partition table provided in any of the above embodiments, and has corresponding functions and beneficial effects.

[0174] The embodiment of the present disclosure further provides a storage medium containing computer-executable instructions, which, when executed by a computer processor, are used to implement the partition table grouping and aggregation query method provided by the embodiment of the present disclosure.

[0175] Of course, the computer executable instructions of a storage medium provided by an embodiment of the present disclosure are not limited to the operations of the method described above, but can also execute related operations in the grouping and aggregation query method for a partitioned table provided by any embodiment of the present disclosure.

[0176] Through the above description of the implementation methods, those skilled in the art can clearly understand that the present disclosure can be implemented with the help of software and necessary general-purpose hardware, and of course it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present disclosure is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as a computer's floppy disk, read-only memory (ROM), random access memory (RAM), flash memory (FLASH), hard disk or optical disk, etc., including a number of instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute the methods described in each embodiment of the present disclosure.

[0177] It is worth noting that in the embodiment of the grouped aggregation query device for the above-mentioned partition table, the various units and modules included are only divided according to functional logic, but are not limited to the above-mentioned division, as long as the corresponding functions can be achieved; in addition, the specific names of the functional units are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this disclosure.

[0178] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, article, or device comprising the element.

[0179] The foregoing description is intended only to provide specific embodiments of the present disclosure, intended to enable those skilled in the art to understand and implement the present disclosure. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure is not intended to be limited to the embodiments described herein, but rather to be construed in the broadest manner consistent with the principles and novel features disclosed herein.

Claims

1. A grouping and aggregation query method for a partitioned table, characterized in that: Applied to the master node, the method includes: When it is determined that the grouping and aggregation query of the partition table meets the preset conditions, the grouping and aggregation operation is pushed down to each partition of the partition table, and a corresponding grouping and aggregation execution plan is generated; Sending the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains corresponding execution results, and sends the execution results to the master node; Summarize all received execution results and return the total execution result to the corresponding client; When it is determined that the grouped aggregation query of the partition table meets the preset conditions, the grouped aggregation operation is pushed down to each partition of the partition table, and before the corresponding grouped aggregation execution plan is generated, the method further includes: obtaining a grouping condition corresponding to the grouped aggregation query; and determining, based on the grouping condition, that the grouped aggregation query meets the preset conditions; the preset conditions are that the partition key of the partition table is used as the grouping condition, and the partition key is not a distribution key.

2. The method according to claim 1, characterized in that The grouped aggregation query is obtained in the following way: Receive a query request sent by a client, parse the query request, and generate a corresponding syntax tree; If the target command included in the syntax tree is a data manipulation language command, performing semantic analysis and query rewriting on the syntax tree to obtain a corresponding query tree; The query tree is extracted to obtain the grouping and aggregation query.

3. The method according to claim 2, characterized in that Also includes: If the target command contained in the syntax tree is not a data manipulation language command, the target command is sent to all computing nodes through the local command program, so that each computing node executes the target command respectively, obtains the corresponding execution result, and sends the execution result to the master node.

4. A grouping and aggregation query method for a partition table, characterized in that: Applied to each computing node, the method includes: receiving a grouping and aggregation execution plan sent by a master node, wherein the grouping and aggregation execution plan is generated by the master node obtaining a grouping condition corresponding to the grouping and aggregation query, and when determining, based on the grouping condition, that the grouping and aggregation query of the partition table meets a preset condition, pushing down the grouping and aggregation operation to each partition of the partition table, wherein the preset condition is to use a partition key of the partition table as the grouping condition, and the partition key is not a distribution key; The group aggregation execution plan is executed in parallel on each partition to obtain corresponding execution results, and the execution results are sent to the master node so that the master node summarizes all received execution results and returns the total execution result obtained after summarization to the corresponding client.

5. The method according to claim 4, characterized in that The executing the group aggregation execution plan in parallel on each partition to obtain corresponding execution results includes: According to the grouping and aggregation execution plan, obtaining the original partition data to be aggregated corresponding to each partition; Performing a partial aggregation operation on the original partition data to obtain a first aggregation result; Performing a data redistribution operation on the first aggregation result to obtain a second aggregation result; performing a final aggregation operation on the second aggregation result to obtain a third aggregation result; Perform an append operation on all third aggregation results to obtain corresponding execution results.

6. A partition table grouping and aggregation query device, characterized in that: Applied to a master node, the device comprises: A generation module, configured to push down the grouping and aggregation operation to each partition of the partition table and generate a corresponding grouping and aggregation execution plan when determining that the grouping and aggregation query of the partition table meets the preset conditions; a sending module, configured to send the grouping and aggregation execution plan to all computing nodes, so that each computing node executes the grouping and aggregation execution plan in parallel on each partition, obtains a corresponding execution result, and sends the execution result to the master node; The summary module is used to summarize all received execution results and return the total execution result obtained after summary to the corresponding client; The generation module is further used to push the grouping and aggregation operation down to each partition of the partition table when it is determined that the grouping and aggregation query of the partition table meets the preset conditions, and before generating the corresponding grouping and aggregation execution plan, obtain the grouping conditions corresponding to the grouping and aggregation query; determine that the grouping and aggregation query meets the preset conditions based on the grouping conditions; the preset conditions are that the partition key of the partition table is the grouping condition, and the partition key is not the distribution key.

7. A grouping and aggregation query device for a partition table, characterized in that: Applied to each computing node, the device includes: a receiving module, configured to receive a grouping and aggregation execution plan sent by a master node, wherein the grouping and aggregation execution plan is generated by the master node obtaining a grouping condition corresponding to the grouping and aggregation query, and when it is determined according to the grouping condition that the grouping and aggregation query of the partition table meets a preset condition, pushing down the grouping and aggregation operation to each partition of the partition table, wherein the preset condition is to use the partition key of the partition table as the grouping condition, and the partition key is not a distribution key; The execution module is used to execute the group aggregation execution plan in parallel on each partition, obtain the corresponding execution result, and send the execution result to the master node so that the master node can summarize all the received execution results and return the total execution result obtained after summary to the corresponding client.

8. A computer device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 5.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images