Metadata file-based table structure self-adaption method and device between heterogeneous databases

Abstract

The invention discloses a metadata file-based table structure self-adaption method and device between heterogeneous databases, and belongs to the technical field of database management, and the method comprises the following steps: obtaining data from a source service system, and generating a DDL table structure information file; analyzing the DDL table structure information file, and extracting table structure information; based on the analyzed metadata information, mapping conversion is carried out through a heterogeneous database field type and length conversion mapping relation, and converted DDL information is formed; based on the converted DDL information, data source connection is judged, a database type is obtained, and a database appearance or a temporary table is created; by comparing metadata information of a database appearance or a temporary table and a formal table, table structure difference is identified, structure information of the formal table of the database is automatically adapted, and table structure self-adaption is achieved. The table structure self-adaption is realized, the table structure changing efficiency and accuracy are improved, the working efficiency and the data quality are improved, and the daily operation cost is effectively reduced.

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 highly rely on notifications from source systems and require manual impact analysis and script development.

[0003] There are many deficiencies in this manual method: First, the operation and maintenance cost is high and it is error-prone because it is necessary to handle frequent changes in hundreds of systems and tens of thousands of tables. The manual workload is large, and data quality problems are likely to occur in complex environments. Second, the system coupling degree is high. There is a strong coupling between changes in the source system and the data system. Once the changes are not synchronized, batch errors are likely to occur. 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 of heterogeneous databases to solve the above problems. Summary of the Invention

[0005] In order 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 solutions adopted by the present invention to solve its technical problems are as follows: 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: 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 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 mapping relationship of heterogeneous database field type and length conversion, perform 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, determine the data source connection, obtain the database type, and create an external database table or a temporary table, where the external database table or the temporary table supports concurrent execution by multiple corporate banks; Step S5: By comparing the metadata information of the external database table or the temporary table with that of the formal table, identify the table structure differences, and automatically adapt to the structure information of the formal database table to achieve table structure self - adaptation.

[0007] As a possible implementation of this embodiment, step S1 includes: Step S11: Based on the standard download specification, obtain the metadata DDL information of the table from the source business system; Step S12: Generate a metadata file in XML format according to the metadata DDL information. 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; Step S13: Synchronously download the generated metadata file and the corresponding data file according to the preset download frequency.

[0008] As a possible implementation 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.

[0009] As a possible implementation 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.

[0010] As a possible implementation of this embodiment, step S2 includes: Step S21: Parse the DDL table structure information file according to the standard download specification; Step S22: Based on the downloaded metadata DDL file obtained by parsing, parse the metadata XML file according to the XML download specification; Step S23: From the parsed metadata XML file, perform format verification on the table name, database type, number of fields, and field type length; then obtain the structure information of the table name, field name, field order, field type, field length, and Chinese comments.

[0011] As a possible implementation of this embodiment, step S21 includes: Read the DDL table structure information file; Parse the table structure definition in the DDL table structure information file according to the standard download specification.

[0012] As a possible implementation of this embodiment, step S22 includes: Read the metadata XML file; According to the XML file specification, parse the metadata tags in the metadata XML file to obtain the table structure information.

[0013] As a possible implementation of this embodiment, step S23 includes: Traverse the parsed metadata XML file; 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.

[0014] As a possible implementation of this embodiment, step S3 includes: Step S31, read the data source connection configuration to obtain the target database type; 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 by itself according to the specification or adopt a fixed configuration, supporting field type mapping, length multiple expansion, maximum length limit, and default value, and there is no mutual influence between different databases; 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.

[0015] 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.

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

[0017] As a possible implementation of this embodiment, step S4 includes: Step S41, based on the latest mapped and converted DDL information, judge the data source connection to 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 legal person bank and one table; Among them, the specific process of creating the database external table or the temporary table is: A method for determining 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 a database external table or a temporary table. If not, determine whether there is a structural difference between the DDL and the current database external table or temporary table, and perform a structural comparison. If there is a difference, reconstruct the external table. If not, it is assumed that the table structure has not changed and there is no need to create a table each time; or use volatile to create a temporary table without recording metadata.

[0018] As a possible implementation of this embodiment, the step of creating a database external table or a temporary table includes: For the unified construction system, create an external table according to the system structure constructed uniformly across the bank. For the self-built system, create an external table according to the system structure built by each member bank itself.

[0019] As a possible implementation of this embodiment, the step of determining whether to create an external table as needed is: 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.

[0020] 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: Compare the DDL field information with the field information of the current external table. If there is a difference, it is determined that the external table needs to be reconstructed.

[0021] As a possible implementation of this embodiment, the step S5 includes: Step S51: Perform 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 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 a structural information verification check, check whether the structure changes are consistent, and generate a change list log.

[0022] As a possible implementation of this embodiment, the performing of the table structure comparison includes: New table judgment. If the main table of the target database does not exist, create a new table. New field judgment: When the field order and name are inconsistent, record the change information of the new field; Deleted field processing: It is not allowed to delete fields in the official database table. Assign null values to the deleted fields according to the field mapping relationship; Field type modification judgment: When the field types are inconsistent, record the change information of the field types; Field length modification judgment: When the field lengths or precisions are inconsistent, record the change information of the field lengths; Chinese field comment change judgment: When the Chinese comments are inconsistent, record the change information of the Chinese field comments; Change lock judgment: Based on the changed table structure and corporate bank, 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 affects 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 execute the archiving and warehousing operation, add the archiving 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.

[0023] As a possible implementation of this embodiment, the obtaining of the table structure change lock includes: 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 is verified to be consistent.

[0024] As a possible implementation of this embodiment, the execution of the database table structure change includes: Based on the verified structure difference list SQL, execute the SQL change operation to complete the table structure update and record the change log.

[0025] As a possible implementation of this embodiment, the execution of the structure information verification and check includes: According to the lower-level DDL file, check whether the structure changes are consistent, check whether there are any omissions or change errors, and form a change list log.

[0026] In a second aspect, an adaptive device for the table structure between heterogeneous databases based on a metadata file provided by an embodiment of the present invention includes: A data collection module, configured to obtain data from a source business system and generate a metadata information file, where 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, and 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 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, and 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, and the database external table or the temporary table supports simultaneous parallel execution by multiple legal persons; An adaptive adaptation module, which is used to identify the table structure differences by comparing the metadata information of the database external table or the temporary table and the formal table, and automatically adapt the structure information of the database formal table to achieve table structure self-adaptation.

[0027] The beneficial effects produced by the technical solution of the embodiment of the present invention are as follows: The present invention realizes the 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-legal person batch mode and the scenarios of different rows with one set of table structure or multiple sets of table structure, and meets the needs of different enterprise types; through the automatic table structure change of metadata information, the work efficiency and data quality are improved, and the daily operation cost is effectively reduced.

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

[0029] 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; 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; Figure 3 is a flowchart of generating a change SQL by performing a table structure comparison shown according to an exemplary embodiment. Detailed Embodiment

[0030] To more clearly illustrate the technical features of the solution of the present invention, the present invention will be described in detail below through specific embodiments and in conjunction with its accompanying drawings.

[0031] As Figure 1 shown, a method for adaptive table structure between heterogeneous databases based on a metadata file provided by an embodiment of the present invention 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 mapping relationship of heterogeneous database field type and length conversion, perform 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, create a database external table or a temporary table, where the database external table or the temporary table supports simultaneous parallel execution by multiple legal person banks; Step S5, by comparing the metadata information of the database external table or the temporary table with the formal table, identify the table structure differences, and automatically adapt the structure information of the database formal table to achieve table structure adaptation.

[0032] As a possible implementation manner of this embodiment, step S1 includes: Step S11, obtain the metadata DDL information of the table from the source business system based on the standard download specification; 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; Step S13, synchronously download the generated metadata file and the corresponding data file according to the preset download frequency.

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

[0034] 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.

[0035] As a possible implementation of this embodiment, step S2 includes: Step S21, parsing the DDL table structure information file according to the standard downgrading specification; Step S22, based on the parsed downgraded metadata DDL file, parsing the metadata XML file according to the XML downgrading specification; Step S23, from the parsed metadata XML file, by performing format verification on elements such as table name, database type, number of fields, and field type length, to ensure that the metadata information is problem-free; then obtaining the structure information of table name, field name, field order, field type, field length, and Chinese comment.

[0036] As a possible implementation of this embodiment, step S21 includes: Reading the DDL table structure information file; Parsing the table structure definition in the DDL table structure information file according to the standard downgrading specification.

[0037] As a possible implementation of this embodiment, step S22 includes: Reading the metadata XML file; According to the XML downgrading specification, parsing the metadata tags in the metadata XML file to obtain the table structure information.

[0038] As a possible implementation of this embodiment, step S23 includes: Traversing the parsed metadata XML file; According to the tags in the metadata XML file, respectively extracting the table name, field name, field order, field type, field length, and Chinese comment.

[0039] As a possible implementation of this embodiment, step S3 includes: Step S31, reading the data source connection configuration to obtain the target database type; Step S32, reading the field type mapping table of the target database to obtain the mapping relationship between field type and length; the field type mapping table is not limited to fixed configuration, and can also set its own specifications, supporting field type mapping, length multiple expansion, maximum length limit, and default value, etc., and different databases do not affect each other; Step S33, based on the parsed metadata information, through the field type and length conversion mapping relationship of the target database, mapping and converting the DDL source database field type and length to the target database field type and length, forming the latest converted DDL information.

[0040] 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 required.

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

[0042] As a possible implementation of this embodiment, step S4 includes: Step S41, based on the DDL information after the latest mapping conversion, 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, following the naming specification requirements, 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 creation; Among them, the specific process of creating the database external table or the 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. 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 structural comparison. If there are differences, reconstruct 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, 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.

[0043] At the same time, the database external table or the temporary table supports parallel execution by multiple legal person banks. Using 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 performed as required to improve the execution efficiency.

[0044] As a possible implementation of this embodiment, the steps of creating the database external table or the temporary table include: For the unified construction system, create an external table according to the system structure built uniformly by the whole bank; For the self-built system, create an external table according to the system structure built by each member bank itself.

[0045] As a possible implementation of this embodiment, the steps of judging whether to create an external table as required are: Determine whether to perform the operation of creating an external table on demand according to preset conditions or user input, so as to avoid excessive metadata pressure caused by frequent operations on the target database metadata.

[0046] As a possible implementation manner of this embodiment, the step of determining whether there is a structural difference between the DDL and the current external table is as follows: Compare the DDL field information with the field information of the current external table. If there is a difference, it is determined that the external table needs to be rebuilt.

[0047] As a possible implementation manner of this embodiment, the step S5 includes: Step S51, perform 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 SQL for the list of structure changes. The structure comparison includes comparison of field order, name, type, length, and remarks; 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; Step S53, execute the database table structure change according to the SQL for the list of structure changes; Step S54, execute structure information verification and check whether the structure changes are consistent, and generate a log of the list of changes.

[0048] As a possible implementation manner of this embodiment, the performing of the table structure comparison includes: New table judgment. If the main table of the target database does not exist, a new table is created; New field judgment. When the field order and name are inconsistent, record the change information of the new field; Deleted field processing. It is not allowed to delete fields from the formal database table. The deleted fields are assigned null values according to the field mapping relationship; Field type modification judgment. When the field types are inconsistent, record the change information of the field types; Field length modification judgment. When the field lengths or precisions are inconsistent, record the change information of the field lengths; Chinese field comment change judgment. When the Chinese comments are inconsistent, record the change information of the Chinese field comments; 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 archiving execution lock. When the metadata changes, add the metadata lock, and release the lock after the change is completed. When continuing to execute 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 chaos and duplication, but not affecting multiple lines executing simultaneously.

[0049] As a possible implementation of this embodiment, the obtaining of the table structure change lock includes: 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.

[0050] As a possible implementation of this embodiment, the execution of the database table structure change includes: Based on the verified structure difference list SQL, execute the SQL change operation to complete the table structure update and record the change log.

[0051] As a possible implementation of this embodiment, the execution of the structure information verification and validation includes: According to the lower-level DDL file, verify whether the structure changes are consistent, check for any omissions or change errors, and form a change list log.

[0052] As Figure 2 shown, an adaptive device for table structure between heterogeneous databases based on metadata files provided by an embodiment of the present invention includes: A data collection module for obtaining data from the source business system and generating a metadata information file, where the metadata information file is a DDL table structure information file; A file parsing module for parsing the DDL table structure information file, verifying and extracting 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 for performing 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 for judging the data source connection based on the converted DDL information, obtaining the database type, and creating a database external table or a temporary table, where the database external table or the temporary table supports multiple corporate banks to execute in parallel simultaneously; An adaptive adaptation module, which is used to identify table structure differences by comparing the metadata information of the external table or temporary table of the database with the formal table, automatically adapt the structure information of the formal table of the database, and achieve table structure self - adaptation.

[0053] The present invention is based on the metadata DDL file of the source business system's downgraded file. By parsing the metadata DDL file information, it performs mapping conversion of field types and field lengths between heterogeneous databases, creates an external table or temporary table of the database, compares it with the structure of the formal table of the database, identifies table structure differences, automatically adapts the structure information of the formal table of the database, and achieves 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 self - adaptation.

[0054] The design idea of the present invention is as follows: (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. (2) Metadata file parsing: Parse the DDL table structure information file according to the metadata file downgraded specification. (3) Metadata type and length mapping: Based on the parsed metadata information, through the mapping conversion relationship of heterogeneous database field types and lengths, perform mapping conversion of the DDL source database type, length and the target database field type, length to form the latest converted DDL information. (4) Creating an external table or temporary table: Based on the latest mapped and converted DDL information, judge the data source connection, obtain the database type, and create an external table or temporary table of the database. The external table or temporary table of the database supports multi - legal - person rows to execute in parallel simultaneously. (5) Database table structure self - adaptation: Compare the metadata information between the external table of the database and the formal table. Based on the comparison result, identify table structure differences, change the formal table of the database, and achieve database table structure self - adaptation; if there is 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 multiple rows from performing structure change operations simultaneously. After the single - legal - person row that obtains the change lock finishes execution, other rows do not need to perform self - adaptation changes anymore, reducing useless change operations. (6) Finally, based on the technical solution of the present invention, a set of executable programs is formed.

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

[0056] (1) Metadata file generation.

[0057] The metadata DDL of the source system is downloaded based on 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 <line number>_<date>__<frequency flag>_<extraction strategy>.ddl according to the table. The file consists of two parts: a file header and a content body. After the file is generated, it is downloaded synchronously with the data file according to the download frequency.

[0058] Supports configurable data sources, customizes formats such as file format, naming, frequency, incremental / full volume, character set, etc., and supports the conversion of rare characters (conversion between unicode code and decimal); and supports transmission to the specified machine directory through ESB files for downstream use; can also be used for its own parsing.

[0059] (2) Parsing of metadata files.

[0060] 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.

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

[0062] Read the data source connection configuration to obtain the target database type, read the field type mapping table of the target database to obtain the mapping relationship between field types and lengths, 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 needed. The field type mapping table is not limited to fixed configurations, and can also set specifications by itself, supporting field type mapping, length multiple expansion, maximum length limit, and default values, etc., and there is no mutual influence between different databases.

[0063] The present invention is compatible with multiple database types, supports multi-legal person batch modes and scenarios of different rows with one set of table structures or multiple sets of table structures, and meets the needs of different enterprise types.

[0064] (4) Creating external tables or temporary tables.

[0065] Read the data source connection configuration to 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 external tables following the naming specification requirements. Rules for creating external tables: Create one external table for each legal person row and each table. The structure of the unified construction system (system built uniformly 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 row) are different for each row.

[0066] A method for determining whether to create an external table each time. If so, after each external table deletion, create a database external table or a temporary table according to the DDL field information. The external table includes a database external table or a temporary table. If not, determine whether there is a structural difference between the DDL and the current database external table or temporary table, perform a structural comparison. If there is a difference, reconstruct the external table; if not, it is assumed that the table structure has not changed and there is no need to create a table each 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 does not record metadata, and the creation method is flexible and configurable.

[0067] At the same time, the database external table or temporary table supports parallel execution by multiple corporate banks. Using the method of a single row and a single 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 performed as needed to improve the execution efficiency.

[0068] (5) Database table structure adaptation.

[0069] Support parallel batch processing by multiple corporate banks. The structure change is completed in multiple links according to the processing flow to change the database table structure, and the overall process is a transaction.

[0070] 1. Perform a table structure comparison to generate a change SQL.

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

[0072] (1) New table processing: Compare with the target database main table to determine whether it exists. If it exists, do not create an external table; otherwise, create a new table according to the specified specifications, and there is no need to perform other structure checks. Close the transaction to complete the adaptation process.

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

[0074] (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 found in the structure change comparison, do not perform the deletion, but default to assign a null value to the corresponding field in the target database main table according to the field mapping relationship.

[0075] (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 SQL of the structure change list.

[0076] (5) Field length modification detection: For fields such as numeric and character types, determine whether their lengths or precisions have changed. If a change occurs, record the change information and write it into the SQL of the structure change list.

[0077] (6) Chinese field comment detection: Determine whether the Chinese comment has changed. If there is a change, record the change information and write it into the SQL of the structure change list.

[0078] (7) Structure change lock detection: Determine the change lock. Based on the changed table structure and corporate bank, 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 affects data accuracy and execution efficiency; The lock is divided into two levels, the metadata lock and the archival execution lock. When the metadata changes, add the metadata lock, and release the lock after the change is completed. When continuing to execute the archival storage operation, add the archival execution lock to solve the problem of a single line executing repeatedly multiple times, avoiding data confusion and duplication, but not affecting multiple lines executing simultaneously.

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

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

[0081] 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 bank are irregular, in order to avoid each bank from changing the table structure, reducing the database pressure and useless operations, a table structure change lock (table-level lock) is added.

[0082] Each corporate bank must obtain the structure change lock to execute the table structure change. Only the batch of the corporate bank that obtains the lock can perform the table structure change. For batches that cannot obtain the lock, they need to wait and sleep. After waiting for the specified time interval, perform the first-step judgment again to determine whether the table structure is consistent. If the table structure check is consistent, the adaptive process is completed. If it is inconsistent, then judge again to obtain the structure change lock.

[0083] 3. Database table structure change.

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

[0085] 4. Structure information verification.

[0086] After the structural change is completed, according to the DDL file of the lower file, check again whether the structure has changed consistently, whether there are any omissions or change errors, and finally form a change list log for subsequent analysis and processing. Close the transaction and complete the structure self-adaptation.

[0087] 5. Special processing scenarios.

[0088] For the scenario of deleting a table, obtain the table deletion identifier, then automatically skip it and do not perform structure self-adaptation.

[0089] During the night batch running process of the computer program formed based on the above method, it realizes the self-adaptation of the table structure, realizes the consistency processing with the structure information of the lower file of the source system, reduces the coupling degree with the source system, and improves the operation and maintenance change and maintenance efficiency.

[0090] Finally, 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 them. Although the present invention has been described in detail with reference to the above embodiments, those of ordinary skill in the art should understand that it is still possible to modify the specific implementation manners of the present invention or make equivalent replacements. Any modification or equivalent replacement that does not depart from the spirit and scope of the present invention shall be covered by the protection scope of the claims of the present invention.

Claims

1. A method for adaptive table structure between heterogeneous databases based on metadata files, characterized in that: The steps include: Step S1, acquiring data from a source business system and generating a metadata information file, wherein the metadata information file is a DDL table structure information file; Step S2, parsing the DDL table structure information file, verifying and extracting the table structure information, wherein 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, mapping conversion is performed between the DDL source database type and length and the target database field type and length through the heterogeneous database field type and length conversion mapping relationship to form converted DDL information; Step S4, based on the converted DDL information, determine the data source connection, obtain the database type, and create a database table or temporary table, wherein the database table or temporary table supports simultaneous execution of multiple legal person rows; Step S5, by comparing the metadata information of the database external table or temporary table and the formal table, identifying the table structure difference, automatically adapting the structure information of the database formal table, and realizing table structure adaptation.

2. The method for self-adapting table structures between heterogeneous databases based on metadata files according to claim 1, characterized in that: The step S1 comprises: Step S11, based on the standard file specification, obtain the metadata DDL information of the table 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 self-adapting table structures between heterogeneous databases based on metadata files according to claim 2 is 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 self-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 self-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 self-adapting table structures between heterogeneous databases based on metadata files according to claim 1, characterized in that: 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 self-adapting table structures between heterogeneous databases based on metadata files according to claim 1, characterized in that: The step S4 comprises: Step S41, based on the latest DDL information after mapping conversion, determine the data source connection and obtain the database type; Step S42, reading the data source connection configuration, and obtaining the latest mapped DDL field information according to the database type; Step S43, following the naming convention requirements and following the rule of creating one table per legal person, create a database table or temporary table; The specific process of creating a database table or temporary table is as follows: Determine whether to create a foreign table each time, if so, create a database foreign table or a temporary table according to the DDL field information each time after deleting the foreign table, the foreign table includes the database foreign table or the temporary table; If not, determine whether there are structural differences between the DDL and the current database foreign table or temporary table, and compare the structures. If there are differences, rebuild the foreign table. If not, assume that the table structure remains unchanged and there is no need to create the table each time. Alternatively, use volatile to create a temporary table without recording metadata.

8. The method for self-adapting table structures between heterogeneous databases based on metadata files according to any one of claims 1 to 7, characterized in that: The step S5 comprises: Step S51, perform table structure comparison, compare metadata information based on the database external table or temporary table and the formal table, obtain the structure change list, and generate the structure change list SQL. The structure comparison includes comparison of field order, name, type, length and remarks; Step S52, obtain the table structure change lock. When multiple legal person rows are processed in parallel, only the legal person row that has obtained the lock is allowed to execute the table structure change; Step S53, executing database table structure change according to the structure change list SQL; Step S54, perform structural information verification to check whether the structural changes are consistent, and generate a change list log.

9. The method for self-adapting table structures between heterogeneous databases based on metadata files according to claim 8, characterized in that: 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; Judgment of field Chinese annotation changes, recording field Chinese annotation change information when Chinese annotations are inconsistent; 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.

10. A device for adaptively adjusting table structures between heterogeneous databases based on metadata files, 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, used to parse the DDL table structure information file, verify and extract the table structure information, the table structure information includes table name, field name, field order, field type, field length and field comment; A mapping conversion module is used to perform mapping conversion between the DDL source database type and length and the target database field type and length based on the parsed metadata information and through the heterogeneous database field type and length conversion mapping relationship to form the converted DDL information; A table creation module is used to determine the data source connection, obtain the database type, and create a database table or temporary table based on the converted DDL information. The database table or temporary table supports the simultaneous execution of multiple legal person rows. The adaptive adaptation module is used to identify the differences in table structures by comparing the metadata information of the database external table or temporary table and the formal table, and automatically adapt the structural information of the formal table of the database to achieve table structure adaptation.

Images