Data warehouse job chain shortening method, device, equipment, storage medium and product

Abstract

The application discloses a data warehouse job link shortening method and device, equipment, storage medium and product, and relates to the technical field of big data processing. The data warehouse job link shortening method comprises the following steps: obtaining a work job to be analyzed, wherein the work job comprises at least one SQL script; for each SQL script, a relation analysis tree is constructed; all the relation analysis trees in each work job are connected in series according to an execution order to obtain a relation analysis tree sequence, and redundant nodes in the relation analysis tree sequence are removed to obtain a target relation analysis tree sequence; and based on the target relation analysis tree sequence, global dependency analysis is performed on all the work jobs to obtain a skip link. Through the above different levels of data processing, the data complexity can be obviously reduced, and the performance of ETL work can be improved.

Description

Technical Field

[0001] This application relates to the field of big data processing technology, and in particular to a method, apparatus, equipment, storage medium and product for shortening the data warehouse operation chain. Background Technology

[0002] In related technologies, optimization methods for large-scale ETL jobs are still insufficient. They are usually based on the detection of statistical analysis indicators, such as computation hotspot analysis (visualizing the task execution status by statistically analyzing the ETL job's time consumption, CPU / IO resource consumption, and error frequency, thereby helping developers quickly locate performance bottlenecks) and table-level dependency discovery (sinking the frequently used tables and their common processing methods in various businesses into common DWS layer tables to reduce redundant processing), etc. However, it is difficult to accurately identify specific bottleneck points, and the guidance for performance optimization of large-scale jobs is limited. Summary of the Invention

[0003] The main purpose of this application is to provide a method, apparatus, equipment, storage medium and product for shortening the data warehouse operation chain, which aims to solve the technical problem that the traditional single-machine scheduling method can no longer meet the actual needs and limit the performance improvement of ETL operations.

[0004] To achieve the above objectives, this application proposes a method for shortening data warehouse operation links, the method comprising: Obtain the processing job to be analyzed, wherein the processing job includes at least one SQL script; For each of the SQL scripts, construct a relation parse tree; All the relation parse trees in each processing job are concatenated in the execution order to obtain a relation parse tree sequence, and redundant nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence; Based on the target relation parsing tree sequence, a global dependency analysis is performed on all the processing jobs to obtain skippable links.

[0005] In one embodiment, the step of performing global dependency analysis on all processing jobs based on the target relation parse tree sequence to obtain skippable links includes: Iterate through all the processing jobs and obtain the upstream dependent jobs of the target processing job. Based on the target relation parse tree sequence, a skip decision is made for each of the upstream dependent jobs; If there are skippable upstream dependent jobs, the target processing job is reconstructed based on the target upstream dependent jobs, and the step of obtaining the upstream dependent jobs of the traversed target processing jobs is returned until there are no skippable upstream dependent jobs.

[0006] In one embodiment, the step of skipping each upstream dependent job based on the target relation parse tree sequence includes any one of the following: Based on the target relation parse tree sequence, determine whether there is an intersection between the table nodes in the upstream dependent job and the table nodes in the target processing job. If there is no intersection, skip the upstream dependent job. Based on the target relation parse tree sequence, determine whether there is an intersection between the relation parse tree corresponding to the target processing job and the table nodes in the upstream dependent job. If there is no intersection, skip the upstream dependent job. Based on the target relation parse tree sequence, determine whether the relation parse tree corresponding to the target processing job satisfies the condition that it has an intersection with the table nodes in the upstream dependent job, and has no intersection with the field nodes corresponding to the table nodes that have an intersection. If the condition is met, skip the upstream dependent job.

[0007] In one embodiment, the step of concatenating all the relation parse trees in each processing job in execution order to obtain a relation parse tree sequence includes: Obtain the job execution log, and perform a two-dimensional analysis based on the job execution log and the SQL script to obtain the execution order; Based on the execution order, the relation parsing trees are processed sequentially, and the association between the currently processed first relation parsing tree and the second relation parsing tree that precedes the first relation parsing tree in the execution order is identified, wherein the association includes at least the dependency relationship between table nodes and the calculation relationship between field nodes; Based on the aforementioned association, a connection is established between the corresponding nodes in the first relation parse tree and the target relation parse tree to obtain a relation parse tree sequence.

[0008] In one embodiment, the redundant nodes include at least one of redundant table nodes and redundant field nodes, and the step of removing redundant nodes from the relation parse tree sequence to obtain the target relation parse tree sequence includes: Determine whether there exists a target table node with an out-degree of a preset value in the relation parse tree sequence. If it exists, then the target table node is determined to be the redundant table node. Determine whether there is a target field node in the relation parse tree sequence that does not have a preset calculation relationship. If there is, then the target field node is determined as the redundant field node. The preset calculation relationship includes the column calculation relationship between the target field node and the field node in the input table node of the last SQL script in the relation parse tree sequence, and the column calculation relationship between the target field node and the field node in the relation parse tree sequence that has a row calculation relationship. The redundant table nodes and redundant field nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence.

[0009] In one embodiment, the step of constructing a relational parse tree for each of the SQL scripts includes: Based on each of the SQL scripts, an abstract syntax tree is constructed, wherein each node in the abstract syntax tree corresponds to each lexical unit in the SQL script; Starting from the root node of the abstract syntax tree, the system traverses the target node and performs a corresponding mapping operation based on the node type of the target node to obtain different nodes. The different nodes include at least one of table nodes, field nodes, virtual table nodes, and virtual field nodes. Based on the computational relationships between the different nodes, a temporary relation parse tree is constructed, and the virtual table nodes and / or virtual field nodes in the temporary relation parse tree are cleaned to obtain the relation parse tree.

[0010] Furthermore, to achieve the above objectives, this application also proposes a data warehouse operation link shortening device, which includes: An acquisition module is used to acquire the processing job to be analyzed, wherein the processing job includes at least one SQL script; The building module is used to construct a relational parse tree for each of the SQL scripts; A redundancy removal module is used to concatenate all the relation parsing trees in each processing job in the execution order to obtain a relation parsing tree sequence, and remove redundant nodes from the relation parsing tree sequence to obtain a target relation parsing tree sequence; The analysis module is used to perform global dependency analysis on all the processing jobs based on the target relation parse tree sequence to obtain skippable links.

[0011] In addition, to achieve the above objectives, this application also proposes a data warehouse job chain shortening device, the device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the data warehouse job chain shortening method as described above.

[0012] In addition, to achieve the above objectives, this application also proposes a storage medium, which is a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, it implements the steps of the data warehouse operation link shortening method described above.

[0013] In addition, to achieve the above objectives, this application also provides a computer program product, which includes a computer program that, when executed by a processor, implements the steps of the data warehouse job link shortening method described above.

[0014] One or more technical solutions proposed in this application have at least the following technical effects: In contrast to related technologies, optimization methods for large-scale ETL jobs remain insufficient. These methods typically rely on statistical analysis metrics, such as computational hotspot analysis (visualizing task execution status by statistically analyzing ETL job time, CPU / IO resource consumption, and error frequency to help developers quickly pinpoint performance bottlenecks) and table-level dependency discovery (sinking frequently used tables and their common processing methods in various business processes into common DWS layer tables to reduce redundant processing). However, these methods struggle to accurately identify specific bottlenecks and offer limited guidance for performance optimization of large-scale jobs. In comparison, this application, when acquiring processing jobs including SQL scripts for analysis, first constructs a relational parse tree for each SQL script. Based on this, all relational parse trees in each processing job are concatenated in execution order. Redundant nodes are removed from the resulting relational parse tree sequence to obtain the target relational parse tree sequence. Then, based on the target relational parse tree sequence, global dependency analysis is performed on all processing jobs to identify skippable links. Through these different levels of data processing, specific bottlenecks can be accurately identified, improving the performance of ETL jobs. Attached Figure Description

[0015] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0016] 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, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0017] Figure 1 This is a flowchart illustrating an embodiment of the data warehouse operation chain shortening method of this application. Figure 2 This is a schematic diagram of the abstract syntax tree generation for the data warehouse operation link shortening method in this application; Figure 3 This is a flowchart illustrating Embodiment 2 of the data warehouse operation chain shortening method of this application; Figure 4 This is a flowchart illustrating the redundancy analysis of the data warehouse operation link shortening method in this application; Figure 5This is a flowchart illustrating the overall analysis of the data warehouse operation chain shortening method in this application; Figure 6 Construct a temporary relation parse tree graph for the data warehouse operation link shortening method in this application; Figure 7 This is a schematic diagram of the module structure of the data warehouse operation link shortening device according to an embodiment of this application; Figure 8 This is a schematic diagram of the equipment structure of the hardware operating environment involved in the data warehouse operation link shortening method in the embodiments of this application.

[0018] The purpose, features, and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0019] It should be understood that the specific embodiments described herein are merely illustrative of the technical solutions of this application and are not intended to limit this application.

[0020] To better understand the technical solution of this application, a detailed description will be provided below in conjunction with the accompanying drawings and specific implementation methods.

[0021] The main solution of this application embodiment is as follows: obtain the processing job to be analyzed, wherein the processing job includes at least one SQL script; construct a relation parse tree for each SQL script; concatenate all the relation parse trees in each processing job in the execution order to obtain a relation parse tree sequence, and remove redundant nodes from the relation parse tree sequence to obtain a target relation parse tree sequence; perform global dependency analysis on all the processing jobs based on the target relation parse tree sequence to obtain skippable links.

[0022] In related technologies, optimization methods for large-scale ETL jobs are still insufficient. They are usually based on the detection of statistical analysis indicators, such as computation hotspot analysis (visualizing the task execution status by statistically analyzing the ETL job's time consumption, CPU / IO resource consumption, and error frequency, thereby helping developers quickly locate performance bottlenecks) and table-level dependency discovery (sinking the frequently used tables and their common processing methods in various businesses into common DWS layer tables to reduce redundant processing), etc. However, it is difficult to accurately identify specific bottleneck points, and the guidance for performance optimization of large-scale jobs is limited.

[0023] When this application obtains the processing jobs to be analyzed, including SQL scripts, it first constructs a relational parse tree for each SQL script. Based on this, it concatenates all relational parse trees in each processing job according to the execution order. Redundant nodes are removed from the resulting relational parse tree sequence to obtain the target relational parse tree sequence. Based on the target relational parse tree sequence, a global dependency analysis is performed on all processing jobs to obtain skippable links. Through the above different levels of data processing, specific bottlenecks can be accurately identified, improving the performance of ETL jobs.

[0024] It should be noted that the executing entity in this embodiment can be a computing service device with data processing, network communication, and program execution functions, such as a tablet computer, personal computer, or mobile phone, or an electronic device or data warehouse operation link shortening device capable of performing the above functions. The following description uses a data warehouse operation link shortening device as an example to illustrate this embodiment and the subsequent embodiments.

[0025] Based on this, embodiments of this application provide a method for shortening the data warehouse operation chain, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the data warehouse operation link shortening method of this application.

[0026] In this embodiment, the data warehouse operation link shortening method includes steps S10 to S40: Step S10: Obtain the processing job to be analyzed, wherein the processing job includes at least one SQL script; It should be noted that the execution entity in this embodiment is a data warehouse job link shortening device. "Data warehouse processing task" refers to the data warehouse processing task that requires link shortening analysis; this task is the basic unit for optimization analysis. "SQL script" refers to a program fragment composed of Structured Query Language statements, used to implement specific processing logic such as data extraction, transformation, and loading. The data warehouse job link shortening device acquires the processing task to be analyzed, and this processing task contains at least one SQL script.

[0027] Step S20: For each of the SQL scripts, construct a relation parse tree; Understandably, a relational parse tree is a tree-like data structure used to express the row-column relationships within an SQL statement. It centers on table nodes and field nodes, using column and row computational relationships as edges to describe data flow and processing constraints. The data warehouse workflow shortening device performs lexical and syntactic analysis on the SQL script, decomposing it into lexical units, generating an abstract syntax tree (AST), and then traversing the AST to extract table nodes, field nodes, and the column and row computational relationships between nodes. This ultimately forms a relational parse tree that fully describes the data flow and conditional relationships of the SQL statement.

[0028] Specifically, an Abstract Syntax Tree (AST) is a tree-like data structure used to represent the syntactic structure of program code. After obtaining lexical and grammar files that conform to the syntax of a specified data warehouse, an AST can be quickly constructed using the ANTLR v4 tool. Alternatively, tools with SQL syntax parsing capabilities, such as Apache Calcite and Druid, can be used directly for construction. Figure 2 , Figure 2 A schematic diagram of abstract syntax tree generation is provided, and the mapping relationship between nodes in the abstract syntax tree and lexical units is shown in Table 1.

[0029] Table 1

[0030] Step S30: Concatenate all the relation parsing trees in each processing job in the execution order to obtain a relation parsing tree sequence, and remove redundant nodes from the relation parsing tree sequence to obtain the target relation parsing tree sequence; It should be noted that the data warehouse operation link shortening device concatenates all the relation parsing trees in each processing operation according to the execution order of their respective corresponding SQL scripts in the processing operation, thereby forming an ordered relation parsing tree sequence. This preserves the full-link information of data processing across SQL within the operation, and removes redundant nodes from the sequence, actively clearing table nodes and field nodes that do not contribute to the final output, ultimately obtaining a simplified target relation parsing tree sequence.

[0031] Step S40: Based on the target relation parsing tree sequence, perform global dependency analysis on all the processing jobs to obtain skippable links.

[0032] It is understood that skippable links refer to paths formed by one or more jobs in the global job dependency graph that, after analysis, can be safely ignored (i.e., do not need to wait for their completion). After skipping these jobs, downstream jobs can still execute correctly, thereby shortening the links. The data warehouse job link shortening device uses the dependency information between jobs to compare the target relationship parse tree sequence of each job with the corresponding sequence of its upstream job. It applies preset rules to determine whether the upstream job can be ignored. When the rules are met, it records the upstream job and the ignorable job paths further upstream, ultimately obtaining all job links that can be skipped, i.e., the skippable links.

[0033] In one feasible implementation, step S40 includes: Iterate through all the processing jobs and obtain the upstream dependent jobs of the target processing job. It should be noted that upstream dependent jobs refer to those processing jobs whose output (such as table data) is directly used or indirectly depended upon by the target processing job in the global job dependency relationship. The data warehouse job chain shortening device traverses all the processing jobs and, for the currently traversed target processing job, obtains all its corresponding upstream dependent jobs.

[0034] Based on the target relation parse tree sequence, a skip decision is made for each of the upstream dependent jobs; Understandably, the data warehouse operation link shortening device compares the target relationship parse tree sequence of the current target processing operation with the output information of each upstream dependent operation, and applies preset rules to determine whether the upstream dependent operation can be safely skipped.

[0035] If there are skippable upstream dependent jobs, the target processing job is reconstructed based on the target upstream dependent jobs, and the step of obtaining the upstream dependent jobs of the traversed target processing jobs is returned until there are no skippable upstream dependent jobs.

[0036] It should be noted that if the data warehouse operation link shortening device determines that there is a skippable upstream dependent operation, it merges the parse tree sequence of the skippable upstream operation with the parse tree sequence of the current target processing operation, and reconstructs a new target relationship parse tree sequence to obtain the reconstructed target processing operation. Then, it returns to the step of "obtaining the upstream dependent operations of the traversed target processing operation" and continues to obtain and skip the upstream dependent operations of the newly constructed target processing operation. The above process is executed cyclically until there is no skippable upstream dependent operation in a certain judgment, ensuring that a series of skippable operations can be absorbed and finally the real upstream dependency boundary that cannot be skipped is found.

[0037] In one feasible implementation, the step of skipping each upstream dependent job based on the target relation parse tree sequence includes any one of the following: Based on the target relation parse tree sequence, determine whether there is an intersection between the table nodes in the upstream dependent job and the table nodes in the target processing job. If there is no intersection, skip the upstream dependent job. Understandably, a table node refers to a node in a relational parse tree that represents a data table. It has a globally unified identifier and is used to represent the physical table that actually exists in the processing operation. The data warehouse operation link shortening device determines whether there is an intersection between the table nodes in the upstream dependent operation and the table nodes in the target processing operation based on the target relational parse tree sequence. If there is no intersection, that is, all tables output by the upstream dependent operation are not used by the target processing operation, then it is determined that the upstream dependent operation can be skipped.

[0038] Based on the target relation parse tree sequence, determine whether there is an intersection between the relation parse tree corresponding to the target processing job and the table nodes in the upstream dependent job. If there is no intersection, skip the upstream dependent job. It should be noted that the data warehouse operation link shortening device extracts all table nodes involved in the entire relation parsing tree corresponding to the target processing operation and compares them with the set of table nodes output by the upstream dependent operation. If there are no common table nodes between the two, that is, none of the tables output by the upstream dependent operation appear in the relation parsing tree of the target processing operation, it is determined that the upstream dependent operation can be skipped.

[0039] Based on the target relation parse tree sequence, determine whether the relation parse tree corresponding to the target processing job satisfies the condition that it has an intersection with the table nodes in the upstream dependent job, and has no intersection with the field nodes corresponding to the target table nodes that have an intersection. If the condition is met, skip the upstream dependent job.

[0040] Understandably, the data warehouse operation shortening device determines, based on the target relation parse tree sequence, whether the relation parse tree corresponding to the target processing operation satisfies the following conditions: First, the relation parse tree intersects with the table nodes in the upstream dependent operation (i.e., they share at least one table node); second, for these intersecting table nodes, the corresponding field nodes in the relation parse tree do not intersect with the field nodes output by the upstream dependent operation. If both conditions are met, it is determined that the upstream dependent operation can be skipped.

[0041] Specifically, after obtaining skippable links through the above steps, the data warehouse operation link shortening device generates a dependency shortening report. In addition, it records the relevant table and field information each time a rule is matched, forming a secondary redundancy analysis result, which gives the result accurate interpretability.

[0042] In this implementation, it can accurately provide job-level link shortening optimization points and global link shortening optimization points. In addition to describing the link optimization points, it can also automatically analyze and provide table-level and field-level reasons for the matching rules, obtain secondary redundancy analysis results, and help explain the direction of optimization.

[0043] Based on the first embodiment of this application, in the second embodiment of this application, the content that is the same as or similar to that in the first embodiment described above can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 3 Step S30, the data warehouse operation link shortening method further includes steps S01~S03: Step S01: Obtain the job execution log, and perform a two-dimensional analysis based on the job execution log and the SQL script to obtain the execution order; It should be noted that the job execution log refers to the information automatically recorded by the system during the actual execution of the processing job, including the actual execution order of SQL statements, start time, end time, execution status, variable assignment and reference, external script calls, and other runtime context information. The data warehouse job chain shortening device acquires the job execution log and performs a two-dimensional analysis based on the job execution log and the SQL script to obtain the execution order. Specifically, on the one hand, the SQL script is statically parsed to extract the statement structure, control commands (such as conditions, jumps, loops, external execution scripts, etc.) and variable definitions. On the other hand, the job execution log is parsed to obtain the actual execution path, variable dynamic values, and external script call records. The information from the two dimensions is fused to correct the control flow and dynamic variable problems that pure static analysis cannot handle, thereby accurately restoring the true execution order of each operation in the processing job.

[0044] Step S02: Based on the execution order, process the relation parsing trees sequentially, and identify the association between the currently processed first relation parsing tree and the second relation parsing tree that precedes the first relation parsing tree in the execution order, wherein the association includes at least the dependency relationship between table nodes and the calculation relationship between field nodes; Understandably, the first relation parse tree refers to the relation parse tree analyzed in the execution order at the current processing moment. The second relation parse tree refers to any relation parse tree that precedes the first relation parse tree in the execution order (i.e., is executed before the first relation parse tree). For each first relation parse tree, the data warehouse job chain shortening device traverses all second relation parse trees executed before it, checking whether there are dependencies between their table nodes (e.g., whether the source table node in the first relation parse tree is equal to the root table node in the second relation parse tree) and whether there are computational relationships between their field nodes. These relationships include at least dependencies between table nodes and computational relationships between field nodes.

[0045] Step S03: Based on the association relationship, establish a connection between the corresponding nodes in the first relation parse tree and the target relation parse tree to obtain a relation parse tree sequence.

[0046] It should be noted that corresponding nodes refer to table nodes and field nodes that need to be connected between different relational parse trees through the aforementioned association relationships (such as dependencies between table nodes and computational relationships between field nodes). The job link shortening device, based on the currently processed first relational parse tree, utilizes the identified table node dependencies and field node computational relationships with the second relational parse tree (or other existing target relational parse trees) to associate the source table nodes in the first relational parse tree with the root table nodes in the target relational parse tree, and establishes column computational relationships between the field nodes in the first relational parse tree and their corresponding field nodes in the target relational parse tree. Through this cross-parse tree node connection, the current parse tree is appended or embedded into the existing relational parse tree set according to the execution order, thereby forming a complete, cross-SQL relational parse tree sequence.

[0047] In one feasible implementation, the redundant node includes at least one of a redundant table node and a redundant field node, and step S30 includes: Determine whether there exists a target table node with an out-degree of a preset value in the relation parse tree sequence. If it exists, then the target table node is determined to be the redundant table node. As can be understood, out-degree refers to the number of edges a node has pointing to other nodes in graph theory or tree structures. The preset value is a pre-defined threshold used to determine whether a table node is redundant; typically, the preset value is 0, indicating that the table node has no output relationships pointing to any other node. A redundant table node is a table node in the relation parse tree sequence that does not contribute to the final processing result and can be safely removed. The job link shortening device traverses all table nodes in the relation parse tree sequence, calculating the out-degree of each table node (i.e., the number of relationships established by that table node as a source). If the out-degree of a target table node equals the preset value (usually 0), then that target table node is determined to be a redundant table node. Through this quantitative indicator of out-degree, it automatically identifies invalid table nodes that are no longer depended on by any subsequent nodes and do not contribute to the data processing flow.

[0048] Determine whether there is a target field node in the relation parse tree sequence that does not have a preset calculation relationship. If there is, then the target field node is determined as the redundant field node. The preset calculation relationship includes the column calculation relationship between the target field node and the field node in the input table node of the last SQL script in the relation parse tree sequence, and the column calculation relationship between the target field node and the field node in the relation parse tree sequence that has a row calculation relationship. It should be noted that the preset calculation relationships refer to two types of column calculation relationships pre-defined to determine whether field nodes are non-redundant. The first type is the column calculation relationship between the target field node and the field nodes in the input table node of the last SQL script in the relational parse tree sequence; the second type is the column calculation relationship between the target field node and field nodes in the relational parse tree sequence that have row calculation relationships. Redundant field nodes refer to field nodes in the relational parse tree sequence that do not actually contribute to the final processing result or data constraints and can be safely removed. The input table node of the last SQL script refers to the table node read or referenced by the last SQL script in the execution order of the job corresponding to the relational parse tree sequence; the field nodes in this table node represent the data source of the final output of the job. Field nodes with row calculation relationships refer to field nodes in the relational parse tree sequence that participate in or indirectly participate in row calculation relationships (such as as filtering conditions, association conditions, grouping conditions, etc.); these field nodes play a role in row-level filtering or constraint of the data. The job link shortening device traverses all field nodes in the relation parse tree sequence. For each target field node, it checks whether it satisfies at least one of the following two types of column calculation relations. If the target field node does not satisfy both of the above relations, that is, there is no preset calculation relation, then the target field node is determined to be a redundant field node.

[0049] Specifically, after completing the above steps, the data warehouse workflow shortening device also records the relevant table and field information each time a rule match is hit, forming a basic redundancy analysis result, which can be referenced. Figure 4 , Figure 4 A redundancy analysis flowchart is provided. Furthermore, the results of this basic redundancy analysis, together with the results of the secondary redundancy analysis, constitute a redundancy point report, which is output together with the dependency reduction report to aid in judgment and reference. Figure 5 , Figure 5 A flowchart of the overall analysis process is provided.

[0050] The redundant table nodes and redundant field nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence.

[0051] Understandably, the data warehouse operation link shortening device removes redundant table nodes (such as table nodes with an out-degree of 0) and redundant field nodes (such as field nodes that do not have column calculation relationships with the final input table fields or row calculation relationship fields) from the relation parse tree sequence. At the same time, it removes all column calculation relationships and row calculation relationship edges related to these nodes. For chain changes such as the out-degree of upstream nodes becoming 0 due to node deletion, it continues to clean up until the sequence no longer contains any redundant table nodes and redundant field nodes, thus obtaining a simplified target relation parse tree sequence that has removed all invalid tables and fields.

[0052] In one feasible implementation, step S20 includes: Based on each of the SQL scripts, an abstract syntax tree is constructed, wherein each node in the abstract syntax tree corresponds to each lexical unit in the SQL script; It should be noted that lexical units (Tokens) refer to the tokens with independent semantics that are decomposed from SQL text through lexical and syntactic analysis. For each SQL script, the data warehouse workflow shortening device first decomposes the script text into a series of lexical units using a lexical analyzer; then, based on predefined SQL syntax rules, a syntactic analyzer organizes these lexical units into a hierarchical abstract syntax tree. This tree fully expresses the syntactic structure of the SQL statement, such as projection nodes, join nodes, table nodes, and field nodes, thereby converting unstructured SQL text into a structured, programmable, traversable tree representation. This provides a syntactic-level foundational model for subsequent extraction of row and column relationships and construction of relational parse trees.

[0053] Starting from the root node of the abstract syntax tree, the system traverses the target node and performs a corresponding mapping operation based on the node type of the target node to obtain different nodes. The different nodes include at least one of table nodes, field nodes, virtual table nodes, and virtual field nodes. Understandably, a virtual table node refers to an intermediate table node that is temporarily generated during traversal to maintain the continuity of relationship establishment and does not correspond to an actual physical table. Similarly, a virtual field node refers to an intermediate field node that is temporarily generated during traversal to maintain the continuity of relationship establishment and does not correspond to an actual physical field. The data warehouse operation link shortening device accesses each node in the abstract syntax tree starting from the root node. For different node types (e.g., CreateTableAsNode maps to table nodes, ProjectNode maps to virtual table nodes, FieldNode maps to field nodes, LiteralNode maps to virtual field nodes, etc.), it generates corresponding relational parse tree nodes and records the initial relationships between nodes. Through this mapping operation, it ultimately obtains different nodes that include at least one of the following: table nodes, field nodes, virtual table nodes, and virtual field nodes.

[0054] Since some nodes are not directly associated with specific table nodes and field nodes, but rather serve as carriers of intermediate results, virtual nodes are constructed to establish associations between these nodes and specific table nodes and field nodes, thus ensuring the traceability of results.

[0055] Specifically, refer to Table 2, which is a node mapping table: Table 2

[0056] Based on the computational relationships between the different nodes, a temporary relation parse tree is constructed, and the virtual table nodes and / or virtual field nodes in the temporary relation parse tree are cleaned to obtain the relation parse tree.

[0057] It should be noted that computational relationships include column-based and row-based computational relationships. A temporary relation parse tree refers to the preliminary tree structure formed during the construction of the relation parse tree based on the different nodes and their computational relationships obtained from the abstract syntax tree mapping. The data warehouse operation link shortening device connects the table nodes, field nodes, virtual table nodes, and virtual field nodes obtained from the aforementioned mapping operation into a tree structure according to their column-based and row-based computational relationships, forming a temporary relation parse tree. Then, it traverses this temporary tree. For each virtual table node, it directly establishes a relationship between its upstream and downstream table nodes and removes the virtual node; for each virtual field node, it directly establishes a column-based computational relationship between its upstream and downstream field nodes and removes the virtual node. Simultaneously, it handles the relationship merging and redirection caused by node removal, thereby obtaining a pure relation parse tree without any virtual nodes. (Refer to...) Figure 6 , Figure 6 A temporary relation parse tree construction graph is provided.

[0058] Specifically, column computed relationships are used to describe the direction and type of data transfer between fields. If field F1 is directly and explicitly processed from field F2 through a function or other complex expression, then there is a column + computed relationship F2. F1(TYPE). Column calculation types include ordinary processing, aggregation calculations, etc., which generally do not affect row and column relationship analysis.

[0059] Example 1: insert into T1 (F1) select F2 from T2; This example contains a column that calculates the relation T2.F2. T1.F1.

[0060] Example 2: update T1 set F1=F2 from T2 where … ; This example includes a column that calculates the relation T2.F2. T1.F1.

[0061] Row-computed relationships are used to describe the data constraints and types between fields and tables. If field F1 participates in the conditional expressions when data flows to table T1, including filtering conditions, join conditions, aggregation expressions, and sorting expressions in certain scenarios, then a row-computed relationship F1 exists. T1(TYPE). Row calculation types generally include WHERE, HAVING, GROUP_BY, JOIN_ON, etc.

[0062] Example 1: `insert into T1 (F1) select F2 from T2 where F3 = 1;` This example includes a row that calculates the relation T2.F3. T1(WHERE).

[0063] Example 2: `insert into T1 (F1) select F2 from T2 left join T3 on T2.F1 = T3.F1;` This example calculates the relationship T2.F1. T1(JOIN_ON),T3.F1 T1(JOIN_ON).

[0064] In this implementation, a temporary relation parse tree is constructed based on the computational relationships between different nodes. This temporary tree fully preserves all intermediate nodes and relationships mapped from the abstract syntax tree, thus avoiding the loss of any potential information before cleaning. Furthermore, since this step cleans the virtual table nodes and virtual field nodes in the temporary relation parse tree, the intermediate layer is eliminated by directly connecting the real nodes at both ends of the virtual nodes. This results in a simplified relation parse tree that only contains actual physical table nodes and actual field nodes, making the data flow and constraint relationships clearer and more direct. This provides a noise-free and highly efficient data model for subsequent analysis steps such as job-level concatenation and redundant node determination, significantly reducing the complexity of subsequent traversal and calculation.

[0065] It should be noted that the above examples are only for understanding this application and do not constitute a limitation on the data warehouse operation link shortening method of this application. Any simple modifications based on this technical concept are within the protection scope of this application.

[0066] This application also provides a data warehouse operation link shortening device, please refer to... Figure 7 The data warehouse operation link shortening device includes: The acquisition module 10 is used to acquire the processing job to be analyzed, wherein the processing job includes at least one SQL script; Module 20 is used to construct a relation parse tree for each of the SQL scripts; The redundancy removal module 30 is used to concatenate all the relation parsing trees in each processing job in the execution order to obtain a relation parsing tree sequence, and remove redundant nodes from the relation parsing tree sequence to obtain a target relation parsing tree sequence; Analysis module 40 is used to perform global dependency analysis on all the processing jobs based on the target relation parse tree sequence to obtain skippable links.

[0067] Optionally, the analysis module includes: The reconstruction submodule is used to traverse all the processing jobs and obtain the upstream dependent jobs of the traversed target processing jobs; based on the target relation parse tree sequence, a skip judgment is performed on each of the upstream dependent jobs; if there are skippable target upstream dependent jobs, the target processing job is reconstructed based on the target upstream dependent jobs, and the step of obtaining the upstream dependent jobs of the traversed target processing jobs is returned until there are no skippable target upstream dependent jobs.

[0068] Optionally, the reconstruction submodule includes: The judgment unit is used to determine, based on the target relation parse tree sequence, whether there is an intersection between the table nodes in the upstream dependent job and the table nodes in the target processing job; if not, the upstream dependent job is skipped. It also determines, based on the target relation parse tree sequence, whether there is an intersection between the relation parse tree corresponding to the target processing job and the table nodes in the upstream dependent job; if not, the upstream dependent job is skipped. Furthermore, based on the target relation parse tree sequence, it determines whether the relation parse tree corresponding to the target processing job satisfies the condition that it intersects with the table nodes in the upstream dependent job, but does not intersect with the field nodes corresponding to the intersecting table nodes; if satisfied, the upstream dependent job is skipped.

[0069] Optionally, the redundancy removal module includes: A submodule is established to obtain job execution logs. Based on the job execution logs and the SQL script, a two-dimensional analysis is performed to obtain the execution order. Based on the execution order, the relation parse trees are processed sequentially, and the association between the currently processed first relation parse tree and the second relation parse tree that precedes the first relation parse tree in the execution order is identified. The association includes at least the dependency relationship between table nodes and the calculation relationship between field nodes. Based on the association, a connection is established between the corresponding nodes in the first relation parse tree and the target relation parse tree to obtain a relation parse tree sequence.

[0070] The removal submodule is used to determine whether there is a target table node in the relation parse tree sequence with an out-degree of a preset value. If so, the target table node is identified as a redundant table node. It also determines whether there is a target field node in the relation parse tree sequence that does not have a preset computational relationship. If so, the target field node is identified as a redundant field node. The preset computational relationship includes column computational relationships between the target field node and field nodes in the input table node of the last SQL script in the relation parse tree sequence, and column computational relationships between the target field node and field nodes in the relation parse tree sequence that have row computational relationships. The redundant table nodes and redundant field nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence.

[0071] Optionally, the building module includes: The mapping submodule is used to construct an abstract syntax tree based on each SQL script, wherein each node in the abstract syntax tree corresponds to each lexical unit in the SQL script; starting from the root node of the abstract syntax tree, traversing the target node and performing corresponding mapping operations on the target node according to the node type of the target node to obtain different nodes, wherein the different nodes include at least one of table nodes, field nodes, virtual table nodes, and virtual field nodes; based on the computational relationship between the different nodes, constructing a temporary relation parse tree, and cleaning the virtual table nodes and / or virtual field nodes in the temporary relation parse tree to obtain the relation parse tree.

[0072] The data warehouse operation link shortening device provided in this application, employing the data warehouse operation link shortening method in the above embodiments, can solve the technical problem of data warehouse operation link shortening. Compared with the prior art, the beneficial effects of the data warehouse operation link shortening device provided in this application are the same as the beneficial effects of the data warehouse operation link shortening method provided in the above embodiments, and other technical features in the data warehouse operation link shortening device are the same as the features disclosed in the methods of the above embodiments, and will not be repeated here.

[0073] This application provides a data warehouse job link shortening device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the data warehouse job link shortening method in the first embodiment described above.

[0074] The following is for reference. Figure 8The diagram illustrates a structural schematic suitable for implementing the data warehouse job link shortening device in the embodiments of this application. The data warehouse job link shortening device in the embodiments of this application may include, but is not limited to, mobile terminals such as mobile phones, laptops, tablets, digital broadcast receivers, PDAs (Personal Digital Assistants), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 8 The data warehouse operation link shortening device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0075] like Figure 8 As shown, the data warehouse job link shortening device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage device 1003 into a random access memory (RAM) 1004. The RAM 1004 also stores various programs and data required for the operation of the data warehouse job link shortening device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: input devices 1007 including, for example, a touch screen, touchpad, keyboard, mouse, image sensor, microphone, accelerometer, gyroscope, etc.; output devices 1008 including, for example, a liquid crystal display (LCD), speaker, vibrator, etc.; storage devices 1003 including, for example, magnetic tape, hard disk, etc.; and communication devices 1009. Communication device 1009 allows the data warehouse job link shortening device to communicate wirelessly or wiredly with other devices to exchange data. While the figures show data warehouse job link shortening devices with various systems, it should be understood that implementation or possession of all the systems shown is not required. More or fewer systems may be implemented alternatively.

[0076] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from ROM 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the methods of the embodiments disclosed in this application.

[0077] The data warehouse operation link shortening device provided in this application, employing the data warehouse operation link shortening method in the above embodiments, can solve the technical problem of data warehouse operation link shortening. Compared with the prior art, the beneficial effects of the data warehouse operation link shortening device provided in this application are the same as the beneficial effects of the data warehouse operation link shortening method provided in the above embodiments, and other technical features in the data warehouse operation link shortening device are the same as the features disclosed in the method of the previous embodiment, and will not be repeated here.

[0078] It should be understood that the various parts disclosed in this application can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any suitable manner in one or more embodiments or examples.

[0079] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0080] This application provides a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, the computer-readable program instructions being used to execute the data warehouse operation link shortening method in the above embodiments.

[0081] The computer-readable storage medium provided in this application may be, for example, a USB flash drive, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, system, or device. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination thereof.

[0082] The aforementioned computer-readable storage medium may be included in the data warehouse job link shortening device; or it may exist independently and not be assembled into the data warehouse job link shortening device.

[0083] The aforementioned computer-readable storage medium carries one or more programs. When these programs are executed by a data warehouse job link shortening device, the data warehouse job link shortening device causes the following: it acquires a processing job to be analyzed, wherein the processing job includes at least one SQL script; for each SQL script, it constructs a relational parse tree; it concatenates all the relational parse trees in each processing job in execution order to obtain a relational parse tree sequence, and removes redundant nodes from the relational parse tree sequence to obtain a target relational parse tree sequence; based on the target relational parse tree sequence, it performs global dependency analysis on all the processing jobs to obtain skippable links.

[0084] Computer program code for performing the operations of this application can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a Local Area Network (LAN) or a Wide Area Network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0085] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0086] The modules described in the embodiments of this application can be implemented in software or hardware. The names of the modules do not necessarily limit the functionality of the unit itself.

[0087] The readable storage medium provided in this application is a computer-readable storage medium that stores computer-readable program instructions (i.e., a computer program) for executing the above-described data warehouse job link shortening method, thereby solving the technical problem of data warehouse job link shortening. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the data warehouse job link shortening method provided in the above embodiments, and will not be repeated here.

[0088] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the data warehouse job chain shortening method described above.

[0089] The computer program product provided in this application can solve the technical problem of shortening the data warehouse operation chain. Compared with the prior art, the beneficial effects of the computer program product provided in this application are the same as the beneficial effects of the data warehouse operation chain shortening method provided in the above embodiments, and will not be repeated here.

[0090] The above description is only a part of the embodiments of this application and does not limit the scope of protection of this application. All equivalent structural transformations made under the technical concept of this application and using the content of this application specification and drawings, or direct / indirect applications in other related technical fields, are included in the scope of protection of this application.

Claims

1. A method for shortening the data warehouse operation chain, characterized in that, The method for shortening the data warehouse operation chain includes: Obtain the processing job to be analyzed, wherein the processing job includes at least one SQL script; For each of the SQL scripts, construct a relation parse tree; All the relation parse trees in each processing job are concatenated in the execution order to obtain a relation parse tree sequence, and redundant nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence; Based on the target relation parsing tree sequence, a global dependency analysis is performed on all the processing jobs to obtain skippable links.

2. The data warehouse operation chain shortening method as described in claim 1, characterized in that, The step of performing global dependency analysis on all processing jobs based on the target relation parsing tree sequence to obtain skippable links includes: Iterate through all the processing jobs and obtain the upstream dependent jobs of the target processing job. Based on the target relation parse tree sequence, a skip decision is made for each of the upstream dependent jobs; If there are skippable upstream dependent jobs, the target processing job is reconstructed based on the target upstream dependent jobs, and the step of obtaining the upstream dependent jobs of the traversed target processing jobs is returned until there are no skippable upstream dependent jobs.

3. The data warehouse operation chain shortening method as described in claim 2, characterized in that, The step of skipping a decision for each upstream dependent job based on the target relation parse tree sequence includes any one of the following: Based on the target relation parse tree sequence, determine whether there is an intersection between the table nodes in the upstream dependent job and the table nodes in the target processing job. If there is no intersection, skip the upstream dependent job. Based on the target relation parse tree sequence, determine whether there is an intersection between the relation parse tree corresponding to the target processing job and the table nodes in the upstream dependent job. If there is no intersection, skip the upstream dependent job. Based on the target relation parse tree sequence, determine whether the relation parse tree corresponding to the target processing job satisfies the condition that it has an intersection with the table nodes in the upstream dependent job, and has no intersection with the field nodes corresponding to the target table nodes that have an intersection. If the condition is met, skip the upstream dependent job.

4. The data warehouse operation chain shortening method as described in claim 1, characterized in that, The step of concatenating all the relation parsing trees in each processing job according to the execution order to obtain a relation parsing tree sequence includes: Obtain the job execution log, and perform a two-dimensional analysis based on the job execution log and the SQL script to obtain the execution order; Based on the execution order, the relation parsing trees are processed sequentially, and the association between the currently processed first relation parsing tree and the second relation parsing tree that precedes the first relation parsing tree in the execution order is identified, wherein the association includes at least the dependency relationship between table nodes and the calculation relationship between field nodes; Based on the aforementioned association, a connection is established between the corresponding nodes in the first relation parse tree and the target relation parse tree to obtain a relation parse tree sequence.

5. The data warehouse operation chain shortening method as described in claim 1, characterized in that, The redundant nodes include at least one of redundant table nodes and redundant field nodes, and the step of removing redundant nodes from the relation parse tree sequence to obtain the target relation parse tree sequence includes: Determine whether there exists a target table node with an out-degree of a preset value in the relation parse tree sequence. If it exists, then the target table node is determined to be the redundant table node. Determine whether there is a target field node in the relation parse tree sequence that does not have a preset calculation relationship. If there is, then the target field node is determined as the redundant field node. The preset calculation relationship includes the column calculation relationship between the target field node and the field node in the input table node of the last SQL script in the relation parse tree sequence, and the column calculation relationship between the target field node and the field node in the relation parse tree sequence that has a row calculation relationship. The redundant table nodes and redundant field nodes are removed from the relation parse tree sequence to obtain the target relation parse tree sequence.

6. The data warehouse operation chain shortening method as described in claim 1, characterized in that, The step of constructing a relational parse tree for each SQL script includes: Based on each of the SQL scripts, an abstract syntax tree is constructed, wherein each node in the abstract syntax tree corresponds to each lexical unit in the SQL script; Starting from the root node of the abstract syntax tree, the system traverses the target node and performs a corresponding mapping operation based on the node type of the target node to obtain different nodes. The different nodes include at least one of table nodes, field nodes, virtual table nodes, and virtual field nodes. Based on the computational relationships between the different nodes, a temporary relation parse tree is constructed, and the virtual table nodes and / or virtual field nodes in the temporary relation parse tree are cleaned to obtain the relation parse tree.

7. A data warehouse operation link shortening device, characterized in that, The device includes: An acquisition module is used to acquire the processing job to be analyzed, wherein the processing job includes at least one SQL script; The building module is used to construct a relational parse tree for each of the SQL scripts; A redundancy removal module is used to concatenate all the relation parsing trees in each processing job in the execution order to obtain a relation parsing tree sequence, and remove redundant nodes from the relation parsing tree sequence to obtain a target relation parsing tree sequence; The analysis module is used to perform global dependency analysis on all the processing jobs based on the target relation parse tree sequence to obtain skippable links.

8. A data warehouse workflow shortening device, characterized in that, The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the data warehouse operation link shortening method as described in any one of claims 1 to 6.

9. A storage medium, characterized in that, The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, it implements the steps of the data warehouse operation link shortening method as described in any one of claims 1 to 6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the steps of the data warehouse operation link shortening method as described in any one of claims 1 to 6.

Images