Historical data automatic archiving method and device, computer device and storage medium

Abstract

The application relates to a historical data automatic archiving method and device, computer equipment, a storage medium and a computer program product. The method comprises the following steps: obtaining a historical data table to be archived, and extracting an archiving time field of the historical data table to be archived; partitioning the historical data table to be archived according to the archiving time field, and establishing a Hive data table structure of a target end; calling a Sqoop command to extract historical data in the historical data table to be archived and load the historical data into the Hive data table structure, so as to obtain a Hive data table; when the Hive data table is consistent with the historical data table to be archived, obtaining a primary key value in the historical data table to be archived, and determining the number of rows to be deleted in each batch; and deleting the historical data in the historical data table to be archived based on the primary key value and the number of rows to be deleted in each batch. The method can meet the demand of big data archiving.

Description

Technical Field

[0001] This application relates to the field of data archiving technology, and in particular to a method, apparatus, computer equipment, storage medium and computer program product for automatic archiving of historical data. Background Technology

[0002] With the increasing volume of business and the number of customers, the amount of historical data in various systems is growing, making the processing of historical data a hot topic.

[0003] Currently, historical data is processed manually by database administrators using open-source tools, such as pt-archiver, on a monthly or quarterly basis to back up the data and archive it during off-peak business periods.

[0004] However, as the amount of data involved grows larger and larger, even during off-peak business periods there is still a large amount of data. Relying solely on open-source tools that operate manually at fixed times for archiving is becoming increasingly inadequate for the current needs of big data archiving. This has significantly impacted the performance of archiving historical big data, making query speeds slower and backup times longer. Summary of the Invention

[0005] Therefore, it is necessary to provide a method, apparatus, computer equipment, computer-readable storage medium, and computer program product that can automatically archive historical data to meet the needs of big data archiving, addressing the aforementioned technical problems.

[0006] Firstly, this application provides a method for automatically archiving historical data. The method includes:

[0007] Obtain the historical data table to be archived, and extract the archiving time field from the historical data table to be archived;

[0008] Based on the historical data table to be archived, partition it by the archiving time field and establish the Hive data table structure on the target end;

[0009] The Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table;

[0010] When the Hive data table matches the historical data table to be archived, obtain the primary key value from the historical data table to be archived and determine the number of rows to be deleted in each batch;

[0011] Delete historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0012] In one embodiment, the method further includes:

[0013] The required number of archived rows is queried based on the archived time field. Based on the ratio of the required number of archived rows to the total number of rows in the historical data table, and the size of the historical data table in the historical data table space, the size of the historical data table space to be archived is predicted.

[0014] Based on the size of the historical data table to be archived and its corresponding total historical data table space, the historical data table to be archived is backed up.

[0015] In one embodiment, backing up the historical data table to be archived based on the size of the total historical data table space occupied by the historical data table to be archived includes:

[0016] When the size of the historical data table to be archived does not exceed the preset space threshold, the historical data table to be archived is logically backed up.

[0017] When the size of the historical data table to be archived exceeds a preset space threshold, the historical data table to be archived is physically backed up.

[0018] In one embodiment, the Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure, resulting in a Hive data table including:

[0019] The Sqoop command is invoked to extract the historical data to the big data HDFS layer, and the historical data of the big data HDFS layer is loaded into the Buf temporary table of the big data ODS layer.

[0020] Historical data from the loaded data in the Buf temporary table is merged into the Hive data table structure to obtain the Hive data table.

[0021] In one embodiment, when the Hive data table matches the historical data table to be archived, obtaining the primary key value in the historical data table to be archived and determining the number of rows to be deleted in each batch includes:

[0022] When the Hive data table is consistent with the historical data table to be archived, obtain the maximum and minimum values ​​of the primary key values ​​in the historical data table to be archived, as well as the memory refresh dirty page threshold.

[0023] Based on the required number of archived rows and the actual size of the total historical data table space occupied by the historical data table to be archived, the average row length of the historical data table to be archived is obtained.

[0024] The number of rows to be deleted in each batch is determined based on the average row length and the memory refresh dirty page threshold.

[0025] In one embodiment, after deleting the historical data in the required archived historical data table based on the primary key value and the number of rows deleted in each batch, the method further includes:

[0026] Analyze the fragmentation rate of historical data tablespaces after data deletion, and reclaim tablespaces whose fragmentation rate exceeds a preset fragmentation rate threshold.

[0027] Secondly, this application also provides an automatic historical data archiving device. The device includes:

[0028] The data table acquisition module is used to acquire the historical data table to be archived and extract the archiving time field of the historical data table to be archived.

[0029] The table creation module is used to partition the historical data table to be archived by the archiving time field and create the Hive data table structure on the target end.

[0030] The data loading module is used to call the Sqoop command to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table;

[0031] The parameter acquisition module, when the Hive data table is consistent with the historical data table to be archived, acquires the primary key value in the historical data table to be archived and determines the number of rows to be deleted in each batch;

[0032] The data deletion module deletes historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0033] Thirdly, this application also provides a computer device. The computer device includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to perform the following steps:

[0034] Obtain the historical data table to be archived, and extract the archiving time field from the historical data table to be archived;

[0035] Based on the historical data table to be archived, partition it by the archiving time field and establish the Hive data table structure on the target end;

[0036] The Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table;

[0037] When the Hive data table matches the historical data table to be archived, obtain the primary key value from the historical data table to be archived and determine the number of rows to be deleted in each batch;

[0038] Delete historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0039] Fourthly, this application also provides a computer-readable storage medium. The computer-readable storage medium stores a computer program thereon, which, when executed by a processor, performs the following steps:

[0040] Obtain the historical data table to be archived, and extract the archiving time field from the historical data table to be archived;

[0041] Based on the historical data table to be archived, partition it by the archiving time field and establish the Hive data table structure on the target end;

[0042] The Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table;

[0043] When the Hive data table matches the historical data table to be archived, obtain the primary key value from the historical data table to be archived and determine the number of rows to be deleted in each batch;

[0044] Delete historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0045] Fifthly, this application also provides a computer program product. The computer program product includes a computer program that, when executed by a processor, performs the following steps:

[0046] Obtain the historical data table to be archived, and extract the archiving time field from the historical data table to be archived;

[0047] Based on the historical data table to be archived, partition it by the archiving time field and establish the Hive data table structure on the target end;

[0048] The Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table;

[0049] When the Hive data table matches the historical data table to be archived, obtain the primary key value from the historical data table to be archived and determine the number of rows to be deleted in each batch;

[0050] Delete historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0051] The aforementioned method, apparatus, computer equipment, storage medium, and computer program product for automatic historical data archiving first obtains the historical data table to be archived and extracts the archiving time field from the historical data table. Based on the historical data table, it is partitioned according to the archiving time field to establish a Hive data table structure on the target end. Then, the Sqoop command is called to extract historical data from the historical data table and load it into the Hive data table structure, resulting in a Hive data table. The Hive data table obtained by calling the Sqoop command allows for fast querying of deleted historical data. When the Hive data table matches the historical data table to be archived, the primary key value of the historical data table to be archived is obtained, and the number of rows to be deleted in each batch is determined. Data in the historical data table to be archived is deleted based on the primary key value and the number of rows to be deleted in each batch. After deleting data, the original historical data in the historical database is reduced, and the speed of analyzing the remaining historical data is accelerated. Large-scale data synchronization and deletion of historical data makes query speed faster and backup time shorter, meeting the needs of large-scale data archiving. Attached Figure Description

[0052] Figure 1 This is a diagram illustrating the application environment of a historical data automatic archiving method in one embodiment.

[0053] Figure 2 This is a flowchart illustrating a method for automatically archiving historical data in one embodiment;

[0054] Figure 3 This is a flowchart illustrating the automatic archiving method for historical data in another embodiment;

[0055] Figure 4 This is a flowchart illustrating the automatic archiving method for historical data in yet another embodiment;

[0056] Figure 5 This is a flowchart illustrating the automatic archiving method for historical data in yet another embodiment;

[0057] Figure 6 This is a structural block diagram of an automatic historical data archiving device in one embodiment;

[0058] Figure 7 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0059] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0060] The automatic archiving method for historical data provided in this application embodiment can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or placed on a cloud or other network server. First, terminal 102 sends a historical data automatic archiving request to server 104. This request contains information about the historical data table to be archived. Server 104 receives the historical data automatic archiving request from terminal 102, extracts the information about the historical data table to be archived from the request, and extracts the archiving time field from the historical data table to be archived. Based on the historical data table to be archived, it partitions it according to the archiving time field, establishing a Hive data table structure for the target end. It calls the Sqoop command to extract historical data from the historical data table to be archived and loads it into the Hive data table structure, obtaining a Hive data table. When the Hive data table matches the historical data table to be archived, it obtains the primary key value from the historical data table to be archived and determines the number of rows to be deleted in each batch. Based on the primary key value and the number of rows to be deleted in each batch, it deletes the historical data from the historical data table to be archived. Furthermore, server 104 can also return the remaining historical data table after deleting the historical data in the required archived historical data table to terminal 102. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, and tablets, while server 104 can be implemented using a standalone server or a server cluster consisting of multiple servers.

[0061] In one embodiment, such as Figure 2 As shown, an automatic archiving method for historical data is provided, which can be applied to... Figure 1 Taking terminal 102 as an example, the explanation includes the following steps:

[0062] S100: Obtain the required historical data table and extract the archiving time field from the required historical data table.

[0063] Among them, archiving refers to the process of submitting processed and valuable documents for preservation and filing after being organized by the system. This application refers to organizing historical data and submitting it to the big data preservation and filing system. The historical data table to be archived is extracted from the original historical data master table. The archiving time field refers to the time when the historical data was archived.

[0064] For example, the historical data table information to be archived is historical data table information from 30 days ago. At this time, terminal 102 sends a historical data automatic archiving request to server 104. The historical data automatic archiving request contains the historical data table information to be archived. Server 104 receives the historical data automatic archiving request from terminal 102, extracts the historical data table information to be archived from the historical data automatic archiving request, that is, extracts the historical data table information from 30 days ago, and extracts the archiving time field of the historical data table to be archived from the historical data table information from 30 days ago.

[0065] Furthermore, the required archived historical data table information includes, but is not limited to, historical data table information dating back 30 days.

[0066] S200: Based on the historical data table to be archived, partition it by the archiving time field to establish the Hive data table structure on the target side.

[0067] Hive is a data warehouse tool (framework) built on Hadoop. It can map structured data files into a data table and perform reading, writing, and management of the data files. A Hive data table structure refers to a data table structure that is stored as a Hive file without containing actual data, after mapping historical data tables required for archiving from the MySQL (My Structured Query Language) source.

[0068] Specifically, the required historical data table structure is mapped to the target end, and partitioned by the archiving time field to obtain the Hive data table structure of the target end (big data end).

[0069] Furthermore, partitioning by the archive time field maps the required historical data table structure to the target end:

[0070] ① Existing fields are mapped one-to-one, and field names remain unchanged;

[0071] ② Field types need to be converted. The mapping relationship between the field types of the historical data table to be archived in the MySQL source and the field types of the Hive data table structure is as follows: varchar is converted to string, datetime is converted to string, text is converted to string, timestamp is converted to string, bigint with digits is converted to bigint without digits, int with digits is converted to int without digits, and char is converted to int without digits.

[0072] ③ In addition to the existing fields, add arcHive_date as a partition field, where the archive time format is yyyyMMdd;

[0073] For example, the table structure code on the MySQL source side includes, but is not limited to, the following:

[0074] CREATE TABLE`test`(`id`bigint(16)NOT NULL COMMENT'primary key ID',`c_1`datetime(6)DEFAULT NULL,`c_2`varchar(32)DEFAULT NULL,`c_3`int(11)DEFAULTNULL,PRIMARY KEY(`id`)USING BTREE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4ROW_FORMAT=DYNAMIC;

[0075] Therefore, the statements for creating Hive table structures in big data include, but are not limited to:

[0076] CREATE TABLE `test`(`id`bigint COMMENT'Primary Key ID',`c_1`string,`c_2`string,`c_3`int)partitioned by(arcHive_date STRING comment'Archived date, in yyyyMMdd').

[0077] S300 calls the Sqoop command to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table.

[0078] Sqoop is an open-source tool primarily used for transferring data between Hadoop (Hive) and traditional databases (MySQL, PostgreSQL, etc.). It can import data from a relational database (MySQL, Oracle, and Postgres, etc.) into the HDFS layer of Hadoop, which is big data, and can also import data from HDFS into a relational database.

[0079] Specifically, after establishing the Hive data table structure, server 104 uses the Sqoop open-source tool to transfer data between the Hive target end and the MySQL source end. This involves extracting historical data from the historical data tables that need to be archived on the MySQL source end and loading them into the Hive data table structure on the Hive target end, thus obtaining a complete Hive data table.

[0080] S400: When the Hive data table matches the historical data table to be archived, retrieve the primary key value from the historical data table to be archived and determine the number of rows to be deleted in each batch.

[0081] A primary key is a combination of one or more columns. It is mainly used for foreign key associations with other tables, as well as for modifying and deleting text records. The primary key value can uniquely identify each row in the table, and it can enforce the entity integrity of the table.

[0082] Specifically, when the Hive data table is consistent with the historical data table to be archived, and the historical data table to be archived is fully synchronized to the Hive target, i.e., the Hive data table of the big data, the deletion operation of the historical data table to be archived is performed. At this time, it is necessary to query the table structure of the historical data table to be archived, obtain the primary key value that meets the archiving conditions, and determine the number of rows to be deleted in each batch when deleting the historical data to be archived in batches.

[0083] S500 deletes historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0084] Specifically, when it is necessary to delete historical data in the required archived historical data table, it is determined whether the current dirty page size of the database instance (number of dirty pages * 16KB) does not exceed the memory refresh dirty page threshold, and whether the master-slave latency does not exceed the preset master-slave latency threshold. When neither the current dirty page size nor the master-slave latency exceeds the threshold, the historical data in the required archived historical data table is deleted in batches based on the primary key value and the number of rows to be deleted in each batch when deleting the required archived historical data. The deletion condition is that the master-slave latency is less than the preset master-slave latency threshold and the primary key value in the required archived historical data table falls between the maximum and minimum primary key values. After deletion, the program sleeps for a certain period of time and then loops again to check until all historical data has been deleted.

[0085] For example, a procedure for deleting historical data from a required archived historical data table based on the primary key value and the number of rows to be deleted in each batch includes, but is not limited to:

[0086] delete from FROM`employees`.`departments`FORCE INDEX(`PRIMARY`)WHERECREATE_TIME<'xxx'and id>=id1 and id<=id2 limit N.

[0087] The above-described automatic historical data archiving method first obtains the historical data table to be archived and extracts the archiving time field from it. Based on the historical data table, it is partitioned by the archiving time field to establish a Hive data table structure on the target end. Then, the Sqoop command is called to extract historical data from the historical data table to be archived and load it into the Hive data table structure, resulting in a Hive data table. The Hive data table obtained by calling the Sqoop command allows for fast querying of deleted historical data. When the Hive data table matches the historical data table to be archived, the primary key value of the historical data table to be archived is obtained, and the number of rows to be deleted in each batch is determined. Data in the historical data table to be archived is deleted based on the primary key value and the number of rows to be deleted in each batch. After deleting data, the original historical data in the historical database is reduced, and the speed of analyzing the remaining historical data is accelerated. Large-scale data synchronization and deletion of historical data makes query speed faster and backup time shorter, meeting the needs of large-scale data archiving.

[0088] In one embodiment, the method for automatically archiving historical data further includes:

[0089] The required number of archived rows is retrieved based on the archived time field. Then, based on the ratio of the required number of archived rows to the total number of rows in the historical data table, and the size of the historical data table space occupied by the historical data table, the size of the historical data table space occupied by the required archived historical data table is predicted.

[0090] A tablespace is a logical partition of a database. A tablespace can only belong to one database. All database objects are stored in a specified tablespace, but the main objects stored there are tables, hence the name tablespace.

[0091] Specifically, the historical data to be archived will be assigned an archive time field when archived. Based on the archive time field and other conditions, the required number of archived rows of the historical data is queried. The required number of archived rows of the historical data is compared with the number of rows in the historical database master table to obtain the ratio of the required number of archived rows to the total number of rows in the historical data master table. The ratio of the required number of archived rows to the total number of rows in the historical data master table can be regarded as the ratio of the historical data table to be archived to the historical data master table. By analyzing the size of the historical data master table occupied by the historical data master table, and combining the ratio of the historical data table to be archived to the historical data master table, the size of the historical data table to be archived occupied by the historical data master table can be predicted.

[0092] Furthermore, other conditions refer to specific conditions required for data archiving, such as data with a final status of success or failure in the status field.

[0093] Backup the historical data tables to be archived based on the size of the total historical data table space occupied by the historical data tables to be archived.

[0094] Backup refers to copying data from a file system or database system and saving it to another safe location. In the event of unforeseen disasters or erroneous operations, this backup can be used to quickly restore the data to its normal state at the time of backup, thereby ensuring that the system can quickly resume normal and stable operation. However, backup has low timeliness and the contents of the backup cannot be viewed at any time. Its main purpose is to protect data security.

[0095] Specifically, based on the size of the historical data table to be archived and its corresponding total historical data table space, the backup method corresponding to the size of the historical data table to be archived is selected to back up the historical data table to be archived.

[0096] In this embodiment, by backing up the historical data table to be archived, the historical data can be recovered.

[0097] In one embodiment, backing up the historical data table to be archived, based on the size of the total historical data table space occupied by the historical data table to be archived, includes:

[0098] If the size of the historical data table to be archived does not exceed the preset space threshold, a logical backup is performed on the historical data table to be archived.

[0099] Among them, the preset space threshold refers to the value of the total table space of historical data tables that need to be archived; logical backup uses the mysqldump command built into the MySQL database or a third-party tool to export the data in the database as SQL statements into a file. When data needs to be restored, the SQL statements in the backup file are extracted and re-executed in the database using relevant commands to achieve the purpose of data restoration.

[0100] For example, if the preset space threshold is 5G, and the size of the historical data table to be archived does not exceed 5G of the total historical data table space, then use the mysqldump logical backup tool or mydumper logical backup tool to back up the data to be archived, generate SQL statements, and then export them to a file.

[0101] Optionally, the preset space threshold includes, but is not limited to, 5G.

[0102] When the predicted size of the historical data table to be archived exceeds the preset space threshold, a physical backup of the historical data table to be archived is performed.

[0103] Physical backup involves using commands (such as cp, tar, scp, etc.) to directly copy one or more copies of the database's storage data files and store them in other directories to achieve the backup effect.

[0104] For example, if the preset space threshold is 5G, when the size of the historical data table to be archived exceeds 5G, the entire data of the archived table is backed up online using xtrabackup to obtain the ibd file of the entire table.

[0105] Optionally, the preset space threshold includes, but is not limited to, 5G.

[0106] In this embodiment, physical backups or logical backups are distinguished by the size of the historical data table to be archived in the total historical data table space. This allows for the use of physical backups for historical data tables with large amounts of data and logical backups for historical data tables with small amounts of data, thus enabling rapid recovery of historical data.

[0107] In one embodiment, such as Figure 3 As shown, S300 includes:

[0108] S320 calls the Sqoop command to extract historical data to the big data HDFS layer, and loads the historical data from the big data HDFS layer into the Buf temporary table of the big data ODS layer.

[0109] When data is on HDFS (Hadoop Distributed File System), it is just plain data and cannot be directly manipulated. This data needs to be put into a framework that allows data manipulation, namely the ODS (Operational Data Processing) layer. The ODS layer is used to read data from HDFS without losing its content and put it into Hive for analysis. The Buf temporary table is a table that temporarily stores historical data.

[0110] Specifically, after establishing the Hive data table structure, the historical data to be archived needs to be transferred to the Hive data table structure. Since the HDFS layer is only used to store the extracted historical data, and the ODS layer is used to load and operate on the historical data of the HDFS layer, the Sqoop open-source tool is called to extract the read-only instance of the historical data to be archived to the HDFS layer of the big data system, and then the historical data of the HDFS layer is loaded into the big data ODS layer for subsequent query and analysis. At this time, the Buf temporary table is loaded into the big data ODS layer.

[0111] For example, if the payment transaction history table (make_loan_order_history) archives data up to 30 days ago, and the business field is opt_datetime, then the code to query historical data up to 30 days ago can be:

[0112] select*from make_loan_order_history where opt_datetime <date_format(date_sub(now(),INTERVAL(30)day),'%Y-%m-%d');

[0113] The final Sqoop statement is as follows:

[0114] Sqoop import-Dyarn.app.mapreduce.am.resource.mb=${am_memory_mb}\

[0115] -Dmapreduce.map.memory.mb=${map_memory_mb}\

[0116] -Dorg.apache.Sqoop.splitter.allow_text_splitter=true\

[0117] --connect${db_url} / ${db}\

[0118] --username${user}\

[0119] --password${passwd}\

[0120] --num-mappers 1\

[0121] --null-string"\

[0122] --null-non-string”\

[0123] --query"select*from make_loan_order_history where opt_datetime <date_for mat(date_sub(now(),INTERVAL(30)day),'%Y-%m-%d')and\$CONDITIONS"\

[0124] --target-dir${dest_base_path} / ${db} / make_loan_order_history\

[0125] --delete-target-dir\

[0126] --fields-terminated-by'\001'\

[0127] --Hive-drop-import-delims\

[0128] --escaped-by'\\'

[0129] The parameter variables are explained below:

[0130] ①am_memory_mb and map_memory_mb are YARN runtime parameters, which are generally fixed.

[0131] ②The meanings of db_url, db, user, and passwd are the MySQL source database connection address, database name, username, and password, respectively;

[0132] ③dest_base_path is the HDFS transit directory.

[0133] After extracting historical data from the required archived historical data tables to the HDFS layer, perform load and merge operations to load the historical data from HDFS into the Buf temporary table in the ODS layer. Specific code includes, but is not limited to, the following:

[0134] load data inpath'${HDFS_base_dir} / ${data_dirname} / make_loan_order_history'overwrite into table${dbname}.make_loan_order_history_Buf.

[0135] S340: Merge the historical data in the loaded data Buf temporary table into the Hive data table structure to obtain the Hive data table.

[0136] Among them, a Hive data table refers to a data table that is mapped from a historical data table on the MySQL source side, stored in the form of a Hive file, and loaded with historical data.

[0137] Specifically, since the previously established Hive data table structure does not contain historical data and is only a structural mapping of the historical data table from the MySQL source, the historical data in the already loaded data Buf temporary table is merged into the Hive data table structure to obtain the final Hive data table. The Hive data table can be used to query the historical data of the HDFS layer loaded into the ODS layer.

[0138] Furthermore, to obtain the final Hive data table, it is necessary to compare the consistency between the historical data table on the MySQL source side and the mapped Hive data table to avoid errors in the historical data mapping process. When comparing the historical data table on the MySQL source side and the mapped Hive data table, the row count is compared first by executing row count query statements on both the MySQL source side and the Hive target side. After confirming that the row count is correct, the specific data of the corresponding rows is compared. Once this is confirmed, the final Hive data table is obtained, completing the operation of synchronizing the required archived historical data table to the Hive target side.

[0139] For example, statements that archive payment transaction history tables (make_loan_order_history) up to 30 days ago, and merge historical data from the loaded Buf temporary table into the Hive data table structure, include, but are not limited to:

[0140] Insert overwrite${dbname}.make_loan_order_history(${paymentDB[$tbl]})select${paymentDB[$tbl]}from${dbname}.make_loan_order_history_Buf;

[0141] Statements for querying the number of rows in the source region of MySQL include, but are not limited to:

[0142] Select count(1)frommake_loan_order_history where opt_datetime <date_form at(date_sub(now(),INTERVAL(30)day),'%Y-%m-%d');

[0143] The query statement for counting rows on the target server in Hive includes, but is not limited to:

[0144] select count(1)from make_loan_order_history where opt_datetime <to_date(d ate_sub(now(),30))。

[0145] In this embodiment, by establishing a Hive data table in the big data that is consistent with the historical data table to be archived, the synchronization of historical data can be achieved, so that even if the historical data is deleted, it can still be queried quickly and in real time on the big data.

[0146] In one embodiment, such as Figure 4 As shown, S400 includes:

[0147] S420: When the Hive data table matches the historical data table to be archived, obtain the maximum and minimum values ​​of the primary key values ​​in the historical data table to be archived, as well as the memory flush dirty page threshold.

[0148] Dirty pages refer to data in memory that is inconsistent with data on disk. Flushing dirty pages in memory refers to flushing dirty pages in memory to disk according to certain rules. The dirty page flushing threshold in memory refers to the size of dirty pages in memory that need to be flushed. It is generally used to determine whether the database is busy. The default value of the dirty page flushing threshold in memory is usually 1 / 10 of the size of the database instance's memory pool.

[0149] Specifically, examine the table structure of the historical data table to be archived, and obtain the minimum value id1 and the maximum value id2 among the primary key values ​​that meet the archiving conditions.

[0150] For example, a program for retrieving the minimum value id1 and the maximum value id2 among primary key values ​​that satisfy archiving conditions includes, but is not limited to:

[0151] #Start transaction

[0152] set autocommit = 0;

[0153] #Retrieve the minimum and maximum primary key values ​​that meet the specified conditions

[0154] SELECT min(id)as id1,max_id as id2 FROM`employees`.`departments`FORCEINDEX(`PRIMARY`)WHERE CREATE_TIME<'xxx'.

[0155] S440: Based on the required number of archived rows and the actual size of the total historical data table space occupied by the required archived historical data table, obtain the average row length of the required archived historical data table.

[0156] Specifically, since the product of the average row length of the historical data table to be archived and the number of rows to be archived is the actual size of the total historical data table space occupied by the historical data table to be archived, the average row length of the historical data table to be archived can be obtained based on the number of rows to be archived and the actual size of the total historical data table space occupied by the historical data table to be archived.

[0157] S460 determines the number of rows to delete in each batch based on the average row length and the memory refresh dirty page threshold.

[0158] Specifically, after obtaining the average row length, the number of rows deleted in each batch can be obtained by dividing the average row length by the memory dirty page refresh threshold.

[0159] In this embodiment, by determining the dirty page refresh threshold and the average row length, the number of rows to be deleted in each batch can be obtained, thus achieving both stability and cleaning speed when deleting data.

[0160] In one embodiment, such as Figure 5 As shown, following S500, it also includes:

[0161] S600 analyzes the fragmentation rate of historical data tablespaces after data deletion and reclaims tablespaces whose fragmentation rate exceeds a preset fragmentation rate threshold.

[0162] The tablespace fragmentation rate refers to the size of the tablespace occupied by holes; a tablespace hole is a blank area that appears in the originally contiguous space after data is deleted.

[0163] Specifically, the tablespace fragmentation rate is scanned and analyzed after deleting the historical data tables to be archived. It is then determined whether the tablespace fragmentation rate threshold exceeds the preset fragmentation rate threshold. If it does, the tablespace after deleting the historical data is reclaimed to reduce the tablespace fragmentation rate.

[0164] For example, a program that scans a database to obtain tables whose tablespace fragmentation rate exceeds a preset fragmentation rate threshold includes, but is not limited to:

[0165] SELECT

[0166] TABLE_SCHEMA AS `db`,

[0167] TABLE_NAME AS`tbl`,concat(truncate((DATA_LENGTH) / 1024 / 1024,2),'MB'),concat(truncate((INDEX_LENGTH) / 1024 / 1024,2),'MB'),concat(truncate((data_free) / 1024 / 1024,2),'MB'),

[0168] 1-(TABLE_ROWS*AVG_ROW_LENGTH) / (

[0169] DATA_LENGTH+INDEX_LENGTH+DATA_FREE)AS`fragment_pct`

[0170] FROM

[0171] information_schema.TABLES

[0172] WHERE

[0173] TABLE_SCHEMA NOT IN(

[0174] 'information_schema','MySQL','performance_schema','sys')

[0175] AND(1-(TABLE_ROWS*AVG_ROW_LENGTH) / (DATA_LENGTH+INDEX_LENGTH+DATA_FREE))>0.5

[0176] AND(DATA_LENGTH+INDEX_LENGTH+DATA_FREE)>1024*1024*1024.

[0177] In this embodiment, by performing tablespace reclamation on tables with fragmentation rates exceeding a preset fragmentation rate threshold, physical disk space can be released in a timely manner, and database performance can be improved.

[0178] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0179] Based on the same inventive concept, this application also provides an automatic historical data archiving device for implementing the above-described automatic historical data archiving method. The solution provided by this device is similar to the implementation described in the above method; therefore, the specific limitations in one or more embodiments of the automatic historical data archiving device provided below can be found in the limitations of the automatic historical data archiving method described above, and will not be repeated here.

[0180] In one embodiment, such as Figure 6 As shown, an automatic historical data archiving device is provided, including: a data table acquisition module 100, a table creation module 200, a data loading module 300, a parameter acquisition module 400, and a data deletion module 500, wherein:

[0181] The data table acquisition module 100 is used to acquire the historical data table to be archived and extract the archive time field of the historical data table to be archived.

[0182] The table creation module 200 is used to partition the historical data table to be archived by the archiving time field and create the Hive data table structure on the target end.

[0183] The data loading module 300 is used to call the Sqoop command to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table.

[0184] The parameter acquisition module 400, when the Hive data table is consistent with the historical data table to be archived, acquires the primary key value in the historical data table to be archived and determines the number of rows to be deleted in each batch.

[0185] The data deletion module 500 deletes historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch.

[0186] In one embodiment, the system further includes a data backup module, which is also configured to: query the required number of archived rows based on the archived time field; predict the size of the historical data table to be archived based on the ratio of the required number of archived rows to the total number of rows in the historical data table and the size of the historical data table in the historical data table space; and back up the historical data table to be archived based on the size of the historical data table to be archived in the historical data table space.

[0187] In one embodiment, the data backup module is further configured to: perform logical backup of the historical data table to be archived when the predicted size of the historical data table to be archived does not exceed a preset space threshold; and perform physical backup of the historical data table to be archived when the predicted size of the historical data table to be archived exceeds a preset space threshold.

[0188] In one embodiment, the data loading module 300 is further configured to: call the Sqoop command to extract historical data to the big data HDFS layer, and load the historical data of the big data HDFS layer into the Buf temporary table of the big data ODS layer; merge the historical data in the loaded data Buf temporary table into the Hive data table structure to obtain the Hive data table.

[0189] In one embodiment, the parameter acquisition module 400 is further configured to: when the Hive data table is consistent with the historical data table to be archived, acquire the maximum and minimum values ​​of the primary key values ​​in the historical data table to be archived, as well as the memory refresh dirty page threshold; obtain the average row length of the historical data table to be archived based on the number of rows to be archived and the actual size of the total historical data table space occupied by the historical data table to be archived; and determine the number of rows to be deleted in each batch based on the average row length and the memory refresh dirty page threshold.

[0190] In one embodiment, the system further includes a space release module, which is also used to: analyze the fragmentation rate of historical data tablespaces after data deletion, and reclaim tablespaces whose fragmentation rate exceeds a preset fragmentation rate threshold.

[0191] The modules in the aforementioned automatic historical data archiving device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of a computer device in software form, so that the processor can call and execute the operations corresponding to each module.

[0192] In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 7As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs in the non-volatile storage media to run. The database stores historical data tables that need to be archived. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements an automatic historical data archiving method.

[0193] Those skilled in the art will understand that Figure 7 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0194] In one embodiment, a computer device is also provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above method embodiments.

[0195] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.

[0196] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0197] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0198] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0199] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for automatically archiving historical data, characterized in that, The method includes: Obtain the historical data table to be archived, and extract the archiving time field from the historical data table to be archived; Based on the historical data table to be archived, partition it by the archiving time field and establish the Hive data table structure on the target end; The Sqoop command is invoked to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table; When the Hive data table is consistent with the historical data table to be archived, obtain the maximum and minimum values ​​of the primary key values ​​in the historical data table to be archived, as well as the memory refresh dirty page threshold. Based on the required number of archived rows and the actual size of the total historical data table space occupied by the historical data table to be archived, the average row length of the historical data table to be archived is obtained, and based on the average row length and the memory refresh dirty page threshold, the number of rows to be deleted in each batch is determined. Based on the primary key value and the number of rows to be deleted in each batch, historical data in the required archived historical data table is deleted; wherein, by determining whether the current dirty page size of the database instance does not exceed the memory refresh dirty page threshold, and whether the master-slave latency does not exceed the preset master-slave latency threshold; if the determination result is yes, then the deletion range is defined based on the maximum and minimum values ​​of the primary key value, and combined with the number of rows to be deleted in each batch, the historical data in the required archived historical data table is deleted in batches; wherein, after each deletion, the program sleeps for a set time, and then loops to determine again until all historical data has been deleted.

2. The method according to claim 1, characterized in that, The method further includes: The required number of archived rows is queried based on the archived time field. Based on the ratio of the required number of archived rows to the total number of rows in the historical data table, and the size of the historical data table in the historical data table space, the size of the historical data table space to be archived is predicted. Based on the size of the historical data table to be archived and its corresponding total historical data table space, the historical data table to be archived is backed up.

3. The method according to claim 2, characterized in that, The step of backing up the historical data tables to be archived based on the size of the total historical data table space occupied by the historical data tables to be archived includes: When the size of the historical data table to be archived does not exceed the preset space threshold, the historical data table to be archived is logically backed up. When the size of the historical data table to be archived exceeds a preset space threshold, the historical data table to be archived is physically backed up.

4. The method according to claim 1, characterized in that, The step of calling the Sqoop command to extract historical data from the required archived historical data table and load it into the Hive data table structure results in the following Hive data table: The Sqoop command is invoked to extract the historical data to the big data HDFS layer, and the historical data of the big data HDFS layer is loaded into the Buf temporary table of the big data ODS layer. Historical data from the loaded data in the Buf temporary table is merged into the Hive data table structure to obtain the Hive data table.

5. The method according to claim 1, characterized in that, After deleting historical data from the required archived historical data table based on the primary key value and the number of rows deleted in each batch, the process further includes: Analyze the fragmentation rate of historical data tablespaces after data deletion, and reclaim tablespaces whose fragmentation rate exceeds a preset fragmentation rate threshold.

6. An automatic historical data archiving device, characterized in that, The device includes: The data table acquisition module is used to acquire the historical data table to be archived and extract the archiving time field of the historical data table to be archived. The table creation module is used to partition the historical data table to be archived by the archiving time field and create the Hive data table structure on the target end. The data loading module is used to call the Sqoop command to extract historical data from the required archived historical data table and load it into the Hive data table structure to obtain the Hive data table; The parameter acquisition module, when the Hive data table is consistent with the historical data table to be archived, acquires the maximum and minimum values ​​of the primary key values ​​in the historical data table to be archived, as well as the memory refresh dirty page threshold; based on the number of rows to be archived and the actual size of the total historical data table space occupied by the historical data table to be archived, it obtains the average row length of the historical data table to be archived, and determines the number of rows to be deleted in each batch based on the average row length and the memory refresh dirty page threshold; The data deletion module deletes historical data from the required archived historical data table based on the primary key value and the number of rows to be deleted in each batch. Specifically, it determines whether the current dirty page size of the database instance does not exceed the memory refresh dirty page threshold and whether the master-slave latency does not exceed a preset master-slave latency threshold. If the determination is yes, a deletion range is defined based on the maximum and minimum values ​​of the primary key value, and the historical data from the required archived historical data table is deleted in batches, combined with the number of rows to be deleted in each batch. After each deletion, the program sleeps for a set time and then loops again until all historical data has been deleted.

7. The apparatus according to claim 6, characterized in that, The device further includes a data backup module for: The required number of archived rows is queried based on the archived time field. Based on the ratio of the required number of archived rows to the total number of rows in the historical data table, and the size of the historical data table in the historical data table space, the size of the historical data table to be archived in the historical data table space is predicted. Based on the size of the historical data table to be archived in the historical data table space, the historical data table to be archived is backed up.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 5.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 5.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 5.

Images