A method for database metadata collection and management

Abstract

The application relates to a database metadata acquisition and management method, which is realized by using a metadata acquisition network and a metadata management network, wherein the metadata acquisition network comprises a data source database, a built-in relational database, Debezium and Kafka, and the method comprises the following steps: (1) the metadata management network monitors the data source database and starts to acquire metadata, including full acquisition and incremental acquisition; (2) metadata information is saved to the built-in relational database; (3) the metadata management network manages the metadata obtained from the built-in relational database, including adding a database dictionary, marking metadata information and updating an old version, and then returning to step (2) until an external interface in the metadata management network receives an operation instruction, and the method ends. Compared with the prior art, the application has the advantages of strong real-time performance and convenience in managing historical data.

Description

Technical Field

[0001] This invention belongs to the field of metadata technology and relates to a method for collecting and managing database metadata. Background Technology

[0002] To meet the synchronization needs of different users' databases, users may require synchronizing data in specific databases or tables, or performing heterogeneous database synchronization. Based on this need, some companies have developed data synchronization products based on transaction logs. These products parse the database's transaction log data into database-independent structured data, and then import this structured data into the target database according to its requirements, thus achieving data synchronization. In this data synchronization process, to parse the transaction log data into database-independent structured data, it is usually necessary to obtain the database's metadata. However, the transaction log itself cannot directly provide this data. Therefore, existing solutions for obtaining metadata include: obtaining DDL (Data Definition Language) from the database transaction log; executing the DDL in the same database and then obtaining the metadata from that same database; directly obtaining metadata from the database and saving it to a backup database, providing metadata query capabilities; and obtaining all metadata from the database and saving it to a backup database, then parsing the database transaction log based on this point in time to update the backup database with subsequent metadata changes, providing metadata query capabilities.

[0003] However, the existing methods for obtaining metadata have the following drawbacks: they can only provide metadata corresponding to the timestamp of the database transaction log data, and cannot provide metadata from other points in time; since database metadata changes in real time, directly querying the database may result in inconsistent metadata obtained with the metadata obtained by parsing the database transaction log. Summary of the Invention

[0004] The purpose of this invention is to provide a method for collecting and managing database metadata, so as to overcome the shortcomings of poor real-time performance of incremental collection and lack of management of historical data.

[0005] The objective of this invention can be achieved through the following technical solutions:

[0006] A method for database metadata collection and management is provided. This method utilizes a metadata collection network and a metadata management network. The metadata collection network includes a data source database, a built-in relational database, Debezium, and Kafka. The method includes:

[0007] Step S1: Manage the metadata management network monitoring data source database and start collecting all metadata, while simultaneously executing steps S2 and S3;

[0008] Step S2: Check if there is incremental data. If yes, proceed to step S5; otherwise, re-enter step S2.

[0009] Step S3: Metadata information is saved to the built-in relational database;

[0010] Step S4: The metadata management network manages the metadata obtained from the built-in relational database, including adding database dictionaries, marking metadata information, and updating old versions. Then it returns to step S3 until the external interface in the metadata management network receives operation instructions, and then it ends.

[0011] Step S5: Perform incremental data acquisition, then proceed to step S3.

[0012] Furthermore, the incremental acquisition steps include:

[0013] Step S501: When the metadata in the data source database changes, Debezium parses the transaction log of the data source database;

[0014] Step S502: Kafka receives the data definition language parsed by Debezium;

[0015] Step S503: After the data definition language is read, the changed metadata information is saved to the built-in relational database.

[0016] The metadata management network includes a metadata monitoring and management module, a metadata version management module, a metadata tagging management module, a database dictionary management module, and a metadata interaction management module, which are responsible for managing data collection, version updates, metadata information tagging, database dictionary management, and user access, respectively.

[0017] Furthermore, the metadata monitoring and management module is responsible for initiating a Connector creation command to Debezium, instructing Debezium to collect data and save it to the built-in relational database; reading the data definition language from Kafka; obtaining changed metadata information and saving it completely to the built-in relational database, while also saving this saved record to the built-in relational database.

[0018] Furthermore, the metadata version management module acquires metadata information; updates the version field of changed metadata, and adds new changed metadata information; and uses the association field to connect the new version and the old version.

[0019] Furthermore, the metadata tagging management module is responsible for tagging the database metadata information in the built-in relational database. The tagging includes annotations on the metadata information and related relationships.

[0020] The markings include those automatically assigned by the system or those manually assigned by the customer after querying.

[0021] Furthermore, the database dictionary management module queries metadata information in the built-in relational database and supports updating and adjusting historical data dictionary information.

[0022] Furthermore, the metadata interaction management module is responsible for providing a metadata information management page and providing external interfaces, supporting queries, updates, and deletions of metadata information.

[0023] The data source database includes MySQL, Oracle, SQL Server, DB2, or PostgreSQL.

[0024] Compared with the prior art, the present invention has the following characteristics:

[0025] 1. This invention is based on Debezium and Kafka, and sets up a metadata monitoring and management module to monitor the collection and storage of metadata in real time. Once the metadata of the data source database changes, the system can immediately detect and update it in real time, and store the metadata in the built-in relational database, which improves the real-time performance of incremental collection and ensures the consistency and integrity of the data.

[0026] 2. This invention includes a metadata tagging management module, which tags the collected metadata information to facilitate customers' query of historical data and provides technical support for customers' fuzzy search and intelligent classification of data.

[0027] 3. By setting up a database dictionary management module, this invention provides data dictionary query and update functions, providing technical support for customers to standardize the design and implementation of data management systems.

[0028] 4. This invention provides query and update functions for various versions of metadata by setting up a metadata version management module.

[0029] 5. The databases of this invention include MySQL, Oracle, SQL Server, DB2, and PostgreSQL, supporting data collection from multiple databases. Attached Figure Description

[0030] Figure 1 System architecture diagram for metadata collection and management;

[0031] Figure 2 This is a flowchart illustrating the specific implementation process of metadata collection and management. Detailed Implementation

[0032] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0033] This invention provides a method for database metadata collection and management, characterized in that the method is implemented by using a metadata collection network and a metadata management network, such as... Figure 1 As shown, the metadata collection network includes a data source database, a built-in relational database, Debezium, and Kafka. Figure 1 In this context, DB is a built-in relational database used to store metadata information collected from data source databases. Source represents the data source database, including but not limited to MySQL, Oracle, SQL Server, DB2, or PostgreSQL. DB1 and DB2 belong to Source and represent multiple data source databases.

[0034] The methods for collecting and managing the database metadata include:

[0035] Step S1: Manage the metadata network monitoring source and start collecting all metadata.

[0036] Step S2: The collected metadata information is saved to the DB.

[0037] Step S3: The metadata management network manages the metadata information obtained by the database, including adding database dictionaries, marking metadata information, and updating old versions;

[0038] Step S4: Check if there is incremental data. If there is, proceed to step S5. If not, re-enter step S3.

[0039] Step S5: Perform incremental data acquisition and re-enter step S2.

[0040] The metadata information includes databases, tables, and fields.

[0041] The data collection process based on Debezium and Kafka in this invention adopts an agentless mode, which consumes relatively low resources such as CPU, memory and disk on the operating system where the data source is located.

[0042] The incremental acquisition steps include:

[0043] Step S401: When the metadata in the Source changes, Debezium parses the transaction log of the Source;

[0044] Step S402: Kafka receives the DDL (Data Definition Language) parsed by Debezium;

[0045] In step S403, after the DDL is read, the changed metadata information is saved to the DB.

[0046] As can be seen from the above process, the present invention can collect and manage the metadata of the source, capture changes in the metadata of the data source in real time, store the metadata in the DB, and ensure the consistency and integrity of the data. Moreover, the present invention obtains incremental metadata information from the database transaction log, which consumes less computing and storage resources in the database where the data source is located.

[0047] Kafka is a fast, scalable, and persistent messaging system that provides multiple replicas and partitions for recording messages.

[0048] Debezium is an open-source component that supports resuming interrupted downloads. When the database monitoring and management module sends a request to Debezium to create a Connector, Debezium begins collecting full and incremental data from the data source database. After completing data collection, Debezium parses the transaction logs of the data source database and then sends the obtained DDL to Kafka. The Connector is a runtime resource of Debezium. When it is necessary to collect full and incremental data from the data source, a Connector needs to be created. The configuration of the Debezium Connector for collecting metadata from the data source is as follows:

[0049] name = "test1" / / Connector name

[0050] database.hostname = "hostname" / / Address of the data source database server

[0051] database.user = "user" / / Database user with appropriate permissions

[0052] database.password = "password" / / Password for the database user

[0053] database.port = "port" / database server port number

[0054] database.serverId="1" / / Unique ID of the connector

[0055] database.history.kafka.topic = "dbhistory_dbserver" / / The name of the database history topic

[0056] database.server.name = "dbserver" / / Logical name of the server or cluster

[0057] include.schema.changes="true" / / Specifies whether the connector should generate events for DDL changes and send them to the fulfillment schema change topic for consumer use.

[0058] snapshot.mode = "schema_only" / / Specifies the conditions for running snapshots when the connector starts.

[0059] skipped.operations = "c,u,d" / / A comma-separated list of operation types to skip during the stream.

[0060] like Figure 1 As shown, the metadata management network includes a metadata monitoring and management module, a metadata version management module, a metadata tagging management module, a database dictionary management module, and a metadata interaction management module, which are responsible for managing data collection, version updates, metadata information tagging, database dictionary management, and user access, respectively.

[0061] The metadata monitoring and management module is responsible for initiating a Connector creation command to Debezium, instructing Debezium to collect data and save it to the database. When the metadata of the data source database changes, Debezium automatically sends the required DDL to Kafka. The metadata monitoring and management module reads the DDL from Kafka. It then obtains the changed metadata information and saves it completely to the database, simultaneously saving this record. When processing the data definition language again, this record is read first, and processing continues from that point, thus ensuring the functionality of resuming interrupted downloads. Throughout the incremental data collection process, the metadata monitoring and management module continuously reads metadata information from Kafka to monitor the source.

[0062] The aforementioned resume download means that if a network jitter or other abnormal situation occurs during the data acquisition process, causing the task to fail, the system can resume data acquisition from where the task was interrupted once the network returns to normal.

[0063] The DDL only contains changed metadata information. Because the metadata monitoring and management module only updates the changed metadata information, it can guarantee real-time performance.

[0064] The metadata version management module is responsible for version control of the collected metadata information. This module records details of the database, table, and field metadata for each version from the start of metadata collection to the current time, as well as all metadata information and metadata change history of the data source database. After the metadata monitoring and management module completes the parsing of the DDL, the metadata version management module extracts information about the database, table, and field related to the metadata from the DDL, updates the version fields of changed metadata, adds new changed metadata information, and uses correlation fields to link the old and new versions, thereby achieving the function of managing metadata.

[0065] The metadata tagging management module is responsible for tagging the collected metadata information with information notes, information tags, and relationship notes. Since different versions of metadata are recorded, tagging of historical data is also supported. Metadata tags can be automatically set by the system or manually set by the customer after querying; the default tag is empty. It is responsible for tagging database metadata information in the DB, including adding notes to metadata information and relationships.

[0066] The database dictionary management module is responsible for recording and displaying the data dictionary of the data source. After the metadata is saved to the DB, the database dictionary management module queries the database, table and field information based on the associated fields recorded by the metadata monitoring and management module to realize the dictionary query function. It also supports operations such as updating and adjusting historical data dictionary information, providing technical support for customers to design and implement data management systems in a standardized manner.

[0067] The metadata interaction management module is responsible for providing a metadata information management page and providing a RESTful interface to the outside world, supporting the querying, updating and deleting of metadata information.

[0068] In this invention, data can be queried after the full data collection is completed.

[0069] In one specific implementation, such as Figure 2 As shown, the collection and management of metadata includes the following processes:

[0070] (1) Operators add the data source information that needs to be monitored;

[0071] (2) The system checks whether the production environment meets the software operation requirements and whether the data source is connected normally. If yes, proceed to the next step; otherwise, return to the previous step.

[0072] (3) The system saves the data source connection information to the database;

[0073] (4) Under the metadata monitoring and management, the system obtains connection information from the DB and begins to collect metadata, including full collection and incremental collection. After the collection is completed, a Debezium ready flag is generated.

[0074] (5) Save metadata information to the DB;

[0075] (6) The database dictionary management module adds time record information to the acquired metadata information to realize the database dictionary function;

[0076] (7) The metadata tagging management module tags metadata information by default;

[0077] (8) The metadata version management module updates the metadata information, updates the old version fields and adds the new version information, returns to step (5), and continues until the external interface in the metadata interaction management module receives the operation instructions, which includes query, update and delete.

[0078] If the above methods are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art, or a 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 this 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.

[0079] The above description of the embodiments is provided to enable those skilled in the art to understand and use the invention. It will be apparent to those skilled in the art that various modifications can be made to these embodiments, and the general principles described herein can be applied to other embodiments without inventive effort. Therefore, the present invention is not limited to the above embodiments, and any improvements and modifications made by those skilled in the art based on the disclosure of the present invention without departing from the scope of the invention should be within the protection scope of the present invention.

Claims

1. A method for collecting and managing database metadata, characterized in that, This method is implemented by using a metadata collection network and a metadata management network. The metadata collection network includes a data source database, a built-in relational database, Debezium, and Kafka. The method includes: Step S1: Manage the metadata management network monitoring data source database and start collecting all metadata, while simultaneously executing steps S2 and S3; Step S2: Check if there is incremental data. If yes, proceed to step S5; otherwise, re-enter step S2. Step S3: Metadata information is saved to the built-in relational database; Step S4: The metadata management network manages the metadata obtained from the built-in relational database, including adding database dictionaries, marking metadata information, and updating old versions. Then it returns to step S3 until the external interface in the metadata management network receives operation instructions, and then it ends. Step S5: Perform incremental data acquisition, then proceed to step S3; The metadata management network includes a metadata monitoring and management module, a metadata version management module, a metadata tagging management module, a database dictionary management module, and a metadata interaction management module, which are responsible for managing data collection, version updates, metadata information tagging, database dictionary management, and user access, respectively. The metadata monitoring and management module is responsible for initiating a Connector creation command to Debezium, instructing Debezium to collect data and save it to the built-in relational database; reading the data definition language from Kafka; obtaining changed metadata information and saving it completely to the built-in relational database, while also saving this saved record to the built-in relational database; The metadata version management module acquires metadata information; updates the version field of changed metadata, and adds new changed metadata information; and uses the association field to connect the new version and the old version.

2. The method for database metadata collection and management according to claim 1, characterized in that, The incremental acquisition steps include: Step S501: When the metadata in the data source database changes, Debezium parses the transaction log of the data source database; Step S502: Kafka receives the data definition language parsed by Debezium; Step S503: After the data definition language is read, the changed metadata information is saved to the built-in relational database.

3. The method for database metadata collection and management according to claim 1, characterized in that, The metadata tagging management module is responsible for tagging the database metadata information in the built-in relational database. The tagging includes annotations on the metadata information and related relationships.

4. The method for database metadata collection and management according to claim 3, characterized in that, The markings include those automatically assigned by the system or those manually assigned by the customer after querying.

5. The method for database metadata collection and management according to claim 1, characterized in that, The database dictionary management module queries metadata information in the built-in relational database and also supports updating and adjusting historical data dictionary information.

6. The method for database metadata collection and management according to claim 1, characterized in that, The metadata interaction management module is responsible for providing a metadata information management page and providing external interfaces, supporting queries, updates, and deletions of metadata information.

7. The method for database metadata collection and management according to claim 1, characterized in that, The data source database includes MySQL, Oracle, SQL Server, DB2, or PostgreSQL.

Images