Automatic data comparison method, device and equipment and storage medium

Abstract

The invention provides an automatic data comparison method and device, equipment and a storage medium, and the method comprises the steps: obtaining to-be-compared data from a source database; generating a data file which can be loaded by a target database based on the to-be-compared data; creating a corresponding external table based on the data file in the target database; performing data comparison on the external table and a target table existing in the target database; and determining difference data according to a comparison result of the data comparison. By adopting the method, the efficiency and accuracy of data comparison can be greatly improved.

Description

Technical Field

[0001] This application relates to the field of data processing, and more specifically, to an automated data comparison method, apparatus, device, and storage medium. Background Technology

[0002] In data-intensive scenarios such as financial management system construction projects (including SAP), the in-depth advancement of information technology innovation transformation has made frequent data migration and synchronization between heterogeneous databases (such as Oracle source database and GaussDB DWS target database) a common occurrence. Consistency verification after data migration or synchronization is a key link to ensure the accuracy of financial accounting and business decision-making. The reliability and efficiency of data comparison technology directly affect the stable operation of business.

[0003] Currently, a mainstream data consistency verification technology in the industry is table-level data total comparison. This solution involves executing SELECT COUNT(*) statements in both the source and target databases to obtain the total number of records in the two corresponding data tables. Then, the two totals are manually compared to determine whether there are differences in the total data between the source and target databases.

[0004] The existing technology has obvious drawbacks: it can only perform a rough verification of the total amount of table-level data, and cannot detect situations where the total amount is consistent but a single record has missing, duplicate, or incorrect field values. The accuracy of difference location is extremely low, and it cannot meet the needs of fine-grained verification of data consistency in scenarios such as financial data where the accuracy requirements are extremely high. It is easy to cause business decision errors due to hidden record-level or field-level differences. Summary of the Invention

[0005] In view of this, the purpose of this application is to provide an automated data comparison method, apparatus, device and storage medium that can significantly improve the efficiency and accuracy of data comparison.

[0006] In a first aspect, embodiments of this application provide an automated data comparison method, the method comprising: Obtain the comparison data from the source database; Generate a loadable data file for the target database based on the data to be compared. In the target database, a corresponding external table is created based on the data file; Compare the data in the external table with the target table that already exists in the target database; The differences are determined based on the comparison results of the data comparison.

[0007] Optionally, after determining the difference data based on the comparison results of the data comparison, the method further includes: The difference data is stored in a predefined difference result table in the target database; A structured difference report is generated based on the difference data.

[0008] Optionally, a data file loadable by the target database is generated based on the data to be compared, including: The shell script is used to call the database command-line tool to connect to the source database and execute data query commands to export the query results; Write the query results into an intermediate file; The intermediate file is subjected to format conversion and integrity verification to form intermediate data in the target format; Based on the intermediate data, a data file in a specified format that can be loaded by the target database is generated.

[0009] Optionally, creating a corresponding external table based on the data file includes: The structure information of the external table is determined based on the storage path and format definition of the data file; Based on the structure information, a table creation statement is executed in the target database to create the external table that maps to the data file.

[0010] Optionally, the step of comparing the data between the external table and the target table already existing in the target database includes: Based on the external table and the target table, execute an SQL query that includes a first set difference operation to identify a first type of record that exists in the external table but not in the target table; Based on the external table and the target table, execute an SQL query that includes a second set difference operation to identify a second type of record that exists in the target table but not in the external table; Connect the records with the same primary key in the external table and the target table, and compare the values ​​of each non-primary key field to identify a third type of record with inconsistent content.

[0011] Optionally, the discrepancies are determined based on the comparison results, including: The first type of record is identified as source-specific differential data; The second type of record is identified as unique difference data for the target end; The third type of record is identified as data showing inconsistencies in content.

[0012] Optionally, the method further includes performing incremental comparison, including: Obtain the preset start time point information for incremental comparison; Based on the starting time point information, only data that has been updated after the starting time point is obtained from the source database as the data to be compared. When comparing data between the external table and the target table, based on the start time information, only data in the target table that has been updated after the start time is compared with the external table.

[0013] Secondly, embodiments of this application provide an automated data comparison device, the device comprising: The comparison data acquisition module is used to acquire comparison data from the source database; The data file generation module is used to generate a loadable data file for the target database based on the data to be compared. An external table creation module is used to create a corresponding external table in the target database based on the data file. The data comparison module is used to compare the data of the external table with the target table that already exists in the target database. The difference data determination module is used to determine the difference data based on the comparison results of the data comparison.

[0014] Optionally, the device further includes a structured difference report generation module, used to store the difference data into a predefined difference result table in the target database after determining the difference data based on the comparison results of the data comparison; A structured difference report is generated based on the difference data.

[0015] Optionally, a data file loadable by the target database is generated based on the data to be compared, including: The shell script is used to call the database command-line tool to connect to the source database and execute data query commands to export the query results; Write the query results into an intermediate file; The intermediate file is subjected to format conversion and integrity verification to form intermediate data in the target format; Based on the intermediate data, a data file in a specified format that can be loaded by the target database is generated.

[0016] Optionally, creating a corresponding external table based on the data file includes: The structure information of the external table is determined based on the storage path and format definition of the data file; Based on the structure information, a table creation statement is executed in the target database to create the external table that maps to the data file.

[0017] Optionally, the step of comparing the data between the external table and the target table already existing in the target database includes: Based on the external table and the target table, execute an SQL query that includes a first set difference operation to identify a first type of record that exists in the external table but not in the target table; Based on the external table and the target table, execute an SQL query that includes a second set difference operation to identify a second type of record that exists in the target table but not in the external table; Connect the records with the same primary key in the external table and the target table, and compare the values ​​of each non-primary key field to identify a third type of record with inconsistent content.

[0018] Optionally, the discrepancies are determined based on the comparison results, including: The first type of record is identified as source-specific differential data; The second type of record is identified as unique difference data for the target end; The third type of record is identified as data showing inconsistencies in content.

[0019] Optionally, the apparatus further includes an incremental comparison module for: Obtain the preset start time point information for incremental comparison; Based on the starting time point information, only data that has been updated after the starting time point is obtained from the source database as the data to be compared. When comparing data between the external table and the target table, based on the start time information, only data in the target table that has been updated after the start time is compared with the external table.

[0020] Thirdly, embodiments of this application provide a computer device, including: a processor, a memory, and a bus. The memory stores machine-readable instructions executable by the processor. When the computer device is running, the processor communicates with the memory via the bus. When the machine-readable instructions are executed by the processor, the steps of the automated data comparison method described in any of the optional embodiments of the first aspect are performed.

[0021] Fourthly, embodiments of this application provide a computer-readable storage medium storing a computer program that, when executed by a processor, performs the steps of the automated data comparison method described in any of the optional embodiments of the first aspect.

[0022] The technical solution provided in this application includes, but is not limited to, the following beneficial effects: Obtaining the comparison data from the source database is the core input for the entire data comparison process. By accurately acquiring the comparison data from the source database, a reliable and complete data source foundation is ensured for subsequent comparison work. This avoids distortion of comparison results caused by incomplete data acquisition or data selection bias, thus building a solid foundation for data consistency verification.

[0023] Based on the data to be compared, a loadable data file for the target database is generated. This step achieves format adaptation between the source database and the target database. The generated loadable data file eliminates data format barriers between heterogeneous databases, ensuring that the source data can be recognized and processed by the target database. This avoids problems such as unusable data and interrupted comparison processes due to format incompatibility, and provides the necessary conditions for subsequent comparison operations within the target database.

[0024] In the target database, a corresponding external table is created based on the data file. This step establishes a direct association between the data file and the target database, enabling direct access to the source data through the external table. This eliminates the need to import the entire source data into the target database, saving storage resources and avoiding potential data loss or tampering during import. Furthermore, the creation of the external table allows data comparison to be conducted centrally within the target database, simplifying the comparison process.

[0025] The external table is compared with the target table already existing in the target database. This step directly achieves targeted verification of the source data (through the external table) and the target data. By conducting the comparison in the same database environment, the accurate execution of the comparison logic and the efficiency of the comparison process are ensured. This avoids the delays and errors caused by cross-environment comparisons, allowing data consistency verification to directly focus on the core difference identification process.

[0026] Based on the comparison results, discrepancies are identified. This step clarifies the core output of the comparison process: by accurately locating discrepancies, the inconsistencies are presented intuitively, providing a clear and definite basis for subsequent data correction and problem investigation. This avoids situations where comparison results are ambiguous or specific problems cannot be located, ensuring that data consistency verification can effectively solve practical problems and guarantee data quality.

[0027] The five steps described above form a complete closed-loop logic for automated data comparison. From data acquisition to the output of discrepancy data, each step provides crucial support for subsequent stages. The entire process can achieve end-to-end operation from data preparation to discrepancy identification without manual intervention, significantly improving the efficiency and accuracy of data comparison. It effectively solves the problems of non-standard processes and unreliable results in traditional data comparison, providing stable and efficient technical support for data consistency verification between heterogeneous databases.

[0028] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, preferred embodiments are described below in detail with reference to the accompanying drawings. Attached Figure Description

[0029] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0030] Figure 1 A flowchart of an automated data comparison method provided in Embodiment 1 of this application is shown; Figure 2 A flowchart of a structured difference report generation method provided in Embodiment 1 of this application is shown; Figure 3 A flowchart of a data file generation method provided in Embodiment 1 of this application is shown; Figure 4 A flowchart of an external table generation method provided in Embodiment 1 of this application is shown; Figure 5 A flowchart of a data comparison method provided in Embodiment 1 of this application is shown; Figure 6 A schematic diagram of an automated data comparison process provided in Embodiment 1 of this application is shown; Figure 7 This illustration shows a schematic diagram of an automated data comparison device provided in Embodiment 2 of this application; Figure 8 A schematic diagram of the structure of a computer device provided in Embodiment 3 of this application is shown. Detailed Implementation

[0031] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. The components of the embodiments of this application described and shown in the accompanying drawings can generally be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of this application provided in the accompanying drawings is not intended to limit the scope of the claimed application, but merely represents selected embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0032] Example 1 To facilitate understanding of this application, the following is combined with... Figure 1 The flowchart of the automated data comparison method provided in Embodiment 1 of this application illustrates Embodiment 1 in detail.

[0033] See Figure 1 As shown, Figure 1 A flowchart of an automated data comparison method provided in Embodiment 1 of this application is shown, wherein the method includes steps S101 to S105: S101: Obtain the comparison data from the source database.

[0034] Specifically, the source database is an Oracle database, and the project belongs to "Financial Management System Construction Project (including SAP)". When acquiring data, the Oracle official command-line tool SQL*Plus is called through a shell script. It supports full comparison (extracting all data from the table) and incremental comparison (filtering based on the timestamp field `LAST_UPDATE_TIME`). It also has a built-in data sharding loading strategy (Chunk Loading), which can handle TB-level data and solve the problem of efficiency in extracting large data tables.

[0035] S102: Generate a loadable data file for the target database based on the data to be compared.

[0036] Specifically, the target database is Huawei GaussDB DWS (Data Warehouse Service, a distributed analytical database based on GaussDB); the generation of data files requires automatic connection between Oracle and DWS through an adaptive ETL pipeline, combined with DDL parsing to generate compatible field mapping rules (ensuring consistent field types and lengths).

[0037] The data file format is CSV. After generation, integrity verification (including record count verification and MD5 checksum) needs to be performed. Solutions for handling CSV format errors include "direct error exit, attempt repair, record dirty data, and automatic retry / skip". Compressed transmission is optional.

[0038] S103: In the target database, create a corresponding external table based on the data file.

[0039] Specifically, the following prerequisites must be met: prepare an ETL server that connects the source database (Oracle) and the target database (DWS), enable the firewall, configure the database driver, create a "dedicated schema for comparison" on DWS, and configure GDS (GaussDB Data Service) and SQL*Plus.

[0040] The specific SQL statement for creating the external table is: CREATE FOREIGN TABLE ext_table_A (col1 INT, col2 VARCHAR(50),...)SERVER gsmpp_server OPTIONS (LOCATION 'gsfs: / / path / to / table_A.csv',FORMAT'CSV', DELIMITER ',' ); External tables are "virtual tables in the database, with the actual data stored in an external CSV file," and are loaded at high speed using GDS.

[0041] S104: Compare the data of the external table with the target table that already exists in the target database.

[0042] Specifically, the comparison is performed in DWS, using the MINUS full-field comparison algorithm based on set algebra operations (similar to Oracle's MINUS or standard SQL's EXCEPT), and supports configurable fault tolerance threshold management (ThresholdConfiguration). Relying on a high-performance batch processing framework, it has a dynamic resource allocation mechanism, an incremental comparison mode (DeltaComparison) to reduce IO consumption, and ensures reliability through "automatic retry mechanism (Retry Policy with Backoff)" and "dual-verification transaction protection (Dual-Verification Transaction)".

[0043] For large datasets, data is divided into blocks based on time range (e.g., partitioned by hour / day) for parallel comparison, without relying on shell background jobs or professional parallel computing frameworks.

[0044] S105: Determine the difference data based on the comparison results of the data comparison.

[0045] Specifically, the discrepancies are divided into three categories: SOURCE_ONLY (Source Database Only): The record exists in Oracle but not in DWS; TARGET_ONLY (Target database only): The record exists in DWS but not in Oracle; CONTENT_DIFF (Content Inconsistency): The primary keys are the same, but the values ​​of non-primary key fields are different; The comparison accuracy is at the field level, which solves the problem that the traditional solution only locates differences at the table level, and there is no problem of insufficient coverage by manual sampling.

[0046] This process completely replaces the traditional manual comparison mode through a fully automated chain of "Oracle data extraction - CSV file generation - DWS external table creation - full field comparison - difference classification", significantly improving the data verification efficiency of financial management systems. In terms of accuracy, it avoids problems such as missed detections and calculation errors in manual sampling. In terms of adaptability, it directly connects to the domestic DWS database through GDS and MINUS algorithms, solving the pain point of traditional ETL tools (such as Informatica) relying on commercial licenses and being unable to directly adapt to the domestic IT innovation environment. At the same time, the field-level comparison accuracy meets the standardized requirements of stored procedure migration verification.

[0047] In an optional implementation, see Figure 2 As shown, Figure 2 The flowchart of a structured difference report generation method provided in Embodiment 1 of this application is shown. After determining the difference data based on the comparison results of the data comparison, the method further includes steps S201-S202: S201: Store the difference data in a predefined difference result table in the target database.

[0048] Specifically, the target database is DWS, and the specific SQL statement for creating the difference results table is: CREATE TABLE diff_results ( diff_type VARCHAR(10), -- 'SOURCE_ONLY' / 'TARGET_ONLY' / 'CONTENT_DIFF' pk_value VARCHAR(100), -- primary key identifier diff_detailsTEXT -- details of the difference fields); -- perform MINUS comparison and record the results INSERT INTO diff_results SELECT 'SOURCE_ONLY', pk_col, NULL FROM source_table MINUS SELECT 'SOURCE_ONLY', pk_col, NULL FROM ext_table_A UNION ALL ...; The table adopts a third normal form structure, supports result set persistence and snapshot versioning, and includes all records in the end-to-end audit log, solving the problem of "easy loss of traditional manual records".

[0049] S202: Generate a structured difference report based on the difference data.

[0050] Specifically, the report includes built-in differential classification indicators (CRITICAL / WARNING / INFO) and can generate "reports (including charts) containing abnormal data" for manual review and investigation, enabling unattended automated operation.

[0051] This solution achieves standardized storage of discrepancy data through a three-normal form discrepancy result table, solving the problems of messy and easily lost traditional manual records. The end-to-end audit log can trace the time, results, and processing records of each comparison, meeting the compliance requirements for data traceability in financial management systems. The discrepancy classification and identification of structured reports can help operations and maintenance personnel quickly focus on core issues and avoid ineffective investigations. From an operations and maintenance cost perspective, no dedicated DBA is required to participate in data organization and report generation, achieving unattended operation, significantly reducing manpower input, and saving time for timely handling of data issues.

[0052] In an optional implementation, see Figure 3 As shown, Figure 3 The flowchart of a data file generation method provided in Embodiment 1 of this application is shown, wherein the step of generating a loadable data file for a target database based on the data to be compared includes steps S301 to S304: S301: Connect to the source database by calling the database command-line tool through a Shell script, and execute data query commands to export query results.

[0053] Specifically, the database command-line tool is SQL*Plus, and the shell script code is as follows: #! / bin / bash sqlplus -s user / pwd@oracle_db< <EOF SET HEADING OFF SETFEEDBACK OFF SPOOL / data / export / table_A.csv SELECT * FROM schema.table_A;SPOOL OFF EXIT; EOF; In the script, `user / pwd@oracle_db` represents the Oracle database connection information, ` / data / export / table_A.csv` is the path to store the exported file, `SET HEADING OFF` disables the table header, and `SET FEEDBACK OFF` disables execution feedback to ensure that the output is pure data.

[0054] S302: Write the query results into an intermediate file.

[0055] Specifically, the intermediate file, ` / data / export / table_A.csv` in the script mentioned above, is stored in the ` / data / export / ` directory of the ETL server. The file content is the raw query result exported by SQL*Plus, without any extra formatting information, and serves as a transitional carrier for subsequent processing.

[0056] S303: Perform format conversion and integrity verification on the intermediate file to form intermediate data in the target format.

[0057] Specifically, the format conversion will convert the original file to CSV format, and the integrity verification will use existing tools / methods such as "row count comparison and MD5 checksum"; the solutions for handling CSV format errors (column mismatch, unclosed quotation marks, etc.) include: directly reporting an error and exiting, attempting to repair, recording dirty data, and automatically retrying / skipping.

[0058] S304: Based on the intermediate data, generate a data file in a specified format that can be loaded by the target database.

[0059] Specifically, the specified format is CSV, the file must be compatible with GDS loading requirements, and the storage path must conform to the `gsfs: / / path / to / table_A.csv` format (for subsequent external table calls); it supports TB-level data processing, improves throughput by relying on the data chunk loading strategy, and the generated file does not require secondary processing and can be directly connected to DWS (solving the defect of traditional ETL tools such as Informatica that "requires secondary processing").

[0060] This step automates the data export process by using a Shell script to call SQL*Plus, avoiding data omissions or formatting issues caused by manual querying and copying / pasting. Parameters such as `SET HEADING OFF` ensure the purity of the exported data, laying the foundation for subsequent format conversion. Integrity verification (row count, MD5) can identify file corruption or data loss in advance, reducing unnecessary operations in subsequent comparison steps. The generated CSV file is directly compatible with GDS loading requirements, requiring no secondary processing. This solves the pain point of data format incompatibility requiring additional processing in traditional solutions. At the same time, the TB-level fragmentation processing capability meets the export needs of large-scale tables in financial management systems, improving the overall data flow efficiency.

[0061] In an optional implementation, see Figure 4 As shown, Figure 4 The flowchart of an external table generation method provided in Embodiment 1 of this application is shown, wherein the step of creating a corresponding external table based on a data file includes steps S401-S402: S401: Determine the structure information of the external table based on the storage path and format definition of the data file.

[0062] Specifically, the data file storage path is `gsfs: / / path / to / table_A.csv`, and the format is defined as CSV (with commas as the separator). The external table structure information must be completely consistent with the source table (Oracle's `schema.table_A`) and the target table (DWS table), including field names, data types (such as `col1 INT`, `col2 VARCHAR(50)`), and field lengths, to ensure that the data mapping is without deviation.

[0063] S402: Based on the structure information, execute a table creation statement in the target database to create the external table that maps to the data file.

[0064] Specifically, the target database is DWS, and the table creation statement is as follows: CREATE FOREIGN TABLE ext_table_A ( col1 INT, col2 VARCHAR(50), ... )SERVER gsmpp_server OPTIONS ( LOCATION 'gsfs: / / path / to / table_A.csv', FORMAT 'CSV', DELIMITER ',' ); Where `gsmpp_server` is the GDS service name, `LOCATION` specifies the data file path, and `FORMAT` and `DELIMITER` define the file format and delimiter, enabling direct mapping between external tables and CSV files.

[0065] By directly mapping external tables to CSV data files, there is no need to import all data from the source database into DWS, significantly saving DWS storage resources, which is especially suitable for comparison scenarios involving TB-level large data volumes. The external table structure is completely consistent with the source and target tables, ensuring that there are no data type incompatibility or missing field issues during field-level comparison, and avoiding comparison errors caused by structural deviations. Relying on the high-speed loading capabilities of the GDS service, the external table can quickly read CSV file data, providing efficient data support for subsequent MINUS comparisons with the target table, solving the problems of long time consumption and high resource consumption in traditional data import.

[0066] In an optional implementation, the data comparison between the external table and the target table already existing in the target database includes: Based on the external table and the target table, execute an SQL query that includes a first set difference operation to identify a first type of record that exists in the external table but not in the target table.

[0067] Specifically, the first set difference operation is MINUS, and the specific SQL statement (incremental scenario) is as follows: (SELECT col1, col2, col3, ..., pk_col FROM ext_table_A -- External table (source database data) WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME) MINUS (SELECT col1, col2, col3, ..., pk_col FROM target_table -- DWS target table WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME); Where `LAST_UPDATE_TIME` is the timestamp field of the source table and the target table, and `LAST_COMPARE_TIME` is the last comparison completion time. Incremental data is filtered for comparison.

[0068] Based on the external table and the target table, execute an SQL query that includes a second set difference operation to identify a second type of record that exists in the target table but not in the external table.

[0069] Specifically, the second set difference operation is a reverse MINUS, and the specific SQL statement (incremental scenario) is as follows: (SELECT col1, col2, col3, ..., pk_col FROM target_table -- DWS target table WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME) MINUS (SELECT col1, col2, col3, ..., pk_col FROM ext_table_A -- External table (source database data) WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME); Connect the records with the same primary key in the external table and the target table, and compare the values ​​of each non-primary key field to identify a third type of record with inconsistent content.

[0070] Specifically, the third type of record, "CONTENT_DIFF (Content Inconsistency)" records, refers to records where the primary key is the same in the external table (source database) and the target table (DWS table), but the values ​​of non-primary key fields differ. During identification, the two tables are first joined using the primary key (incremental scenarios, "LAST_UPDATE_TIME>LAST_COMPARE_TIME" filtering is applied), then non-primary key values ​​are checked field by field (character values ​​are compared to content, numeric values ​​to numeric values, and date values ​​to timestamps). If any field does not match, the record is identified as belonging to this type. Finally, its primary key (pk_value) and difference details (diff_details) are stored in the result table, and the diff_type is marked as "CONTENT_DIFF".

[0071] This comparison scheme uses MINUS set difference operations to accurately identify records unique to both the source and target ends, directly locating specific discrepancies and avoiding the problem of "knowing there is a difference but not knowing where it is." The logic of primary key joins and field-by-field comparison enables field-level difference identification, clearly outputting detailed information such as "col1 source end is A, target end is B," providing direct evidence for data problem investigation. In incremental scenarios, data is filtered using `LAST_UPDATE_TIME`, comparing only the newly added / changed parts, significantly reducing IO consumption and comparison time, supporting high-frequency comparison needs, and meeting the rapid verification requirements of financial management systems after data updates.

[0072] In an optional implementation, the discrepancies are determined based on the comparison results, including: The first type of record is identified as source-specific differential data.

[0073] Specifically, this type of record is marked as `diff_type = 'SOURCE_ONLY'`, and the processing method is as follows: record `pk_value` to the `diff_results` table, notify the data maintenance personnel to confirm whether it needs to be synchronized to DWS, and if it does need to be synchronized, record the synchronization operation log.

[0074] The second type of record is identified as target-specific difference data.

[0075] Specifically, this type of record is marked as `diff_type = 'TARGET_ONLY'`, and the processing method is as follows: check the data cleaning rules and ETL processing logic to determine whether it is "normal business rule data removal". If it is abnormal data, trigger a data quality alarm and start the abnormal handling process.

[0076] The third type of record is identified as data showing inconsistencies in content.

[0077] Specifically, this type of record is marked as `diff_type = 'CONTENT_DIFF'`, and the processing method is as follows: write the `diff_details` field (including details of the difference fields) into the `diff_results` table, perform snapshot persistence on the difference data, and support subsequent source tracing analysis.

[0078] By clearly classifying discrepancies into three categories—"SOURCE_ONLY," "TARGET_ONLY," and "CONTENT_DIFF"—data maintenance personnel can quickly identify the nature of the discrepancies and take targeted measures: unique source data can be synchronized in a timely manner to avoid missing data in the target database; unique target data can be used to verify ETL logic and avoid redundant or erroneous data; inconsistent data is stored through detailed records and snapshots, facilitating the tracing of the causes of discrepancies (such as export errors or loading anomalies) and providing a basis for root cause analysis; the entire process eliminates the need for manual screening of discrepancy data, avoiding errors in manual classification, while operation logs and snapshots ensure that the processing of each discrepancy data is traceable, meeting the data consistency and compliance requirements of financial management systems.

[0079] In an optional implementation, see Figure 5 As shown, Figure 5 A flowchart of a data comparison method provided in Embodiment 1 of this application is shown, wherein the method further includes performing incremental comparison, including steps S501 to S503: S501: Obtain the preset start time point information for incremental comparison.

[0080] Specifically, the start time information is `LAST_COMPARE_TIME` (the time when the last comparison was completed), which is stored in the comparison system configuration or the version snapshot of the `diff_results` table, and serves as the benchmark for incremental data filtering.

[0081] S502: Based on the starting time point information, only data that has been updated after the starting time point is obtained from the source database as the comparison data.

[0082] Specifically, the source database is Oracle. Incremental data is filtered using the SQL statement `WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME` to avoid the high IO consumption of full export. This method supports the efficiency of incremental comparison and is suitable for the high-frequency comparison needs of financial management systems.

[0083] S503: When comparing data between the external table and the target table, based on the start time information, only the data in the target table that has been updated after the start time is compared with the external table.

[0084] Specifically, the target table (DWS) also filters incremental data using `WHERE LAST_UPDATE_TIME>LAST_COMPARE_TIME`; it splits data blocks by time range (e.g., by hour / day), executes comparison tasks in parallel through a background queue, and dynamically adjusts the shard size (automatically optimized based on data volume).

[0085] Incremental comparison filters data by time range, processing only newly added / changed data. Compared to full comparison, this significantly reduces the amount of data, lowers IO consumption and system load for source database export, file transfer, and DWS comparison, making it particularly suitable for rapid verification scenarios after daily data updates in financial management systems. The "time-sharded + parallel execution" mode further improves processing efficiency, meeting the needs of high-frequency verification of multiple tables. Dynamically adjusting shard size allows for flexible resource allocation based on data volume, avoiding resource waste when data volume is low and processing congestion when data volume is high. At the same time, no manual intervention is required to filter incremental data, achieving unattended automated operation and reducing maintenance costs.

[0086] For a better explanation of the automated data comparison method provided in this application, please refer to [link / reference]. Figure 6 As shown, Figure 6This diagram illustrates an automated data comparison process provided in Embodiment 1 of this application. The process starts from "Start": First, it executes "Configure ETL server and source database (Oracle) and target database (GaussDB DWS) link" (corresponding to the aforementioned pre-environment configuration step). Then, it enters the verification step of "Determine if the table structure is consistent"—this step corresponds to the aforementioned requirement that the external table must be consistent with the source table and target table structure. If the result is "No", it executes "Adjust table structure". Afterwards, together with the branch with the result of "Yes", it enters the "SQLPlus export source data" step (i.e., the aforementioned operation of exporting data by calling SQLPlus through Shell script). After completion, it executes "GDS build external table" (corresponding to the aforementioned step of creating an external table based on GDS). Then, it enters the selection step of "Determine if incremental comparison is needed". If it is "Yes", it executes "MINUS incremental comparison"; if it is "No", it executes "full comparison" (both correspond to the aforementioned comparison logic based on MINUS operation). After the comparison is completed, it executes "Abnormal data processing" (corresponding to the aforementioned classification, storage and processing of difference data). Finally, the process ends with "End".

[0087] Example 2 See Figure 7 As shown, Figure 7 This illustration shows a schematic diagram of an automated data comparison device provided in Embodiment 2 of this application, wherein the device includes: The comparison data acquisition module 701 is used to acquire comparison data from the source database; Data file generation module 702 is used to generate a data file that can be loaded into the target database based on the data to be compared; External table creation module 703 is used to create a corresponding external table in the target database based on the data file; The data comparison module 704 is used to compare the data of the external table with the target table that already exists in the target database. The difference data determination module 705 is used to determine the difference data based on the comparison results of the data comparison.

[0088] In an optional implementation, the apparatus further includes a structured difference report generation module, used to store the difference data into a predefined difference result table in the target database after determining the difference data based on the comparison results of the data comparison; A structured difference report is generated based on the difference data.

[0089] In an optional implementation, a loadable data file for the target database is generated based on the data to be compared, including: The shell script is used to call the database command-line tool to connect to the source database and execute data query commands to export the query results; Write the query results into an intermediate file; The intermediate file is subjected to format conversion and integrity verification to form intermediate data in the target format; Based on the intermediate data, a data file in a specified format that can be loaded by the target database is generated.

[0090] In an optional implementation, creating the corresponding external table based on the data file includes: The structure information of the external table is determined based on the storage path and format definition of the data file; Based on the structure information, a table creation statement is executed in the target database to create the external table that maps to the data file.

[0091] In an optional implementation, the data comparison between the external table and the target table already existing in the target database includes: Based on the external table and the target table, execute an SQL query that includes a first set difference operation to identify a first type of record that exists in the external table but not in the target table; Based on the external table and the target table, execute an SQL query that includes a second set difference operation to identify a second type of record that exists in the target table but not in the external table; Connect the records with the same primary key in the external table and the target table, and compare the values ​​of each non-primary key field to identify a third type of record with inconsistent content.

[0092] In an optional implementation, the discrepancies are determined based on the comparison results, including: The first type of record is identified as source-specific differential data; The second type of record is identified as unique difference data for the target end; The third type of record is identified as data showing inconsistencies in content.

[0093] In an optional implementation, the apparatus further includes an incremental comparison module for: Obtain the preset start time point information for incremental comparison; Based on the starting time point information, only data that has been updated after the starting time point is obtained from the source database as the data to be compared. When comparing data between the external table and the target table, based on the start time information, only data in the target table that has been updated after the start time is compared with the external table.

[0094] Example 3 Based on the same application concept, see [link / reference] Figure 8 As shown, Figure 8 This illustration shows a structural schematic diagram of a computer device provided in Embodiment 3 of this application, wherein, as shown... Figure 8 As shown, the computer device 800 provided in Embodiment 3 of this application includes: The computer device 800 includes a processor 801, a memory 802, and a bus 803. The memory 802 stores machine-readable instructions that can be executed by the processor 801. When the computer device 800 is running, the processor 801 communicates with the memory 802 through the bus 803. When the machine-readable instructions are executed by the processor 801, the steps of the automated data comparison method shown in Embodiment 1 are performed.

[0095] Example 4 Based on the same concept, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed by a processor, performs the steps of the automated data comparison method described in any of the above embodiments.

[0096] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working process of the system and apparatus described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0097] The computer program product for automated data comparison provided in this application includes a computer-readable storage medium storing program code. The instructions included in the program code can be used to execute the methods described in the preceding method embodiments. For specific implementation details, please refer to the method embodiments, which will not be repeated here.

[0098] The automated data comparison device provided in this application embodiment can be specific hardware on a device or software or firmware installed on the device. The implementation principle and technical effects of the device provided in this application embodiment are the same as those in the foregoing method embodiments. For the sake of brevity, any parts not mentioned in the device embodiment can be referred to the corresponding content in the foregoing method embodiments. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the systems, devices, and units described above can all be referred to the corresponding processes in the above method embodiments, and will not be repeated here.

[0099] In the embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. The apparatus embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. Furthermore, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Additionally, the displayed or discussed mutual couplings, direct couplings, or communication connections may be through some communication interfaces; indirect couplings or communication connections between devices or units may be electrical, mechanical, or other forms.

[0100] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0101] In addition, the functional units in the embodiments provided in this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.

[0102] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0103] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures. In addition, the terms "first", "second", "third", etc. are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0104] Finally, it should be noted that the above-described embodiments are merely specific implementations of this application, used to illustrate the technical solutions of this application, and not to limit them. The protection scope of this application is not limited thereto. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that any person skilled in the art can still modify or easily conceive of changes to the technical solutions described in the foregoing embodiments, or make equivalent substitutions for some of the technical features, within the scope of the technology disclosed in this application; and these modifications, changes, or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application. All should be covered within the protection scope of this application. Therefore, the protection scope of this application should be determined by the protection scope of the claims.

Claims

1. An automated data comparison method, characterized in that, The method includes: Obtain the comparison data from the source database; Generate a loadable data file for the target database based on the data to be compared. In the target database, a corresponding external table is created based on the data file; Compare the data in the external table with the target table that already exists in the target database; The differences are determined based on the comparison results of the data comparison.

2. The method according to claim 1, characterized in that, After determining the discrepancies based on the comparison results of the data comparison, the method further includes: The difference data is stored in a predefined difference result table in the target database; A structured difference report is generated based on the difference data.

3. The method according to claim 1, characterized in that, Generate a loadable data file for the target database based on the data to be compared, including: The shell script is used to call the database command-line tool to connect to the source database and execute data query commands to export the query results; Write the query results into an intermediate file; The intermediate file is subjected to format conversion and integrity verification to form intermediate data in the target format; Based on the intermediate data, a data file in a specified format that can be loaded by the target database is generated.

4. The method according to claim 1, characterized in that, The creation of the corresponding external table based on the data file includes: The structure information of the external table is determined based on the storage path and format definition of the data file; Based on the structure information, a table creation statement is executed in the target database to create the external table that maps to the data file.

5. The method according to claim 1, characterized in that, The comparison of data between the external table and the target table already existing in the target database includes: Based on the external table and the target table, execute an SQL query that includes a first set difference operation to identify a first type of record that exists in the external table but does not exist in the target table; Based on the external table and the target table, execute an SQL query that includes a second set difference operation to identify a second type of record that exists in the target table but not in the external table; Connect the records with the same primary key in the external table and the target table, and compare the values ​​of each non-primary key field to identify a third type of record with inconsistent content.

6. The method according to claim 5, characterized in that, The discrepancies were determined based on the comparison results, including: The first type of record is identified as source-specific differential data; The second type of record is identified as unique difference data for the target end; The third type of record is identified as data showing inconsistencies in content.

7. The method according to claim 1, characterized in that, The method further includes performing incremental comparisons, including: Obtain the preset start time point information for incremental comparison; Based on the starting time point information, only data that has been updated after the starting time point is obtained from the source database as the data to be compared. When comparing data between the external table and the target table, based on the start time information, only data in the target table that has been updated after the start time is compared with the external table.

8. An automated data comparison device, characterized in that, The device includes: The comparison data acquisition module is used to acquire comparison data from the source database; The data file generation module is used to generate a loadable data file for the target database based on the data to be compared. An external table creation module is used to create a corresponding external table in the target database based on the data file. The data comparison module is used to compare the data of the external table with the target table that already exists in the target database. The difference data determination module is used to determine the difference data based on the comparison results of the data comparison.

9. A computer device, characterized in that, include: The computer device includes a processor, a memory, and a bus. The memory stores machine-readable instructions executable by the processor. When the computer device is running, the processor communicates with the memory via the bus. When the machine-readable instructions are executed by the processor, they perform the steps of the automated data comparison method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, performs the steps of the automated data comparison method as described in any one of claims 1 to 7.

Images