CN109885585B

CN109885585B - Distributed database system and method supporting stored procedures, triggers and views

Info

Publication number: CN109885585B
Application number: CN201910119603.6A
Authority: CN
Inventors: 徐飞; 代正卿
Original assignee: Xinghuan Information Technology (shanghai) Co Ltd
Current assignee: Transwarp Technology Shanghai Co Ltd
Priority date: 2019-02-18
Filing date: 2019-02-18
Publication date: 2020-01-10
Anticipated expiration: 2039-02-18
Also published as: CN109885585A

Abstract

The embodiment of the invention discloses a distributed database system and a method for supporting a storage process, a trigger and a view, wherein the distributed database system comprises a public gateway, a distributed database and at least one computing node, and each computing node is provided with a relational database for supporting a database linking function; the method comprises the following steps: the public gateway carries out syntactic semantic analysis on a Structured Query Language (SQL) statement input by a user to obtain the type of the SQL statement and whether the SQL statement has completely distributed support attribute information; the public gateway sends the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has completely distributed support attribute information; the distributed database and/or compute nodes execute the received SQL statements. The embodiment supports complex functions such as ANSI SQL, triggers, stored procedures, views and the like.

Description

Distributed database system and method supporting stored procedures, triggers and views

Technical Field

The embodiment of the invention relates to a database technology, in particular to a distributed database system and a method supporting storage processes, triggers and views.

Background

In the scenario of high concurrency and large amount of data storage and processing, the performance of a stand-alone relational database is often difficult to meet the requirements of actual business. Solutions on mainframes, represented by Oracle, IBM, tend to be very expensive and require high maintenance costs. Therefore, distributed databases are growing and are becoming the trend in the industry. A distributed database system typically includes a plurality of smaller computers, each of which may be individually located in a single location, each of which may have a complete copy, or a partial copy, of the database management system and its own local database, with many computers located at different locations interconnected via a network to form a complete, globally logically centralized, physically distributed, large database.

However, the existing distributed database only provides partial support of ANSI SQL, but does not support complex functions such as complex SQL, triggers, and storage procedures. If the distributed database needs to completely support ANSI SQL, a large amount of compiler codes need to be written, so that a large amount of manpower and material resources are needed. Therefore, under the distributed database architecture, the database engine, the trigger and the storage process of the complete support degree are difficult to realize.

Disclosure of Invention

The embodiment of the invention provides a distributed database system and a method supporting storage of processes, triggers and views, so as to support complex functions of ANSI SQL, triggers, storage of processes, views and the like.

In a first aspect, an embodiment of the present invention provides a statement execution method supporting a storage process, a trigger, and a view, where the method is applied to a distributed database system, where the distributed database system includes a public gateway, a distributed database, and at least one computing node, and each computing node is loaded with a relational database supporting a database link function; the method comprises the following steps:

the public gateway carries out syntactic semantic analysis on a Structured Query Language (SQL) statement input by a user to obtain the type of the SQL statement and whether the SQL statement has completely distributed support attribute information;

the public gateway sends the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has completely distributed support attribute information;

the distributed database and/or the computing nodes execute the received SQL statements;

if the SQL statement is executed by a distributed database, and the returned result is correct after the execution result is subjected to union operation, the SQL statement has attribute information capable of being completely distributed and supported;

and if the SQL statement is executed by the distributed database, and the returned result is wrong after the execution result is subjected to union operation, the SQL statement does not have the attribute information capable of completely supporting the distribution.

In a second aspect, an embodiment of the present invention further provides a distributed database system supporting stored procedures, triggers, and views, including: the system comprises a public gateway, a distributed database and at least one computing node, wherein a relational database supporting a database linking function is loaded on each computing node;

the public gateway is used for carrying out syntactic semantic analysis on a Structured Query Language (SQL) statement input by a user to obtain the type of the SQL statement and whether the SQL statement has completely distributed support attribute information;

the public gateway is used for sending the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has attribute information capable of completely supporting distribution;

the distributed database and/or the computing nodes are used for executing the received SQL statements;

In the distributed database system provided in this embodiment, on the basis of an original distributed database, a public gateway and a computing node supporting a database link function are added, syntax semantic analysis is performed on an SQL statement through the public gateway to obtain a type of the SQL statement and whether the SQL statement has completely distributed support attribute information, and the SQL statement is sent to the distributed database and/or the computing node according to the type of the SQL statement and whether the SQL statement has completely distributed support attribute information; moreover, the method can be realized by adding the public gateway and the computing node supporting the database linking function, and has the advantages of simple realization mode, no need of a large amount of compiler codes and no need of a large amount of manpower and material resources. In the embodiment, the SQL statement is executed by the calculation node and the distributed database in a coordinated manner, so that for the whole distributed database processing system, more comprehensive support of ANSI SQL is provided.

Drawings

FIG. 1a is a flowchart of a statement execution method supporting stored procedures, triggers and views according to an embodiment of the present invention;

fig. 1b is a schematic structural diagram of a distributed database system according to an embodiment of the present invention;

FIG. 2a is a flowchart of a statement execution method supporting stored procedures, triggers and views according to a second embodiment of the present invention;

fig. 2b is a schematic structural diagram of a distributed database system according to a second embodiment of the present invention;

fig. 3 is a flowchart of a statement execution method supporting a stored procedure, a trigger, and a view according to a third embodiment of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.

Example one

Fig. 1a is a flowchart of a statement execution method supporting a stored procedure, a trigger, and a view according to an embodiment of the present invention, and fig. 1b is a schematic structural diagram of a distributed database system according to an embodiment of the present invention. The embodiment is applicable to the case of executing the statements storing the procedures, triggers and views in the distributed database system, and the method can be executed by the distributed database system. As shown in fig. 1b, the distributed database system 10 includes: the system comprises a public gateway 11, a distributed database 12 and at least one computing node 13, wherein each computing node 13 is loaded with a relational database supporting a database linking function.

Therein, a public gateway (gate)11 is a gateway of the user-oriented distributed database system 10. The user provides SQL statements to the public gateway 11 to execute the statements storing procedures, triggers and views in the distributed database system 10. The public gateway 11 may also send the execution result to the user and display it on the terminal device 20 of the user. Alternatively, in practical applications, the gateway may be used as the public gateway 11, based on which the user provides SQL statements to the gateway through the terminal device 20 via the communication link.

Public gateway 11 is communicatively coupled to compute nodes 13, and the number of compute nodes 13 may be at least one. In practical applications, the computing nodes 13 may be devices with computing functions distributed in different geographic locations, and each computing node 13 is responsible for the computation of SQL statements and carries a relational database supporting database link (DBlink) functions, such as PostgreSQL, MySQL, maridb, and other classical standalone relational databases. These databases have relatively full functional support such as triggers, views, stored procedures, functions, and ACID transactions (hereinafter referred to as transactions). The ACID transaction comprises four basic elements for correctly executing the database transaction: atomicity (Atomicity), Consistency (Consistency), Isolation (Isolation), Durability (Durability). Moreover, the database linking function enables the database in the computing node 13 to refer to and read and write tables in the distributed database 12.

Public gateway 11 is also communicatively coupled to distributed database 12, distributed database 12 including a plurality of storage nodes 121, and in particular, public gateway 11 is communicatively coupled to each storage node 121. Each storage node 121 may be a device with data storage function distributed in different geographic locations, and each storage node 121 is responsible for storing data, and has a relational database such as MySQL, maridb, PostgreSQL and the like loaded thereon to support transactions.

With reference to fig. 1a, the method provided in this embodiment specifically includes the following steps:

s110, the public gateway 11 performs syntax semantic analysis on the Structured Query Language (SQL) statement input by the user to obtain the type of the SQL statement and whether the SQL statement has attribute information capable of supporting complete distribution.

Where the SQL statement includes a stored procedure, trigger, view, or function, the SQL statement type includes creating (Create) a statement to store the procedure, trigger, view, or function, modifying (Alter) a statement to store the procedure, trigger, view, or function, deleting (Drop) a statement to store the procedure, trigger, view, or function, and calling a statement to store the procedure, trigger, view, or function. The creation statement, modification statement, and deletion statement described above belong to a database schema Definition Language (DDL), and the call statement belongs to a Data Manipulation Language (DML). The present embodiment can process the SQL statement including the stored procedure, trigger, view, or function, but is not limited thereto, and the present embodiment can also process the SQL statement not including the stored procedure, trigger, view, or function, and the processing methods are the same.

In an optional embodiment, the public gateway 11 is specifically configured to analyze the SQL statement based on the syntax semantic analyzer, and after the analysis is completed, the syntax semantic analyzer outputs an Abstract Syntax Tree (AST), where the abstract syntax tree is a representation of a tree structure of the SQL statement, each node on the tree is a word, and the tree structure represents syntax. And obtaining the type of the SQL statement according to the abstract syntax tree. In another alternative embodiment, public gateway 11 is specifically configured to extract a syntax string in the SQL statement, such as select, insert, delete, update, create, and the like, and determine the type of the SQL statement to which the syntax string belongs.

If an SQL statement is sent to the distributed database 12 through the public gateway 11, executed by the distributed database 12, and the execution result of the distributed database 12 is subjected to union (for example, union all) operation, and the returned result is correct, then the statement has attribute information capable of supporting full distribution. On the contrary, if the SQL statement is executed by the distributed database, and the result returned after the union operation is performed is incorrect, this statement does not have the completely distributed support attribute information. In one example, assume that T tables are distributed among three different databases of distributed database 12, each database containing tables T with name and grade fields, database 1 { "tom": 86, "mike": 89}, database 2: 85 { "bob": 89}, and the database 3: { "tony": 88, "vincent": 98}, when a select name from T heregorde >88 is run, the return result after execution of a union all by the three databases is { "mike", "mary", "vincent" }, the return result is correct, and then the SQL statement has the completely distributed support attribute information; when a select count (.) form T where grade >88 is run, after the result all is executed by three databases, the returned result is {1,1,1} and the correct returned result is {3}, so that the result returned by the statement is wrong, and the SQL statement does not have completely distributable support attribute information.

And S120, the public gateway 11 sends the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has the attribute information capable of completely supporting distribution.

S130, the distributed database 12 and/or the compute node 13 executes the received SQL statement.

Because the database loaded on the computing node 13 supports the database linking function, the complex SQL, the storage process, the trigger, the function and the transaction are supported by the database linking function, that is, the computing node 13 has the complex SQL, the storage process, the trigger and the transaction function. The distributed database 12 does not have complex SQL, stored procedures, triggers, and transaction functions, but only has simple SQL functions and basic transaction functions. The simple SQL function comprises a read-write function of the single table, and the read-write operation of the single table comprises the following four basic operations.

1，select*from t where filter；

2，insert into t(column_list)values(value_list)；

3，delete from t where filter；

4，update t set…where filter。

Based on the above analysis, the distributed database 12 and the computing node 13 have different computing capabilities, and different statement types and whether the computing node has the computing capability required by the fully distributed supporting attribute information, the public gateway 11 sends the SQL statement to the distributed database 12 and/or the computing node 13 according to the SQL statement type and whether the SQL statement has the fully distributed supporting attribute information, so that the computing capability required by the SQL statement matches the computing capability of the receiver (the distributed database 12 and/or the computing node 13), and the distributed database 12 and the computing node 13 successfully execute the received SQL statement.

Alternatively, if there are more than two compute nodes 13, the public gateway 11 sends the SQL statement to any of the compute nodes 13.

Alternatively, if the public gateway 11 sends the SQL statement to the distributed database 12, the distributed database 12 sends the SQL statement to the storage node 121 therein according to the load balancing rule, and the storage node 121 that receives the SQL statement executes the SQL statement.

In the distributed database system 10 provided in this embodiment, on the basis of the original distributed database 12, a public gateway 11 and a computing node 13 supporting a database link function are added, syntax semantic analysis is performed on an SQL statement by the public gateway 11, so as to obtain a type of the SQL statement and whether the SQL statement has completely distributed support attribute information, and according to the type of the SQL statement and whether the SQL statement has completely distributed support attribute information, the SQL statement is sent to the distributed database 12 and/or the computing node 13, in comparison with the distributed database system 10 in the prior art, the database system provided in this embodiment supports complex functions such as ANSI SQL, triggers, storage procedures, views, and the like; moreover, the method can be realized by adding the public gateway 11 and the computing node 13 supporting the database linking function, and has the advantages of simple realization mode, no need of a large amount of compiler codes and no need of a large amount of manpower and material resources. In the embodiment, the SQL statement is executed by the calculation node and the distributed database in a coordinated manner, so that for the whole distributed database processing system, more comprehensive support of ANSI SQL is provided.

Example two

Fig. 2a is a flowchart of a statement execution method supporting a stored procedure, a trigger, and a view according to a second embodiment of the present invention, and this embodiment is further optimized based on various optional implementation manners of the foregoing embodiments. With reference to fig. 2a, the method provided by the embodiment of the present invention includes the following steps:

s210, the public gateway 11 performs syntax semantic analysis on the Structured Query Language (SQL) statement input by the user to obtain the type of the SQL statement and whether the SQL statement has attribute information capable of supporting complete distribution. Execution continues with S220, S221, or S222.

S220, the public gateway 11 judges that the SQL statement belongs to a statement for creating a stored procedure, a trigger, a view or a function, and judges whether the statement for creating the stored procedure, the trigger, the view or the function has completely distributed support attribute information. If the attribute information capable of being completely distributed is available, jumping to S230; if the completely distributable support attribute information is not available, it jumps to S240.

And S221, the public gateway 11 judges that the SQL statement belongs to a statement for deleting a stored process, a trigger, a view or a function, and jumps to S230.

S222, the public gateway 11 judges that the SQL statement belongs to a statement for modifying the stored procedure, trigger, view or function, generates a statement for deleting the stored procedure, trigger, view or function and a statement for creating a new stored procedure, trigger, view or function from the SQL statement. Execution continues with S223 and S224.

And S223, the public gateway 11 sends the statement for deleting the stored procedures, triggers, views or functions to the distributed database 12 and the computing nodes 13. Execution continues with S230.

S224, the public gateway 11 determines whether the statement creating the new stored procedure, trigger, view, or function has fully distributable support attribute information. If yes, go to S225; if not, S226 is executed.

S225, transmitting the statement for creating the new storage process, trigger, view or function to the distributed database 12 and the computing node 13. Execution continues with S250.

S226, sending the statement for creating the new storage process, trigger, view or function to the computing node 13. Execution continues with S260.

And S230, sending the SQL statement to the distributed database 12 and the computing node 13. Execution continues with S250.

And S240, sending the SQL statement to the computing node 13. Execution continues with S260.

S250, the distributed database 12 and the compute node 13 execute the received SQL statement. And finishing the operation.

S260, the computing node 13 executes the received SQL statement.

The statements of creating, deleting, modifying the stored procedure, the trigger, the view or the function belong to DDL type SQL statements, some of which do not include the DML statement and some of which include the DML statement. If the SQL statement does not comprise the DML statement, all statements comprised by the SQL statement have completely distributed support attribute information, and then the SQL statement is sent to the computing nodes and the distributed database. If the SQL statement comprises the DML statement, it needs to determine whether the SQL statement has the completely distributable support attribute information according to whether the DML statement has the completely distributable support attribute information. Specifically, if the DML statements included in the SQL statement all have completely distributable support attribute information, the SQL statement has completely distributable support attribute information, and is further sent to the computing node and the distributed database; if the DML statement included in the SQL statement does not have the completely-distributed support attribute information, and then the SQL statement is sent to the computing node.

The execution of the compute nodes and distributed databases is detailed below with respect to SQL statements that store procedures, triggers, views, or functions.

Taking the statement of creating the stored procedure as an example, if the DML statements included in the statement of creating the stored procedure all have fully distributable support attribute information, the statement of creating the stored procedure is sent to the computing node 13 and the distributed database 12. The computing node 13 and the distributed database 12 respectively establish a storage procedure in the local database, and transmit storage procedure creation information to the metadata manager 15. If the DML statement included in the create stored procedure statement does not have fully distributable support attribute information, the create stored procedure statement is sent to the compute node 13. The computing node 13 establishes a stored procedure in the local database and sends stored procedure creation information to the metadata manager 15. Wherein storing the procedure creation information comprises: the process name and the parameter name are stored.

Taking the statement of creating the function as an example, if the DML statements included in the statement of creating the function all have fully distributable support attribute information, the statement of creating the function is sent to the computing node 13 and the distributed database 12. The computation node 13 and the distributed database 12 respectively build functions in the local databases and send function creation information to the metadata manager 15. If the DML statement included in the create storage procedure statement does not have fully distributable support attribute information, the create function statement is sent to the compute node 13. The computing node 13 builds a function in the local database and sends the function creation information to the metadata manager 15.

Taking the statement of creating the trigger as an example, if the DML statements included in the statement of creating the trigger all have fully distributable support attribute information, the statement of creating the trigger is sent to the computing node 13 and the distributed database 12. The computing node 13 and the distributed database 12 respectively establish a trigger in the local database and transmit trigger establishment information to the metadata manager 15. If the DML statement included in the create trigger statement does not have fully distributable support attribute information, the create trigger statement is sent to the compute node 13. The computing node 13 establishes a trigger in the local database and sends trigger establishment information to the metadata manager 15. Wherein the trigger setup information includes: the name of the trigger and the name of the corresponding table. For example, the computing node 13 creates a trigger1 on the remote mapping table t, and sends < t, trigger1> information to the metadata manager 15.

Taking the statement for creating the view as an example, if the DML statements included in the statement for creating the view all have fully distributable support attribute information, the statement for creating the view is sent to the computing node 13 and the distributed database 12. The compute node 13 and the distributed database 12 respectively build views in the local databases and send the view building information to the metadata manager 15. If the DML statement included in the create view statement does not have fully distributable support attribute information, the create view statement is sent to the compute node 13. The compute node 13 builds a view in the local database and sends the view build information to the metadata manager 15. Wherein the view establishment information includes information of a base table of the view, the table being the view.

Taking the statements for deleting stored procedures, triggers, views or functions as an example, the delete statements do not include DML statements and have fully distributable support attribute information, and then the delete statements are sent to the computing node 13 and the distributed database 12. The distributed database 12 and the compute nodes 13 delete stored procedures, triggers, views or functions in SQL statements.

Taking the statement for modifying the stored procedure, trigger, view or function as an example, the public gateway 11 generates the statement for deleting the stored procedure, trigger, view or function and the statement for creating a new stored procedure, trigger, view or function from the SQL statement. Sending the statement to delete the stored procedure, trigger, view or function to the distributed database 12 and the compute node 13; determining that the statement for creating the new stored procedure, trigger, view or function has fully distributable support attribute information, and sending the statement for creating the new stored procedure, trigger, view or function to the distributed database 12 and the computing node 13; and judging that the statement for creating the new stored procedure, trigger, view or function does not have fully distributable support attribute information, and sending the statement for creating the new stored procedure, trigger, view or function to the computing node 13.

For the distributed database 12 and the computing node 13, the corresponding stored procedure, trigger, view or function is deleted according to the received delete statement, and then a new stored procedure, trigger, view or function is created according to the received create statement, thereby implementing the modification of the SQL statement.

Taking the table creating statement as an example except for an SQL statement aiming at a stored process, a trigger, a view or a function, the table creating statement does not include a DML statement and has completely distributed support attribute information, the table creating statement is sent to a computing node 13 and a distributed database 12, the distributed database 12 creates a common table, the computing node 13 creates a remote mapping table in a locally-mounted relational database, and the remote mapping table is mapped to the common table in the distributed database 12, so that the global logic representation of the distributed database 12 in the computing node 13 is obtained. Then, the distributed database 12 and the computation node 13 transmit the creation table information to the metadata manager 15. Wherein the creation table information includes a remote mapping table name and a common table name.

As shown in fig. 2b, the metadata manager 15 is communicatively connected to the public gateway 11. The metadata manager 15 may employ components that support high availability, such as ZooKeeper or etcd. The metadata manager 15 is used to manage data in the compute nodes 13. Alternatively, the computing node 13 and the distributed database 12 first transmit the creation table information, the view creation information, the storage procedure creation information, and the trigger creation information to the public gateway 11, and the public gateway 11 then transmits the received information to the metadata manager 15.

As shown in fig. 2b, the private gateway 14 serves as a data accessor for the compute node 13. As with the public gateway 11, the private gateway 14 may also be a gateway. The private gateway 14 is respectively connected with the computing node 13 and the distributed database 12 in a communication way. The computing node 13 reads and writes tables in the distributed database 12 through the private gateway 14.

In this embodiment, the public gateway 11 determines that the SQL statement belongs to a statement for creating, modifying, and deleting a storage process, a trigger, a view, or a function, and sends the SQL statement to the distributed database and/or a node according to whether the statement has completely distributed support attribute information, so that the distributed database system completely supports complex functions such as a storage process, a trigger, a view, or a function.

EXAMPLE III

Fig. 3 is a flowchart of a statement execution method supporting a stored procedure, a trigger, and a view according to a third embodiment of the present invention, and this embodiment is further optimized based on various optional implementation manners of the foregoing embodiments. With reference to fig. 3, the method provided by the embodiment of the present invention includes the following steps:

s310, the public gateway 11 carries out syntax semantic analysis on the SQL sentence of the structured query language input by the user to obtain the type of the SQL sentence and whether the SQL sentence has the attribute information capable of being completely distributed and supported.

And S320, the public gateway 11 judges whether the SQL statement belongs to the data manipulation language DML, judges whether the SQL statement comprises a stored procedure, a trigger, a view or a function which does not have completely distributed support attribute information, if yes, jumps to S330, and if not, jumps to S340.

In this embodiment, the SQL statements belonging to the DML include statements that call stored procedures, triggers, views, or functions.

The stored procedure, trigger, view or function includes at least one SQL statement without completely distributable support attribute information, and is referred to as a stored procedure, trigger, view or function without completely distributable support attribute information. If the SQL statement includes a function, stored procedure, trigger, or view that does not have fully distributable support attribute information, indicating that the processing power of the distributed database 12 is exceeded, then the SQL statement is executed by the compute node 13.

S330, sending the SQL statement to the computing node 13. Execution continues with S390.

In an example, after receiving an add, modify, query, or delete statement (loud statement), the public gateway 11 analyzes the received statement to obtain that an operation object of the SQL statement is a remote mapping table t, and searches the metadata manager 15 for trigger establishment information corresponding to the remote mapping table t. If the < t, trigger1> information is found, which indicates that the trigger1 is established on the remote mapping table t, the SQL statement including the trigger without the completely distributable support attribute information is forwarded to the computing node 13. The addition, modification, query or deletion operation on the computation node 13 may cause a change of the remote mapping table t, and thus a trigger established on the computation node 13 may be triggered, and further an operation of modifying the table in the distributed database 12 may be triggered, so that a repeated operation on the table in the distributed database 12 is not required, the operation amount of the distributed database 12 is reduced, and the situations of high concurrency and storage and processing of a large amount of data are avoided.

It is worth noting that the trigger requires the compute node 13 to support transactional operations to the remote mapping table. If all operations of the computing node 13 for the remote mapping table are within one session/connection, then the transaction may be supported.

S340, judging whether the SQL statement has the completely distributable support attribute information, if the SQL statement has the completely distributable support attribute information, jumping to S350, and if the SQL statement does not have the completely distributable support attribute information, jumping to S360.

If the SQL statement does not include a stored procedure, trigger, view, or function that does not have fully distributable support attribute information, the SQL statement is interpreted to include a stored procedure, trigger, view, or function that does have fully distributable support attribute information, and/or the SQL statement includes a non-stored procedure, trigger, view, or function. Therefore, it is not yet determined whether the attribute information of the SQL statement is sent to the computing node 13 or the distributed database 12.

And S350, sending the SQL statement to the distributed database 12. Execution continues with S370.

If the SQL statement has fully distributable support attribute information, the SQL statement is executed by the distributed database 12.

And S360, the public gateway 11 sends the SQL statement to the distributed database 12 or the computing node 13. Execution continues with S380.

S370, the distributed database 12 executes the received SQL statement. And finishing the operation.

S380, the distributed database 12 or the compute node 13 executes the received SQL statement. And finishing the operation.

S390, the computing node 13 executes the received SQL statement.

If the SQL statement does not have the fully distributable support attribute information, executing any one of the following optional embodiments:

in an alternative embodiment, the public gateway 11 determines that the SQL statement is a simple SQL statement, i.e., the post-processing of the SQL statement is within the computing power of the public gateway 11, sending the SQL statement to the distributed database 12.

Optionally, the threshold criterion for judging the simple SQL statement and the complex SQL statement is cbo (cost base optimizer) optimization, that is, cost-based query transformation, and the threshold is determined according to cost. According to the description of the attribute information in the above embodiment, the SQL statements without completely distributable support attribute information cannot be correctly obtained by the distributed database 12. However, the public gateway 11 has a certain computing power, and the result obtained by the distributed database 12 is post-processed to obtain a correct result. Thus, if the SQL statement is a simple SQL statement, the SQL statement is sent to the distributed database 12, stating whether the post-processing of the SQL statement is within the computing power of the public gateway 11. After the distributed database 12 executes the received SQL statement, the public gateway 11 obtains an execution result of the distributed database 12, and performs post-processing on the execution result, thereby obtaining a correct result.

In the above example, assume that T tables are distributed among three different databases of distributed database 12, each database containing a table T with name and grade fields, database 1 { "tom": 86, "mike": 89}, database 2: 85 { "bob": 89}, and the database 3: { "tony": 88, "vincent": 98}, when a select count (— form two gradient >88 is run, after the result all is executed by the three databases, the result is returned to {1,1,1 }. Then, {1,1,1} is transmitted to the public gateway 11, and the public gateway 11 adds {1,1,1} to obtain {3 }.

In another optional embodiment, the public gateway 11 determines that the SQL statement is a complex SQL statement, and sends the SQL statement to the computing node 13; and the compute node 13 executes the SQL statement. Optionally, when executing the received SQL statement, the computing node 13 splits the SQL statement into a plurality of simple SQL statements; the plurality of simple SQL statements are then sent to the distributed database 12. The distributed database 12 executes the received plurality of simple SQL statements.

In the present embodiment, the computing node 13 only uses the distributed database 12 as a storage engine, so that the storage engine can break the complex SQL statement into a simple SQL statement, thereby implementing the support of the complex SQL. In one example, the compute nodes 13 use the MariaDB and Federatedx storage engines. Assume that there are two tables t1, t2 in the distributed database 12. Table t1 is as follows:

a b

1 2

2 3

table t2 is as follows:

a c

1 2

2 3

the compute node 13 creates locally a joined (federated) table, a remote mapping table t1 and a remote mapping table t2, a remote mapping table t1 and a remote mapping table t2, which map to a common table t1 and a common table t2 in the distributed database 12, respectively.

The public gateway 11 receives the SQL statement input by the user: and (3) selecting from t1join t2on t1.a is t2.a, and carrying out syntax semantic analysis on the SQL statement to obtain that the type of the SQL statement is DML and does not have attribute information capable of being completely distributed, and then forwarding the SQL statement to the computing node 13. The calculation node 13 splits the SQL statement to obtain the following 3 simple statements, where the execution order of the 3 simple statements may be as follows:

Select a,b from t1；

Select a,c from t2where t2.a＝1；

Select a,c from t2where t2.a＝2；

after the 3 simple sentences are run through the distributed database 12, the splicing result is obtained as follows:

a b c

1 2 2

2 3 3

in this embodiment, the public gateway 11 determines that the SQL statement belongs to the data manipulation language DML, and sends the SQL statement to the computing node 13 if the SQL statement includes a storage process, a trigger, a view, or a function that does not have completely distributable support attribute information, and sends the SQL statement to the distributed database 12 if the SQL statement does not include a storage process, a trigger, a view, or a function that does not have completely distributable support attribute information and the public gateway 11 determines that the SQL statement has completely distributable support attribute information; and by judging that the SQL sentence does not have the completely-distributed support attribute information, if the SQL sentence is judged to be a simple SQL sentence by the public gateway 11 and the postprocessing of the SQL sentence is within the computing capability of the public gateway 11, the SQL sentence is sent to the distributed database 12, and if the SQL sentence is judged to be a complex SQL sentence by the public gateway 11, the SQL sentence is sent to the computing node 13, so that the computing capability of the distributed database 12 and the computing capability of the public gateway 11 are fully utilized, and the sentence execution burden of the computing node 13 is lightened. Moreover, the distributed database system 10 is made more fully supporting ANSI SQL by the compute nodes 13 executing functions, stored procedures, triggers or views that do not have fully distributable support attribute information, and complex SQL statements.

In alternative implementations of the foregoing embodiments, after splitting the complex SQL statement into a plurality of simple SQL statements, the computing node 13 is further configured to: a predicate (filter) push down is performed. Specifically, when reading the remote mapping table, all the associated predicates are pushed down to reduce data transmission with the distributed database 12.

In each optional implementation manner of the foregoing embodiments, the computing node 13 is further configured to, when reading the remote mapping table, obtain a column of interest by analyzing the SQL statement, and read only the column of interest, and not read the column of no interest, thereby implementing column reduction (column prune) and reducing data transmission.

In each optional implementation manner of the foregoing embodiments, the computing node 13 is configured to optimize a lookup connection (lookup join), and implement a windowed lookup connection (windowed lookup join). In is used to reduce the number of communications between the compute node 13 and the distributed database 12, particularly when executing lookup join, while limiting that one batch (batch) cannot be too large, i.e. limiting that the number of entities in cannot be too large, for example, may be set to 1024. In the example above where a federated (Federated) table is created, the public gateway 11 receives the user input of an SQL statement: select from t1join 2on t1. a. t2.a, and forward to compute node 13. After the computation node 13 splits the SQL statement, windowing, searching and connecting the split statement, the execution sequence becomes:

Select a,b from t1；

Select a,c from t2where t2.a in(1,2)

optionally, the computing node 13 may correlate the read data, for example, a hash (hash) table may be used to correlate the data.

Example four

Referring to fig. 1b, a distributed database system 10 is provided according to a fourth embodiment of the present invention. The distributed database system 10 includes: the system comprises a public gateway 11, a distributed database 12 and at least one computing node 13, wherein each computing node 13 is loaded with a relational database supporting a database linking function. Further optionally, in conjunction with fig. 2b, the distributed database system 10 further comprises a metadata manager 15, the metadata manager 15 being communicatively connected to the public gateway 11. The metadata manager 15 may employ components that support high availability, such as ZooKeeper or etcd. The metadata manager 15 is used to manage data in the compute nodes 13. The metadata manager 15 is described in detail in the above embodiments, and will not be described in detail here.

Further optionally, in conjunction with fig. 2b, the distributed database system 10 further comprises a private gateway 14(private gateway), the private gateway 14 serving as a data accessor for the compute node 13. As with the public gateway 11, the private gateway 14 may also be a gateway. The private gateway 14 is respectively connected with the computing node 13 and the distributed database 12 in a communication way. The computing node 13 implements mapping of the remote mapping table to the distributed database 12 and performing read-write operations on the table in the distributed database 12 through the private gateway 14. The description of private gateway 14 is described in detail in the above embodiments and will not be repeated here.

The public gateway 11 is used for performing syntax and semantic analysis on a Structured Query Language (SQL) statement input by a user to obtain the type of the SQL statement and whether the SQL statement has completely distributed support attribute information;

the public gateway 11 is used for sending the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has the attribute information capable of completely supporting distribution;

the distributed database 12 and/or the computing nodes 13 are used for executing the received SQL statements;

if the SQL statement is executed by the distributed database, and the returned result is correct after the execution result is subjected to union operation, the SQL statement has attribute information capable of being completely supported in a distributed manner;

if the SQL statement is executed by the distributed database, and the returned result is wrong after the execution result is subjected to union operation, the SQL statement does not have the completely distributed support attribute information.

Optionally, when sending the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has attribute information capable of being fully distributed, the public gateway 11 is specifically configured to: if the SQL statement is judged to belong to the statement for creating the stored procedure, trigger, view or function, and the statement for creating the stored procedure, trigger, view or function has the fully-distributable support attribute information, the SQL statement is sent to the distributed database 12 and the computing node 13; if the SQL statement is judged to belong to the statement for creating the stored procedure, trigger, view or function, and the statement for creating the stored procedure, trigger, view or function does not have the fully distributable support attribute information, the SQL statement is sent to the computing node 13.

Optionally, when sending the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has attribute information capable of being fully distributed, the public gateway 11 is specifically configured to: if the SQL statement is determined to belong to a statement that deletes a stored procedure, trigger, view, or function, the SQL statement is sent to the distributed database 12 and the compute nodes 13.

Optionally, when sending the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has attribute information capable of being fully distributed, the public gateway 11 is specifically configured to: if the SQL statement is judged to belong to the statement for modifying the stored procedure, trigger, view or function, the SQL statement is generated into the statement for deleting the stored procedure, trigger, view or function and the statement for creating a new stored procedure, trigger, view or function; sending the statement to delete the stored procedure, trigger, view or function to the distributed database 12 and the compute node 13; if the statement for creating the new stored procedure, trigger, view or function is determined to have fully distributable support attribute information, sending the statement for creating the new stored procedure, trigger, view or function to the distributed database 12 and the computing node 13; if it is determined that the statement to create a new stored procedure, trigger, view, or function does not have fully distributable support attribute information, the statement to create a new stored procedure, trigger, view, or function is sent to the compute node 12.

Optionally, when sending the SQL statement to the distributed database 12 and/or the computing node 13 according to the type of the SQL statement and whether the SQL statement has attribute information capable of being fully distributed, the public gateway 11 is specifically configured to: judging whether the SQL statement belongs to a data manipulation language DML and does not comprise a storage process, a trigger, a view or a function which does not have completely distributed supporting attribute information, and judging whether the SQL statement has completely distributed supporting attribute information; if the SQL statement is determined to have fully distributable support attribute information, the SQL statement is sent to the distributed database 12. Further optionally, the public gateway 11 is specifically configured to: if the SQL statement is judged not to have the fully distributable support attribute information, the SQL statement is sent to the distributed database 12 or the computing node 13.

Optionally, when determining that the SQL statement does not have fully distributable support information and sending the SQL statement to the distributed database 12 or the computing node 13, the public gateway 11 is specifically configured to: if the SQL statement is judged not to have the completely distributed support attribute information, the SQL statement is judged to be a simple SQL statement, and the SQL statement is sent to the distributed database 12; after the distributed database 12 executes the received SQL statement, the public gateway 11 is further configured to: and acquiring an execution result of the distributed database 12, and performing post-processing on the execution result.

Optionally, when determining that the SQL statement does not have the attribute information capable of being fully distributed, and sending the SQL statement to the computing node 13, the public gateway 11 is specifically configured to: if the SQL statement is determined not to have fully distributable support attribute information and the SQL statement is determined to be a complex SQL statement, the SQL statement is sent to the computing node 13.

The statement execution method supporting the storage process, the trigger and the view provided by the above embodiment can be applied to the distributed database system 10 provided by the embodiment of the present invention, and has corresponding technical features and beneficial effects.

From the above description of the embodiments, it is obvious for those skilled in the art that the present invention can be implemented by software and necessary general hardware, and certainly, can also be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which may be stored in a computer-readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the methods according to the embodiments of the present invention.

It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1.A statement execution method supporting a storage process, a trigger and a view is characterized in that the statement execution method is applied to a distributed database system, the distributed database system comprises a public gateway, a distributed database and at least one computing node, and a relational database supporting a database linking function is loaded on each computing node;

the method comprises the following steps:

if the SQL statement is executed by the distributed database, and the returned result is wrong after the execution result is subjected to union operation, the SQL statement does not have the attribute information capable of being completely distributed and supported;

the public gateway sends the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has attribute information capable of being completely distributed, and the method comprises the following steps:

if the public gateway judges that the SQL statement belongs to a statement for creating a stored process, a trigger, a view or a function, and the statement for creating the stored process, the trigger, the view or the function has completely distributable support attribute information, the SQL statement is sent to a distributed database and a computing node;

if the public gateway judges that the SQL statement belongs to a statement for creating a stored procedure, a trigger, a view or a function, and the statement for creating the stored procedure, the trigger, the view or the function does not have completely distributable support attribute information, sending the SQL statement to a computing node;

if the public gateway judges that the SQL statement belongs to a statement for deleting a stored process, a trigger, a view or a function, the SQL statement is sent to a distributed database and a computing node;

the public gateway judges that the SQL statement belongs to a data manipulation language DML, the SQL statement comprises a stored process, a trigger, a view or a function which does not have completely distributed support attribute information, and the SQL statement is sent to a computing node;

the public gateway judges that the SQL statement belongs to a data manipulation language DML, and the SQL statement does not comprise a storage process, a trigger, a view or a function which does not have completely distributed support attribute information, and judges whether the SQL statement has completely distributed support attribute information;

if the public gateway judges that the SQL statement has the attribute information capable of completely supporting the distribution, the SQL statement is sent to a distributed database;

and if the public gateway judges that the SQL statement does not have the attribute information of completely distributed support, sending the SQL statement to a distributed database or a computing node.

2. The method according to claim 1, wherein the public gateway sends the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has fully distributable support attribute information, comprising:

if the public gateway judges that the SQL statement belongs to a statement for modifying a stored procedure, a trigger, a view or a function, the SQL statement is generated into a statement for deleting the stored procedure, the trigger, the view or the function and a statement for creating a new stored procedure, trigger, view or function;

the public gateway sends the statement for deleting the storage process, the trigger, the view or the function to a distributed database and a computing node;

if the public gateway judges that the statement for creating the new storage process, trigger, view or function has the fully-distributable support attribute information, sending the statement for creating the new storage process, trigger, view or function to a distributed database and a computing node;

and if the public gateway judges that the statement for creating the new stored process, trigger, view or function does not have the fully distributable support attribute information, sending the statement for creating the new stored process, trigger, view or function to the computing node.

3. The method of claim 1, wherein sending the SQL statement to a distributed database if the public gateway determines that the SQL statement does not have fully distributable support attribute information comprises:

if the public gateway judges that the SQL statement does not have the attribute information capable of completely supporting the distribution, the SQL statement is judged to be a simple SQL statement, and the SQL statement is sent to a distributed database;

after the distributed database executes the received SQL statement, the method further comprises the following steps:

and the public gateway acquires the execution result of the distributed database and performs post-processing on the execution result.

4. The method of claim 1, wherein sending the SQL statement to a compute node if the public gateway determines that the SQL statement does not have fully distributable support attribute information comprises:

and if the public gateway judges that the SQL statement does not have the attribute information capable of completely supporting the distribution, and judges that the SQL statement is a complex SQL statement, sending the SQL statement to a computing node.

5. A distributed database system supporting stored procedures, triggers, and views, comprising: the system comprises a public gateway, a distributed database and at least one computing node, wherein a relational database supporting a database linking function is loaded on each computing node;

the public gateway is specifically configured to send the SQL statement to a distributed database and/or a computing node according to the type of the SQL statement and whether the SQL statement has attribute information that can be fully distributed, and includes: