Data warehouse association link cleaning method and device, equipment and storage medium

Abstract

The present application relates to the technical field of data warehouse, especially to a data warehouse correlation link cleaning method, device, equipment and storage medium, the method first carries out script retrieval to the obtained task path set based on the preset function name matching rule, respectively to each function call line data obtained denoising and deduplication processing, obtain a plurality of task scheduling functions, based on a plurality of task scheduling functions in the preset data warehouse dictionary table SQL content search, obtain the SQL definition corresponding to each task scheduling function, respectively to each SQL definition character cleaning processing and sorting deduplication processing, obtain the data source table set corresponding to each task scheduling function and the cleaning target table set, the task path set, a plurality of task scheduling functions, a plurality of data source table sets and a plurality of cleaning target table sets are associated and bound and written in the blood relationship table, obtain the standardized blood table, aims at improving the working efficiency of data warehouse blood relationship carding.

Description

Technical Field

[0001] This invention relates to the field of data warehouse technology, and in particular to a method, apparatus, equipment, and storage medium for cleaning data warehouse association links. Background Technology

[0002] In a data warehouse system built on Greenplum, core processes such as data cleaning and scheduling rely on the collaborative operation of numerous functions, data tables, and scheduling tasks. Clarifying the relationships between these three elements is crucial for ensuring data governance efficiency. However, currently, the industry still largely relies on traditional manual methods to clarify the lineage of Greenplum data warehouses, resulting in significant efficiency bottlenecks.

[0003] When querying cleaning functions, staff must first perform a fuzzy search for related functions using a dictionary, and then manually confirm each result from the multiple results returned, consuming a significant amount of time. When identifying the source tables that functions depend on, the only method is to manually scan each line of the function content to identify and manually remove duplicates from the various source tables involved, resulting in extremely low overall efficiency. When confirming the execution function corresponding to a scheduling task, the conventional method is to perform a fuzzy search in the data dictionary using the task name. If the task name and function name differ significantly, this method becomes completely ineffective, requiring logging into the server to consult the corresponding shell script to determine the function—a cumbersome process prone to human error. Furthermore, when querying all functions involved in a table, the fuzzy search results in the dictionary often contain invalid functions. The scheduling tasks corresponding to these functions are invalid, requiring staff to manually filter for valid functions, further increasing operational complexity and time costs. These traditional manual methods are not only inefficient but also prone to errors in confirming lineage due to human oversight, failing to meet the high-efficiency requirements of data traceability, problem localization, and optimization analysis within a data warehouse system, and hindering the overall efficiency of data governance efforts. Summary of the Invention

[0004] To overcome the shortcomings of the prior art, the present invention aims to provide a data warehouse association link cleaning method, apparatus, equipment and storage medium, which aims to improve the efficiency of data warehouse lineage sorting and realize convenient and fast query of various associations.

[0005] The first aspect of this invention provides a data warehouse association link cleaning method, comprising: obtaining a task path set and a preset function name matching rule; performing script retrieval on the task path set based on the function name matching rule to obtain multiple function call line data; performing noise reduction and deduplication processing on each of the function call line data to obtain multiple task scheduling functions; performing SQL (Structured Query Language) content lookup in a preset data warehouse dictionary table based on the multiple task scheduling functions to obtain SQL definitions corresponding to each task scheduling function; performing character cleaning processing on each of the SQL definitions and sorting and deduplicating processing on the multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function; associating and binding the task path set, the multiple task scheduling functions, the multiple sets of data source tables, and the multiple sets of cleaning target tables and writing them into a lineage table to obtain a standardized lineage table.

[0006] Optionally, in a first implementation of the first aspect of the present invention, obtaining the task path set includes: obtaining a system task table and multiple physical paths; performing valid task filtering processing on the system task table to obtain multiple valid task names; concatenating each valid task name with the corresponding physical path to generate a physical storage path for each valid task, and constructing a task path set based on the generated multiple physical storage paths.

[0007] Optionally, in a second implementation of the first aspect of the present invention, the step of performing noise reduction and deduplication processing on each of the function call line data to obtain multiple task scheduling functions includes: using the sed tool to perform comment line deletion processing on each of the function call line data to obtain multiple function code line data; using the awk tool to perform field segmentation and extraction processing on each of the function code line data to obtain multiple function names; and using the awk tool to sort and deduplicate all the extracted function names to obtain multiple task scheduling functions.

[0008] Optionally, in a third implementation of the first aspect of the present invention, the step of searching for SQL content in a preset data warehouse dictionary table based on multiple task scheduling functions to obtain an SQL definition corresponding to each task scheduling function includes: constructing SQL query statements based on each task scheduling function; searching for SQL content in the data warehouse dictionary table based on multiple SQL query statements to obtain a query result set corresponding to each task scheduling function; and using the sed tool to remove redundant characters from each query result set to obtain an SQL definition corresponding to each task scheduling function.

[0009] Optionally, in the fourth implementation of the first aspect of the present invention, the step of performing character cleaning processing on each of the SQL definitions and sorting and deduplicating multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function includes: using the sed tool to delete comment lines from each of the SQL definitions to obtain multiple valid SQL code lines; performing table name retrieval and extraction processing on each of the valid SQL code lines to obtain a set of data source table names and a set of cleaning target table names; and using the awk tool to sort and deduplicate the set of data source table names and the set of cleaning target table names to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function.

[0010] Optionally, in the fifth implementation of the first aspect of the present invention, the step of performing table name retrieval and extraction processing on each of the valid SQL code lines to obtain a set of data source table names and a set of cleaned target table names includes: using the egrep tool to perform data source table association row retrieval processing on each of the valid SQL code lines to obtain multiple data source table association rows; obtaining a preset standardized table name format, and using the egrep tool to perform table name format filtering processing on each of the data source table association rows based on the standardized table name format to obtain multiple data source table name contents; using the sed tool to perform special character removal processing on each of the data source table name contents to obtain the set of data source table names; using the egrep tool to perform target row positioning processing on each of the valid SQL code lines to obtain multiple target rows; obtaining a preset first delimiter and second delimiter, and using the awk tool to perform table name content extraction processing on each of the target rows based on the first delimiter to obtain multiple cleaned target table name contents; and using the awk tool to perform table name extraction processing on each of the cleaned target table name contents based on the second delimiter to obtain the set of cleaned target table names.

[0011] Optionally, in the sixth implementation of the first aspect of the present invention, after associating and binding the task path set, the multiple task scheduling functions, the multiple data source table sets, and the multiple cleaning target table sets and writing them into the lineage table to obtain a standardized lineage table, the method further includes: obtaining a set of table quality indicators based on the multiple data source table sets and the multiple cleaning target table sets; constructing an association mapping table based on the set of table quality indicators and the standardized lineage table; obtaining preset anomaly judgment rules; performing anomaly indicator analysis and processing on the association mapping table based on the anomaly judgment rules to obtain a quality anomaly source set; and generating an optimization suggestion report based on the quality anomaly source set.

[0012] A second aspect of the present invention provides a data warehouse association link cleaning device, comprising: a data acquisition module for acquiring a task path set and a preset function name matching rule; a script retrieval module for performing script retrieval on the task path set based on the function name matching rule to obtain multiple function call line data; a noise reduction and deduplication module for performing noise reduction and deduplication processing on each of the function call line data to obtain multiple task scheduling functions; an SQL lookup module for performing SQL content lookup in a preset data warehouse dictionary table based on the multiple task scheduling functions to obtain SQL definitions corresponding to each task scheduling function; an SQL definition processing module for performing character cleaning processing on each of the SQL definitions and sorting and deduplicating the multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function; and a data writing module for associating and binding the task path set, the multiple task scheduling functions, the multiple sets of data source tables, and the multiple sets of cleaning target tables and writing them into a lineage table to obtain a standardized lineage table.

[0013] A third aspect of the present invention provides a data warehouse association link cleaning device, the data warehouse association link cleaning device comprising: a memory and at least one processor, the memory storing instructions; the at least one processor invokes the instructions in the memory to cause the data warehouse association link cleaning device to perform each step of the data warehouse association link cleaning method described in any of the preceding claims.

[0014] A fourth aspect of the present invention provides a computer-readable storage medium storing instructions that, when executed by a processor, implement the steps of the data warehouse association link cleaning method described in any of the preceding claims.

[0015] In the technical solution of this invention, a task path set and a preset function name matching rule are first obtained. Then, based on the function name matching rule, a script retrieval is performed on the task path set to obtain multiple function call line data. Each function call line data is then processed for noise reduction and deduplication to obtain multiple task scheduling functions. Next, based on the multiple task scheduling functions, an SQL content search is performed in a preset data warehouse dictionary table to obtain the SQL definition corresponding to each task scheduling function. Each SQL definition is then processed for character cleaning and sorting for deduplication to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function. Finally, the task path set, multiple task scheduling functions, multiple data source table sets, and multiple cleaning target table sets are associated and bound and written into a lineage table to obtain a standardized lineage table. This aims to improve the efficiency of data warehouse lineage sorting and enable convenient and fast querying of various relationships. Attached Figure Description

[0016] Figure 1 This is a first flowchart of a data warehouse association link cleaning method provided in an embodiment of the present invention; Figure 2 This is a second flowchart of the data warehouse association link cleaning method provided in an embodiment of the present invention; Figure 3 This is a third flowchart of the data warehouse association link cleaning method provided in the embodiments of the present invention; Figure 4 This is a fourth flowchart of the data warehouse association link cleaning method provided in the embodiments of the present invention; Figure 5 This is a fifth flowchart of the data warehouse association link cleaning method provided in the embodiments of the present invention; Figure 6 This is a sixth flowchart of the data warehouse association link cleaning method provided in the embodiments of the present invention; Figure 7 This is the seventh flowchart of the data warehouse association link cleaning method provided in the embodiments of the present invention; Figure 8 This is a schematic diagram of the data warehouse association link cleaning device provided in an embodiment of the present invention; Figure 9 This is a schematic diagram of the data warehouse association link cleaning device provided in an embodiment of the present invention. Detailed Implementation

[0017] This invention provides a method, apparatus, device, and storage medium for cleaning data warehouse association links. In this invention, the terms "first," "second," "third," "fourth," etc. (if applicable) in the specification, claims, and accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such use of data can be interchanged where appropriate so that the embodiments described herein can be implemented in a sequence other than that illustrated or described herein. Furthermore, the terms "comprising" or "having" and any variations thereof are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or devices.

[0018] For ease of understanding, the specific process of the embodiments of the present invention is described below. Please refer to [link / reference]. Figure 1 One embodiment of the data warehouse association link cleaning method in this invention includes: 101. Obtain the task path set and preset function name matching rules; 102. Based on the function name matching rules, perform script retrieval on the task path set to obtain multiple function call line data; In this embodiment, the task path set is obtained using the scheduling system task table as the data source. First, a complete list of all valid tasks within the system is extracted. Each task name in the list is traversed sequentially, and the total path of the server-side task storage is concatenated with the task name to generate the physical storage path of each valid task on the server, thus constructing a complete and non-redundant task path set. The preset function name matching rules are built around the retrieval requirements of task-related functions. The core setting uses a combination of `select` followed by a whitespace character and any character followed by `sp` as the search keyword, clearly defining the standard and scope boundaries of function name retrieval. Based on these function name matching rules, for each task in the task path set, script files with the `.sh` extension are located at their corresponding physical storage locations. The `egrep` tool is used to perform keyword retrieval operations in the script files, filtering out lines of data that match the matching rules. These lines of data are the function call lines. `egrep` is a commonly used text retrieval tool in Linux systems; its core function is to search for content matching a preset regular expression in a specified file and output the entire matched line of data. By systematically constructing a task path set, we ensure that the storage locations of all valid tasks in the current system are covered, avoiding incomplete extraction of function association information due to task omissions. Relying on standardized function name matching rules to execute script retrieval replaces the inefficient method of manually scanning scripts line by line, greatly improving the efficiency and accuracy of function call line data extraction, and effectively avoiding omissions or misjudgments that are prone to occur in manual operations.

[0019] 103. Perform noise reduction and deduplication processing on each of the function call lines to obtain multiple task scheduling functions; In this embodiment, each function call line data undergoes denoising and deduplication processing. Specifically, this includes removing lines containing comment identifiers to eliminate interference from comment content in function extraction, and performing field segmentation to locate and extract function names, thus completing the denoising process. Then, a sorting operation is performed on the set of function names obtained after denoising to further eliminate duplicate function name records within the same task, ultimately obtaining the task scheduling function corresponding to each task. This achieves a complete removal of various redundant and interfering information from the function call line data, ensuring the high accuracy of the extracted task scheduling functions. At the same time, the sorting and deduplication operation ensures the uniqueness and simplicity of the function list corresponding to each task, completely replacing the inefficient method of manual line-by-line screening and deduplication, significantly improving function extraction efficiency, effectively avoiding omissions or errors that may occur during manual operation, and significantly enhancing the reliability and standardization of the overall data warehouse lineage cleaning work.

[0020] 104. Based on the multiple task scheduling functions, perform SQL content lookup in the preset data warehouse dictionary table to obtain the SQL definition corresponding to each task scheduling function; In this embodiment, for each extracted task scheduling function, a traversal process is performed sequentially by function name. Using the function name as the core search condition, a query operation is performed in the preset Greenplum data warehouse dictionary table. The Greenplum data warehouse dictionary table, as a dedicated table for storing function metadata, completely retains the structured SQL content corresponding to each function. By matching the task scheduling function name with the function identifier in the dictionary table, the complete SQL definition corresponding to each task scheduling function can be located and extracted. The metadata storage characteristics of the data warehouse's native dictionary table replace the inefficient operation of manually logging into the database and searching for function SQL content line by line. This ensures the originality and completeness of the obtained SQL definitions and avoids irrelevant result interference caused by fuzzy queries through function name matching, significantly improving the efficiency and accuracy of SQL definition extraction. Simultaneously, the complete and accurate SQL definitions provide a reliable data foundation for subsequent parsing of source tables, target tables, and other lineage information from the statements, effectively ensuring the continuity of the data warehouse lineage analysis process and the accuracy of the results.

[0021] 105. Perform character cleaning processing on each of the SQL definitions, and sort and deduplicate multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function; In this embodiment, character cleaning is performed on each SQL definition. First, comment lines in the SQL definition are removed to eliminate interference from non-core statements in table name extraction. Then, row data containing data related to the data source table and row data containing data related to the target table are retrieved. For the row data with data related to the data source table, content with standardized table name format is filtered and retained, and special characters and redundant association conditions are removed to extract the data source table name. For the row data with data related to the target table, irrelevant characters are stripped through multiple rounds of field splitting operations to extract and clean the target table name. After character cleaning, the extracted data source table names and cleaning target table names are sorted to eliminate duplicate records caused by multiple SQL statements calling the same table. This ensures that each table name is unique in its corresponding set, ultimately forming a data source table set and a cleaning target table set that correspond one-to-one with each task scheduling function. This effectively avoids table name recognition errors caused by interference factors such as comments, special characters, and redundant statements, ensuring the accuracy of table name extraction. The sorting and deduplication operation constructs a table set with a concise structure and unique elements, completely replacing the inefficient method of manually scanning SQL definitions line by line to extract table names and manually deduplicating them, significantly improving the efficiency and accuracy of table set extraction.

[0022] 106. Associate and bind the task path set, multiple task scheduling functions, multiple data source table sets, and multiple cleaning target table sets and write them into the lineage table to obtain a standardized lineage table.

[0023] In this embodiment, based on the core task attributes extracted from the task path set parsing, and combined with the corresponding task scheduling function, data source table set, and cleaning target table set, a structured association binding is performed to construct a complete association model covering the task, function, and table dimensions. This model incorporates core information such as task name, task frequency, function name, data source table name, and cleaning target table name. All data from this association model is written into a pre-defined lineage table according to a unified standard. Through standardized data format processing and solidification of association relationships, a standardized lineage table with complete fields, clear relationships, and traceability is ultimately formed. Based on this, front-end technology visualizes the full data of the standardized lineage table on an interactive page, and precise search conditions are configured for core fields such as task name, task frequency, function name, data source table name, and cleaning target table name, supporting multi-dimensional association query operations. This processing method integrates scattered task, function, and table sets into a structured, standardized lineage table, replacing the inefficient traditional method of manually sorting out multi-dimensional relationships, ensuring the integrity and consistency of lineage data. The implementation of front-end visualization and precise retrieval capabilities allows developers to quickly obtain the relationships between tasks, functions, and tables without having to use cumbersome methods such as fuzzy dictionary queries or logging into servers to view scripts, significantly reducing the time cost of lineage queries and improving the efficiency of data governance.

[0024] Building upon this foundation, new dynamic dependency analysis and intelligent optimization suggestion functions have been added, achieving a full-chain upgrade from static lineage records to dynamic risk management and intelligent performance optimization. The dynamic dependency analysis function, based on the static relationships in the standardized lineage table, synchronously collects historical task execution logs to deeply mine the dynamic dependency logic between tasks, preserving both static lineage relationships and supplementing dependency relationships during dynamic execution. Based on the collected dynamic dependency data, a dependency graph is automatically constructed, using graph theory algorithms such as topology sorting and critical path analysis to identify potential deadlock risks during task execution. By statistically analyzing historical task execution time, resource consumption data, and failure records, a task execution time window model is constructed. Combined with the dependency graph, the likelihood of task execution conflicts is predicted. Jobs with high-risk dependencies are automatically marked with potential deadlock risks, and an assessment report including risk level and impact scope is generated. Simultaneously, based on task scheduling frequency and resource consumption, dependency priorities are dynamically adjusted to avoid deadlock issues. The intelligent optimization suggestion function, relying on the relationships in the standardized lineage table and integrating historical execution performance data, achieves automated optimization identification and suggestion generation for functions, tables, and tasks. A dedicated performance metric database is built to comprehensively record core metrics such as execution time, resource consumption, and data volume processed for each function. Through multi-dimensional correlation analysis, functions with low execution efficiency, such as those whose execution time exceeds twice the average, are identified. Based on lineage relationships, the impact of inefficient functions on downstream data tables and tasks is traced, prioritizing functions with small impact ranges and significant optimization benefits as optimization targets. Highly targeted optimization solutions are also provided, such as suggesting partition optimization for specific functions, which is expected to improve execution efficiency by 30%. Simultaneously, table quality metric data is combined to analyze the impact path of function execution logic on data quality, providing corresponding data quality optimization suggestions to achieve dual optimization of performance and quality. Extended functionality significantly enhances the practical value of standardized lineage tables. The dynamic dependency analysis module replaces the tedious manual process of checking for deadlock risks, using algorithmic and model-based methods to achieve early warning and dynamic control of risks, ensuring the stability and continuity of task scheduling. The intelligent optimization suggestion function avoids the blindness of manually selecting optimization targets and exploring optimization solutions, providing developers with well-supported and directly implementable optimization directions, significantly reducing optimization costs and improving data warehouse execution performance.

[0025] Please see Figure 2 In this embodiment of the invention, obtaining the task path set includes: 201. Obtain the system task table and multiple physical paths; 202. Perform valid task filtering on the system task table to obtain multiple valid task names; 203. Concatenate each valid task name with its corresponding physical path to generate the physical storage path for each valid task, and construct a task path set based on the generated multiple physical storage paths.

[0026] In this embodiment, the system task table originates from the task list stored in the scheduling system, and the physical paths correspond to the pre-defined total task storage paths on the server side. First, the system task table undergoes a valid task filtering process, extracting task records that are currently running normally and have no invalid or obsolete flags, resulting in multiple valid task names. Invalid tasks are removed to avoid redundant data interference. Then, based on the server task storage specifications, each physical path is paired with each valid task name, and the precise storage location of each valid task on the server is generated through path concatenation. All paired storage locations are integrated to form a complete task path set. Valid task filtering ensures that the path set only covers active tasks, avoiding resource waste and data interference caused by invalid tasks. Replacing manual task location concatenation with standardized path pairing logic significantly improves the efficiency and accuracy of task path set construction, ensuring that the storage location of each valid task can be accurately located.

[0027] Please see Figure 3 In this embodiment of the invention, the step of performing noise reduction and deduplication processing on each function call line data to obtain multiple task scheduling functions includes: 301. Use the sed tool to remove comment lines from each of the function call lines to obtain multiple function code line data; 302. Use the awk tool to perform field segmentation and extraction on the code line data of each function to obtain multiple function names; 303. Use the awk tool to sort and remove duplicates from all the extracted function names to obtain multiple task scheduling functions.

[0028] In this embodiment, the sed tool is a non-interactive editing tool for character streams in the Linux system. Its core function is to perform lightweight editing operations such as deletion, replacement, and filtering on text lines. The awk tool is a text processing language based on pattern matching. It has the core capability to split, extract, and format text fields according to specified delimiters and is a commonly used tool for structured parsing of text data. For each function call line data, the sed tool is first used to remove comment lines, eliminating interference from comment content and resulting in function code line data containing only the core code content. Then, the awk tool is used to perform field segmentation and extraction on the function code line data. The text is split using parentheses (), and the first field is extracted to remove redundant content such as function call parameters. The awk tool is then used again to split the extracted results using periods (.) as delimiters, extracting the second field to locate the function name. After extracting the function names, all extracted function names are sorted and deduplicated to eliminate duplicate function name records within the same task. Finally, the task scheduling function corresponding to each task is obtained. This process replaces the inefficient manual operation of deleting comments line by line and splitting text to extract function names, significantly improving function extraction efficiency. The sorting and deduplication operation ensures the uniqueness and simplicity of the task scheduling function list, avoiding omissions or duplications that are prone to occur during manual deduplication, and ensuring the accuracy of the function list corresponding to each task.

[0029] Please see Figure 4 In this embodiment of the invention, the step of performing an SQL content lookup in a preset data warehouse dictionary table based on multiple task scheduling functions to obtain the SQL definition corresponding to each task scheduling function includes: 401. Construct SQL query statements based on each of the aforementioned task scheduling functions; 402. Based on multiple SQL query statements, perform SQL content lookup in the data warehouse dictionary table to obtain a query result set corresponding to each task scheduling function; 403. Use the sed tool to remove redundant characters from each of the query result sets to obtain the SQL definition corresponding to each of the task scheduling functions.

[0030] In this embodiment, for each task scheduling function, a unique SQL query statement is constructed based on the query syntax specification of the data warehouse dictionary table. The query statement uses the function name as the core search condition to ensure accurate matching with the function metadata in the dictionary table. Multiple constructed SQL query statements are sequentially executed in the preset data warehouse dictionary table to extract the full metadata information associated with each task scheduling function, forming a query result set for each function. This result set includes the SQL content corresponding to the function and redundant metadata characters from the dictionary table itself. Subsequently, the sed tool is used to perform redundant character removal processing on each query result set. The sed tool uses character matching and deletion logic to filter out non-core SQL content in the query result set, such as dictionary table field identifiers, format specifiers, redundant comments, and other irrelevant characters, retaining only the core SQL statements that fully represent the function logic, ultimately obtaining the standardized SQL definition corresponding to each task scheduling function. By constructing SQL query statements in a structured manner, the arbitrary nature of manually writing query statements is replaced, ensuring the accuracy of query conditions and avoiding omissions or errors caused by non-standard query statements. Retrieving data from a dictionary table based on standardized query statements ensures a one-to-one correspondence between the query result set and the task scheduling function, improving the accuracy of SQL content extraction. The sed tool's automated removal of redundant characters replaces the inefficient manual deletion of irrelevant characters line by line, significantly improving the efficiency of SQL definition extraction while avoiding character omissions or accidental deletions that are prone to occur during manual operations.

[0031] Please see Figure 5 In this embodiment of the invention, the step of performing character cleaning processing on each of the SQL definitions and sorting and deduplicating multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function includes: 501. Use the sed tool to delete comment lines from each of the SQL definitions to obtain multiple valid SQL code lines; 502. Perform table name retrieval and extraction processing on each of the valid SQL code lines to obtain the data source table name set and the cleaning target table name set; 503. Use the awk tool to sort and deduplicate the data source table name set and the cleaning target table name set respectively, to obtain the data source table set and the cleaning target table set corresponding to each task scheduling function.

[0032] In this embodiment, for the SQL definition corresponding to each task scheduling function, the sed tool is used to scan and delete comment lines line by line, removing SQL lines containing comment identifiers, eliminating interference from non-executable statements on table name extraction, and retaining only valid SQL code lines with actual execution logic. This replaces the inefficient manual line-by-line deletion of comments, ensuring the purity of valid SQL code lines. Based on the syntactic features of data warehouse table associations, table name retrieval and extraction are performed on each valid SQL code line. Table names matching the association features of the data source table are identified and extracted, forming a set of data source table names; simultaneously, table names matching the association features of the cleaning target table are identified and extracted, forming a set of cleaning target table names. Through structured table name retrieval and extraction logic, the data source table and the cleaning target table are distinguished, avoiding the confusion that easily occurs when manually identifying table types. Subsequently, the awk tool was used to sort and deduplicate the data source table name set and the target table name set for cleaning. The table name sequence was sorted through standardized sorting logic, and duplicate table name records were filtered out. Finally, a data source table set and a target table set for cleaning with a well-organized structure and unique elements were obtained for each task scheduling function. This replaced the manual operation of filtering duplicate table names, greatly improving the efficiency and accuracy of table set organization and ensuring that there are no redundant records in the table set.

[0033] Please see Figure 6 In this embodiment of the invention, the step of performing table name retrieval and extraction processing on each of the valid SQL code lines to obtain a set of data source table names and a set of cleaned target table names includes: 601. Use the egrep tool to perform data source table association row retrieval processing on each of the valid SQL code lines to obtain multiple data source table association rows; 602. Obtain the preset standardized table name format, and use the egrep tool to perform table name format filtering on the associated rows of each data source table based on the standardized table name format to obtain multiple data source table name contents; 603. Use the sed tool to remove special characters from the content of each data source table name to obtain the set of data source table names; 604. Use the egrep tool to locate the target line for each of the valid SQL code lines to obtain multiple target lines; 605. Obtain the preset first delimiter and second delimiter, and use the awk tool to extract the table name content of each target row based on the first delimiter to obtain multiple cleaned target table name contents; 606. Using the awk tool, extract the table name content of each of the cleaning target table names based on the second delimiter to obtain the set of cleaning target table names.

[0034] In this embodiment, the egrep tool is the core tool for structured extraction of target text content. For each valid SQL line, egrep first performs a data source table join row retrieval process. Based on the feature matching rules that include "from" or "join" followed by a schema prefix, rows carrying the data source table join logic are filtered out from the valid SQL lines, resulting in multiple data source table join rows. Then, a preset standardized table name format is retrieved. This format uses any non-whitespace character after the schema as the core matching feature. egrep then performs table name format filtering on each data source table join row, retaining only content fragments that conform to the standardized table name format, resulting in multiple data source table name contents. Next, the sed tool is used to perform special character removal processing on each data source table name content, replacing the right parenthesis and all characters to its right, semicolons, and other non-core special characters with whitespace characters, stripping away irrelevant and interfering characters, and finally obtaining a set of data source table names with a clean structure. By leveraging the retrieval capabilities of the egrep tool to replace the inefficient manual process of identifying related rows in the data source table line by line, the extraction of related rows from the data source table is ensured to be targeted and complete. Through filtering with standardized table name formats, the identification bias caused by non-standardized table name formats is avoided. In addition, the special character removal process automates the cleaning of table name content, replacing the tedious manual deletion of special characters. This significantly improves the efficiency and accuracy of extracting data source table name sets and effectively avoids omissions or errors that are prone to occur in manual operations.

[0035] In this embodiment, for each valid SQL line, the egrep tool is first used to locate the target line. Based on feature matching rules that include "into" and are connected by schema prefixes, the line data carrying the logic of cleaning the target table associations is filtered out from the valid SQL lines, resulting in multiple target lines. Then, the preset first and second delimiters are retrieved, where the first delimiter is "into" and the second delimiter is "|(". The awk tool is used to extract the table name content from each target line. The text is split based on the first delimiter and the second field is extracted. Redundant prefix content is removed, resulting in multiple cleaned target table name contents. Then, the awk tool is used again, with the second delimiter as the delimiter. Based on the table name extraction process performed on the content of each target table for cleaning, the text is split and the first field is extracted to locate the core target table name. Finally, the extraction results are sorted and deduplicated to obtain a set of target table names with a well-structured and unique elements. Utilizing the retrieval capabilities of the egrep tool, the inefficient operation of manually identifying related rows of the target table is replaced, ensuring the targeting and completeness of the target row extraction. By using preset double delimiters and employing the awk tool for hierarchical field extraction, the structured parsing of the target table names is achieved, avoiding the biases and omissions that are prone to occur when manually splitting text and identifying table names. The sorting and deduplication operation ensures the uniqueness and simplicity of the target table name set, significantly improving the efficiency and accuracy of table name extraction.

[0036] Please see Figure 7 In this embodiment of the invention, after associating and binding the task path set, multiple task scheduling functions, multiple data source table sets, and multiple cleaning target table sets and writing them into the lineage table to obtain a standardized lineage table, the method further includes: 701. Obtain a set of table quality indicators based on multiple sets of data source tables and multiple sets of cleaning target tables; 702. Construct an association mapping table based on the set of quality indicators and the standardized kinship table; 703. Obtain preset anomaly judgment rules, and perform anomaly index analysis and processing on the association mapping table based on the anomaly judgment rules to obtain a quality anomaly source set; 704. Generate an optimization suggestion report based on the aforementioned quality anomaly source set.

[0037] In this embodiment, after completing the association and binding of the task path set, task scheduling function, data source table set, and cleaning target table set, and generating a standardized lineage table, the core quality indicators corresponding to each table are first extracted from the data quality monitoring system based on the data source table set and the cleaning target table set. These indicators cover dimensions such as null value rate, duplication rate, and outlier ratio, and are integrated to form a structured set of table quality indicators. Subsequently, this set of table quality indicators is deeply associated with the standardized lineage table, binding the table quality indicators with the lineage relationships between tasks, functions, and tables. This constructs an association mapping table that combines quality and lineage dimensions, fully presenting the correspondence between the quality indicators of each table and the upstream task scheduling function and task path. Next, the pre-defined anomaly detection rules are retrieved. These rules cover static threshold standards for core quality indicators such as null value rate, duplication rate, and outlier ratio, as well as dynamic trend judgment standards for table quality indicators over time. Specifically, dynamic rules are applied when the increase in quality indicators exceeds a preset proportion over multiple consecutive statistical periods. Based on these rules, a systematic anomaly analysis is performed on the quality indicators in the associated mapping tables. Anomalies exceeding the pre-defined static thresholds or meeting the dynamic trend anomaly conditions are identified. The upstream task scheduling functions and task paths corresponding to the anomalies are automatically traced, forming a quality anomaly source set containing anomaly table information, anomaly indicator data, associated functions, and task information. Finally, based on the quality anomaly source set, combined with the time-varying trends of table quality indicators and the correlation analysis results with the execution behavior of related functions, a targeted optimization suggestion report is generated. This clarifies the core source of quality problems and corresponding optimization directions, effectively enhancing the overall governance capabilities of the data warehouse, promoting a shift in data quality control from passive investigation to proactive early warning, and ensuring the accuracy and availability of data warehouse data.

[0038] The data warehouse association link cleaning method in the embodiments of the present invention has been described above. The data warehouse association link cleaning apparatus in the embodiments of the present invention will be described below. Please refer to [link to relevant documentation]. Figure 8 One embodiment of the data warehouse association link cleaning device in this invention includes: Data acquisition module 801: Used to acquire task path sets and preset function name matching rules; Script retrieval module 802: used to perform script retrieval on the task path set based on the function name matching rules, and obtain multiple function call line data; Denoising and deduplication module 803: used to perform denoising and deduplication processing on each of the function call line data to obtain multiple task scheduling functions; SQL lookup module 804: used to perform SQL content lookup in a preset data warehouse dictionary table based on multiple task scheduling functions, and obtain the SQL definition corresponding to each task scheduling function; SQL definition processing module 805: used to perform character cleaning processing on each of the SQL definitions respectively, and sort and deduplicate multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each of the task scheduling functions; Data writing module 806: used to associate and bind the task path set, multiple task scheduling functions, multiple data source table sets and multiple cleaning target table sets and write them into the lineage table to obtain a standardized lineage table.

[0039] Based on the same ideas as the methods in the above embodiments, the apparatus provided in this application can implement the methods in the above embodiments.

[0040] above Figure 8 The data warehouse association link cleaning device in this embodiment of the invention is described in detail from the perspective of modular functional entities. The data warehouse association link cleaning device in this embodiment of the invention is described in detail from the perspective of hardware processing.

[0041] Figure 9 This is a schematic diagram of a data warehouse link cleaning device 900 provided in an embodiment of the present invention. The data warehouse link cleaning device 900 can vary significantly due to different configurations or performance. It may include one or more central processing units (CPUs) 910 (e.g., one or more processors) and a memory 920, and one or more storage media 930 (e.g., one or more mass storage devices) for storing application programs 933 or data 932. The memory 920 and storage media 930 can be temporary or persistent storage. The program stored in the storage media 930 may include one or more modules (not shown in the diagram), each module including a series of instruction operations on the data warehouse link cleaning device 900. Furthermore, the processor 910 may be configured to communicate with the storage media 930 and execute the series of instruction operations in the storage media 930 on the data warehouse link cleaning device 900 to implement the steps of the data warehouse link cleaning method provided in the above-described method embodiments.

[0042] The data warehouse link cleaning device 900 may also include one or more power supplies 940, one or more wired or wireless network interfaces 950, one or more input / output interfaces 960, and / or one or more operating systems 931, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc. Those skilled in the art will understand that... Figure 9The data warehouse association link cleaning device structure shown does not constitute a limitation on the data warehouse association link cleaning device. It may include more or fewer components than shown, or combine certain components, or have different component arrangements.

[0043] The present invention also provides a computer-readable storage medium, which can be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium, wherein the computer-readable storage medium stores instructions that, when executed on a computer, cause the computer to perform the steps of the data warehouse association link cleaning method.

[0044] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working process of the system, device, or unit described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0045] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0046] Finally, it should be noted that the above descriptions are merely preferred embodiments of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A method for cleaning data warehouse association links, characterized in that, include: Get the task path set and the preset function name matching rules; Based on the function name matching rules, a script retrieval is performed on the task path set to obtain multiple function call line data; Each function call line data is processed to remove noise and duplicates, resulting in multiple task scheduling functions; Based on the multiple task scheduling functions, SQL content is searched in a preset data warehouse dictionary table to obtain the SQL definition corresponding to each task scheduling function; Each of the SQL definitions is subjected to character cleaning processing, and multiple SQL definitions are sorted and deduplicated to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function; The task path set, multiple task scheduling functions, multiple data source table sets, and multiple cleaning target table sets are associated and bound together and written into the lineage table to obtain a standardized lineage table.

2. The data warehouse association link cleaning method according to claim 1, characterized in that, The set of task paths to be acquired includes: Obtain the system task table and multiple physical paths; The system task table is filtered to obtain multiple valid task names; Each valid task name is concatenated with its corresponding physical path to generate a physical storage path for each valid task, and a task path set is constructed based on the generated multiple physical storage paths.

3. The data warehouse association link cleaning method according to claim 1, characterized in that, The process of denoising and deduplicating each function call line data yields multiple task scheduling functions, including: The sed tool was used to remove comment lines from each function call line data to obtain multiple function code line data. The awk tool was used to perform field segmentation and extraction on each line of code data of the function, resulting in multiple function names; The awk tool was used to sort and remove duplicates from all the extracted function names to obtain multiple task scheduling functions.

4. The data warehouse association link cleaning method according to claim 1, characterized in that, The step of searching for SQL content in a preset data warehouse dictionary table based on multiple task scheduling functions to obtain the SQL definition corresponding to each task scheduling function includes: Construct SQL query statements based on each of the aforementioned task scheduling functions; Based on multiple SQL query statements, the SQL content is searched in the data warehouse dictionary table to obtain a query result set corresponding to each task scheduling function; The sed tool is used to remove redundant characters from each query result set to obtain the SQL definition corresponding to each task scheduling function.

5. The data warehouse association link cleaning method according to claim 1, characterized in that, The step of performing character cleaning processing on each of the SQL definitions and sorting and deduplicating multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function includes: The sed tool was used to remove comment lines from each of the SQL definitions, resulting in multiple valid SQL code lines. Each valid SQL line is processed by table name retrieval and extraction to obtain a set of data source table names and a set of cleaned target table names. The awk tool is used to sort and deduplicate the data source table name set and the cleaning target table name set respectively, to obtain the data source table set and the cleaning target table set corresponding to each task scheduling function.

6. The data warehouse association link cleaning method according to claim 5, characterized in that, The step of performing table name retrieval and extraction processing on each of the valid SQL code lines to obtain a set of data source table names and a set of cleaned target table names includes: The egrep tool was used to perform data source table association row retrieval processing on each of the valid SQL code lines to obtain multiple data source table association rows; Obtain the preset standardized table name format, and use the egrep tool to filter the table name format of each data source table's associated row based on the standardized table name format to obtain multiple data source table name contents; The sed tool was used to remove special characters from the content of each data source table name to obtain the set of data source table names. The egrep tool was used to locate the target line for each of the valid SQL lines, resulting in multiple target lines. Obtain a preset first delimiter and second delimiter, and use the awk tool to extract table name content from each target row based on the first delimiter to obtain multiple cleaned target table name contents; The awk tool is used to extract the table name content of each of the cleaning target table names based on the second delimiter, so as to obtain the set of cleaning target table names.

7. The data warehouse association link cleaning method according to claim 1, characterized in that, After associating and binding the task path set, multiple task scheduling functions, multiple data source table sets, and multiple cleaning target table sets and writing them into the lineage table to obtain a standardized lineage table, the process further includes: A set of table quality indicators is obtained based on multiple sets of data source tables and multiple sets of cleaning target tables; A correlation mapping table is constructed based on the set of quality indicators in the table and the standardized kinship table; Obtain preset anomaly judgment rules, and perform anomaly index analysis on the association mapping table based on the anomaly judgment rules to obtain a quality anomaly source set; An optimization suggestion report is generated based on the aforementioned quality anomaly source set.

8. A data warehouse association link cleaning device, characterized in that, include: Data acquisition module: used to acquire task path sets and preset function name matching rules; Script retrieval module: used to perform script retrieval on the task path set based on the function name matching rules, and obtain multiple function call line data; Denoising and deduplication module: used to perform denoising and deduplication processing on each of the function call lines to obtain multiple task scheduling functions; SQL lookup module: used to perform SQL content lookup in a preset data warehouse dictionary table based on multiple task scheduling functions, and obtain the SQL definition corresponding to each task scheduling function; SQL definition processing module: used to perform character cleaning processing on each of the SQL definitions, and sort and deduplicate multiple SQL definitions to obtain a set of data source tables and a set of cleaning target tables corresponding to each task scheduling function; Data writing module: used to associate and bind the task path set, multiple task scheduling functions, multiple data source table sets and multiple cleaning target table sets and write them into the lineage table to obtain a standardized lineage table.

9. A data warehouse association link cleaning device, characterized in that, The data warehouse association link cleaning device includes: a memory and at least one processor, wherein the memory stores instructions; At least one of the processors invokes the instructions in the memory to cause the data warehouse association link cleaning apparatus to perform the steps of the data warehouse association link cleaning method as described in any one of claims 1-7.

10. A computer-readable storage medium storing instructions thereon, characterized in that, When the instructions are executed by the processor, they implement the various steps of the data warehouse association link cleaning method as described in any one of claims 1-7.

Images