A Write Table Engine Integration Method, Medium and Device

Abstract

The present application discloses a method, medium and device for integrating a write table engine. The method for integrating a write table engine includes: persisting the metadata information and configuration of a Kudu table into the metadata directory file of the ClickHouse system through the data definition syntax of the ClickHouse system, so as to access the Kudu table through the ClickHouse system; using a ClickHouse-Kudu-SQL parser to parse an SQL statement to be executed into an abstract syntax tree; traversing the abstract syntax tree to extract the node information corresponding to each node of the Kudu table, and generating an execution task based on the node information; executing the execution task through a Kudu cluster, and returning the result to the coordination node of the ClickHouse system. Through the above method, the present application can achieve real-time writing and querying in a large data volume scenario for the ClickHouse system.

Description

Technical Field

[0001] This application relates to the field of data storage, and particularly to a method, medium, and device for integrating a write table engine. Background Art

[0002] Although the existing ClickHouse system supports the processing of big data, its real-time writing performance is not good. In particular, there is currently no good table engine support for high-concurrency real-time writing scenarios. With the growth of business data volume, the access business requirements are also increasing day by day. The existing query engine of ClickHouse is suitable for large-scale data analysis and calculation scenarios, and cannot well meet the real-time fast query and writing requirements of business systems. To meet the real-time query and writing requirements of the business and finance system, it is necessary to explore distributed high-speed real-time query and real-time writing methods, provide real-time second-level query and writing capabilities, and perform real-time writing and updating of ClickHouse. Summary of the Invention

[0003] This application mainly provides a method, medium, and device for integrating a write table engine to solve the problem that the ClickHouse system is difficult to write and query in real time in scenarios with a large amount of data.

[0004] To solve the above technical problem, a technical solution adopted by this application is: to provide a method for integrating a write table engine based on the ClickHouse system, including: through the data definition syntax of the ClickHouse system, persisting the metadata information and configuration of the Kudu table into the metadata directory file of the ClickHouse system, so as to access the Kudu table through the ClickHouse system; using the ClickHouse-Kudu-SQL parser to parse the SQL statement to be executed into an abstract syntax tree; traversing the abstract syntax tree to extract the node information corresponding to each node of the Kudu table, and generating an execution task based on the node information; executing the execution task through the Kudu cluster and returning the result to the coordination node of the ClickHouse system.

[0005] In some embodiments, before persisting the metadata information and configuration of the Kudu table into the metadata directory file of the ClickHouse system through the data definition language syntax of the ClickHouse system, it further includes: configuring the storage path of the ClickHouse system to generate the metadata directory file.

[0006] In some embodiments, traversing the abstract syntax tree to extract node information corresponding to each node of the Kudu table, and generating an execution task based on the node information includes: using a ClickHouse-Kudu-SQL compiler to generate a logical execution plan, the logical execution plan including multiple operators; traversing the abstract syntax tree to extract node information corresponding to each node of the Kudu table, and adding the node information to the operator corresponding to the node information to form a Kudu operator; traversing the logical execution plan, and generating a physical execution plan through a ClickHouse-Kudu-SQL generator, the physical execution plan including an execution task composed of the Kudu operators.

[0007] In some embodiments, executing the execution task through a Kudu cluster and returning the result to the coordination node of the ClickHouse system includes: submitting the physical execution plan to a ClickHouse-Kudu-SQL executor; the ClickHouse-Kudu-SQL executor submitting the operators in the physical execution plan to the Kudu cluster for execution; in response to the completion of the execution of the physical execution plan, returning the execution result of the Kudu cluster to the coordination node of the ClickHouse system.

[0008] In some embodiments, before generating a physical execution plan by traversing the logical execution plan through a ClickHouse-Kudu-SQL generator, it further includes: optimizing the logical execution plan through a ClickHouse-Kudu-SQL optimizer.

[0009] In some embodiments, optimizing the logical execution plan through a ClickHouse-Kudu-SQL optimizer includes: performing predicate pushdown, partition pruning, field pruning, or column pruning on the logical execution plan.

[0010] In some embodiments, optimizing the logical execution plan through a ClickHouse-Kudu-SQL optimizer further includes: generating optimization suggestions in a warning manner in the log and terminal of the ClickHouse system.

[0011] In some embodiments, after using a ClickHouse-Kudu-SQL parser to parse an SQL statement to be executed into an abstract syntax tree, it further includes: performing a syntax check on the SQL statement, including: determining whether a table exists, whether a field exists, or whether the SQL statement is written correctly.

[0012] To solve the above problems, the present application also provides a storage medium, on which program data is stored, and is characterized in that when the program data is executed by a processor, the steps of the above-mentioned write table engine integration method are implemented.

[0013] The present application also provides a computer device, which is characterized in that it includes a processor and a memory connected to each other, the memory stores a computer program, and when the processor executes the computer program, the steps of the above-mentioned write table engine integration method are implemented.

[0014] The beneficial effects of the present application are as follows: Different from the prior art, the present application discloses a write table engine integration method, medium and device. Through the data definition syntax of the ClickHouse system, the metadata information and configuration of the Kudu table are persisted into the metadata directory file of the ClickHouse system, enabling users to access the Kudu table through the ClickHouse system; using the ClickHouse-Kudu-SQL parser to parse the SQL statement to be executed into an abstract syntax tree, so that the input SQL statement can be recognized and understood by the system; traversing the abstract syntax tree to extract the node information corresponding to each node of the Kudu table, and generating an execution task based on the node information, converting the input SQL statement into a task executed by the system for execution; executing the execution task through the Kudu cluster and returning the result to the coordination node of the ClickHouse system, enabling the data in the Kudu table to be directly called through the ClickHouse system, combining the big data processing ability of the ClickHouse system and the efficient real-time writing and updating of the Kudu table, endowing the ClickHouse system with the ability to access from the ClickHouse side and write and update a large amount of data in real time. Description of the Drawings

[0015] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the following drawings are only some embodiments of the present application. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings, where:

[0016] Figure 1 is a schematic flowchart of an embodiment of the write table engine integration method provided by the present application;

[0017] Figure 2 is as Figure 1 shown in the schematic flowchart of an embodiment of step 30 of the method;

[0018] Figure 3 is as Figure 1Schematic flowchart of a method step 40 in an embodiment;

[0019] Figure 4 Schematic structural diagram of a storage medium provided by the present application in an embodiment;

[0020] Figure 5 Schematic structural diagram of a computer device provided by the present application in an embodiment. Detailed implementation manners

[0021] Next, the technical solutions in the embodiments of the present application will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts belong to the scope of protection of the present application.

[0022] The terms "first", "second", and "third" in the embodiments of the present application are only used for descriptive purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the quantity of the indicated technical features. Thus, the features defined with "first", "second", and "third" may explicitly or implicitly include at least one of such features. In the description of the present application, the meaning of "a plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or device that includes a series of steps or units is not limited to the listed steps or units, but optionally further includes unlisted steps or units, or optionally further includes other steps or units inherent to these processes, methods, products, or devices.

[0023] Referring to "embodiment" herein means that the specific features, structures, or characteristics described in connection with the embodiment may be included in at least one embodiment of the present application. The phrase appears in various places in the specification and does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment mutually exclusive with other embodiments. Those skilled in the art will explicitly and implicitly understand that the embodiments described herein may be combined with other embodiments.

[0024] ClickHouse is an open-source, high-performance, column-oriented database management system for real-time data analysis, used for online analytical processing. It is the fastest OLAP query engine today, with a data processing speed at least 100 times faster than traditional databases. ClickHouse adopts a decentralized architecture and has complete database management functions.

[0025] Refer to Figure 1 , Figure 1It is a schematic flowchart of an embodiment of the write table engine integration method provided by this application. The write table engine integration method based on the ClickHouse system includes the following steps:

[0026] 10: Persist the metadata information and configuration of the Kudu table into the metadata directory file of the ClickHouse system through the data definition syntax of the ClickHouse system, so as to access the Kudu table through the ClickHouse system.

[0027] Synchronize the data of the Kudu table to the ClickHouse table in real time through data synchronization.

[0028] In ClickHouse, the data definition language (DDL) syntax is used to define and manage database structures, such as tables, views, indexes, etc. When creating a table using the ClickHouse DDL syntax, ClickHouse will create a corresponding.sql file in its metadata directory to store the definition of the table.

[0029] Kudu (Apache Kudu) is a distributed columnar storage system that supports efficient real-time writing and updating operations.

[0030] Before step 10, it also includes: configuring the storage path of the ClickHouse system to generate a metadata directory file.

[0031] The storage path of ClickHouse can be configured in the config.xml configuration file. For example, if the configured path is / data / ClickHouse, then the metadata file path for storing the table is / data / ClickHouse / metadata / db / tablename.sql.

[0032] By integrating Kudu into ClickHouse as a real-time write table engine of ClickHouse, Kudu tables can be created and queried in ClickHouse to achieve real-time writing.

[0033] Metadata persistence is a prerequisite for SQL parsing, which is used to ensure that ClickHouse can recognize and access Kudu tables.

[0034] 20: Use the ClickHouse-Kudu-SQL parser to parse the SQL statement to be executed into an abstract syntax tree.

[0035] When writing SQL in ClickHouse, it is first necessary to use the ClickHouse-Kudu-SQL parser to parse ClickHouse SQL into an abstract syntax tree. It is also possible to directly use the syntax of ClickHouse SQL to query, update, delete, etc. the data in the external data source Kudu table. These operations will be converted into an abstract syntax tree by the parser.

[0036] Among them, the ClickHouse-Kudu-SQL parser is used to convert the input SQL instructions into a language that ClickHouse can understand, that is, an abstract syntax tree. In ClickHouse, when submitting an SQL query, the ClickHouse-Kudu-SQL parser of ClickHouse will parse the SQL instructions into an abstract syntax tree, and then pass it to the optimizer to optimize this abstract syntax tree, so as to generate an efficient execution plan.

[0037] The abstract syntax tree consists of two parts: CLICKHOUSE_FROM and CLICKHOUSE_INSERT. CLICKHOUSE_FROM represents the syntax tree of the from clause, and the CLICKHOUSE _INSERT subtree is the main part of the query, including the query result CLICKHOUSE _DESTINATION subtree, the select subquery syntax tree, and the query condition (where, group by, order by, limit, having, etc.) syntax tree. Each table generates a CLICKHOUSE _TABREF node, the from keyword generates a CLICKHOUSE _FROM node, each query field generates a CLICKHOUSE _SELEXPR node, if the query field uses a function, the corresponding generated node is CLICKHOUSE_FUNCTION, each used field column generates a CLICKHOUSE _TABLE_OR_COL node, the where keyword generates a CLICKHOUSE _WHERE node, if it is groupby, it corresponds to a CLICKHOUSE _GROUPBY node, and the limit number of rows generates a CLICKHOUSE_LIMIT node.

[0038] Optionally, after step 20, it also includes: performing a syntax check on the SQL statement, including: determining whether the table exists, whether the field exists, or whether the SQL statement is written correctly.

[0039] 30: Traverse the abstract syntax tree to extract the node information corresponding to each node of the Kudu table, and generate execution tasks based on the node information.

[0040] Traverse the abstract syntax tree to identify the nodes related to the Kudu table. Then extract information such as table names, column names, and conditions from the identified nodes. Based on the extracted information, generate query or operation tasks for Kudu.

[0041] Further, refer to Figure 2 , step 30 includes the following steps:

[0042] 31: Use the ClickHouse-Kudu-SQL compiler to generate a logical execution plan, which contains multiple operators.

[0043] At this time, the complexity of traversing the parsed abstract syntax tree is still high because the abstract syntax tree is only some abstract operation descriptions and cannot be directly compiled into the corresponding api operators of Kudu, let alone executed. Therefore, further parsing and structuring are required.

[0044] The logical execution plan consists of individual api operators, but at this time the operators do not have information filled in, and there is no connection between each operator.

[0045] 32: Traverse the abstract syntax tree to extract the node information corresponding to each node of the Kudu table, and add the node information to the operator corresponding to the node information to form Kudu operators.

[0046] Generate a logical execution plan through the ClickHouse-Kudu-SQL compiler. When traversing the abstract syntax tree, extract the information corresponding to each node in the abstract syntax tree and add it to the corresponding operator to form Kudu operators containing node information.

[0047] 33: Traverse the logical execution plan and generate a physical execution plan through the ClickHouse-Kudu-SQL generator. The physical execution plan includes execution tasks composed of Kudu operators.

[0048] The logical execution plan only extracts some fragmentary information of ClickHouse SQL, and there is no connection between each operator. It cannot be directly executed and needs to generate the corresponding api query task. At this time, it is necessary to traverse the logical execution plan and generate the corresponding physical execution plan through the ClickHouse-Kudu-SQL generator.

[0049] The physical plan is an executable task composed of operators of the Kudu API corresponding to the logical execution plan. For Kudu, this plan is the executable task, which is equivalent to a Java code snippet that manually invokes the Kudu API.

[0050] Optionally, before step 33, it also includes: optimizing the logical execution plan through the ClickHouse-Kudu-SQL optimizer.

[0051] Specifically, suggestions for optimizing the logical execution plan such as predicate pushdown, partition pruning, field pruning, or column pruning are provided.

[0052] Optionally, generate optimization suggestions in the form of warnings in the logs and terminals of the ClickHouse system.

[0053] After generating the suggestions, print them out in the form of warnings in the logs and terminals of the ClickHouse system.

[0054] 40: Execute this execution task through the Kudu cluster and return the result to the coordination node of the ClickHouse system.

[0055] Execute the SQL query or operation task on the Kudu cluster, obtain and process the result set returned by Kudu, and return the result set to the coordination node of the ClickHouse system.

[0056] Furthermore, refer to Figure 3 For step 40, it includes the following steps:

[0057] 41: Submit the physical execution plan to the ClickHouse-Kudu-SQL executor.

[0058] The ClickHouse-Kudu-SQL executor receives the submitted physical execution plan, understands the Kudu-related operations in the physical execution plan, and converts them into Kudu operator calls.

[0059] 42: The ClickHouse-Kudu-SQL executor submits the operators in the physical execution plan to the Kudu cluster for execution.

[0060] The operators are submitted to the corresponding nodes in the Kudu cluster through the integration platform. After receiving the operator calls, the Kudu cluster executes the corresponding data retrieval, insertion, update, or deletion operations according to the instructions in the calls.

[0061] 43: In response to the completion of the execution of the physical execution plan, return the execution result of the Kudu cluster to the coordination node of the ClickHouse system.

[0062] After the Kudu cluster finishes an operation, it returns the result data to the ClickHouse-Kudu-SQL executor. The ClickHouse-Kudu-SQL executor may need to further process these results, such as format conversion, data aggregation, etc., to meet the subsequent processing requirements of ClickHouse. Finally, the processed result data is returned to the ClickHouse coordination node, and the coordination node can store it back into the ClickHouse table for the next query.

[0063] When real-time data writing is required, the data is first sent to the Kudu cluster. The Kudu cluster receives and stores the data while ensuring high throughput and random read / write capabilities of the data.

[0064] Over time, the historical data in Kudu needs to be transferred to ClickHouse for storage. This can be achieved through regular data migration tasks, where the data in Kudu is exported and imported into ClickHouse.

[0065] For real-time data queries, the data in the Kudu cluster can be directly accessed through the ClickHouse-Kudu integration layer.

[0066] For historical data queries, they can be directly performed in ClickHouse.

[0067] By integrating Kudu, which is good at handling large-scale data queries and analysis in ClickHouse and supports efficient real-time writing and update operations, into ClickHouse as the real-time writing table engine of ClickHouse, Kudu tables can be created and queried in ClickHouse to achieve real-time writing; at the same time, the historical data in Kudu is regularly migrated to ClickHouse to meet the large-scale data query requirements.

[0068] See Figure 4 , Figure 4 is a schematic structural diagram of an embodiment of the storage medium provided by this application.

[0069] The storage medium 300 stores program data 310, and when the program data 310 is executed by a processor, it implements the steps of the writing table engine integration method as described in Figure 1 .

[0070] The program data 310 is stored in a storage medium 300 and includes several instructions for causing a network device (which can be a router, a personal computer, a server, etc.) or a processor to execute all or part of the steps of the methods described in various embodiments of this application.

[0071] Optionally, the storage medium 300 may be various media capable of storing program data, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disc.

[0072] Refer to Figure 5 , Figure 5 which is a schematic structural diagram of an embodiment of the computer device provided by this application.

[0073] The computer device 400 includes a processor 420 and a memory 410 that are interconnected. The memory 410 stores a computer program. When the processor 420 executes the computer program, the steps of the write table engine integration method as described above are implemented.

[0074] Different from the prior art, this application integrates ClickHouse and Kudu, directly accesses the Kudu table through ClickHouse, and uses Kudu as the write table engine of ClickHouse, realizing the real-time writing and querying of a large amount of data in ClickHouse. In this process, all operations can be performed through ClickHouse SQL, and complex calculations including querying, inserting, and deleting can be directly performed through SQL, without writing complex Java code, and all of these can be completed on the ClickHouse client page, providing a better display for users. At the same time, the mode of integrating Kudu into ClickHouse also provides the ability to perform joint queries on massive data, and tables across data sources can be associated and queried in ClickHouse, improving convenience and efficiency.

[0075] Each embodiment in this specification is described in a progressive manner. The same or similar parts among the embodiments can be referred to each other, and the key points of each embodiment are the differences from other embodiments. In particular, for the storage medium embodiment and the computer device embodiment, since they are basically similar to the method embodiment, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiment.

[0076] This application can be used in many general-purpose or special-purpose computing system environments or configurations. For example: personal computers, handheld devices or portable devices, tablet devices, multi-processor systems, microprocessor-based systems, network PCs, small computers, distributed computing environments including any of the above systems or devices, and so on.

[0077] In several embodiments provided in the present application, it should be understood that the disclosed methods and devices can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed.

[0078] The units described as separate components may or may not be physically separated. The components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0079] In addition, each functional unit in various embodiments of the present application can be integrated in a processing unit, or each unit can exist physically alone, or two or more units can be integrated in one unit. The above integrated units can be implemented in the form of hardware or in the form of software functional units.

[0080] The above are only the embodiments of the present application, and do not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made by using the content of the specification and drawings of the present application, or directly or indirectly applied in other related technical fields, shall be equally included in the patent protection scope of the present application.

Claims

1. A write table engine integration method based on ClickHouse system, characterized in that: include: Configure the ClickHouse system storage path to generate a metadata directory file; By using the data definition syntax of the ClickHouse system, the metadata information and configuration of the Kudu table are persisted in the metadata directory file of the ClickHouse system, so as to access the Kudu table through the ClickHouse system, and by integrating Kudu into ClickHouse as a real-time table writing engine of ClickHouse, the Kudu table is established and queried in ClickHouse; Use ClickHouse-Kudu-SQL parser to parse the SQL statements to be executed in ClickHouse into abstract syntax trees; Use the ClickHouse-Kudu-SQL compiler to generate a logical execution plan, where the logical execution plan contains multiple operators; Traversing the abstract syntax tree to extract node information corresponding to each node of the Kudu table, and adding the node information to the operator corresponding to the node information to form a Kudu operator; Traversing the logical execution plan, and generating a physical execution plan through the ClickHouse-Kudu-SQL generator, wherein the physical execution plan includes an execution task consisting of the Kudu operator; Execute the execution task through the Kudu cluster and return the result to the coordination node of the ClickHouse system; The Kudu table is used to support real-time writing, querying, and data updating of the ClickHouse system.

2. The table writing engine integration method according to claim 1, characterized in that: Executing the execution task through the Kudu cluster and returning the result to the coordination node of the ClickHouse system includes: Submit the physical execution plan to the ClickHouse-Kudu-SQL executor; The ClickHouse-Kudu-SQL executor submits the operators in the physical execution plan to the Kudu cluster for execution; In response to the completion of the execution of the physical execution plan, the execution result of the Kudu cluster is returned to the coordinating node of the ClickHouse system.

3. The table writing engine integration method according to claim 1, characterized in that: Before traversing the logical execution plan and generating a physical execution plan through the ClickHouse-Kudu-SQL generator, it also includes: The logical execution plan is optimized through the ClickHouse-Kudu-SQL optimizer.

4. The table writing engine integration method according to claim 3, characterized in that: Optimizing the logical execution plan by the ClickHouse-Kudu-SQL optimizer includes: Predicate pushdown, partition pruning, field pruning, or column pruning is performed on the logical execution plan.

5. The table writing engine integration method according to claim 3, characterized in that: The optimizing the logical execution plan by the ClickHouse-Kudu-SQL optimizer further includes: Generates optimization suggestions in the form of warnings in the log and terminal of the ClickHouse system.

6. The table writing engine integration method according to claim 1, characterized in that: After the ClickHouse-Kudu-SQL parser is used to parse the SQL statement to be executed into an abstract syntax tree, it also includes: Performing a syntax check on the SQL statement includes: determining whether a table exists, whether a field exists, or whether the SQL statement is correctly written.

7. A storage medium having program data stored thereon, characterized in that: When the program data is executed by a processor, the steps of the write table engine integration method as described in any one of claims 1-6 are implemented.

8. A computer device, characterized in that: It comprises a processor and a memory connected to each other, the memory stores a computer program, and when the processor executes the computer program, the steps of the write table engine integration method as described in any one of claims 1-6 are implemented.

Images