A self-adaptive data access reconciliation method, device and medium

Abstract

The present invention provides an adaptive data access reconciliation method, device and medium, which belongs to the field of computer information processing technology. The method includes: selecting a source database and source data table, a target database and a target data table for reconciliation, selecting the primary keys of the source data table and the target data table as the basis for data comparison, and setting the initial reconciliation frequency; reading the data of the source database and the target database through Spark SQL, and performing distributed preprocessing on the data of the source database; generating an MD5 digest for each row of data in the source data table and the target data table through the MD5 algorithm, and adding the generated MD5 digest as a new column to the data table; searching for the MD5 digest according to the primary key comparison, determining the difference data row, searching for the problem data according to the difference data row, and recording the reconciliation result; adjusting the reconciliation frequency according to the current data reconciliation result through the Q-learning algorithm. The present invention improves the reconciliation speed and accuracy, more efficiently utilizes computing resources, and reduces hardware resource consumption.

Description

Technical Field

[0001] The invention relates to an adaptive data access and reconciliation method, device and medium, and belongs to the technical field of computer information processing. Background Art

[0002] In the current data access process, especially when synchronizing data in a distributed system, data consistency is a crucial issue. However, due to various reasons such as network latency and concurrent writing, data inconsistency often occurs. Although traditional reconciliation methods, such as primary key comparison and transaction log tracking, can solve the problem of data inconsistency to a certain extent, they are powerless when faced with massive and highly concurrent data scenarios.

[0003] Specifically, the traditional reconciliation method has the following main defects:

[0004] Low reconciliation efficiency: The traditional method of reconciliation on large-scale data sets takes a long time and cannot meet real-time requirements. In the case of large amounts of data, the reconciliation process may take hours or even days, seriously affecting business operation efficiency.

[0005] Data integrity is difficult to guarantee: When network transmission is unstable or data is updated frequently, it is difficult to ensure data consistency and integrity by simply relying on the database's own transaction mechanism. Problems such as data loss, duplication or incorrect updates often occur, bringing great risks to business operations.

[0006] High resource consumption: With the exponential growth of data volume, traditional reconciliation methods require repeated calculation and storage of a large amount of data, resulting in serious waste of resources. This not only increases the consumption of hardware resources, but also increases operating costs.

[0007] Therefore, in response to the above-mentioned problems of traditional reconciliation methods, the industry urgently needs an efficient, accurate and adaptive data access reconciliation method to adapt to the growing amount of data and concurrent access requirements, and ensure data consistency and business operation efficiency. Summary of the invention

[0008] The purpose of the present invention is to provide an adaptive data access reconciliation method, device and medium to solve the above-mentioned problems and provide an efficient, accurate and adaptive data access reconciliation method.

[0009] In order to achieve the above object, the present invention is implemented through the following technical solutions:

[0010] An adaptive data access reconciliation method comprises the following steps:

[0011] Select the source database and source data table, target database and target data table for reconciliation, select the primary keys of the source data table and target data table as the basis for data comparison, and set the initial reconciliation frequency;

[0012] Use Spark SQL to read data from the source and target databases, and perform distributed preprocessing on the data in the source database.

[0013] Generate an MD5 digest for each row of data in the source data table and the target data table using the MD5 algorithm, and add the generated MD5 digest as a new column to the data table;

[0014] Search for MD5 digests based on primary key comparison, determine the difference data rows, find problematic data based on the difference data rows, and record the reconciliation results;

[0015] The reconciliation frequency is adjusted according to the current data reconciliation results through the Q-learning algorithm.

[0016] Preferably, the method of searching for data rows with inconsistent MD5 digests based on primary key comparison is as follows:

[0017] Create a Spark elastic distributed dataset, perform an inner join on the source data table and the target data table based on the primary key field, compare the MD5 digest columns in the source data table and the target data table, and locate the data rows with inconsistent MD5 digests as the difference data rows.

[0018] Preferably, the method of searching for problematic data according to the difference data rows is as follows:

[0019] Generate MD5 digests for each column of the difference data rows, compare the MD5 digests of the corresponding columns in the source data table and the target data table, and mark the columns with inconsistent MD5 digests as problematic columns;

[0020] For each problematic data row, compare the problematic columns, find the specific problematic columns for each row of data, calculate the accuracy of the field content, and record a list of abnormal data, including source data and target data.

[0021] Preferably, the reconciliation results are recorded including execution time, accuracy, number of data items, and YARN queue load.

[0022] Preferably, the Q-learning algorithm adjusts the reconciliation frequency by adjusting the cron expression that is executed regularly according to the current data reconciliation result;

[0023] The Q-learning algorithm state space includes accuracy, execution time and YARN queue load;

[0024] The Q-learning algorithm reward function is as follows:

[0025] ,

[0026] ,

[0027] ,

[0028] ,

[0029] in, Represents the reward value, Indicates the current accuracy rate. Indicates the reward change rate caused by unit data volume deviation, Indicates the current number of data items. Indicates the expected number of data items for each reconciliation. Indicates the YARN queue load.

[0030] Optimally, the reward change rate caused by unit data volume deviation The calculation formula is as follows:

[0031] .

[0032] Preferably, the distributed preprocessing includes data cleaning, conversion, and aggregation.

[0033] An adaptive data access and reconciliation device comprises a processor and a memory storing program instructions, wherein the processor is configured to execute the adaptive data access and reconciliation method when running the program instructions.

[0034] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the adaptive data access and reconciliation method.

[0035] The advantages of the present invention are: by leveraging the distributed computing capability of the Spark platform and the high efficiency of the MD5 algorithm, the present invention greatly improves the speed and accuracy of account reconciliation. By comparing the MD5 digests of rows and columns twice, the calculation process is optimized, further improving the efficiency.

[0036] The method of the present invention can flexibly cope with the growing data volume and concurrent access requirements, support large-scale distributed deployment and horizontal expansion, and meet the needs of future business development.

[0037] Automatically adjust strategies based on changes in the environment, such as data volume growth, system performance fluctuations, etc. Automatically adjust the reconciliation frequency through the Q-learning algorithm, avoiding the tedious process of constantly adjusting parameters or strategies manually.

[0038] The introduction of a timestamp-based data change perception mechanism only reconciles new or updated data items, reducing unnecessary data processing and computing overhead. Automatically adjust the reconciliation frequency to avoid unnecessary data comparisons, making more efficient use of computing resources and reducing hardware resource consumption. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The accompanying drawings are used to provide further understanding of the present invention and constitute a part of the specification. They are used to explain the present invention together with the embodiments of the present invention and do not constitute a limitation of the present invention.

[0040] Figure 1 It is a schematic diagram of the process of the present invention. DETAILED DESCRIPTION

[0041] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0042] Example 1

[0043] An adaptive data access reconciliation method comprises the following steps:

[0044] S1: Select the source database and source data table, target database and target data table for reconciliation, select the primary keys of the source data table and target data table as the basis for data comparison, and set the initial reconciliation frequency;

[0045] S2: Spark extracts data, reads data from the source database and the target database through Spark SQL, and performs distributed preprocessing on the data in the source database;

[0046] S3: Generate an MD5 digest for each row of data in the source data table and the target data table using the MD5 algorithm, and add the generated MD5 digest as a new column to the data table;

[0047] S4: Search for the MD5 digest based on the primary key comparison, determine the difference data rows, search for the problematic data based on the difference data rows, and record the reconciliation results;

[0048] S5: Adjust the reconciliation frequency based on the current data reconciliation results through the Q-learning algorithm.

[0049] As a refinement of the above embodiment, when setting the reconciliation process, it is first necessary to clarify the source database and its data tables, as well as the target database and the corresponding data tables. Subsequently, the primary keys of these data tables are accurately selected as the basis for data comparison. Next, the initial reconciliation frequency is set according to business needs, for example, once an hour. In each reconciliation operation, the system will intelligently filter according to the insertion and update timestamps of the data, and only compare the incremental data generated since the last reconciliation, thereby optimizing the reconciliation efficiency and reducing unnecessary resource consumption.

[0050] Specifically, the system records the end time of each reconciliation as a screening benchmark. Subsequently, SQL query statements are used to filter out new or updated records after the last reconciliation from the source and target data tables, and these incremental data are compared. Finally, the last reconciliation time is updated for next use. This process improves reconciliation efficiency and accuracy and optimizes resource utilization.

[0051] As a refinement of the above embodiment, the process of Spark extracting data is as follows:

[0052] In order to start an Apache Spark application and use its parallel computing capabilities to process large-scale data sets, you first need to initialize a SparkSession. On this basis, we will use the powerful functions of Spark SQL to achieve efficient data reading and interaction between the source and target databases. As a module of Spark, Spark SQL not only provides SQL-like query capabilities, but also deeply integrates the DataFrame API, making distributed reading, processing, and analysis of data intuitive and efficient. Using this feature, the data in the source database is deeply distributed preprocessed, including but not limited to data cleaning, conversion, aggregation and other operations. These tasks will be executed in parallel in Spark's distributed environment, thereby greatly improving processing speed and throughput.

[0053] In addition, to ensure effective resource management and flexible job scheduling, jobs will be configured to be submitted to the Yarn queue for running. YARN (Yet Another Resource Negotiator), as the resource manager of the Hadoop cluster, can intelligently allocate and manage cluster resources, including CPU, memory, etc., to ensure that Spark jobs can run efficiently and stably. Submitting Spark tasks to the YARN queue for running not only helps optimize resource utilization, but also improves the controllability and predictability of jobs, allowing large-scale data processing tasks to be executed more smoothly.

[0054] As a refinement of the above embodiment, the specific process of generating an MD5 digest for each line of data is as follows:

[0055] Use the MD5 algorithm to generate a summary for each row of data in the source and target data tables, generate a unique data fingerprint, and reduce the comparison complexity of the original data. Add the generated MD5 summary as a new column to the data table.

[0056] As a refinement of the above embodiment, the specific method of searching for data rows with inconsistent MD5 digests based on primary key comparison is as follows:

[0057] Create a Spark resilient distributed dataset (RDD) and inner join the source data table with the target data table based on the primary key field. Compare the MD5 digest columns in the source data table and the target data table. As a widely used hash algorithm, MD5 digest can generate a unique fingerprint of data, which greatly simplifies the data consistency verification process. By calculating and comparing the MD5 digest columns of the corresponding rows in the source data table and the target data table, we can quickly locate those data rows with inconsistent MD5 digests, which represent the problem of data inconsistency. This not only significantly improves the efficiency of data verification, but also reduces the complexity and error rate when directly comparing multiple columns of data, providing strong support for data reconciliation in a big data environment.

[0058] As a refinement of the above embodiment, the specific method of searching for problematic data according to the difference data rows is as follows:

[0059] Process the problematic data rows and find the problematic columns; filter out the different data rows from the data rows with inconsistent MD5 summaries. Generate MD5 summaries for each column of the different data rows. Compare the MD5 summaries of the corresponding columns in the source data table and the target data table. Mark the columns in the source data table and the target data table with inconsistent MD5 summaries.

[0060] Accurately find the problematic data; for each problematic data row, just compare the problematic columns to find the specific problematic columns for each row of data. Calculate the accuracy of the field content and record a list of abnormal data, including source data and target data. When accurately finding the problematic columns for each row of data, you only need to compare the problematic data columns, avoiding the comparison of all columns and saving calculation time.

[0061] As a refinement of the above embodiment, the reconciliation result is recorded including the execution time, accuracy, number of data items, and YARN queue load. The Q-learning algorithm adjusts the reconciliation frequency by adjusting the cron expression that is executed regularly according to the current data reconciliation result;

[0062] The Q-learning algorithm state space includes accuracy, execution time and YARN queue load;

[0063] The Q-learning algorithm reward function is as follows:

[0064] ,

[0065] ,

[0066] ,

[0067] ,

[0068] in, Represents the reward value, Indicates the current accuracy rate. Indicates the reward change rate caused by unit data volume deviation, Indicates the current number of data items. Indicates the expected number of data items for each reconciliation. Indicates the YARN queue load.

[0069] Reward change rate caused by unit data volume deviation The calculation formula is as follows:

[0070] .

[0071] Through the design of the above reward function, the agent will learn to reduce the frequency of reconciliation when the accuracy is close to 100% and the YARN queue load is high, and maintain the frequency of reconciliation when the number of data items is close to the expected number of items per reconciliation. This helps to improve the accuracy and efficiency of reconciliation and make rational use of resources.

[0072] Specifically, for each dimension, we need to define a reasonable interval division in order to map continuous values ​​to discrete state spaces. In this embodiment, the state space of the Q-learning algorithm is defined as follows:

[0073] Accuracy range: [0-89], [90-91], [92-93], [94,95], [96,97], [98,99], [99,100].

[0074] Execution time interval: from 0 to 300 seconds (5 minutes), divided into intervals of 30 seconds each.

[0075] YARN queue load range: from 0 to 100%, divided into intervals of 10%.

[0076] The Q-learning algorithm action space is defined as follows:

[0077] Defines how to adjust the time interval of the cron expression. The action space is defined as: {+10 minutes, -10 minutes, +30 minutes, -30 minutes, remain unchanged}.

[0078] As a refinement of the above embodiment, the Q-learning algorithm includes the following steps:

[0079] (1) Initialization: Initialize a Q table to store the expected reward of each state-action pair, and assign all elements to 0. Given an exploration rate , learning rate and discount factor .

[0080] (2) Get the current state: Find the corresponding state based on the current accuracy and number of data items.

[0081] (3) Select action: Use The strategy balances exploration and exploitation. The probability of randomly choosing an action is The probability of choosing the action with the largest Q value is

[0082] (4) Execute action: Adjust the cron expression according to the selected action.

[0083] (5) Receive feedback: Calculate rewards based on the reconciliation results.

[0084] (6) Update the Q table: Update the Q value according to the reward value, so as to continuously optimize the Q value function and make it gradually converge to the optimal value. The formula for updating the Q value is to use the Bellman equation to update the value in the Q table.

[0085] ,

[0086] in, Indicates in status Next Action The Q value, is the learning rate (controls the weights for each update), It is an execution action After that, the agent can get an immediate reward, is the discount factor (controls the weight of future rewards, indicating the importance of future rewards), and Indicates the new state and new action after executing the current action. Indicates that in the next state By continuously iteratively updating the Q value, the agent can eventually learn the optimal strategy for taking different actions in different states, thereby achieving autonomous decision-making.

[0087] Decay exploration rate: Gradually reduce the exploration rate so that the agent relies more on what it has learned.

[0088] As a refinement of the above embodiment, a reconciliation report is generated based on the reconciliation results. When data inconsistency is detected, a corresponding error correction mechanism is triggered according to a pre-set strategy. At the same time, the reconciliation results are fed back to the operation and maintenance personnel in a visual manner, so that problems can be promptly checked and measures can be taken to repair data differences.

[0089] It should be noted that the adaptive data access and reconciliation method proposed in the present invention has the following significant advantages:

[0090] High performance: With the distributed computing power of the Spark platform and the high efficiency of the MD5 algorithm, the reconciliation speed and accuracy are greatly improved. The computing efficiency is improved by comparing the MD5 digests of rows and columns twice.

[0091] Scalability: It can flexibly cope with the growing amount of data and concurrent access requirements, and supports large-scale distributed deployment and horizontal expansion.

[0092] Adaptability: Ability to automatically adjust strategies based on changes in the environment, such as growth in data volume, fluctuations in system performance, etc. Automatically discover the optimal action through the learning process without the need for manual constant adjustment of parameters or strategies.

[0093] Resource optimization: By adaptively adjusting the reconciliation frequency, unnecessary data comparison is avoided, computing resources can be used more efficiently, and hardware resource consumption is reduced.

[0094] This method can be widely used in financial transactions, IoT device data synchronization, e-commerce order systems and other fields, helping to improve the stability and data consistency of data processing systems, thereby improving business operation efficiency and customer satisfaction.

[0095] The embodiment of the present disclosure provides an adaptive data access and reconciliation device, including a processor and a memory. Optionally, the device may also include a communication interface and a bus. The processor, the communication interface, and the memory may communicate with each other through the bus. The communication interface may be used for information transmission. The processor may call the logic instructions in the memory to execute the adaptive data access and reconciliation method of the above embodiment.

[0096] In addition, the logic instructions in the above-mentioned memory can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product.

[0097] The memory, as a computer-readable storage medium, can be used to store software programs and computer executable programs, such as program instructions / modules corresponding to the method in the embodiment of the present disclosure. The processor 304 executes functional applications and data processing by running the program instructions / modules stored in the memory, that is, implementing the adaptive data access reconciliation method in the above embodiment.

[0098] The memory may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required for at least one function; the data storage area may store data created according to the use of the terminal device, etc. In addition, the memory may include a high-speed random access memory and may also include a non-volatile memory.

[0099] An embodiment of the present disclosure provides a computer-readable storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured to execute the above-mentioned adaptive data access reconciliation method.

[0100] The computer-readable storage medium mentioned above may be a transient computer-readable storage medium or a non-transitory computer-readable storage medium.

[0101] The technical solution of the embodiment of the present disclosure can be embodied in the form of a software product, which is stored in a storage medium and includes one or more instructions for enabling a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in the embodiment of the present disclosure. The aforementioned storage medium may be a non-transient storage medium, including: a USB flash drive, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a disk or an optical disk, and other media that can store program codes, or a transient storage medium.

[0102] Finally, it should be noted that the above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art can still modify the technical solutions described in the aforementioned embodiments or replace some of the technical features therein by equivalents. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. An adaptive data access and reconciliation method, characterized in that: The following steps are involved: Select the source database and source data table, target database and target data table for reconciliation, select the primary keys of the source data table and target data table as the basis for data comparison, and set the initial reconciliation frequency; Use Spark SQL to read data from the source and target databases, and perform distributed preprocessing on the data in the source database. Generate an MD5 digest for each row of data in the source data table and the target data table using the MD5 algorithm, and add the generated MD5 digest as a new column to the data table; Search for MD5 digests based on primary key comparison, determine the difference data rows, find problematic data based on the difference data rows, and record the reconciliation results; Adjust the reconciliation frequency based on the current data reconciliation results through the Q-learning algorithm; The Q-learning algorithm adjusts the reconciliation frequency by adjusting the cron expression that is executed regularly according to the current data reconciliation result; The Q-learning algorithm state space includes accuracy, execution time and YARN queue load; The Q-learning algorithm reward function is as follows: ， ， ， ， in, Represents the reward value, Indicates the current accuracy rate. Indicates the reward change rate caused by unit data volume deviation, Indicates the current number of data items. Indicates the expected number of data items for each reconciliation. Indicates the YARN queue load.

2. The adaptive data access and reconciliation method according to claim 1, characterized in that: The specific method of searching for data rows with inconsistent MD5 digests based on primary key comparison is as follows: Create a Spark elastic distributed dataset, perform an inner join on the source data table and the target data table based on the primary key field, compare the MD5 digest columns in the source data table and the target data table, and locate the data rows with inconsistent MD5 digests as the difference data rows.

3. The adaptive data access and reconciliation method according to claim 2, characterized in that: The specific method of searching for problematic data based on the difference data rows is as follows: Generate MD5 digests for each column of the difference data rows, compare the MD5 digests of the corresponding columns in the source data table and the target data table, and mark the columns with inconsistent MD5 digests as problematic columns; For each problematic data row, compare the problematic columns, find the specific problematic columns for each row of data, calculate the accuracy of the field content, and record a list of abnormal data, including source data and target data.

4. The adaptive data access and reconciliation method according to claim 1, characterized in that: The reconciliation results recorded include execution time, accuracy, number of data items, and YARN queue load.

5. The adaptive data access and reconciliation method according to claim 1, characterized in that: Reward change rate caused by unit data volume deviation The calculation formula is as follows: 。 6. The adaptive data access and reconciliation method according to claim 1, characterized in that: The distributed preprocessing includes data cleaning, conversion, and aggregation.

7. The adaptive data access and reconciliation method according to claim 1, characterized in that: Filter incremental data based on the data insertion and update timestamps, and only compare data that has changed since the last reconciliation.

8. An adaptive data access and reconciliation device, comprising a processor and a memory storing program instructions, characterized in that: The processor is configured to execute the adaptive data access reconciliation method according to any one of claims 1 to 7 when running the program instructions.

9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and when the program is executed by a processor, the adaptive data access and reconciliation method as described in any one of claims 1 to 7 above is implemented.

Images