Write table engine integration method, medium and equipment

Abstract

The invention discloses a write table engine integration method, a medium and equipment. The write table engine integration method comprises the following steps of: persisting metadata information and configuration of a Kudu table into a metadata directory file of a ClickHouse system through a data definition grammar of the ClickHouse system so as to access the Kudu table through the ClickHouse system; the method comprises the following steps of: analyzing an SQL (Structured Query Language) statement to be executed into an abstract syntax tree by using a ClickHouse-Kuduu-SQL analyzer; 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; and executing the execution task through the Kudu cluster, and returning a result to a coordination node of the ClickHouse system. By means of the mode, real-time writing and query of the ClickHouse system in a large-data-volume scene can be achieved.

Description

Technical Field

[0001] The present application relates to the field of data storage, and in particular to a write table engine integration method, medium and device. Background Art

[0002] Although the existing ClickHouse system supports the processing of big data, it is not very good at real-time writing, especially for high-concurrency real-time writing scenarios, which currently do not have good table engine support. With the growth of business data volume, the business needs of access are also increasing. The existing query engine of ClickHouse is suitable for large-scale data analysis and calculation scenarios, but it cannot meet the real-time and fast query and writing needs of business systems. In order to meet the real-time query and writing needs of business and financial systems, 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 table writing engine integration method, medium and equipment to solve the problem that the ClickHouse system is difficult to write and query in real time in scenarios with large data volumes.

[0004] To solve the above technical problems, a technical solution adopted in the present application is: to provide a table writing engine integration method based on the ClickHouse system, including: through the data definition syntax of the ClickHouse system, the metadata information and configuration of the Kudu table are persisted to the metadata directory file of the ClickHouse system, so as to access the Kudu table through the ClickHouse system; the SQL statement to be executed is parsed into an abstract syntax tree using the ClickHouse-Kudu-SQL parser; the abstract syntax tree is traversed to extract the node information corresponding to each node of the Kudu table, and an execution task is generated based on the node information; the execution task is executed through the Kudu cluster, and the result is returned to the coordination node of the ClickHouse system.

[0005] In some embodiments, before persisting the metadata information and configuration of the Kudu table to the metadata directory file of the ClickHouse system through the data definition language syntax of the ClickHouse system, it also includes: configuring the ClickHouse system storage path 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 the 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 the ClickHouse-Kudu-SQL generator, the physical execution plan including an execution task composed of the Kudu operator.

[0007] In some embodiments, executing the execution task through the Kudu cluster and returning the result to the coordination node of the ClickHouse system includes: submitting 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, returning the execution result of the Kudu cluster to the coordination node of the ClickHouse system.

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

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

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

[0011] In some embodiments, after using the ClickHouse-Kudu-SQL parser to parse the SQL statement to be executed into an abstract syntax tree, it also 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, characterized in that when the program data is executed by a processor, the steps of the write table engine integration method as described above are implemented.

[0013] The present application also provides a computer device, 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 write table engine integration method as described above are implemented.

[0014] The beneficial effects of the present application are as follows: Different from the prior art, the present application discloses a method, medium and device for writing table engine integration. Through 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 that the user can access the Kudu table through the ClickHouse system; the SQL statement to be executed is parsed into an abstract syntax tree using the ClickHouse-Kudu-SQL parser, so that the input SQL statement can be recognized and understood by the system; the abstract syntax tree is traversed to extract the node information corresponding to each node of the Kudu table, and an execution task is generated based on the node information, and the input SQL statement is converted into a task executed by the system for execution; the execution task is executed through the Kudu cluster, and the result is returned to the coordination node of the ClickHouse system, so that the data in the Kudu table can be directly called through the ClickHouse system, combined with the big data processing capability of the ClickHouse system and the efficient real-time writing and updating of the Kudu table, the ClickHouse system is endowed with the ability to access from the ClickHouse end, and write and update a large amount of data in real time. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the prior art descriptions are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative work, among which: Figure 1 It is a flowchart of an embodiment of a write table engine integration method provided by the present application; Figure 2 Yes Figure 1 The flowchart of the embodiment of step 30 of the method is shown; Figure 3 Yes Figure 1 The flowchart of the embodiment of step 40 of the method is shown; Figure 4It is a structural schematic diagram of an embodiment of a storage medium provided by the present application; Figure 5 It is a structural diagram of an embodiment of a computer device provided by the present application. DETAILED DESCRIPTION

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

[0017] The terms "first", "second", "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 number of indicated technical features. Thus, the features defined as "first", "second", "third" can expressly or implicitly include at least one of the features. In the description of the present application, the meaning of "multiple" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but optionally also includes steps or units that are not listed, or optionally also includes other steps or units inherent to these processes, methods, products or devices.

[0018] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

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

[0020] See also Figure 1 , Figure 1 This is a flow chart of an embodiment of a write table engine integration method provided by the present application. The write table engine integration method based on the ClickHouse system includes the following steps: 10: Use the data definition syntax of the ClickHouse system to persist the metadata information and configuration of the Kudu table to the metadata directory file of the ClickHouse system to access the Kudu table through the ClickHouse system.

[0021] Through data synchronization, the data of the Kudu table is synchronized to the ClickHouse table in real time.

[0022] In ClickHouse, Data Definition Language (DDL) syntax is used to define and manage database structures, such as tables, views, indexes, etc. When you create a table using ClickHouse DDL syntax, ClickHouse creates a corresponding .sql file in its metadata directory to store the definition of the table.

[0023] Kudu (Apache Kudu) is a distributed column storage system that supports efficient real-time write and update operations.

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

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

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

[0027] Metadata persistence is a prerequisite for SQL parsing and is used to ensure that ClickHouse can identify and access Kudu tables.

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

[0029] To write SQL in ClickHouse, you first need to use the ClickHouse-Kudu-SQL parser to parse ClickHouse SQL into an abstract syntax tree. You can also directly use the ClickHouse SQL syntax to query, update, delete, and other operations on the data in the Kudu table of the external data source. These operations will be converted into an abstract syntax tree through the parser.

[0030] 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 a SQL query is submitted, ClickHouse's ClickHouse-Kudu-SQL parser will parse the SQL instructions into an abstract syntax tree, and then pass it to the optimizer to optimize the abstract syntax tree to generate an efficient execution plan.

[0031] The abstract syntax tree consists of two parts: CLICKHOUSE_FROM and CLICKHOUSE_INSERT. CLICKHOUSE_FROM represents the syntax tree of the from clause. The CLICKHOUSE_INSERT subtree is the main part of the query, which includes 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. A CLICKHOUSE _TABREF node is generated for each table, a CLICKHOUSE _FROM node is generated for the from keyword, and a CLICKHOUSE _SELEXPR node is generated for each queried field. If a function is used in the queried field, the corresponding node generated is CLICKHOUSE_FUNCTION. A CLICKHOUSE _TABLE_OR_COL node is generated for each used field column. The where keyword generates a CLICKHOUSE _WHERE node. If groupby is used, the corresponding node is CLICKHOUSE _GROUPBY. The limit node generates a CLICKHOUSE_LIMIT node to limit the number of entries.

[0032] Optionally, after step 20, the method 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 correctly written.

[0033] 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.

[0034] Traverse the abstract syntax tree and identify the nodes related to the Kudu table. Then extract the table name, column name, condition and other information from the identified nodes. Based on the extracted information, generate Kudu query or operation tasks.

[0035] Further, see Figure 2, step 30 comprises the following steps: 31: Use the ClickHouse-Kudu-SQL compiler to generate a logical execution plan, which contains multiple operators.

[0036] At this point, the complexity of the abstract syntax tree after traversal and parsing is still very high, because the abstract syntax tree is just some abstract operation instructions, which cannot be directly compiled into the API operator corresponding to Kudu, let alone executed, so further parsing and structuring are required.

[0037] The logical execution plan is composed of API operators, but no information is filled in the operators at this time, and there is no connection between each operator.

[0038] 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 a Kudu operator.

[0039] The logical execution plan is generated by the ClickHouse-Kudu-SQL compiler. When traversing the abstract syntax tree, the information corresponding to each node in the abstract syntax tree is extracted and added to the corresponding operator to form a Kudu operator containing node information.

[0040] 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.

[0041] The logical execution plan only extracts some fragment information of ClickHouse SQL. There is no connection between each operator, and it cannot directly execute 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.

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

[0043] Optionally, before step 33, the method further includes: optimizing the logical execution plan through the ClickHouse-Kudu-SQL optimizer.

[0044] Specifically, it provides suggestions for optimizing the logical execution plan, such as predicate pushdown, partition pruning, field pruning, or column pruning.

[0045] Optionally, generate optimization suggestions as warnings in the ClickHouse system log and terminal.

[0046] After generating the suggestions, they are printed as warnings in the ClickHouse system log and terminal.

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

[0048] Execute SQL queries or operation tasks on the Kudu cluster, obtain and process the result set returned by Kudu, and return the result set to the coordinating node of the ClickHouse system.

[0049] Further, see Figure 3 , step 40 comprises the following steps: 41: Submit the physical execution plan to the ClickHouse-Kudu-SQL executor.

[0050] 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.

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

[0052] The operator is submitted to the corresponding node in the Kudu cluster through the integration platform. After receiving the operator call, the Kudu cluster performs the corresponding data retrieval, insertion, update or deletion operation according to the instructions in the call.

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

[0054] After the Kudu cluster completes the operation, it returns the result data to the ClickHouse-Kudu-SQL executor, which 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, which can store it back in the ClickHouse table for the next query.

[0055] When data needs to be written in real time, the data is first sent to the Kudu cluster. The Kudu cluster receives and stores the data while ensuring high data throughput and random read and write capabilities.

[0056] As time goes by, historical data in Kudu needs to be transferred to ClickHouse for storage. This can be achieved through regular data migration tasks to export and import data from Kudu into ClickHouse.

[0057] For real-time data queries, you can directly access the data in the Kudu cluster through the ClickHouse-Kudu integration layer.

[0058] For historical data queries, you can do it directly in ClickHouse.

[0059] By integrating ClickHouse, which is good at processing large amounts of data queries and analysis, and Kudu, which supports efficient real-time write and update operations, Kudu is integrated into ClickHouse. As the real-time write table engine of lickHouse, Kudu tables can be established and queried in ClickHouse to achieve real-time writing; at the same time, historical data in Kudu is regularly migrated to ClickHouse to meet the query needs of large amounts of data.

[0060] See also Figure 4 , Figure 4 It is a structural diagram of an embodiment of the storage medium provided by the present application.

[0061] The storage medium 300 stores program data 310. When the program data 310 is executed by the processor, the following is achieved: Figure 1 The steps of the described write table engine integration method.

[0062] The program data 310 is stored in a storage medium 300, and includes a number of instructions for enabling a network device (such as a router, a personal computer, a server, etc.) or a processor to execute all or part of the steps of the method described in each embodiment of the present application.

[0063] Optionally, the storage medium 300 may be a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, an optical disk, or other medium that can store program data.

[0064] See also Figure 5 , Figure 5 It is a structural schematic diagram of an embodiment of a computer device provided by the present application.

[0065] The computer device 400 includes a processor 420 and a memory 410 which are connected to each other. The memory 410 stores a computer program. When the processor 420 executes the computer program, the steps of the above-mentioned write table engine integration method are implemented.

[0066] Different from the existing technology, this application integrates ClickHouse and Kudu, directly accesses Kudu tables through ClickHouse, and uses Kudu as the write table engine of ClickHouse, so as to realize the real-time writing and query of large amounts of data in ClickHouse. In this process, all operations can be performed through ClickHouse SQL, and complex calculations including query insertion and deletion can be performed directly through SQL without writing complex Java code, and all of these can be completed in the ClickHouse client page to give users a better display. At the same time, the mode of integrating Kudu into ClickHouse also provides the ability to jointly query massive data. Tables across data sources can be associated and used in ClickHouse, which improves convenience and efficiency.

[0067] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on 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 be referred to the partial description of the method embodiment.

[0068] The present application can be used in many general or special computing system environments or configurations, such as personal computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, network PCs, minicomputers, and distributed computing environments including any of the above systems or devices.

[0069] In the several embodiments provided in this 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 only illustrative, for example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not performed.

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

[0071] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0072] The above descriptions are merely embodiments of the present application and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also 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: Persist the metadata information and configuration of the Kudu table in 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; Use ClickHouse-Kudu-SQL parser to parse the SQL statement to be executed into an abstract syntax tree; 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; The execution task is executed through the Kudu cluster, and the result is returned to the coordination node of the ClickHouse system.

2. The table writing engine integration method according to claim 1, characterized in that: Before persisting the metadata information and configuration of the Kudu table to the metadata directory file of the ClickHouse system through the data definition language syntax of the ClickHouse system, it also includes: Configure the ClickHouse system storage path to generate the metadata directory file.

3. The table writing engine integration method according to claim 1, characterized in that: The 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: 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; The logical execution plan is traversed, and a physical execution plan is generated through the ClickHouse-Kudu-SQL generator, wherein the physical execution plan includes an execution task consisting of the Kudu operator.

4. The table writing engine integration method according to claim 3, 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.

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

6. The table writing engine integration method according to claim 4, 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.

7. The table writing engine integration method according to claim 5, 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.

8. 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.

9. 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-8 are implemented.

10. 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 to 8 are implemented.

Images