Pure SQL (Structured Query Language) method for realizing data increment update in multi-table complex logic scene

Abstract

The invention relates to the technical field of data processing, and provides a pure SQL (Structured Query Language) method for realizing data incremental updating in a multi-table complex logic scene, which comprises the following steps of: 1, setting an incremental updating reference quantitative parameter; 2, creating an incremental update buffer pool; 3, organizing incremental updating buffer pool data; 4, writing back incremental update reference quantitative parameters; 5, determining an incremental update data processing range; and 6, processing logic of the whole set of data organization. According to the invention, data increment processing can be well carried out.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a pure SQL method for realizing incremental data update in a multi-table complex logic scenario. Background Art

[0002] In large and medium-sized enterprises, businesses are often complex and systems are numerous. For example, the source data of indicators such as KPIs often come from multiple business lines or even multiple systems, which requires the organization and processing of multi-table, multi-dimensional complex logical data. The processing cycle is generally the same year. The usual solution is to process the inventory, that is, to process from January 1 to the current time, so as to track the progress of the business throughout the year in real time.

[0003] The existing stock processing technology solutions have certain limitations, especially in the problem of repeated data processing. Take the short-term insurance system with more than 20 million policy records in a year as an example: in December, in order to process the latest indicator data of the day, the previous more than 20 million policy data need to be reprocessed, resulting in a series of problems such as long processing cycle, high consumption of server resources, and easy blockage of the backend database. Therefore, it is necessary to develop an efficient processing solution to solve the above problems and achieve cost reduction and efficiency improvement. Summary of the invention

[0004] The present invention provides a pure SQL method for implementing incremental data update in a multi-table complex logic scenario, which can overcome certain or some defects of the prior art.

[0005] The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to the present invention includes the following steps:

[0006] 1. Set the quantitative parameters of the incremental update benchmark;

[0007] 2. Create an incremental update buffer pool;

[0008] 3. Organize incremental updates to buffer pool data;

[0009] 4. Write-back incremental update benchmark quantitative parameters;

[0010] 5. Determine the scope of incremental update data processing;

[0011] 6. A complete set of data organization and processing logic.

[0012] Preferably, in step one, the latest benchmark quantitative parameters are obtained. The benchmark quantitative parameters are the key to realizing incremental update and are used to determine whether the data belongs to the incremental range. The benchmark quantitative parameters include a unique serial number, time or combined parameters, and a backtracking mechanism is added to realize fault-tolerant processing.

[0013] Preferably, in step 2, the buffer pool is used to store incremental update index data, including primary key information fields of processed data and information fields required in the processing logic.

[0014] Preferably, in step three, based on the benchmark quantitative parameters, the incremental data in all single tables of the processing logic are retrieved, merged and summarized, and then put into the data buffer pool.

[0015] Preferably, in step 4, the latest incremental update benchmark quantitative parameters are written back and saved for use in the next incremental update.

[0016] Preferably, in step five, the incremental processing range of the data is determined by updating the buffer pool.

[0017] Preferably, in step six, the entire data processing is carried out according to the original organizational logic based on the incremental processing scope of the data.

[0018] Compared with the existing data processing method, the data incremental processing method of the present invention has the following advantages:

[0019] 1) Solved the problem of repeated data processing, greatly improved the processing efficiency, especially for data above one million. In practice, the processing cycle (in minutes) can be improved from hundreds to tens or even units;

[0020] 2) Effectively save processing resources, significantly reduce server and backend database pressure, and no longer worry about database congestion;

[0021] 3) Incremental processing method, basically no longer limited by the processing time range and the size of the processing data base;

[0022] 4) The solution has good compatibility. You only need to add an incremental data buffer pool at the beginning and then connect it later. The processing logic and program structure of the original data do not need to be changed. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 It is a flowchart of a pure SQL method for implementing incremental data update in a multi-table complex logic scenario in an embodiment;

[0024] Figure 2 This is a schematic diagram of setting incremental update benchmark quantitative parameters using time as an example in the embodiment;

[0025] Figure 3 It is a schematic diagram of organizing incremental updates of buffer pool data in an embodiment. DETAILED DESCRIPTION

[0026] In order to further understand the content of the present invention, the present invention is described in detail in conjunction with the accompanying drawings and embodiments. It should be understood that the embodiments are only for explaining the present invention and are not intended to limit it.

[0027] Example

[0028] like Figure 1 As shown, this embodiment provides a pure SQL method for implementing incremental data update in a multi-table complex logic scenario, which includes the following steps:

[0029] 1. Set the quantitative parameters of the incremental update benchmark;

[0030] Obtain the latest benchmark quantitative parameters. The benchmark quantitative parameters are the key to achieving incremental updates and are used to determine whether the data belongs to the incremental range. The benchmark quantitative parameters include unique serial numbers, time or combined parameters. A backtracking mechanism is added to achieve fault-tolerant processing.

[0031] like Figure 2 As shown, taking time as an example, first obtain the latest data update time of the target table, and then determine whether the update time is empty. If so, obtain the current time, and then trace the latest data update time back to the specified time to set the incremental update benchmark time; if not, directly trace the latest data update time back to the specified time to set the incremental update benchmark time.

[0032] 2. Create an incremental update buffer pool;

[0033] The buffer pool is used to store incremental update index data, including the primary key information fields of the processed data and the information fields required in the processing logic.

[0034] 3. Organize incremental updates to buffer pool data;

[0035] Based on the benchmark quantitative parameters, the incremental data in all single tables of the processing logic are retrieved, merged and summarized, and then put into the data buffer pool. Figure 3 As shown, specifically:

[0036] 3.1) Initialize the list of N original data tables to be processed;

[0037] 3.2) Determine whether to continue the list traversal search. If so, retrieve the feature element data list that needs to be updated and is associated with the current data original table according to the reference time parameter, and traverse it in a loop; if not, proceed to the next step;

[0038] 3.3) Summarize and merge N lists of feature element data that need to be updated;

[0039] 3.4) The merged result data is written into the update data buffer table.

[0040] 4. Write-back incremental update benchmark quantitative parameters;

[0041] The latest incremental update benchmark quantitative parameters are written back and saved for use in the next incremental update.

[0042] 5. Determine the scope of incremental update data processing;

[0043] By updating the buffer pool, the incremental processing range of the data is determined.

[0044] 6. The entire set of (original) data organization and processing logic;

[0045] According to the incremental processing scope of the data, the entire set of data processing is carried out according to the (original) organizational logic.

[0046] SQL: Structured Query Language (SQL) is a special-purpose programming language. It is a database query and programming language used to access data and query, update and manage relational database systems.

[0047] The multi-table complex logic incremental data update implemented in this embodiment is based on the incremental update of single-table data. Therefore, the incremental update of single-table data is the technical prerequisite for implementing this solution.

[0048] The key point of this embodiment is to establish a set of incremental update data retrieval mechanism. Through the dynamically set incremental benchmark parameters, all processing-related original tables are traversed to retrieve and merge the list of characteristic data elements that need to be updated, so as to determine the scope of incremental processing data; at the same time, data omission is prevented by retroactively setting the incremental benchmark parameters.

[0049] Example 1:

[0050] The short-term insurance policy contract theme project is based on a centralized database and a large amount of data of more than 10 million. Through the incremental processing mode, it exponentially improves the processing efficiency and achieves a data update cycle of minutes.

[0051] Short-term insurance refers to insurance with a term of 1 year or less stipulated in the insurance contract, including accident insurance, school insurance, major illness hospitalization insurance, aviation insurance, etc., which has the characteristics of small amount and large quantity. In recent years, the company has more than 20 million short-term insurance contracts each year, and the cumulative data has reached more than 200 million. At the same time, this topic also involves the interrelated processing of 9 other data tables such as sub-offers, policyholders, and insureds. The data volume of the sub-offer table and the insured table has reached hundreds of millions, and the overall basic data volume is huge.

[0052] According to the traditional stock update mode, all data for the current year must be processed every day. As time goes by, the amount of data processed each time will reach tens of millions. Not only is most of the data processed repeatedly, but the processing window period is also very long, requiring at least 5-6 hours. However, after the newly created incremental processing mode is implemented, only the latest updated data needs to be processed every day, and the processing work can be completed in an average of 20 minutes, achieving an exponential increase in the data update cycle.

[0053] Example 2:

[0054] The new contract customer business analysis project has achieved real-time business support for data through reconstruction of the incremental processing model.

[0055] The new contract stage of insurance refers to a series of process links before the insurance contract takes effect, including customer information registration, premium prepayment, policy write-off, etc. Customer management is the top priority of business operations. Collecting and analyzing customer-related information from the new contract stage can help companies obtain data and information on current company operations in the first place, which can be used to support business decision-making.

[0056] In the past, according to the traditional inventory update model, each processing of this project took dozens of minutes, and the data update cycle was long. Especially during the company's sales nodes and theme activities, it was difficult to meet the front-line sales team's work needs for real-time tracking of premium data and customer service information, and dynamic analysis and decision-making. After the transformation of the newly created incremental processing model, the data processing cycle was successfully and stably improved to less than 5 minutes, and the frequency of information update was sufficient to support the company's sales department's needs for real-time business decision-making.

[0057] Example 3:

[0058] The province-wide premium data situation analysis project has achieved cost reduction and efficiency improvement in premium data review applications through incremental processing model transformation.

[0059] Premiums are the core data information of insurance companies. From provinces, cities, counties, to workplaces, teams, and (salesperson) individuals, they are all daily indicators, so the organization and processing of premium data is a daily essential workflow. At the same time, in the company, premiums involve multiple core systems such as long-term insurance, short-term insurance, universal insurance, annuities, and comprehensive insurance. Ultimately, they need to be implemented and displayed in multiple premium dimension indicators such as long-term insurance, short-term insurance, health insurance, three-year and ten-year insurance, individual insurance, group insurance, and bank insurance.

[0060] According to the traditional stock update model, all premium data for the year must be processed every day. There are many data source channels, huge basic data volume, complex logical calculations, and many processing and circulation links. The processing cycle is generally several hours long. At the same time, in order to ensure that the premium data can be processed normally, a few hours will be set aside every night as an exclusive time period for premium processing. In the fourth quarter of each year, it is necessary to have someone monitor the processing progress at night and handle problems at any time, but it is still impossible to avoid data processing blockages and failures.

[0061] The transformation of the incremental processing mode is very convenient. There is no need to change the original logic. You only need to embed the incremental data pool retrieval and docking implementation logic at the beginning of the processing program. After the implementation of the incremental processing mode, the processing cycle of premium data is greatly shortened, and the pressure on the backend server and database is reduced exponentially, which effectively saves processing resources. There is no problem of data processing blockage and failure, and the effect of reducing costs and increasing efficiency is very significant.

[0062] The present invention and its embodiments are described schematically above, and the description is not restrictive. The drawings show only one embodiment of the present invention, and the actual structure is not limited thereto. Therefore, if a person skilled in the art is inspired by it and designs a structural method and an embodiment similar to the technical solution without creativity without departing from the purpose of the invention, they shall all fall within the protection scope of the present invention.

Claims

1. A pure SQL method for implementing incremental data updates in multi-table complex logic scenarios, characterized by: The following steps are involved:

1. Set the quantitative parameters of the incremental update benchmark; 2. Create an incremental update buffer pool; 3. Organize incremental updates to buffer pool data; 4. Write-back incremental update benchmark quantitative parameters; 5. Determine the scope of incremental update data processing; 6. A complete set of data organization and processing logic.

2. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 1 is characterized in that: In step one, the latest benchmark quantitative parameters are obtained. The benchmark quantitative parameters are the key to realizing incremental update and are used to determine whether the data belongs to the incremental range. The benchmark quantitative parameters include unique serial numbers, time or combined parameters, and a backtracking mechanism is added to realize fault-tolerant processing.

3. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 2 is characterized in that: In step 2, the buffer pool is used to store incremental update index data, including the primary key information field of the processed data and the information fields required in the processing logic.

4. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 3 is characterized in that: In step three, based on the benchmark quantitative parameters, the incremental data in all single tables of the processing logic are retrieved, merged and summarized, and put into the data buffer pool.

5. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 4 is characterized in that: In step 4, the latest incremental update benchmark quantitative parameters are written back and saved for use in the next incremental update.

6. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 5 is characterized in that: In step five, the incremental processing range of the data is determined by updating the buffer pool.

7. The pure SQL method for implementing incremental data update in a multi-table complex logic scenario according to claim 6, characterized in that: In step six, the entire data processing is carried out according to the original organizational logic based on the incremental processing scope of the data.

Images