Multi-platform data consistency checking method based on database language

Abstract

The invention discloses a multi-platform data consistency checking method based on a database language. The method comprises the following steps: S1) migrating data to other databases or platforms through a migration tool; s2) checking whether the data volumes before and after data migration are consistent, and if yes, continuing to perform verification; s3) primary key fields of the source end database table data and the target end table data are verified, and if the primary key fields are consistent, verification is continued; and S4) finally, checking the numerical field and the key field. According to the multi-platform data consistency checking method based on the database language, aggregation calculation is carried out on different types of business fields, data analysis is carried out on data according to rules, and the data consistency is checked through the database language, so that the data consistency before and after data migration is guaranteed.

Description

Technical Field

[0001] The present invention relates to a data consistency verification method, in particular to a multi-platform data consistency verification method based on a database language. Background Art

[0002] With the widespread use of big data applications, the same data has differences in file format, data type, storage method, etc. on different database platforms. However, in actual application scenarios, data migration has many problems, such as data loss, duplication, and consistency, which bring many troubles to business applications. For example: 1) After data is migrated across platforms, the MD5 values ​​of data files are inconsistent due to inconsistent storage formats, and it is impossible to determine whether the data is consistent from the files; 2) After data is migrated across platforms, simply having consistent data volume cannot eliminate business concerns about data consistency. Summary of the invention

[0003] The technical problem to be solved by the present invention is to provide a multi-platform data consistency verification method based on database language, which can solve the data consistency problem in cross-platform data migration and ensure the consistency of data before and after data migration.

[0004] The technical solution adopted by the present invention to solve the above technical problems is to provide a multi-platform data consistency verification method based on database language, comprising the following steps: S1) migrating data to other databases or platforms through a migration tool; S2) first verifying whether the data volume before and after data migration is consistent, and if the data volume is consistent, continuing the verification; S3) then verifying the primary key fields of the source database table data and the target table data respectively, and if the primary key fields are consistent, continuing the verification; S4) finally verifying the numeric fields and key fields.

[0005] Furthermore, the migration tool in step S1 is DataX, Kettle or Liquibase.

[0006] Furthermore, the step S2 verifies whether the data volumes before and after the data migration are consistent by using a database management tool, Navicat or DBeaver.

[0007] Furthermore, when the primary key field in step S3 is a data type, a sum calculation operation is performed; if the primary key field is a string type, the string length is calculated before the sum calculation operation is performed; if the calculation results of the source and target ends are consistent, the primary key field data is considered to be consistent.

[0008] Furthermore, in step S4, for the data of the numerical type field, the source data and the target data are respectively checked for data consistency by summing or averaging; for the data of the string type field, the sum is calculated according to the string length, and if the results are consistent, the data are confirmed to be consistent; for the data of the time type field, the sum is calculated according to one or more dimensions of year, month, day, hour, minute, and second, and if the results are consistent, the data are confirmed to be consistent.

[0009] Furthermore, in step S4, the Boolean type field data is treated as the string type field data for consistency verification, and the floating point type field data is treated as the numerical type field data for consistency verification.

[0010] Furthermore, in step S4, when the number of table fields exceeds a preset threshold, only one field of each data type is verified.

[0011] Compared with the prior art, the present invention has the following beneficial effects: the multi-platform data consistency verification method based on database language provided by the present invention performs aggregation calculations on different types of business fields, analyzes data according to rules, and verifies data consistency using database language to ensure data consistency before and after data migration. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] Figure 1 The present invention is a flowchart of multi-platform data consistency verification based on database language. DETAILED DESCRIPTION

[0013] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0014] Figure 1 The present invention is a flowchart of multi-platform data consistency verification based on database language.

[0015] See also Figure 1 The multi-platform data consistency verification method based on database language provided by the present invention comprises the following steps: S1) Migrate data to other databases or platforms using migration tools; S2) First verify whether the data volume before and after data migration is consistent. If the data volume is consistent, continue verification; S3) Then, the primary key fields of the source database table data and the target table data are verified respectively. If the primary key fields are consistent, the verification continues; S4) Finally, the numerical fields and key fields are verified.

[0016] The key fields are determined as follows: 1. Uniqueness determination: A field or a group of fields can uniquely identify each row of records in the table, such as the primary key.

[0017] 2. Redundancy determination: avoid unnecessary duplication, save computing resources, and improve data consistency.

[0018] 3. Data type determination: non-string, numeric fields and other regular type fields.

[0019] 4. Business-specified key fields: The business clearly identifies one or some fields as key fields.

[0020] The multi-platform data consistency verification method based on database language provided by the present invention has the following main steps: 1. Data Migration: The present invention uses migration tools to migrate data to other databases or platforms and ensures data availability. Data migration tools: For different databases, you can choose appropriate migration tools to achieve data migration. The mainstream migration tools in the industry mainly include: DataX: relational database, Nosql database, big data platform, etc.

[0021] Kettle: Open source ETL tool, written in JAVA.

[0022] Liquibase: Open source data management tool for database version control and data migration.

[0023] 2. Data volume verification: First, confirm whether the data volume is consistent before and after data migration. If the data volume is inconsistent, the data must be inconsistent. If the data volume is consistent, continue with the verification.

[0024] The available database management tools mainly include: Navicat: supports multiple databases, including MySQL, Oracle, SQL Server, etc.

[0025] DBeaver: Open source database tool that supports all major databases.

[0026] 3. Primary key verification: Perform sum calculation operations on the primary key fields of the source database table data and the target table data respectively. For example, if the primary key field is a data type, you can directly sum it up. If the primary key field is a string type, you can calculate the length and then sum it up. If the results on the source and target sides are consistent, the primary key field data consistency is confirmed.

[0027] 4. Numeric type verification: Numeric types include int, bigint, Long and other type structures. For data consistency verification of numeric type structures, the source data and the target data can be checked for consistency by summing or averaging. If the results are consistent, the data consistency is confirmed.

[0028] 5. String type verification: String types include string, text, etc. For this type of data, you can perform a length sum operation. If the results are consistent, the data consistency is confirmed.

[0029] 6. Time type consistency verification: Time types include date, timestamp, etc. Time types can be calculated by summing up, or by summing up one or more dimensions of year, month, day, hour, minute, and second. If the results are consistent, the data consistency is confirmed.

[0030] 7. The Bool type can be verified for consistency according to the string type, and the floating point type can be verified for consistency according to the numeric type.

[0031] 8. When there are many fields in the table, only one field needs to be verified for each data type.

[0032] Although the present invention has been disclosed as above with preferred embodiments, it is not intended to limit the present invention. Any person skilled in the art may make some modifications and improvements without departing from the spirit and scope of the present invention. Therefore, the scope of protection of the present invention shall be based on the definition of the claims.

Claims

1. A multi-platform data consistency verification method based on database language, characterized in that: The steps include: S1) Migrate data to other databases or platforms using migration tools; S2) First verify whether the data volume before and after data migration is consistent. If the data volume is consistent, continue verification; S3) Then, the primary key fields of the source database table data and the target table data are verified respectively. If the primary key fields are consistent, the verification continues; S4) Finally, the numerical fields and key fields are verified.

2. The multi-platform data consistency verification method based on database language according to claim 1, characterized in that: The migration tool in step S1 is DataX, Kettle or Liquibase.

3. The multi-platform data consistency verification method based on database language according to claim 1, characterized in that: The step S2 verifies whether the data volume before and after data migration is consistent by using the database management tool Navicat or DBeaver.

4. The multi-platform data consistency verification method based on database language according to claim 1, characterized in that: When the primary key field in step S3 is a data type, a sum calculation operation is performed; If the primary key field is a string type, the string length is calculated before the sum calculation operation is performed; if the calculation results on the source and target ends are consistent, the primary key field data is considered to be consistent.

5. The multi-platform data consistency verification method based on database language according to claim 1, characterized in that: In step S4, for the data of the numerical type field, the source end data and the target end data are respectively checked for data consistency by summing or averaging; for the data of the string type field, the sum calculation is performed according to the string length, and if the results are consistent, the data is confirmed to be consistent; For the data in the time type field, the sum is calculated according to one or more dimensions of year, month, day, hour, minute, and second. If the results are consistent, the data is confirmed to be consistent.

6. The multi-platform data consistency verification method based on database language according to claim 5, characterized in that: The step S4 performs consistency verification on the Boolean type field data as string type field data, and performs consistency verification on the floating point type field data as numeric type field data.

7. The multi-platform data consistency verification method based on database language according to claim 5, characterized in that: In step S4, when the number of table fields exceeds a preset threshold, only one field of each data type is verified.

Images