Metadata storage method supporting efficient access

Abstract

The application relates to the technical field of database information, and discloses a metadata storage method supporting efficient access, which stores metadata into a relational database through a relational model description of the metadata, adopts the relational model description of the metadata, and enables developers to use familiar technology stacks to access the metadata for integration of external systems; the metadata is stored in layers, upper-layer metadata is stored first, then lower-layer metadata is stored, metadata objects are stored in layers, all primary key IDs of upper-layer tables are redundantly stored in lower-layer tables, indexes are established, query and access are facilitated, only one copy of metadata is saved, data duplication is avoided, storage cost is reduced, operation and maintenance cost is also reduced, the metadata stored in layers is compatible with heterogeneous databases, metadata tables can be joined in any two, and multi-condition search is efficiently supported through storage of the primary key IDs; indexes of all primary key IDs of the redundant upper-layer tables are established in the heterogeneous databases, so that efficient access of the metadata is supported.

Description

Technical Field

[0001] This invention relates to the field of database information technology, and in particular to a metadata storage method that supports efficient access. Background Technology

[0002] Database metadata services play a crucial role as a foundational service in database management. They need to interface with numerous third-party systems and support advanced data governance, including asset tagging and classification, data lineage analysis, and storage cost management. Therefore, how metadata information is stored is critical. Currently, the most popular centralized metadata management systems are represented by open-source systems like Apache Atlas and LinkedIn DataHub. These systems store metadata using a non-relational model, resulting in high deployment costs and complex integration with other business systems. They also introduce search engines to support metadata searching and access. Multiple copies of metadata increase storage costs and operational complexity, and also introduce metadata latency and consistency issues.

[0003] For example, the authorization announcement number CN112905564B discloses a method for managing Oracle database metadata based on Atlas. It supports the pattern of custom extended type indexes based on the Atlas component and provides storage and indexing services by extending custom Oracle types. However, this method does not consider the storage and indexing of metadata for other databases besides Oracle. Moreover, this method is based on the Atlas component, which uses a key-value model to store metadata in HBase. Introducing HBase will increase the cost of learning, deployment and operation and maintenance. When integrating with the metadata system, it is not possible to directly access it through SQL at the database layer. Instead, it is necessary to call it through the service layer API and perform additional processing at the application layer, which increases the complexity and workload of the overall system development.

[0004] For example, the authorization announcement number CN115062084B discloses a method and device for building API interfaces based on database metadata. This technology can reduce the operation and maintenance management problems caused during the connection. However, this technology uses an interface-level connection method and does not support database-level connection.

[0005] The aforementioned patents suffer from the problems described in this background section: high deployment costs of the storage systems, high complexity of integration with other business systems, and multiple copies of metadata increasing storage costs and operational complexity, while also introducing metadata latency and consistency issues. To address these problems, this invention proposes a metadata storage method that supports efficient access. Summary of the Invention

[0006] The purpose of this section is to outline some aspects of embodiments of the present invention and to briefly describe some preferred embodiments. Simplifications or omissions may be made in this section, as well as in the abstract and title of this application, to avoid obscuring the purpose of these documents; however, such simplifications or omissions should not be construed as limiting the scope of the invention.

[0007] In view of the problems existing in the above-mentioned metadata storage methods that support efficient access, this invention is proposed.

[0008] Therefore, the purpose of this invention is to provide a metadata storage method that supports efficient access.

[0009] To solve the above-mentioned technical problems, the present invention provides the following technical solution: describing metadata through a relational model and storing the metadata in a relational database;

[0010] The metadata is stored in layers, with the upper-layer metadata stored first, followed by the lower-layer metadata.

[0011] This enables the hierarchical storage of metadata to be compatible with heterogeneous databases;

[0012] Indexes are created for the primary keys of all redundant upper-level tables in the heterogeneous database to support efficient access to metadata.

[0013] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, a table is designed for each level of metadata and stored in the relational database.

[0014] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, the fields of the table contain all attributes of the metadata of that layer, and must have an auto-incrementing primary key and the primary keys of all upper-level tables.

[0015] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, the relational database includes MySQL, Oracle, SQL Server, DB2, and PostgreSQL.

[0016] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, the hierarchical storage includes establishing metadata for single-table storage instances, databases, schemas, tables, views, columns, and index objects respectively.

[0017] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, when collecting the metadata, the upper-level metadata is collected first, that is, collected in the order of instance-database-schema. The lower-level objects are also collected in the order of instance-database-schema. When storing the lower-level tables, the primary keys of all upper-level tables are redundant.

[0018] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, the management rules for the heterogeneous database include that the metadata design is based on multi-level database types, while for database types with fewer levels, default values ​​are filled in the corresponding fields.

[0019] As a preferred embodiment of the metadata storage method supporting efficient access described in this invention, the metadata storage query process is as follows:

[0020] First, the metadata is collected; second, the metadata is stored hierarchically; then, the metadata is queried; third, the metadata is verified and access is controlled; and finally, the query results are returned.

[0021] A computer device includes: a memory for storing instructions; and a processor for executing the instructions, causing the device to perform a metadata storage method that supports efficient access.

[0022] A computer-readable storage medium having a computer program stored thereon, which, when executed, implements a metadata storage method that supports efficient access.

[0023] The beneficial effects of this invention are as follows: This invention describes metadata using a relational model and stores the metadata in a relational database. Using a relational model allows for metadata storage using relational databases such as MySQL, offering a cost advantage compared to NoSQL. It also allows developers to access metadata using familiar technology stacks for integration with external systems. The metadata is stored in layers: first, upper-layer metadata is stored, then lower-layer metadata. The lower-layer tables redundantly store the primary key IDs of all upper-layer tables and create indexes for easy querying and access. Only one copy of the metadata is stored, avoiding data replication and the maintenance and consistency issues associated with data synchronization channels, thus reducing both storage and maintenance costs. The layered metadata storage is compatible with heterogeneous databases. Through the storage of primary key IDs, metadata tables can be joined arbitrarily between any two tables, efficiently supporting multi-condition searches. Indexes are created for the redundant primary keys of all upper-layer tables in the heterogeneous database to support efficient metadata access. Furthermore, the metadata tables facilitate convenient database asset statistics. Attached Figure Description

[0024] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0025] Figure 1 This is a flowchart of the metadata storage method that supports efficient access according to the present invention;

[0026] Figure 2 This is a flowchart illustrating the metadata storage and query process described in the metadata storage method supporting efficient access according to the present invention.

[0027] Figure 3 This is a metadata storage hierarchy diagram for the metadata storage method that supports efficient access according to the present invention. Detailed Implementation

[0028] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0029] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0030] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in one embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that is mutually exclusive with other embodiments.

[0031] Secondly, the present invention is described in detail with reference to the schematic diagrams. When detailing the embodiments of the present invention, for ease of explanation, the cross-sectional views illustrating the device structure may be partially enlarged, not according to the usual scale. Furthermore, the schematic diagrams are merely examples and should not limit the scope of protection of the present invention. In addition, actual fabrication should include three-dimensional spatial dimensions of length, width, and depth.

[0032] Example 1

[0033] This embodiment provides a method flowchart for a metadata storage method that supports efficient access, such as... Figure 3 As shown, metadata storage methods that support efficient access include:

[0034] Metadata is described using a relational model and stored in a relational database.

[0035] Design a table to store the metadata for each level, and store it in a relational database;

[0036] The table's fields contain all the attributes of the metadata at that level, and must have an auto-incrementing primary key and the primary keys of all upper-level tables;

[0037] Relational databases include MySQL, Oracle, SQL Server, DB2, and PostgreSQL;

[0038] Taking MySQL / PostgreSQL / MongoDB as an example, the hierarchical relationship of metadata in the database is shown in Table 1:

[0039] Table 1. Hierarchical relationship of metadata in the database

[0040] Metadata layer MySQL PostgreSQL MongoDB layer Example Example Example Second floor Database / Schema database database Three floors Tables / Views model gather Fourth floor Columns / Index Tables / Views Indexes, etc. Fifth floor Columns / Index

[0041] The metadata is stored in layers, with the upper-layer metadata stored first, followed by the lower-layer metadata.

[0042] Tiered storage includes establishing metadata for single-table storage instances, databases, schemas, tables, views, columns, and index objects separately;

[0043] When collecting metadata, the upper-level metadata is collected first, that is, in the order of instance-database-schema. The lower-level objects are also collected in the order of instance-database-schema. When storing the lower-level tables, the primary keys of all upper-level tables are redundant.

[0044] In practice, when storing metadata for a storage schema, redundant upper-level table primary key IDs and instance primary key IDs are stored in the schema metadata table.

[0045] This enables the hierarchical storage of metadata to be compatible with heterogeneous databases;

[0046] The management rules for heterogeneous databases include metadata design based on multi-level database types, while for low-level database types, default values ​​are filled in the corresponding fields.

[0047] When managing heterogeneous databases, there may be scenarios where the parent object hierarchy of a certain object is not completely consistent. For example, the parent objects of table metadata are different in MySQL and PostgreSQL.

[0048] In MySQL, the upper-level objects of a table have two layers: database and instance. In PostgreSQL, the upper-level objects of a table have three layers: schema, database, and instance. For this scenario, the design of table metadata is mainly based on multi-level database types, and default values ​​are filled in the corresponding fields for database types with fewer levels.

[0049] For example, in the storage design of table metadata in MySQL and PostgreSQL, the PostgreSQL hierarchy is used as the main one, with redundant storage of instance ID, database ID and schema ID, while the schema ID of MySQL table metadata is filled with a default value.

[0050] In heterogeneous databases, indexes are created for the primary keys of all redundant upper-level tables to support efficient access to metadata;

[0051] The process for querying metadata storage is as follows:

[0052] First, metadata is collected; second, it is stored in layers; third, it is queried; fourth, it is validated and access controlled; and finally, the query results are returned. Figure 2 As shown;

[0053] Taking PostgreSQL as an example, the metadata storage includes instance metadata storage, database metadata storage, schema metadata storage, table metadata storage, view metadata storage, field metadata storage, and index metadata storage. The hierarchy of metadata storage is shown in the diagram below. Figure 3 As shown;

[0054] Table 2 shows the core fields of instance metadata storage and their descriptions:

[0055] Table 2 Instance Metadata Table (meta_instance)

[0056] field name type Chinese meaning ID BIGINT Instance ID INSTANCE_ALIAS VARCHAR Instance aliases HOST VARCHAR host PORT VARCHAR port DB_TYPE INT Database type VERSION VARCHAR Version number IS_RW INT Is it read-only? RW_INSTANCE_ID INT Associated read / write instances

[0057] It should be noted in the table that: the instance table is the top-level table for storing database metadata. Its core is the unique instance ID and the common attributes of all databases below it. The instance ID serves as the unique identifier for the association of lower-level objects.

[0058] Table 3 shows the core fields of the database metadata storage and their descriptions.

[0059] Table 3. Database metadata table (meta_database)

[0060] field name type Chinese meaning ID BIGINT Database ID INSTANCE_ID BIGINT Instance ID DB_NAME VARCHAR Database name CHARSET VARCHAR Encoding type TABLE_SPACE VARCHAR tablespace

[0061] The table should be noted as follows: The database is the lower layer of the instance. It uses the database ID as a unique identifier and also stores the instance ID to which it belongs. To query the instance information to which the current database belongs, only a join of the two tables is needed. An index is created for the instance ID field, which supports single table queries or join table queries by index.

[0062] The core fields of the schema metadata storage and their descriptions are shown in Table 4:

[0063] Table 4. Schema Metadata Table (meta_schema)

[0064]

[0065]

[0066] The table should be noted as follows: The schema is a lower-level object of the database. It uses the schema ID as a unique identifier to store the database ID to which it belongs and redundantly stores the instance ID to which it belongs. Complex queries can be achieved by joining the schema metadata table and the database metadata table, as well as the schema metadata table and the instance metadata table. At the same time, indexes are created for the instance ID and the database ID to support fast search.

[0067] Table 5 shows the core fields for storing table metadata and their descriptions.

[0068] Table 5. Metadata Table

[0069] field name type Chinese meaning ID BIGINT Table ID INSTANCE_ID BIGINT Instance ID DB_ID BIGINT Database ID SCHEMA_ID BIGINT Pattern ID TABLE_NAME VARCHAR Table Name

[0070] It should be noted in the table that: the table is a lower-level object of the schema, and uses the table ID as a unique identifier to store the schema ID to which it belongs. It also redundantly stores the instance ID and database ID to which it belongs. Indexes are created for the schema ID, database ID and instance ID. More complex queries can be achieved through join operations with the upper-level metadata table.

[0071] The core fields of view metadata storage and their descriptions are shown in Table 6:

[0072] Table 6. View Metadata Table (meta_view)

[0073] field name type Chinese meaning ID BIGINT View ID INSTANCE_ID BIGINT Instance ID DB_ID BIGINT Database ID SCHEMA_ID BIGINT Pattern ID VIEW_NAME VARCHAR View Name

[0074] The table should be noted as follows: As a lower-level object of the schema, the view uses the view ID as a unique identifier to store its schema ID, and redundantly stores the instance ID and database ID. Indexes are created for the schema ID, database ID, and instance ID. More complex queries can be achieved through join operations with the upper-level metadata table.

[0075] The core fields of the field metadata storage and their descriptions are shown in Table 7:

[0076] Table 7. Metadata Table (meta_column)

[0077] field name type Chinese meaning ID BIGINT Field ID INSTANCE_ID BIGINT Instance ID DB_ID BIGINT Database ID SCHEMA_ID BIGINT Pattern ID TABLE_ID BIGINT Table ID CULUMN_NAME VARCHAR field name CULUMN_TYPE INT Field type

[0078] The table should be explained as follows: the field is a lower-level object of the table. It uses the field ID as a unique identifier to store the table ID to which it belongs. It also redundantly stores the instance ID, database ID, and schema ID. Indexes are created for the table ID, schema ID, database ID, and instance ID. More complex queries can be achieved through join operations with the upper-level metadata.

[0079] The core fields of the index metadata storage and their descriptions are shown in Table 8:

[0080] Table 8. Index Metadata Table (meta_index)

[0081] field name type Chinese meaning ID BIGINT Index ID INSTANCE_ID BIGINT Instance ID DB_ID BIGINT Database ID SCHEMA_ID BIGINT Pattern ID TABLE_ID BIGINT Table ID INDEX_NAME VARCHAR Index name INDEX_TYPE INT Index type

[0082] The table should be explained as follows: As a lower-level object of the table, the index uses the index ID as a unique identifier to store the table ID to which it belongs, and redundantly stores the instance ID, database ID and schema ID. Indexes are created for the table ID, schema ID, database ID and instance ID. More complex queries can be achieved through join operations with the upper-level metadata table.

[0083] When performing single-table or joined-table queries, you can filter data using indexed fields. For example, when the search criteria involve instance and table-level fields, you can simply join metadata_instance and metadata_table by INSTANCE_ID.

[0084] Example 2

[0085] In this embodiment, a computer device is provided, including a memory and a processor. The memory is used to store instructions, and the processor is used to execute the instructions, causing the computer device to perform the steps of implementing the above-described metadata storage method that supports efficient access.

[0086] Example 3

[0087] In this embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed, implements the steps of the above-described metadata storage method that supports efficient access.

[0088] The computer-readable storage medium may include various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory, random access memory, magnetic disks, or optical disks.

[0089] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A metadata storage method supporting efficient access, characterized by: The metadata is stored into a relational database by describing the metadata through a relational model; The metadata is stored in layers, with upper layer metadata stored first, and lower layer metadata stored later; The metadata stored in layers is compatible with heterogeneous databases; The metadata stored in layers includes metadata of single table storage instances, databases, modes, tables, views, columns and index objects respectively; When collecting the metadata, upper layer metadata is collected first, in the order of instance-database-mode, and lower layer objects are collected in the order of instance-database-mode, and when storing lower layer tables, primary keys of all upper layer tables are redundantly stored; Indexes are established for the primary keys of all upper layer tables in the heterogeneous databases to support efficient access of the metadata. A table is designed for storing metadata of each layer, and is stored into the relational database; 2. The metadata storage method for supporting efficient access of claim 1, wherein: Fields of the table include all attributes of the metadata of the layer, and must have a self-incrementing primary key and primary keys of all upper layer tables. The relational database includes MySQL, Oracle, SQLserver, DB2 and PostgreSQL.

3. The metadata storage method supporting efficient access of claim 2, wherein: Management rules of the heterogeneous databases include that the metadata is designed mainly in multi-layer database types, and default values are filled in corresponding fields in less-layer database types.

4. The metadata storage method supporting efficient access of claim 3, wherein: A metadata storage and query process is as follows:

5. The metadata storage method supporting efficient access of claim 4, wherein: First, the metadata is collected, then the metadata is stored in layers, then the metadata is queried, then the metadata is verified and accessed, and finally, a query result is returned. Metadata storage includes instance metadata storage, database metadata storage, mode metadata storage, table metadata storage, view metadata storage, column metadata storage and index metadata storage.

6. The metadata storage method supporting efficient access of claim 5, wherein: An instance metadata table is the uppermost table of database metadata storage, and the core is a unique instance ID and common attributes of all DBs under the instance ID, and the instance ID is used as a unique identifier for association of lower layer objects; 7. The metadata storage method supporting efficient access of claim 6, wherein: A database metadata table is a lower layer object of the instance metadata table, and stores a database ID as a unique identifier and an instance ID to which the database metadata table belongs; A mode metadata table is a lower layer object of the database metadata table, and stores a mode ID as a unique identifier, a database ID to which the mode metadata table belongs and an instance ID redundantly; A table metadata table is a lower layer object of the mode metadata table, and stores a table ID as a unique identifier, a mode ID to which the table metadata table belongs, an instance ID redundantly and a database ID; A view metadata table is a lower layer object of the mode metadata table, and stores a view ID as a unique identifier, a mode ID to which the view metadata table belongs, an instance ID redundantly and a database ID; A column metadata table is a lower layer object of the table metadata table, and stores a column ID as a unique identifier, a table ID to which the column metadata table belongs, an instance ID redundantly, a database ID and a mode ID; An index metadata table is a lower layer object of the table metadata table, and stores an index ID as a unique identifier, a table ID to which the index metadata table belongs, an instance ID redundantly, a database ID and a mode ID. The memory is configured to store instructions.

8. A computer device, comprising: ​ ​ a processor configured to execute the instructions to cause the device to perform a method of metadata storage supporting efficient access as claimed in any of claims 1-7.

9. A computer readable storage medium having stored thereon a computer program, characterized in that: the computer program which, when executed by a computer, performs a method of metadata storage supporting efficient access as claimed in any of claims 1-7.

Images