Hive QL generation method and system based on modularized data stream

Abstract

The invention discloses a Hive QL generation method and system based on a modularized data stream, and the method comprises the steps: constructing a data processing directed acyclic graph, carrying out the topological structure serialization, and generating a structured graph data file; and compiling the structured graph data file to generate a structured Hive QL script. According to the method, the data processing directed acyclic graph is constructed, and the topological structure of the data processing directed acyclic graph is serialized, so that complex data processing logic can be visually defined and persisted, accurate input is provided for subsequent automatic compiling, and logic errors possibly introduced by manual code compiling are avoided from the source.

Description

Technical Field

[0001] This invention belongs to the field of big data processing technology, specifically relating to a Hive QL generation method and system based on componentized data flow. Background Technology

[0002] Currently, there are several visualization tools or platforms in the big data field used to simplify data querying and processing. Their implementation schemes can be summarized into the following categories:

[0003] Form-based query builder: These tools provide users with a graphical interface, allowing them to select tables and fields and configure filtering and aggregation conditions through form elements such as dropdown menus and input boxes. The system then constructs and generates the corresponding Hive QL query statements based on the user's configuration.

[0004] Node- and connection-based data flow designers: These tools allow users to build data processing flows (i.e., directed acyclic graphs, DAGs) by dragging and dropping pre-defined "processor" nodes (such as data sources, filters, joins, aggregations, etc.) and connecting them. The system then parses the graph structure and converts it into executable Hive QL scripts.

[0005] Although the aforementioned existing technologies, especially node- and connection-based solutions, have simplified operations to some extent, they still have the following inherent defects, making them unsuitable for efficient and reliable application in industrial-grade big data platforms.

[0006] 1. Weak metadata support and error-prone configuration: Most tools are disconnected from the Hive metadata service (Metastore) of big data platforms. When configuring data sources and fields, users still need to manually enter information or select from numerous tables based on memory, a tedious and error-prone process. Tools cannot automatically acquire and utilize platform data assets such as table structures, field types, and their Chinese annotations.

[0007] 2. The generated scripts have a messy structure and poor maintainability: The SQL scripts generated by existing visualization tools often have poor internal structure, and common output formats include:

[0008] Single, complex, multi-level nested queries: Compressing all logic into a single, deeply nested SELECT statement results in extremely poor readability.

[0009] A series of discrete temporary table operations: each step is generated as a CREATE TABLE AS SELECT operation on the temporary table, resulting in lengthy scripts and complex management of intermediate results.

[0010] Neither of the above two structures can intuitively reflect the lineage of the business logic built by the user, making the scripts difficult to read, debug, and maintain, forming a "script maintenance hell" and bringing great difficulties to subsequent operation and maintenance and iteration.

[0011] 3. Lack of deep integration with big data platforms: Many tools are functionally isolated and fail to integrate as native components with the big data platform's data asset governance, unified access control, and job scheduling systems. Therefore, they cannot form an end-to-end closed-loop solution from data definition and processing to delivery, limiting their practical value in enterprise-level production environments. Summary of the Invention

[0012] In view of the shortcomings of the prior art, the purpose of this invention is to provide a Hive QL generation method and system based on componentized data flow, so as to solve the core technical problems in the prior art, such as cumbersome and error-prone configuration, poor script maintainability, and inability to deliver end-to-end, caused by metadata disconnect, chaotic script structure, and low platform integration.

[0013] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0014] The present invention provides a Hive QL generation method based on component-based data flow, comprising the following steps:

[0015] 1) Construct a directed acyclic graph for data processing and perform topology serialization to generate a structured graph data file;

[0016] 2) Compile the structured graph data files to generate structured Hive QL scripts.

[0017] Further, step 1) specifically includes:

[0018] 11) Construct a directed acyclic graph for data processing, wherein the graph contains multiple nodes, each node being labeled with its type; the graph includes at least one node of type data source, at least one node of type intermediate processor, and at least one node of type data writing; the nodes are connected by directed edges, which are used to define the data flow dependency relationship between the nodes;

[0019] 12) Encode the node attributes and inter-node connections in the directed acyclic graph into a JSON string according to a predetermined format to generate a structured graph data file.

[0020] Further, step 11) specifically includes:

[0021] 111) For nodes of type data source, query the Hive metadata service to obtain its optional data tables and field information, and complete the configuration of the node based on this;

[0022] 112) For nodes of type data writing, query the Hive metadata service to obtain the structured information of its target table, and complete the configuration of the node based on this.

[0023] Further, step 2) specifically includes:

[0024] 21) Parse the structured graph data file, identify all nodes and directed edges; determine the execution sequence of nodes based on the dependencies defined by the directed edges;

[0025] 22) Traverse the execution sequence and generate a common table expression for each node;

[0026] 23) Assemble all common table expressions and data write statements to generate a structured Hive QL script.

[0027] Further, step 21) specifically includes:

[0028] 211) Parse the structured graph data file and identify all nodes and directed edges between them;

[0029] 212) Based on the directed edges in the directed acyclic graph of data processing, perform topological sorting on the nodes to generate an execution sequence of all nodes; the execution sequence guarantees that any node is executed after all its upstream nodes.

[0030] Further, step 22) specifically includes:

[0031] Following the execution sequence in step 21), process each node in the execution sequence cyclically; in each iteration, perform the following steps:

[0032] 221) Assign a unique temporary result set identifier within the script to the currently being processed node; the assignment rule is: concatenate the English description of the node type with the auto-incrementing sequence number maintained by the system;

[0033] 222) Based on the type of the current node, select the corresponding SQL syntax template from the predefined template library;

[0034] 223) Fill the temporary result set identifiers of all upstream nodes of the current node, as well as the configuration parameters of the current node, into the corresponding placeholders of the selected SQL syntax template to generate a complete subquery;

[0035] 224) Combine the generated subquery with the temporary result set identifier of the current node to form a common table expression in the format of temporary result set identifier AS subquery;

[0036] 225) Determine if all nodes have been processed; if not, return to step 221) to process the next node; if yes, end.

[0037] Further, step 23) specifically includes:

[0038] 231) Based on the node types marked in step 11), identify all nodes whose type is data writing from the node execution sequence;

[0039] 232) Separate all the common table expressions generated in step 224) using commas according to the execution sequence of the nodes, and add the keyword WITH in front of them to form a complete common table expression definition section.

[0040] 233) For each node identified in step 231) as having a data write type, perform the following operations:

[0041] 2331) Obtain the target table name and partition information specified in the node configuration;

[0042] 2332) Generate a data write statement with the format: INSERT OVERWRITE TABLE target_table_name_partition_information SELECT * FROM upstream_temporary_result_set_identifier; where the upstream_temporary_result_set_identifier is the identifier corresponding to the upstream node that the data write node directly depends on in the execution sequence;

[0043] 2333) Combine the common table expression definition part constructed in step 232) with the generated data writing statement to form a structured Hive QL script.

[0044] This invention also provides a Hive QL generation system based on componentized data flow, comprising:

[0045] The graph data file generation module is used to construct a directed acyclic graph for data processing, perform topology serialization, and generate structured graph data files.

[0046] The Hive QL script generation module is used to compile structured graph data files to generate structured Hive QL scripts.

[0047] The beneficial effects of this invention are:

[0048] 1. This invention constructs a directed acyclic graph for data processing and serializes its topology, enabling complex data processing logic to be intuitively defined and persisted. This provides precise and unambiguous input for subsequent automated compilation, fundamentally avoiding logical errors that may be introduced by manually writing code.

[0049] 2. This invention determines the node execution sequence by performing topological sorting, and compiles each node into an independent common table expression based on this sequence, so that the final generated Hive QL script has a clear and linear logical structure composed of CTE sequences. This structure makes the logical hierarchy of the script completely consistent with the data flow graph designed by the user, which greatly improves the readability, debuggability and maintainability of the script.

[0050] 3. This invention accurately reproduces the dependencies in the data flow graph in the SQL script by assigning a temporary result set identifier to each node and referencing the identifier of the upstream node when generating subqueries. This on-demand referencing mechanism avoids generating redundant intermediate tables containing all fields, improves the execution efficiency of the script, and ensures the accuracy of the data processing logic.

[0051] 4. This invention assembles the public table expression definition part with the data writing statement to generate a complete script that conforms to the big data platform specifications and is ready to use. This method directly transforms the visual design model into a standard asset that can be deployed to the production environment, realizing an automated closed loop from data definition to processing and delivery, and significantly improving the delivery efficiency and reliability of data development tasks. Attached Figure Description

[0052] Figure 1 This is a schematic diagram of the method of the present invention. Detailed Implementation

[0053] To facilitate understanding by those skilled in the art, the present invention will be further described below with reference to embodiments and accompanying drawings. The content mentioned in the embodiments is not intended to limit the present invention.

[0054] Reference Figure 1 As shown, the present invention provides a Hive QL generation method based on component-based data flow, comprising the following steps:

[0055] 1) Construct a directed acyclic graph for data processing and perform topology serialization to generate a structured graph data file; specifically including:

[0056] 11) Construct a directed acyclic graph for data processing, wherein the graph contains multiple nodes, each node being labeled with its type; the graph includes at least one node of type data source, at least one node of type intermediate processor, and at least one node of type data writing; the nodes are connected by directed edges, which are used to define the data flow dependency relationship between the nodes;

[0057] 12) Encode the node attributes and inter-node connections in the directed acyclic graph into a JSON string according to a predetermined format to generate a structured graph data file.

[0058] Specifically, step 11) includes:

[0059] 111) For nodes of type data source, query the Hive metadata service to obtain its optional data tables and field information, and complete the configuration of the node based on this;

[0060] 112) For nodes of type data writing, query the Hive metadata service to obtain the structured information (i.e., field definitions) of its target table, and complete the configuration of the node based on this.

[0061] 2) Compile the structured graph data files to generate structured Hive QL scripts; specifically including:

[0062] 21) Parse the structured graph data file, identify all nodes and directed edges; determine the execution sequence of nodes based on the dependencies defined by the directed edges;

[0063] 22) Traverse the execution sequence and generate a common table expression for each node;

[0064] 23) Assemble all common table expressions and data write statements to generate a structured Hive QL script.

[0065] Specifically, step 21) includes:

[0066] 211) Parse the structured graph data file and identify all nodes and directed edges between them;

[0067] 212) Based on the directed edges in the directed acyclic graph of data processing, perform topological sorting on the nodes to generate an execution sequence of all nodes; the execution sequence guarantees that any node is executed after all its upstream nodes.

[0068] Specifically, step 22) includes:

[0069] Following the execution sequence in step 21), process each node in the execution sequence cyclically; in each iteration, perform the following steps:

[0070] 221) Assign a unique temporary result set identifier within the script to the currently being processed node; the assignment rule is: concatenate the English description of the node type with the auto-incrementing sequence number maintained by the system;

[0071] 222) Based on the type of the current node, select the corresponding SQL syntax template from the predefined template library;

[0072] 223) Fill the temporary result set identifiers of all upstream nodes of the current node, as well as the configuration parameters of the current node, into the corresponding placeholders of the selected SQL syntax template to generate a complete subquery;

[0073] 224) Combine the generated subquery with the temporary result set identifier of the current node to form a common table expression in the format of temporary result set identifier AS subquery;

[0074] 225) Determine if all nodes have been processed; if not, return to step 221) to process the next node; if yes, end.

[0075] Specifically, step 23) includes:

[0076] 231) Based on the node types marked in step 11), identify all nodes whose type is data writing from the node execution sequence;

[0077] 232) Separate all the common table expressions generated in step 224) using commas according to the execution sequence of the nodes, and add the keyword WITH in front of them to form a complete common table expression definition section.

[0078] 233) For each node identified in step 231) as having a data write type, perform the following operations:

[0079] 2331) Obtain the target table name and partition information specified in the node configuration;

[0080] 2332) Generate a data write statement with the format: INSERT OVERWRITE TABLE target_table_name_partition_information SELECT * FROM upstream_temporary_result_set_identifier; where the upstream_temporary_result_set_identifier is the identifier corresponding to the upstream node that the data write node directly depends on in the execution sequence;

[0081] 2333) Combine the common table expression definition part constructed in step 232) with the generated data writing statement to form a structured Hive QL script.

[0082] This invention also provides a Hive QL generation system based on componentized data flow, comprising:

[0083] The graph data file generation module is used to construct a directed acyclic graph for data processing, perform topology serialization, and generate structured graph data files.

[0084] The Hive QL script generation module is used to compile structured graph data files to generate structured Hive QL scripts.

[0085] This invention has many specific applications. The above description is only a preferred embodiment of this invention. It should be noted that for those skilled in the art, several improvements can be made without departing from the principle of this invention, and these improvements should also be considered within the scope of protection of this invention.

Claims

1. A Hive QL generation method based on component-based data flow, characterized in that, The steps are as follows: 1) Construct a directed acyclic graph for data processing and perform topology serialization to generate a structured graph data file; 2) Compile the structured graph data files to generate structured Hive QL scripts.

2. The Hive QL generation method based on component-based data flow according to claim 1, characterized in that, Step 1) specifically includes: 11) Construct a directed acyclic graph for data processing, wherein the graph contains multiple nodes, each node being labeled with its type; the graph includes at least one node of type data source, at least one node of type intermediate processor, and at least one node of type data writing; the nodes are connected by directed edges, which are used to define the data flow dependency relationship between the nodes; 12) Encode the node attributes and inter-node connections in the directed acyclic graph into a JSON string according to a predetermined format to generate a structured graph data file.

3. The Hive QL generation method based on component-based data flow according to claim 2, characterized in that, Step 11) specifically includes: 111) For nodes of type data source, query the Hive metadata service to obtain its optional data tables and field information, and complete the configuration of the node; 112) For nodes of type data writing, query the Hive metadata service to obtain the structured information of its target table and complete the configuration of the node.

4. The Hive QL generation method based on component-based data flow according to claim 3, characterized in that, Step 2) specifically includes: 21) Parse the structured graph data file, identify all nodes and directed edges; determine the execution sequence of nodes based on the dependencies defined by the directed edges; 22) Traverse the execution sequence and generate a common table expression for each node; 23) Assemble all common table expressions and data write statements to generate a structured Hive QL script.

5. The Hive QL generation method based on component-based data flow according to claim 4, characterized in that, Step 21) specifically includes: 211) Parse the structured graph data file and identify all nodes and directed edges between them; 212) Based on the directed edges in the directed acyclic graph of data processing, perform topological sorting on the nodes to generate an execution sequence of all nodes; the execution sequence guarantees that any node is executed after all its upstream nodes.

6. The Hive QL generation method based on component-based data flow according to claim 5, characterized in that, Step 22) specifically includes: Following the execution sequence in step 21), process each node in the execution sequence cyclically; in each iteration, perform the following steps: 221) Assign a unique temporary result set identifier within the script to the currently being processed node; the assignment rule is: concatenate the English description of the node type with the auto-incrementing sequence number maintained by the system; 222) Based on the type of the current node, select the corresponding SQL syntax template from the predefined template library; 223) Fill the temporary result set identifiers of all upstream nodes of the current node, as well as the configuration parameters of the current node, into the corresponding placeholders of the selected SQL syntax template to generate a complete subquery; 224) Combine the generated subquery with the temporary result set identifier of the current node to form a common table expression in the format of temporary result set identifier AS subquery; 225) Determine if all nodes have been processed; if not, return to step 221) to process the next node; if yes, end.

7. The Hive QL generation method based on component-based data flow according to claim 6, characterized in that, Step 23) specifically includes: 231) Based on the node types marked in step 11), identify all nodes whose type is data writing from the node execution sequence; 232) Separate all the common table expressions generated in step 224) using commas according to the execution sequence of the nodes, and add the keyword WITH in front of them to form a complete common table expression definition section. 233) For each node identified in step 231) as having a data write type, perform the following operations: 2331) Obtain the target table name and partition information specified in the node configuration; 2332) Generate a data write statement with the format: INSERT OVERWRITE TABLE target_table_name_partition_information SELECT * FROM upstream_temporary_result_set_identifier; where the upstream_temporary_result_set_identifier is the identifier corresponding to the upstream node that the data write node directly depends on in the execution sequence; 2333) Combine the common table expression definition part constructed in step 232) with the generated data writing statement to form a structured Hive QL script.

8. A Hive QL generation system based on component-based data flow, characterized in that, include: The graph data file generation module is used to construct a directed acyclic graph for data processing, perform topology serialization, and generate structured graph data files. The Hive QL script generation module is used to compile structured graph data files to generate structured HiveQL scripts.

Images