CN110399386B

CN110399386B - Presto-based SQL UPDATE method and control system

Info

Publication number: CN110399386B
Application number: CN201910637363.9A
Authority: CN
Inventors: 洪灿榕
Original assignee: Linewell Software Co Ltd
Current assignee: Linewell Software Co Ltd
Priority date: 2019-07-15
Filing date: 2019-07-15
Publication date: 2022-05-10
Anticipated expiration: 2039-07-15
Also published as: CN110399386A

Abstract

The invention belongs to the technical field of information retrieval, and discloses a Presto-based SQL UPDATE method and a control system, wherein the Presto supports UPDATE operation in syntax by modifying an SQL interpreter, generates a corresponding AST syntax tree when receiving an UPDATE statement, and generates a DELETE execution plan according to a DELETE flow; replicating the DELETE execution plan writes the SET portion into the DELETE's execution plan node, packages it into an UPDATE node, and is ultimately identified by the corresponding connector and performs the UPDATE operation. The invention receives and analyzes the UPDATE SQL request by Presto, generates the UPDATE flow based on the DELETE flow, identifies the flow through the connector, and performs data updating operation when finally operating data, thereby achieving the UPDATE SQL execution effect and realizing data updating.

Description

Presto-based SQL UPDATE method and control system

Technical Field

The invention belongs to the technical field of information retrieval, and particularly relates to a Presto-based SQL UPDATE method and a control system.

Background

Currently, the current state of the art commonly used in the industry is such that:

presto is a large data distributed SQL query engine. The interactive query can be performed on big data from the number G to the number P, and the query speed reaches the level of a commercial data warehouse. It may query data storage products including Hive, Cassandra, and even some businesses. A single Presto query may consolidate data from multiple data sources for unified analysis.

Presto is a distributed system that operates in a clustered environment, with a complete installation including a coordinator (coordinator) and multiple workers. Queries are submitted to the coordinator via a client, such as a Presto CLI, and the coordinator is responsible for parsing, analyzing, and scheduling queries to be executed on different workers. And each worker node reads data from various data sources through a corresponding data source connector.

Since Presto official design does not support SQL UPDATE operations, it cannot be implemented in Presto systems when there is an UPDATE requirement.

When the normal flow submits UPDATE SQL to Presto, the server directly returns no support because Presto of the official original edition has no UPDATE function, and because the prior art has the defect that the table data UPDATE function is absent, the data source needs to be directly operated when a scene with data UPDATE exists in actual use, SQL cannot be used, and the operation is easy to make mistakes.

In summary, the problems of the prior art are as follows:

the existing official Presto original edition does not support UPDATE operation, so that a data source needs to be directly operated when a scene with data updating exists in actual use, SQL cannot be used, and operation is easy to make mistakes.

The difficulty of solving the technical problems is as follows:

the official source code has too few explanatory documents, and the reading and understanding of the source code are difficult; the source code has large code amount and more places to be changed; the compatibility of the newly added module with the functions of the original modules needs to be ensured. More importantly, the difficulty is innovation.

The significance of solving the technical problems is as follows:

the invention provides a method for implementing UPDATE SQL on Presto UPDATE table data, which is used for supplementing the function deficiency of an official version and is a process from 'no' to 'available'.

Disclosure of Invention

Aiming at the problems in the prior art, the invention provides a Presto-based SQL UPDATE method and a control system. The invention provides a function for updating the table data for the prior art and provides an implementation idea at the same time

The invention is realized in this way, a Presto-based SQL UPDATE method includes:

by modifying the SQL interpreter, Presto syntactically supports UPDATE operation, generates a corresponding AST syntax tree when receiving an UPDATE statement, generates a DELETE execution plan according to the DELETE flow, copies the DELETE execution plan, writes the SET part into the execution plan nodes of DELETE, packs the SET part into the UPDATE node, and finally identifies and UPDATEs the corresponding connector.

Further, the Presto-based SQL UPDATE method specifically comprises the following steps:

step one, modifying an SQL parser item, adding UPDATE grammar parsing content, and enabling presto to parse and generate a corresponding AST grammar tree when an UPDATE statement is received.

And step two, adding an UPDATE execution plan node.

And step three, executing the UPDATE execution plan according to a DELETE flow, reading data from a data source by connecting the data source, filtering the data meeting WHERE conditions, and acquiring unique identifiers (ids) of the data.

And step four, executing the PDATE execution plan according to the DELETE flow, reading data from a data source through the data source connection, filtering the data meeting WHERE conditions, and acquiring unique identifiers (id) of the data.

And step five, the connector identifies the UPDATE according to the flow node type and the SET content, and UPDATEs the data according to the id acquired in the step four and the SET content in the UPDATE SQL.

The invention also aims to provide a Presto-based SQL UPDATE control system for implementing the Presto-based SQL UPDATE method.

The invention also aims to provide a Presto-based SQL UPDATE program, which realizes the Presto-based SQL UPDATE method.

The invention also aims to provide an information data processing terminal for realizing the Presto-based SQL UPDATE method.

It is another object of the present invention to provide a computer-readable storage medium, comprising instructions which, when executed on a computer, cause the computer to perform the Presto-based SQL UPDATE method.

In summary, the advantages and positive effects of the invention are:

software version	Executing update (set) 30 world name ═ sam'; effect of (1)	Conclusion
			Original edition	Query failed:line 1:1:mismatched input'update'.	Operation failure
Version of the invention	UPDATE:1row	Operation is successful

Effect graph witness part

The invention makes Presto support SQL UPDATE operation by means of expanding SQL syntax parser, expanding original edition SQL DELETE flow and the like; the SQL UPDATE operation is realized by modifying an antr 4 grammar file, creating an UPDATE flow node, executing a DELETE flow, identifying the UPDATE operation and executing data updating.

The invention receives and analyzes the UPDATE SQL request by Presto, generates the UPDATE flow based on the DELETE flow, identifies the flow through the connector, and performs data updating operation when finally operating data, thereby achieving the UPDATE SQL execution effect and realizing data updating.

Drawings

Fig. 1 is a flowchart of the Presto-based SQL UPDATE method according to the embodiment of the present invention.

Fig. 2 is a schematic diagram of a basic architecture of a Presto system cluster according to an embodiment of the present invention.

Fig. 3 is a schematic processing flow diagram of the UPDATE SQL in the system according to the embodiment of the present invention.

Fig. 4 is an official original version (failure to execute) diagram provided by an embodiment of the present invention.

Fig. 5 is a diagram of the version (execution success) provided by an embodiment of the invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is further described in detail with reference to the following embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

To solve the above problems, the following describes the technical solution of the present invention in detail with reference to the accompanying drawings.

The SQL UPDATE method based on Presto provided by the embodiment of the invention comprises the following steps:

by modifying the SQL interpreter, Presto syntactically support UPDATE operations, upon receiving an UPDATE statement, a corresponding AST syntax tree is generated, and a DELETE execution plan is generated in accordance with DELETE flow, which is replicated and packaged as UPDATE nodes by writing the SET portion into the execution plan nodes of DELETE, and finally identified by the corresponding connectors and subjected to UPDATE operations.

As shown in fig. 1 to fig. 3, the SQL UPDATE method based on Presto provided in the embodiment of the present invention specifically includes the following steps:

s101, modifying an SQL parser item, adding UPDATE grammar parsing content, and enabling presto to parse and generate a corresponding AST grammar tree when an UPDATE statement is received.

S102, adding an UPDATE execution plan node.

S103, executing the UPDATE execution plan according to a DELETE flow, reading data from a data source by connecting the data source, filtering the data meeting WHERE conditions, and obtaining the unique identification (id) of the data.

S104, executing the PDATE execution plan according to a DELETE flow, reading data from a data source through a data source connection, filtering the data meeting WHERE conditions, and obtaining the unique identification (id) of the data.

And S105, identifying the UPDATE by the connector according to the flow node type and the SET content, and updating the data according to the id acquired in the fourth step and the SET content in the UPDATE SQL.

The invention is further described with reference to specific examples.

Example 1:

assuming that there is a table user, there are two columns in the table, name and pwd, and there are several data in the table, each data has a unique id, when executing a statement:

update user set pwd＝'123'where name＝'mar'。

the invention provides syntax and function support of update for presto which originally does not support update, and the specific steps comprise:

(1) and (4) modifying an antlr4 syntax rule file and adding update syntax support.

(2) When the server receives update sql, it parses and generates the corresponding AST syntax tree.

(3) An update execution plan node is created that inherits the delete execution plan node, where set content is added.

(4) And creating a delete query execution process, putting the update execution plan node into the process, and executing according to the delete process.

(5) And the delete flow screens out the ids of all data with the name ═ mar' and sends the ids to the connector for data deletion operation.

(6) When the connector processes the data deleting operation, whether the data deleting operation is an update flow or not is identified specifically, if so, the corresponding row is updated, otherwise, the data deleting operation is executed according to the original delete flow.

The node added with the UPDATE execution plan node provided by the embodiment of the invention is the extension of the DELETE execution plan node, only has the content of column value more than that of the DELETE node, and is compatible with the execution flow of DELETE because of the subtype of the DELETE node.

The invention is further described below in connection with a functional demonstration.

The following is the function demonstration content of the invention, and the flow is as follows: creating a table containing 3 fields, inserting 3 pieces of data into the table, executing an UPDATE statement to UPDATE one piece of data, and checking an UPDATE result.

The official original version (failed execution) is as in fig. 4. The version of the invention (successful execution) is shown in fig. 5.

In the above embodiments, all or part of the implementation may be realized by software, hardware, firmware, or any combination thereof. When used in whole or in part, is implemented in a computer program product that includes one or more computer instructions. When the computer program instructions are loaded or executed on a computer, the procedures or functions according to the embodiments of the present invention are wholly or partially generated. The computer may be a general purpose computer, a special purpose computer, a network of computers, or other programmable device. The computer instructions may be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another, for example, the computer instructions may be transmitted from one website site, computer, server, or data center to another website site, computer, server, or data center via wire (e.g., coaxial cable, fiber optic, Digital Subscriber Line (DSL), or wireless (e.g., infrared, wireless, microwave, etc.)). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device, such as a server, a data center, etc., that includes one or more of the available media. The usable medium may be a magnetic medium (e.g., floppy Disk, hard Disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., Solid State Disk (SSD)), among others.

The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents and improvements made within the spirit and principle of the present invention are intended to be included within the scope of the present invention.

Claims

1. A Presto-based SQL UPDATE method is characterized by comprising the following steps:

modifying the SQL interpreter to perform UPDATE syntax operation; receiving an UPDATE statement, generating a corresponding AST syntax tree, and generating a DELETE execution plan according to a DELETE flow;

copying the DELETE execution plan to write the SET part into the execution plan nodes of the DELETE, packaging into UPDATE nodes, and finally identifying and updating by corresponding connectors;

the SQL UPDATE method based on Presto specifically comprises the following steps:

modifying an SQL parser item, adding UPDATE grammar parsing content, and enabling presto to parse and generate a corresponding AST grammar tree when an UPDATE statement is received;

step two, adding an UPDATE execution plan node;

step three, executing the UPDATE execution plan according to a DELETE flow, reading data from a data source by data source connection, filtering the data meeting WHERE conditions, and obtaining unique identification ids of the data;

and step four, the connector identifies the UPDATE according to the flow node type and the SET content, and UPDATEs the data according to the id acquired in the step three and the SET content in the UPDATE SQL.

2. A Presto-based SQL UPDATE control system implementing the Presto-based SQL UPDATE method of claim 1.

3. A Presto-based SQL UPDATE program, characterized in that the Presto-based SQL UPDATE program implements the Presto-based SQL UPDATE method of claim 1.

4. An information data processing terminal for implementing the Presto-based SQL UPDATE method according to claim 1.

5. A computer-readable storage medium comprising instructions that, when executed on a computer, cause the computer to perform the Presto-based SQL UPDATE method of claim 1.