SQL query method and device, electronic device and storage medium

Abstract

The present specification provides an SQL query method and device, an electronic device and a storage medium, the method comprising: if the SQL statement to be processed is suitable for partitioned execution, determining the partition characteristics of the field on which the SQL statement is to be executed for partitioning; if the partition characteristics of the field on which the SQL statement is to be executed for partitioning are weak partitions, controlling each partitioning node to execute the SQL statement for data rows that meet the partitioning rules in the partitioning node, and sending the partitioning execution results and the data rows that do not meet the partitioning rules in the partitioning node to a coordination node, wherein the weak partitioning is used to characterize that some data rows in the field meet the partitioning rules; controlling the coordination node to execute the SQL statement for the received data rows that do not meet the partitioning rules, and generating a query result based on the locally obtained execution results and the received partitioning execution results.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of database technology, and in particular, to a SQL query method and device, an electronic device, and a storage medium. Background Art

[0002] In database systems, using partitioning features to speed up queries is a common method. Typical scenarios include partition-wise-join, partition-wise-group by, partition-wise-distinct, etc. This method can use the partitioning features to avoid the data repartitioning step and reduce the network transmission cost of distributed databases to execute query tasks, thereby improving the query performance of distributed databases.

[0003] In the related art, the query optimization mechanism using the partitioning characteristics can only be applied to a small amount of data, and its application scope is too narrow, resulting in limited improvement in database query performance. Summary of the invention

[0004] In view of this, one or more embodiments of the present specification provide a SQL query method and device, an electronic device, and a storage medium.

[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, a SQL query method is proposed, the method comprising:

[0007] If the SQL statement to be processed is suitable for partition execution, determining the partition characteristics of the field for which the SQL statement is to be executed in partition;

[0008] If the partition characteristic of the field for which the SQL statement is partitioned is weak partitioning, each partitioning node is controlled to execute the SQL statement for the data rows that meet the partitioning rule in the partitioning node, and the partitioning execution result and the data rows that do not meet the partitioning rule in the partitioning node are sent to the coordination node, wherein the weak partitioning is used to indicate that some data rows in the field meet the partitioning rule;

[0009] The coordination node is controlled to execute the SQL statement for the received data rows that do not conform to the partitioning rule, and a query result is generated based on the locally obtained execution result and the received partition execution result.

[0010] In a possible embodiment of this specification, the method further includes:

[0011] The coordination node is controlled to determine, based on the partitioning rule, a partitioning node corresponding to each received data row that does not conform to the partitioning rule, and each data row that does not conform to the partitioning rule is sent to a corresponding partitioning node.

[0012] In a possible embodiment of the present specification, after the control partitioning node sends the data row that does not conform to the partitioning rule to the coordination node, the method further includes:

[0013] The partitioning node is controlled to delete data rows that do not comply with the partitioning rule.

[0014] In a possible embodiment of the present specification, the weak partition is used to represent that the proportion of data rows in the field that meet the partition rule in all data rows in the field is not less than a proportion threshold.

[0015] In a possible embodiment of this specification, the method further includes:

[0016] If the partition characteristic of the field for which the SQL statement is partitioned is strong partitioning, each partitioning node is controlled to execute the SQL statement for the data rows in the partitioning node, and the partitioning execution result is sent to the coordination node, wherein the strong partitioning is used to indicate that all data rows in the field comply with the partitioning rule;

[0017] The coordination node is controlled to generate a query result according to the received partition execution result.

[0018] In a possible embodiment of this specification, the method further includes:

[0019] If the partition characteristic of the field for which the SQL statement is partitioned is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to represent that the proportion of the data rows in the field that meet the partition rule in all the data rows in the field is less than a proportion threshold;

[0020] The coordination node is controlled to execute the SQL statement for the received data row to generate a query result.

[0021] In a possible embodiment of this specification, the method further includes:

[0022] The partition characteristic of each field in the query result is recorded.

[0023] In a possible embodiment of this specification, the method further includes:

[0024] If the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

[0025] According to a second aspect of one or more embodiments of this specification, a SQL query device is provided, the device comprising:

[0026] A characteristic determination module, configured to determine the partition characteristic of the field for which the SQL statement is to be executed in partitions if the SQL statement to be processed is suitable for partition execution;

[0027] A weak partition execution module, configured to control each partition node to execute the SQL statement for the data rows that meet the partition rule in the partition node if the partition characteristic of the field for which the SQL statement is partitioned is weak partition, and send the partition execution result and the data rows that do not meet the partition rule in the partition node to the coordination node, wherein the weak partition is used to indicate that some data rows in the field meet the partition rule;

[0028] The result fusion module is used to control the coordination node to execute the SQL statement for the received data rows that do not meet the partitioning rules, and generate query results based on the locally obtained execution results and the received partition execution results.

[0029] In a possible embodiment of the present specification, the device further includes a re-partitioning module, configured to:

[0030] The coordination node is controlled to determine, based on the partitioning rule, a partitioning node corresponding to each received data row that does not conform to the partitioning rule, and each data row that does not conform to the partitioning rule is sent to a corresponding partitioning node.

[0031] In a possible embodiment of the present specification, the device further includes a deletion module, configured to:

[0032] After controlling the partitioning node to send the data row that does not conform to the partitioning rule to the coordination node, controlling the partitioning node to delete the data row that does not conform to the partitioning rule.

[0033] In a possible embodiment of the present specification, the weak partition is used to represent that the proportion of data rows in the field that meet the partition rule in all data rows in the field is not less than a proportion threshold.

[0034] In a possible embodiment of the present specification, the device further includes a strong partition execution module, which is used to:

[0035] If the partition characteristic of the field for which the SQL statement is partitioned is strong partitioning, each partitioning node is controlled to execute the SQL statement for the data rows in the partitioning node, and the partitioning execution result is sent to the coordination node, wherein the strong partitioning is used to indicate that all data rows in the field comply with the partitioning rule;

[0036] The coordination node is controlled to generate a query result according to the received partition execution result.

[0037] In a possible embodiment of the present specification, the device further includes an empty partition execution module, which is used to:

[0038] If the partition characteristic of the field for which the SQL statement is partitioned is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to represent that the proportion of the data rows in the field that meet the partition rule in all the data rows in the field is less than a proportion threshold;

[0039] The coordination node is controlled to execute the SQL statement for the received data row to generate a query result.

[0040] In a possible embodiment of the present specification, the device further includes a characteristic recording module, which is used to:

[0041] The partition characteristic of each field in the query result is recorded.

[0042] In a possible embodiment of the present specification, the device further includes a determination module, configured to:

[0043] If the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

[0044] According to a third aspect of one or more embodiments of this specification, a computer program product is provided, comprising a computer program / instruction, which implements the steps of the method described in the first aspect when executed by a processor.

[0045] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, including:

[0046] processor;

[0047] a memory for storing processor-executable instructions;

[0048] The processor implements the method as described in the first aspect by running the executable instructions.

[0049] According to a fifth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method described in the first aspect are implemented.

[0050] The technical solutions provided by the embodiments of this specification may have the following beneficial effects:

[0051] The SQL query method provided in the embodiment of the present specification, if the SQL statement to be processed is suitable for partition execution, then determine the partition characteristics of the field for which the SQL statement is partitioned; if the partition characteristics of the field for which the SQL statement is partitioned is weak partition, then control each partition node to execute the SQL statement for the data rows that meet the partition rules in the partition node, and send the partition execution results and the data rows that do not meet the partition rules in the partition node to the coordination node; control the coordination node to execute the SQL statement for the received data rows that do not meet the partition rules, and generate query results based on the locally obtained execution results and the received partition execution results. Since the weak partition is used to characterize that some data rows in the field meet the partition rules, when the method performs partition execution for the field with the partition characteristic of weak partition, the data rows that meet the partition rules can be partitioned in the partition node, and the data rows that do not meet the partition rules can be gathered at the coordination node for execution, thereby partially realizing partition execution, avoiding the performance impact caused by the repartitioning step to a certain extent, further reducing the network transmission cost of distributed databases to execute query tasks, expanding the scope of application of the query optimization mechanism using partition characteristics, and greatly improving the query performance of distributed databases. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 is a schematic diagram of a distributed database provided by an exemplary embodiment.

[0053] Figure 2 It is a flowchart of a SQL query method provided by an exemplary embodiment.

[0054] Figure 3 It is a schematic diagram of the interaction between partition nodes in a SQL query method provided by an exemplary embodiment.

[0055] Figure 4 It is a structural schematic diagram of a device provided by an exemplary embodiment.

[0056] Figure 5 It is a block diagram of a SQL query device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0057] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of this specification. Instead, they are merely examples of devices and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0058] It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or less than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0059] In the related art, the query optimization mechanism using the partitioning characteristics is only applicable to the fields with the partitioning characteristics of strong partitioning, but cannot be applied to the fields with the partitioning characteristics of weak partitioning or other situations, resulting in missing the optimization space in the query optimization process.

[0060] Based on the above technical problems, at least one embodiment of this specification provides a SQL query method, which can be applied to a distributed database, such as the attached Figure 1 In the distributed database system shown, the query optimization mechanism using partition characteristics provided by the method can be applied to fields with strong partition characteristics and weak partition characteristics, thereby further reducing the network transmission cost of the distributed database to execute query tasks and significantly improving the query performance of the distributed database.

[0061] The distributed database has multiple partition nodes; the node receiving the SQL statement serves as a coordination node, which is responsible for interacting with other working nodes and clients, etc. For example, the method may be executed by a coordination node.

[0062] Please refer to the attached Figure 2 , which exemplarily shows a flow chart of the SQL query method, including steps S201 to S202.

[0063] In step S201, if the SQL statement to be processed is suitable for partition execution, the partition characteristics of the field for which the SQL statement is to be executed in partition are determined.

[0064] Among them, SQL statements suitable for partition execution refer to SQL statements that can be executed independently for data rows in each partition node, and query results can be obtained by only fusing the execution results of each partition node. In other words, SQL statements suitable for partition execution do not need to repartition data rows in different partition nodes when executing.

[0065] If the SQL statement can be executed independently for a data row corresponding to a value or a value range of a certain field, and the query result of the SQL statement can be obtained by only fusing the independent execution results of different values ​​or value ranges in the field, then the field is a field for partition execution of the SQL statement suitable for partition execution. It should be understood that the field for partition execution of the SQL statement mentioned in this step refers to one or some fields in the data table targeted by the SQL statement.

[0066] Optionally, if the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

[0067] Exemplarily, if a SQL statement contains a Left join clause, it can be determined that the SQL statement is suitable for partition execution, and the fields targeted by the Left join clause are used as the fields targeted by the SQL statement for partition execution. For example, the following SQL statement Q1 is a SQL statement suitable for partition execution, and the fields targeted by the SQL statement for partition execution are t1.a and t2.a.

[0068] create table t1(a int, b int, c int) partition by hash(a) partitions4;

[0069] create table t2(a int, b int, c int) partition by hash(a) partitions4;

[0070] Q1: explain select t1.a, t1.b, t2.a, t2.b from t1 left join t2 on t1.a= t2.a

[0071] For example, if the SQL statement contains a Group by clause, it can be determined that the SQL statement is suitable for partition execution, and the field targeted by the Group by clause is used as the field targeted by the SQL statement for partition execution. For example, the following SQL statement Q2 is a SQL statement suitable for partition execution, and the field targeted by the SQL statement for partition execution is t2.a.

[0072] create table t1(a int, b int, c int) partition by hash(a) partitions4;

[0073] create table t2(a int, b int, c int) partition by hash(a) partitions4;

[0074] Q2: explain select t2.a, sum(t1.b) from t1 left join t2 on t1.a = t2.agroup by t2.a

[0075] As another example, if the SQL statement includes a Distinct clause, it can be determined that the SQL statement is suitable for partition execution, and the field targeted by the Distinct clause is used as the field targeted by the SQL statement for partition execution.

[0076] As another example, if a Join clause is included in an SQL statement, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the Join clause is used as the field targeted by the SQL statement for partitioned execution.

[0077] As another example, if the SQL statement includes a window function clause, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the window function clause is used as the field targeted by the SQL statement for partitioned execution.

[0078] Among them, the partition characteristics include strong partition, weak partition and empty partition. The strong partition is used to indicate that all data rows in the field meet the partition rule. The weak partition is used to indicate that some data rows in the field meet the partition rule; for example, the weak partition is used to indicate that the proportion of data rows in the field that meet the partition rule in all data rows in the field is not less than the proportion threshold. The empty partition is used to indicate that the proportion of data rows in the field that meet the partition rule in all data rows in the field is less than the proportion threshold.

[0079] The partitioning rule refers to the rule for determining the partition node corresponding to each data row in a distributed database. For example, the hash value of the key value is used as the partition parameter, that is, each partition node corresponds to one or more hash values. If the hash value of the key value of a data row corresponds to a partition node, the partition node is determined as the partition node corresponding to the data row, and the data row is stored in the corresponding partition node.

[0080] For example, in the following SQL statement, the hash value of the key value of the data table is used as the partition parameter. The key value of table t1 is its a field, and the key value of table t2 is also its a field.

[0081] create table t1(a int, b int, c int) partition by hash(a) partitions4;

[0082] create table t2(a int, b int, c int) partition by hash(a) partitions4.

[0083] In step S202, if the partition characteristic of the field on which the SQL statement is executed for partitioning is weak partitioning, each partition node is controlled to execute the SQL statement for the data rows in the partition node that meet the partitioning rules, and the partitioning execution result and the data rows in the partition node that do not meet the partitioning rules are sent to the coordination node, wherein the weak partition is used to characterize that some data rows in the field meet the partitioning rules.

[0084] It should be understood that each partition node mentioned in this step refers to the partition node where each partition of the data table targeted by the SQL statement is located.

[0085] Take Q2 in the above example: explain select t2.a, sum(t1.b) from t1 left join t2on t1.a = t2.a group by t2.a as an example. The partition characteristic of t2.a is weak partition. Before t1 and t2 are outer joined, the partition characteristics of t1.a and t2.a are both strong partitions. After t1 and t2 are outer joined, t1.a maintains strong partition. The partition characteristic of t2.a is converted to weak partition due to the null values ​​distributed in different partition nodes. That is, all data rows in t2.a except null values ​​meet the partition rules, and only the data rows with null values ​​do not meet the partition rules.

[0086] For example, the data tables t1 and t2 in the above example are as follows:

[0087] Table t1

[0088]

[0089] Table t2

[0090]

[0091] In the partitioning rule, the hash values ​​of key values ​​1, 2, and 3 correspond to partition node 1. Therefore, the data rows with a values ​​of 1, 2, and 3 in the above table t1 are in partition node 1, and the data rows with a values ​​of 1 and 2 in the above table t2 are in partition node 1; in the partitioning rule, the hash values ​​of key values ​​4, 5, and 6 correspond to partition node 2. Therefore, the data rows with a value of 4 in the above table t1 are in partition node 2, and the data rows with a value of 5 in the above table t2 are in partition node 2.

[0092] When tables t1 and t2 are outer joined (t1 left join t2 on t1.a = t2.a), the partition characteristics of t1.a and t2.a are both strong partitions. Therefore, the outer join can be performed in partitions, that is, the outer join of the t1 table partitions with t1.a values ​​of 1, 2, and 3 and the t2 table partitions with t2.a values ​​of 1 and 2 is performed in partition node 1, and the outer join of the t1 table partitions with t1.a values ​​of 3 and 4 and the t2 table partitions with t2.a value of 5 is performed in partition node 2.

[0093] The results of the outer join of tables t1 and t2 are as follows:

[0094] Table t1 left join t2 on t1.a = t2.a

[0095]

[0096] It can be seen that the t1.a field still meets the partitioning rules, that is, the data rows where 1, 2, and 3 are located are in partition node 1, and the data row where 4 is located is in partition node 2. Therefore, the partitioning characteristic of the t1.a field is strong partitioning; the data rows where 1 and 2 are located in the t2.a field also meet the partitioning rules. In partition node 1, the data row where null is located in the t2.a field does not meet the partitioning rules. The two data rows are respectively in partition node 1 and partition node 2, and the hash value of null does not correspond to partition nodes 1 or 2.

[0097] Therefore, Q2: explain select t2.a, sum(t1.b) from t1 left join t2 on t1.a =t2.a group by t2.a can be executed in the way provided in this step. Figure 3, partition node 1 executes Q2 for the data rows whose t2.a field is 1 and 2, and sends the execution results and the data rows whose t2.a field is null (that is, the data rows whose t1.a field is 3) to the coordinating node. Partition node 2 sends the data rows whose t2.a field is null (that is, the data rows whose t1.a field is 4) to the coordinating node. The coordinating node executes Q2 for the two data rows whose t2.a field is null (that is, the two data rows whose t1.a field is 3 and 4), and generates the query result of Q2 based on the execution result of local Q2 and the execution result sent by partition node 1.

[0098] The execution results obtained by partition node 1 are as follows:

[0099]

[0100] In step S203, the coordinating node is controlled to execute the SQL statement for the received data rows that do not comply with the partitioning rule, and a query result is generated based on the locally obtained execution result and the received partition execution result.

[0101] The data rows that do not conform to the partitioning rules are gathered at the coordination node, and SQL statements can be executed for these data rows in the coordination node. This is equivalent to forcibly gathering these data rows that do not conform to the partitioning rules and are scattered in different partitioning nodes and then executing SQL statements uniformly, thereby improving efficiency; although compared with the case where the partitioning characteristics are strong partitions, it is necessary to increase the inter-node transmission of these data rows that do not conform to the partitioning rules, but it is far less than the communication volume of the re-partitioning operation step.

[0102] Continuing with the example of Q2 in the above example: explain select t2.a, sum(t1.b) from t1 left joint2 on t1.a = t2.a group by t2.a, please refer to the attached Figure 3 The coordinating node executes Q2 on the two data rows whose t2.a field is null (that is, the two data rows whose t1.a field is 3 and 4), and generates the query result of Q2 based on the execution result obtained by the local Q2 and the execution result sent by the partition node 1.

[0103] The execution results of Q2 executed locally on the coordinating node are as follows:

[0104]

[0105] The query results for Q2 are as follows:

[0106]

[0107] Exemplarily, after controlling the partitioning node to send the data rows that do not comply with the partitioning rules to the coordination node, the method can also control the coordination node to determine the partitioning node corresponding to each received data row that does not comply with the partitioning rules based on the partitioning rules, and send each data row that does not comply with the partitioning rules to the corresponding partitioning node.

[0108] For example, let’s continue with Q2 in the above example: explain select t2.a, sum(t1.b) from t1 leftjoin t2 on t1.a = t2.a group by t2.a. Figure 3 , the coordinating node determines that the hash value of null corresponds to partition node 3, and then the two data rows whose t2.a field is null (that is, the two data rows whose t1.a field is 3 and 4) can be sent to partition node 3 for storage.

[0109] Furthermore, the method may also control the partitioning node to delete the data row that does not conform to the partitioning rule after controlling the partitioning node to send the data row that does not conform to the partitioning rule to the coordination node.

[0110] For example, continue with Q2 in the above example: explain select t2.a, sum(t1.b) from t1 leftjoin t2 on t1.a = t2.a group by t2.a. Control partition node 1 sends the data row with the t2.a field being null (that is, the data row with the t1.a field being 3) to the coordinating node and then deletes the data row; control partition node 2 sends the data row with the t2.a field being null (that is, the data row with the t1.a field being 4) to the coordinating node and then deletes the data row.

[0111] In this example, the coordinating node repartitions the data row that does not conform to the partitioning rules after receiving it, so that the method can not only perform partitioning on the field whose partitioning characteristic is weak partitioning, but also synchronously complete the repartitioning of the data row that does not conform to the partitioning rules. Even if the partitioning characteristic of the field is updated from weak partitioning to strong partitioning, the communication between nodes can be avoided when the SQL statement is subsequently executed for the field, thereby improving the query performance.

[0112] In another exemplary embodiment, after generating the query result, the method may also record the partition characteristics of each field in the query result, so that when the SQL statement is subsequently executed for the query result, the partition characteristics of the field for which the SQL statement is partitioned can be directly confirmed, thereby further improving the execution efficiency and query performance.

[0113] The SQL query method provided in the embodiment of the present specification, if the SQL statement to be processed is suitable for partition execution, then determine the partition characteristics of the field for which the SQL statement is partitioned; if the partition characteristics of the field for which the SQL statement is partitioned is weak partition, then control each partition node to execute the SQL statement for the data rows that meet the partition rules in the partition node, and send the partition execution results and the data rows that do not meet the partition rules in the partition node to the coordination node; control the coordination node to execute the SQL statement for the received data rows that do not meet the partition rules, and generate query results based on the locally obtained execution results and the received partition execution results. Since the weak partition is used to characterize that some data rows in the field meet the partition rules, when the method performs partition execution for the field with the partition characteristic of weak partition, the data rows that meet the partition rules can be partitioned in the partition node, and the data rows that do not meet the partition rules can be gathered at the coordination node for execution, thereby partially realizing partition execution, avoiding the performance impact caused by the repartitioning step to a certain extent, further reducing the network transmission cost of distributed databases to execute query tasks, expanding the scope of application of the query optimization mechanism using partition characteristics, and greatly improving the query performance of distributed databases.

[0114] In some embodiments of the present disclosure, if the partition characteristic of the field on which the SQL statement is executed for partitioning is strong partitioning, each partitioning node is controlled to execute the SQL statement for the data rows in the partitioning node, and the partitioning execution result is sent to the coordination node, wherein the strong partitioning is used to characterize that all data rows in the field comply with the partitioning rule; and the coordination node is controlled to generate a query result based on the received partitioning execution result.

[0115] In some embodiments of the present disclosure, if the partition characteristic of the field on which the SQL statement is executed for partitioning is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to characterize that the proportion of data rows in the field that meet the partitioning rules in all data rows in the field is less than a proportion threshold; and the coordination node is controlled to execute the SQL statement for the received data rows to generate a query result.

[0116] Of course, it should be understood that the above two embodiments can also record the partition characteristics of each field in the query result, so that when the SQL statement is subsequently executed for the query result, the partition characteristics of the field for which the SQL statement is partitioned can be directly confirmed, thereby further improving the execution efficiency and query performance.

[0117] For example, in Q2 in the above example: explain select t2.a, sum(t1.b) from t1 left join t2on t1.a = t2.a group by t2.a, the partition characteristics of t1.a and t2.a are recorded in the result of the outer join of tables t1 and t2 (t1 left join t2 on t1.a = t2.a), which makes the subsequent execution of Q2 very convenient and efficient.

[0118] Figure 4 is a schematic structural diagram of a device provided by an exemplary embodiment. Figure 4 At the hardware level, the device includes a processor 402, an internal bus 404, a network interface 406, a memory 408, and a non-volatile memory 410, and may also include hardware required for other tasks. One or more embodiments of this specification may be implemented based on software, such as the processor 402 reading the corresponding computer program from the non-volatile memory 410 into the memory 408 and then running it. Of course, in addition to the software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0119] Please refer to Figure 5 , the SQL query device can be applied to Figure 4 The SQL query device may include:

[0120] The characteristic determination module 501 is used to determine the partition characteristic of the field for which the SQL statement is to be executed in partitions if the SQL statement to be processed is suitable for partition execution;

[0121] A weak partition execution module 502 is used to control each partition node to execute the SQL statement for the data rows that meet the partition rule in the partition node if the partition characteristic of the field for which the SQL statement is partitioned is weak partition, and send the partition execution result and the data rows that do not meet the partition rule in the partition node to the coordination node, wherein the weak partition is used to indicate that some data rows in the field meet the partition rule;

[0122] The result fusion module 503 is used to control the coordination node to execute the SQL statement for the received data rows that do not comply with the partitioning rule, and generate a query result based on the locally obtained execution result and the received partition execution result.

[0123] In a possible embodiment of the present specification, the device further includes a re-partitioning module, configured to:

[0124] The coordination node is controlled to determine, based on the partitioning rule, a partitioning node corresponding to each received data row that does not conform to the partitioning rule, and each data row that does not conform to the partitioning rule is sent to a corresponding partitioning node.

[0125] In a possible embodiment of the present specification, the device further includes a deletion module, configured to:

[0126] After controlling the partitioning node to send the data row that does not conform to the partitioning rule to the coordination node, controlling the partitioning node to delete the data row that does not conform to the partitioning rule.

[0127] In a possible embodiment of the present specification, the weak partition is used to represent that the proportion of data rows in the field that meet the partition rule in all data rows in the field is not less than a proportion threshold.

[0128] In a possible embodiment of the present specification, the device further includes a strong partition execution module, which is used to:

[0129] If the partition characteristic of the field for which the SQL statement is partitioned is strong partitioning, each partitioning node is controlled to execute the SQL statement for the data rows in the partitioning node, and the partitioning execution result is sent to the coordination node, wherein the strong partitioning is used to indicate that all data rows in the field comply with the partitioning rule;

[0130] The coordination node is controlled to generate a query result according to the received partition execution result.

[0131] In a possible embodiment of the present specification, the device further includes an empty partition execution module, which is used to:

[0132] If the partition characteristic of the field for which the SQL statement is partitioned is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to represent that the proportion of the data rows in the field that meet the partition rule in all the data rows in the field is less than a proportion threshold;

[0133] The coordination node is controlled to execute the SQL statement for the received data row to generate a query result.

[0134] In a possible embodiment of the present specification, the device further includes a characteristic recording module, which is used to:

[0135] The partition characteristic of each field in the query result is recorded.

[0136] In a possible embodiment of the present specification, the device further includes a determination module, configured to:

[0137] If the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

[0138] One or more embodiments of the present specification also propose a computer program product, including a computer program / instruction, which implements the steps of the method provided in the first aspect when the computer program / instruction is executed by a processor.

[0139] One or more embodiments of the present specification also propose a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method described in the first aspect.

[0140] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email transceiver, a game console, a tablet computer, a wearable device or a combination of any of these devices.

[0141] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0142] Memory may include non-permanent storage in a computer-readable medium, in the form of random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0143] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. Information can be computer-readable instructions, data structures, program modules 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 technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0144] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0145] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0146] The terms used in one or more embodiments of this specification are only for the purpose of describing specific embodiments, and are not intended to limit one or more embodiments of this specification. The singular forms of "a", "said" and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0147] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0148] 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, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0149] The above description is merely a preferred embodiment of one or more embodiments of the present specification and is not intended to limit one or more embodiments of the present specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present specification shall be included in the scope of protection of one or more embodiments of the present specification.

Claims

1. A SQL query method, the method comprising: If the SQL statement to be processed is suitable for partition execution, determining the partition characteristics of the field for which the SQL statement is to be executed in partition, wherein the partition characteristics include strong partition and weak partition, and the strong partition is used to indicate that all data rows in the field comply with the partition rule; If the partition characteristic of the field for which the SQL statement is partitioned is weak partitioning, each partitioning node is controlled to execute the SQL statement for the data rows that meet the partitioning rules in the partitioning node, and the partitioning execution result and the data rows that do not meet the partitioning rules in the partitioning node are sent to the coordination node, wherein the weak partitioning is used to represent that the proportion of the data rows that meet the partitioning rules in the field among all the data rows in the field is not less than the proportion threshold; Control the coordinating node to execute the SQL statement for the received data rows that do not conform to the partitioning rule, and generate a query result based on the locally obtained execution result and the received partition execution result; The method further comprises: If the partition characteristic of the field for which the SQL statement is partitioned is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to represent that the proportion of the data rows in the field that meet the partition rule in all the data rows in the field is less than a proportion threshold; The coordination node is controlled to execute the SQL statement for the received data row to generate a query result.

2. The SQL query method according to claim 1, further comprising: The coordination node is controlled to determine, based on the partitioning rule, a partitioning node corresponding to each received data row that does not conform to the partitioning rule, and each data row that does not conform to the partitioning rule is sent to a corresponding partitioning node.

3. The SQL query method according to claim 2, after the control partition node sends the data rows that do not comply with the partition rule to the coordination node, the method further comprises: The partitioning node is controlled to delete data rows that do not comply with the partitioning rule.

4. The SQL query method according to claim 1, further comprising: If the partition characteristic of the field for which the SQL statement is partitioned is strong partitioning, each partition node is controlled to execute the SQL statement for the data row in the partition node, and the partition execution result is sent to the coordination node; The coordination node is controlled to generate a query result according to the received partition execution result.

5. The SQL query method according to claim 1 or 4, further comprising: The partition characteristic of each field in the query result is recorded.

6. The SQL query method according to claim 1, further comprising: If the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

7. A SQL query device, comprising: A characteristic determination module, configured to determine the partition characteristic of the field for which the SQL statement is to be executed in partition if the SQL statement to be processed is suitable for partition execution, wherein the partition characteristic includes strong partition and weak partition, and the strong partition is used to indicate that all data rows in the field comply with the partition rule; A weak partition execution module, configured to control each partition node to execute the SQL statement for the data rows that meet the partition rule in the partition node if the partition characteristic of the field for which the SQL statement is partitioned is weak partition, and send the partition execution result and the data rows that do not meet the partition rule in the partition node to the coordination node, wherein the weak partition is used to indicate that the proportion of the data rows that meet the partition rule in the field among all the data rows in the field is not less than a proportion threshold; A result fusion module, used to control the coordination node to execute the SQL statement for the received data rows that do not conform to the partitioning rule, and generate a query result based on the locally obtained execution result and the received partition execution result; The device also includes an empty partition execution module, which is used to: If the partition characteristic of the field for which the SQL statement is partitioned is an empty partition, each partition node is controlled to send the data rows in the partition node to the coordination node, wherein the empty partition is used to represent that the proportion of the data rows in the field that meet the partition rule in all the data rows in the field is less than a proportion threshold; The coordination node is controlled to execute the SQL statement for the received data row to generate a query result.

8. The SQL query device according to claim 7, further comprising a repartitioning module, configured to: The coordination node is controlled to determine, based on the partitioning rule, a partitioning node corresponding to each received data row that does not conform to the partitioning rule, and each data row that does not conform to the partitioning rule is sent to a corresponding partitioning node.

9. The SQL query device according to claim 7, further comprising a deletion module, configured to: After controlling the partitioning node to send the data row that does not conform to the partitioning rule to the coordination node, controlling the partitioning node to delete the data row that does not conform to the partitioning rule.

10. The SQL query device according to claim 7, further comprising a characteristic recording module, configured to: The partition characteristic of each field in the query result is recorded.

11. The SQL query device according to claim 7, further comprising a judgment module, configured to: If the SQL statement contains at least one of a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, it is determined that the SQL statement is suitable for partition execution.

12. A computer program product, comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 6.

13. An electronic device, comprising: processor; a memory for storing processor-executable instructions; The processor implements the method according to any one of claims 1 to 6 by running the executable instructions.

14. A computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method according to any one of claims 1 to 6.

Images