CN110704475A - Method and system for comparing ETL loading table structures - Google Patents
Method and system for comparing ETL loading table structures Download PDFInfo
- Publication number
- CN110704475A CN110704475A CN201910933482.9A CN201910933482A CN110704475A CN 110704475 A CN110704475 A CN 110704475A CN 201910933482 A CN201910933482 A CN 201910933482A CN 110704475 A CN110704475 A CN 110704475A
- Authority
- CN
- China
- Prior art keywords
- report data
- database
- target
- data
- source system
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
Abstract
The invention provides a method and a system for comparing ETL loading table structures, wherein the method comprises the following steps: receiving report data of a source system; testing the connection of the database of the target system according to the database connection information of the target system; after the connection is successful, acquiring target report data of the target system; and comparing the report data of the source system with the target report data to obtain a difference comparison result. The method and the system can comprehensively check the consistency of the table structure of the source system and the table structure of the target system, quickly and accurately find the difference, greatly improve the comparison efficiency, and can classify according to the difference result of the target system and generate the DDL statement so as to facilitate the table synchronization of the source system.
Description
Technical Field
The invention relates to the technical field of data warehouses, in particular to a method and a system for comparing an ETL loading table structure.
Background
At present, a foreign exchange report system has a complex data source system and relates to a plurality of upstream systems. In particular, when there are a plurality of system sources, the upstream system does not change the table structure every time and notifies in time. If the field length of a table in an upstream system is enlarged or increased, a receiver does not receive the notification and timely modifies the table, which can cause the loading and error reporting of the production report table and influence the use of service personnel. Particularly, the length of one field is increased, report errors can be found only when actual data reaches a critical value at a certain time, and the report errors can be located on the upstream table structure problem only after a long time of troubleshooting is needed.
The existing method for checking the ETL loading table structure is through manual checking, which is time-consuming and labor-consuming, and needs to export the table structure in the database to be stored in an EXCEL format, then to compare with the table structure document issued by an upstream system, and check one field by one field, and is easy to make mistakes. Especially for a large-scale data warehouse system, the related tables and systems are numerous, and the problems that how to ensure the accuracy of ETL loading, how to quickly find out the difference of the table structure and how to avoid ETL loading errors in a production environment are a big problem. If the verification is not carried out, an error is reported when the test or production runs, and an error log and a bad file are generated; if the data item load is misaligned, it is difficult to find an error through the bad file and the log.
In view of the above, a solution for matching the ETL loading table structure quickly and accurately is needed.
Disclosure of Invention
In order to solve the problems, the invention provides a comparison method and a comparison system for an ETL loading table structure, and the method and the system can comprehensively check the consistency of an upstream table structure and a target system table structure, avoid the inconsistency of the upstream and the downstream and improve the data quality. Especially for a large-scale data warehouse system, the difference can be found quickly and accurately, and the comparison efficiency is greatly improved.
In an embodiment of the present invention, a method for comparing ETL load table structures is provided, where the method includes:
receiving report data of a source system;
testing the connection of the database of the target system according to the database connection information of the target system;
after the connection is successful, acquiring target report data of the target system;
and comparing the report data of the source system with the target report data to obtain a difference comparison result.
Optionally, the report data of the source system includes: data table name, field type, data length, and fractional bit length.
Optionally, receiving report data of the source system, further includes:
receiving system data of a source system;
and importing the system data of the source system into an EXCEL data dictionary, generating report data of the source system after the importing is finished, and storing the report data of the source system into a first temporary table of a comparison database.
Optionally, the database connection information of the target system includes: the IP of the database of the target system, database instance information, database username, and database password.
Optionally, after the connection is successful, obtaining the target report data of the target system further includes:
after the connection is successful, acquiring target report data generated by a target system database by using ORACLE database view information;
and storing the target report data to a second comparison table of a comparison database.
Optionally, comparing the report data of the source system with the target report data to obtain a difference comparison result, further comprising:
and calling a PL/SQL module of the comparison database, and comparing the report data of the first temporary table with the report data of the second comparison table to obtain a difference comparison result.
Optionally, comparing the report data of the first temporary table with the report data of the second comparison table to obtain a difference comparison result, further comprising:
and comparing the report data of the first temporary table with the report data of the second comparison table to obtain a difference comparison result, wherein the data table name, the field type, the data length and the decimal bit length are in the report data of the first temporary table and the report data of the second comparison table.
Optionally, the difference alignment result includes one of the following cases:
the name of the report data exists in the source system database and does not exist in the target system database;
the name of the report data exists in the target system database and does not exist in the source system database;
the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the source system database and does not exist in the target system database;
the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the target system database and does not exist in the source system database;
the name of the report data and the fields in the report data exist in the source system database and the target system database, and the types of the corresponding fields are inconsistent;
the name of the report data and the fields in the report data exist in the source system database and the target system database, the types of the corresponding fields are consistent, and the lengths of the data or the decimal place are inconsistent.
Optionally, the method further includes:
according to the difference comparison result, determining report data to be modified, and generating an SQL script for establishing a report or changing the report;
and executing the SQL script, and synchronizing the corresponding report data with the difference into the same report data.
In an embodiment of the present invention, a system for comparing ETL load table structures is further provided, where the system includes:
the source system data receiving module is used for receiving report data of a source system;
the database connection module is used for testing the connection of the database of the target system according to the database connection information of the target system;
the target system data acquisition module is used for acquiring target report data of the target system after the connection is successful;
and the difference comparison module is used for comparing the report data of the source system with the target report data to obtain a difference comparison result.
In an embodiment of the present invention, a computer device is further provided, which includes a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor implements a comparison method for an ETL loading table structure when executing the computer program.
In an embodiment of the present invention, a computer-readable storage medium is further provided, where a computer program for executing the comparison method of the ETL loading table structure is stored in the computer-readable storage medium.
The comparison method and the comparison system for the ETL loading table structure can comprehensively check the consistency of the table structure of the source system and the table structure of the target system, quickly and accurately find the difference, greatly improve the comparison efficiency, classify the table structure according to the difference result of the target system, and generate the DDL statement so as to facilitate the table synchronization of the source system.
Drawings
Fig. 1 is a flowchart of a method for comparing ETL load table structures according to an embodiment of the present invention.
Fig. 2 is a schematic structural diagram of an alignment system of an ETL loading table structure according to an embodiment of the present invention.
Fig. 3 is a schematic diagram of a report data comparison operation interface according to an embodiment of the present invention.
Fig. 4 is a schematic diagram of an operation interface for comparing database information according to an embodiment of the present invention.
FIG. 5 is a schematic interface diagram of comparison results according to an embodiment of the invention.
FIG. 6 is a schematic interface diagram illustrating detailed comparison results according to an embodiment of the invention.
FIG. 7 is a diagram of an interface for deriving results according to an embodiment of the present invention.
Fig. 8 is a diagram illustrating a comparison relationship between report data according to an embodiment of the present invention.
Detailed Description
The principles and spirit of the present invention will be described with reference to a number of exemplary embodiments. It is understood that these embodiments are given solely for the purpose of enabling those skilled in the art to better understand and to practice the invention, and are not intended to limit the scope of the invention in any way. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.
As will be appreciated by one skilled in the art, embodiments of the present invention may be embodied as a system, apparatus, device, method, or computer program product. Accordingly, the present disclosure may be embodied in the form of: entirely hardware, entirely software (including firmware, resident software, micro-code, etc.), or a combination of hardware and software.
According to the embodiment of the invention, a comparison method and a comparison system of an ETL loading table structure are provided.
In this context, it is to be understood that, in the terms referred to:
ETL: the abbreviation of english Extract-Transform-Load is used to describe the process of extracting, inter-transforming and loading data from a source end to a destination end.
The principles and spirit of the present invention are explained in detail below with reference to several representative embodiments of the invention.
Fig. 1 is a flowchart of a method for comparing ETL load table structures according to an embodiment of the present invention. As shown in fig. 1, the method includes:
and step S101, receiving report data of the source system.
The report data of the source system comprises: the data table name, the field type, the data length and the decimal length are basic items for comparison in the subsequent steps, and in practice, the data table name, the field type, the data length and the decimal length can be adjusted according to the actual situation.
In an embodiment, the specific process of step S101 is as follows:
receiving system data of a source system; and importing the system data of the source system into an EXCEL data dictionary, generating report data of the source system after the importing is finished, and storing the report data of the source system into a first temporary table of a comparison database. The imported report data may be a single table, multiple tables, all tables under a certain source system or all tables in a document, and these tables are stored together in a temporary table USER _ TAB _ COL _ EXCEL in the comparison database, and the above process is actually the process of ETL.
And S102, testing the connection of the database of the target system according to the database connection information of the target system.
Wherein, the database connection information of the target system may include: the IP of the database of the target system, database instance information, database username, and database password. The staff can input the connection information of the target database and test the database connection of the target system.
And step S103, acquiring target report data of the target system after the connection is successful.
The specific process is as follows: after the connection is successful, acquiring target report data generated by the target system database by using ORACLE database USER _ TAB _ COLS view information; wherein, the content of the target report data is similar to the report data of the source system, including: data table name, field type, data length, and fractional bit length.
And storing the target report data to a second comparison table USER _ TAB _ COS _ ENV of a comparison database. Thus, the first temporary table USER _ TAB _ COL _ EXCEL and the second comparison table USER _ TAB _ COS _ ENV are imported into the comparison database.
And step S104, comparing the report data of the source system with the target report data to obtain a difference comparison result.
The specific process is as follows: calling a PL/SQL module of the comparison database, comparing the report data of the first temporary table USER _ TAB _ COL _ EXCEL with the report data of the second comparison table USER _ TAB _ COS _ ENV to obtain a difference comparison result, wherein the comparison content comprises: the data table name, field type, data length and decimal place length in the report data.
After the comparison is completed, the obtained comparison RESULT can be stored in the RESULT table T _ CMP _ RESULT, and meanwhile, the comparison RESULT can be exported as EXCEL and stored in the user-specified directory, so as to be displayed to the user, and the user can perform corresponding processing based on the comparison RESULT.
The alignment result may be one of the following cases:
1. the name of the report data exists in the source system database and does not exist in the target system database;
2. the name of the report data exists in the target system database and does not exist in the source system database;
3. the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the source system database and does not exist in the target system database;
4. the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the target system database and does not exist in the source system database;
5. the name of the report data and the fields in the report data exist in the source system database and the target system database, and the types of the corresponding fields are inconsistent;
6. the name of the report data and the fields in the report data exist in the source system database and the target system database, the types of the corresponding fields are consistent, and the lengths of the data or the decimal place are inconsistent.
In the cases 1 and 2, the entire table of report data exists only in a certain system database, in short, the report a exists in a certain system, and the other system does not, that is, the system database stores completely different reports.
The case 5 is that the same report data (name) is in the system database, the same field (name) is in the system database, but the field type is different. That is, there is the same report A and the same field B, and the field types are different.
The 6 th case is opposite to the 5 th case in that the field types are the same, but the length and accuracy of the data are not uniform.
And step S105, determining report data to be modified according to the difference comparison result, and generating an SQL script for establishing a report or changing the report. Aiming at six conditions of comparison results, six SQL scripts can be correspondingly set to respectively process one condition; the script may be generated automatically or manually by the user.
In addition, after the script is generated, the script can be displayed to a user, and the user can execute the SQL script according to selection or automatically execute the SQL script and synchronize corresponding report data with difference into the same report data; if the user chooses not to execute, the report data can be automatically edited and adjusted according to the requirement.
Therefore, by automatically generating the script, developers do not need to write the DDL script one by one according to the difference, the system can keep the table structure of the target system consistent with the table structure of the source system by directly executing the DDL script in the background of the target system after generating the DDL script, the table structure synchronization is realized, and the difference synchronization of the table structures of the source system and the target system is completed by one key.
The comparison method of the ETL loading table structure provided by the invention has simple and efficient processing process, can be applied to developers to confirm loading configuration, and can also be applied to automatic testing of testers. Compared with the prior art that the ETL loading table structure is checked only by manual checking, namely, the table structures of the source system and the target system are compared one by one, the table structures in the database are firstly exported and stored into an EXCEL format, and then the table structures are compared with the table structure documents issued by the upstream system, and the table structures are checked one by one field.
It should be noted that although the operations of the method of the present invention have been described in the above embodiments and the accompanying drawings in a particular order, this does not require or imply that these operations must be performed in this particular order, or that all of the operations shown must be performed, to achieve the desired results. Additionally or alternatively, certain steps may be omitted, multiple steps combined into one step execution, and/or one step broken down into multiple step executions.
Based on the same inventive concept, the present invention further provides a system for comparing ETL loading table structures, as shown in fig. 2, the system includes:
a source system data receiving module 100, configured to receive report data of a source system;
a database connection module 200, configured to test connection of a database of a target system according to database connection information of the target system;
the target system data acquisition module 300 is configured to acquire target report data of the target system after the connection is successful;
and the difference comparison module 400 is configured to compare the report data of the source system with the target report data to obtain a difference comparison result.
In addition, the system may further include:
and the script generating module 500 is configured to determine report data to be modified according to the difference comparison result, and generate an SQL script for establishing a report or changing the report.
An executing module 600, configured to execute the SQL script and synchronize the corresponding report data with differences into the same report data
It should be noted that although several modules of the alignment system of the ETL load table structure are mentioned in the above detailed description, such partitioning is merely exemplary and not mandatory. Indeed, the features and functionality of two or more of the modules described above may be embodied in one module according to embodiments of the invention. Conversely, the features and functions of one module described above may be further divided into embodiments by a plurality of modules.
Based on the same inventive concept, the invention also provides computer equipment, which comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the processor realizes the comparison method of the ETL loading table structure when executing the computer program.
Based on the same inventive concept, the invention also provides a computer readable storage medium, which stores a computer program for executing the comparison method of the ETL loading table structure.
For a clearer explanation of the comparison method and system of the ETL loading table structure, a specific embodiment is described below, but it should be noted that the embodiment is only for better describing the present invention and is not to be construed as an inappropriate limitation to the present invention.
The first embodiment is as follows:
taking the comparison process of a certain report data as an example, as shown in fig. 3, an operation interface schematic diagram is shown, and in the interface, a user can perform operations of importing an EXCEL data dictionary, inputting target database information, performing comparison, checking results and the like.
Before the comparison work is carried out, the source system needs to acquire the latest list of the table structure, namely the EXCEL data dictionary every time. In addition, the data types in the upstream documents have given non-specifications, such as CHAR type needs to be converted into VARCHAR2 type for comparison, because the CHAR type and the VARCHAR2 type are generally considered to be the same, and only the storage size is affected in some scenarios.
Specifically, before each version is put into production, the data is sent to a data downloading platform source system, a data table structure document is downloaded and downloaded, as shown in table 1, the document is a transaction information table (data table english name DEAL _1), the format of the document is fixed, a table can be established on a database, the table is imported into the table through a JAVA program, and then comparison in subsequent steps is performed.
TABLE 1 data sheet Structure
First, the user can select the EXCEL data dictionary and import the report data of the source system as required. The process is to download the data table structure documents from the source system through the data downloading platform, the documents all contain the field information as shown in the above table 1, can import a single table, multiple tables, all tables under one system and all tables in the document, and store the imported report data into a temporary table USER _ TAB _ COL _ EXCEL in the database.
Furthermore, the user can input the information of the target system database and test the database connection on the operation interface. After the connection is successful, the database of the target system stores the table structure information in the USER _ TAB _ COL _ ENV table by using the ORACLE database USER _ TAB _ COLS view information.
After the import is finished, the USER can click and select a comparison mode, such as 'fuzzy comparison' and click 'comparison execution' buttons, the report names, the field names, the types and the small-digit numbers in the import table USER _ TAB _ COL _ EXCEL and the USER _ TAB _ COL _ ENV are compared, after the comparison is finished, the RESULT is stored in the RESULT table T _ CMP _ RESULT, and the export of the RESULT as EXCEL is supported and stored in a USER-specified directory. The user can click on the 'view result' to view the comparison result.
In a specific embodiment, as shown in fig. 4, after the comparison type is selected as "database", the database information and the target database information may be input on the operation interface, and the corresponding "comparison execution" and "query result" may be clicked, so that the comparison result may be displayed.
The alignment differences were divided into six types:
1. the report data exists in the source database, and does not exist in the target database.
2. The report data exists in the target database and does not exist in the source database.
3. The fields in the report data exist in the source database, and do not exist in the target database.
4. The fields in the report data exist in the target database and do not exist in the source database.
5. Report data exists in the database, but the field types are not consistent.
6. Report data exists in the database, the types of fields are consistent, but the lengths or the precision are not consistent.
Fig. 5 is a schematic diagram of an interface of comparison results, as shown in fig. 5, the left side is target database information, and the right side is database information, through which the result of comparison difference can be queried.
Wherein, in the pull-down menu of 'coincidence' is set
Is completely consistent,
"left with right without"),
"left without right with" and
and selecting a certain item to correspondingly screen out a result of a certain comparison difference, such as 'inconsistent (not empty left and right)'.
In addition, the specific table name can be input, and the result can be inquired and derived.
For example, taking the two tables in the labeled box 501 in fig. 5 as an example, there exists both database and source database, but if the result of the comparison difference is inconsistent, it may be that the fields are different in type, length, or precision.
Further, referring to FIG. 6, the detailed comparison result can be viewed, and the table name "Z _ PERSONAL" in the first row (labeled block 601) is taken as an example, and the comparison result is "inconsistent". Upon double clicking on the line, more detailed difference information may be displayed.
Wherein, in the pull-down menu of "please select query conditions" (mark box 602), a pull-down menu is set
Is completely consistent,
"the table exists in the source database, does not exist in the target database"),
"this table exists in the target database, does not exist in the source database"),
"this field is present in the source database, absent in the data dictionary or target database"),
"this field exists in the target database or data dictionary, does not exist in the source database"),
"type inconsistency" and
"consistent type, inconsistent length or inconsistent precision";
the last six of the seven species are inconsistent and correspond to the six types of alignment differences mentioned in the foregoing summary, so that by selecting one of the three, the specific result to be viewed can be screened out.
As can be seen from the table of fig. 6, the difference types of the first row in the left table show "type is consistent, field length is inconsistent" (not all are shown), and the number "22" of the first row field length (mark box 603) in the right database information table is shown in red, so that the staff can know the detailed comparison difference result according to the difference types, and analyze and process the difference to synchronize the information.
As shown in fig. 7, after the comparison results are obtained, the staff may export the results into the EXCEL table by using the "export result" button, and save the EXCEL table for subsequent analysis of each comparison difference and synchronization.
As an optional component, the invention can also add a one-key generation synchronization SQL statement function on the basic flow. After the comparison result is obtained, the SQL sentence generating function can be set, that is, according to the comparison result, which tables need to be modified is determined, and the latest table building sentence or the latest change sentence is generated by one key. Specifically, each type of difference comparison has a fixed solution, and a corresponding synchronization script is generated according to a specific comparison difference result.
The script can be displayed to the staff after being generated, and the staff can select to execute the script according to the requirements; in addition, the statements can be set to be automatically executed, and automatic difference synchronization of the table structures of the source system and the target system is achieved.
The method for automatically generating the SQL script can ensure that developers do not need to write the DDL script one by one according to the difference, the system can keep the table structure of the target system consistent with the table structure of the source system by directly executing the DDL script in the background of the target system after generating the DDL script, the table structure synchronization is realized, and the difference synchronization of the table structures of the source system and the target system is completed by one key.
In summary, in practical application, the comparison method and system for the ETL loading table structure provided by the invention can automatically perform comparison only by providing the EXCEL data dictionary of the source system and the database connection information of the target system, accurately classify comparison results, facilitate development and analysis of testers, greatly improve efficiency and avoid the ETL loading table structure error.
For example: before each version is put into production, the DCDS data downloading platform is used for downloading the data table structure document from the downloading source system. Because the format of the document is fixed, a table is established in a database, the table is imported into the table through a JAVA program, and then comparison is carried out. And exporting the inconsistent contents for the developers to check and find the problems as soon as possible.
In addition, as shown in connection with FIG. 8, both the source system and the target system may select a type, which includes database information, EXCEL data dictionary.
If the source system selects the database information, taking the table structure of the target system database as a reference, and comparing the table structure with the target system; and if the source system selects the EXCEL data dictionary, taking the data dictionary of the upstream system as input, and comparing the data dictionary with the corresponding table structure of the target system. Accordingly, the target system may also select one of the two types.
By using the mode of selecting the EXCEL data dictionary and the database connection information as the source system report data and the target system report data respectively, simple and efficient comparison can be realized, the time is not needed to be spent on the table structure arrangement of the related source system and the target system, and the data report can be directly used as input for comparison.
The comparison method and the comparison system for the ETL loading table structure can comprehensively check the consistency of the table structure of the source system and the table structure of the target system, quickly and accurately find the difference, greatly improve the comparison efficiency, facilitate the analysis of development and testing personnel, support the automatic generation and execution of synchronous scripts and realize the automatic synchronization of report data.
While the spirit and principles of the invention have been described with reference to several particular embodiments, it is to be understood that the invention is not limited to the disclosed embodiments, nor is the division of aspects, which is for convenience only as the features in such aspects may not be combined to benefit. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.
Claims (12)
1. A method for comparing ETL loading table structures is characterized by comprising the following steps:
receiving report data of a source system;
testing the connection of the database of the target system according to the database connection information of the target system;
after the connection is successful, acquiring target report data of the target system;
and comparing the report data of the source system with the target report data to obtain a difference comparison result.
2. The method of claim 1, wherein the report data of the source system comprises: data table name, field type, data length, and fractional bit length.
3. The method for comparing the ETL loading table structure according to claim 2, wherein the receiving the report data of the source system further comprises:
receiving system data of a source system;
and importing the system data of the source system into an EXCEL data dictionary, generating report data of the source system after the importing is finished, and storing the report data of the source system into a first temporary table of a comparison database.
4. The method of claim 3, wherein the database connection information of the target system comprises: the IP of the database of the target system, database instance information, database username, and database password.
5. The method for comparing the ETL loaded table structure as recited in claim 4, wherein the step of obtaining the target report data of the target system after the connection is successful further comprises:
after the connection is successful, acquiring target report data generated by a target system database by using ORACLE database view information;
and storing the target report data to a second comparison table of a comparison database.
6. The method of claim 5, wherein the comparing the report data of the source system with the target report data to obtain the difference comparison result further comprises:
and calling a PL/SQL module of the comparison database, and comparing the report data of the first temporary table with the report data of the second comparison table to obtain a difference comparison result.
7. The method of claim 6, wherein the comparing the report data of the first temporary table with the report data of the second comparison table to obtain the difference comparison result, further comprises:
and comparing the report data of the first temporary table with the report data of the second comparison table to obtain a difference comparison result, wherein the data table name, the field type, the data length and the decimal bit length are in the report data of the first temporary table and the report data of the second comparison table.
8. The method of claim 7, wherein the ETL load table structure alignment comprises one of:
the name of the report data exists in the source system database and does not exist in the target system database;
the name of the report data exists in the target system database and does not exist in the source system database;
the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the source system database and does not exist in the target system database;
the name of the report data exists in both a source system database and a target system database, and a field in the report data exists in the target system database and does not exist in the source system database;
the name of the report data and the fields in the report data exist in the source system database and the target system database, and the types of the corresponding fields are inconsistent;
the name of the report data and the fields in the report data exist in the source system database and the target system database, the types of the corresponding fields are consistent, and the lengths of the data or the decimal place are inconsistent.
9. The method of claim 8, further comprising:
according to the difference comparison result, determining report data to be modified, and generating an SQL script for establishing a report or changing the report;
and executing the SQL script, and synchronizing the corresponding report data with the difference into the same report data.
10. An alignment system for ETL loading table structure, the system comprising:
the source system data receiving module is used for receiving report data of a source system;
the database connection module is used for testing the connection of the database of the target system according to the database connection information of the target system;
the target system data acquisition module is used for acquiring target report data of the target system after the connection is successful;
and the difference comparison module is used for comparing the report data of the source system with the target report data to obtain a difference comparison result.
11. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the method of any of claims 1 to 9 when executing the computer program.
12. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program for executing the method of any one of claims 1 to 9.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910933482.9A CN110704475A (en) | 2019-09-29 | 2019-09-29 | Method and system for comparing ETL loading table structures |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910933482.9A CN110704475A (en) | 2019-09-29 | 2019-09-29 | Method and system for comparing ETL loading table structures |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110704475A true CN110704475A (en) | 2020-01-17 |
Family
ID=69198070
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910933482.9A Pending CN110704475A (en) | 2019-09-29 | 2019-09-29 | Method and system for comparing ETL loading table structures |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110704475A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111309806A (en) * | 2020-02-05 | 2020-06-19 | 杭州数梦工场科技有限公司 | Data synchronization method and device, electronic equipment and storage medium |
| CN111427787A (en) * | 2020-03-26 | 2020-07-17 | 杭州趣维科技有限公司 | Heterogeneous database business data synchronization test method |
| CN112685325A (en) * | 2021-01-22 | 2021-04-20 | 中信银行股份有限公司 | ETL software research and development test management method and system |
| CN112711573A (en) * | 2021-01-15 | 2021-04-27 | 中化石化销售有限公司 | Database structure difference report generation method and device |
| CN113495890A (en) * | 2021-07-08 | 2021-10-12 | 上海二三四五网络科技有限公司 | Automatic comparison system, method and equipment for batch heterogeneous data sources |
| TWI794720B (en) * | 2021-01-07 | 2023-03-01 | 台北富邦商業銀行股份有限公司 | Performance management analyzing system and method thereof |
| CN116361391A (en) * | 2023-03-30 | 2023-06-30 | 中电云数智科技有限公司 | Method and device for detecting and repairing structural abnormality of data synchronization table |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20110191299A1 (en) * | 2010-02-01 | 2011-08-04 | Microsoft Corporation | Logical data backup and rollback using incremental capture in a distributed database |
| CN104268291A (en) * | 2014-10-23 | 2015-01-07 | 税友软件集团股份有限公司 | Data consistency processing method and system |
| CN104965735A (en) * | 2015-06-18 | 2015-10-07 | 北京京东尚科信息技术有限公司 | Apparatus for generating upgrade SQL script |
-
2019
- 2019-09-29 CN CN201910933482.9A patent/CN110704475A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20110191299A1 (en) * | 2010-02-01 | 2011-08-04 | Microsoft Corporation | Logical data backup and rollback using incremental capture in a distributed database |
| CN104268291A (en) * | 2014-10-23 | 2015-01-07 | 税友软件集团股份有限公司 | Data consistency processing method and system |
| CN104965735A (en) * | 2015-06-18 | 2015-10-07 | 北京京东尚科信息技术有限公司 | Apparatus for generating upgrade SQL script |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111309806A (en) * | 2020-02-05 | 2020-06-19 | 杭州数梦工场科技有限公司 | Data synchronization method and device, electronic equipment and storage medium |
| CN111427787A (en) * | 2020-03-26 | 2020-07-17 | 杭州趣维科技有限公司 | Heterogeneous database business data synchronization test method |
| CN111427787B (en) * | 2020-03-26 | 2023-05-30 | 杭州小影创新科技股份有限公司 | Synchronous testing method for service data of heterogeneous database |
| TWI794720B (en) * | 2021-01-07 | 2023-03-01 | 台北富邦商業銀行股份有限公司 | Performance management analyzing system and method thereof |
| CN112711573A (en) * | 2021-01-15 | 2021-04-27 | 中化石化销售有限公司 | Database structure difference report generation method and device |
| CN112685325A (en) * | 2021-01-22 | 2021-04-20 | 中信银行股份有限公司 | ETL software research and development test management method and system |
| CN112685325B (en) * | 2021-01-22 | 2023-07-28 | 中信银行股份有限公司 | ETL software research and development test management method and system |
| CN113495890A (en) * | 2021-07-08 | 2021-10-12 | 上海二三四五网络科技有限公司 | Automatic comparison system, method and equipment for batch heterogeneous data sources |
| CN116361391A (en) * | 2023-03-30 | 2023-06-30 | 中电云数智科技有限公司 | Method and device for detecting and repairing structural abnormality of data synchronization table |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110704475A (en) | Method and system for comparing ETL loading table structures | |
| US20230418732A1 (en) | System and method for performing automated api tests | |
| US11163731B1 (en) | Autobuild log anomaly detection methods and systems | |
| US8386419B2 (en) | Data extraction and testing method and system | |
| US8793660B2 (en) | Automated testing of programming code for a web service | |
| US10013439B2 (en) | Automatic generation of instantiation rules to determine quality of data migration | |
| US8904352B2 (en) | Systems and methods for processing source code during debugging operations | |
| CN107368503B (en) | Data synchronization method and system based on button | |
| US20130041900A1 (en) | Script Reuse and Duplicate Detection | |
| CN103514223A (en) | Data synchronism method and system of database | |
| US20180165179A1 (en) | Determining incompatibilities of automated test cases with modified user interfaces | |
| CN111240968A (en) | Automatic test management method and system | |
| CN103064780B (en) | A kind of method of software test and device | |
| CN109582286B (en) | Freemarker technology-based data normalization verification method and device | |
| US10042743B2 (en) | Computer system testing | |
| US9104356B2 (en) | Extendable system for preprocessing print document and method for the same | |
| CN107273293B (en) | Big data system performance test method and device and electronic equipment | |
| CN115292307A (en) | Data synchronization system, method and corresponding computer equipment and storage medium | |
| WO2023277802A2 (en) | Device and method for identifying errors in a software application | |
| CN114490892A (en) | Data transmission method and device based on datax | |
| CN113238940A (en) | Interface test result comparison method, device, equipment and storage medium | |
| CN111143221B (en) | Test method and device | |
| US20240027998A1 (en) | Resume of failed automates in end-to-end process | |
| CN116431494A (en) | Positioning method and positioning device for test data, target equipment and target server | |
| CN112307108A (en) | Data extraction method and system based on simple configuration mode |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20200117 |