Data lifecycle management method and device

Abstract

An embodiment of the present application provides a data lifecycle management method and device, the method comprising: when checking that the data fields and index attributes of a business table, an intermediate table, a local history table, and a history library history table are consistent and it is determined that there is no data in the local history table, obtaining the data to be migrated in the business table; migrating the data to be migrated in the business table to the local history table, and determining the corresponding history library history table according to the time field of the data to be migrated; migrating the data to be migrated in the local history table to the history library history table; the present application can effectively improve the efficiency and accuracy of data cleaning.

Description

Technical Field

[0001] The present application relates to the field of data processing and can also be used in the financial field, and specifically relates to a data lifecycle management method and device. Background Art

[0002] During the system's online operation, as time goes by, business promotion and user growth increase, the business data that needs to be stored also increases. Usually this part of the data is stored in the database, and the core system is mostly a relational database. As the amount of data continues to grow, the performance of the relational database will continue to decline, and users will feel that the system is getting slower and slower. At the same time, the disks that need to store data will become larger and larger, so the database server needs to be continuously expanded. Not only the disk needs to be expanded, but also the CPU and memory resources need to be expanded to meet the requirements for normal operation of the system. However, there is an upper limit to the expansion of a single server. When it exceeds a certain scale, it can no longer be expanded. At this time, the system will face great danger.

[0003] The inventor found that in the prior art, some system data cleaning must be executed through the Delete command, and it is impossible to perform daily table division. The characteristic of this part of data is that only part of the data of the specified date can be deleted according to the status code. This type of data table needs to synchronously transform the application system to create a sub-table. Some system transformations are very difficult and costly. Therefore, this type of data table will not perform the operation of creating a sub-table on a daily basis, but will use the Delete method to delete the data of the specified time based on the date. In this case, since Delete will occupy a large amount of server resources, the ability of the database to provide services will be affected at this time, so this type of operation is usually chosen to be performed during the off-peak period of business, that is, in the early morning. This type of data also needs to be backed up before deletion, the data needs to be exported, transferred to the archive database server, and the import operation needs to be performed. After the import is completed, the data volume needs to be checked to ensure that the data volume on both sides is consistent before the deletion operation can be performed. The whole process requires manual participation. With the increase of operation and maintenance systems, the personnel cost is also increasing, and the efficiency is extremely low. Summary of the invention

[0004] In response to the problems in the prior art, the present application provides a data lifecycle management method and device, which can effectively improve the efficiency and accuracy of data cleaning.

[0005] In order to solve at least one of the above problems, the present application provides the following technical solutions:

[0006] In a first aspect, the present application provides a data lifecycle management method, comprising:

[0007] When the data fields and index attributes of the business table, the intermediate table, the local history table, and the history table of the history library are checked to be consistent and it is determined that there is no data in the local history table, the data to be migrated in the business table is obtained;

[0008] Migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated;

[0009] Migrate the data to be migrated in the local history table to the history table of the history library.

[0010] Further, after checking that the data fields and index attributes of the business table, the intermediate and local history tables, and the history library history table are consistent, the following steps are performed:

[0011] Determine whether the business table is a partition table;

[0012] If so, it is determined whether there is historical data in the partition table according to the partition field and migration date of the partition table. If so, the historical data is migrated to the history table of the history library.

[0013] Further, the obtaining the data to be migrated in the business table includes:

[0014] Determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated;

[0015] The data volume of the data to be migrated is determined according to the partition name, and the data to be migrated is acquired.

[0016] Further, the migrating the data to be migrated in the business table to the local history table includes:

[0017] According to a preset partition exchange command, the partition table containing the data to be migrated is exchanged with the intermediate table to migrate the data to be migrated to the intermediate table, and the partition table is deleted;

[0018] The data to be migrated is migrated to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

[0019] In a second aspect, the present application provides a data lifecycle management device, including:

[0020] The module for acquiring data to be migrated is used to acquire the data to be migrated in the business table when checking that the data fields and index attributes of the business table, the intermediate table, the local history table and the history table of the history library are consistent and it is determined that there is no data in the local history table;

[0021] A local migration module, used to migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated;

[0022] The history library migration module is used to migrate the data to be migrated in the local history table to the history library history table.

[0023] Furthermore, the module for acquiring data to be migrated further includes:

[0024] A partition table determination unit, used to determine whether the service table is a partition table;

[0025] The partition historical data processing unit is used to determine whether there is historical data in the partition table according to the partition field and migration date of the partition table if the business table is determined to be a partition table, and if so, migrate the historical data to the historical table of the history library.

[0026] Furthermore, the module for acquiring data to be migrated further includes:

[0027] a partition name determination unit, configured to determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated;

[0028] The partition data to be migrated determining unit is used to determine the data volume of the data to be migrated according to the partition name and obtain the data to be migrated.

[0029] Furthermore, the local migration module includes:

[0030] The intermediate table exchange unit is used to migrate the data to be migrated to the intermediate table by performing a partition exchange operation on the partition table containing the data to be migrated and the intermediate table according to a preset partition exchange command, and delete the partition table;

[0031] The local history table exchange unit is used to migrate the to-be-migrated data to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

[0032] In a third aspect, the present application provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the data lifecycle management method when executing the program.

[0033] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the steps of the data lifecycle management method when executed by a processor.

[0034] It can be seen from the above technical solution that the present application provides a data lifecycle management method and device, by checking that the data fields and index attributes of the business table, intermediate table, local history table and history library history table are consistent and it is determined that there is no data in the local history table, obtaining the data to be migrated in the business table, migrating the data to be migrated in the business table to the local history table, and determining the corresponding history library history table according to the time field of the data to be migrated, migrating the data to be migrated in the local history table to the history library history table, thereby automatically migrating data that is no longer used from the business database to the history database, which can usually keep the database disk usage stable and save the disk space required for backup. When the data volume is constant, the CPU and memory resources required by the database are usually relatively stable. At the same time, it also avoids performance problems caused by the increase in data volume, and can effectively improve the efficiency and accuracy of data cleaning. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0036] Figure 1 This is one of the flow charts of the data lifecycle management method in the embodiment of the present application;

[0037] Figure 2 This is the second flow chart of the data lifecycle management method in the embodiment of the present application;

[0038] Figure 3 This is the third flow chart of the data lifecycle management method in the embodiment of the present application;

[0039] Figure 4 This is a fourth flowchart of the data lifecycle management method in an embodiment of the present application;

[0040] Figure 5 This is one of the structural diagrams of the data lifecycle management device in the embodiment of the present application;

[0041] Figure 6 This is the second structural diagram of the data lifecycle management device in the embodiment of the present application;

[0042] Figure 7 This is the third structural diagram of the data lifecycle management device in the embodiment of the present application;

[0043] Figure 8This is the fourth structural diagram of the data lifecycle management device in the embodiment of the present application;

[0044] Fig. 9 It is a schematic diagram of the structure of an electronic device in an embodiment of the present application. DETAILED DESCRIPTION

[0045] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0046] Considering that in the prior art, some system data cleaning must be performed through the Delete command and cannot be performed by day, the characteristic of this part of data is that only part of the data on the specified date can be deleted according to the status code. The creation of sub-tables for this type of data table requires the simultaneous transformation of the application system. Some system transformations are very difficult and costly. Therefore, this type of data table will not perform the operation of creating sub-tables on a daily basis, but will use the Delete method to delete the data at the specified time based on the date. In this case, since Delete will occupy a large amount of server resources, the ability of the database to provide services will be affected at this time, so this type of operation is usually chosen to be performed during the off-peak period of business, that is, in the early morning. This type of data also needs to be backed up before deletion, the data needs to be exported, transferred to the archive database server, and the import operation needs to be performed. After the import is completed, the data volume needs to be checked to ensure that the data volume on both sides is consistent before the deletion operation can be performed. The whole process requires manual participation. With the increase of operation and maintenance systems, the personnel cost is also getting higher and higher, and the efficiency is extremely low. The present application provides a data lifecycle management method and device. By checking that the data fields and index attributes of the business table, the intermediate table, the local history table and the history table of the history library are consistent and it is judged that there is no data in the local history table, the data to be migrated in the business table is obtained, and the data to be migrated in the business table is migrated to the local history table. The corresponding history library history table is determined according to the time field of the data to be migrated, and the data to be migrated in the local history table is migrated to the history library history table, which can effectively improve the efficiency and accuracy of data cleaning.

[0047] In order to effectively improve the efficiency and accuracy of data cleaning, this application provides an embodiment of a data lifecycle management method, see Figure 1 The data lifecycle management method specifically includes the following contents:

[0048] Step S101: when checking that the data fields and index attributes of the business table, the intermediate table, the local history table, and the history table in the history library are consistent and it is determined that there is no data in the local history table, the data to be migrated in the business table is obtained.

[0049] Optionally, the main program code of this application is encapsulated into the dba_rotate_main_prc stored procedure, and the data governance program will be executed from this. When the main program is executed, the subprograms will be called in sequence, and the subprograms are also encapsulated into the stored procedure. The advantage of this is that it is easy to debug and easy to judge and solve problems when they occur. And when making modifications, it can also ensure that the impact is only controlled in a single stored procedure.

[0050] Optional, the subroutines called in sequence are:

[0051] (1)dba_rotate_compare_prc: Checks the business table, intermediate table fields and attribute information, and repairs any inconsistencies.

[0052] (2) dba_rotate_diff_prc: Checks the fields and index information of the business table, local history table, and history table in the history library. Only after they are confirmed to be consistent can the following process be continued.

[0053] Optionally, this application can call the dba_rotate_precheck_normal_prc stored procedure, which is used to check whether the local history table data is 0. If it is 0, the number of rows of this data migration is counted, otherwise the program terminates. First, use the Count command to check whether the amount of data in the local history table is 0. If it is not 0, it means that the last data governance program failed to run, and there is unfinished data. Manual intervention is required to synchronize the data to the history table of the history library. If the statistical data is 0, the amount of data that needs to be migrated on that day is counted through the Count and time fields, and the data amount is passed to the next process as a parameter.

[0054] In some embodiments of the present application, when starting the dba_rotate_main_prc task, the dba_rotate_compare_prc stored procedure needs to be called first. This stored procedure mainly checks whether the fields and attribute information of the partition table and the history table are consistent. By querying the database data dictionary sys.dba_tab_columns, the field and attribute information of the business table and the local history table can be obtained. If the information is consistent, the execution of this process ends and the program will enter the next process. If the information is inconsistent, the repair module of this process is performed.

[0055] Optionally, the repair module will first create a copy of the business table and the local history table. The copy is consistent with the original table except for the table name. Then the dbms_metadata_diff.compare_alter function is used for comparison to generate an SQL command to repair the field inconsistency. The command will first be executed on the copy of the history table. If the execution is successful, the table name of the local history table copy in the repair SQL will be replaced with the table name of the local history table, the intermediate table, and the history table of the history library. Then the command will be executed in the business library and the history library in turn, and finally ensure that the fields and attributes of the business table are consistent with the intermediate table, the local history table, and the history library history table.

[0056] In some embodiments of the present application, the present application may also call the dba_rotate_diff_prc stored procedure, which is mainly used to check whether the fields and index information of the business table, local history table, and history library history table are consistent. If the business table has an intermediate table and it is a partitioned table, it is necessary to check whether the fields and attribute information of the business table and the intermediate table are consistent. The field information of the business table and the intermediate table can be queried through the data dictionary dba_tab_cols. The same method is used to check whether the fields and attributes of the business table and the local history table are consistent. Finally, check whether the fields and attributes of the business table and the history library history table are consistent.

[0057] It should be noted here that since the history database and the business database are deployed on different servers, when querying the field information of the history table of the history database, it is necessary to perform the query operation through dblink. Next, the index information will be checked. First, the business table and the intermediate table index information will be checked to see if they are consistent. This can be confirmed by querying the data dictionary dba_ind_columns and dba_indexes. Then use the same method to check whether the business table and the local history table index information are consistent. The history database history table index information does not need to be checked, because the partition table is implemented through partition exchange when cleaning data, and the data is imported into the history database through the Insert command. The principles are different, so the history database does not require the index to be consistent with the business database. If the field and index information are consistent, proceed to the next step.

[0058] Step S102: Migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated.

[0059] Optionally, this application can call the dba_rotate_normal_prc stored procedure, which is used to insert the data that needs to be migrated from the business table into the local history table through the Insert command, and then delete the migrated data from the business table. First, confirm the data that needs to be migrated based on the time field. After executing the spliced ​​Insert command, the data will be inserted into the local history table. At this time, the amount of inserted data can be recorded by the SQL%ROWCOUNT function. If this amount of data is consistent with the amount of data migration for the day counted in the previous step, delete this part of the migrated data in the business table, and record the amount of deleted data through the SQL%ROWCOUNT function. If the amount of deleted data is consistent with the amount of migrated data, commit the transaction. Otherwise, roll back the transaction and terminate the program.

[0060] Step S103: Migrate the data to be migrated in the local history table to the history table in the history library.

[0061] Optionally, this application can call the dba_rotate_dblink_prc stored procedure, which generates a DBLINK based on the migration date, so that the program can automatically identify which historical repository to insert the data into. Due to disk capacity limitations, each year's data is stored in a different historical repository, so it is required to dynamically generate the corresponding historical repository dblink based on the time field of the data to be migrated. This process will be judged based on ordinary tables and partitioned tables. If it is an ordinary table, the year information is extracted based on the time field in the data. The created dblink naming format is: MIGRATE_LINK_YYYY. For example, if the time field of the data to be migrated is 2016, the created dblink is MIGRATE_LINK_2016. In this way, data from different dates will be automatically archived into different historical repositories. The method of creating dblinks for partitioned tables will be explained later when introducing the partition table process.

[0062] Optionally, this application can call the dba_rotate_hist_prc stored procedure, which is responsible for migrating the data in the local history table to the history table in the history library. First, insert the data into the history table in the history library through the Insert and dblink commands, and record the amount of inserted data through the SQL%ROWCOUNT function. If this amount of data is consistent with the amount of data that needs to be migrated on that day, clear the data in the local history table through the Truncate command. So far, the data governance process for ordinary tables has ended, and the data has been migrated from the business table through the local history table and finally to the history table in the history library.

[0063] From the above description, it can be seen that the data lifecycle management method provided in the embodiment of the present application can obtain the data to be migrated in the business table when checking that the data fields and index attributes of the business table, intermediate table, local history table and history library history table are consistent and it is determined that there is no data in the local history table, migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated, and migrate the data to be migrated in the local history table to the history library history table, so as to effectively improve the efficiency and accuracy of data cleaning.

[0064] In order to accurately distinguish the partition table, in one embodiment of the data lifecycle management method of the present application, see Figure 2 , the above step S101 may also specifically include the following contents:

[0065] Step S201: Determine whether the service table is a partition table.

[0066] Step S202: If yes, determine whether there is historical data in the partition table according to the partition field and migration date of the partition table; if yes, migrate the historical data to the history table of the history library.

[0067] Optionally, this application can distinguish whether the business table belongs to an ordinary table or a partitioned table. First, we introduce the calling process of ordinary tables. The data governance process of ordinary tables is very different from that of partitioned tables. The main technology of partitioned tables adopts partition exchange. The advantage of this technology is that it only needs to modify the data dictionary and does not consume server CPU and IO resources. Ordinary tables use Delete and Insert methods to migrate and delete data. This method consumes a lot of server resources, so it is usually recommended to modify ordinary tables to partitioned tables. However, some business modules do not have a downtime window, or the table belongs to the core table and there is no suitable field to be modified to a partitioned table. New partition fields need to be added, so there will be a lot of verification steps. For the stable operation of the business, such ordinary tables will usually not be modified to partitioned tables.

[0068] Optionally, if the business table to be migrated belongs to a partitioned table, you must first call the dba_rotate_par_rows_check_prc stored procedure, which is used to check whether there is historical data in the partitioned table. The historical data here refers to the situation where data exists in the partition that has been migrated. Usually, this kind of situation is caused by a business failure. At this time, the business re-runs the batch data, resulting in the insertion of data. In this case, it is usually necessary to confirm with the development and manually migrate this part of the data to the historical table of the historical library. At the beginning of this stored procedure task, you need to confirm the number of partition fields by querying the data dictionary dba_part_key_columns. If the partition field is not equal to 1, an error is reported. If it is 1, the name of the partition field is recorded. By using the partition field and the migration date, you can confirm whether there is data in the historical partition of the business table. If there is no data, proceed to the next process.

[0069] In order to accurately determine the data to be migrated in the partition table, in one embodiment of the data lifecycle management method of the present application, see Figure 3 , the above step S101 may also specifically include the following contents:

[0070] Step S301: determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated.

[0071] Step S302: determining the data volume of the data to be migrated according to the partition name and acquiring the data to be migrated.

[0072] Optionally, this application can call the dba_rotate_precheck_par_prc stored procedure, which is used to check whether the amount of data in the intermediate table and the local history table is 0. If it is 0, the number of migrations today is counted, otherwise the data governance program terminates. First, the Count command is used to count the amount of data in the intermediate table and the local history table. If both are 0, the High_value of the partition is found through the time field of the data to be migrated. The partition name can be determined by the High_value. Finally, the partition name is used to count the amount of data that needs to be migrated today, and this information is passed to the next step through parameters.

[0073] In order to accurately migrate data to the partition table, in one embodiment of the data lifecycle management method of the present application, see Figure 4 , the above step S102 may also specifically include the following contents:

[0074] Step S401: according to a preset partition exchange command, the partition table containing the data to be migrated is exchanged with the intermediate table to migrate the data to be migrated to the intermediate table, and the partition table is deleted.

[0075] Step S402: Migrate the data to be migrated to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

[0076] Optionally, this application can call the dba_rotate_par_prc stored procedure, which is responsible for migrating data from the current partition to the intermediate table, and then to the local historical table. First, use the partition exchange command to exchange the partition containing the day's migration data with the intermediate table. At this time, the data is exchanged from the partition table to the intermediate table. Then exchange the partitions of the intermediate table with the local historical table, and the data is migrated from the intermediate table to the local historical table. Partition exchange takes a very short time and only requires modifying the data dictionary, so it is extremely efficient. However, it should be noted that if there are global indexes in the partition table, it is necessary to specify the 'INCLUDING INDEXES without validation updateglobal indexes' parameter when executing the partition exchange. This parameter can update the global index information after the partition exchange is executed to ensure that the global index is valid. Finally, the amount of data in the local historical table is counted to ensure that it is consistent with the amount of data counted before the migration. If they are consistent, proceed to the next step, otherwise the data governance program terminates.

[0077] Optionally, this application can call the dba_rotate_dblink_prc stored procedure, which generates a DBLINK based on the migration date. Here we only introduce the process of the partition table. Different from the ordinary table, here we extract the year information based on the time field of the migration partition and dynamically generate a dblink.

[0078] Optionally, this application can call the dba_rotate_hist_prc stored procedure, which is responsible for migrating the data in the local history table to the history table in the history library. First, insert the data into the history table in the history library through the Insert and dblink commands, and record the amount of data inserted through the SQL%ROWCOUNT function. If this amount of data is consistent with the amount of data that needs to be migrated on the same day, clear the data in the local history table through the Truncate command.

[0079] Optionally, this application can call the dba_rotate_auto_drop_prc stored procedure, which is used to delete empty partitions whose data has been migrated, and only delete empty partitions that have exceeded the specified time. First, you need to check whether the data of the partition to be deleted is 0. If it is 0, delete this partition through the drop command. When deleting, you need to use the 'update globalindexes' parameter to avoid global index failure. The local history table also deletes empty partitions in this way. So far, the data governance process of the partitioned table has been completed.

[0080] In order to effectively improve the efficiency and accuracy of data cleaning, the present application provides an embodiment of a data lifecycle management device for implementing all or part of the content of the data lifecycle management method, see Figure 5 , the data lifecycle management device specifically includes the following contents:

[0081] The module 10 for obtaining data to be migrated is used to obtain the data to be migrated in the business table when checking that the data fields and index attributes of the business table, the intermediate table, the local history table and the history table in the history library are consistent and it is determined that there is no data in the local history table.

[0082] The local migration module 20 is used to migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated.

[0083] The history library migration module 30 is used to migrate the data to be migrated in the local history table to the history library history table.

[0084] From the above description, it can be seen that the data lifecycle management device provided in the embodiment of the present application can obtain the data to be migrated in the business table by checking that the data fields and index attributes of the business table, the intermediate table, the local history table and the history library history table are consistent and it is determined that there is no data in the local history table. The data to be migrated in the business table is migrated to the local history table, and the corresponding history library history table is determined according to the time field of the data to be migrated. The data to be migrated in the local history table is migrated to the history library history table, which can effectively improve the efficiency and accuracy of data cleaning.

[0085] In order to accurately distinguish the partition table, in one embodiment of the data lifecycle management device of the present application, see Figure 6 , the to-be-migrated data acquisition module 10 further includes:

[0086] The partition table determination unit 11 is used to determine whether the service table is a partition table.

[0087] The partition historical data processing unit 12 is used to determine whether there is historical data in the partition table according to the partition field and migration date of the partition table if the business table is determined to be a partition table, and if so, migrate the historical data to the historical table of the history library.

[0088] In order to accurately determine the data to be migrated in the partition table, in one embodiment of the data lifecycle management device of the present application, see Figure 7 , the to-be-migrated data acquisition module 10 further includes:

[0089] The partition name determination unit 13 is used to determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated.

[0090] The partition data to be migrated determining unit 14 is used to determine the data volume of the data to be migrated according to the partition name and obtain the data to be migrated.

[0091] In order to accurately migrate data on the partition table, in one embodiment of the data lifecycle management device of the present application, see Figure 8 , the local migration module 20 includes:

[0092] The intermediate table exchange unit 21 is used to migrate the data to be migrated to the intermediate table by performing a partition exchange operation on the partition table containing the data to be migrated and the intermediate table according to a preset partition exchange command, and delete the partition table.

[0093] The local history table exchange unit 22 is used to migrate the to-be-migrated data to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

[0094] In order to further illustrate the present solution, the present application also provides a specific application example of using the above-mentioned data lifecycle management device to implement the data lifecycle management method, which specifically includes the following contents:

[0095] Step 1: First, you need to create an error code. When the data governance program is abnormal, you can quickly locate and fix the problem through the error code in the log. The corresponding information of the error code is as follows:

[0096] --20001 The field attributes of the business table and the intermediate table are inconsistent

[0097] --20002 The field attributes of the business table and the local history table are inconsistent

[0098] --20003 The field attributes of the business table and the history table in the history library are inconsistent

[0099] --20004 The index field attributes of the business table and the intermediate table are inconsistent

[0100] --20005 The index field attributes of the business table and the local history table are inconsistent

[0101] --20006 The local history table is not empty

[0102] --20007 local history table and intermediate table are not empty

[0103] --20008 The deleted data in the business table is inconsistent with the migration data on that day

[0104] --20009 The inserted data of the local history table of the common table is inconsistent with the migrated data

[0105] --20010 partition table local history table insert data is inconsistent with migration data

[0106] --20011 Configuration table update failed

[0107] --20012 The inserted data in the history table of the history library is inconsistent with the migrated data

[0108] --20013 The history partition of the local history table to be deleted is not empty

[0109] --20014 The historical partition of the business table to be deleted is not empty

[0110] --20015 The table type is neither a normal table nor a partition table

[0111] --20016 If current_data_date in the configuration table is greater than target_data_date, an alarm is issued

[0112] --20017 Check if there is data less than current_data_date

[0113] --20018 partition table has multiple partition fields

[0114] Step 2: Encapsulate the main program code into the dba_rotate_main_prc stored procedure. The data governance program will start from here. When the main program is executed, it will call the subprograms in sequence. The subprograms are also encapsulated into the stored procedure. The advantage of this is that it is easy to debug and easy to judge and solve problems when they occur. And when making modifications, it can also ensure that the impact is only controlled in a single stored procedure.

[0115] The subroutines called in sequence are:

[0116] 2.1dba_rotate_compare_prc: Check the business table, intermediate table fields and attribute information, and repair them if they are inconsistent.

[0117] 2.2dba_rotate_diff_prc: Check the fields and index information of the business table, local history table, and history library history table. Only after they are confirmed to be consistent can the following process be continued.

[0118] Ordinary table calling process:

[0119] 2.3dba_rotate_precheck_normal_prc: Checks whether the amount of data in the local history table is zero. If it is zero, returns the number of rows migrated this time, otherwise the program terminates.

[0120] 2.4dba_rotate_normal_prc: Migrate data from the business table to the local history table.

[0121] 2.5dba_rotate_dblink_prc: Generate DBLINK based on the migration date.

[0122] 2.6dba_rotate_hist_prc: Migrate data from the local history table to the history library history table.

[0123] Partition table calling process:

[0124] 2.3dba_rotate_par_rows_check_prc: Checks whether historical data has value.

[0125] 2.4dba_rotate_precheck_par_prc: Checks whether the data volume of the intermediate table and local history table is zero. If it is zero, returns the number of rows in this migration partition, otherwise the program terminates.

[0126] 2.5dba_rotate_par_prc: First migrate the data from the partition of the business table to the intermediate table, and then migrate it to the local history table.

[0127] 2.6dba_rotate_dblink_prc: Generates DBLINK based on the migration date.

[0128] 2.7dba_rotate_hist_prc: Migrate data from the local history table to the history library history table.

[0129] 2.8dba_rotate_auto_drop_prc: Deletes empty partitions of the business table N days ago. N needs to be determined based on the business's data retention period.

[0130] Step 3: When starting the dba_rotate_main_prc task, you first need to call the dba_rotate_compare_prc stored procedure. This stored procedure mainly checks whether the fields and attribute information of the partition table and the history table are consistent. By querying the database data dictionary sys.dba_tab_columns, you can get the field and attribute information of the business table and the local history table. If the information is consistent, this process is completed and the program will enter the next process. If the information is inconsistent, the repair module of this process is performed.

[0131] The repair module will first create a copy of the business table and the local history table. The copy is consistent with the original table except for the table name. Then the dbms_metadata_diff.compare_alter function is used for comparison to generate an SQL command to repair the inconsistent fields. The command will first be executed on the copy of the history table. If the execution is successful, the table name of the local history table copy in the repair SQL will be replaced with the table name of the local history table, the intermediate table, and the history table of the history library. Then the command will be executed in the business library and the history library in turn, and finally ensure that the fields and attributes of the business table are consistent with the intermediate table, the local history table, and the history library history table.

[0132] Step 4, call the dba_rotate_diff_prc stored procedure, which is mainly used to check whether the fields and index information of the business table, local history table, and history library history table are consistent. If the business table has an intermediate table and is a partitioned table, you need to check whether the field and attribute information of the business table and the intermediate table are consistent. You can query the field information of the business table and the intermediate table through the data dictionary dba_tab_cols. Use the same method to check whether the fields and attributes of the business table and the local history table are consistent. Finally, check whether the fields and attributes of the business table and the history library history table are consistent.

[0133] It should be noted here that since the history database and the business database are deployed on different servers, when querying the field information of the history table of the history database, it is necessary to perform the query operation through dblink. Next, the index information will be checked. First, the business table and the intermediate table index information will be checked to see if they are consistent. This can be confirmed by querying the data dictionary dba_ind_columns and dba_indexes. Then use the same method to check whether the business table and the local history table index information are consistent. The history database history table index information does not need to be checked, because the partition table is implemented through partition exchange when cleaning data, and the data is imported into the history database through the Insert command. The principles are different, so the history database does not require the index to be consistent with the business database. If the field and index information are consistent, proceed to the next step.

[0134] Step 5: This step will distinguish whether the business table belongs to a common table or a partitioned table. First, we will introduce the calling process of a common table. The data governance process of a common table is very different from that of a partitioned table. The main technology of a partitioned table uses partition exchange. The advantage of this technology is that it only requires modifying the data dictionary and does not consume server CPU and IO resources. Common tables use the Delete and Insert methods to migrate and delete data. This method consumes a lot of server resources, so it is usually recommended to modify a common table to a partitioned table. However, some business modules do not have a downtime window, or the table is a core table that has no suitable fields when modified to a partitioned table, and a new partition field needs to be added. Therefore, there will be many steps required for verification. In order to ensure the stable operation of the business, this type of common table will usually not be modified to a partitioned table. The calling process of a common table will be introduced below.

[0135] Step 6, call the dba_rotate_precheck_normal_prc stored procedure, which is used to check whether the local history table data is 0. If it is 0, the number of rows of this data migration is counted, otherwise the program terminates. First, use the Count command to check whether the amount of data in the local history table is 0. If it is not 0, it means that the last data governance program failed to run, there is unfinished data, and manual intervention is required to synchronize the data to the history table of the history library. If the statistical data is 0, the amount of data that needs to be migrated on that day is counted through the Count and time fields, and the data amount is passed to the next process as a parameter.

[0136] Step 7, call the dba_rotate_normal_prc stored procedure, which is used to insert the data that needs to be migrated from the business table into the local history table through the Insert command, and then delete the migrated data from the business table. First, confirm the data that needs to be migrated based on the time field. After executing the spliced ​​Insert command, the data will be inserted into the local history table. At this time, the SQL%ROWCOUNT function can be used to record the amount of inserted data. If this amount of data is consistent with the amount of data migration on the day counted in the previous step, delete this part of the migrated data in the business table, and record the amount of deleted data through the SQL%ROWCOUNT function. If the amount of deleted data is consistent with the amount of migrated data, commit the transaction. Otherwise, roll back the transaction and terminate the program.

[0137] Step 8, call the dba_rotate_dblink_prc stored procedure, which generates a DBLINK based on the migration date, so that the program can automatically identify which historical database to insert the data into. Due to disk capacity limitations, each year's data is stored in a different historical database, so it is required to dynamically generate the corresponding historical database dblink based on the time field of the data to be migrated. This process will be judged based on ordinary tables and partitioned tables. If it is an ordinary table, the year information is extracted based on the time field in the data. The created dblink naming format is: MIGRATE_LINK_YYYY. For example, if the time field of the data to be migrated is 2016, the created dblink is MIGRATE_LINK_2016. In this way, data of different dates will be automatically archived into different historical databases. The method of creating dblinks for partitioned tables will be explained later when introducing the partition table process.

[0138] Step 9, call the dba_rotate_hist_prc stored procedure, which is responsible for migrating the data of the local history table to the history table of the history library. First, insert the data into the history table of the history library through the Insert and dblink commands, and record the amount of inserted data through the SQL%ROWCOUNT function. If this amount of data is consistent with the amount of data that needs to be migrated on that day, clear the data in the local history table through the Truncate command. So far, the data governance process of the ordinary table has ended, and the data has been migrated from the business table to the local history table and finally to the history table of the history library. Next, the migration process of the partition table will be introduced.

[0139] Step 10: If the business table to be migrated belongs to a partitioned table, the following process needs to be followed from step 5 onwards. First, the dba_rotate_par_rows_check_prc stored procedure needs to be called. This stored procedure is used to check whether there is historical data in the partitioned table. The historical data here refers to the situation where data exists in the partition that has been migrated. Usually, this kind of situation is caused by a business failure. At this time, the business re-runs the batch data, resulting in the insertion of data. In this case, it is usually necessary to confirm with the development and manually migrate this part of the data to the historical table of the historical library. At the beginning of this stored procedure task, it is necessary to confirm the number of partition fields by querying the data dictionary dba_part_key_columns. If the partition field is not equal to 1, an error is reported. If it is 1, the name of the partition field is recorded. By using the partition field and the migration date, it can be confirmed whether there is data in the historical partition of the business table. If there is no data, proceed to the next process.

[0140] Step 11, call the dba_rotate_precheck_par_prc stored procedure, which is used to check whether the amount of data in the intermediate table and the local history table is 0. If it is 0, count the number of migrations today, otherwise the data governance program terminates. First, use the Count command to count the amount of data in the intermediate table and the local history table. If both are 0, find the High_value of the partition through the time field of the data to be migrated, and determine the partition name through the High_value. Finally, use the partition name to count the amount of data that needs to be migrated today, and pass this information to the next step through parameters.

[0141] Step 12, call the dba_rotate_par_prc stored procedure, which is responsible for migrating data from the current partition to the intermediate table, and then to the local historical table. First, use the partition exchange command to exchange the partition containing the day's migration data with the intermediate table. At this time, the data is exchanged from the partition table to the intermediate table. Then, exchange the partitions of the intermediate table with the local historical table, and the data is migrated from the intermediate table to the local historical table. Partition exchange takes a very short time and only requires modifying the data dictionary, so it is very efficient. However, it should be noted that if the partition table has a global index, it is necessary to specify the 'INCLUDING INDEXES without validation update global indexes' parameter when executing the partition exchange. This parameter can update the global index information after the partition exchange is executed to ensure that the global index is valid. Finally, count the amount of data in the local historical table to ensure that it is consistent with the amount of data counted before the migration. If they are consistent, proceed to the next step, otherwise the data governance program terminates.

[0142] Step 13, call the dba_rotate_dblink_prc stored procedure, which generates a DBLINK based on the migration date. This only introduces the process of the partition table. Different from the ordinary table, the year information is extracted based on the time field of the migration partition and the dblink is dynamically generated.

[0143] Step 14, call the dba_rotate_hist_prc stored procedure, which is responsible for migrating the data in the local history table to the history table in the history library. First, insert the data into the history table in the history library through the Insert and dblink commands, and use the SQL%ROWCOUNT function to record the amount of data inserted. If this amount of data is consistent with the amount of data that needs to be migrated on that day, use the Truncate command to clear the data in the local history table.

[0144] Step 15, call the dba_rotate_auto_drop_prc stored procedure, which is used to delete empty partitions whose data has been migrated, and only delete empty partitions that have exceeded the specified time. First, you need to check whether the data of the partition to be deleted is 0. If it is 0, delete this partition through the drop command. When deleting, you need to use the 'update global indexes' parameter to avoid global index failure. The local history table also deletes empty partitions in this way. So far, the data governance process of the partition table has been completed.

[0145] From the above content, it can be known that this application can automatically migrate data that is no longer used from the business database to the historical database according to business needs. It can usually keep the usage of the database disk stable and save the disk space required for backup. When the amount of data is constant, the CPU and memory resources required by the database are usually relatively stable. Therefore, the database that has undergone data governance usually does not need to expand the server resources, and no longer requires manual regular data cleaning. Especially in an environment with many business systems, the server expansion cost and manual maintenance cost are greatly saved, and performance problems caused by the increase in data volume are also avoided.

[0146] From the hardware level, in order to effectively improve the efficiency and accuracy of data cleaning, the present application provides an embodiment of an electronic device for implementing all or part of the content of the data lifecycle management method, and the electronic device specifically includes the following content:

[0147] Processor, memory, communication interface and bus; wherein the processor, memory and communication interface communicate with each other through the bus; the communication interface is used to realize information transmission between the data lifecycle management device and related devices such as the core business system, user terminal and related database; the logic controller can be a desktop computer, a tablet computer and a mobile terminal, etc., but the present embodiment is not limited thereto. In the present embodiment, the logic controller can be implemented with reference to the embodiment of the data lifecycle management method and the embodiment of the data lifecycle management device in the embodiment, and the contents thereof are incorporated herein, and the repeated parts are not repeated.

[0148] It is understandable that the user terminal may include a smart phone, a tablet electronic device, a network set-top box, a portable computer, a desktop computer, a personal digital assistant (PDA), a vehicle-mounted device, a smart wearable device, etc. Among them, the smart wearable device may include smart glasses, a smart watch, a smart bracelet, etc.

[0149] In practical applications, part of the data lifecycle management method can be executed on the electronic device side as described above, or all operations can be completed in the client device. The specific selection can be based on the processing capability of the client device and the limitations of the user's usage scenario. This application does not limit this. If all operations are completed in the client device, the client device may also include a processor.

[0150] The client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side, and other implementation scenarios may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, or a server cluster consisting of multiple servers, or a server structure of a distributed device.

[0151] Fig. 9 FIG. 9 is a schematic block diagram of the system structure of the electronic device 9600 according to an embodiment of the present application. Fig. 9 As shown, the electronic device 9600 may include a central processor 9100 and a memory 9140; the memory 9140 is coupled to the central processor 9100. It is worth noting that Fig. 9 is exemplary; other types of structures may also be used to supplement or replace this structure to implement telecommunication functions or other functions.

[0152] In one embodiment, the data lifecycle management method functionality may be integrated into the central processor 9100 .

[0153] The CPU 9100 may be configured to perform the following control:

[0154] Step S101: when checking that the data fields and index attributes of the business table, the intermediate table, the local history table, and the history table in the history library are consistent and it is determined that there is no data in the local history table, the data to be migrated in the business table is obtained.

[0155] Step S102: Migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated.

[0156] Step S103: Migrate the data to be migrated in the local history table to the history table in the history library.

[0157] From the above description, it can be seen that the electronic device provided in the embodiment of the present application can effectively improve the efficiency and accuracy of data cleaning by obtaining the data to be migrated in the business table, the intermediate table, the local history table and the history library history table, and when it is determined that the data fields and index attributes of the business table, the intermediate table, the local history table and the history library history table are consistent and it is determined that there is no data in the local history table, migrating the data to be migrated in the business table to the local history table, and determining the corresponding history library history table according to the time field of the data to be migrated, and migrating the data to be migrated in the local history table to the history library history table.

[0158] In another embodiment, the data lifecycle management device may be configured separately from the central processor 9100. For example, the data lifecycle management device may be configured as a chip connected to the central processor 9100, and the data lifecycle management method function may be implemented through the control of the central processor.

[0159] like Fig. 9 As shown, the electronic device 9600 may also include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily have to include Fig. 9 In addition, the electronic device 9600 may also include Fig. 9 For components not shown, reference may be made to the prior art.

[0160] like Fig. 9 As shown, the central processing unit 9100 is sometimes also referred to as a controller or an operation control, and may include a microprocessor or other processor device and / or logic device. The central processing unit 9100 receives input and controls the operation of various components of the electronic device 9600.

[0161] The memory 9140 may be, for example, one or more of a cache, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices. The above-mentioned information related to the failure may be stored, and a program for executing the relevant information may also be stored. The CPU 9100 may execute the program stored in the memory 9140 to implement information storage or processing, etc.

[0162] The input unit 9120 provides input to the central processing unit 9100. The input unit 9120 is, for example, a key or a touch input device. The power supply 9170 is used to provide power to the electronic device 9600. The display 9160 is used to display display objects such as images and texts. The display may be, for example, an LCD display, but is not limited thereto.

[0163] The memory 9140 may be a solid-state memory, such as a read-only memory (ROM), a random access memory (RAM), a SIM card, etc. It may also be a memory that saves information even when the power is off, can be selectively erased, and is provided with more data, examples of which are sometimes referred to as EPROMs, etc. The memory 9140 may also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142, which is used to store application programs and function programs or processes for executing the operation of the electronic device 9600 through the central processor 9100.

[0164] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for communication functions of the electronic device and / or for executing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0165] The communication module 9110 is a transmitter / receiver 9110 that sends and receives signals via an antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processor 9100 to provide input signals and receive output signals, which may be the same as the case of a conventional mobile communication terminal.

[0166] Based on different communication technologies, multiple communication modules 9110 may be provided in the same electronic device, such as a cellular network module, a Bluetooth module and / or a wireless LAN module, etc. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide an audio output via the speaker 9131 and receive an audio input from the microphone 9132, thereby realizing a common telecommunication function. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor 9130 is also coupled to the central processor 9100, so that recording can be performed on the local machine through the microphone 9132, and the sound stored on the local machine can be played through the speaker 9131.

[0167] The embodiments of the present application also provide a computer-readable storage medium capable of implementing all steps of the data lifecycle management method in the above embodiments, where the execution subject is a server or a client. The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, all steps of the data lifecycle management method in the above embodiments are implemented by the execution subject being a server or a client. For example, when the processor executes the computer program, the following steps are implemented:

[0168] Step S101: when checking that the data fields and index attributes of the business table, the intermediate table, the local history table, and the history table in the history library are consistent and it is determined that there is no data in the local history table, the data to be migrated in the business table is obtained.

[0169] Step S102: Migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated.

[0170] Step S103: Migrate the data to be migrated in the local history table to the history table in the history library.

[0171] From the above description, it can be seen that the computer-readable storage medium provided in the embodiment of the present application can effectively improve the efficiency and accuracy of data cleaning by obtaining the data to be migrated in the business table, the intermediate table, the local history table and the history library history table, and when it is determined that the data fields and index attributes of the business table, the intermediate table, the local history table and the history library history table are consistent and it is determined that there is no data in the local history table, migrating the data to be migrated in the business table to the local history table, and determining the corresponding history library history table according to the time field of the data to be migrated, and migrating the data to be migrated in the local history table to the history library history table.

[0172] It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as methods, devices, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0173] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (apparatus), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0174] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0175] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0176] The present invention uses specific embodiments to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method of the present invention and its core idea. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present invention.

Claims

1. A data lifecycle management method, characterized in that: The method comprises: When the data fields and index attributes of the business table, the intermediate table, the local history table, and the history table of the history library are checked to be consistent and it is determined that there is no data in the local history table, the data to be migrated in the business table is obtained; Migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated; Migrating the data to be migrated in the local history table to the history table in the history library; After the data fields and index attributes of the business table, the intermediate and local history tables, and the history library history tables are checked to be consistent, the following steps are included: Determine whether the business table is a partition table; If so, it is determined whether there is historical data in the partition table according to the partition field and migration date of the partition table. If so, the historical data is migrated to the history table of the history library.

2. The data lifecycle management method according to claim 1, characterized in that: The obtaining the data to be migrated in the business table includes: Determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated; The data volume of the data to be migrated is determined according to the partition name, and the data to be migrated is acquired.

3. The data lifecycle management method according to claim 1, characterized in that: The step of migrating the data to be migrated in the business table to the local history table includes: According to a preset partition exchange command, the partition table containing the data to be migrated is exchanged with the intermediate table to migrate the data to be migrated to the intermediate table, and the partition table is deleted; The data to be migrated is migrated to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

4. A data lifecycle management device, characterized in that: include: The module for acquiring data to be migrated is used to acquire the data to be migrated in the business table when checking that the data fields and index attributes of the business table, the intermediate table, the local history table and the history table of the history library are consistent and it is determined that there is no data in the local history table; A local migration module, used to migrate the data to be migrated in the business table to the local history table, and determine the corresponding history library history table according to the time field of the data to be migrated; A history library migration module, used to migrate the data to be migrated in the local history table to the history library history table; After the data fields and index attributes of the business table, the intermediate and local history tables, and the history library history tables are checked to be consistent, the following steps are included: Determine whether the business table is a partition table; If so, it is determined whether there is historical data in the partition table according to the partition field and migration date of the partition table. If so, the historical data is migrated to the history table of the history library.

5. The data lifecycle management device according to claim 4, characterized in that: The module for acquiring data to be migrated further includes: a partition name determination unit, configured to determine whether the intermediate table is empty, and if so, determine the partition name of the partition table according to the time field of the data to be migrated; The partition data to be migrated determining unit is used to determine the data volume of the data to be migrated according to the partition name and obtain the data to be migrated.

6. The data lifecycle management device according to claim 4, characterized in that: The local migration module includes: The intermediate table exchange unit is used to migrate the data to be migrated to the intermediate table by performing a partition exchange operation on the partition table containing the data to be migrated and the intermediate table according to a preset partition exchange command, and delete the partition table; The local history table exchange unit is used to migrate the to-be-migrated data to the local history table by performing a partition exchange operation between the intermediate table and the local history table.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the steps of the data lifecycle management method according to any one of claims 1 to 3 are implemented.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the data lifecycle management method according to any one of claims 1 to 3 are implemented.

Images