Aggregated query method and apparatus
By estimating NDV and controlling the storage threshold on the database server node, the problem of the database optimizer having difficulty selecting the grouping pushdown algorithm in parallel scenarios is solved, achieving more efficient CPU resource utilization and data skew handling, and improving database query performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING OCEANBASE TECHNOLOGY CO LTD
- Filing Date
- 2023-04-28
- Publication Date
- 2026-05-12
AI Technical Summary
In existing technologies, database optimizers have difficulty accurately selecting grouping pushdown algorithms, which may lead to incorrect decisions in parallel scenarios, resulting in increased CPU overhead and data skew issues.
By estimating the number of unique values (NDV) of the column to be aggregated in the data stream on the server node, a target storage threshold is determined when a set condition is met, CPU overhead is controlled, and NDV estimation technology is introduced into the adaptive algorithm to help handle data skew.
It reduces CPU overhead, avoids unnecessary network communication overhead, solves data skew issues, and improves database query performance.
Smart Images
Figure CN116501756B_ABST
Abstract
Description
Technical Field
[0001] This specification relates to the field of database technology in one or more embodiments, and in particular to an aggregation query method and apparatus. Background Technology
[0002] Group By Pushdown (GPD) is an optimization technique for databases to compute aggregations in parallel scenarios. Specifically, it involves pre-aggregating data locally before network transmission, and then distributing the pre-aggregated data to different worker threads to complete the final aggregation. GPD offers excellent scalability and can reduce data distribution costs in scenarios with high aggregation rates.
[0003] In related technologies, such execution plans are typically determined by the optimizer based on statistical information from the actual data in the database tables, choosing whether to use the GPD algorithm. However, because the optimizer may struggle to obtain accurate statistical information in some cases, it may be unable to make the correct grouping and pushing decisions. Summary of the Invention
[0004] In view of this, one or more embodiments of this specification provide an aggregation query method and apparatus.
[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:
[0006] According to a first aspect of one or more embodiments of this specification, an aggregation query method is proposed, applied to one or more server nodes, comprising:
[0007] Receive the data stream of the database table targeted by the aggregate query request, wherein the database table is distributed across the multiple server nodes;
[0008] Determine the number of non-duplicate values (NDV) of the column to be aggregated in the first data in the data stream, and send the first data to the target node, wherein the target node is one of the plurality of server nodes;
[0009] If the NDV reaches the first set condition, a target storage threshold is determined based on the NDV, and the transmission of the first data is stopped;
[0010] The aggregation operation indicated by the aggregation query request is performed on the second data in the received data stream, and the aggregation result is sent to the target node. The storage area used by the aggregation result does not exceed the target storage threshold.
[0011] In some embodiments, the method further includes:
[0012] The deduplication rate of the first data is obtained at a set period, and the deduplication rate is determined based on the ratio of the NDV to the total amount of the received first data.
[0013] If the deduplication rate is greater than the first proportional threshold, it is determined that the NDV meets the first set condition.
[0014] In some embodiments, before determining the number of unique values (NDV) in the column to be aggregated of the first data in the data stream, the method further includes:
[0015] The aggregation operation indicated by the aggregation query request is performed on the third data in the data stream, and the aggregation result is sent to the target node. The storage area used by the aggregation result does not exceed the initial storage threshold.
[0016] If the aggregation result does not meet the second set condition, determine the NDV in the column to be aggregated of the first data in the data stream, and send the first data to the target node.
[0017] In some embodiments, performing the aggregation operation indicated by the aggregation query request on the third data in the data stream and sending the aggregation result to the target node includes:
[0018] If the storage area used by the aggregation result reaches the initial storage threshold, obtain the deduplication rate of the aggregation result;
[0019] If the deduplication rate is less than the second proportional threshold, after sending the aggregation result, the aggregation result stored in the storage area is cleared, and the aggregation result is regenerated based on the received third data.
[0020] In some embodiments, the method further includes:
[0021] If the number of times the aggregation result is cleared consecutively reaches a set threshold, it is determined that the aggregation result does not meet the second set condition.
[0022] In some embodiments, the initial storage threshold is determined according to any one of the following:
[0023] The available memory size of the one or more server nodes;
[0024] The available processor cache size of the one or more computing server nodes.
[0025] In some embodiments, the method further includes:
[0026] If the deduplication rate is greater than or equal to the second proportional threshold, the initial storage threshold is increased, and the received third data is aggregated based on the increased initial storage threshold.
[0027] In some embodiments, the storage area includes a processor L2 cache.
[0028] According to a second aspect of one or more embodiments of this specification, an aggregation query apparatus is provided, applied to one or more server nodes, comprising:
[0029] The receiving unit is used to receive the data stream of the database table targeted by the aggregation query request, wherein the database table is distributed on the multiple server nodes;
[0030] A bypass unit is used to determine the number of non-duplicate values (NDV) of the column to be aggregated in the data stream and send the first data to the target node, wherein the target node is one of the plurality of server nodes;
[0031] The determining unit is configured to, when the NDV reaches a first preset condition, determine a target storage threshold based on the NDV and stop sending the first data;
[0032] A pre-aggregation unit is used to pre-aggregate the first data in the data stream and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed a set storage threshold. The target node is one of the plurality of server nodes.
[0033] The update unit is used to perform the aggregation operation indicated by the aggregation query request on the second data in the data stream to be received, and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed the target storage threshold.
[0034] In some embodiments, the apparatus further includes a first determining unit, configured to:
[0035] The deduplication rate of the first data is obtained at a set period, and the deduplication rate is determined based on the ratio of the NDV to the total amount of the received first data.
[0036] If the deduplication rate is greater than the first proportional threshold, it is determined that the NDV meets the first set condition.
[0037] In some embodiments, the apparatus further includes a second pre-aggregation unit, configured to: perform an aggregation operation on the third data in the data stream as indicated by the aggregation query request before determining the number of non-duplicate values NDV in the column to be aggregated of the first data in the data stream, and send the aggregation result to the target node, wherein the storage area used by the aggregation result does not exceed an initial storage threshold;
[0038] If the aggregation result does not meet the second set condition, determine the NDV in the column to be aggregated of the first data in the data stream, and send the first data to the target node.
[0039] In some embodiments, when the second pre-aggregation unit performs the aggregation operation indicated by the aggregation query request on the third data in the data stream and sends the aggregation result to the target node, it is specifically used for:
[0040] If the storage area used by the aggregation result reaches the initial storage threshold, obtain the deduplication rate of the aggregation result;
[0041] If the deduplication rate is less than the second proportional threshold, after sending the aggregation result, the aggregation result stored in the storage area is cleared, and the aggregation result is regenerated based on the received third data.
[0042] In some embodiments, the apparatus further includes a second determining unit, configured to:
[0043] If the number of times the aggregation result is cleared consecutively reaches a set threshold, it is determined that the aggregation result does not meet the second set condition.
[0044] In some embodiments, the initial storage threshold is determined according to any one of the following:
[0045] The available memory size of the one or more server nodes;
[0046] The available processor cache size of the one or more computing server nodes.
[0047] In some embodiments, the apparatus further includes a lifting unit for:
[0048] If the deduplication rate is greater than or equal to the second proportional threshold, the initial storage threshold is increased, and the received third data is aggregated based on the increased initial storage threshold.
[0049] In some embodiments, the storage area includes a processor L2 cache.
[0050] According to a third aspect of one or more embodiments of this specification, a database system is provided, comprising:
[0051] The client is used to receive aggregate query requests;
[0052] The scheduling node is used to determine one or more server nodes that store the database table targeted by the aggregate query request, and to distribute the aggregate query task to the one or more server nodes.
[0053] One or more server nodes are used to execute the methods proposed in the above embodiments.
[0054] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, comprising:
[0055] processor;
[0056] Memory used to store processor-executable instructions;
[0057] The processor implements the method proposed in the above embodiments by running the executable instructions.
[0058] According to a fifth aspect of one or more embodiments of this specification, a computer-readable storage medium is provided that stores computer instructions thereon, which, when executed by a processor, implement the steps of the method proposed in the above embodiments.
[0059] In the embodiments of this specification, for the data stream of the database table targeted by the aggregation query request, the NDV of the column to be aggregated in the first data in the data stream is determined, and the first data is sent to the target node; if the NDV reaches a first set condition, a target storage threshold is determined based on the NDV, and the sending of the first data is stopped; then, the aggregation operation indicated by the aggregation query request is performed on the second data in the data stream to be received, and the aggregation result is sent to the target node, wherein the storage area used by the aggregation result does not exceed the target storage threshold. By directly sending the received first data without aggregation and determining the NDV during data reception, the upper limit of the storage area that the aggregation result can use is adjusted according to the actual data distribution, which can reduce CPU overhead, avoid unnecessary network communication overhead, and solve the data skew problem caused by optimizer decision-making in related technologies. Attached Figure Description
[0060] Figure 1 This is a system architecture diagram of a distributed database provided in an exemplary embodiment.
[0061] Figure 2 This is a flowchart of an exemplary embodiment of an aggregation query method.
[0062] Figure 3 This is a flowchart of another aggregation query method provided in an exemplary embodiment.
[0063] Figure 4 This is a block diagram of an aggregate query apparatus provided in an exemplary embodiment.
[0064] Figure 5 This is a schematic diagram of the structure of a device provided in an exemplary embodiment. Detailed Implementation
[0065] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.
[0066] It should be noted that the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification in other embodiments. In some other embodiments, the methods may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.
[0067] Grouped pushdown (GPD) is an optimization technique for databases to compute aggregations in parallel scenarios. Specifically, before data is transmitted over the network, a pre-aggregation of the data is performed locally. The pre-aggregated data is then distributed to different worker threads to complete the final aggregation. GPD offers good scalability and can reduce data distribution costs in scenarios with high aggregation rates. However, GPD incurs an additional CPU overhead for performing the aggregation once, which can lead to performance degradation if the pre-aggregation deduplication is not effective enough.
[0068] In practical production, we often encounter group pushdown plans in parallel scenarios. In related technologies, such execution plans are typically determined by the optimizer based on statistical information from database tables, choosing whether to use the GPD algorithm. However, because the optimizer may struggle to obtain accurate statistical information in some cases, it may fail to make the correct group pushdown decision.
[0069] In view of this, the embodiments of this specification propose an aggregation query method that does not rely on the optimizer's estimation, but controls the CPU overhead by performing adaptive pre-aggregation by the executor itself. Furthermore, the adaptive algorithm introduces an estimation technique for the number of distinct values (NDV) in the expected aggregation column, which can assist in adaptive decision-making to handle scenarios with data skew.
[0070] The query method proposed in this disclosure is applicable to aggregate queries in distributed databases. The system architecture of a distributed database can be, for example, as follows: Figure 1As shown. This distributed database may include a scheduling node, multiple server nodes, and clients. Clients are connected to the scheduling node, and the scheduling node is connected to each server node via a network. The data in the distributed database tables is typically distributed across the storage of multiple server nodes. When a client receives an aggregation query request, the scheduling node determines the server node that stores the database table targeted by the aggregation query request and forwards the request to the processor (executor) of the server node that stores that database table. The aggregation query method proposed in this embodiment is executed by one or more server nodes that store the database table targeted by the aggregation query request.
[0071] Figure 2 This is a flowchart of an exemplary embodiment of an aggregation query method, the method comprising:
[0072] Step 201: Receive the data stream of the database table targeted by the aggregation query request.
[0073] The aggregate query request can be an SQL (Structured Query Language) aggregate query request issued by the user, or it can be an aggregate query request using other types of database query languages.
[0074] Taking an SQL aggregation query request as an example, this request includes the database table to be queried. This database table is distributed and stored in the memory of one or more server nodes executing the aggregation query. The processor in each server node retrieves the real-time data stream generated from the locally stored database table and can distribute or pre-aggregate the data in this stream. This data stream may include data from the database table, or it may include intermediate processing results of the data from the database table.
[0075] In some embodiments, an SQL aggregation query request may include an aggregation function and a grouping clause for each column to be aggregated.
[0076] The aggregate function may include one or a combination of the following functions: SUM, COUNT, MAX, MIN, AVG; the grouping clause may be, for example, a group by clause.
[0077] For example, an SQL aggregation query request could be:
[0078] Select c1,SUM(c2)from t1 group by c1
[0079] Where t1 is the database table to be queried; c1 and c2 are column data in database table t1. This query request is used to request the sum of c2 data in each group after grouping database table t1 according to the data in c1.
[0080] by Figure 1 Taking the distributed database system shown as an example, assuming that multiple partitions of database table t1 are stored separately on server node 1 and server node 2, when the scheduling node receives an aggregation query request for database table t1 and detects that the table is distributed across server nodes 1 and 2, it distributes the aggregation query task to the processors of server node 1 and server node 2. Upon receiving the task, server nodes 1 and 2 retrieve the data from their locally stored database table t1 and receive the real-time data stream generated by database table t1.
[0081] Step 202: Determine the number of non-duplicate values NDV of the column to be aggregated in the first data in the data stream, and send the first data to the target node.
[0082] The target node is one of the plurality of server nodes. During the group pushdown process, the target node receives the pre-aggregation results sent by each server node, obtains the final aggregated query result based on the received pre-aggregation results, and returns the aggregated query result to the client that issued the request.
[0083] In this step, the data in the data stream is processed in a streaming manner. That is, the NDV of the first received data is estimated in real time. For example, the current NDV is estimated for each row of data received from the database. To distinguish the data received in this step from the data received in other stages, the data received here can be referred to as the first data.
[0084] In one example, for each received row of data, dots can be added to a bitmap based on the value of the column to be processed. Duplicate values will appear in the same position in the bitmap, while different values will likely appear in different positions. Therefore, the distribution of these dots can be used to estimate the NDV (Number of Distances).
[0085] The NDV estimation method described above has low space complexity and can estimate the NDV of large data volumes with relatively small memory usage.
[0086] The process of sending the first data directly in step 202 without aggregation is called bypass processing.
[0087] Step 203: When the NDV reaches the first set condition, determine the target storage threshold based on the NDV and stop sending the first data.
[0088] When the amount of data received is small, the NDV distribution may deviate from the true NDV distribution. However, as the amount of data processed increases, the NDV becomes closer to the true distribution. Therefore, if the total amount of data sent directly in step 202 (the amount of data processed by bypass) reaches the preset total amount of data, step 203 can be executed, so that the estimated NDV can reflect the true NDV distribution.
[0089] In some embodiments, the ratio of the NDV to the total number of received first data can be obtained at a set period to determine the deduplication rate of the first data. The deduplication rate is calculated according to the following formula: Deduplication rate = 1 - (NDV / total number).
[0090] For example, the deduplication rate can be determined by obtaining the ratio once every time a set amount of the first data is received; alternatively, the ratio can be obtained at set intervals. For instance, the deduplication rate can be determined every 1000 rows of the first data received.
[0091] If the deduplication rate is greater than a first proportional threshold, the NDV is determined to meet a first set condition. This deduplication rate also reflects the aggregation rate of data in the database table estimated by the server node. A higher deduplication rate indicates a higher aggregation rate and better aggregation effect; conversely, a lower deduplication rate indicates a worse aggregation effect. The aforementioned first proportional threshold can be set according to the desired deduplication rate or aggregation rate.
[0092] Steps 202-203 enable execution-time NDV estimation, allowing for the estimation of the aggregation rate of data in the database table to be queried. If the aggregation rate is high, bypass processing can be stopped, and pre-aggregation operations can begin on subsequently received data to reduce data distribution costs. Conversely, if the aggregation rate is poor, performing additional pre-aggregation increases CPU overhead, leading to performance degradation. In such cases, the additional CPU overhead can be avoided by directly sending data from the database table, allowing the target node to perform aggregation queries on the received data.
[0093] Furthermore, by obtaining actual data to estimate NDV, a more accurate estimate of NDV can be achieved, which can solve the data skew problem caused by using optimizers for statistical information estimation in related technologies.
[0094] In this step, if the NDV reaches the first set condition, the NDV can be determined as the target storage threshold, which is used for pre-aggregation of subsequently received data.
[0095] Step 204: Perform the aggregation operation indicated by the aggregation query request on the second data in the data stream to be received, and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed the target storage threshold.
[0096] After stopping the bypass process, the data in the subsequently received data stream can be grouped and pushed down using the target storage threshold determined during the bypass process. To distinguish the data processed in this step from the data processed in other stages, the data in the received data stream here can be referred to as the second data.
[0097] Specifically, the server node performs an aggregation operation on the second data in the data stream received after the bypass processing is completed. The aggregation result is stored in memory or the processor's cache. The target storage threshold is used as the upper limit of the storage area used, i.e., the upper limit of memory usage or cache usage. In this specification, the aggregation operation performed before network transmission can be called a pre-aggregation operation, and the aggregation result obtained from this operation is called the pre-aggregation result.
[0098] Multiple server nodes can perform the above aggregation query process in parallel. By first performing pre-aggregation on each server node, and then summarizing the pre-aggregation results of each node to the target node for the final aggregation query, the amount of aggregated data is greatly reduced, effectively reducing network communication overhead, and avoiding having all aggregation query work handled by a single node.
[0099] In the embodiments of this specification, for the data stream of the database table targeted by the aggregation query request, the NDV of the column to be aggregated in the first data in the data stream is determined, and the first data is sent to the target node; if the NDV reaches a first set condition, a target storage threshold is determined based on the NDV, and the sending of the first data is stopped; then, the aggregation operation indicated by the aggregation query request is performed on the second data in the data stream to be received, and the aggregation result is sent to the target node, wherein the storage area used by the aggregation result does not exceed the target storage threshold. By directly sending the received first data without aggregation and determining the NDV during data reception, the upper limit of the storage area that the aggregation result can use is adjusted according to the actual data distribution, which can reduce CPU overhead, avoid unnecessary network communication overhead, and solve the data skew problem caused by optimizer decision-making in related technologies.
[0100] In some embodiments, when an aggregation query request includes an aggregation function and a grouping clause, data aggregation in a data stream can be achieved in the following ways:
[0101] Group the data into the grouping column indicated by the grouping clause;
[0102] The aggregation function is used to perform aggregation operations on the data of each group to obtain the aggregation result.
[0103] In one example, when the grouping clause includes `group by`, the data can be grouped by retrieving the hash value of the grouping column indicated by `group by` as the key. The aggregation result can then be stored as a hash table, which includes the hash values corresponding to each group.
[0104] Taking the SQL aggregation query request "Select c1,SUM(c2)from t1 group by c1" as an example, the database table t1 targeted by this query is as follows:
[0105] Database table t1
[0106] ID c1 c2 1111 16 23 2222 16 45 3333 17 36 4444 17 76 5555 17 23 6666 18 65
[0107] For the received multiple rows of data in database table t1, the data in database table t1 is first grouped according to the grouping column c1 specified by GROUP BY. Specifically, the hash value of each data in grouping column c1 is obtained, and the data is grouped according to the hash value. Then, the aggregation operation of summation is performed on the data in the aggregation column c2 of each group using the aggregation function SUM to obtain the aggregation result. The hash table t2 constructed based on this aggregation result is shown below:
[0108] Hash table t2
[0109] Hash value c1 c2 HASH(16) 16 68 HASH(17) 17 135 HASH(18) 18 65
[0110] Those skilled in the art will understand that the grouping clause may also include other clauses, such as the Distinct clause, and this specification does not limit this.
[0111] When the aggregation result is stored as a hash table, the upper limit of memory or cache used by the hash table is the target storage threshold determined in step 203, that is, the memory or cache occupied by the hash table cannot exceed the target storage threshold.
[0112] In some embodiments, the hash table can be stored in the processor L2 cache of the server node.
[0113] In some embodiments, before performing the bypass processing in step 202, or in the initial stage of receiving the data stream from the database table, a group pushdown can be performed first. The server node determines whether to proceed with the bypass processing based on the pre-aggregation results. In this specification, the group pushdown stage before the bypass processing can be referred to as the first pre-aggregation stage, and the group pushdown stage after the bypass processing can be referred to as the second pre-aggregation stage.
[0114] The first pre-aggregation stage is similar to the second pre-aggregation stage; both involve performing aggregation operations on the data in the received data stream according to the aggregation query request, and sending the aggregation results to the target node. The difference lies in that, in the first pre-aggregation stage, the storage area used for the aggregation results does not exceed the initial storage threshold.
[0115] The initial storage threshold is determined based on the available memory size of the one or more server nodes, or based on the available processor cache size of the one or more computing server nodes. In other words, the initial storage threshold is determined based on the storage conditions of the server nodes.
[0116] If the pre-aggregation result in the first pre-aggregation stage does not meet the second set conditions, that is, if the effect of pre-aggregation does not meet expectations, then a bypass operation is determined to determine the maximum memory or maximum cache that can be used for the pre-aggregation result in the second pre-aggregation stage through execution-time NDV estimation.
[0117] In some embodiments, it can be determined whether the pre-aggregation result meets the second preset condition in the following ways.
[0118] First, the data in each row of the received database table is aggregated, and the aggregation result is filled into a hash table. When the storage area used by the hash table reaches the initial storage threshold, the deduplication rate of the aggregation result is obtained. Specifically, this deduplication rate can be determined based on the NDV of the column to be aggregated and the total number of input rows. The deduplication rate is calculated using the following formula: Deduplication rate = 1 - (NDV / Total number of input rows). To further distinguish the data received in the first pre-aggregation stage from the data received in the second pre-aggregation stage and the bypass stage, the data received here is referred to as third data.
[0119] If the determined deduplication rate is less than the second threshold, it indicates a low aggregation rate and poor aggregation effect. In this case, after sending the aggregation result, the aggregation result stored in the storage area is cleared, and the aggregation result is regenerated based on the received third data. That is, if the deduplication rate of the already filled hash table is less than the second threshold, the hash table is cleared after the data in the hash table is delivered to the network, and a new hash table is reconstructed based on the data received subsequently.
[0120] If the number of times the aggregation result is continuously cleared reaches a set threshold, it is determined that the aggregation result does not meet the second set condition. That is, if the number of times a new hash table is continuously rebuilt reaches the set threshold, for example, 5 times, it can be considered that the data in the received database table is not suitable for grouping and pushdown. In this case, the hash table is cleared after the data in the hash table is delivered to the network, and the process enters the bypass phase.
[0121] If the determined deduplication rate is greater than or equal to the aforementioned second proportional threshold, the initial storage threshold is increased, and the received third data is aggregated based on the increased initial storage threshold. That is, if the deduplication rate of the already filled hash table is less than the second proportional threshold, the upper limit of the storage area used by the hash table is increased, and new data streams are processed based on the expanded storage area. For example, if the hash table is stored in the processor's L2 cache, it can be expanded and stored in the processor's L3 cache.
[0122] Figure 3 This is a flowchart illustrating another aggregation query method proposed in an exemplary embodiment. The method is applied to... Figure 1 The distributed database system shown.
[0123] Figure 3 The data stream in the middle is generated in the following way:
[0124] The client receives an aggregation query request. This aggregation query request can be, for example, the SQL aggregation query request "Select c1, SUM(c2) from t1 group by c1", where t1 is the database table to be queried; c1 and c2 are column data in database table t1. This query request is used to request the sum of c2 data in each group after grouping database table t1 based on the data in c1.
[0125] Upon receiving an aggregation query request for database table t1, the scheduling node probes the server nodes that store the table. Assuming that database table t1 is located on server node 1 and server node 2, the scheduling node distributes the aggregation query task to the processors of server node 1 and server node 2. Upon receiving the task, server nodes 1 and 2 retrieve the data from their locally stored memory for database table t1 and receive the real-time data stream generated by database table t1.
[0126] Figure 3 The flowchart shown illustrates the aggregation query process executed by the processor in server node 1 and the processor in server node 2, respectively.
[0127] After receiving the data stream from database table t1, the system first enters the first pre-aggregation stage.
[0128] Specifically, for the data received in database t1, the grouping and aggregation operation indicated by the SQL aggregation query request is performed, and the aggregation result is filled into the hash table. When the storage area used reaches the initially set threshold, the deduplication rate of the hash table is determined, and the relationship between the deduplication rate and the second proportional threshold is judged.
[0129] If the determined deduplication rate is greater than the second ratio threshold mentioned above, the initial threshold is increased, and the data streams received next are aggregated according to the increased initial threshold, and subsequent operations such as filling the hash table are performed.
[0130] If the determined aggregation is less than or equal to the second ratio threshold mentioned above, the hash table is cleared; if the number of times the hash table is cleared does not reach the set number threshold, the data received next is aggregated according to the current initial aggregation threshold, and subsequent operations such as filling the hash table are performed.
[0131] When the number of clearing attempts reaches the aforementioned threshold, the bypass phase begins.
[0132] During the bypass phase, the received data is not aggregated; instead, it is sent directly to the target node. When the amount of data processed via bypass reaches a preset total amount, the NDV (Discretionary Value) of the received data is calculated in real time. The ratio of the NDV to the total amount of received data is obtained periodically to determine the deduplication rate of the first data. If the deduplication rate does not reach a first threshold, bypass processing continues until the deduplication rate reaches the first threshold. If the NDV meets a first preset condition, a target storage threshold is determined based on the NDV, and direct data transmission to the target node stops, exiting the bypass phase.
[0133] After exiting the by-pass phase, it enters the second pre-aggregation phase.
[0134] Specifically, for the data in database t1 received in the second pre-aggregation stage, the grouping aggregation operation indicated by the above SQL aggregation query request is performed, the aggregation result is filled into the hash table, and the upper limit of the storage area used by the hash table is set to the target set threshold.
[0135] In the second pre-aggregation stage, when the storage area used reaches the target threshold, the deduplication rate of the hash table is still detected, and it is determined whether to update the upper limit of the storage area that the hash table can use according to the method proposed in the embodiments of this disclosure, until the acquisition of the entire database table t1 is completed.
[0136] In the above embodiment, by detecting the deduplication rate of the pre-aggregation processing of the server node in the group pushdown process, after determining that the current data is not suitable for group pushdown, the bypass logic is entered, and the data is directly delivered to the network and the execution-time NDV estimation is implemented to determine the maximum storage area that the hash table can use. The bypass processing saves redundant hash table probing, effectively reduces CPU overhead, improves SQL execution performance, and can handle skewed data distribution in distributed databases.
[0137] Figure 4 This is an exemplary embodiment of an aggregation query apparatus applied to one or more server nodes. The aggregation query apparatus includes:
[0138] The receiving unit 401 is used to receive the data stream of the database table targeted by the aggregation query request, wherein the database table is distributed on the multiple server nodes;
[0139] The bypass unit 402 is used to determine the number of non-duplicate values NDV of the column to be aggregated in the data stream and send the first data to the target node, wherein the target node is one of the plurality of server nodes;
[0140] The determining unit 403 is configured to, when the NDV reaches a first set condition, determine a target storage threshold based on the NDV and stop sending the first data;
[0141] The pre-aggregation unit 404 is used to pre-aggregate the first data in the data stream and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed a set storage threshold. The target node is one of the plurality of server nodes.
[0142] The update unit is used to perform the aggregation operation indicated by the aggregation query request on the second data in the data stream to be received, and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed the target storage threshold.
[0143] In some embodiments, the apparatus further includes a first determining unit, configured to:
[0144] The deduplication rate of the first data is obtained at a set period, and the deduplication rate is determined based on the ratio of the NDV to the total amount of the received first data.
[0145] If the deduplication rate is greater than the first proportional threshold, it is determined that the NDV meets the first set condition.
[0146] In some embodiments, the apparatus further includes a second pre-aggregation unit, configured to: perform an aggregation operation on the third data in the data stream as indicated by the aggregation query request before determining the number of non-duplicate values NDV in the column to be aggregated of the first data in the data stream, and send the aggregation result to the target node, wherein the storage area used by the aggregation result does not exceed an initial storage threshold;
[0147] If the aggregation result does not meet the second set condition, determine the NDV in the column to be aggregated of the first data in the data stream, and send the first data to the target node.
[0148] In some embodiments, when the second pre-aggregation unit performs the aggregation operation indicated by the aggregation query request on the third data in the data stream and sends the aggregation result to the target node, it is specifically used for:
[0149] If the storage area used by the aggregation result reaches the initial storage threshold, obtain the deduplication rate of the aggregation result;
[0150] If the deduplication rate is less than the second proportional threshold, after sending the aggregation result, the aggregation result stored in the storage area is cleared, and the aggregation result is regenerated based on the received third data.
[0151] In some embodiments, the apparatus further includes a second determining unit, configured to:
[0152] If the number of times the aggregation result is cleared consecutively reaches a set threshold, it is determined that the aggregation result does not meet the second set condition.
[0153] In some embodiments, the initial storage threshold is determined according to any one of the following:
[0154] The available memory size of the one or more server nodes;
[0155] The available processor cache size of the one or more computing server nodes.
[0156] In some embodiments, the apparatus further includes a lifting unit for:
[0157] If the deduplication rate is greater than or equal to the second proportional threshold, the initial storage threshold is increased, and the received third data is aggregated based on the increased initial storage threshold.
[0158] In some embodiments, the storage area includes a processor L2 cache.
[0159] According to a third aspect of one or more embodiments of this specification, a database system is provided, comprising:
[0160] The client is used to receive aggregate query requests;
[0161] The scheduling node is used to determine one or more server nodes that store the database table targeted by the aggregate query request, and to distribute the aggregate query task to the one or more server nodes.
[0162] One or more server nodes are used to execute the methods proposed in the above embodiments.
[0163] Figure 5 This is a schematic structural diagram of a device provided in an exemplary embodiment. Please refer to... Figure 5 At the hardware level, the device includes a processor 502, an internal bus 504, a network interface 506, memory 508, and non-volatile memory 510, and may also include other hardware required for tasks. One or more embodiments of this specification can be implemented in software, such as the processor 502 reading the corresponding computer program from the non-volatile memory 510 into memory 508 and then running it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.
[0164] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.
[0165] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0166] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.
[0167] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.
[0168] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.
[0169] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.
[0170] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms “a,” “described,” and “the” used in one or more embodiments of this specification and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.
[0171] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first information may also be referred to as second information without departing from the scope of one or more embodiments of this specification, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."
[0172] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.
Claims
1. An aggregation query method, applied to one or more server nodes, comprising: Receive the data stream of the database table targeted by the aggregate query request, wherein the database table is distributed across the multiple server nodes; Determine the number of non-duplicate values (NDV) of the column to be aggregated in the first data in the data stream, and send the first data to the target node, wherein the target node is one of the plurality of server nodes; If the NDV reaches the first set condition, a target storage threshold is determined based on the NDV, and the transmission of the first data is stopped; The aggregation operation indicated by the aggregation query request is performed on the second data in the received data stream, and the aggregation result is sent to the target node. The storage area used by the aggregation result does not exceed the target storage threshold. The first setting condition is determined as follows: the deduplication rate of the first data is obtained at a set period, and the deduplication rate is determined based on the ratio of the NDV to the total amount of the received first data. If the deduplication rate is greater than the first proportional threshold, it is determined that the NDV meets the first set condition.
2. The method according to claim 1, further comprising, before determining the number of unique values (NDV) in the column to be aggregated of the first data in the data stream: The aggregation operation indicated by the aggregation query request is performed on the third data in the data stream, and the aggregation result is sent to the target node. The storage area used by the aggregation result does not exceed the initial storage threshold. If the aggregation result does not meet the second set condition, determine the NDV in the column to be aggregated of the first data in the data stream, and send the first data to the target node.
3. The method according to claim 2, wherein performing the aggregation operation indicated by the aggregation query request on the third data in the data stream and sending the aggregation result to the target node includes: If the storage area used by the aggregation result reaches the initial storage threshold, obtain the deduplication rate of the aggregation result; If the deduplication rate is less than the second proportional threshold, after sending the aggregation result, the aggregation result stored in the storage area is cleared, and the aggregation result is regenerated based on the received third data.
4. The method according to claim 3, further comprising: If the number of times the aggregation result is cleared consecutively reaches a set threshold, it is determined that the aggregation result does not meet the second set condition.
5. The method of claim 2, wherein the initial storage threshold is determined according to any one of the following: The available memory size of the one or more server nodes; The available processor cache size of the one or more server nodes.
6. The method according to claim 3, further comprising: If the deduplication rate is greater than or equal to the second proportional threshold, the initial storage threshold is increased, and the received third data is aggregated based on the increased initial storage threshold.
7. The method according to claim 1, wherein the storage area includes a processor L2 cache.
8. The method according to claim 2, wherein the aggregation query request includes an aggregation function and a grouping clause, and aggregates the target data in the data stream, including: The target data is grouped according to the grouping column indicated by the grouping clause; The aggregation operation is performed on the target data of each group according to the aggregation function to obtain the aggregation result. The target data includes second data and third data.
9. The method according to claim 8, wherein the grouping clause includes group by, and the grouping of the target data on the grouping column indicated by the grouping clause includes: Obtain the hash value of the grouping column indicated by group by, and group the target data according to the hash value; The method further includes: The aggregation result is stored as a hash table, which includes the hash value corresponding to each group.
10. An aggregation query apparatus, applied to one or more server nodes, comprising: The receiving unit is used to receive the data stream of the database table targeted by the aggregation query request, wherein the database table is distributed on the multiple server nodes; A bypass unit is used to determine the number of non-duplicate values (NDV) of the column to be aggregated in the data stream and send the first data to the target node, wherein the target node is one of the plurality of server nodes; The determining unit is configured to, when the NDV reaches a first preset condition, determine a target storage threshold based on the NDV and stop sending the first data; An update unit is used to perform the aggregation operation indicated by the aggregation query request on the second data in the data stream to be received, and send the aggregation result to the target node. The storage area used by the aggregation result does not exceed the target storage threshold. The first setting condition is determined as follows: the deduplication rate of the first data is obtained at a set period, and the deduplication rate is determined based on the ratio of the NDV to the total amount of the received first data. If the deduplication rate is greater than the first proportional threshold, it is determined that the NDV meets the first set condition.
11. A database system, comprising: The client is used to receive aggregate query requests; The scheduling node is used to determine one or more server nodes that store the database table targeted by the aggregate query request, and to distribute the aggregate query task to the one or more server nodes. One or more server nodes are configured to perform the method as described in any one of claims 1-9.
12. An electronic device, comprising: processor; Memory used to store processor-executable instructions; The processor implements the method as described in any one of claims 1-9 by executing the executable instructions.
13. A computer-readable storage medium having stored thereon computer instructions that, when executed by a processor, implement the steps of the method as claimed in any one of claims 1-9.