How to implement the Update function in Trino Iceberg connection

Abstract

The present invention provides a method for realizing the Update function in the Trino Iceberg connection, comprising the following steps: receiving and parsing the Update Sql task submitted by the user through the Trino Engine Sql execution engine to obtain an executable plan; supporting the Update function logic of Iceberg metadata and underlying data through the UpdatablePageSource software programming interface provided by Trino; and performing data UPDATE operation on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg according to the Update function logic. The present invention can support the Update function when connecting to the Iceberg data lake through Trino, so that users can complete more batch processing scenarios conveniently and efficiently.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method for implementing an Update function in a Trino Iceberg connection, a computer device, and a non-temporary computer-readable storage medium. Background Art

[0002] Iceberg is an open table format for large analytical data sets. It uses a high-performance table format similar to a SQL (Structured Query Language) table. The table can be added to computing engines such as Spark (a fast and general computing engine designed for large-scale data processing), Trino (a fast distributed SQL query engine for big data analysis), Flink (an open source stream processing framework), and Hive (a data warehouse tool) and supports operations such as adding, deleting, querying, and modifying the table format.

[0003] Currently, the only computing engines that can support UPDATE operations on Iceberg tables are Spark and Flink, which provide more computing scenarios in real-time and offline computing and analysis to meet more business needs. However, Trino does not support UPDATE operations on Iceberg tables.

[0004] Trino is a PB-level distributed SQL computing engine based on memory. It realizes low coupling between computing layer and storage layer through Connector SPI (Serial Peripheral Interface), which supports access and operation of various data sources and has the feature of cross-source query. As one of the data lake solutions, Iceberg supports Iceberg Connector's Update syntax, which is an important part of Trino's docking data lake solution. However, in related technologies, Trino does not support UPDATE operation on Iceberg table, which makes it impossible to implement batch update function of data when Trino docks Iceberg data. Summary of the invention

[0005] To solve the above technical problems, a first object of the present invention is to provide a method for realizing the Update function in Trino Iceberg connection.

[0006] A second object of the present invention is to provide a computer device.

[0007] A third object of the present invention is to provide a non-transitory computer-readable storage medium.

[0008] The technical solution adopted by the present invention is as follows:

[0009] An embodiment of the first aspect of the present invention proposes a method for implementing the Update function in the Trino Iceberg connection, comprising the following steps: receiving and parsing the Update Sql task submitted by the user through the Trino Engine Sql execution engine to obtain an executable plan; supporting the Update function logic of Iceberg metadata and underlying data through the UpdatablePageSource software programming interface provided by Trino; and performing data UPDATE operations on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg according to the Update function logic.

[0010] The method for implementing the Update function in the Trino Iceberg connection proposed in the present invention may also have the following additional technical features:

[0011] According to one embodiment of the present invention, receiving and parsing an Update Sql task submitted by a user through a Trino Engine Sql execution engine to obtain an executable plan specifically includes: receiving the Update Sql task submitted by a user through the Trino Engine Sql execution engine; the Trino Engine Sql execution engine obtains a Session submitted by the user according to the Update Sql task, and authenticates the user's authority according to the Session submitted by the user; if the authority authentication passes, the Update Sql task is parsed by a logical planner to generate an abstract syntax tree, and according to the Catalog where the Update Sql task is located, the metadata information of the table involved in the Update Sql task is obtained from the corresponding Connector and the optimization rules of the syntax optimizer of the Trino Engine, the Update Sql task is optimized and converted into a logical plan; a distributed query plan is generated according to the logical plan through a distributed planner, and is handed over to an execution planner; the execution planner converts the distributed query plan into an executable plan, discovers and obtains available nodes through a node manager, and distributes the executable plan to the available nodes for execution.

[0012] According to an embodiment of the present invention, the Update function logic of Iceberg metadata and underlying data is supported through the UpdatablePageSource software programming interface provided by Trino, specifically including: according to the executable plan, the split information of the table involved in the Update Sql task is obtained through the SplitManager software programming interface; an UpdatablePageSource software programming interface is created for each split information, so that the Trino Engine obtains the next page of data through the getNextPage method provided by the UpdatablePageSource software programming interface, and performs filtering according to SQL conditions when reading the next page of data and generates a filtered result page; according to the result page, the generation of the deleteFile file and the insertFile file of the data is completed according to the rowID in the updateRows method through the UpdatablePageSource software programming interface; in the finish method of the UpdateablePageSource software programming interface, the Transaction application programming interface provided by Iceberg is used to record the underlying metadata and add and delete data for the deleteFile file and the insertFile file; when the Trino Engine has processed all the pages of the Splits, the Trino Engine calls the finishUpdate method of the ConnectorMetadata software programming interface to submit the transaction.

[0013] According to one embodiment of the present invention, according to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg, specifically including: judging the types of the FileContent of the deleteFile file and the insertFile file; if the type is the DATA type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addInsertFile method; if it is the POS-DELETETION type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addDeleteFile method; if the type is neither the DATA type nor the POS-DELETETION type, throwing an exception that the type is not supported; committing this transaction to complete the data UPDATE operation on the Update Sql task.

[0014] An embodiment of a second aspect of the present invention provides a computer device.

[0015] Embodiments of a third aspect of the present invention provide a non-transitory computer-readable storage medium.

[0016] Beneficial effects of the present invention:

[0017] The present invention can support the Update function when docking with Iceberg data through Trino, allowing users to complete batch running and ad hoc query logging operations in the same engine, conveniently and efficiently completing more batch processing scenarios, not only reducing the development cost and operation and maintenance cost of the task, but also completing the ETL, modeling and analysis of the data in the OLAP (On-Line Transaction Processing) analysis engine, and eliminating the problem of inconsistent data caliber. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] Figure 1 is a flowchart of a method for implementing the Update function in a Trino Iceberg connection according to an embodiment of the present invention. DETAILED DESCRIPTION

[0019] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0020] Figure 1 is a flow chart of a method for implementing the Update function in a Trino Iceberg connection according to an embodiment of the present invention. Figure 1 As shown, the method comprises the following steps:

[0021] S1, receive and parse the Update Sql task submitted by the user through the Trino Engine Sql execution engine to obtain an executable plan.

[0022] Furthermore, the Update Sql task submitted by the user is received and parsed by the Trino Engine Sql execution engine to obtain an executable plan, which specifically includes: receiving the Update Sql task submitted by the user by the Trino Engine Sql execution engine; the Trino Engine Sql execution engine obtains the Session submitted by the user according to the Update Sql task, and authenticates the user's authority according to the Session submitted by the user; if the authority authentication is passed, the Update Sql task is parsed by the logical planner to generate an abstract syntax tree, and according to the Catalog where the Update Sql task is located, combined with the metadata information of the table involved in the Update Sql task obtained from the corresponding Connector and the optimization rules of the syntax optimizer of the Trino Engine, the Update Sql task is optimized and converted into a logical plan; a distributed query plan is generated according to the logical plan by the distributed planner, and is handed over to the execution planner; the execution planner converts the distributed query plan into an executable plan, and discovers and obtains available nodes through the node manager, and distributes the executable plan to the available nodes for execution.

[0023] Specifically, the Trino Engine Sql execution engine receives the Update Sql task submitted by the user, obtains the Session submitted by the user according to the Update Sql task, and performs permission authentication on the user according to the Session submitted by the user. If the permission authentication fails, it returns permission failure to the client.

[0024] If the permission is granted, the Update Sql task after authentication is parsed using the logical planner to generate an abstract syntax tree (AST), and based on the Catalog where the Update Sql task is located, the metadata information of the table involved in the Update Sql task is obtained from the corresponding Connector and the optimization rules of the syntax optimizer of the Trino Engine are optimized and converted into a logical plan. The distributed planner generates a distributed query plan and hands it over to the execution planner. The execution planner converts its distributed query plan into a truly executable plan, obtains available nodes through service discovery, and distributes the plan to these available nodes for execution.

[0025] S2, through the UpdatablePageSource software programming interface provided by Trino, supports the Update function logic of Iceberg metadata and underlying data.

[0026] Further, according to an embodiment of the present invention, the Update function logic of Iceberg metadata and underlying data is supported through the UpdatablePageSource software programming interface provided by Trino, specifically including: according to the executable plan, the split information of the table involved in the Update Sql task is obtained through the SplitManager software programming interface; an UpdatablePageSource software programming interface is created for each split information, so that the Trino Engine obtains the next page of data through the getNextPage method provided by the UpdatablePageSource software programming interface, and performs filtering according to the SQL conditions when reading the next page of data and generates a filtered result page; according to the result page, the generation of the deleteFile file and the insertFile file of the data is completed according to the rowID in the updateRows method through the UpdatablePageSource software programming interface; in the finish method of the UpdateablePageSource software programming interface, the Transaction application programming interface provided by Iceberg is used to record the underlying metadata of the deleteFile file and the insertFile file and add and delete data; when the Trino Engine has processed all the pages of the Splits, the Trino Engine calls the finishUpdate method of the ConnectorMetadata software programming interface to submit the transaction.

[0027] S3, according to the Update function logic, performs data UPDATE operation on Iceberg's underlying data through the RowDelta application programming interface provided by Iceberg.

[0028] Further, according to an embodiment of the present invention, according to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg, specifically including: judging the type of the FileContent of the deleteFile file and the insertFile file; if the type is DATA type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addInsertFile method; if it is POS-DELETETION type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addDeleteFile method; if the type is neither DATA type nor POS-DELETETION type, throwing an exception that the type is not supported; committing this transaction to complete the data UPDATE operation on the Update Sql task.

[0029] This implements the Update function in the Trino Iceberg connection.

[0030] In summary, the method for implementing the Update function in the Trino Iceberg connection according to an embodiment of the present invention can support the Update function when docking Iceberg data through Trino, allowing users to complete batch running and ad hoc query operations in the same engine, conveniently and efficiently completing more batch processing scenarios, not only reducing the development cost and operation and maintenance cost of the task, but also completing the ETL, modeling and analysis of the data in the OLAP analysis engine, and eliminating the problem of inconsistent data caliber.

[0031] The present invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the method for realizing the Update function in the Trino Iceberg connection is implemented.

[0032] According to the computer device of the embodiment of the present invention, when the computer program stored in the memory is executed by the processor, the Update Sql task submitted by the user is received and parsed through the Trino Engine Sql execution engine to obtain an executable plan, and the Update function logic of Iceberg metadata and underlying data is supported through the UpdatablePageSource software programming interface provided by Trino. According to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg, so that the Update function can be supported when the Iceberg data is connected through Trino, and the user can complete the batch running and ad hoc query logging operations in the same engine, so as to complete more batch processing scenarios conveniently and efficiently, which can not only reduce the development cost and operation and maintenance cost of the task, but also complete the ETL, modeling and analysis of the data in the OLAP analysis engine, and eliminate the problem of inconsistent data caliber.

[0033] In addition, the present invention also proposes a non-transitory computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the method for realizing the Update function in the Trino Iceberg connection is implemented.

[0034] According to the non-temporary computer-readable storage medium of an embodiment of the present invention, when the computer program stored thereon is executed by the processor, the Update Sql task submitted by the user is received and parsed by the Trino Engine Sql execution engine to obtain an executable plan, and the Update function logic of Iceberg metadata and underlying data is supported through the UpdatablePageSource software programming interface provided by Trino. According to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg, so that the Update function can be supported when the Iceberg data is connected through Trino, and the user can complete the batch running and ad hoc query logging operations in the same engine, so as to complete more batch processing scenarios conveniently and efficiently, which can not only reduce the development cost and operation and maintenance cost of the task, but also complete the ETL, modeling and analysis of the data in the OLAP analysis engine, and eliminate the problem of inconsistent data caliber.

[0035] In the description of the present invention, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include one or more of the features. "Multiple" means two or more, unless otherwise clearly and specifically defined.

[0036] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily target the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. In addition, the technical personnel in this field can combine and combine the different embodiments or examples described in this specification and the features of different embodiments or examples without contradiction. In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily target the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. Furthermore, those skilled in the art may combine and associate different embodiments or examples and features of different embodiments or examples described in this specification without mutual contradiction.

[0037] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, segment or portion of code comprising one or more executable instructions for implementing the steps of a custom logical function or process, and the scope of the preferred embodiments of the present invention includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present invention belong.

[0038] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute the instructions), or in combination with these instruction execution systems, devices or apparatuses. For the purpose of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in combination with these instruction execution systems, devices or apparatuses. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium and then editing, interpreting or processing in other suitable ways if necessary, and then stored in a computer memory.

[0039] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0040] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.

[0041] In addition, each functional unit in each embodiment of the present invention may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.

[0042] The storage medium mentioned above may be a read-only memory, a magnetic disk or an optical disk, etc. Although the embodiments of the present invention have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limiting the present invention. A person of ordinary skill in the art may change, modify, replace and modify the above embodiments within the scope of the present invention.

[0043] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A method for implementing the Update function in a Trino Iceberg connection, characterized in that: The following steps are involved: The Trino Engine Sql execution engine receives and parses the Update Sql task submitted by the user to obtain an executable plan; The UpdatablePageSource software programming interface provided by Trino is used to support the Update function logic of Iceberg metadata and underlying data; According to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg; Among them, the Update function logic of Iceberg metadata and underlying data is supported through the UpdatablePageSource software programming interface provided by Trino, which specifically includes: according to the executable plan, the split information of the table involved in the Update Sql task is obtained through the SplitManager software programming interface; an UpdatablePageSource software programming interface is created for each split information, so that Trino Engine obtains the next page of data through the getNextPage method provided by the UpdatablePageSource software programming interface, and performs filtering according to SQL conditions when reading the next page of data and generates a filtered result page; according to the result page, the deleteFile file and the insertFile file of the data are generated according to rowID in the updateRows method through the UpdatablePageSource software programming interface; in the finish method of the UpdateablePageSource software programming interface, the Transaction application programming interface provided by Iceberg is used to record the underlying metadata and add and delete data for the deleteFile file and the insertFile file; when the Trino Engine has processed all the pages of Splits, TrinoEngine calls the finishUpdate method of the ConnectorMetadata software programming interface to submit the transaction; According to the Update function logic, the data UPDATE operation is performed on the Iceberg underlying data through the RowDelta application programming interface provided by Iceberg, specifically including: judging the types of the FileContent of the deleteFile file and the insertFile file; if the type is DATA type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addInsertFile method; if it is POS-DELETETION type, adding the file's location information and data information to the Iceberg table metadata information through the RowDelta.addDeleteFile method; if the type is neither the DATA type nor the POS-DELETETION type, throwing an exception indicating that the type is not supported; committing this transaction to complete the data UPDATE operation on the Update Sql task.

2. The method for implementing the Update function in Trino Iceberg connection according to claim 1, characterized in that: The Trino Engine Sql execution engine receives and parses the Update Sql task submitted by the user to obtain an executable plan, including: Receiving the Update Sql task submitted by the user through the Trino Engine Sql execution engine; The Trino Engine Sql execution engine obtains the Session submitted by the user according to the Update Sql task, and performs authority authentication on the user according to the Session submitted by the user; If the authority authentication is passed, the Update Sql task is parsed using the logical planner to generate an abstract syntax tree, and the Update Sql task is optimized and converted into a logical plan according to the Catalog where the Update Sql task is located, the metadata information of the table involved in the Update Sql task is obtained from the corresponding Connector, and the optimization rules of the syntax optimizer of the Trino Engine; Generate a distributed query plan according to the logical plan through a distributed planner and hand it over to an execution planner; The execution planner converts the distributed query plan into an executable plan, discovers and obtains available nodes through a node manager, and distributes the executable plan to the available nodes for execution.

3. A computer device, characterized in that: The invention comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the program, the method for realizing the Update function in the Trino Iceberg connection according to claim 1 or 2 is realized.

4. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method for realizing the Update function in the Trino Iceberg connection as claimed in claim 1 or 2 is implemented.

Images