Method and device for adaptive table structure between heterogeneous databases based on metadata files

Abstract

A method and device for adaptive table structure between heterogeneous databases based on metadata files, belonging to the technical field of database management. The method includes the steps of: obtaining data from the source business system and generating a DDL table structure information file; parsing the DDL table structure information file to extract table structure information; based on the parsed metadata information, through the mapping relationship of heterogeneous database field type and length conversion, performing mapping conversion to form the converted DDL information; based on the converted DDL information, judging the data source connection, obtaining the database type, and creating a database external table or a temporary table; by comparing the metadata information of the database external table or the temporary table with that of the formal table, identifying the table structure differences, and automatically adapting the structure information of the database formal table to achieve table structure adaptation. The present invention realizes table structure adaptation, improves the efficiency and accuracy of table structure change, enhances work efficiency and data quality, and effectively reduces the daily operation cost.

Description

Technical Field

[0001] The present invention relates to a method and device for adapting table structures between heterogeneous databases based on metadata files, belonging to the technical field of database management. Background Art

[0002] In the financial industry, due to the diversity and complexity of business systems, frequent changes in table structures have become the norm. However, the existing methods for changing database table structures rely heavily on notifications from source systems and require manual impact analysis and script development.

[0003] This manual method has many deficiencies: First, the operation and maintenance cost is high and it is error-prone because it needs to handle frequent changes in hundreds of systems and tens of thousands of tables, resulting in a large amount of manual work and easily causing data quality problems in complex environments; Second, the system coupling degree is high, and the changes between the source system and the data system are strongly coupled. Once the changes are not synchronized, it is easy to cause batch errors; Finally, it is difficult to convert heterogeneous databases. Due to significant differences in syntax, field types, and length definitions between different database systems (such as TDH, Gauss DB (DWS), etc.), manual conversion is extremely likely to cause exceptions.

[0004] Therefore, there is an urgent need for an automated and low-coupling method for adapting table structures between heterogeneous databases to solve the above problems. Summary of the Invention

[0005] To solve the problems of large operation and maintenance workload, poor change flexibility, and high coupling degree with the source business system caused by changes in database table structures in the prior art, the present invention proposes a method and device for adapting table structures between heterogeneous databases based on metadata files.

[0006] The technical solution adopted by the present invention to solve its technical problems is as follows:

[0007] In a first aspect, a method for adapting table structures between heterogeneous databases based on metadata files provided by an embodiment of the present invention includes the following steps:

[0008] Step S1, obtain data from the source business system and generate a metadata information file, where the metadata information file is a DDL table structure information file;

[0009] Step S2, parse the DDL table structure information file, verify and extract table structure information, where the table structure information includes table name, field name, field order, field type, field length, and field comment;

[0010] Step S3, based on the parsed metadata information, through the mapping conversion of heterogeneous database field types and lengths, perform the mapping conversion of the DDL source database type, length, and target database field type, length, and form the converted DDL information;

[0011] Step S4, based on the converted DDL information, judge the data source connection, obtain the database type, and create a database external table or a temporary table, where the database external table or the temporary table supports parallel execution by multiple legal person banks simultaneously;

[0012] Step S5, by comparing the metadata information of the database external table or the temporary table with that of the formal table, identify the table structure differences, automatically adapt to the structure information of the database formal table, and achieve table structure self - adaptation.

[0013] As a possible implementation manner of this embodiment, the step S1 includes:

[0014] Step S11, based on the standard download specification, obtain the metadata DDL information of the table from the source business system;

[0015] Step S12, generate a metadata file in XML format according to the metadata DDL information, and the naming format of the metadata file is <row number>_<date>__<frequency flag>_<extraction strategy>.ddl; the metadata file includes two parts: a file header and a content body;

[0016] Step S13, synchronously download the generated metadata file and the corresponding data file according to the preset download frequency.

[0017] As a possible implementation manner of this embodiment, the standard download specification defines the format requirements, content organization methods, and the correlation relationships among the data file, the metadata file, and the verification file.

[0018] As a possible implementation manner of this embodiment, the file header contains the version information and generation time of the metadata, and the content body contains the field definitions, data types, and field description information of the table.

[0019] As a possible implementation manner of this embodiment, the step S2 includes:

[0020] Step S21, parse the DDL table structure information file according to the standard download specification;

[0021] Step S22, based on the parsed download metadata DDL file, parse the metadata XML file according to the XML download specification;

[0022] Step S23: Perform format verification on the table name, database type, number of fields, and field type length from the parsed metadata XML file; then obtain the structural information of the table name, field name, field order, field type, field length, and Chinese comment.

[0023] As a possible implementation of this embodiment, the step S21 includes:

[0024] Read the DDL table structure information file;

[0025] Parse the table structure definition in the DDL table structure information file according to the standard download specification.

[0026] As a possible implementation of this embodiment, the step S22 includes:

[0027] Read the metadata XML file;

[0028] Parse the metadata tags in the metadata XML file according to the XML download specification to obtain the table structure information.

[0029] As a possible implementation of this embodiment, the step S23 includes:

[0030] Traverse the parsed metadata XML file;

[0031] Extract the table name, field name, field order, field type, field length, and Chinese comment respectively according to the tags in the metadata XML file.

[0032] As a possible implementation of this embodiment, the step S3 includes:

[0033] Step S31: Read the data source connection configuration to obtain the target database type;

[0034] Step S32: Read the field type mapping table of the target database to obtain the mapping relationship between the field type and the length; the field type mapping table can be set according to its own specifications or use fixed configurations, supporting field type mapping, length multiple expansion, maximum length limit, and default value, and there is no mutual influence between different databases;

[0035] Step S33: Based on the parsed metadata information, through the field type and length conversion mapping relationship of the target database, map and convert the DDL source database field type and length to the target database field type and length to form the latest converted DDL information.

[0036] As a possible implementation of this embodiment, the field type mapping table needs to be configured in advance, and when the target databases are different, the mapping relationships are different, and they need to be configured according to their respective development specifications as needed.

[0037] As a possible implementation manner of this embodiment, in step S33, process the type and length differences between the source database fields and the target database fields to ensure accurate conversion to the field type and length of the target database.

[0038] As a possible implementation manner of this embodiment, step S4 includes:

[0039] Step S41, based on the DDL information after the latest mapping conversion, judge the data source connection and obtain the database type;

[0040] Step S42, read the data source connection configuration, and according to the database type, obtain the latest mapped DDL field information;

[0041] Step S43, follow the naming specification requirements, and create a database external table or a temporary table according to the rule of creating one external table for one corporate bank and one table;

[0042] Among them, the specific process of creating the database external table or the temporary table is as follows:

[0043] Judge whether to create an external table each time. If so, after deleting the external table each time, create a database external table or a temporary table according to the DDL field information, where the external table includes the database external table or the temporary table;

[0044] If not, judge whether there is a structural difference between the DDL and the current database external table or temporary table, conduct a structural comparison. If there is a difference, reconstruct the external table. If not, it is default that the table structure has no change and there is no need to create a table each time; or create a temporary table using volatile and do not record the metadata.

[0045] As a possible implementation manner of this embodiment, the step of creating the database external table or the temporary table includes:

[0046] For the uniformly built system, create an external table according to the system structure uniformly built for the whole bank;

[0047] For the self-built system, create an external table by each member bank according to the system structure built by itself.

[0048] As a possible implementation manner of this embodiment, the step of judging whether to create an external table as needed is:

[0049] According to the preset conditions or user input, determine whether to perform the operation of creating an external table as needed to avoid excessive metadata pressure caused by frequent operations on the target database metadata.

[0050] As a possible implementation manner of this embodiment, the step of judging whether there is a structural difference between the DDL and the current external table is:

[0051] Compare the DDL field information with the field information of the current external table. If there are differences, determine that the external table needs to be rebuilt.

[0052] As a possible implementation of this embodiment, step S5 includes:

[0053] Step S51, perform a table structure comparison, compare metadata information based on the database external table or temporary table and the formal table, obtain a list of structure changes, and generate a structure change list SQL. The structure comparison includes comparisons of field order, name, type, length, and remarks;

[0054] Step S52, obtain a table structure change lock. When multiple legal person banks process in parallel, only the legal person bank that obtains the lock is allowed to execute the table structure change;

[0055] Step S53, execute the database table structure change according to the structure change list SQL;

[0056] Step S54, execute a structure information verification check, check whether the structure changes are consistent, and generate a change list log.

[0057] As a possible implementation of this embodiment, the performing a table structure comparison includes:

[0058] New table judgment. If the main table of the target database does not exist, create a new table;

[0059] New field judgment. Record the new field change information when the field order and name are inconsistent;

[0060] Deleted field processing. It is not allowed to delete fields from the formal database table. Assign null values to the deleted fields according to the field mapping relationship;

[0061] Field type modification judgment. Record the field type change information when the field types are inconsistent;

[0062] Field length modification judgment. Record the field length change information when the field lengths or precisions are inconsistent;

[0063] Field Chinese comment change judgment. Record the field Chinese comment change information when the Chinese comments are inconsistent;

[0064] Change lock judgment. Based on the change table structure and corporate banks, obtain the table structure change lock. Only the corporate bank that obtains the lock is allowed to execute the table structure change. Other banks will perform a sleep wait for the lock to be released to avoid multiple lines executing simultaneously and repeatedly operating on the target table structure, which may affect data accuracy and execution efficiency. The lock is divided into two levels: the metadata lock and the archive execution lock. When the metadata changes, add the metadata lock and release the lock after the change is completed. When continuing to execute the archive storage operation, add the archive execution lock to solve the problem of a single line executing repeatedly multiple times, avoiding data chaos and duplication, but not affecting multiple lines executing simultaneously.

[0065] As a possible implementation manner of this embodiment, the obtaining of the table structure change lock includes:

[0066] Before each corporate bank executes the table structure change, it attempts to obtain the structure change lock. The corporate bank that fails to obtain the lock will wait for a specified time and then retry to obtain the lock until the lock is obtained or the table structures are verified to be consistent.

[0067] As a possible implementation manner of this embodiment, the execution of the database table structure change includes:

[0068] Based on the verified structure difference list SQL, execute the SQL change operation to complete the table structure update and record the change log.

[0069] As a possible implementation manner of this embodiment, the execution of the structure information verification and check includes:

[0070] According to the downloaded DDL file, check whether the structure changes are consistent, check for any omissions or change errors, and form a change list log.

[0071] In a second aspect, an adaptive device for table structures between heterogeneous databases based on metadata files provided by an embodiment of the present invention includes:

[0072] A data collection module, configured to obtain data from the source business system and generate a metadata information file, where the metadata information file is a DDL table structure information file;

[0073] A file parsing module, configured to parse the DDL table structure information file, verify, check, and extract the table structure information, where the table structure information includes table name, field name, field order, field type, field length, and field comment;

[0074] A mapping conversion module, configured to perform mapping conversion of the DDL source database type, length, and target database field type, length based on the parsed metadata information through the heterogeneous database field type and length conversion mapping relationship to form the converted DDL information;

[0075] An external table creation module, which is used to determine a data source connection based on the converted DDL information, obtain a database type, and create a database external table or a temporary table, where the database external table or the temporary table supports parallel execution by multiple legal person banks simultaneously;

[0076] An adaptive adaptation module, which is used to identify table structure differences by comparing the metadata information of the database external table or the temporary table with that of the formal table, automatically adapt the structure information of the database formal table, and achieve table structure self-adaptation.

[0077] The beneficial effects produced by the technical solution of the embodiment of the present invention are as follows:

[0078] The present invention realizes table structure self-adaptation between heterogeneous databases based on metadata information, improves the efficiency and accuracy of table structure changes; is compatible with multiple database types, supports the multi-issuer batch mode and scenarios of one set of table structures or multiple sets of table structures for different rows, meeting the needs of different enterprise types; through the automatic change of the table structure by metadata information, the work efficiency and data quality are improved, and the daily operation cost is effectively reduced.

[0079] For the operating system constructed based on the technical solution of the present invention, when the source business system archives data, the metadata information is archived synchronously. Based on the metadata information, the data system automatically changes the table structure during the batch process, greatly reducing the input cost of manual changes, solving the strong coupling problem between the source business system and the data system, and can also perform quality verification on the archived business data based on the metadata information, effectively improving the data quality. Description of the Drawings

[0080] Figure 1 is a flowchart of a method for table structure self-adaptation between heterogeneous databases based on a metadata file shown according to an exemplary embodiment;

[0081] Figure 2 is a schematic structural diagram of a device for table structure self-adaptation between heterogeneous databases based on a metadata file shown according to an exemplary embodiment;

[0082] Figure 3 is a flowchart of generating a change SQL by performing table structure comparison shown according to an exemplary embodiment. Detailed Embodiments

[0083] To more clearly illustrate the technical features of the solution of the present invention, the present invention will be elaborated in detail below through specific embodiments and in combination with its drawings.

[0084] As Figure 1 shown, a method for table structure self-adaptation between heterogeneous databases based on a metadata file provided by an embodiment of the present invention includes the following steps:

[0085] Step S1, obtain data from the source business system and generate a metadata information file, where the metadata information file is a DDL table structure information file;

[0086] Step S2, parse the DDL table structure information file, verify and extract the table structure information, where the table structure information includes table name, field name, field order, field type, field length, and field comment;

[0087] Step S3, based on the parsed metadata information, through the heterogeneous database field type and length conversion mapping relationship, perform the mapping conversion of the DDL source database type, length, and target database field type, length, and form the converted DDL information;

[0088] Step S4, based on the converted DDL information, judge the data source connection, obtain the database type, create a database external table or a temporary table, where the database external table or the temporary table supports parallel execution by multiple legal person banks simultaneously;

[0089] Step S5, by comparing the metadata information of the database external table or the temporary table with that of the formal table, identify the table structure differences, automatically adapt to the structure information of the database formal table, and achieve table structure self - adaptation.

[0090] As a possible implementation manner of this embodiment, the step S1 includes:

[0091] Step S11, based on the standard download specification, obtain the metadata DDL information of the table from the source business system;

[0092] Step S12, generate a metadata file in XML format according to the metadata DDL information, where the naming format of the metadata file is <line number>_<date>__<frequency flag>_<extraction strategy>.ddl; the metadata file includes two parts: a file header and a content body;

[0093] Step S13, synchronously download the generated metadata file and the corresponding data file according to the preset download frequency.

[0094] As a possible implementation manner of this embodiment, the standard download specification defines the format requirements, content organization methods, and the correlation relationships among the data file, the metadata file, and the verification file.

[0095] As a possible implementation manner of this embodiment, the file header contains the version information and generation time of the metadata, and the content body contains the field definitions, data types, and field description information of the table.

[0096] As a possible implementation manner of this embodiment, the step S2 includes:

[0097] Step S21, parse the DDL table structure information file according to the standard downgrading specification;

[0098] Step S22, based on the parsed DDL metadata file, parse the metadata XML file according to the XML downgrading specification;

[0099] Step S23, from the parsed metadata XML file, ensure the metadata information is correct by performing format verification on elements such as table name, database type, number of fields, and field type length; then obtain the structural information of the table name, field name, field order, field type, field length, and Chinese comment.

[0100] As a possible implementation of this embodiment, the step S21 includes:

[0101] Read the DDL table structure information file;

[0102] Parse the table structure definition in the DDL table structure information file according to the standard downgrading specification.

[0103] As a possible implementation of this embodiment, the step S22 includes:

[0104] Read the metadata XML file;

[0105] Parse the metadata tags in the metadata XML file according to the XML downgrading specification to obtain the table structure information.

[0106] As a possible implementation of this embodiment, the step S23 includes:

[0107] Traverse the parsed metadata XML file;

[0108] Extract the table name, field name, field order, field type, field length, and Chinese comment respectively according to the tags in the metadata XML file.

[0109] As a possible implementation of this embodiment, the step S3 includes:

[0110] Step S31, read the data source connection configuration to obtain the target database type;

[0111] Step S32, read the field type mapping table of the target database to obtain the mapping relationship between field types and lengths; the field type mapping table is not limited to fixed configurations, and can also be set by itself, supporting field type mapping, length multiple expansion, maximum length limit, and default values, etc., and different databases do not affect each other;

[0112] Step S33: Based on the parsed metadata information, through the field type and length conversion mapping relationship of the target database, map and convert the DDL source database field type and length to the target database field type and length, forming the latest converted DDL information.

[0113] As a possible implementation of this embodiment, the field type mapping table needs to be configured in advance. When the target databases are different, the mapping relationships are different and need to be configured according to their respective development specifications as required.

[0114] As a possible implementation of this embodiment, in step S33, handle the differences in the field types and lengths between the source database fields and the target database fields to ensure accurate conversion to the field types and lengths of the target database.

[0115] As a possible implementation of this embodiment, step S4 includes:

[0116] Step S41: Based on the latest mapped and converted DDL information, judge the data source connection and obtain the database type;

[0117] Step S42: Read the data source connection configuration and obtain the latest mapped DDL field information according to the database type;

[0118] Step S43: Follow the naming specification requirements and create a database external table or a temporary table according to the rule of creating one external table for one legal person bank and one table;

[0119] Among them, the specific process of creating the database external table or the temporary table is as follows:

[0120] Judge whether to create an external table every time. If so, after deleting the external table each time, create a database external table or a temporary table according to the DDL field information. The external table includes the database external table or the temporary table;

[0121] If not, judge whether there are structural differences between the DDL and the current database external table or temporary table, conduct a structural comparison. If there are differences, rebuild the external table. If not, it is default that the table structure has no change and there is no need to create a table every time, which improves the execution efficiency and reduces the metadata pressure on the target database; or use volatile to create a temporary table in a way that the metadata is not recorded, and the creation method is flexible and configurable.

[0122] Meanwhile, the database external table or the temporary table supports parallel execution by multiple legal person banks at the same time. Adopting the method of one row and one external table or temporary table, the parameter configuration of the external table can be flexibly specified, such as delimiter, character set, file path, delimiter, etc., and the table partition, table index, and table format can be flexibly configured; at the same time, data redistribution such as runstats and analyze can also be carried out as required to improve the execution efficiency.

[0123] As a possible implementation of this embodiment, the step of creating a database external table or a temporary table includes:

[0124] For the unified construction system, create an external table according to the system structure built uniformly across the bank;

[0125] For the self-built system, create an external table by each member bank according to the system structure built by itself.

[0126] As a possible implementation of this embodiment, the step of determining whether to create an external table as needed is:

[0127] According to preset conditions or user input, determine whether to perform the operation of creating an external table as needed, so as to avoid excessive metadata pressure caused by frequent operations on the target database metadata.

[0128] As a possible implementation of this embodiment, the step of determining whether there is a structural difference between the DDL and the current external table is:

[0129] Compare the DDL field information with the field information of the current external table. If there are differences, it is determined that the external table needs to be rebuilt.

[0130] As a possible implementation of this embodiment, step S5 includes:

[0131] Step S51: Conduct a table structure comparison, compare the metadata information based on the database external table or temporary table and the formal table, obtain a list of structure changes, and generate a structure change list SQL. The structure comparison includes the comparison of field order, name, type, length, and remarks;

[0132] Step S52: Obtain a table structure change lock. When multiple corporate banks process in parallel, only the corporate bank that obtains the lock is allowed to execute the table structure change;

[0133] Step S53: Execute the database table structure change according to the structure change list SQL;

[0134] Step S54: Execute the structure information verification check, check whether the structure changes are consistent, and generate a change list log.

[0135] As a possible implementation of this embodiment, the conduct of the table structure comparison includes:

[0136] New table judgment. If the main table of the target database does not exist, create a new table;

[0137] New field judgment. When the field order and name are inconsistent, record the new field change information;

[0138] Delete field processing. It is not allowed to delete fields in the official database table. Instead, null values are assigned to the deleted fields according to the field mapping relationship.

[0139] Field type modification judgment. When the field types are inconsistent, information on field type changes is recorded.

[0140] Field length modification judgment. When the field lengths or precisions are inconsistent, information on field length changes is recorded.

[0141] Field Chinese comment change judgment. When the Chinese comments are inconsistent, information on field Chinese comment changes is recorded.

[0142] Change lock judgment. Based on the changed table structure and the corporate bank, a table structure change lock is obtained. Only the corporate bank that obtains the lock is allowed to execute the table structure change. Other banks will sleep and wait for the lock to be released, avoiding multiple lines executing simultaneously and repeatedly operating on the target table structure, which may affect data accuracy and execution efficiency. The lock is divided into two levels: the metadata lock and the archive execution lock. When the metadata changes, the metadata lock is added and released after the change is completed. When continuing to execute the archive storage operation, the archive execution lock is added to solve the problem of a single line executing repeatedly multiple times, avoiding data chaos and duplication, but not affecting multiple lines executing simultaneously.

[0143] As a possible implementation of this embodiment, the obtaining of the table structure change lock includes:

[0144] Before each corporate bank executes the table structure change, it attempts to obtain the structure change lock. The corporate bank that fails to obtain the lock will wait for a specified time and then retry to obtain the lock until the lock is obtained or the table structure check is consistent.

[0145] As a possible implementation of this embodiment, the execution of the database table structure change includes:

[0146] Based on the SQL for the checked structure difference list, execute the SQL change operation to complete the table structure update and record the change log.

[0147] As a possible implementation of this embodiment, the execution of the structure information verification and check includes:

[0148] According to the DDL file of the lower level, check whether the structure changes are consistent, check whether there are any omissions or change errors, and form a change list log.

[0149] As Figure 2 shown, an adaptive table structure device between heterogeneous databases based on metadata files provided by an embodiment of the present invention includes:

[0150] A data collection module, which is used to obtain data from the source business system and generate a metadata information file, and the metadata information file is a DDL table structure information file.

[0151] A file parsing module, which is used to parse the DDL table structure information file, verify and extract the table structure information, and the table structure information includes table name, field name, field order, field type, field length and field comment;

[0152] A mapping and conversion module, which is used to perform mapping conversion of the DDL source database type, length and the target database field type, length based on the parsed metadata information through the heterogeneous database field type and length conversion mapping relationship, and form the converted DDL information;

[0153] An external table creation module, which is used to judge the data source connection based on the converted DDL information, obtain the database type, and create a database external table or a temporary table, and the database external table or temporary table supports multi-legal-person banks to execute in parallel at the same time;

[0154] An adaptive adaptation module, which is used to identify the table structure differences by comparing the metadata information of the database external table or temporary table and the formal table, and automatically adapt the structure information of the database formal table to achieve table structure self-adaptation.

[0155] The present invention is based on the metadata DDL file archived from the source business system. By parsing the metadata DDL file information, it performs mapping conversion of the field type and field length between heterogeneous databases, creates a database external table or a temporary table, compares it with the structure of the database formal table, identifies the table structure differences, automatically adapts the structure information of the database formal table to achieve table structure self-adaptation; and by adding a database table structure change lock, it solves the problem of single-instance multi-legal-person synchronous batch running and self-adaptation.

[0156] The design idea of the present invention is as follows:

[0157] (1) Metadata file generation: The source business system generates a metadata information file, that is, a DDL table structure information file, according to the metadata file generation specification;

[0158] (2) Metadata file parsing: Parse the DDL table structure information file according to the metadata file archival specification;

[0159] (3) Metadata type and length mapping: Based on the parsed metadata information, perform mapping conversion of the DDL source database type, length and the target database field type, length through the heterogeneous database field type and length conversion mapping relationship, and form the latest converted DDL information;

[0160] (4) Create an external table or a temporary table: Based on the latest mapped and converted DDL information, judge the data source connection, obtain the database type, and create a database external table or a temporary table, and the database external table or temporary table supports multi-legal-person banks to execute in parallel at the same time;

[0161] (5) Database table structure self - adaptation: Compare metadata information between the external database table and the formal table. Based on the comparison results, identify the differences in the table structure, and change the formal database table to achieve the self - adaptation of the database table structure. In the case of a single - instance multi - legal - person scenario where the same set of tables is shared, the program will automatically add a database table structure change lock to avoid concurrent execution of structure change operations on multiple lines. After a single legal - person row that has obtained the change lock has completed the execution, other rows do not need to perform self - adaptation changes anymore, reducing unnecessary change operations.

[0162] (6) Based on the technical solution of the present invention, a set of executable programs is finally formed.

[0163] The specific implementation process of the present invention is as follows.

[0164] (1) Generation of metadata files.

[0165] The download of the source system metadata DDL follows the standard download specification, including data files, metadata files (structure files), and verification files. Among them, the metadata file is in XML format and is uniformly named <row number>_<date>__<frequency flag>_<extraction strategy>.ddl according to the table. The file consists of two parts: a file header and a main content body. After the file is generated, it is downloaded synchronously with the data file according to the download frequency.

[0166] Support for configurable data sources, customizing formats such as file format, naming, frequency, incremental / full - volume, character set, etc., and supporting the conversion of rare characters (conversion between unicode code and decimal); and supporting transmission to the specified machine directory through the ESB file for downstream use; it can also be used for self - parsing.

[0167] (2) Parsing of metadata files.

[0168] Parse the DDL table structure information file according to the metadata file download specification. Based on the downloaded metadata DDL file, parse the metadata XML file according to the XML download specification to obtain structural information such as table name, field name, field order, field type, field length, and Chinese comment.

[0169] (3) Metadata type and length mapping.

[0170] Read the data source connection configuration, obtain the target database type, read the field type mapping table of the target database, obtain the mapping relationship between the field type and length, process the type and length differences between the source field type and the target field, and accurately convert them into the field type and length of the target database. The field type mapping table of the database needs to be configured in advance. Different target databases have different mapping relationships, and they need to be configured according to their respective development specifications as required. The field type mapping table is not limited to fixed configurations, but can also set its own specifications, supporting field type mapping, length multiple expansion, maximum length limit, default value, etc., and there is no mutual influence between different databases.

[0171] The present invention is compatible with multiple database types, supports the multi-legal person batch mode and different scenarios of one set of tables or multiple sets of tables per row, and meets the needs of different enterprise types.

[0172] (4) Create an external table or a temporary table.

[0173] Read the data source connection configuration and obtain the target database type. Different databases have differences in syntax and processes. Based on the target database, obtain the latest mapped DDL field information and create an external table following the naming specification requirements. External table creation rules: Create one external table for each legal person bank and each table. The structure of the unified construction system (a system uniformly built for the whole bank) is unified, and the structures of the self-built systems (systems built by member banks themselves with different structures for each bank) are different for each bank.

[0174] Judge whether to create an external table each time. If so, after deleting the external table each time, create a database external table or a temporary table according to the DDL field information. The external table includes the database external table or the temporary table;

[0175] If not, then judge whether there are structural differences between the DDL and the current database external table or temporary table, conduct a structural comparison. If there are differences, rebuild the external table. If not, it is default that the table structure has not changed, and there is no need to create a table each time, improving the execution efficiency and reducing the metadata pressure on the target database; or use volatile to create a temporary table in a way that does not record metadata, and the creation method is flexible and configurable.

[0176] At the same time, the database external table or temporary table supports parallel execution by multiple legal person banks simultaneously. Using the method of one external table or temporary table per row, the parameter configuration of the external table can be flexibly specified, such as delimiter, character set, file path, delimiter, etc., and the table partition, table index, and table format can be flexibly configured; at the same time, data redistribution such as runstats and analyze can also be performed as required to improve the execution efficiency.

[0177] (5) Database table structure self-adaptation.

[0178] Support parallel batch processing for multiple legal entities. The structure change is completed in multiple links according to the processing flow to change the database table structure. The overall process is a transaction.

[0179] 1. Compare the table structures and generate the change SQL.

[0180] As Figure 3 shown, compare the table structures based on the external table and the target database main table. The comparison includes structural information such as field order, name, type, length, and remarks. Conduct a structural comparison, obtain the structure change list, and generate the structure change list SQL.

[0181] (1) New table processing: Compare with the target database main table to determine if it exists. If it exists, do not create the external table. Otherwise, create a new table according to the specified specifications without further structural verification, close the transaction, and complete the adaptive process.

[0182] (2) New field detection: Based on the field order, determine if the field name is the same as that of the target database main table. If not, it is determined that there are new fields, record the change information, and generate the structure change list SQL; if they are the same, continue the verification.

[0183] (3) Deleted field processing: According to the database specification constraints, prohibit deleting fields from the target database main table. When a deleted field scenario is detected in the structure change comparison, do not delete it, but assign a null value to the corresponding field in the target database main table according to the field mapping relationship.

[0184] (4) Field type modification detection: After matching by field name, compare whether the field type has changed. If the types are inconsistent, record the change information and write it into the structure change list SQL.

[0185] (5) Field length modification detection: For numeric, character, etc. fields, determine if their length or precision has changed. If it has changed, record the change information and write it into the structure change list SQL.

[0186] (6) Chinese field comment detection: Determine if the Chinese comment has changed. If it has, record the change information and write it into the structure change list SQL.

[0187] (7) Structure change lock detection: Determine the change lock. Based on the change table structure and the corporate bank, obtain the table structure change lock. Only the corporate bank that obtains the lock is allowed to perform the table structure change, and other banks will perform a sleep wait for the lock to be released, avoiding multiple lines from executing simultaneously and repeating operations on the target table structure, which may affect data accuracy and execution efficiency. The lock is divided into two levels: the metadata lock and the archiving execution lock. When the metadata changes, add the metadata lock, and release the lock after the change is completed. When continuing to perform the archiving and warehousing operation, add the archiving execution lock to solve the problem of a single line executing repeatedly multiple times simultaneously, avoiding data disorder and duplication, but not affecting multiple lines from executing simultaneously.

[0188] The present invention improves work efficiency and data quality through the automatic table structure change of metadata information, and effectively reduces the daily operation cost.

[0189] 2. Obtain the table structure change lock.

[0190] The program based on the method of the present invention supports multi-corporate parallel batch processing. However, since the batch order and time of each line are irregular, in order to avoid each line from changing the table structure, reducing the database pressure and useless operations, a table structure change lock (table-level lock) is added.

[0191] Each corporate bank must obtain the structure change lock to perform the table structure change. Only the batch of the corporate bank that obtains the lock can perform the table structure change. For the batch that fails to obtain the lock, it needs to wait and sleep. After waiting for the specified time interval, perform the first step of judgment again to determine whether the table structure is consistent. If the table structure check is consistent, the adaptive process is completed. If not, judge again to obtain the structure change lock.

[0192] 3. Database table structure change.

[0193] After obtaining the structure change lock, based on the SQL of the verified structure difference list, execute the SQL change to complete the table structure update and register the log of the record change.

[0194] 4. Structure information verification and validation.

[0195] After the structure change is executed, according to the DDL file of the next level, check again whether the structure has changed consistently and whether there are any omissions or change errors. Finally, form a change list log for subsequent analysis and processing. Close the transaction to complete the structure adaptation.

[0196] 5. Special processing scenarios.

[0197] For the scenario of deleting a table, obtain the delete table identifier and then automatically skip it without performing structure adaptation.

[0198] The computer program formed based on the above method realizes table structure adaptation during the night batch running process, achieves consistency processing with the source system's downstream structural information, reduces the degree of coupling with the source system, and improves the efficiency of operation and maintenance changes.

[0199] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit it. Although the present invention has been described in detail with reference to the above embodiments, ordinary technicians in the relevant field should understand that the specific implementation methods of the present invention can still be modified or replaced by equivalents. Any modification or equivalent replacement that does not depart from the spirit and scope of the present invention should be covered within the scope of protection of the claims of the present invention.

Claims

1. An adaptive method for table structures between heterogeneous databases based on metadata files, characterized in that, It includes the following steps: Step S1: Obtain data from the source business system and generate a metadata information file, where the metadata information file is a DDL table structure information file; Step S2: Parse the DDL table structure information file, verify and extract the table structure information, where the table structure information includes table name, field name, field order, field type, field length, and field comment; Step S3: Based on the parsed metadata information, through the heterogeneous database field type and length conversion mapping relationship, perform the mapping conversion of the DDL source database type, length, and target database field type, length to form the converted DDL information; Step S4: Based on the converted DDL information, judge the data source connection, obtain the database type, and create a database external table or a temporary table, where the database external table or the temporary table supports parallel execution by multiple legal person banks simultaneously; Step S5: By comparing the metadata information of the database external table or the temporary table and the formal table, identify the table structure differences, automatically adapt to the structure information of the database formal table, and achieve table structure self - adaptation; The said Step S4 includes: Step S41: Based on the latest mapped and converted DDL information, judge the data source connection and obtain the database type; Step S42: Read the data source connection configuration, and according to the database type, obtain the latest mapped DDL field information; Step S43: Follow the naming specification requirements, and create a database external table or a temporary table according to the rule of creating one external table for one legal person bank and one table; Among them, the specific process of creating the database external table or the temporary table is: Judge whether to create an external table each time. If so, after deleting the external table each time, create a database external table or a temporary table according to the DDL field information, where the external table includes the database external table or the temporary table; If not, judge whether there are structural differences between the DDL and the current database external table or the temporary table, conduct a structure comparison. If there are differences, reconstruct the external table. If not, it is default that the table structure has no change and there is no need to create a table each time; or use the way of creating a temporary table with volatile and not recording metadata; The said Step S5 includes: Step S51: Conduct a table structure comparison, compare the metadata information based on the database external table or the temporary table and the formal table, obtain a structure change list, and generate a structure change list SQL. The structure comparison includes the comparison of field order, name, type, length, and remarks; Step S52: Obtain a table structure change lock. When multiple legal person banks process in parallel, only the legal person bank that obtains the lock is allowed to execute the table structure change; Step S53: Execute the database table structure change according to the structure change list SQL; Step S54: Execute the structure information verification check, check whether the structure changes are consistent, and generate a change list log; 2. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 1, wherein The said Step S1 includes: Step S11: Based on the standard lower - file specification, obtain the table metadata DDL information from the source business system; Step S12, generating a metadata file in XML format according to the metadata DDL information, wherein the naming format of the metadata file is <row number>_<date>__<frequency flag>_<extraction strategy>.ddl; the metadata file includes two parts: a file header and a content body; Step S13, downloading the generated metadata file and the corresponding data file synchronously according to the preset downloading frequency.

3. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 2, characterized in that The standard file defines the format requirements, content organization methods and mutual relationships of the data files, metadata files and verification files.

4. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 2, characterized in that, The file header includes the version information and generation time of the metadata, and the content body includes the field definition, data type and field description information of the table.

5. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 1, characterized in that, The step S2 comprises: Step S21, parsing the DDL table structure information file according to the standard file specification; Step S22, based on the parsed metadata DDL file, parse the metadata XML file according to the XML file specification; Step S23, perform format check on the table name, database type, number of fields and field type length from the parsed metadata XML file; then obtain the structure information of the table name, field name, field order, field type, field length and Chinese annotations.

6. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 1, wherein The step S3 comprises: Step S31, read the data source connection configuration and obtain the target database type; Step S32, reading the field type mapping table of the target database to obtain the mapping relationship between the field type and the length; the field type mapping table may be self-set or fixedly configured to support field type mapping, length multiple expansion, maximum length limit and default value, and different databases do not affect each other; Step S33, based on the parsed metadata information, the DDL source database field type and length mapping is converted to the target database field type and length through the field type and length conversion mapping relationship of the target database to form the latest converted DDL information.

7. The method for adapting table structures between heterogeneous databases based on metadata files according to claim 1, wherein The table structure comparison includes: Add table judgment, if the main table of the target database does not exist, create a new table; Added new field judgment, recording the new field change information when the field order and name are inconsistent; Delete field processing: Field deletion is not allowed in the official database table. The deleted field will be assigned a null value according to the field mapping relationship. Field type modification judgment, recording field type change information when field types are inconsistent; Field length modification judgment, recording field length change information when field length or precision is inconsistent; Field Chinese annotation change judgment, based on the Chinese annotation inconsistency, record the field Chinese annotation change information; Change lock judgment: based on the change table structure and legal person row, obtain the table structure change lock, and only allow the legal person row that has obtained the lock to execute the table structure change, while other rows wait for the lock to be released.

8. An inter-heterogeneous database table structure self-adaptive device based on a metadata file, characterized in that include: The data acquisition module is used to obtain data from the source business system and generate a metadata information file, wherein the metadata information file is a DDL table structure information file; A file parsing module, which is used to parse the DDL table structure information file, verify and extract the table structure information, where the table structure information includes table name, field name, field order, field type, field length, and field comment; A mapping and conversion module, which is used to perform mapping and conversion of the DDL source database type, length, and target database field type, length based on the parsed metadata information through the heterogeneous database field type and length conversion mapping relationship to form the converted DDL information; An external table creation module, which is used to judge the data source connection based on the converted DDL information, obtain the database type, and create a database external table or a temporary table, where the database external table or temporary table supports parallel execution by multiple corporate banks simultaneously; An adaptive adaptation module, which is used to identify the table structure differences by comparing the metadata information of the database external table or temporary table and the formal table, and automatically adapt to the structure information of the database formal table to achieve table structure self - adaptation; The specific process of the external table creation module for judging the data source connection, obtaining the database type, and creating a database external table or a temporary table based on the converted DDL information is as follows: Step S41, based on the latest mapped and converted DDL information, judge the data source connection and obtain the database type; Step S42, read the data source connection configuration, and obtain the latest mapped DDL field information according to the database type; Step S43, follow the naming specification requirements, and create a database external table or a temporary table according to the rule of creating one external table for one corporate bank and one table; Among them, the specific process of creating the database external table or temporary table is as follows: Judge whether to create an external table each time. If so, after deleting the external table each time, create a database external table or a temporary table according to the DDL field information, where the external table includes a database external table or a temporary table; If not, judge whether there are structural differences between the DDL and the current database external table or temporary table, conduct a structure comparison. If there are differences, reconstruct the external table. If not, it is defaulted that the table structure has not changed and there is no need to create a table each time; or use the volatile method to create a temporary table without recording metadata; The specific process of the adaptive adaptation module for identifying the table structure differences by comparing the metadata information of the database external table or temporary table and the formal table, and automatically adapting to the structure information of the database formal table to achieve table structure self - adaptation is as follows: Step S51, conduct a table structure comparison, compare the metadata information based on the database external table or temporary table and the formal table, obtain a structure change list, and generate a structure change list SQL. The structure comparison includes the comparison of field order, name, type, length, and remarks; Step S52, obtain a table structure change lock. When multiple corporate banks are processing in parallel, only the corporate bank that obtains the lock is allowed to execute the table structure change; Step S53, execute the database table structure change according to the structure change list SQL; Step S54, execute the structure information verification and check, check whether the structure change is consistent, and generate a change list log.

Images