CN110399386A - A kind of SQL UPDATE method and control system based on Presto - Google Patents
A kind of SQL UPDATE method and control system based on Presto Download PDFInfo
- Publication number
- CN110399386A CN110399386A CN201910637363.9A CN201910637363A CN110399386A CN 110399386 A CN110399386 A CN 110399386A CN 201910637363 A CN201910637363 A CN 201910637363A CN 110399386 A CN110399386 A CN 110399386A
- Authority
- CN
- China
- Prior art keywords
- update
- sql
- presto
- data
- delete
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
- G06F16/2471—Distributed queries
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Abstract
The invention belongs to technical field of information retrieval, disclose a kind of SQL UPDATE method and control system based on Presto, by modifying SQL interpreter, Presto is set grammatically to support UPDATE operation, corresponding AST syntax tree is generated when receiving UPDATE statement, and generates DELETE executive plan by DELETE process；Duplication DELETE executive plan will be packaged into UPDATE node in the executive plan node of SET partial write DELETE, is finally identified by corresponding connector and is updated operation.The present invention is received and parsed through UPDATE SQL request by Presto and is generated UPDATE process based on DELETE process, identification by connector to process, data are carried out in final operation data and update operation, to reach UPDATE SQL implementation effect, realize that data update.
Description
Technical field
The invention belongs to technical field of information retrieval more particularly to a kind of SQL UPDATE methods and control based on Presto
System processed.
Background technique
Currently, the prior art commonly used in the trade is such that
Presto is the distributed SQL query engine an of big data.It can be interacted to from several G to the big data of several P
The speed of the inquiry of formula, inquiry reaches the rank of Business Data Warehouse.It can be inquired including Hive, Cassandra even
The product data storage of some business.Single Presto inquires the combinable data from multiple data sources and carries out united analysis.
Presto is a distributed system, is operated in cluster environment, and complete installation includes a coordinator
(coordinator) and multiple workers.Inquiry is submitted to coordinator, coordinator for example, by the client of Presto CLI
It is responsible for parsing, analysis and arranges to inquire to execute on different worker.Each worker node is connected by corresponding data source
Device reads data from various data sources.
Due to not supporting SQL UPDATE operation when the design of Presto official, when upgrading demand in Presto
It can not just be realized by SQL in system.
When normal flow submits UPDATE SQL to Presto, since the Presto of official's master does not have more new function, because
This server, which directly returns, not to be supported, due to drawback of the prior art is that causing not to the more new function of table data in reality
It needs directly to operate data source when the scene that border has data to update in, is not available SQL and operation is easy error.
In conclusion problem of the existing technology is:
Existing official Presto master does not support UPDATE operation, when leading to the scene for having data to update in actual use
It needs directly to operate data source, is not available SQL and operation is easy error.
Solve the difficulty of above-mentioned technical problem:
Official's source code illustrates that document is very little, and reading understands that source code difficulty is larger；Source code size of code is big, the ground for needing to change
Side is more；Need to guarantee the module being newly added to the compatibility etc. of original each functions of modules.More mainly difficulty is innovation.
Solve the meaning of above-mentioned technical problem:
The invention proposes a kind of to execute the implementation method that UPDATE SQL carries out the update of table data on Presto, is used for
The function of supplementing official version is insufficient, is the process from " not having " to " having ".
Summary of the invention
In view of the problems of the existing technology, the present invention provides a kind of SQL UPDATE method and control based on Presto
System processed.The present invention provides the function of updating to table data for the prior art, while proposing a kind of realization approach
The invention is realized in this way a kind of SQL UPDATE method based on Presto, comprising:
By modifying SQL interpreter, Presto is set grammatically to support UPDATE operation, the life when receiving UPDATE statement
DELETE executive plan is generated at corresponding AST syntax tree, and by DELETE process, replicates DELETE executive plan for the portion SET
Divide in the executive plan node of write-in DELETE and be packaged into UPDATE node, is finally identified and be updated by corresponding connector
Operation.
Further, the SQL UPDATE method based on Presto specifically includes the following steps:
Step 1 modifies SQL syntax resolver parser project, adds UPDATE syntax parsing content, presto is made to exist
It can be parsed when receiving UPDATE statement and generate corresponding AST syntax tree.
Step 2 adds a kind of UPDATE executive plan node.
Step 3, UPDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data,
The data for meeting WHERE condition are filtered out, and obtain the unique identification (id) of these data.
Step 4, PDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data,
The data for meeting WHERE condition are filtered out, and obtain the unique identification (id) of these data.
Step 5, connector go out UPDATE with flow nodes type and SET content recognition, according to the id obtained in step 4
With the SET content in UPDATE SQL, operation is updated to data.
Another object of the present invention be to provide a kind of SQL UPDATE method based on Presto described in implementation based on
The SQL UPDATE control system of Presto.
Another object of the present invention is to provide a kind of SQL UPDATE program based on Presto, described based on Presto's
SQL UPDATE program realizes the SQL UPDATE method based on Presto.
Another object of the present invention is to provide the information of a kind of SQL UPDATE method described in realize based on Presto
Data processing terminal.
Another object of the present invention is to provide a kind of computer readable storage medium, including instruction, when its on computers
When operation, so that computer executes the SQL UPDATE method based on Presto.
In conclusion advantages of the present invention and good effect are as follows:
| Software version | Execute update emp set age=30where name='sam'；Effect | Conclusion |
| Original version | Query failed:line 1:1:mismatched input'update'. | Operation failure |
| Version of the present invention | UPDATE:1row | It operates successfully |
* effect picture witnesses bright part
The present invention supports Presto by means such as generalized Petri net grammar parser, extension master SQL DELETE processes
SQL UPDATE operation；By modifying antlr4 grammar file, UPDATE flow nodes are created, DELETE process, identification are executed
UPDATE operation and execution data update, and realize SQL UPDATE operation.
The present invention receives and parses through UPDATE SQL request by Presto and generates UPDATE based on DELETE process
Process, the identification by connector to process carry out data in final operation data and update operation, to reach UPDATE
SQL implementation effect realizes that data update.
Detailed description of the invention
Fig. 1 is the SQL UPDATE method flow diagram provided in an embodiment of the present invention based on Presto.
Fig. 2 is the basic framework schematic diagram of Presto system cluster provided in an embodiment of the present invention.
Fig. 3 is the processing flow schematic diagram of UPDATE SQL provided in an embodiment of the present invention in systems.
Fig. 4 is official's original version (executing failure) figure provided in an embodiment of the present invention.
Fig. 5 is provided in an embodiment of the present invention (to be run succeeded) figure using version of the present invention.
Specific embodiment
In order to make the objectives, technical solutions, and advantages of the present invention clearer, with reference to embodiments, to the present invention
It is further elaborated.It should be appreciated that the specific embodiments described herein are merely illustrative of the present invention, it is not used to
Limit the present invention.
Existing official Presto master does not support UPDATE operation, when leading to the scene for having data to update in actual use
It needs directly to operate data source, is not available SQL and operation is easy error.
To solve the above problems, being described in detail with reference to the accompanying drawing to technical solution of the present invention.
SQL UPDATE method provided in an embodiment of the present invention based on Presto includes:
By modifying SQL interpreter, Presto is set grammatically to support UPDATE operation, the life when receiving UPDATE statement
DELETE executive plan is generated at corresponding AST syntax tree, and by DELETE process, replicates DELETE executive plan for the portion SET
Divide in the executive plan node of write-in DELETE and be packaged into UPDATE node, is finally identified and be updated by corresponding connector
Operation.
As shown in Figure 1 to Figure 3, the SQL UPDATE method provided in an embodiment of the present invention based on Presto specifically include with
Lower step:
S101 modifies SQL syntax resolver parser project, adds UPDATE syntax parsing content, is meeting presto
It can be parsed when receiving UPDATE statement and generate corresponding AST syntax tree.
S102 adds a kind of UPDATE executive plan node.
S103, UPDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data, mistake
The data for meeting WHERE condition are filtered out, and obtain the unique identification (id) of these data.
S104, PDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data, mistake
The data for meeting WHERE condition are filtered out, and obtain the unique identification (id) of these data.
S105, connector go out UPDATE with flow nodes type and SET content recognition, according to the id that is obtained in step 4 and
SET content in UPDATE SQL is updated operation to data.
The invention will be further described combined with specific embodiments below.
Embodiment 1:
Assuming that there is a table user, there are two column, respectively name and pwd in table, there are several data in table, every data is all
There is unique id, when execution sentence:
Update user set pwd='123'where name='mar'.
The present invention does not support that the presto of update provides the grammer of update and function is supported, specific steps packet for script
It includes:
(1) antlr4 Grammar Rule file is modified, addition update grammer is supported.
(2) it when server receives update sql, parses and generates corresponding AST syntax tree.
(3) update executive plan node is created, which inherits delete executive plan node, add in the node
Set content.
(4) delete query execution process is created, update executive plan node is put into process, by delete process
It executes.
(5) id of all data of name='mar' is filtered out and is sent to connector and counted by delete process
According to delete operation.
(6) when connector handles data delete operation, then specifically identify whether if it is to update for update process
Otherwise corresponding row is executed by original delete process.
Addition UPDATE executive plan node provided in an embodiment of the present invention, such node are DELETE executive plan nodes
Extension, the content of column=value has only been had more than DELETE node, due to being the subtype of DELETE node,
The execution process of the compatible DELETE of this UPDATE node.
Below with reference to function presentation, the invention will be further described.
The following are function presentation content of the invention, processes are as follows: one table comprising 3 fields of creation is inserted into 3 into table
Then data executes UPDATE statement and is updated to a data therein, and checks update result.
Official's original version (executing failure) such as Fig. 4.Version (running succeeded) such as Fig. 5 of the present invention.
In the above-described embodiments, can come wholly or partly by software, hardware, firmware or any combination thereof real
It is existing.When using entirely or partly realizing in the form of a computer program product, the computer program product include one or
Multiple computer instructions.When loading on computers or executing the computer program instructions, entirely or partly generate according to
Process described in the embodiment of the present invention or function.The computer can be general purpose computer, special purpose computer, computer network
Network or other programmable devices.The computer instruction may be stored in a computer readable storage medium, or from one
Computer readable storage medium is transmitted to another computer readable storage medium, for example, the computer instruction can be from one
A web-site, computer, server or data center pass through wired (such as coaxial cable, optical fiber, Digital Subscriber Line (DSL)
Or wireless (such as infrared, wireless, microwave etc.) mode is carried out to another web-site, computer, server or data center
Transmission).The computer-readable storage medium can be any usable medium or include one that computer can access
The data storage devices such as a or multiple usable mediums integrated server, data center.The usable medium can be magnetic Jie
Matter, (for example, floppy disk, hard disk, tape), optical medium (for example, DVD) or semiconductor medium (such as solid state hard disk Solid
State Disk (SSD)) etc..
The foregoing is merely illustrative of the preferred embodiments of the present invention, is not intended to limit the invention, all in essence of the invention
Made any modifications, equivalent replacements, and improvements etc., should all be included in the protection scope of the present invention within mind and principle.
Claims (6)
1. a kind of SQL UPDATE method based on Presto, which is characterized in that the side UPDATE SQL based on Presto
Method includes:
SQL interpreter is modified, UPDATE grammatical operations are carried out；UPDATE statement is received, generates corresponding AST syntax tree, and press
DELETE process generates DELETE executive plan；
DELETE executive plan is replicated by the executive plan node of SET partial write DELETE, is packaged into UPDATE node, most
It is identified eventually by corresponding connector and is updated operation.
2. the SQL UPDATE method based on Presto as described in claim 1, which is characterized in that described based on Presto's
SQL UPDATE method specifically includes the following steps:
Step 1 modifies SQL syntax resolver parser project, adds UPDATE syntax parsing content, is receiving presto
It can be parsed when to UPDATE statement and generate corresponding AST syntax tree；
Step 2 adds a kind of UPDATE executive plan node；
Step 3, UPDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data, filtering
Meet the data of WHERE condition out, and obtains the unique identification (id) of these data；
Step 4, PDATE executive plan are executed by DELETE process, are first connected from data source to data source and are read data, filtering
Meet the data of WHERE condition out, and obtains the unique identification (id) of these data；
Step 5, connector go out UPDATE with flow nodes type and SET content recognition, according to the id that is obtained in step 4 and
SET content in UPDATE SQL is updated operation to data.
3. a kind of SQL based on Presto for implementing the SQL UPDATE method described in claim 1 based on Presto
UPDATE control system.
4. a kind of SQL UPDATE program based on Presto, which is characterized in that the SQL UPDATE journey based on Presto
Sequence realizes the SQL UPDATE method based on Presto described in claim 1~2 any one.
5. a kind of information data for the SQL UPDATE method realized described in claim 1~2 any one based on Presto
Processing terminal.
6. a kind of computer readable storage medium, including instruction, when run on a computer, so that computer is executed as weighed
Benefit requires the SQL UPDATE method described in 1-2 any one based on Presto.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910637363.9A CN110399386B (en) | 2019-07-15 | 2019-07-15 | Presto-based SQL UPDATE method and control system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910637363.9A CN110399386B (en) | 2019-07-15 | 2019-07-15 | Presto-based SQL UPDATE method and control system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110399386A true CN110399386A (en) | 2019-11-01 |
| CN110399386B CN110399386B (en) | 2022-05-10 |
Family
ID=68325580
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910637363.9A Active CN110399386B (en) | 2019-07-15 | 2019-07-15 | Presto-based SQL UPDATE method and control system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110399386B (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110874364A (en) * | 2019-11-19 | 2020-03-10 | 北京启迪区块链科技发展有限公司 | Query statement processing method, device, equipment and storage medium |
| CN113032642A (en) * | 2019-12-24 | 2021-06-25 | 医渡云（北京）技术有限公司 | Data processing method, device and medium for target object and electronic equipment |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107729428A (en) * | 2017-09-28 | 2018-02-23 | 南威软件股份有限公司 | A kind of SQL query method based on Presto and Elasticsearch |
| US20180196850A1 (en) * | 2017-01-11 | 2018-07-12 | Facebook, Inc. | Systems and methods for optimizing queries |
| CN108563509A (en) * | 2018-04-28 | 2018-09-21 | 北京京东金融科技控股有限公司 | Data query implementation method, device, medium and electronic equipment |
| CN109033123A (en) * | 2018-05-31 | 2018-12-18 | 康键信息技术（深圳）有限公司 | Querying method, device, computer equipment and storage medium based on big data |
| CN109739882A (en) * | 2019-01-04 | 2019-05-10 | 南威软件股份有限公司 | A kind of big data enquiring and optimizing method based on Presto and Elasticsearch |
-
2019
- 2019-07-15 CN CN201910637363.9A patent/CN110399386B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20180196850A1 (en) * | 2017-01-11 | 2018-07-12 | Facebook, Inc. | Systems and methods for optimizing queries |
| CN107729428A (en) * | 2017-09-28 | 2018-02-23 | 南威软件股份有限公司 | A kind of SQL query method based on Presto and Elasticsearch |
| CN108563509A (en) * | 2018-04-28 | 2018-09-21 | 北京京东金融科技控股有限公司 | Data query implementation method, device, medium and electronic equipment |
| CN109033123A (en) * | 2018-05-31 | 2018-12-18 | 康键信息技术（深圳）有限公司 | Querying method, device, computer equipment and storage medium based on big data |
| CN109739882A (en) * | 2019-01-04 | 2019-05-10 | 南威软件股份有限公司 | A kind of big data enquiring and optimizing method based on Presto and Elasticsearch |
Non-Patent Citations (3)
| Title |
|---|
| GE WENSHUAI等: "Business-oriented customized big data query system and its SQL parser design and implementation", 《ROCEEDINGS OF 2018 2ND INTERNATIONAL CONFERENCE ON ELECTRONIC INFORMATION TECHNOLOGY AND COMPUTER ENGINEERING (EITCE 2018)》 * |
| SUNDYXIONG: "分布式sql引擎原理分析-逻辑执行计划生成", 《HTTPS://CLOUD.TENCENT.COM/DEVELOPER/ARTICLE/1195072》 * |
| 王立: "分布式内存数据库系统的查询处理与优化", 《中国优秀博硕士学位论文全文数据库(博士)信息科技辑》 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110874364A (en) * | 2019-11-19 | 2020-03-10 | 北京启迪区块链科技发展有限公司 | Query statement processing method, device, equipment and storage medium |
| CN113032642A (en) * | 2019-12-24 | 2021-06-25 | 医渡云（北京）技术有限公司 | Data processing method, device and medium for target object and electronic equipment |
| CN113032642B (en) * | 2019-12-24 | 2024-02-09 | 医渡云（北京）技术有限公司 | Data processing method and device for target object, medium and electronic equipment |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110399386B (en) | 2022-05-10 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108399256B (en) | Heterogeneous database content synchronization method and device and middleware | |
| CN110908997B (en) | Data blood relationship construction method and device, server and readable storage medium | |
| US8065323B2 (en) | Offline validation of data in a database system for foreign key constraints | |
| CN101901265B (en) | Objectification management system of virtual test data | |
| US9563650B2 (en) | Migrating federated data to multi-source universe database environment | |
| US10997131B1 (en) | Using a member attribute to perform a database operation on a computing device | |
| US9128962B2 (en) | View based table replacement for applications | |
| CN112235311A (en) | OVSDB client code automatic generation method, system, device and medium | |
| CN110399386A (en) | A kind of SQL UPDATE method and control system based on Presto | |
| US20010052111A1 (en) | Management of application programming interface interoperability | |
| CN112650526B (en) | Method, device, electronic equipment and medium for detecting version consistency | |
| CN108694172B (en) | Information output method and device | |
| CN111694846B (en) | Separation mode distributed storage process implementation method based on Type 2JDBC driver | |
| CN112948306A (en) | Method and device for expanding system interface | |
| US11900269B2 (en) | Method and apparatus for managing knowledge base, device and medium | |
| US11620285B2 (en) | Automatic database query translation | |
| US20210165726A1 (en) | Transferring data from a source complex variable to a target complex variable | |
| CN111400396A (en) | Method and device for generating data synchronization script and computer readable storage medium | |
| US20150106309A1 (en) | Automatically deriving context when extracting a business rule | |
| US20230289332A1 (en) | Lifecycle support for metadata-based data ingestion | |
| US20240126541A1 (en) | Inversion process for rules framework | |
| US11888937B2 (en) | Domain specific provider contracts for core data services | |
| CN110321138A (en) | A kind of program updates, moving method and device | |
| CN112925655B (en) | Decoupling system for dividing service and method thereof | |
| JP4120879B2 (en) | Program generation system and method and program thereof |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |