A clickhouse-based data analysis method, device and system

Abstract

The application discloses a data analysis method, device and system based on Clickhouse, and belongs to the field of data analysis; Data data containing a node edge relationship list and a node list are obtained according to selection and operation selection of a data set; a ClickHouse core parser is used to perform node relationship analysis based on the node edge relationship list and the node list in the Data data, so that a directed acyclic graph is obtained; then, SQL assembly is performed on the directed acyclic graph to obtain target SQL; finally, the target SQL is automatically executed to obtain target data. When ClickHouse is selected as an underlying (OLAP) to develop data analysis services, the data throughput capacity is large, a directed acyclic graph is obtained according to the node edge relationship list and the node list in the Data data, and then the directed acyclic graph is automatically converted into ClickHouse executable target SQL, so that data analysis personnel do not need to write SQL; the requirement for business analysis personnel is greatly reduced, the data analysis cost is low, only the selection of a data set and the operation selection of the data set are needed, and the analysis mode is simple.

Description

Technical Field

[0001] This invention relates to the field of data analysis, and in particular to a data analysis method, apparatus, and system based on Clickhouse. Background Technology

[0002] In the field of big data analytics, traditional big data analysis requires different frameworks and technology combinations to achieve the final results. The high costs of human resources, technical capabilities, hardware, and maintenance make big data analysis an expensive endeavor.

[0003] While other systems can store different columns separately, their optimizations for specific scenarios render them ineffective for handling analytical queries. Examples include HBase, BigTable, Cassandra, and HyperTable. These systems can achieve throughput of hundreds of thousands of rows per second, but in some cases, users require throughput of hundreds of millions of rows per second. Therefore, current data analysis speeds cannot meet user needs. Furthermore, data analysis is primarily geared towards business analysts, and current methods require them to manually write SQL statements. The high complexity of writing these statements places high demands on business analysts, resulting in high costs and complex analysis methods. Summary of the Invention

[0004] To overcome the shortcomings of existing technologies, this invention provides a data analysis method, apparatus, and system based on Clickhouse, which solves the problems that existing data analysis speed cannot meet user needs and that it places high demands on business analysts, resulting in high usage costs and complex analysis methods.

[0005] The technical solution adopted by this invention to solve its technical problem is:

[0006] Firstly, a data analysis method based on Clickhouse is provided, including the following steps:

[0007] Receive the user's selection of a dataset and the operation selection of the dataset to obtain Data, which includes a list of node edge relationships and a list of nodes;

[0008] The ClickHouse core parser is used to parse node relationships based on the node edge relationship list and node list in the Data data, resulting in a directed acyclic graph;

[0009] The target SQL is obtained by assembling the directed acyclic graph using the ClickHouse SQL specification and functions.

[0010] Executing the target SQL yields the target data used for data analysis.

[0011] Further, receiving the user's selection of a dataset and the operation selection on the dataset to obtain Data includes:

[0012] The operation and the corresponding dataset are used as nodes to obtain a node list;

[0013] Set an edge between any two nodes corresponding to any two association operations to obtain a list of node edge relationships;

[0014] The list of nodes and the list of node relationships are used as the Data.

[0015] Furthermore, the step of using the ClickHouse core parser to parse node relationships based on the node-edge relationship list and node list in the Data data to obtain a directed acyclic graph includes:

[0016] Obtain the order of nodes based on the node edge relationship list and the node list;

[0017] Connecting nodes with edges in sequence yields a directed acyclic graph.

[0018] Furthermore, the step of combining the ClickHouse SQL specification and functions to assemble the directed acyclic graph into the target SQL includes:

[0019] According to the ClickHouse SQL specification and functions, a fixed SQL template is pre-set for each operation. The SQL template is a statement that can be executed in ClickHouse.

[0020] The SQL statement for each node is obtained by associating the dataset corresponding to the operation in each node of the directed acyclic graph with the SQL template.

[0021] The target SQL is obtained by relating the SQL statement of each node to the directed acyclic graph.

[0022] Further, obtaining the target SQL based on the SQL statement of each node and the directed acyclic graph includes:

[0023] The directed acyclic graph (DAG) is traversed using a depth-first search algorithm. Simultaneously, a stack and a HashMap are used to store and parse the nodes and edges of the DAG to obtain the target SQL.

[0024] Furthermore, the method employs a depth-first traversal algorithm to perform graph traversal on the directed acyclic graph (DAG), and simultaneously uses a stack and a HashMap to store and parse the nodes and edges of the DAG to obtain the target SQL, including:

[0025] Generate a stack for storing nodes traversed in the directed acyclic graph, a HashMap for storing the association relationships resolved by edge traversal, and a LIST for temporarily storing data popped from the stack based on the directed acyclic graph.

[0026] When a JOIN NODE is encountered, the graph traversal is stopped, a mark is made, the NODE is popped from the stack and put into the LIST, until the stack is empty or a SUB SELECT subquery is encountered, then the popping stops, so as to obtain a branch of the directed acyclic graph.

[0027] Traverse the LIST from back to front, and combine the SQL statement of each node in the branch to obtain the SQL statement of a branch;

[0028] Recursively retrieve the previous node of the marked JOIN node from the HashMap up to the unrelated key to obtain the starting node of the adjacent branch;

[0029] Start a depth-first traversal of the new branch until the currently marked JOIN node is reached, and generate the SQL statement for the new branch;

[0030] Join the SQL statements that link all branches associated with JOIN NODE;

[0031] Starting from the marked JOIN NODE, perform a depth-first traversal of subsequent nodes until the entire graph has been traversed, and obtain the target SQL.

[0032] Further, the step of generating a stack for storing nodes traversed in the directed acyclic graph and a HashMap for storing the association relationships resolved from edge traversal based on the directed acyclic graph includes:

[0033] Nodes are pushed onto the stack in traversal order, and the node IDs of the FROM and END nodes are parsed and stored as key-value pairs in a HashMap.

[0034] Furthermore, the operation selection includes join queries, filtering, group by aggregation, and sorting.

[0035] Secondly, a data analysis device based on Clickhouse is provided, including:

[0036] The data acquisition module is used to receive the user's selection of a dataset and the operation selection of the dataset to obtain Data, which includes a list of node edge relationships and a list of nodes;

[0037] The node relationship parsing module is used to parse node relationships based on the node edge relationship list and node list in the Data data using the ClickHouse core parser, and obtain a directed acyclic graph.

[0038] The target SQL acquisition module is used to combine the ClickHouse SQL specification and functions to assemble the SQL into the directed acyclic graph to obtain the target SQL.

[0039] The target data acquisition module is used to execute the target SQL to obtain target data for data analysis.

[0040] Thirdly, a data analysis system based on Clickhouse is provided, including:

[0041] processor;

[0042] Memory used to store the processor's executable instructions;

[0043] The processor is configured to perform the method described in any one of the first aspects of the technical solution.

[0044] Beneficial effects:

[0045] This application provides a data analysis method, apparatus, and system based on ClickHouse. First, it receives the user's selection of a dataset and the operations to be performed on that dataset. These selections determine how to obtain the target data for data analysis. Based on the dataset and operation selections, it generates "Data" containing a list of node-edge relationships and a list of nodes. Then, the ClickHouse core parser parses the node relationships based on the list of node-edge relationships and the list of nodes in the "Data" data, resulting in a directed acyclic graph (DAG). Next, it combines ClickHouse SQL specifications and functions to assemble the DAG into the target SQL. Finally, it automatically executes the target SQL to obtain the target data. When using ClickHouse as the underlying OLAP (Online Analytical Processing) service, it offers high data throughput. The DAG is obtained from the list of node-edge relationships and the list of nodes in the "Data" data, and then the DAG is automatically generated into executable target SQL using ClickHouse, eliminating the need for data analysts to write SQL. This significantly reduces the requirements for business analysts, resulting in low data analysis costs; it only requires the user to select a dataset and perform the dataset selection operation, simplifying the analysis method. Attached Figure Description

[0046] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0047] Figure 1 This is a flowchart of a data analysis method based on Clickhouse provided in an embodiment of the present invention;

[0048] Figure 2 This is a schematic diagram of graph traversal of a directed acyclic graph provided in an embodiment of the present invention;

[0049] Figure 3 This is a schematic diagram of a data analysis device based on Clickhouse provided in an embodiment of the present invention. Detailed Implementation

[0050] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of the present invention will be described in detail below with reference to the accompanying drawings and embodiments. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments in this application, all other implementation methods obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0051] The first embodiment, referred to Figure 1 This invention provides a data analysis method based on Clickhouse, comprising the following steps:

[0052] S11: Receive the user's selection of the dataset and the operation selection of the dataset to obtain the Data, which includes a list of node edge relationships and a list of nodes;

[0053] S12: The ClickHouse core parser is used to parse the node relationships based on the node edge relationship list and node list in the Data data to obtain a directed acyclic graph;

[0054] S13: Combine ClickHouse SQL specifications and functions to assemble SQL from a directed acyclic graph to obtain the target SQL;

[0055] S14: Execute the target SQL to obtain the target data for data analysis.

[0056] The ClickHouse-based data analysis method provided in this invention first receives the user's selection of a dataset and the selected operations for that dataset. These selections determine how to obtain the target data for data analysis. Based on the dataset and operation selections, a list of nodes and edges containing a list of nodes and edges is obtained. Then, the ClickHouse core parser uses this list to parse the node relationships, resulting in a directed acyclic graph (DAG). Next, the DAG is assembled using ClickHouse SQL specifications and functions to obtain the target SQL. Finally, the target SQL is automatically executed to obtain the target data. When using ClickHouse as the underlying OLAP (Online Analytical Processing) service, it offers high data throughput. The DAG is automatically generated from the list of nodes and edges in the dataset, eliminating the need for data analysts to write SQL. This significantly reduces the requirements for business analysts, resulting in low data analysis costs. Users only need to select a dataset, making the analysis process simple.

[0057] In the second embodiment, as a supplement to the first embodiment, the present invention provides a specific data analysis method based on Clickhouse. It should be noted that Clickhouse is selected as the underlying layer (OLAP) in this embodiment. The specific method includes the following steps:

[0058] The system receives the user's selection of a dataset and the chosen operations for that dataset to generate "Data". This Data includes a list of node-edge relationships and a list of nodes. Specifically, a web page is provided for rule configuration, allowing data analysts (users) to select the corresponding dataset and perform operations on it. Operations include, but are not limited to, join queries, filtering, group by aggregation, and sorting. A visual selection interface is provided for user convenience. In some optional embodiments, the user can output the name of the selected dataset and the operations to be performed on that dataset. The system retrieves the user's dataset selection and operation selection based on their input. Upon receiving the dataset and operation selections, the system treats the operations and their corresponding datasets as nodes to generate a list of nodes. Edges are then established between any two nodes corresponding to any two join operations to generate a list of node-edge relationships. This list of nodes and node relationships is then used as the Data. The user's intent is represented in the node and node relationship lists to facilitate subsequent transformations and automatic SQL generation.

[0059] The ClickHouse core parser is used to parse node relationships based on the node-edge relationship list and node list in the Data, resulting in a directed acyclic graph (DAG). Specifically, the order of nodes is obtained according to the node-edge relationship list and node list; nodes with edges are connected sequentially to form the DAG. In other words, the step-by-step graph for generating the target data is the DAG, where each node corresponds to one operation.

[0060] This method combines ClickHouse SQL specifications and functions to assemble SQL from a directed acyclic graph (DAG) to obtain the target SQL. Specifically, based on the ClickHouse SQL specifications and functions, a fixed SQL template is pre-set for each operation. The SQL template is a statement that can be executed in ClickHouse. The dataset corresponding to the operation in each node of the DAG is associated with the SQL template to obtain the SQL statement for each node. The target SQL is obtained by combining the SQL statement of each node with the DAG. In other words, each node's operation is treated as an operator, with its SQL template pre-set. The SQL template is automatically improved based on the node relationships, allowing the target SQL to be automatically obtained without data analysts writing code. Furthermore, when a new operation needs to be added, only the SQL template for that operation needs to be pre-written, so that it can be directly called when needed, without the need for coding the entire operation process. The DAG includes nodes and edges; the graph's data structure is defined as nodes (NODE) ​​and edges (EDGE). A depth-first traversal algorithm is used to traverse the DAG, while a stack and a HashMap are used to store and parse the nodes and edges of the DAG to generate the target SQL. Specifically, as shown... Figure 2 As shown:

[0061] a: The traversal order of the DAG (Directed Acyclic Graph) is as follows: Figure 2 The sequence number (downward arrow + number) is used. The depth-first traversal algorithm is adopted. When a JOIN NODE is encountered, the current depth-first traversal is stopped, a mark is made, and the depth-first traversal of the next branch associated with the JOIN NODE begins.

[0062] EDGE also acts as a virtual node in the graph, participating in traversal. The storage data structures involve three types: stack – storing the NODE nodes for graph traversal; HashMap – storing the relationships resolved during EDGE node traversal; and List – temporarily storing data popped from the stack by NODE nodes.

[0063] b: Graph traversal. Ordinary NODE nodes are directly pushed onto the stack in traversal order. At the same time, EDGE nodes parse the node IDs of FROM and END nodes and store them as key-value pairs in a HashMap.

[0064] c: When encountering a JOIN NODE (at this point, the traversal order is...) Figure 2 (In the sequence 1, 2, 3), stop graph traversal and mark it. Start popping NODE from the stack and putting it into LIST until the stack is empty or a SUB SELECT subquery is encountered, then stop popping from the stack to obtain a branch of the directed acyclic graph.

[0065] d: Traverse the LIST from back to front, and combine the SQL statement of each node in the branch to obtain the SQL statement of a branch. For example, SELECT NODE is transformed into SELECT columes FROM catalog.schema.table (columes and catalog.schema.table are used as attributes of SELECT NODE). When encountering FILTER NODE, WHERE columes = condition (columes operator condition is used as an attribute of FILTER NODE).

[0066] e: Recursively retrieve the previous node of the currently marked JOIN node from the EDGE node in the HashMap until there is no associated key, thus determining the starting node of the adjacent branch. Then, begin a depth-first traversal of the new branch until the currently marked JOIN node is reached (at this point, the traversal order is...). Figure 2 Repeat steps (c), (d), and (e) to generate the select clause for a new branch (items 4, 5, and 6).

[0067] f: Process JOIN NODE, connecting the SQL statements of the branches associated with JOIN NODE using JOIN (obtaining the attributes of the JOINNODE node as the join direction and join condition).

[0068] Starting from the marked JOIN NODE, perform a depth-first traversal of subsequent nodes (corresponding to...). Figure 2 If a new JOIN NODE is encountered (in steps 7 and 8), repeat steps (d), (e), and (f). Continue until the entire graph has been traversed and the target SQL is generated.

[0069] Execute the target SQL to obtain the target data for data analysis.

[0070] Existing methods for data analysis first require filtering to obtain target data. Due to the large volume of data, the data source may differ each time, and filtering conditions or data operations may vary. Therefore, after obtaining the user's dataset selection and operation choices, the SQL statement needs to be rewritten. The solution in this invention, however, generates "Data" after obtaining the user's dataset selection and operation choices. The process of obtaining the target data is divided into nodes, and the relationships between these nodes are represented by a directed acyclic graph (DAG). Parsing this DAG automatically generates the target SQL statement; no manual writing is required. The entire process is divided into individual steps through nodes. During development, only an SQL template needs to be set for each node. Even if the operation selection differs each time data analysis is performed, the corresponding operation can be selected based on the selected operation. This quickly solves the problems of hard-coded rules and SQL, decoupling business and technical aspects. Furthermore, in subsequent development iterations, new rules can be added to the business; only the corresponding parsing operators need to be extended. This allows for rapid adaptation to diverse business needs and, combined with a high-performance OLAP database, provides high efficiency for the data analysis system.

[0071] The specific ClickHouse-based data analysis method provided in this invention first receives the user's selection of a dataset and the selected operations for that dataset. These selections determine how to obtain the target data for data analysis. Based on the dataset and operation selections, a list of nodes and edges containing node-edge relationships and a node list are obtained. Then, the ClickHouse core parser uses the node-edge relationship list and node list in the Data data to parse the node relationships, resulting in a directed acyclic graph (DAG). Next, the DAG is assembled using ClickHouse SQL specifications and functions to obtain the target SQL. Finally, the target SQL is automatically executed to obtain the target data. When using ClickHouse as the underlying OLAP (Online Analytical Processing) service, it offers high data throughput. The DAG is obtained from the node-edge relationship list and node list in the Data data, and then the DAG is automatically generated into a ClickHouse-executable target SQL, eliminating the need for data analysts to write SQL. This significantly reduces the requirements for business analysts, resulting in low data analysis costs; only user selection of a dataset and the corresponding operation are required, simplifying the analysis process.

[0072] In a third embodiment, the present invention provides a data analysis device based on Clickhouse, such as... Figure 3 As shown, it includes:

[0073] The data acquisition module 31 receives the user's selection of a dataset and the selected operations on that dataset to obtain Data, which includes a list of node-edge relationships and a list of nodes. Specifically, the data acquisition module 31 uses the operations and their corresponding datasets as nodes to obtain a list of nodes; it sets edges between any two nodes corresponding to any two related operations to obtain a list of node-edge relationships; and it uses the list of nodes and the list of node relationships as Data. Operation selections include join queries, filtering, group by aggregation, and sorting.

[0074] The node relationship parsing module 32 is used to parse the node relationships based on the node edge relationship list and node list in the Data data using the ClickHouse core parser to obtain a directed acyclic graph. Specifically, the node relationship parsing module 32 obtains the order of each node according to the node edge relationship list and node list; and connects the nodes with edges in order to obtain a directed acyclic graph.

[0075] The target SQL acquisition module 33 is used to assemble the target SQL from the directed acyclic graph (DAG) by combining the ClickHouse SQL specification and functions. Specifically, the target SQL acquisition module 33 pre-sets a fixed SQL template for each operation according to the ClickHouse SQL specification and functions. The SQL template is a statement that can be executed in ClickHouse. It establishes an association between the dataset corresponding to the operation in each node of the DAG and the SQL template to obtain the SQL statement for each node. The target SQL is obtained by combining the SQL statement of each node with the DAG. Further, a depth-first traversal algorithm is used to traverse the DAG, and a stack and a HashMap are used to store and parse the nodes and edges of the DAG to obtain the target SQL.

[0076] Specifically, a depth-first traversal algorithm is used to traverse the directed acyclic graph (DAG), while a stack and a HashMap are used to store and parse the nodes and edges of the DAG to obtain the target SQL, including:

[0077] Generate a stack to store nodes for graph traversal, a HashMap to store the association relationships for edge traversal parsing, and a LIST to temporarily store data popped from the stack based on the directed acyclic graph.

[0078] When a JOIN node is encountered, the graph traversal stops, and a mark is made. The node is popped from the stack and placed into the LIST. This process continues until the stack is empty or a SUB SELECT subquery is encountered, at which point the popping stops, resulting in a branch of the directed acyclic graph. The LIST is traversed from back to front, and the SQL statement of each node in the branch is combined to obtain the SQL statement for that branch. The previous node of the marked JOIN node is recursively retrieved from the HashMap until an unrelated key is found, thus determining the starting node of the adjacent branch. A depth-first traversal of the new branch begins until the currently marked JOIN node is reached, generating the SQL statement for the new branch. The SQL statements of all branches associated with the JOIN node are joined together using the JOIN operator. Starting from the marked JOIN node, a depth-first traversal of the subsequent nodes is performed until the entire graph traversal is complete, yielding the target SQL. The process of generating a stack for storing nodes traversed in a directed acyclic graph and a HashMap for storing the association relationships of edge traversal parsing based on the directed acyclic graph includes: pushing nodes onto the stack in traversal order, and storing the node IDs of edge parsing FROM and END nodes as key-value pairs in the HashMap.

[0079] The target data acquisition module 34 is used to execute target SQL to obtain target data for data analysis.

[0080] The ClickHouse-based data analysis device provided in this embodiment of the invention includes a data acquisition module that receives the user's selection of a dataset and the selection of operations on the dataset to obtain Data, which includes a list of node-edge relationships and a list of nodes. A node relationship parsing module uses the ClickHouse core parser to parse the node relationships based on the list of node-edge relationships and the list of nodes in the Data to obtain a directed acyclic graph (DAG). A target SQL acquisition module combines the ClickHouse SQL specification and functions to assemble the DAG into target SQL. A target data acquisition module executes the target SQL to obtain target data for data analysis. The analysis device provided in this embodiment of the invention first receives the user's selection of a dataset and the selection of operations on the dataset. The selection of the dataset and the selection of operations determine how to obtain target data for data analysis. Based on the selection of the dataset and the selection of operations, Data containing a list of node-edge relationships and a list of nodes is obtained. Then, the ClickHouse core parser is used to parse the node relationships based on the list of node-edge relationships and the list of nodes in the Data to obtain a DAG. Next, the ClickHouse SQL specification and functions are combined to assemble the DAG into target SQL. Finally, the target SQL is automatically executed to obtain target data. When using ClickHouse as the underlying OLAP platform for data analysis services, it offers high data throughput. It generates a directed acyclic graph (DAG) based on the node-edge relationship list and node list in the data, and then automatically generates executable SQL statements for ClickHouse, eliminating the need for data analysts to write SQL. This significantly reduces the demands on business analysts, resulting in low data analysis costs. Users only need to select a dataset, making the analysis process simple.

[0081] Fourth embodiment: The present invention provides a data analysis system based on Clickhouse, comprising:

[0082] processor;

[0083] Memory used to store processor-executable instructions;

[0084] The processor is configured to execute the Clickhouse-based data analysis method provided in the first or second embodiment.

[0085] The ClickHouse-based data analysis system provided in this invention stores the processor's executable instructions in memory. When these instructions are executed, the processor first receives the user's selection of a dataset and the corresponding operation. This selection determines how to obtain the target data for data analysis. Based on the dataset and operation selections, it generates "Data" containing a list of node-edge relationships and a list of nodes. Then, the ClickHouse core parser parses the node relationships in the "Data" to obtain a directed acyclic graph (DAG). Next, it combines ClickHouse SQL specifications and functions to assemble the DAG into the target SQL. Finally, it automatically executes the target SQL to obtain the target data. When using ClickHouse as the underlying OLAP (Online Analytical Processing) service, it offers high data throughput. The DAG is automatically generated from the list of node-edge relationships and the node list in the "Data," eliminating the need for data analysts to write SQL. This significantly reduces the requirements for business analysts, resulting in low data analysis costs. Users only need to select a dataset, simplifying the analysis process.

[0086] It is understood that the same or similar parts in the above embodiments can be referred to each other, and the contents not described in detail in some embodiments can be referred to the same or similar contents in other embodiments.

[0087] It should be noted that in the description of this application, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Furthermore, in the description of this application, unless otherwise stated, "a plurality of" means at least two.

[0088] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0089] Although embodiments of this application have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting this application. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of this application.

Claims

1. A data analysis method based on ClickHouse, characterized in that, Includes the following steps: Receive the user's selection of a dataset and the operation selection of the dataset to obtain Data, which includes a list of node edge relationships and a list of nodes; The ClickHouse core parser is used to parse the node relationships based on the node edge relationship list and the node list in the Data data to obtain a directed acyclic graph. This includes: obtaining the order of each node according to the node edge relationship list and the node list; and connecting the nodes with edges in the order to obtain the directed acyclic graph. The target SQL is obtained by assembling the directed acyclic graph (DAG) using ClickHouse SQL specifications and functions, including: pre-setting a fixed SQL template for each operation according to ClickHouse SQL specifications and functions, wherein the SQL template is a statement that can be executed in ClickHouse; establishing an association between the dataset corresponding to the operation in each node of the DAG and the SQL template to obtain the SQL statement for each node; and obtaining the target SQL by combining the SQL statement of each node with the DAG. Executing the target SQL yields the target data used for data analysis.

2. The method according to claim 1, characterized in that: The process of receiving the user's selection of a dataset and the operation selection on the dataset to obtain the Data includes: The operation and the corresponding dataset are used as nodes to obtain a node list; Set an edge between any two nodes corresponding to any two association operations to obtain a list of node edge relationships; The list of nodes and the list of node edge relationships are used as the Data.

3. The method according to claim 1, characterized in that: The step of obtaining the target SQL based on the SQL statement of each node and the directed acyclic graph includes: The directed acyclic graph (DAG) is traversed using a depth-first search algorithm. Simultaneously, a stack and a HashMap are used to store and parse the nodes and edges of the DAG to obtain the target SQL.

4. The method according to claim 3, characterized in that: The method employs a depth-first traversal algorithm to perform graph traversal on the directed acyclic graph (DAG), and simultaneously uses a stack and a HashMap to store and parse the nodes and edges of the DAG to obtain the target SQL, including: Generate a stack for storing nodes traversed in the directed acyclic graph, a HashMap for storing the association relationships resolved by edge traversal, and a LIST for temporarily storing data popped from the stack based on the directed acyclic graph. When a JOIN NODE is encountered, the graph traversal is stopped, a mark is made, the NODE is popped from the stack and put into the LIST, until the stack is empty or a SUB SELECT subquery is encountered, then the popping stops, so as to obtain a branch of the directed acyclic graph. Traverse the LIST from back to front, and combine the SQL statement of each node in the branch to obtain the SQL statement of a branch; Recursively retrieve the previous node of the marked JOIN node from the HashMap up to the unrelated key to obtain the starting node of the adjacent branch; Begin a depth-first traversal of the new branch until the currently marked JOIN node is reached, generating the SQL statement for the new branch; Join the SQL statements of all branches associated with JOIN NODE; Starting from the marked JOIN NODE, perform a depth-first traversal of subsequent nodes until the entire graph has been traversed, and obtain the target SQL.

5. The method according to claim 4, characterized in that: The step of generating a stack for storing nodes traversed in the directed acyclic graph and a HashMap for storing the association relationships resolved by edge traversal based on the directed acyclic graph includes: Nodes are pushed onto the stack in traversal order, and the node IDs of the FROM and END nodes are parsed and stored as key-value pairs in a HashMap.

6. The method according to claim 1, characterized in that: The operation options include join queries, filtering, groupby aggregation, and sorting.

7. A data analysis device based on Clickhouse, characterized in that, include: The data acquisition module is used to receive the user's selection of a dataset and the operation selection of the dataset to obtain Data, which includes a list of node edge relationships and a list of nodes; The node relationship parsing module is used to parse node relationships based on the node edge relationship list and node list in the Data data using the ClickHouse core parser, and obtain a directed acyclic graph; specifically, it is used to obtain the order of each node according to the node edge relationship list and node list; and connect the nodes with edges in order to obtain a directed acyclic graph. The target SQL acquisition module is used to assemble the directed acyclic graph (DAG) into target SQL by combining the ClickHouse SQL specification and functions. Specifically, it is used to pre-set a fixed SQL template for each operation according to the ClickHouse SQL specification and functions. The SQL template is a statement that can be executed in ClickHouse. It establishes an association between the dataset corresponding to the operation in each node of the DAG and the SQL template to obtain the SQL statement of each node. It obtains the target SQL based on the SQL statement of each node and the DAG. The target data acquisition module is used to execute the target SQL to obtain target data for data analysis.

8. A data analysis system based on Clickhouse, characterized in that, include: processor; Memory used to store the processor's executable instructions; The processor is configured to perform the method according to any one of claims 1-6.

Images