Method for realizing table-level blood relationship information construction based on task running log

Abstract

The invention discloses a method for realizing table-level blood relationship information construction based on a task running log, which comprises the following steps of: S1) collecting and acquiring task running log data in a system, and analyzing the log to acquire a database operation statement; s2) analyzing the obtained log file, and extracting a database operation statement in the log file; s3) screening the database operation statements obtained by analysis in combination with a job operation record so as to determine target operation statements related to data blood relationship construction; s4) establishing a mapping relation between a network protocol address and a host name, and further analyzing a mapping relation from the source table to the target table; s5, the screened target operation statements are used for constructing the blood relationship between the data tables. By deeply analyzing the task running logs, key data operation can be automatically recognized, the blood relationship between the data tables is constructed, and therefore the efficiency and accuracy of data blood relationship analysis are remarkably improved.

Description

Technical Field

[0001] The present invention relates to a method for constructing blood relationship information, and in particular to a method for constructing table-level blood relationship information based on task operation logs. Background Art

[0002] The prior art provides a data lineage relationship analysis method and system based on metadata (patent document number: CN110555032A). This method defines the lexical rules and grammatical rules of SQL through an open source parser, converts SQL into an abstract syntax tree, and then transforms it through the logical layer and physical layer optimizer to generate the final execution plan, thereby analyzing the input and output tables, fields and corresponding processing conditions. This method can effectively sort out the relationships between data tables and fields.

[0003] Another existing technology is a data task analysis method, system and medium based on data lineage. When a data task is executed, the technology analyzes the data lineage relationship, establishes a mapping relationship and saves it. When the metadata information changes, the system can compare the data lineage mapping relationship of the current task, mark the changed content, and synchronize the data task information related to the change. This helps solve the problem of manually analyzing the impact of data tasks when data lineage changes.

[0004] In the current era of big data, data accuracy and traceability are crucial to ensuring data quality and business decision-making. However, existing technologies have obvious deficiencies in automatically building data lineage relationships, especially in scenarios where large-scale or complex data streams are processed. These methods often show low automation, insufficient accuracy, and poor adaptability. Summary of the invention

[0005] The technical problem to be solved by the present invention is to provide a method for constructing table-level blood relationship information based on task operation logs, which can efficiently and accurately construct table-level data blood relationship from task operation logs.

[0006] The technical solution adopted by the present invention to solve the above-mentioned technical problems is to provide a method for realizing table-level blood relationship information construction based on task operation logs, comprising the following steps: S1) collecting and acquiring task operation log data in the system, parsing the logs to acquire database operation statements; S2) parsing the acquired log files, and extracting the database operation statements therein; S3) combining the job operation records, filtering the parsed database operation statements to determine the target operation statements related to the data blood relationship construction; S4) establishing a mapping relationship between the network protocol address and the host name, and further analyzing the mapping relationship from the source table to the target table; S5) using the filtered target operation statements to construct the blood relationship between the data tables.

[0007] Furthermore, the task running log data in step S1 is in plain text format or JSON format, including date, time, log level and log content.

[0008] Furthermore, step S3 automatically identifies and configures batch dependencies of data processing tasks based on the parsed database operation statements and metadata, generates batch scheduling information, and specifies the execution order, time, and required resources of each data processing task.

[0009] Furthermore, step S2 uses regular expressions or string processing to separate database operation statements from job running records. After step S3 completes preliminary screening, step S4 performs the following precise screening in combination with the mapping relationship: establishing a mapping relationship between the network protocol address and the host name through the DNS system, hosts file and / or network configuration file; while parsing the database operation statement, recording the source information of the operation statement, including the client IP address and host name that initiated the operation; matching the source information of the operation statement with the mapping relationship between the network protocol and the host name; and further screening the target operation statement in combination with the source information of the operation statement according to the requirements of data lineage relationship construction.

[0010] Furthermore, the step S5 includes: S51, determining the data type that needs to be managed and defining metadata attributes; S52, identifying the data source, and using metadata extraction tools or services to automatically collect metadata from the data source; S53, filtering out statements related to the target operation from the collected metadata; and parsing the filtered operation statements to identify the dependencies between data tables and the data flow path; S54, determining the blood relationship type and the design diagram model; S55, implementing an algorithm to track the data flow path based on the parsed operation statements and design diagram model; determining the complete path from one data element to another, including all intermediate steps and transformations; S56, using a graph database or graph processing framework to store blood relationships and provide a query mechanism; S57, monitoring changes in the data source in real time or periodically, and updating the blood relationship graph model and stored data in a timely manner based on the monitored changes.

[0011] Furthermore, the data types in step S51 include database tables, fields, views and stored procedures, and the metadata attributes include name, description, data type, source and destination.

[0012] Furthermore, the blood relationship types in step S54 include upstream / downstream relationships, parent-child relationships, and dependency relationships, and the design graph model is a directed graph, an undirected graph, or a mixed graph.

[0013] Furthermore, it also includes step S6: constructing a terminal table, and filtering and verifying the established blood relationship through the terminal table; the terminal table is the final output table or target table in the data flow, the terminal table receives data from the upstream table, and serves as the end point of the data blood relationship chain.

[0014] Furthermore, the step S6 includes: S61, selecting the final output table or target table in the data flow as the terminal table, the terminal table receives data from the upstream table and serves as the end point of the data lineage chain; S62, identifying all terminal tables according to the data flow diagram and metadata management information; collecting attribute information of the terminal table, including table name, field name, data type and data format; S63, establishing terminal table filtering rules; S64, applying filtering rules for automatic filtering or manual verification; S65, verifying the accuracy of lineage relationships; S66, real-time or periodic monitoring of changes in data sources, and continuous optimization and update of filtering rules and verification methods based on verification results and business needs.

[0015] Furthermore, the filtering rules in step S63 include: data integrity rules: ensuring that the data in the terminal table is complete, without missing or duplicate data; data consistency rules: checking whether the data in the terminal table is consistent with the data in the upstream table, including field values, data types and data formats; data validity rules: verifying whether the data in the terminal table meets business rules and logical constraints; step S65 includes: tracing the data flow path: starting from the terminal table, tracing the data flow path upstream to verify the correctness of each data flow step; checking data conversion logic: verifying whether the conversion logic of the data during the flow process is correct, including field mapping and data calculation; comparing metadata with actual data: comparing the blood relationship in the metadata with the actual data flow path to ensure that the two are consistent.

[0016] Compared with the prior art, the present invention has the following beneficial effects: the method provided by the present invention for realizing table-level blood relationship information construction based on task operation logs can automatically identify key data operations and build blood relationship between data tables through in-depth analysis of task operation logs, thereby significantly improving the efficiency and accuracy of data blood relationship analysis. This not only reduces the burden on data analysts, but also enhances the transparency and reliability of the data processing process. The specific advantages are as follows: 1. Improve efficiency: Automated blood relationship construction greatly reduces manual intervention and shortens data processing time. 2. Enhance accuracy: By accurately identifying the flow of data, errors that may occur due to manual tracking are reduced. 3. Improve adaptability: The present invention can adapt to different data environments and structures, and has good versatility and flexibility. 4. Ensure data security: Accurate blood relationships help to quickly locate data problems and provide additional protection for data security. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 The present invention implements a flow chart for constructing table-level blood relationship information based on task operation logs. DETAILED DESCRIPTION

[0018] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0019] Figure 1 The present invention implements a flow chart for constructing table-level blood relationship information based on task operation logs.

[0020] The method for constructing table-level blood relationship information based on task operation logs provided by the present invention comprises the following steps: S1. Collection and analysis of log data: As the starting point of the whole method, we first need to collect and obtain the task operation log data in the system, which is the basis for subsequent analysis. Parsing the log to obtain database operation statements is a key step in understanding data flow.

[0021] S2, SQL parsing technology: Parse the SQL statements in the obtained logs to identify the operational relationships between data tables. This step is crucial for building data lineage relationships because it can provide direct contact information between data nodes.

[0022] S3. Combined with the job running records, the parsed database operation statements are screened to determine the target operation statements related to the construction of data lineage relationships. Automatic identification and configuration of lineage relationships: Based on the parsed database operation statements and metadata, the batch dependencies of data processing tasks are automatically identified and configured, and batch scheduling information is generated. Batch dependencies refer to the order and interdependence between different batches of data processing tasks in the data processing process. This relationship ensures that data can flow and process in the correct order and logic. When the system parses database operation statements and metadata, it can automatically identify these batch dependencies and generate batch scheduling information based on these relationships. Batch scheduling information is a detailed plan that specifies the key elements such as the execution order, time, and required resources of each data processing task. By generating batch scheduling information, the system can ensure that data processing tasks are executed according to the correct batch dependencies, thereby avoiding risks caused by configuration errors. This helps to improve the efficiency and accuracy of data processing and ensure the quality and consistency of data.

[0023] This is critical to avoid the risks associated with configuration errors.

[0024] S4. Establishing a mapping relationship between the network protocol address and the host name can further analyze the mapping relationship from the source table to the target table.

[0025] S5. Use the filtered target operation statements to build the blood relationship between data tables.

[0026] The method for constructing table-level blood relationship information based on task operation logs provided by the present invention not only solves the bottleneck problem in existing data processing, but also provides a more powerful and reliable tool for data management and analysis, and promotes the progress and innovation of data processing technology; please refer to Figure 1 The specific implementation of the present invention is as follows: 1. Get task running log: Currently supported task log file formats are plain text or JSON, containing information such as date, time, log level (such as INFO, ERROR, etc.), log content, etc. As the starting point of the entire method, you first need to collect and obtain the task running log data in the system, which is the basis for subsequent analysis.

[0027] 2. Parse the log to obtain database operation statements: Parse the obtained log file and extract the database operation statements. Examples of SQL statements in the log file are: 2024-04-18 19:42:22 [pool-1-thread-2] [INFO] SQL statement: -- insert governance task replace into task_trxxXX(instance_id,task_id,task_nNN,task_level,task_type,batch_no,task_state,error_content,duration,transfer_time,transfer_num,enter_time,dsjzx_taskid) select distinct c.instance_id, c.task_id, c.task_nNN, c.task_level, null as task_type, if(b.unit_code is not null,b.batch_no,a.batch_no)as batch_no, 'normal' as task_state, null as error_content, if(b.unit_code isnot null,timestampdiff(second,b.start_dt,b.complete_dt),a.duration) asduration, b.lastupd as transfer_time, b.unit_transfer_data_num as transfer_num, now() as enter_time, '202404181935' as dsjzx_taskid from XXXX_dataflCC_job_trigger_BCS a left join xxx_datastSS_unit_ABC b on a.job_code = b.unit_code inner join instance_xxxx c on a.job_code = c.task_id . These statements are the key to understanding the flow of data.

[0028] 3. Use job running records to filter operation statements: Combine job running records to filter the parsed database operation statements to determine the target operation statements related to data lineage relationship construction.

[0029] 4. Set the mapping relationship between network protocol and host name: In order to accurately identify and associate data operations, it is necessary to establish a mapping relationship between the network protocol address and the host name.

[0030] 4.1. Filtering Operation Statements 1) Collect job running records: Collect job running records from the job management system or log system.

[0031] These records should include information such as the time when the job was executed, who executed it, the operations performed (including database operation statements), and the job status.

[0032] 2) Parsing database operation statements: Extract database operation statements from job running records.

[0033] Use regular expressions or other string processing techniques to separate the action statements from the records.

[0034] 3) Preliminary screening: Formulate screening conditions based on the needs of building data lineage relationships.

[0035] For example, you may only be interested in certain types of operations (such as INSERT, UPDATE, DELETE, etc.), or only in operations on specific tables or fields.

[0036] Use the screening conditions to perform preliminary screening on the extracted database operation statements.

[0037] 4.2. Combine mapping relationships for accurate screening 1) Set the mapping relationship between network protocol and host name: Establish a mapping relationship between a network protocol address (such as an IP address) and a host name.

[0038] This can be achieved through the DNS system, hosts files, network configuration files, etc.

[0039] Ensure the accuracy and completeness of mapping relationships so that data operations can be accurately identified and associated in subsequent steps.

[0040] 2) Analyze and record the source of the operation: While parsing database operation statements, the source information of the operation statements is recorded.

[0041] This may include the client IP address, host name, etc. that initiated the operation.

[0042] 3) Matching mapping relationship: Match the source information of the operation statement with the mapping relationship between the network protocol and the host name.

[0043] Determine which host or client initiated the operation statement.

[0044] 4) Further screening: According to the needs of building data lineage relationships, the target operation statements are further screened in combination with the source information of the operation statements.

[0045] For example, you might only be interested in statements from a specific host or client.

[0046] Build data lineage relationships based on target operation statements: Use the filtered operation statements to start building lineage relationships between data tables. Specific construction method: 5.1. Clarify objectives and scope 1) Determine the type of data that needs to be managed: This usually includes database tables, fields, views, stored procedures, etc.

[0047] 2) Define metadata attributes: Define attributes for these data types, such as name, description, data type, source, destination, etc.

[0048] 5.2 Extracting metadata 1) Data source identification: Determine which data sources the data comes from, such as databases, data warehouses, ETL tools, etc.

[0049] 2) Metadata collection: Use metadata extraction tools or services to automatically collect metadata from these data sources.

[0050] 5.3. Parsing operation statements 1) Filter operation statements: Filter statements related to the target operation from the collected metadata, such as specific SQL queries, stored procedure calls, etc.

[0051] 2) Parsing statements: Parse the filtered operation statements to identify the dependencies between data tables, data flow paths, etc.

[0052] 5.4 Design of blood relationship graph model 1) Determine the type of blood relationship: such as upstream / downstream relationship, father-child relationship, dependency relationship, etc.

[0053] 2) Design a graph model: Represent these relationships in a graphical way, using directed graphs, undirected graphs, or mixed graphs.

[0054] 5.5. Implementing a blood relationship tracking algorithm 1) Algorithm design: Based on the parsed operation statements and the designed graph model, implement the algorithm to track the flow path of data.

[0055] 2) Full path determination: Determine the complete path from one data element to another, including all intermediate steps and transformations.

[0056] 5.6. Storing and querying blood relationships 1) Choose storage technology: Use a graph database (such as Neo4j) or a graph processing framework (such as Apache Giraph, Spark GraphX) to store lineage relationships.

[0057] 2) Query and optimization: Implement an efficient query mechanism to quickly find the relationship between data tables. At the same time, optimize storage and query performance to handle large-scale data sets.

[0058] 5.7. Maintaining and updating blood relationships 1) Monitor data changes: Monitor changes in data sources in real time or regularly, such as new tables, fields, stored procedures, etc.

[0059] 2) Update blood relationship: Update the blood relationship graph model and stored data in a timely manner based on monitored changes.

[0060] 5.8 Application Kinship 1) Impact analysis: Use blood relationships to perform impact analysis, such as evaluating the impact of data changes on downstream applications.

[0061] 2) Data quality management: Identify data quality issues through lineage relationships and trace their sources for repair.

[0062] 3) Data security: Determine the security level and access rights of data based on blood relationships.

[0063] 6. Construct a terminal table filtering mechanism to improve accuracy: Filter and verify the established blood relationships through the terminal table to improve the accuracy and reliability of blood relationship construction.

[0064] The specific method is as follows: 6.1. Define the terminal table The terminal table usually refers to the final output table or target table in the data flow (such as task_trxxXX), which receives data from the upstream table and serves as the end point of the data lineage chain. Clarifying the definition and scope of the terminal table is the basis for subsequent filtering and verification work.

[0065] 6.2. Collect terminal table information 1) Identify terminal tables: Identify all terminal tables based on the data flow diagram and metadata management information.

[0066] 2) Collect attribute information: Collect attribute information of the terminal table, including table name, field name, data type, data format, etc.

[0067] 6.3. Establish terminal table filtering rules 1) Data integrity rule: Ensure that the data in the terminal table is complete, without missing or duplicate data.

[0068] 2) Data consistency rules: Check whether the data in the terminal table is consistent with the data in the upstream table, including field values, data types, and data formats.

[0069] 3) Data validity rules: Verify whether the data in the terminal table meets business rules and logical constraints, such as data range, data format, data relationship, etc.

[0070] 6.4. Apply filtering rules to filter 1) Automated filtering: Use data quality tools or scripts to automatically filter the terminal table according to the established filtering rules.

[0071] 2) Manual verification: Manually verify the automated filtering results to ensure the accuracy of the filtering.

[0072] 6.5. Verify the accuracy of blood relationship 1) Trace the data flow path: Starting from the terminal table, trace the data flow path upstream to verify the correctness of each data flow step.

[0073] 2) Check data conversion logic: Verify whether the conversion logic of data during the flow process is correct, including field mapping, data calculation, etc.

[0074] 3) Compare metadata with actual data: Compare the lineage relationship in the metadata with the actual data flow path to ensure that the two are consistent.

[0075] 6.6. Continuous optimization and update 1) Monitor data changes: Monitor changes in data sources in real time or regularly, such as the addition of new tables, fields, stored procedures, etc., to ensure the timeliness and accuracy of blood relationships.

[0076] 2) Feedback and optimization: Continuously optimize and update filtering rules and verification methods based on verification results and business needs.

[0077] 7. End: Complete the construction of data lineage relationships to provide support for data management and analysis.

[0078] The present invention can significantly improve the efficiency and accuracy of data management and analysis by analyzing task operation logs, automatically identifying and constructing the blood relationship between data tables, and having the following beneficial effects: 1. Improve the efficiency of data lineage analysis: By automatically analyzing task operation logs, this technical solution can significantly reduce the time and labor required for manual tracking and analysis of data lineage, thereby improving the overall efficiency of data lineage analysis.

[0079] 2. Enhance the accuracy of data lineage analysis: By using automated parsing of log data, the solution can more accurately identify and construct lineage relationships between data tables, reduce human errors, and ensure the reliability of data lineage information.

[0080] 3. Optimize data management and maintenance: Accurate data lineage provides strong support for data management, helping data managers better understand data flow and optimize data maintenance and data quality control processes.

[0081] 4. Support data governance and quality control: This technical solution helps data governance. Through clear lineage relationships, it is easier to identify data problems and risk points, and then implement effective data governance and quality control measures.

[0082] 5. Promote data reuse and value discovery: By building data lineage relationships, it is easier to discover the potential use and value of data, and promote data reuse and innovative applications.

[0083] 6. Improve problem diagnosis and fault recovery capabilities: When data problems occur, the solution can quickly locate the source of the problem, simplify the fault diagnosis process, and provide support for rapid recovery.

[0084] 7. Visualize data assets: Visualize data lineage relationships to help users more intuitively understand the organizational structure and dependencies of data assets, so as to make more informed decisions.

[0085] Together, these beneficial effects have promoted the modernization of data management and analysis, improved the level of intelligence in data processing, and brought considerable benefits to organizations.

[0086] Although the present invention has been disclosed as above with preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art may make some modifications and improvements without departing from the spirit and scope of the present invention. Therefore, the scope of protection of the present invention shall be based on the definition of the claims.

Claims

1. A method for constructing table-level blood relationship information based on task operation logs, characterized in that: The steps include: S1) Collect and obtain task running log data in the system, and parse the log to obtain database operation statements; S2) Parse the obtained log files and extract the database operation statements therein; S3) Combined with the job running records, the parsed database operation statements are screened to determine the target operation statements related to the data lineage relationship construction; S4) Establish a mapping relationship between the network protocol address and the host name, and further analyze the mapping relationship from the source table to the target table; S5) Use the filtered target operation statements to build the blood relationship between data tables.

2. The method for constructing table-level blood relationship information based on task operation logs according to claim 1, characterized in that: The task running log data in step S1 is in plain text format or JSON format, including date, time, log level and log content.

3. The method for constructing table-level blood relationship information based on task operation logs according to claim 1, characterized in that: The step S3 automatically identifies and configures the batch dependencies of the data processing tasks based on the parsed database operation statements and metadata, generates batch scheduling information, and specifies the execution order, time, and required resources of each data processing task.

4. The method for constructing table-level blood relationship information based on task operation logs according to claim 1, characterized in that: The step S2 uses regular expressions or string processing to separate database operation statements from job running records. After the step S3 completes the preliminary screening, the step S4 performs the following precise screening in combination with the mapping relationship: Establish a mapping relationship between network protocol addresses and host names through the DNS system, hosts files and / or network configuration files; While parsing the database operation statement, record the source information of the operation statement, including the IP address and host name of the client that initiated the operation; Match the source information of the operation statement with the mapping relationship between the network protocol and the host name; According to the needs of building data lineage relationships, the target operation statements are further screened in combination with the source information of the operation statements.

5. The method for constructing table-level blood relationship information based on task operation logs according to claim 1, characterized in that: The step S5 comprises: S51, determine the data type that needs to be managed and define metadata attributes; S52, identifying a data source, and automatically collecting metadata from the data source using a metadata extraction tool or service; S53, filtering out statements related to the target operation from the collected metadata; and parsing the filtered operation statements to identify dependencies between data tables and data flow paths; S54, determine the type of blood relationship and design diagram model; S55. Based on the parsed operation statements and the design diagram model, implement an algorithm to track the flow path of data; determine the complete path from one data element to another, including all intermediate steps and conversions; S56. Use a graph database or graph processing framework to store blood relationships and provide a query mechanism; S57. Monitor the changes of data sources in real time or regularly, and update the blood relationship graph model and stored data in a timely manner according to the monitored changes.

6. The method for constructing table-level blood relationship information based on task operation logs according to claim 5, characterized in that: The data types in step S51 include database tables, fields, views and stored procedures, and the metadata attributes include name, description, data type, source and destination.

7. The method for constructing table-level blood relationship information based on task operation logs as claimed in claim 5, characterized in that: The types of blood relationships in step S54 include upstream / downstream relationships, parent-child relationships, and dependency relationships, and the design graph model is a directed graph, an undirected graph, or a mixed graph.

8. The method for constructing table-level blood relationship information based on task operation logs according to claim 1, characterized in that: It also includes step S6: constructing a terminal table, and filtering and verifying the established blood relationship through the terminal table; the terminal table is the final output table or target table in the data flow, the terminal table receives data from the upstream table, and serves as the end point of the data blood relationship chain.

9. The method for constructing table-level blood relationship information based on task operation logs according to claim 8, characterized in that: The step S6 comprises: S61, selecting the final output table or target table in the data flow as the terminal table, wherein the terminal table receives data from the upstream table and serves as the end point of the data lineage chain; S62, identifying all terminal tables according to the data flow diagram and metadata management information; collecting attribute information of the terminal tables, including table name, field name, data type and data format; S63, establishing terminal table filtering rules; S64, applying filtering rules for automated filtering or manual verification; S65. Verify the accuracy of blood relationship; S66. Monitor changes in data sources in real time or regularly, and continuously optimize and update filtering rules and verification methods based on verification results and business needs.

10. The method for constructing table-level blood relationship information based on task running logs according to claim 8, characterized in that: The filtering rules in step S63 include: Data integrity rules: ensure that the data in the terminal table is complete, without missing or duplicate data; Data consistency rules: Check whether the data in the terminal table is consistent with the data in the upstream table, including field values, data types, and data formats; Data validation rules: verify whether the data in the terminal table meets the business rules and logical constraints; The step S65 comprises: Trace the data flow path: starting from the terminal table, trace the data flow path upstream to verify the correctness of each data flow step; Check data conversion logic: verify whether the conversion logic of data during the flow process is correct, including field mapping and data calculation; Compare metadata with actual data: Compare the lineage relationship in the metadata with the actual data flow path to ensure that the two are consistent.

Images