Historical data storage-oriented configurable filing method and equipment

Abstract

The invention discloses a configurable filing method and equipment for historical data storage. The method comprises the steps of defining database related information and table structure information of a service table and an archiving table, providing addition, deletion, modification and query functions of the database related information and the table structure information, defining a binding relationship between the service table and the archiving table, and providing addition, deletion, modification and query functions of the binding relationship. And configuring an archiving task of each service table, formulating an archiving strategy of each archiving task, performing archiving operation on historical data meeting the archiving strategy in the service table, generating a daily archiving task detail record table, and configuring an interface for perfecting functions and outputting processing so as to query a processing result of archiving service through the interface and perform background display. According to the invention, the business application system does not need to pay attention to related functions of archiving, repeated development is avoided, and manpower cost waste is reduced.

Description

Technical Field

[0001] The present invention relates to the technical field of historical data storage, and particularly to a configurable archiving method and device for historical data storage. Background Art

[0002] With the development of the banking industry, the precipitated data is increasing, such as historical transaction data of customers, historical deposit slip data, historical income data, etc. Different application systems store historical data in different industry fields, and all these information have the requirement of archiving. There are two common practices. One is to define a current table and a historical table to logically distinguish the attribution of information. The other is to define an archiving library to physically distinguish the attribution of information. The normal archiving methods are divided into two types: one is according to the time dimension, for example, for transaction records, determine which information belongs to historical data according to the order time of the customer's transaction behavior and push it to the historical table or historical library; the other is according to the cascade relationship dimension, such as deposit slips and income, where there is a cascade relationship and a subordinate relationship between the data, and the data needs to be archived together according to the logical relationship when archiving. These historical data archiving operations have commonalities, but currently each system repeats the implementation of this function, resulting in duplicate construction and waste of labor costs. Summary of the Invention

[0003] The object of the present invention is to provide a configurable archiving method and device for historical data storage in view of the deficiencies of the existing technology.

[0004] To achieve the above object, the present invention provides a configurable archiving method for historical data storage, including:

[0005] Step 1, define the database-related information and the table structure information of the business table and the archiving table, and provide the functions of adding, deleting, modifying, and querying the database-related information and the table structure information;

[0006] Step 2, define the binding relationship between the business table and the archiving table, and provide the functions of adding, deleting, modifying, and querying the binding relationship;

[0007] Step 3, configure the archiving task for each business table, each archiving task is formed by a business table, and formulate the archiving strategy for each archiving task, and each business table corresponds to one archiving strategy;

[0008] Step 4, perform archiving operations on the historical data in the business table that meets the archiving strategy to transfer and store the historical data that meets the archiving strategy into the corresponding archiving table;

[0009] Step 5, generate a daily archiving task detail record table, and configure a perfect function and an output processing interface to query the processing result of the archiving service and display it in the background through this interface.

[0010] Further, the database-related information includes database identifier, database type, database instance, connection IP, connection port, connection username, and connection password.

[0011] Further, the table structure information includes table name, query SQL, insert SQL, delete SQL, and query SQL condition parameter JSON.

[0012] Further, the binding relationship between the business table and the archival table in step 2 includes binding relationship identifier, business table name, archival table name, business table database identifier, and archival table database identifier.

[0013] Further, the archival task includes task number, business table name, binding relationship identifier, archival strategy, archival days, parent-child table identifier, parent table name, and parent table associated field name. The task number is globally unique and is used to identify the archival strategy of each business table.

[0014] Further, the archival strategy includes single-table archival by time strategy and multi-table archival by cascade relationship strategy;

[0015] The single-table archival by time strategy is as follows:

[0016] Calculate the archival date = current date - archival days according to the defined archival days, and archive the historical data in the business table whose creation time is less than or equal to the archival date;

[0017] The multi-table archival by cascade relationship strategy is as follows:

[0018] Establish a cascade relationship according to the defined parent-child table identifier, the parent table name associated with the child table, and the parent table associated field name;

[0019] Maintain the query SQL and query SQL condition parameter JSON of the child table to support querying child table data according to the parent table associated field;

[0020] The parent table queries all the parent table archival information to be archived according to the archival date, probes data from the child table according to the parent table associated field name, and associates and finds all the child table archival information that meets the conditions. After all the child table data is archived, the parent table data is archived.

[0021] Further, step 4 specifically includes:

[0022] Provide an archival service Http request entry to support the start of the archival service by the business system;

[0023] Query the archival task configuration and poll to process the archival tasks of each business table;

[0024] Define the archive factory service class ArchiveServiceFactory to load all archive strategy implementation services that define the archive annotation @ArchiveStrategyAnno through the factory pattern and based on the Spring event @EventListener;

[0025] Define the abstract archive service class AbstractArchiveService to define the standard process of the archive function through the template method pattern and provide unified methods that are common in archiving at the same time;

[0026] Define subclasses of the archive strategy to provide specific implementations of the archive strategy, including completing the query of business data, the query SQL according to the business, the query SQL condition parameter JSON, querying the archived data range that meets the conditions, inserting archived data, the insert SQL according to the archive table, inserting archived data into the target library and archive table, deleting business data, the delete SQL according to the business table, and deleting the archived archived data.

[0027] Furthermore, the archived task details record table includes an archived task number, an archived task date, and an archived task completion status.

[0028] In a second aspect, the present invention provides a configurable archiving device for historical data storage, including a storage medium and a processor. The storage medium stores a computer program, and when the computer program is executed by the processor, it is used to implement the above method.

[0029] Beneficial effects: 1. By abstracting the commonality of the archiving function and decoupling it from specific business applications, the present invention realizes cohesive componentized functions, enabling the business application system not to need to pay attention to the related functions of archiving, while avoiding repeated development and reducing waste of labor costs;

[0030] 2. The present invention can be accessed in a componentized manner. After the component functions are tested, they can stably provide archiving services. The business system does not need to be repeatedly debugged and tested, is plug-and-play, and can process tens of millions of data after the performance is evaluated through stress testing;

[0031] 3. The present invention solves the diverse implementations caused by different technology selections, is easy to maintain and expand, the components are uniformly upgraded, and can be seamlessly accessed into various business systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a schematic flowchart of a configurable archiving method for historical data storage. DETAILED DESCRIPTION OF THE INVENTION

[0033] The present invention will be further clarified below in conjunction with the accompanying drawings and specific embodiments. These embodiments are implemented on the premise of the technical solution of the present invention. It should be understood that these embodiments are only used to illustrate the present invention and not to limit the scope of the present invention.

[0034] As Figure 1 shown, an embodiment of the present invention provides a configurable archiving method for historical data storage, including:

[0035] Step 1: Define the database-related information and the table structure information of the business table and the archive table, and provide the functions of adding, deleting, modifying, and querying the database-related information and the table structure information. Among them, the database-related information includes database identifier, database type, database instance, connection IP, connection port, connection user name, and connection password, etc. The table structure information includes table name, query SQL, insert SQL, delete SQL, and query SQL condition parameter JSON, etc.

[0036] Step 2: Define the binding relationship between the business table and the archive table, and provide the functions of adding, deleting, modifying, and querying the binding relationship. The binding relationship between the business table and the archive table includes binding relationship identifier, business table name, archive table name, business table database identifier, and archive table database identifier. The business table and the archive table support configuring one-to-one, one-to-many, and many-to-one mapping relationships.

[0037] Step 3: Configure the archiving task for each business table. Each archiving task is formed by a business table, and formulate the archiving strategy for each archiving task. Each business table corresponds to an archiving strategy. The above-mentioned archiving task is created in terms of the dimension of the business table, and specifically includes task number, business table name, binding relationship identifier, archiving strategy, archiving days, parent-child table identifier, parent table name, and parent table associated field name. The above task number is globally unique and is used to identify the archiving strategy of each business table. According to the binding relationship, the target archive table name of the archiving task and the database where the archive table is located can be determined. The archiving strategies of the present application include two categories: single-table archiving according to time strategy and multi-table archiving according to cascade relationship strategy. Among them, the single-table archiving according to time strategy is as follows:

[0038] Calculate the archiving date according to the defined archiving days = current date - archiving days, and archive the historical data in the business table whose information creation time is less than or equal to the archiving date.

[0039] The multi-table archiving according to the cascade relationship strategy is as follows:

[0040] Establish a cascading relationship based on the defined parent-child table identifier, the parent table name associated with the child table, and the parent table association field name. Maintain the query SQL of the child table and the query SQL condition parameter JSON to support querying child table data according to the parent table association field. The parent table queries all the parent table data to be archived based on the archival date, and probes data from the child table according to the parent table association field name, and associates and retrieves all the child table data to be archived that meet the conditions. After all the child table data is archived, the parent table data is archived. The child table data supports recursion until the bottom-level child table data is polled. At the same time, it supports the business system to customize the archival strategy. To customize the archival strategy, it is necessary to first define the archival strategy dictionary and inject the archival strategy annotation in the custom archival strategy implementation class, and the annotation value is the archival strategy dictionary value. The strategy dictionary can be used through background configuration, and the archival service can scan the custom implementation class according to the annotation to complete the business system's custom archival strategy.

[0041] Step 4: Perform an archival operation on the historical data in the business table that meets the archival strategy to transfer and store the historical data that meets the archival strategy into the corresponding archival table. Specifically as follows:

[0042] Provide a RESTful-style HTTP request entry for the archival service to support the business system to schedule the start of the archival service.

[0043] Query the archival task configuration and poll to process the archival tasks of each business table.

[0044] Define the archival factory service class ArchiveServiceFactory to load all the archival strategy implementation services service that define the archival annotation @ArchiveStrategyAnno through the factory pattern and based on the Spring event @EventListener.

[0045] Define the abstract archival strategy service class AbstractArchiveService to define the standard process of the archival function through the template method pattern and provide unified methods that are common in archiving at the same time. Whether the specific strategy is based on time or the cascading relationship ability is provided by the subclass implementation. This abstract method is dealData.

[0046] Define subclasses of the archiving strategy, such as TimeDimensionArchiveService (example: 1 - according to the single-table time strategy), which need to inherit from the parent class AbstractArchiveService, mark the annotation @ArchiveStrategyAnno, and define the dictionary values of the archiving strategy (example: 1 - according to the single-table time strategy; 2 - according to the cascade relationship strategy). Implement the abstract methods getTmpDatapage and dealData to provide the specific implementation of the archiving strategy, including completing the query of business data, the query SQL according to the business, the JSON of the query SQL condition parameters, querying the archiving data range that meets the conditions, inserting the archiving data, the insert SQL according to the archiving table, inserting the archiving data into the target library and the archiving table, deleting the business data, the delete SQL according to the business table, and deleting the archived archiving data. In addition, Spring JDBC's transaction management has been used by default in the same database to ensure data consistency. For cross-database use, a distributed transaction management middleware is used, depending on the middleware already connected to the business system. By default, no distributed transaction management is done and customization and extension are required.

[0047] Step 5: Generate a daily archiving task details record table, configure and improve the functions, and output the processing interface to query the processing results of the archiving service and display them in the background. The above archiving task details record table includes the archiving task number, archiving task date, and archiving task completion status (00 - not processed, 01 - processing, 02 - successful, 03 - failed).

[0048] Illustrative example:

[0049] The following is an example of accessing historical data storage for a certain business system of a bank. Taking the transaction flow table tb_trans_log of the business system as an example, the tb_trans_log of the business system needs to be archived by time into the tb_trans_log_h table in the archiving database OceanBase. The specific content is as follows:

[0050] 1) Define the business system database and related connection information. The business system has multiple sub-databases, a total of 5 databases, which are respectively defined as:

[0051] 1-1) TRANS01, business database 1, mysql, fnc, 121.0.0.1, 3306, root, XXX;

[0052] 1-2) TRANS02, business database 2, mysql, fnc, 121.0.0.2, 3306, root, XXX;

[0053] 1-3) TRANS03, business database 3, mysql, fnc, 121.0.0.3, 3306, root, XXX;

[0054] 1 - 4) TRANS04, Business Database 4, mysql, fnc, 121.0.0.4, 3306, root, XXX;

[0055] 1 - 5) TRANS05, Business Database 5, mysql, fnc, 121.0.0.5, 3306, root, XXX;

[0056] Define the archival database and related connection information. There is only 1 database, defined as:

[0057] 1 - 6) OB01, Archival Database, mysql (distributed database, supporting mysql protocol), fncdb, 121.0.0.6, 2883, fncuser@fnc@obarch, XXX.

[0058] 2) Define the business table with the table name tb_trans_log, and maintain the query SQL, insert SQL, delete SQL, and query SQL condition parameter JSON for this table (example: {"param1": "creat_time <= :transDate", "param2": "trans_status in ('02', '03')"); Define the archival table with the table name tb_trans_log_h, and maintain the query SQL, insert SQL, delete SQL, and query SQL condition parameter JSON for this table.

[0059] 3) Configure the binding relationship between the business table and the archival table:

[0060] The binding relationship between the business table tb_trans_log and the archival table tb_trans_log_h is 5 to 1, specifically:

[0061] 3 - 1) Binding relationship identifier rela001, tb_trans_log, tb_trans_log_h, TRANS01, OB01.

[0062] 3 - 2) Binding relationship identifier rela001, tb_trans_log, tb_trans_log_h, TRANS02, OB01.

[0063] 3 - 3) Binding relationship identifier rela001, tb_trans_log, tb_trans_log_h, TRANS03, OB01.

[0064] 3 - 4) Binding relationship identifier rela001, tb_trans_log, tb_trans_log_h, TRANS04, OB01.

[0065] 3 - 5) Bind relationship identifiers rela001, tb_trans_log, tb_trans_log_h, TRANS05, OB01.

[0066] 4) Configure the archiving task for the business table tb_trans_log:

[0067] The task information is: archiveTask01, tb_trans_log, rela001, 1 (archiving strategy: archive by single - table time), 30 (days, archive data 30 days ago), empty (do not configure whether it is a parent table for single - table time archiving), empty (do not configure the parent table name for single - table time archiving), empty (do not configure the parent - table associated field name for single - table time archiving).

[0068] 5) The archiving service implementation of tb_trans_log is as follows:

[0069] 5 - 1) Define the factory class ArchiveServiceFactory, and load the archiving strategy service Service according to the strategy dictionary. The specific code is as follows:

[0070]

[0071]

[0072]

[0073] 5 - 2) Define the abstract service class AbstractArchiveService, and define the template method dealDataTemplate. The unified processing process is: query business - table data, insert archived - table data, and delete business - table data in these three steps.

[0074] 5 - 3) Define the archiving - strategy annotation ArchiveStrategyAnno, with the default dictionary value being 1 - archive by single - table time, 2 - archive by cascade relationship, and support the business system to expand downwards in sequence. The single - table - time - archiving service subclass TimeDimensionArchiveService inherits from the parent class AbstractArchiveService and marks the annotation ArchiveStrategyAnno's value = "1".

[0075] 5-4) Implement the subclass TimeDimensionArchiveService of the single-table time archiving service. The implementation of the method getTmpDatapage is as follows. Calculate new Date() - 30 = transDate (30 days ago, for example, 2024-11-11) based on 30 days of archiving days.

[0076] Substitute 2024-11-11 into the query SQL. The effect of assembling with the query SQL condition parameter JSON is as follows:

[0077] Select*from tb_trans_log where 1=1and creat_time<=’2024-11-11’andtrans_status in(’02’,’03’);

[0078] According to the above SQL, query all the data with successful and failed status in tb_trans_log on and before 2024-11-11. The data structure is List <map>Execute processing in 5 libraries in sequence according to the binding relationship.

[0079] If the business system accesses MyCat, the binding relationship is maintained as:

[0080] Binding relationship identifiers rela001, tb_trans_log, tb_trans_log_h, MYCAT01, OB01.

[0081] The above SQL only needs to be executed once to obtain all data that meet the conditions and get the List <map>The object is passed to the dealData method.

[0082] 5-4) For the single-table time archiving service subclass TimeDimensionArchiveService, the insertion implementation of the method dealData is as follows. Loop through the List <map>, obtain the single-object Map, that is, the data row to be archived. According to the binding relationship, only 1 OB01 can be queried in the archive library. Obtain the insert SQL of the archive table, and execute the insert statement according to the archived data row Map. The key value in the Map needs to correspond to the value in the insert SQL, which should be noted during configuration.

[0083] 5-4) For the single-table time archiving service subclass TimeDimensionArchiveService, the deletion implementation of the method dealData is as follows. Loop through the List <map>, obtain the single-object Map, i.e., the data rows to be archived. After completing the above insert SQL, obtain the delete SQL of the business table and execute the delete statement according to the data row Map. The key value in the Map needs to correspond to the unique primary key in the delete SQL. Pay attention when configuring. The specific code is as follows:

[0084]

[0085]

[0086] 6) After archiving historical data, provide a perfect function and output processing interface:

[0087] 6-1) After archiving is completed, register the details of the archiving task: archiveTask01, now() (current date), 02 (archiving task status - success).

[0088] 6-2) Provide an interface to support querying the task details, which can be used for background display to facilitate tracking the real-time completion progress of the archiving task and querying the completion result.

[0089] The above are only the preferred embodiments of the present invention. It should be noted that for those of ordinary skill in the art in this technical field, the other parts not specifically described belong to the prior art or common general knowledge. Without departing from the principle of the present invention, several improvements and refinements can be made, and these improvements and refinements should also be regarded as the protection scope of the present invention.< / map> < / map> < / map> < / map>

Claims

1. A configurable archiving method for historical data storage, characterized in that: include: Step 1: define database related information and table structure information of business table and archive table, and provide functions of adding, deleting, modifying and querying the database related information and table structure information; Step 2: define the binding relationship between the business table and the archive table, and provide the functions of adding, deleting, modifying and querying the binding relationship; Step 3: Configure the archiving task of each business table. Each archiving task is formed by a business table. Formulate an archiving strategy for each archiving task. Each business table corresponds to an archiving strategy. Step 4: Perform an archiving operation on the historical data in the business table that meets the archiving strategy, so as to transmit and store the historical data that meets the archiving strategy into the corresponding archiving table; Step 5: Generate a daily archive task detail record table, and configure the interface for perfecting functions and output processing, so as to query the processing results and background display of the archive service through this interface.

2. A configurable archiving method for historical data storage according to claim 1, characterized in that: The database related information includes database identification, database type, database instance, connection IP, connection port, connection user name and connection password.

3. A configurable archiving method for historical data storage according to claim 1, characterized in that: The table structure information includes table name, query SQL, insert SQL, delete SQL and query SQL condition parameter JSON.

4. A configurable archiving method for historical data storage according to claim 1, characterized in that: The binding relationship between the business table and the archive table in step 2 includes a binding relationship identifier, a business table name, an archive table name, a business table database identifier, and an archive table database identifier.

5. A configurable archiving method for historical data storage according to claim 1, characterized in that: The archiving task includes a task number, a business table name, a binding relationship identifier, an archiving strategy, archiving days, a parent-child table identifier, a parent table name, and a parent table associated field name. The task number is globally unique and is used to identify the archiving strategy of each business table.

6. A configurable archiving method for historical data storage according to claim 5, characterized in that: The archiving strategy includes archiving a single table according to a time strategy and archiving multiple tables according to a cascading relationship strategy; The single table is archived according to the time strategy as follows: Calculate the archiving date = current date - archiving days according to the defined archiving days, and archive the historical data in the business table whose information creation time is less than or equal to the archiving date; The multiple tables are archived according to the cascading relationship strategy as follows: Establish a cascading relationship based on the defined parent-child table identifier, the parent table name associated with the child table, and the parent table associated field name; Maintain the child table's query SQL and query SQL condition parameter JSON to support querying child table data based on the parent table's associated fields; The parent table queries all the information to be archived in the parent table based on the archiving date, and explores the data in the child table based on the associated field name of the parent table, and associates all the child table data to be archived that meets the conditions. After all the child table data is archived, the parent table data is archived.

7. A configurable archiving method for historical data storage according to claim 1, characterized in that: The step 4 specifically includes: Provides an Http request entry for the archiving service and supports the startup of the business system scheduling archiving service; Query the archiving task configuration and poll and process the archiving tasks of each business table; Define the archive factory service class ArchiveServiceFactory to load all archive strategies defined by the archive annotation @ArchiveStrategyAnno through the factory mode and according to the Spring event @EventListener to implement the service service; Define the archiving strategy abstract service class AbstractArchiveService to define the standard process of archiving functions through the template method mode, and provide a unified method with universality in archiving; Define archiving strategy subclasses to provide specific implementations of archiving strategies, including completing business data queries, querying SQL based on business, querying SQL condition parameter JSON, querying the archived data range that meets the conditions, inserting archived data, inserting SQL based on the archive table, inserting archived data into the target library and archive table, deleting business data, deleting SQL based on the business table, and deleting archived data.

8. A configurable archiving method for historical data storage according to claim 1, characterized in that: The filing task detail record table includes the filing task number, the filing task date and the filing task completion status.

9. A configurable archiving device for storing historical data, comprising a storage medium and a processor, wherein the storage medium stores a computer program, characterized in that: When the computer program is executed by a processor, it is used to implement the method according to any one of claims 1 to 8.

Images