Metadata change automatic monitoring method based on database auditing

Abstract

The invention discloses an automatic metadata change monitoring method based on database auditing, which relates to the field of data change monitoring and comprises the following steps of: regularly updating and collecting technical metadata of a source system, and cataloguing a resource directory of the source system so as to obtain a complete metadata entry of an enterprise; according to the data processing flow direction of the enterprise system, regularly analyzing the metadata reference relationship between the systems, and establishing an enterprise-level metadata blood relationship network; configuring a technical metadata change auditing SQL (Structured Query Language) log at the database end, and writing the SQL change log into Kafka in a streaming manner through a data synchronization tool; according to the method, technical metadata of stream change in Kafka is consumed, technical metadata of source system change is analyzed, and a metadata influence range is recursively retrieved by depending on a metadata blood relationship graph. The method has the advantages that the change condition of the metadata of each source system can be automatically monitored, the downstream influence application is informed in time, and the service aperture calculation problem of the whole data processing link caused by the change of the source metadata is avoided.

Description

Technical Field

[0001] The present invention relates to the field of data change monitoring, and in particular to a method for automatically monitoring metadata changes based on database auditing. Background Art

[0002] Metadata monitoring is an important part of data management and governance, which aims to continuously track and evaluate the quality, consistency and compliance of metadata. Through effective metadata monitoring, organizations can ensure the integrity and reliability of their data assets, support more informed business decisions, and meet regulatory compliance requirements.

[0003] In the prior art, metadata management often relies on databases for data analysis. When metadata changes, it is often impossible to perform real-time monitoring and early warning for data changes in various production links. The degree of automated management and flexibility are insufficient, the workload of collecting and maintaining metadata is large, and omissions and errors are prone to occur. Therefore, the present invention designs an automatic monitoring method for metadata changes based on database auditing. Summary of the invention

[0004] The purpose of the present invention is to solve the problems in the prior art and to propose a method for automatically monitoring metadata changes based on database auditing.

[0005] A method for automatically monitoring metadata changes based on database auditing, comprising:

[0006] A metadata management module, the metadata management module includes a data source configuration submodule and a metadata collection submodule;

[0007] A metadata lineage analysis module, the metadata lineage analysis module includes an SQL script collection submodule and an SQL script analysis submodule;

[0008] A metadata real-time change stream module, wherein the metadata real-time change stream module includes a database audit configuration submodule and a metadata audit change log stream synchronization submodule;

[0009] A metadata lineage tracking module, wherein the metadata lineage impact tracking module includes a metadata change parsing submodule and a metadata impact analysis submodule.

[0010] And take the following steps through the above modules:

[0011] Step S1: Regularly update the technical metadata of the source system and catalog the source system resource directory to obtain complete metadata entries for the enterprise;

[0012] Step S2: According to the data processing flow of the enterprise system, the metadata reference relationship between systems is regularly analyzed to establish an enterprise-level metadata blood relationship network;

[0013] Step S3: Configure the technical metadata change audit SQL log on the database side, and write the SQL change log to Kafka in a streaming manner through the data synchronization tool;

[0014] Step S4: Consume the technical metadata of streaming changes in Kafka, parse the technical metadata of source system changes, and rely on the metadata lineage graph to recursively retrieve the metadata impact range to facilitate impact analysis.

[0015] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, the data source configuration submodule is used to configure the data source of each system according to the enterprise's source system resource directory, and the metadata collection submodule is adapted to multiple types of databases, and collects a complete enterprise technical metadata directory based on the technical metadata mapping relationship of each type of database.

[0016] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, the SQL script collection submodule establishes a complete enterprise source system SQL script folder directory according to the hierarchical relationship of the enterprise data architecture, and regularly collects SQL batch script files of each layer of data system; the SQL script parsing submodule parses the SQL batch scripts of each system from bottom to top according to the hierarchical relationship of the data architecture, and generates an enterprise-level metadata lineage relationship diagram according to the table-level lineage parsing criteria and the field-level lineage parsing criteria.

[0017] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, the database audit configuration submodule configures the audit SQL log dynamic monitoring strategy of the database's technical metadata changes according to the audit policies of multiple types of databases; the metadata audit change log stream synchronization submodule adopts a real-time data synchronization tool integrated with FlinkCDC to synchronize the SQL log of technical metadata changes to the Kafka message middleware in real time.

[0018] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, the metadata change parsing submodule dynamically consumes and parses the metadata change SQL log in Kafka, extracts the table and field information of the source system where the changes occur, and establishes judgment rules according to the deletion or modification relationship; the metadata impact analysis submodule, based on the enterprise-level metadata lineage relationship diagram, recursively retrieves the impact scope of the source table structure changes on the downstream system table-level and field-level metadata.

[0019] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, in step S1, according to the characteristics of the database, the technical metadata of the source system is regularly collected, including the schema name of the source system, the Chinese and English names of the tables, the Chinese and English names of the fields, and the data types. According to the collected technical metadata of each source system, a metadata resource directory of the source system is established, and the metadata is displayed in a hierarchical manner according to the system, schema name, table name, and field name.

[0020] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, in step S2, while the comprehensive metadata resource information of the enterprise is collected and acquired according to step S1, the data flow relationship between the source system to the data platform and the data platform to the terminal analysis application is sorted out according to the current status of the enterprise data architecture, and then the specifications for synchronizing data from the source system to the data platform, ETL script processing between the layers of the data platform, and data services provided by the data platform to the terminal analysis application are formulated and organized, and according to the data synchronization rules, SQL script processing rules and data service rules, the blood relationship diagram between the upstream and downstream system tables and tables, and fields and fields of the data flow is parsed.

[0021] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, in step S3, according to the auditing characteristics of the database, the database metadata change auditing configuration is enabled, and then the SQL script of the technical metadata change is stored in real time in the metadata change script log library, and the database table real-time synchronization service function is configured to synchronize the change records in the metadata change script log library to the Kafka middleware in real time.

[0022] In the above-mentioned automatic monitoring method of metadata changes based on database auditing, in step S4, the technical metadata change SQL script in Kafka is consumed in real time, and the source system, schema name, table, and field attributes of the change are parsed. Then, the parsed technical metadata such as the source system, schema name, table, and field of the change are matched and mapped with the metadata in the metadata lineage relationship diagram to obtain a map of the impact range of the metadata lineage that has changed. Finally, a real-time warning is issued based on the impact range of the metadata change that is automatically identified and monitored.

[0023] Compared with the prior art, the present invention has the following advantages:

[0024] The method and system for automatically monitoring metadata changes of the present invention utilize technical metadata collection, automatic analysis of metadata SQL scripts, database technical metadata change auditing, real-time synchronization of metadata SQL log changes, and real-time analysis of the impact scope of changed technical metadata to ensure automatic monitoring of changes in metadata of each source system, and timely inform downstream affected applications, thereby avoiding business scope calculation problems of the entire data processing link caused by source metadata changes. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 This is a diagram of the operation steps of a method for automatically monitoring metadata changes based on database auditing proposed by the present invention.

[0026] Figure 2 The present invention is a flowchart of a method for automatically monitoring metadata changes based on database auditing. DETAILED DESCRIPTION

[0027] Reference Figure 1-2 ,A method for automatically monitoring metadata changes based on database auditing, including a metadata management module, a metadata lineage parsing module, a metadata real-time change stream module and a metadata lineage tracking module;

[0028] The metadata management module is used to configure the data source of each system, adapt to various types of databases, and collect a complete enterprise technical metadata directory. It specifically includes a data source configuration submodule and a metadata collection submodule. The data source configuration submodule is used to configure the data source of each system according to the enterprise's source system resource directory. The metadata collection submodule adapts to multiple types of databases and collects a complete enterprise technical metadata directory based on the technical metadata mapping relationship of each database.

[0029] The metadata lineage analysis module is used to regularly collect SQL batch script files of each layer of data system, and parse the SQL batch scripts of each system from bottom to top according to the hierarchical relationship of the enterprise data architecture, according to the table-level lineage analysis criteria and the field-level lineage analysis criteria, to generate an enterprise-level metadata lineage relationship diagram. It specifically includes an SQL script collection submodule and an SQL script analysis submodule. The SQL script collection submodule establishes a complete enterprise source system SQL script folder directory according to the hierarchical relationship of the enterprise data architecture, and regularly collects SQL batch script files of each layer of data system. The SQL script analysis submodule parses the SQL batch scripts of each system from bottom to top according to the hierarchical relationship of the data architecture, and generates an enterprise-level metadata lineage relationship diagram according to the table-level lineage analysis criteria and the field-level lineage analysis criteria.

[0030] The metadata real-time change stream module is used to configure the audit dynamic monitoring strategy for database technical metadata changes; the real-time data synchronization tool integrated with FlinkCDC is used to synchronize the changed technical metadata SQL log to the Kafka message middleware in real time, which specifically includes the database audit configuration submodule and the metadata audit change log stream synchronization submodule. The database audit configuration submodule configures the audit SQL log dynamic monitoring strategy for the database's technical metadata changes according to the audit policies of different types of databases; the metadata audit change log stream synchronization submodule uses the real-time data synchronization tool integrated with FlinkCDC to synchronize the SQL log of technical metadata changes to the Kafka message middleware in real time.

[0031] The metadata lineage impact tracking module is used to parse the table and field information of the source system that has changed. According to the enterprise-level metadata lineage relationship diagram, it recursively retrieves the impact of the source table structure change on the downstream system table-level and field-level metadata. Specifically, it includes a metadata change parsing submodule and a metadata impact analysis submodule. The metadata change parsing submodule dynamically consumes and parses the metadata change SQL log in Kafka, extracts the table and field information of the source system that has changed, and establishes judgment rules according to the deletion or modification relationship; the metadata impact analysis submodule is based on the enterprise-level metadata lineage relationship diagram constructed in step 2, recursively retrieves the impact of the source table structure change on the downstream system table-level and field-level metadata, and issues real-time warnings in a timely manner.

[0032] Perform the following steps with the above modules:

[0033] Step 1: Regularly update the technical metadata of the source system and catalog the source system resource directory to obtain complete metadata entries for the enterprise;

[0034] Step 101 regularly collects technical metadata of the source system according to database characteristics, including the source system's schema name, table English name, field English name and data type;

[0035] Step 102: Based on the technical metadata of each source system collected in step 101, a metadata resource directory of the source system is established, and the metadata is displayed hierarchically according to the system, schema name, table name, and field name;

[0036] Step 2: Based on the data processing flow of the enterprise system, regularly analyze the metadata reference relationship between systems and establish an enterprise-level metadata blood relationship network;

[0037] Step 201 collects and obtains comprehensive metadata resource information of the enterprise according to step 1. At the same time, according to the current status of the enterprise data architecture, sorts out the data flow relationship between the source system to the data platform and the data platform to the terminal analysis application;

[0038] Step 202 formulates and organizes specifications for synchronizing data from the source system to the data platform, processing ETL scripts between layers of the data platform, and providing data services from the data platform to terminal analysis applications;

[0039] Step 203 parses the blood relationship diagram between the upstream and downstream system tables and fields according to the data synchronization rules, SQL script processing rules and data service rules;

[0040] Step 3: Configure the technical metadata change audit SQL log on the database side, and write the SQL change log to Kafka in a streaming manner through the data synchronization tool;

[0041] Step 301: Enable the database metadata change audit configuration according to the audit characteristics of the database;

[0042] Step 302 stores the SQL script of the technical metadata change in real time to the metadata change script log library;

[0043] Step 303 configures the database table real-time synchronization service function to synchronize the change records in the metadata change script log library to the Kafka middleware in real time;

[0044] Step 4 consumes the technical metadata of streaming changes in Kafka, parses the technical metadata of source system changes, and relies on the metadata lineage graph to recursively retrieve the metadata impact scope, and promptly informs the person in charge of the affected system to conduct impact analysis.

[0045] Step 401 consumes the SQL script for changing technical metadata in Kafka in real time, and parses the changed source system, schema name, table, and field attributes;

[0046] Step 402 matches and maps the parsed technical metadata such as the change source system, schema name, table, and field with the metadata in the metadata lineage relationship graph to obtain a map of the metadata lineage impact range of the change;

[0047] Step 403 issues a real-time warning based on the impact scope of the metadata change that is automatically identified and monitored.

[0048] It is known from common technical knowledge that the present invention can be implemented by other embodiments that do not deviate from its spirit or essential features. Therefore, the above disclosed embodiments are only illustrative in all respects and are not exclusive. All changes within the scope of the present invention or within the scope of the present invention are included in the present invention.

Claims

1. A method for automatically monitoring metadata changes based on database auditing, characterized in that: Take the following steps: Step S1: Regularly update the technical metadata of the source system through the metadata management module, and catalog the source system resource directory to obtain complete metadata entries of the enterprise; Step S2: According to the data processing flow of the enterprise system, the metadata lineage analysis module is used to regularly analyze the metadata reference relationship between systems to establish an enterprise-level metadata lineage network; Step S3: Configure the technical metadata change audit SQL log from the database side through the metadata real-time change stream module, and write the SQL change log to Kafka in a streaming manner through the data synchronization tool; Step S4: Through the metadata lineage tracking module, consume the technical metadata of streaming changes in Kafka, parse the technical metadata of source system changes, and rely on the metadata lineage relationship graph to recursively retrieve the metadata impact range to facilitate impact analysis.

2. According to claim 1, a method for automatically monitoring metadata changes based on database auditing is characterized in that: The metadata management module includes a data source configuration submodule and a metadata collection submodule. The data source configuration submodule is used to configure the data source of each system according to the source system resource directory of the enterprise; The metadata collection submodule is adapted to multiple types of databases, and collects a complete enterprise technical metadata catalog based on the technical metadata mapping relationship of each type of database.

3. The method for automatically monitoring metadata changes based on database auditing according to claim 1, characterized in that: The metadata lineage analysis module includes an SQL script collection submodule and an SQL script analysis submodule. The SQL script collection submodule establishes a complete enterprise source system SQL script folder directory according to the enterprise data architecture hierarchy, and regularly collects SQL batch script files of each layer of data system; The SQL script parsing submodule parses the SQL batch scripts of each system from bottom to top according to the data architecture hierarchy, and generates an enterprise-level metadata lineage relationship diagram based on table-level lineage parsing criteria and field-level lineage parsing criteria.

4. The method for automatically monitoring metadata changes based on database auditing according to claim 2, characterized in that: The metadata real-time change stream module includes a database audit configuration submodule and a metadata audit change log stream synchronization submodule. The database audit configuration submodule configures the audit SQL log dynamic monitoring strategy of the database's technical metadata changes according to the audit strategy of multiple types of databases; The metadata audit change log stream synchronization submodule uses a real-time data synchronization tool integrated with FlinkCDC to synchronize the SQL log of technical metadata changes to the Kafka message middleware in real time.

5. The method for automatically monitoring metadata changes based on database auditing according to claim 3 is characterized in that: The metadata lineage impact tracking module includes a metadata change parsing submodule and a metadata impact analysis submodule. The metadata change parsing submodule dynamically consumes and parses the metadata change SQL log in Kafka, extracts the table and field information of the source system where the change occurs, and establishes a judgment rule according to whether the relationship is deleted or modified; The metadata impact analysis submodule recursively retrieves the impact scope of source table structure changes on downstream system table-level and field-level metadata based on the enterprise-level metadata lineage relationship diagram.

6. The method for automatically monitoring metadata changes based on database auditing according to claim 1, characterized in that: In step S1, according to the characteristics of the database, the technical metadata of the source system is collected regularly, including the schema name of the source system, the Chinese and English names of the tables, the Chinese and English names of the fields, and the data types. According to the collected technical metadata of each source system, a metadata resource directory of the source system is established, and the metadata is displayed in a hierarchical manner according to the system, schema name, table name, and field name.

7. The method for automatically monitoring metadata changes based on database auditing according to claim 6, characterized in that: In step S2, while collecting and acquiring comprehensive metadata resource information of the enterprise according to step S1, the data flow relationship between the source system to the data platform and between the data platform and the terminal analysis application is sorted out according to the current status of the enterprise data architecture. Then, specifications are formulated and organized for synchronizing data from the source system to the data platform, ETL script processing between various layers of the data platform, and data services provided by the data platform to the terminal analysis application. In addition, according to data synchronization rules, SQL script processing rules and data service rules, a blood relationship diagram between the upstream and downstream system tables and fields is parsed.

8. The method for automatically monitoring metadata changes based on database auditing according to claim 7, characterized in that: In step S3, according to the audit characteristics of the database, the database metadata change audit configuration is enabled, and then the SQL script of the technical metadata change is stored in real time in the metadata change script log library, and the database table real-time synchronization service function is configured to synchronize the change records in the metadata change script log library to the Kafka middleware in real time.

9. The method for automatically monitoring metadata changes based on database auditing according to claim 8, characterized in that: In step S4, the SQL script for technical metadata changes in Kafka is consumed in real time, and the source system, schema name, table, and field attributes of the changes are parsed. The parsed changed source system, schema name, table, and field are then used as technical metadata to match and map with the metadata in the metadata lineage relationship diagram, and a map of the impact range of the metadata lineage that has changed is obtained. Finally, a real-time warning is issued based on the impact range of the metadata changes that are automatically identified and monitored.

Images