Data processing method and device, electronic equipment and storage medium

Abstract

The present disclosure relates to a data processing method and device, electronic equipment and storage medium, comprising: obtaining a target data table to be archived in an original database; obtaining an upper limit and a lower limit of archiving of the target data table; obtaining target row data of a target column from the target data table, wherein a time attribute value of the target column is between the upper limit and the lower limit of archiving; and archiving the target row data to an archive database. In this way, since the upper limit and the lower limit of archiving are recorded, i.e., since the archiving progress is recorded, each time of archiving does not need to start from the beginning, which can avoid wasting a lot of time on finding data that has not been archived, and can improve the efficiency of data archiving. Furthermore, since the archiving progress is recorded, the present disclosure supports pausing and restarting of the archiving task at any time, and after the archiving service is restarted, the archiving task continues to be executed from the recorded last archiving position, avoiding starting from the beginning, which can effectively save computing resources.

Description

Technical Field

[0001] This disclosure relates to the field of computer technology, and more specifically, to data processing methods, apparatus, electronic devices, and storage media. Background Technology

[0002] To ensure database availability and disaster recovery capabilities, databases are typically stored in clusters. A database cluster can contain one master database and several slave databases, and the same data can be stored in different locations within the cluster. For example, data can be stored in the master database, and simultaneously, the same data can be stored in different slave databases.

[0003] Databases may contain infrequently used data, or "cold data," which can be archived to nearline devices. In related technologies, each data archiving process requires searching from the beginning of the entire dataset until previously unarchived data is found and then archived. Because each archiving requires starting from the beginning, a significant amount of time is wasted searching for unarchived data, resulting in low archiving efficiency. Summary of the Invention

[0004] This disclosure provides data processing methods, apparatus, electronic devices, and storage media to at least address the problem of low efficiency in data archiving in the aforementioned related technologies.

[0005] According to a first aspect of the present disclosure, a data processing method is provided, comprising: obtaining a target data table to be archived in an original database, wherein the original database is a database in a database cluster, and the target data table has a target column indicating the time attribute value of each row of data; obtaining an archiving upper limit and an archiving lower limit of the target data table, wherein the archiving upper limit is used to indicate the termination archiving point of the current archiving in the target data table, and the archiving lower limit is used to indicate the starting archiving point of the current archiving in the target data table; obtaining target row data from the target data table whose time attribute value of the target column is between the archiving upper limit and the archiving lower limit; and archiving the target row data to an archive database.

[0006] Optionally, the target column is a column with a physical time attribute in the target data table, or the target column is a timestamp hidden column representing the time when each row of data in the target data table was updated.

[0007] Optionally, obtaining the upper and lower limits of the archived data table includes: generating the upper limit of the current archived data every preset period based on the current physical time and a preset first time interval, and obtaining the lower limit of the current archived data every preset period, wherein the lower limit of the current archived data is the upper limit of the previous archived data.

[0008] Optionally, the target column is an identifier column set for each row of data in the target data table to indicate the order of writing; obtaining the upper and lower limits of the archived data table includes: obtaining a pre-configured first identifier as the upper limit of the archived data table, and obtaining a pre-configured second identifier as the lower limit of the archived data table, wherein the writing order corresponding to the second identifier is before the writing order corresponding to the first identifier.

[0009] Optionally, obtaining the target row data from the target data table where the time attribute value of the target column is between the upper and lower limits of the archived data includes: if the row data in the target data table where the time attribute value of the target column is between the upper and lower limits of the current archived data exceeds a preset condition, starting from the lower limit of the current archived data, obtaining the row data that meets the preset condition as the target row data.

[0010] Optionally, the preset conditions include at least one of the following: the number of rows of row data is a preset number of rows, and the total number of bytes of row data is a preset first byte number.

[0011] Optionally, the data processing method further includes: generating a target lower limit for the next archive of the current archive, wherein the target lower limit is characterized by the maximum time attribute value of the target column corresponding to the target row data; and generating a target upper limit for the next archive, wherein the target upper limit is the same as the upper limit for the current archive.

[0012] Optionally, the data processing method further includes: generating the archive limit for the next archive based on the current physical time and a preset first time interval when the current archive archive position reaches the target archive limit, or when the number of rows of data between the current archive archive position and the target archive limit is less than a preset number of rows.

[0013] Optionally, archiving the target row data to the archive database includes: migrating the target row data to a message queue, so that when the message queue determines that the total number of bytes of the acquired target row data reaches a preset second byte number, it archives the target row data with the total number of bytes of the preset second byte number to the archive database.

[0014] Optionally, the original database is the master database, and the database cluster further includes a slave database storing the target data table; after archiving the target row data to the archive database, the method further includes: synchronizing the upper limit of archiving and the lower limit of archiving to the slave database, so that after the slave database obtains the target row data from the target data table, it archives the target row data to the archive database.

[0015] Optionally, the original database is a slave database; obtaining the archiving upper limit and archiving lower limit of the target data table includes: using a data archiving thread to read the archiving upper limit and the archiving lower limit of the current archive from the archiving location data table, wherein the archiving location data table is stored in the master database in the database cluster, and the archiving location data table is used to write the archiving upper limit and archiving lower limit for each archive.

[0016] Optionally, obtaining the target row data from the target data table where the time attribute value of the target column is between the upper and lower limits of the current archive includes: generating an archive query statement based on the upper and lower limits of the current archive; and querying the target row data from the target data table where the time attribute value of the target column is between the upper and lower limits of the current archive using the archive query statement.

[0017] Optionally, the data processing method further includes: obtaining the upper and lower limits of the verification corresponding to the target data table, wherein the upper limit of the verification is used to indicate the termination point of the current verification in the target data table, and the lower limit of the verification is used to indicate the starting point of the current verification in the target data table; comparing the row data in the original database whose time attribute value of the target column is between the upper and lower limits of the verification with the row data in the archived database whose time attribute value of the target column is between the upper and lower limits of the verification, to obtain a comparison result; if the comparison result is consistent, determining that the row data between the upper and lower limits of the verification is successfully archived; if the comparison result is inconsistent, determining that the row data between the upper and lower limits of the verification fails to be archived.

[0018] Optionally, obtaining the upper and lower limits of the verification corresponding to the target data table includes: obtaining a position less than or equal to the lower limit of the archive as the upper limit of the current verification; and obtaining the upper limit of the previous verification as the lower limit of the current verification.

[0019] Optionally, obtaining a position less than or equal to the lower limit of archiving as the upper limit of the current verification includes: obtaining a position less than the lower limit of archiving and a distance of a preset second time interval from the lower limit of archiving as the upper limit of the current verification, wherein the data archived to the archive database is in a readable state after the preset second time interval following the time of archiving.

[0020] Optionally, the data processing method further includes: obtaining the upper and lower limits of cleanup corresponding to the target data table, wherein the upper limit of cleanup is used to indicate the termination cleanup point of the current cleanup in the target data table, and the lower limit of cleanup is used to indicate the starting cleanup point of the current cleanup in the target data table; deleting the row data in the target data table whose time attribute value of the corresponding target column is between the upper and lower limits of cleanup.

[0021] Optionally, obtaining the upper and lower limits of the cleanup corresponding to the target data table includes: obtaining a position that is less than or equal to the latest verification lower limit as the upper limit of the cleanup for this cleanup; and obtaining the upper limit of the cleanup corresponding to the previous cleanup for this cleanup as the lower limit of the cleanup for this cleanup.

[0022] Optionally, obtaining the cleanup upper limit and cleanup lower limit corresponding to the target data table includes: using a pre-set data cleanup thread in the original database to obtain the cleanup upper limit and cleanup lower limit corresponding to the target data table; deleting row data in the target data table whose time attribute value of the corresponding target column is between the cleanup upper limit and the cleanup lower limit includes: deleting row data in the target data table whose time attribute value of the corresponding target column is between the cleanup upper limit and the cleanup lower limit through the data cleanup thread.

[0023] Optionally, obtaining the target row data whose time attribute value of the target column is between the upper limit of archiving and the lower limit of archiving from the target data table includes: if the target row data is not obtained from the target data table, checking whether the target row data exists in the target data table every preset period until the target row data is obtained, wherein the archiving service is in a dormant state during each preset period, and the archiving service is used to archive the data in the original database to the archive database.

[0024] Optionally, the data processing method further includes: in the event of an anomaly in this archiving, recording the abnormal archiving point in the target data table at the time of the anomaly; creating a new archiving service, and using the new archiving service to continue data archiving from the abnormal archiving point in the target data table.

[0025] Optionally, after archiving the target row data to the archive database, the data processing method further includes: in response to a data access request received from the client, sending the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit for this archive, to the client.

[0026] Optionally, sending the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit of this archive, to the client includes: comparing the first row data in the target data table where the time attribute value of the target column is greater than the archiving limit of this archive with the second row data in the target data table currently included in the archive database to obtain a comparison result; if the comparison result indicates that there are duplicate rows of data between the first row data and the second row data, sending the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the maximum time attribute value of the target column corresponding to the second row data, to the client; if the comparison result indicates that there are no duplicate rows of data between the first row data and the second row data, sending the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit of this archive, to the client.

[0027] According to a second aspect of the present disclosure, a data processing apparatus is provided, comprising: a target data table acquisition module configured to acquire a target data table to be archived in an original database, wherein the original database is a database in a database cluster, and the target data table has a target column indicating the time attribute value of each row of data; an archiving upper limit and archiving lower limit acquisition module configured to acquire an archiving upper limit and an archiving lower limit of the target data table, wherein the archiving upper limit is used to indicate the termination archiving position of the current archiving in the target data table, and the archiving lower limit is used to indicate the starting archiving position of the current archiving in the target data table; a target row data acquisition module configured to acquire target row data from the target data table whose time attribute value of the target column is between the archiving upper limit and the archiving lower limit; and an archiving module configured to archive the target row data to an archive database.

[0028] Optionally, the target column is a column with a physical time attribute in the target data table, or the target column is a timestamp hidden column representing the time when each row of data in the target data table was updated.

[0029] Optionally, the archiving upper limit and archiving lower limit acquisition module is configured to: generate the archiving upper limit of the current archive based on the current physical time and a preset first time interval every preset period, and acquire the archiving lower limit of the current archive every preset period, wherein the archiving lower limit of the current archive is the archiving upper limit of the previous archive.

[0030] Optionally, the target column is an identifier column set for each row of data in the target data table to indicate the order of writing; the archiving upper limit and archiving lower limit acquisition module is configured to: acquire a pre-configured first identifier as the archiving upper limit, and acquire a pre-configured second identifier as the archiving lower limit, wherein the writing order corresponding to the second identifier is before the writing order corresponding to the first identifier.

[0031] Optionally, the target row data acquisition module is configured to: if the row data in the target data table whose time attribute value of the target column exceeds the archive limit and the archive lower limit of the current archive in a preset condition, starting from the archive lower limit of the current archive, acquire the row data that meets the preset condition as the target row data.

[0032] Optionally, the preset conditions include at least one of the following: the number of rows of row data is a preset number of rows, and the total number of bytes of row data is a preset first byte number.

[0033] Optionally, the data processing device further includes: a target archiving lower limit generation module, configured to generate a target archiving lower limit for the next archiving of the current archiving, wherein the target archiving lower limit is characterized by the maximum time attribute value of the target column corresponding to the target row data; and a target archiving upper limit generation module, configured to generate a target archiving upper limit for the next archiving, wherein the target archiving upper limit is the same as the archiving upper limit of the current archiving.

[0034] Optionally, the data processing device further includes: an archive limit generation module, configured to generate an archive limit for the next archive based on the current physical time and a preset first time interval when the target archive limit is reached at the current archive point, or when the number of rows of row data between the current archive point and the target archive limit is less than a preset number of rows.

[0035] Optionally, the archiving module is configured to migrate the target row data to a message queue, so that when the message queue determines that the total number of bytes of the acquired target row data reaches a preset second byte number, it archives the target row data with the total number of bytes of the preset second byte number to the archiving database.

[0036] Optionally, the original database is the master database, and the database cluster further includes a slave database storing the target data table; the data processing device further includes: a synchronization module configured to synchronize the archiving upper limit and the archiving lower limit to the slave database, so that after the slave database obtains the target row data from the target data table, it archives the target row data to the archive database.

[0037] Optionally, the original database is a slave database; the archiving upper limit and archiving lower limit acquisition module is configured to: use the data archiving thread to read the archiving upper limit and the archiving lower limit of the current archive from the archiving position data table, wherein the archiving position data table is stored in the master database in the database cluster, and the archiving position data table is used to write the archiving upper limit and archiving lower limit for each archive.

[0038] Optionally, the target row data acquisition module is configured to: generate an archiving query statement based on the archiving upper limit and the archiving lower limit of the current archive; and use the archiving query statement to retrieve the target row data in the target data table whose time attribute value of the target column is between the archiving upper limit and the archiving lower limit of the current archive.

[0039] Optionally, the data processing device further includes: a verification upper limit and verification lower limit acquisition module, configured to acquire the verification upper limit and verification lower limit corresponding to the target data table, wherein the verification upper limit is used to indicate the termination verification point of the current verification in the target data table, and the verification lower limit is used to indicate the starting verification point of the current verification in the target data table; a comparison module, configured to compare the row data in the original database whose time attribute value of the corresponding target column is between the verification upper limit and the verification lower limit with the row data in the archived database whose time attribute value of the corresponding target column is between the verification upper limit and the verification lower limit, and obtain a comparison result; an archiving success determination module, configured to determine that the row data between the verification upper limit and the verification lower limit is successfully archived if the comparison result is consistent; and an archiving failure determination module, configured to determine that the row data between the verification upper limit and the verification lower limit fails to be archived if the comparison result is inconsistent.

[0040] Optionally, the verification upper limit and verification lower limit acquisition module is configured to: acquire a site that is less than or equal to the archiving lower limit as the verification upper limit of the current verification; and acquire the verification upper limit corresponding to the previous verification of the current verification as the verification lower limit of the current verification.

[0041] Optionally, the verification upper limit and verification lower limit acquisition module is configured to: acquire a position that is less than the archiving lower limit and is a distance from the archiving lower limit by a preset second time interval as the verification upper limit for this verification, wherein the data archived to the archiving database is in a readable state after the preset second time interval following the archiving time.

[0042] Optionally, the data processing device further includes: a cleanup upper limit and cleanup lower limit acquisition module, configured to acquire the cleanup upper limit and cleanup lower limit corresponding to the target data table, wherein the cleanup upper limit is used to indicate the termination cleanup point in the target data table for this cleanup, and the cleanup lower limit is used to indicate the starting cleanup point in the target data table for this cleanup; and a deletion module, configured to delete row data in the target data table whose corresponding target column time attribute value is between the cleanup upper limit and the cleanup lower limit.

[0043] Optionally, the cleanup upper limit and cleanup lower limit acquisition module is configured to: acquire the position less than or equal to the latest verification lower limit as the cleanup upper limit of the current cleanup; and acquire the cleanup upper limit corresponding to the previous cleanup of the current cleanup as the cleanup lower limit of the current cleanup.

[0044] Optionally, the cleanup upper limit and cleanup lower limit acquisition module is configured to: obtain the cleanup upper limit and cleanup lower limit corresponding to the target data table using a pre-set data cleanup thread in the original database; the deletion module is configured to: delete the row data in the target data table whose time attribute value of the corresponding target column is between the cleanup upper limit and the cleanup lower limit through the data cleanup thread.

[0045] Optionally, the target row data acquisition module is configured to: if the target row data is not acquired from the target data table, check whether the target row data exists in the target data table every preset period until the target row data is acquired, wherein the archiving service is in a dormant state during each preset period, and the archiving service is used to archive the data in the original database to the archive database.

[0046] Optionally, the data processing device further includes: an abnormal archive location recording module, configured to record the abnormal archive location in the target data table at the time of the abnormality in the case of an abnormality in the current archiving; and an archive service creation module, configured to create a new archive service and continue data archiving from the abnormal archive location in the target data table using the new archive service.

[0047] Optionally, the data processing device further includes: a row data sending module, configured to, in response to a data access request received from the client, send row data from the target data table that is requested by the data access request and whose time attribute value of the target column is greater than the archiving limit for this archiving to the client.

[0048] Optionally, the row data sending module is configured to: compare the first row of data in the target data table whose time attribute value of the target column is greater than the archiving limit of this archive with the second row of data in the target data table currently included in the archive database, and obtain a comparison result; if the comparison result indicates that there are duplicate row data between the first row of data and the second row of data, send the row data in the target data table requested by the data access request, and whose time attribute value of the target column is greater than the maximum time attribute value of the target column corresponding to the second row of data, to the client; if the comparison result indicates that there are no duplicate row data between the first row of data and the second row of data, send the row data in the target data table requested by the data access request, and whose time attribute value of the target column is greater than the archiving limit of this archive to the client.

[0049] According to a third aspect of the present disclosure, an electronic device is provided, comprising: a processor; and a memory for storing processor-executable instructions; wherein the processor is configured to execute the instructions to implement a data processing method according to the present disclosure.

[0050] According to a fourth aspect of the present disclosure, a computer-readable storage medium is provided that, when instructions in the computer-readable storage medium are executed by a processor of an electronic device, enables the electronic device to perform a data processing method according to the present disclosure.

[0051] According to a fifth aspect of the present disclosure, a computer program product is provided, including a computer program that, when executed by a processor, implements a data processing method according to the present disclosure.

[0052] The technical solutions provided by the embodiments of this disclosure have at least the following beneficial effects:

[0053] Because it records the upper and lower limits of archiving, and thus tracks the archiving progress, each archiving session doesn't need to start from the beginning, avoiding wasting significant time searching for unarchived data and improving archiving efficiency. Furthermore, by recording archiving progress, this disclosure supports pausing and restarting archiving tasks at any time. After restarting, the archiving service resumes execution from the last recorded archiving point, avoiding starting from scratch and effectively saving computational resources.

[0054] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this disclosure. Attached Figure Description

[0055] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure, and are not intended to unduly limit this disclosure.

[0056] Figure 1 This is a schematic diagram illustrating an archiving logic in related technologies;

[0057] Figure 2 This is a flowchart illustrating a data processing method according to an exemplary embodiment of the present disclosure;

[0058] Figure 3 This is a schematic diagram illustrating a data table according to an exemplary embodiment of the present disclosure;

[0059] Figure 4 This is a schematic diagram illustrating an archiving logic according to an exemplary embodiment of the present disclosure;

[0060] Figure 5 This is a block diagram illustrating a data processing apparatus according to an exemplary embodiment of the present disclosure;

[0061] Figure 6 This is a block diagram illustrating an electronic device according to an exemplary embodiment of the present disclosure. Detailed Implementation

[0062] To enable those skilled in the art to better understand the technical solutions of this disclosure, the technical solutions in the embodiments of this disclosure will be clearly and completely described below with reference to the accompanying drawings.

[0063] It should be noted that the terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this disclosure are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this disclosure described herein can be implemented in orders other than those illustrated or described herein. The embodiments described in the following examples do not represent all embodiments consistent with this disclosure. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this disclosure as detailed in the appended claims.

[0064] It should be noted that the phrase "at least one of several items" in this disclosure refers to three parallel cases: "any one of the several items", "a combination of any number of the several items", and "all of the several items". For example, "including at least one of A and B" includes the following three parallel cases: (1) including A; (2) including B; (3) including A and B. As another example, "performing at least one of step one and step two" indicates the following three parallel cases: (1) performing step one; (2) performing step two; (3) performing both step one and step two.

[0065] The database may contain infrequently used data, i.e., "cold data," which can be archived to nearline devices. Various archiving schemes exist, but the overall logic remains largely the same. (Refer to...) Figure 1 , Figure 1 This is a schematic diagram illustrating a type of archiving logic in related technologies. Figure 1 The document illustrates the source database and the archive database, illustrating the need to archive "cold data" from the source database to the archive database. The source database and the archive database may or may not be located on the same server; this disclosure does not impose any restrictions on this.

[0066] A complete archiving process can consist of three parts: data copying (i.e., data dumping), data verification, and data cleanup. "Data dumping" refers to reading the data to be archived from the original database and writing it to the archive database using specific data dumping logic, such as the statement "select * from jobs where create_time>'2022-05-25 11:23:19' and create_time<'2022-05-31 11:23:19').

[0067] "Data validation" refers to verifying the data being archived in the original database for this batch against the data being archived to the archive database to ensure data consistency. If the validation fails, it indicates that the archiving of this batch has failed. At this point, archiving can be terminated, or a retry mechanism can be initiated until the archiving validation passes. It should be noted that there is no fixed paradigm for data validation; it can be selected according to the actual scenario. For example, one can choose to validate single row data, validate batch data (checksum), validate specific fields, etc.

[0068] "Data cleaning" refers to removing data from the original database after it has been archived and validated, in order to free up space and maintain the performance of the original database. Generally, the larger the storage size of a database table, the worse the database performance.

[0069] In related technologies, there are four different archiving schemes: Archiving Scheme 1, Archiving Scheme 2, Archiving Scheme 3, and Archiving Scheme 4.

[0070] Archiving Strategies 1 involve manual archiving. First, table names can be directly modified. Then, new tables can be created to continue providing read and write services. Old data can be transferred to the archive database using a dump table. Once the data migration is complete, the tables in the original database can be deleted.

[0071] Archive Option 2 involves backing up the data using methods such as relational database management system dumps (MySQL dumps), then restoring the data to the archive database. Finally, the backed-up data is manually deleted.

[0072] Archiving scheme 3 involves archiving data using a third-party archiving tool. For example, a third-party archiving tool could be pt-archiver, which provides detailed archiving parameter specifications, including: archive batch size, archive interval, whether data validation is required, archive conditions, and so on.

[0073] Archiving Solution 4 requires a customized archiving system. Typically, a data dump SQL statement is written using scripts, such as "select * from jobs where create_time>'2022-05-25 11:23:19'and create_time<'2022-05-31 11:23:19'"; and a data cleanup SQL statement is written using scripts, such as "delete from jobswhere create_time>'2022-05-25 11:23:19'and create_time<'2022-05-31 11:23:19'". The archiving service is then executed periodically using a timer. Alternatively, a management user interface (UI) can be provided, offering a data dump SQL template, such as "select * from {table} where create_time>{start_time} and create_time<{end_time}", and a data cleanup SQL template, such as "delete from {table} where create_time>{start_time} and create_time<{end_time}". The content within the curly braces "{}" is the template, which can be dynamically populated based on different tables and filter conditions to generate the actual SQL. Furthermore, this can be triggered through a work order system, where users can submit archiving work orders as needed, and the archiving system can then execute the archiving process according to these work orders.

[0074] The drawbacks of archiving schemes 1 and 2 in the relevant technologies are that reading and writing services need to be interrupted during archiving, which has a significant impact on business operations.

[0075] Regarding archiving scheme 3 in the relevant technology, its disadvantages are:

[0076] 1. Archiving can only be done on the main database, which has a significant impact on online business.

[0077] 2. Pausing the archiving task is not supported. If the archiving task is forcibly stopped, it will not resume from the last interrupted archiving point when it is executed again. Instead, the archiving will be executed from the beginning, resulting in excessive consumption of computing resources.

[0078] 3. The selection of archive databases is limited. Archive databases only support relational data and cannot archive data to non-relational databases (NoSQL), such as HBase.

[0079] Regarding archiving scheme 4 in the relevant technology, its disadvantages are:

[0080] 1. It does not support pausing archiving tasks, meaning that archiving tasks cannot be paused anytime, anywhere. Pausing archiving tasks is a very necessary requirement, for example, in scenarios such as archiving system upgrades, archiving database downtime for maintenance, sudden traffic spikes, and holidays.

[0081] 2. Large-scale archive databases use segmentation logic to reduce the impact on the database. However, the segmentation methods used are relatively rudimentary, such as using fixed time slices or data volume assessment algorithms. The data volume assessment algorithm itself requires scanning all archived data to segment it, and this scanning process itself has a significant impact on the database. Furthermore, whether to segment depends on the user's assessment of the data size, which can lead to discrepancies between the decision and the actual data, making adaptive adjustment impossible. Moreover, this segmentation algorithm itself limits the solution to running on large archiving periods, such as daily, weekly, or monthly. The disadvantage of large-cycle archiving is the long archive cleanup cycle, resulting in significant fluctuations in the total amount of data in the table, thus causing fluctuations in table throughput and leading to database performance instability.

[0082] 3. The selection of archive databases is limited, supporting only real-time databases, meaning data must be readable immediately after being written. Some non-real-time databases, such as ClickHouse and Elastic Search, are difficult to use as archive databases. These non-real-time databases generally have high data compression capabilities, offering better economic and cost advantages, but data cannot be read immediately after being written, making real-time data verification impossible. Because archiving solution 4 in related technologies requires real-time data verification, it can only choose a real-time database as the archive database, resulting in a higher economic cost for this archiving solution.

[0083] 4. Data archiving generates operation logs. For example, in MySQL databases, data archiving produces binary logs (binlogs). These operation logs are then synchronized to the slave databases. This presents two drawbacks: First, if a large amount of data is archived, a large number of operation logs will be generated, increasing the latency between the master and slave databases. This can lead to situations where some data in the master database has been archived, but the slave database is not aware of this in time. Consequently, the slave database may push archived data—"cold data"—that should not be displayed to the client, resulting in "dirty reads." Furthermore, the greater the latency between the master and slave databases, the more severe the "dirty reads" will be, negatively impacting online business operations. Second, if a large number of operation logs are generated during the archiving process, downstream database services need to parse and filter these logs (i.e., large amounts of binlogs). This significantly increases the burden on downstream database services, resulting in excessive waste of computing resources.

[0084] To address the aforementioned issues in related technologies, the data processing method provided in this disclosure only synchronizes logs to the slave database when updating the upper and lower limits of the archive. Compared to the method in related technologies where a synchronization log is generated for each archived data, this disclosure significantly reduces the number of synchronization logs generated, effectively reducing latency between the master and slave databases, lowering the probability of reading invalid data, and reducing the occurrence of dirty reads.

[0085] Furthermore, because it records the upper and lower limits of archiving, and thus records the archiving progress, this disclosure supports pausing and restarting archiving tasks at any time. After the archiving service restarts, it will continue to execute the archiving task from the last recorded archiving point, avoiding starting from the beginning and effectively saving computing resources.

[0086] Furthermore, because the service progress is recorded, this disclosure can achieve independent and asynchronous execution of data archiving, data verification, and data cleaning. That is, it can decouple data archiving, data verification, and data cleaning, and does not force real-time data verification. In other words, the selection of the archiving database in this disclosure is not limited. It can choose a real-time database or a non-real-time database, that is, it can choose a database with lower cost, making the selection of the archiving database more flexible.

[0087] Furthermore, this disclosure does not rely on third-party components and tools, making it easy to deploy and maintain.

[0088] Furthermore, this disclosure enables real-time archiving without the need for periodic archiving jobs. It allows for one-time configuration and long-term operation, avoiding drastic fluctuations in online database services caused by large amounts of data to be archived during periodic archiving, and ensuring stable and uninterrupted database read and write efficiency.

[0089] Figure 2 This is a flowchart illustrating a data processing method according to an exemplary embodiment of the present disclosure.

[0090] Reference Figure 2 In step 201, the target data table to be archived can be obtained from the original database. The original database can be a database in a database cluster.

[0091] A data table can contain multiple intersecting rows and columns, as exemplified by [example table]. Figure 3 , Figure 3 This is a schematic diagram illustrating a data table according to an exemplary embodiment of the present disclosure. Wherein, Figure 3 The data table shown contains multiple columns, each named as follows: "Name", "Weight", "Birthday", "Height", "Occupation", etc. Figure 3 The data table shown also contains multiple rows, each containing detailed information for each individual, such as name, weight, date of birth, height, occupation, etc. The target data table can also have a target column indicating the time attribute value for each row of data.

[0092] According to an exemplary embodiment of this disclosure, the target column can be a column with a physical time attribute in the target data table. In this case, the target data table is configured with a column with a physical time attribute when it is created.

[0093] For example, return to reference Figure 3 , Figure 3 The "Birthday" column records a person's birth date, which has a physical time attribute. For example, some people are born earlier, and some are born later. Therefore, it can be... Figure 3 The "Birthday" column is used as the target column; and Figure 3 The four columns "Name", "Weight", "Height" and "Occupation" do not have physical time attributes, therefore, these four columns cannot be used as target columns.

[0094] According to an exemplary embodiment of this disclosure, the target column can be a timestamp hidden column representing the update time of each row of data in the target data table. That is, if the target data table is not configured with a column having a physical time attribute when it is created, the original database can configure a timestamp (update time) hidden column as the target column for the target data table. (Return to Reference) Figure 3 , Figure 3If any column in any row of data containing the five columns "Name", "Weight", "Birthday", "Height" and "Occupation" is changed, the original database will add a timestamp to that row, and the added timestamp will be the timestamp when any column in that row was changed.

[0095] For example, if Figure 3 If the "weight" column in the row containing the name "Zhang San" in the context of "Zhang San", "Li Si", and "Wang Er" is updated, then the timestamp of the entire row containing "Zhang San" will change from the previous time to the physical time when the "weight" column data was updated.

[0096] According to an exemplary embodiment of this disclosure, the target column may further be an identifier column indicating the writing order for each row of data in the target data table; that is, the target column may also be a column with a logical clock attribute. The identifier in the identifier column may be an auto-incrementing ID configured in the original database for a particular row of data when it is written to the original database. Furthermore, the auto-incrementing ID corresponding to the row of data in the target data table that is written to the original database first is less than the auto-incrementing ID corresponding to the row of data written to the original database later.

[0097] For example, suppose that initially, row a of data in the target data table is written to the original database first. The original database can then configure the auto-incrementing ID of row a to 1. Next, row b of data in the target data table is written to the original database. The original database can then configure the auto-incrementing ID of row b to 2. That is, the auto-incrementing ID configured for rows written later in the original database can be incremented based on the auto-incrementing ID configured for rows written earlier. Therefore, while the logical clock, i.e., the aforementioned auto-incrementing ID, cannot measure the actual difference in physical time and space, it can at least reflect the order in which an event occurred. Thus, the logical clock can be used as a basis for archiving decisions.

[0098] Furthermore, secondary indexes can be set for columns in the data table, establishing a mapping between column values ​​and row keys. This allows the archiving service to efficiently read archived data based on the archiving template. If the target column of the data table does not have a secondary index, it can be set using Data Definition Language (DDL) statements.

[0099] In step 202, the upper and lower limits of the archive limit for the target data table can be obtained. The upper limit indicates the termination point of the current archive within the target data table, and the lower limit indicates the starting point of the current archive within the target data table.

[0100] Furthermore, the upper limit of archiving can also be called the high watermark of archiving, which indicates that data below the high watermark can be archived; the lower limit of archiving can also be called the low watermark of archiving, which indicates the starting point of data archiving, that is, data below the low watermark of archiving belongs to data that has been archived and can be ignored in this archiving.

[0101] Furthermore, this disclosure can create a table to record archiving locations, namely an archiving location data table, which can be called a siteMarkTable. This siteMarkTable is used to record the upper and lower limits of archiving for each archiving session. Moreover, each data table can be bound to a single siteMarkTable, or all data tables in the original database can share a single siteMarkTable; this disclosure does not impose any restrictions on this.

[0102] It should be noted that you can also specify which column to use as the target column when creating a data table. Taking the creation of a data table in a relational database management system (MySQL) as an example, the following shows the code for specifying which column to use as the target column when creating a data table:

[0103] 1. CREATE TABLE t1(

[0104] 2. a bigint(20) NOT NULL,

[0105] 3.b int NOT NULL,

[0106] 4.ts bigint(20)UNSIGNED NOT NULL,

[0107] 5. PRIMARY KEY(a),

[0108] 6. KEY kb(b)

[0109] 7.) ENGINE = innodb

[0110] 8.COMMENT='ttl_duration=60;ttl_col=ts;';

[0111] The first line of code, "CREATE TABLE t1()", means: a data table named "t1" needs to be created.

[0112] The meaning of line 2, "a bigint(20)NOT NULL", is as follows: This data table has a column named "a", and the data type of this column is integer bigint. "NOT NULL" means that the column is required to be non-empty, that is, the column needs to be assigned a value when writing to the original database. If the column is not assigned a value when writing to the original database, the original database can reject it and prompt that the column needs to be assigned a value.

[0113] The meaning of line 3, "b int NOT NULL", is as follows: This table has a column named "b" with an integer data type (int). "NOT NULL" indicates that this column is required to be non-empty; that is, this column needs to be assigned a value when writing to the original database. If this column is not assigned a value when writing to the original database, the original database can reject the write and prompt that the column needs to be assigned a value.

[0114] The meaning of line 4, "ts bigint(20)UNSIGNED NOT NULL", is as follows: This data table has a column named "ts", whose data type is integer bigint. "UNSIGNED" means that the value of this column cannot be negative, and "NOT NULL" means that the column must not be empty. That is, this column needs to be assigned a value when writing to the original database. If this column is not assigned a value when writing to the original database, the original database can reject it and prompt that the column needs to be assigned a value.

[0115] The meaning of line 5, "PRIMARY KEY(a)", is: 'a' in the data table is the primary key;

[0116] The meaning of line 6, “KEY kb(b)”, is: b in the data table is a secondary index, and b is aliased as kb;

[0117] The meaning of line 7, “ENGINE=innodb”, is: the data table requires the InnoDB storage engine;

[0118] The meaning of line 8, "COMMENT='ttl_duration=60; ttl_col=ts;'", is: the target column specified in the data table is the "ts" column, and the data validity period is 60 seconds.

[0119] Alternatively, you can choose not to specify which column to use as the target column when creating the table. Instead, the database will automatically set a hidden column as the target column. This column is of type timestamp, meaning that each insert will automatically use the current time as the latest timestamp of the inserted row. The following example shows code for creating a table in a MySQL database without specifying which column to use as the target column during table creation:

[0120] 1. CREATE TABLE t1(

[0121] 2. a bigint(20) NOT NULL,

[0122] 3. PRIMARY KEY(a)

[0123] 4.) ENGINE = innodb

[0124] 5.COMMENT='ttl_duration=60;';

[0125] The meaning of each line of code in this section can be found in the explanation of the first section of code, and will not be repeated here.

[0126] According to an exemplary embodiment of this disclosure, an upper limit for the current archive can be generated at preset intervals based on the current physical time and a preset first time interval, and a lower limit for the current archive can be obtained at preset intervals. The lower limit for the current archive can be the upper limit of the previous archive.

[0127] For example, the difference between the current physical time and a preset first time interval can be obtained at preset intervals as the upper limit of the current archive, and the lower limit of the current archive can be obtained at preset intervals. The lower limit of the current archive is the upper limit of the previous archive, meaning the lower limit of the current archive is the upper limit corresponding to the previous archive at the end of the previous archive process. A background thread can be built into the original database to periodically calculate and update the archive high watermark according to the data validity period, i.e., the preset first time interval. The update cycle is usually very short, for example, 1 second, and this update cycle can be modified online. For example, if the data validity period of the two code snippets mentioned above is 60 seconds, then the difference between the current physical time and the 60-second validity period can be calculated every second as the aforementioned upper limit of the archive; that is, data more than 60 seconds before the current time at any given time can be considered archiveable data.

[0128] For example, the archiving task can continuously read the archiving high watermark and archiving low watermark from the siteMarkTable and generate a query statement that meets the archiving conditions. For instance, the generated query statement could be "select * from stock order by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". Here, 2022-05-06 11:00:00 is the archiving low watermark, and 2022-05-06 12:00:00 is the archiving high watermark. Furthermore, after archiving each batch of data, the archiving low watermark needs to be updated. In the example above, the latest archiving low watermark is 2022-05-06 12:00:00.

[0129] For example, the original database can also provide an interface for setting and modifying archiving conditions for data tables. Users can create an archived data definition through this interface. For instance, an archived data definition could be: "Archive data from one month ago." When parsing the archived data definition, it will be dynamically calculated based on the current physical time, converting the definition into a specific time point. For example, assuming the current time is 2022-06-06 12:00:00, then one month ago would be: 2022-05-06 12:00:00.

[0130] Furthermore, assuming the target column, i.e., the archiving detection column, is 'ts', the target data table is 'stock', the lower limit of this archiving, i.e., the archiving point, is 2022-05-06 11:00:00, and the specific time point obtained by parsing the archiving data definition, i.e., the upper limit of this archiving, is 2022-05-06 12:00:00, then the archiving service can generate a query statement that meets the archiving conditions as follows: "select * from stock order by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". In this way, the archiving service can use this SQL statement to query 1000 rows of data in the target data table that meet the archiving conditions and write them to the archiving database.

[0131] According to an exemplary embodiment of this disclosure, the target column can be an identifier column representing the writing order for each row of data in the target data table. When the determination of data validity is based on a logical clock rather than a timestamp, since the logical clock can only reflect the order in which an event occurs and cannot measure the actual difference in physical time and space, it is necessary to rely on external updates to the archive high-water mark for the logical clock. That is, a first identifier configured by the user can be obtained as the upper limit of the archive, and a second identifier configured by the user can be obtained as the lower limit of the archive, wherein the writing order corresponding to the second identifier is before the writing order corresponding to the first identifier.

[0132] For example, the first identifier, i.e., the first auto-incrementing ID, can be 100000; the second identifier, i.e., the second auto-incrementing ID, can be 99000. In this case, 1000 rows of data with corresponding auto-incrementing IDs between the second auto-incrementing ID 99000 and the first auto-incrementing ID 100000 can be archived to the archive database.

[0133] In step 203, target row data whose time attribute values ​​of the target column are between the upper and lower limits of the archive can be obtained from the target data table.

[0134] In step 204, the target row data can be archived to the archive database.

[0135] According to an exemplary embodiment of this disclosure, if the row data in the target column of the target data table whose time attribute value is between the upper limit of the current archive and the lower limit of the current archive exceeds a preset condition, the row data that meets the preset condition can be obtained starting from the lower limit of the current archive in the target data table and used as the target row data.

[0136] According to an exemplary embodiment of this disclosure, the preset conditions may include at least one of the following: the number of rows of row data is a preset number of rows, and the total number of bytes of row data is a preset first byte number.

[0137] For example, if the number of rows in the data is a preset number, and the number of rows between the upper and lower limits of the archiving limit is greater than the preset number, rows in the target data table whose corresponding target column values ​​are between the upper and lower limits and whose number is the preset number can be archived to the archiving database. For instance, if the number of rows between the upper and lower limits is greater than the preset number of 1000, this archiving can only archive the rows between the upper and lower limits that are the preset number of 1000 to the archiving database, while the data of other rows between the upper and lower limits, excluding the preset number of 1000, can be archived in the next archiving iteration. In this way, this disclosure can limit the maximum number of rows queried in a single archiving iteration, avoiding an excessive number of rows in a single archiving iteration that could affect online business.

[0138] For example, given the aforementioned predetermined condition that the total number of bytes of row data is a preset first byte number, if the total number of bytes of row data between the upper and lower archiving limits is greater than the preset first byte number, the row data in the target data table whose corresponding target column value is between the upper and lower archiving limits and whose total byte count is the preset first byte number can be archived to the archive database. The data in other rows between the upper and lower archiving limits, excluding the row data with the preset first byte number, can be reserved for future archiving. In this way, this disclosure can limit the maximum number of bytes in a single archive query, preventing excessive bytes in a single archive from impacting online services.

[0139] According to exemplary embodiments of this disclosure, a target lower bound for the next archive can also be generated. The target lower bound for the next archive can be represented by the maximum time attribute value of the target column corresponding to the target row data. Furthermore, a target upper bound for the next archive can also be generated. The target upper bound for the next archive can be the same as the upper bound for the current archive.

[0140] According to an exemplary embodiment of this disclosure, an archive query statement can be generated based on the upper limit and lower limit of the current archive. Then, the archive query statement can be used to retrieve target row data from the target data table whose time attribute values ​​in the target column fall between the upper and lower limits of the current archive.

[0141] For example, as before, the archiving service can generate a query statement that meets the archiving conditions as follows: "select * from stockorder by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". Assuming the maximum value of ts for this batch of archived data is 2022-05-06 11:31:00, after the data archiving is completed, the archiving low watermark needs to be updated, that is, the archiving point needs to be updated to 2022-05-06 11:31:00. This archiving point can be updated to the siteMarkTable as the starting point for the next archiving query.

[0142] According to an exemplary embodiment of this disclosure, when the current archived archive position reaches the target archived limit, or when the number of rows of row data between the current archived archive position and the target archived limit is less than a preset number of rows, the archived limit for the next archived archive can be generated based on the current physical time and a preset first time interval.

[0143] When performing the next archive query, two methods can be used. One method is to calculate the new archive limit in real time, i.e., the ts limit; the other method is to maintain the current archive limit unchanged, i.e., maintain the current archive limit as 2022-05-06 12:00:00, until the archive position reaches the current archive limit, or until the number of rows retrieved is less than the limit of 1000, then update the ts limit. In other words, the best strategy for updating the archive limit can be selected according to the characteristics of different databases, and this disclosure does not impose any restrictions on this.

[0144] According to an exemplary embodiment of this disclosure, if the target row data is not obtained from the target data table, the existence of the target row data in the target data table can be detected at preset periods until the target row data is obtained. During each preset period, the archiving service can be in a dormant state, and the archiving service is used to archive data from the original database to the archive database.

[0145] For example, the archiving task in this disclosure can be a continuously running task that will only terminate unless manually intervened. The archiving task can continuously read the data to be archived according to the strategy described above and then write it to the archiving database. To further reduce the pressure on the database, the archiving task can pause for a period of time when it finds no data that meets the archiving conditions; for example, the archiving task can pause for 100 milliseconds before continuing archiving. There can be various pausing strategies, which can be flexibly selected according to the actual situation. The aforementioned pausing method is merely an illustrative example.

[0146] According to an exemplary embodiment of this disclosure, target row data can be migrated to a message queue (MQ) so that when the message queue determines that the total number of bytes of the acquired target row data has reached a preset second byte count, it archives the target row data with the preset second byte count to an archive database. In this way, during data archiving, data can be written to the message queue first, and then consumed by a separate thread, allowing the message queue to aggregate larger batches of data written to the archive database, thereby improving the throughput of data writing to the archive database.

[0147] The archive database in this disclosure can be a single-machine database, such as MySQL; it can also be a distributed database, such as OceanBase, Clickhouse, TiDB, CockroachDB; or it can be a NoSQL database, such as HBase, ElasticSearch, etc. This disclosure does not impose any restrictions on it.

[0148] According to an exemplary embodiment of this disclosure, after the current archiving is completed, in response to a data access request received from the client, the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit for this archive, can be sent to the client. That is, after the current archiving is completed, the client can be prohibited from accessing row data where the column value of the corresponding target column is less than the archiving limit for this archive.

[0149] For example, the original database can proactively filter archived data through SQL rewriting, thereby eliminating duplicate data retrieved from both the original and archived databases. For instance, the original query might be: "select col_1,col_2 from office_dogs where col3<10 AND col3>2"; after rewriting by the database SQL executor, a statement can be added to filter archived data. For example, the rewritten statement could be: "select col_1,col_2 from office_dogs where col3<10 AND col3>2 AND [ttl_col]>[archived low watermark]". Furthermore, the SQL rewriting is transparent to the user. This ensures that expired data, i.e., data that has already been archived, is not returned to the client.

[0150] According to exemplary embodiments of this disclosure, since data archiving is performed in real time, it is possible that the obtained archiving upper limit may differ from the actual archiving position. For example, the currently obtained archiving upper limit may be 12:00, while the actual current archiving position may have already reached 12:01.

[0151] Therefore, the first row of data in the target data table of the original database whose time attribute value is greater than the archiving limit of this archive can be compared with the second row of data in the target data table currently included in the archive database to obtain the comparison result.

[0152] If the comparison result indicates that there are duplicate rows of data in the first and second rows, the row data requested by the data access request in the target data table, and whose time attribute value of the target column is greater than the maximum time attribute value of the target column corresponding to the second row, can be sent to the client.

[0153] It should be noted that the first and second rows of data contain overlapping rows, indicating that the aforementioned situation has occurred where "the currently obtained archiving limit is 12:00, but the actual current archiving point has already reached 12:01." In this case, the overlapping rows are those whose target column's time attribute value is between 12:00 and 12:01. To avoid returning already archived rows to the client, only the rows requested in the data access request, whose target column's time attribute value is greater than the maximum time attribute value of 12:01 for the target column corresponding to the second row of data, can be sent to the client.

[0154] If the comparison result indicates that there are no duplicate rows in the first and second rows, the rows in the target data table requested by the data access request, whose target column's time attribute value is greater than the current archiving limit, can be sent to the client. It should be noted that the absence of duplicate rows in the first and second rows indicates that the currently obtained archiving limit is exactly the current actual archiving point. Therefore, sending the rows in the target data table requested by the data access request, whose target column's time attribute value is greater than the current archiving limit, to the client ensures that data that has already been archived (i.e., expired data) will not be returned to the client.

[0155] According to an exemplary embodiment of this disclosure, the primary database can be the master database, and the database cluster can further include slave databases storing the target data table. After archiving the target row data to the archive database, the upper and lower archiving limits can be synchronized to the slave database, so that the slave database retrieves the target row data from the target data table and then archives the target row data to the archive database. In this way, this disclosure only synchronizes logs to the slave database when updating the upper and lower archiving limits. Compared to the method in related technologies where a synchronization log is generated for each archived data, this disclosure significantly reduces the number of synchronization logs generated, effectively reducing latency between the master and slave databases, lowering the probability of reading invalid data, and reducing the occurrence of dirty reads.

[0156] According to an exemplary embodiment of this disclosure, the primary database can be a secondary database. A data archiving thread can also be used to read the upper and lower limits of the current archive from the archive location data table. The archive location data table is stored in the primary database within the database cluster, and is used to write the upper and lower limits of each archive.

[0157] In this way, the upper and lower limits of archiving can be read from the archiving location data table contained in the master database through an independent data archiving thread. This further eliminates the need for synchronization logs generated when synchronizing the upper and lower limits of archiving from the master database to the slave database. In other words, this disclosure can completely avoid the occurrence of synchronization logs when archiving data, which can greatly reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0158] According to an exemplary embodiment of this disclosure, the upper and lower limits of the verification corresponding to the target data table can also be obtained. The upper limit of verification can be used to indicate the termination point of the current verification within the target data table, and the lower limit of verification can be used to indicate the starting point of the current verification within the target data table. Next, the rows in the original database whose time attribute values ​​of the corresponding target column are between the upper and lower limits of the verification can be compared with the rows in the archived database whose time attribute values ​​of the corresponding target column are between the upper and lower limits of the verification, to obtain a comparison result. If the comparison result is consistent, it can be determined that the rows between the upper and lower limits of the verification can be successfully archived. If the comparison result is inconsistent, it can be determined that the rows between the upper and lower limits of the verification can be archived unsuccessfully.

[0159] In this way, because the verification progress is recorded, this disclosure can enable data archiving, data verification, and data cleanup to be executed independently and asynchronously, that is, it can achieve decoupling of data archiving, data verification, and data cleanup, and the scheduling and management are more flexible.

[0160] According to an exemplary embodiment of this disclosure, since consistency verification is only required for data that has been archived, a position less than or equal to the lower limit of archiving can be obtained as the upper limit of verification for this verification. Alternatively, the upper limit of verification corresponding to the previous verification can be obtained as the lower limit of verification for this verification. In other words, the upper limit of verification corresponding to the previous verification when the previous verification process ended and indicated successful archiving can be obtained as the lower limit of verification for this verification. In other words, the upper limit of verification corresponding to the previous verification can be obtained as the starting point of verification for this verification, avoiding the need to traverse from the beginning for this verification and saving computational resources.

[0161] According to an exemplary embodiment of this disclosure, a position that is less than the lower archiving limit and is a predetermined second time interval away from the lower archiving limit can also be used as the upper verification limit for this verification. The data archived to the archive database is in a readable state after the predetermined second time interval following the archiving time.

[0162] It should be noted that if the archive database is not a real-time database, data archived to the archive database cannot be read immediately; it may take 2 seconds, 3 seconds, or even longer. Therefore, a position lower than the lower archive limit and a preset second time interval from the lower archive limit can be set as the upper limit for this verification. This ensures that the row data to be verified can be successfully read from the archive database during actual verification, avoiding situations where the row data to be verified cannot be read from the archive database, leading to verification failure.

[0163] According to an exemplary embodiment of this disclosure, the upper and lower limits of cleanup corresponding to the target data table can also be obtained. The upper limit of cleanup can be used to indicate the termination point of the current cleanup within the target data table, and the lower limit of cleanup can be used to indicate the starting point of the current cleanup within the target data table. Next, rows in the target data table whose time attribute values ​​of the corresponding target column fall between the upper and lower limits of cleanup can be deleted. Thus, because the cleanup progress is recorded, this disclosure enables independent and asynchronous execution of data archiving, data verification, and data cleanup, achieving decoupling of data archiving, data verification, and data cleanup, resulting in greater flexibility in scheduling and management.

[0164] According to an exemplary embodiment of this disclosure, since only data that passes verification can be cleared, a site less than or equal to the latest verification lower limit can be obtained as the upper limit of this clearing; the upper limit of the previous clearing can also be obtained as the lower limit of this clearing, that is, the upper limit of the previous clearing at the end of the previous clearing process can be obtained as the lower limit of this clearing, that is, the upper limit of the previous clearing can be obtained as the clearing starting point of this clearing, thus avoiding the need to traverse from the beginning of this clearing and saving computational resources.

[0165] According to an exemplary embodiment of this disclosure, a pre-built data cleanup thread within the original database can be used to obtain the upper and lower cleanup limits corresponding to the target data table. Next, the data cleanup thread can be used to delete rows in the target data table whose time attribute values ​​for the corresponding target column fall between the upper and lower cleanup limits.

[0166] This can be achieved by adding a data cleanup thread to the database. This thread reads the cleanup low-water mark and cleanup high-water mark from the siteMarkTable and cleans the data within the cleanup range defined by these two marks. Furthermore, after cleaning a batch of data, the cleanup low-water mark needs to be updated—that is, the cleanup low-water mark in the main database's siteMarkTable needs to be updated. This ensures that the next cleanup operation can start from the latest cleanup low-water mark, avoiding the need to traverse from the beginning and saving computational resources.

[0167] Furthermore, the upper and lower limits of cleanup can be read from the master database through a separate data cleanup thread, which can further eliminate the occurrence of synchronization logs generated when synchronizing the upper and lower limits of cleanup from the master database to the slave database. In other words, this disclosure can completely avoid the occurrence of synchronization logs when performing data cleanup, which can greatly reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0168] Reference Figure 4 , Figure 4 This is a schematic diagram illustrating an archiving logic according to an exemplary embodiment of the present disclosure. Figure 4 As can be seen, the archiving logic of this disclosure, compared with the archiving logic in related technologies, adds archiving point records, verification point records, and cleanup point records. Because it records the archiving progress, verification progress, and cleanup progress, this disclosure can achieve independent and asynchronous execution of data archiving, data verification, and data cleanup, thus achieving decoupling of data archiving, data verification, and data cleanup, and providing better flexibility in scheduling and management.

[0169] According to an exemplary embodiment of this disclosure, in the event of an anomaly during the current archiving process, the abnormal archiving point in the target data table at the time of the anomaly can be recorded. Then, a new archiving service can be created, and data archiving can be resumed from the abnormal archiving point in the target data table using the new archiving service.

[0170] It should be noted that the archiving service in this disclosure is a stateless service. If the archiving service encounters an anomaly, a new archiving service can be immediately started, and the previous archiving position can be read from the siteMarkTable to continue the archiving service. Furthermore, the archiving service, verification service, and cleanup service in this disclosure can be paused and restarted at any time. When the service restarts, the previous position can be read from the siteMarkTable to continue the service, avoiding the need to start the service from scratch and effectively saving computing resources.

[0171] Furthermore, this disclosure can dynamically adjust the archiving period and archiving size based on the real-time load of the database. For example, when the current access traffic to the database is high, the archiving period can be extended, for instance, from 1 second to 3 seconds; or, when the current access traffic to the database is high, the size of a single archive can be reduced. For instance, the maximum number of rows archived in a single archive can be reduced from 1000 to 500. In this way, by dynamically adjusting the archiving period and archiving size according to the real-time load of the database, when the current access traffic to the database is high, the archiving service can be prevented from preempting computing resources, thereby reducing the impact of the archiving service on online business.

[0172] Figure 5 This is a block diagram illustrating a data processing apparatus according to an exemplary embodiment of the present disclosure.

[0173] Reference Figure 5 The device 500 may include a target data table acquisition module 501, an archiving upper limit and archiving lower limit acquisition module 502, a target row data acquisition module 503, and an archiving module 504.

[0174] The target data table acquisition module 501 can acquire the target data table to be archived from the original database. The original database can be a database within a database cluster. The data table can contain multiple overlapping rows and columns, and the target data table can also have a target column indicating the time attribute value of each row of data.

[0175] According to an exemplary embodiment of this disclosure, the target column can be a column with a physical time attribute in the target data table. In this case, the target data table is configured with a column with a physical time attribute when it is created.

[0176] According to an exemplary embodiment of this disclosure, the target column can be a timestamp hidden column representing the time when each row of data in the target data table is updated. That is, if the target data table is not configured with a column having a physical time attribute when it is created, the original database can configure a timestamp (update time) hidden column as the target column for the target data table.

[0177] According to an exemplary embodiment of this disclosure, the target column may further be an identifier column indicating the writing order for each row of data in the target data table; that is, the target column may also be a column with a logical clock attribute. The identifier in the identifier column may be an auto-incrementing ID configured in the original database for a particular row of data when it is written to the original database. Furthermore, the auto-incrementing ID corresponding to the row of data in the target data table that is written to the original database first is less than the auto-incrementing ID corresponding to the row of data written to the original database later.

[0178] For example, suppose that initially, row a of data in the target data table is written to the original database first. The original database can then configure the auto-incrementing ID of row a to 1. Next, row b of data in the target data table is written to the original database. The original database can then configure the auto-incrementing ID of row b to 2. That is, the auto-incrementing ID configured for rows written later in the original database can be incremented based on the auto-incrementing ID configured for rows written earlier. Therefore, while the logical clock, i.e., the aforementioned auto-incrementing ID, cannot measure the actual difference in physical time and space, it can at least reflect the order in which an event occurred. Thus, the logical clock can be used as a basis for archiving decisions.

[0179] Furthermore, secondary indexes can be set for columns in the data table, establishing a mapping between column values ​​and row keys. This allows the archiving service to efficiently read archived data based on the archiving template. If the target column of the data table does not have a secondary index, it can be set using Data Definition Language (DDL) statements.

[0180] The archiving upper and lower limit acquisition module 502 can obtain the archiving upper and lower limits of the target data table. The archiving upper limit indicates the termination archiving position within the target data table for this archiving, and the archiving lower limit indicates the starting archiving position within the target data table for this archiving.

[0181] Furthermore, the upper limit of archiving can also be called the high watermark of archiving, which indicates that data below the high watermark can be archived; the lower limit of archiving can also be called the low watermark of archiving, which indicates the starting point of data archiving, that is, data below the low watermark of archiving belongs to data that has been archived and can be ignored in this archiving.

[0182] Furthermore, this disclosure can create a table to record archiving locations, namely an archiving location data table, which can be called a siteMarkTable. This siteMarkTable is used to record the upper and lower limits of archiving for each archiving session. Moreover, each data table can be bound to a single siteMarkTable, or all data tables in the original database can share a single siteMarkTable; this disclosure does not impose any restrictions on this.

[0183] According to an exemplary embodiment of this disclosure, the archiving upper limit and archiving lower limit acquisition module 502 can generate the archiving upper limit for the current archive at preset intervals based on the current physical time and a preset first time interval, and can acquire the archiving lower limit for the current archive at preset intervals. The archiving lower limit for the current archive can be the archiving upper limit of the previous archive.

[0184] For example, the difference between the current physical time and a preset first time interval can be obtained at preset intervals as the upper limit of the current archive, and the lower limit of the current archive can be obtained at preset intervals. The lower limit of the current archive is the upper limit of the previous archive, meaning the lower limit of the current archive is the upper limit corresponding to the previous archive at the end of the previous archive process. A background thread can be built into the original database to periodically calculate and update the archive high watermark according to the data validity period, i.e., the preset first time interval. The update cycle is usually very short, for example, 1 second, and this update cycle can be modified online. For example, if the data validity period of the two code snippets mentioned above is 60 seconds, then the difference between the current physical time and the 60-second validity period can be calculated every second as the aforementioned upper limit of the archive; that is, data more than 60 seconds before the current time at any given time can be considered archiveable data.

[0185] For example, the archiving task can continuously read the archiving high watermark and archiving low watermark from the siteMarkTable and generate a query statement that meets the archiving conditions. For instance, the generated query statement could be "select * from stock order by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". Here, 2022-05-06 11:00:00 is the archiving low watermark, and 2022-05-06 12:00:00 is the archiving high watermark. Furthermore, after archiving each batch of data, the archiving low watermark needs to be updated. In the example above, the latest archiving low watermark is 2022-05-06 12:00:00.

[0186] For example, the original database can also provide an interface for setting and modifying archiving conditions for data tables. Users can create an archived data definition through this interface. For instance, an archived data definition could be: "Archive data from one month ago." When parsing the archived data definition, it will be dynamically calculated based on the current physical time, converting the definition into a specific time point. For example, assuming the current time is 2022-06-06 12:00:00, then one month ago would be: 2022-05-06 12:00:00.

[0187] Furthermore, assuming the target column, i.e., the archiving detection column, is 'ts', the target data table is 'stock', the lower limit of this archiving, i.e., the archiving point, is 2022-05-06 11:00:00, and the specific time point obtained by parsing the archiving data definition, i.e., the upper limit of this archiving, is 2022-05-06 12:00:00, then the archiving service can generate a query statement that meets the archiving conditions as follows: "select * from stock order by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". In this way, the archiving service can use this SQL statement to query 1000 rows of data in the target data table that meet the archiving conditions and write them to the archiving database.

[0188] According to an exemplary embodiment of this disclosure, the target column can be an identifier column representing the writing order for each row of data in the target data table. When the determination of data validity is based on a logical clock rather than a timestamp, since the logical clock can only reflect the order in which an event occurs and cannot measure the actual difference in physical time and space, it is necessary to rely on external updates to the archive high-water mark for the logical clock. That is, a first identifier configured by the user can be obtained as the upper limit of the archive, and a second identifier configured by the user can be obtained as the lower limit of the archive, wherein the writing order corresponding to the second identifier is before the writing order corresponding to the first identifier.

[0189] For example, the first identifier, i.e., the first auto-incrementing ID, can be 100000; the second identifier, i.e., the second auto-incrementing ID, can be 99000. In this case, 1000 rows of data with corresponding auto-incrementing IDs between the second auto-incrementing ID 99000 and the first auto-incrementing ID 100000 can be archived to the archive database.

[0190] The target row data acquisition module 503 can obtain target row data from the target data table whose time attribute values ​​of the target column are between the upper and lower limits of the archive limit.

[0191] The archiving module 504 can archive the target row data to the archive database.

[0192] According to an exemplary embodiment of this disclosure, if the row data in the target data table whose time attribute value of the target column exceeds the upper limit of the current archive and the lower limit of the current archive exceeds a preset condition, the target row data acquisition module 503 can acquire the row data that meets the preset condition starting from the lower limit of the current archive in the target data table, and use it as the aforementioned target row data.

[0193] According to an exemplary embodiment of this disclosure, the preset conditions may include at least one of the following: the number of rows of row data is a preset number of rows, and the total number of bytes of row data is a preset first byte number.

[0194] For example, if the number of rows in the data is a preset number, and the number of rows between the upper and lower limits of the archiving limit is greater than the preset number, rows in the target data table whose corresponding target column values ​​are between the upper and lower limits and whose number is the preset number can be archived to the archiving database. For instance, if the number of rows between the upper and lower limits is greater than the preset number of 1000, this archiving can only archive the rows between the upper and lower limits that are the preset number of 1000 to the archiving database, while the data of other rows between the upper and lower limits, excluding the preset number of 1000, can be archived in the next archiving iteration. In this way, this disclosure can limit the maximum number of rows queried in a single archiving iteration, avoiding an excessive number of rows in a single archiving iteration that could affect online business.

[0195] For example, given the aforementioned predetermined condition that the total number of bytes of row data is a preset first byte number, if the total number of bytes of row data between the upper and lower archiving limits is greater than the preset first byte number, the row data in the target data table whose corresponding target column value is between the upper and lower archiving limits and whose total byte count is the preset first byte number can be archived to the archive database. The data in other rows between the upper and lower archiving limits, excluding the row data with the preset first byte number, can be reserved for future archiving. In this way, this disclosure can limit the maximum number of bytes in a single archive query, preventing excessive bytes in a single archive from impacting online services.

[0196] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include a target archiving lower limit generation module and a target archiving upper limit generation module. The target archiving lower limit generation module may also generate a target archiving lower limit for the next archive in the current archive. The target archiving lower limit for the next archive may be characterized by the maximum time attribute value of the target column corresponding to the target row data. Furthermore, the target archiving upper limit generation module may also generate a target archiving upper limit for the next archive in the current archive. The target archiving upper limit for the next archive may be the same as the archiving upper limit for the current archive.

[0197] According to an exemplary embodiment of this disclosure, the target row data acquisition module 503 can generate an archive query statement based on the upper limit and lower limit of the current archive. Next, the target row data acquisition module 503 can use the archive query statement to retrieve target row data in the target data table whose time attribute values ​​of the target column are between the upper limit and lower limit of the current archive.

[0198] For example, as before, the archiving service can generate a query statement that meets the archiving conditions as follows: "select * from stockorder by ts where ts<'2022-05-06 12:00:00' AND ts>='2022-05-06 11:00:00' limit 1000". Assuming the maximum value of ts for this batch of archived data is 2022-05-06 11:31:00, after the data archiving is completed, the archiving low watermark needs to be updated, that is, the archiving point needs to be updated to 2022-05-06 11:31:00. This archiving point can be updated to the siteMarkTable as the starting point for the next archiving query.

[0199] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include an archive limit generation module. When the archive point of the current archive reaches a target archive limit, or when the number of rows of data between the archive point of the current archive and the target archive limit is less than a preset number of rows, the archive limit generation module can generate the archive limit for the next archive of the current archive based on the current physical time and a preset first time interval.

[0200] When performing the next archive query, two methods can be used. One method is to calculate the new archive limit in real time, i.e., the ts limit; the other method is to maintain the current archive limit unchanged, i.e., maintain the current archive limit as 2022-05-06 12:00:00, until the archive position reaches the current archive limit, or until the number of rows retrieved is less than the limit of 1000, then update the ts limit. In other words, the best strategy for updating the archive limit can be selected according to the characteristics of different databases, and this disclosure does not impose any restrictions on this.

[0201] According to an exemplary embodiment of this disclosure, if no target row data is obtained from the target data table, the target row data acquisition module 503 can detect whether target row data exists in the target data table at preset periods until the target row data is obtained. During each preset period, the archiving service can be in a dormant state; the archiving service is used to archive data from the original database to the archive database.

[0202] For example, the archiving task in this disclosure can be a continuously running task that will only terminate unless manually intervened. The archiving task can continuously read the data to be archived according to the strategy described above and then write it to the archiving database. To further reduce the pressure on the database, the archiving task can pause for a period of time when it finds no data that meets the archiving conditions; for example, the archiving task can pause for 100 milliseconds before continuing archiving. There can be various pausing strategies, which can be flexibly selected according to the actual situation. The aforementioned pausing method is merely an illustrative example.

[0203] According to an exemplary embodiment of this disclosure, the archiving module 504 can migrate target row data to a message queue (MQ), so that when the message queue determines that the total number of bytes of the acquired target row data has reached a preset second byte count, it archives the target row data with the preset second byte count to the archive database. In this way, during data archiving, data can be written to the message queue first, and then consumed by a separate thread, allowing the message queue to aggregate larger batches of data written to the archive database, thereby improving the throughput of data writing to the archive database.

[0204] The archive database in this disclosure can be a single-machine database, such as MySQL; it can also be a distributed database, such as OceanBase, Clickhouse, TiDB, CockroachDB; or it can be a NoSQL database, such as HBase, ElasticSearch, etc. This disclosure does not impose any restrictions on it.

[0205] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include a row data sending module. After the current archiving is completed, in response to a data access request received from the client, the row data sending module may send the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit of this archive, to the client. That is, after the current archiving is completed, the client may be prohibited from accessing row data where the column value of the corresponding target column is less than the archiving limit of this archive.

[0206] For example, the original database can proactively filter archived data through SQL rewriting, thereby eliminating duplicate data retrieved from both the original and archived databases. For instance, the original query might be: "select col_1,col_2 from office_dogs where col3<10 AND col3>2"; after rewriting by the database SQL executor, a statement can be added to filter archived data. For example, the rewritten statement could be: "select col_1,col_2 from office_dogs where col3<10 AND col3>2 AND [ttl_col]>[archived low watermark]". Furthermore, the SQL rewriting is transparent to the user. This ensures that expired data, i.e., data that has already been archived, is not returned to the client.

[0207] According to exemplary embodiments of this disclosure, since data archiving is performed in real time, it is possible that the obtained archiving upper limit may differ from the actual archiving position. For example, the currently obtained archiving upper limit may be 12:00, while the actual current archiving position may have already reached 12:01.

[0208] Therefore, the row data sending module can compare the first row of data in the target data table of the original database, whose time attribute value of the target column is greater than the archiving limit of this archive, with the second row of data in the target data table currently contained in the archive database to obtain the comparison result.

[0209] If the comparison result indicates that there is duplicate data in the first row and the second row, the row data sending module can send the row data requested by the data access request in the target data table, and whose target column time attribute value is greater than the maximum time attribute value of the target column corresponding to the second row, to the client.

[0210] It should be noted that the first and second rows of data contain overlapping rows, indicating that the aforementioned situation has occurred where "the currently obtained archiving limit is 12:00, but the actual current archiving point has already reached 12:01." In this case, the overlapping rows are those whose target column's time attribute value is between 12:00 and 12:01. To avoid returning already archived rows to the client, only the rows requested in the data access request, whose target column's time attribute value is greater than the maximum time attribute value of 12:01 for the target column corresponding to the second row of data, can be sent to the client.

[0211] If the comparison result indicates that there are no duplicate rows in the first and second rows, the row data sending module can send the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the current archiving limit, to the client. It should be noted that the absence of duplicate rows in the first and second rows indicates that the currently obtained archiving limit is exactly the current actual archiving point. Therefore, the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the current archiving limit, can be sent to the client. At this point, it can be guaranteed that data that has already been archived, i.e., expired data, will not be returned to the client.

[0212] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include a synchronization module. The primary database may be a primary database, and the database cluster may further include a secondary database storing the target data table. After archiving the target row data to the archive database, the synchronization module may also synchronize the upper and lower archiving limits to the secondary database, so that the secondary database retrieves the target row data from the target data table and then archives the target row data to the archive database. In this way, this disclosure only synchronizes logs to the secondary database when updating the upper and lower archiving limits. Compared with the method in related technologies where a synchronization log is generated for each archived data, this disclosure significantly reduces the number of synchronization logs generated, effectively reducing the latency between the primary and secondary databases, reducing the probability of reading invalid data, and reducing the occurrence of dirty reads.

[0213] According to an exemplary embodiment of this disclosure, the primary database can be a secondary database. The archiving upper and lower limit acquisition module 502 can also utilize a data archiving thread to read the archiving upper and lower limits for the current archive from the archiving location data table. The archiving location data table is stored in the primary database within the database cluster, and is used to write the archiving upper and lower limits for each archive. In this way, the archiving upper and lower limits can be read from the archiving location data table contained in the primary database through an independent data archiving thread. This further eliminates the need for synchronization logs generated when synchronizing the archiving upper and lower limits from the primary database to the secondary database. In other words, this disclosure completely avoids the occurrence of synchronization logs during data archiving, significantly reducing latency between the primary and secondary databases, lowering the probability of reading invalid data, and reducing the occurrence of dirty reads.

[0214] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include a verification upper limit and verification lower limit acquisition module, a comparison module, an archiving success determination module, and an archiving failure determination module.

[0215] The verification upper and lower limit acquisition module can also obtain the verification upper and lower limits corresponding to the target data table. The verification upper limit indicates the termination point of the current verification within the target data table, and the verification lower limit indicates the starting point of the current verification within the target data table. Next, the comparison module compares the rows in the original database whose time attribute values ​​of the corresponding target column are between the verification upper and lower limits with the rows in the archived database whose time attribute values ​​of the corresponding target column are between the verification upper and lower limits, obtaining the comparison result. If the comparison result is consistent, the archiving success determination module determines that the rows between the verification upper and lower limits have been successfully archived. If the comparison result is inconsistent, the archiving failure determination module determines that the rows between the verification upper and lower limits have failed to be archived.

[0216] In this way, because the verification progress is recorded, this disclosure can enable data archiving, data verification, and data cleanup to be executed independently and asynchronously, that is, it can achieve decoupling of data archiving, data verification, and data cleanup, and the scheduling and management are more flexible.

[0217] According to an exemplary embodiment of this disclosure, since consistency verification is only required for data that has been archived, the upper and lower limit verification acquisition modules can obtain a position less than or equal to the lower limit of archiving as the upper limit of verification for this verification. The upper and lower limit verification acquisition modules can also obtain the upper limit of verification corresponding to the previous verification for this verification as the lower limit of verification for this verification. In other words, the upper limit of verification corresponding to the previous verification when the previous verification process ended and indicated successful archiving can be obtained as the lower limit of verification for this verification. In other words, the upper limit of verification corresponding to the previous verification can be obtained as the starting point of verification for this verification, avoiding the need to traverse from the beginning for this verification and saving computational resources.

[0218] According to an exemplary embodiment of this disclosure, the verification upper limit and verification lower limit acquisition module can also acquire a position that is less than the archiving lower limit and is a distance from the archiving lower limit by a preset second time interval as the verification upper limit for this verification. The data archived to the archive database is in a readable state after the preset second time interval following the archiving time.

[0219] It should be noted that if the archive database is not a real-time database, data archived to the archive database cannot be read immediately; it may take 2 seconds, 3 seconds, or even longer. Therefore, a position lower than the lower archive limit and a preset second time interval from the lower archive limit can be set as the upper limit for this verification. This ensures that the row data to be verified can be successfully read from the archive database during actual verification, avoiding situations where the row data to be verified cannot be read from the archive database, leading to verification failure.

[0220] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include a cleanup upper limit and cleanup lower limit acquisition module and a deletion module. The cleanup upper limit and cleanup lower limit acquisition module may also acquire the cleanup upper limit and cleanup lower limit corresponding to the target data table. The cleanup upper limit may be used to indicate the termination cleanup point within the target data table, and the cleanup lower limit may be used to indicate the starting cleanup point within the target data table. Next, the deletion module may delete rows in the target data table whose time attribute values ​​of the corresponding target column fall between the cleanup upper limit and the cleanup lower limit. Thus, because the cleanup progress is recorded, this disclosure enables independent and asynchronous execution of data archiving, data verification, and data cleanup, i.e., it achieves decoupling of data archiving, data verification, and data cleanup, resulting in better scheduling and management flexibility.

[0221] According to an exemplary embodiment of this disclosure, since only data that passes verification can be cleared, the upper and lower limit clearing module can obtain a position less than or equal to the latest verification lower limit as the upper limit of this clearing; it can also obtain the upper limit of the previous clearing as the lower limit of this clearing, that is, obtain the upper limit of the previous clearing at the end of the previous clearing process as the lower limit of this clearing, that is, obtain the upper limit of the previous clearing at the end of the previous clearing process as the lower limit of this clearing, that is, obtain the upper limit of the previous clearing as the starting point of this clearing, thus avoiding the traversal from the beginning of this clearing and saving computational resources.

[0222] According to an exemplary embodiment of this disclosure, the cleanup upper and lower limit acquisition module can use a pre-built data cleanup thread in the original database to obtain the cleanup upper and lower limits corresponding to the target data table. Next, the deletion module can use the data cleanup thread to delete rows in the target data table whose time attribute values ​​for the corresponding target column are between the cleanup upper and lower limits.

[0223] This can be achieved by adding a data cleanup thread to the database. This thread reads the cleanup low-water mark and cleanup high-water mark from the siteMarkTable and cleans the data within the cleanup range defined by these two marks. Furthermore, after cleaning a batch of data, the cleanup low-water mark needs to be updated—that is, the cleanup low-water mark in the main database's siteMarkTable needs to be updated. This ensures that the next cleanup operation can start from the latest cleanup low-water mark, avoiding the need to traverse from the beginning and saving computational resources.

[0224] Furthermore, the upper and lower limits of cleanup can be read from the master database through a separate data cleanup thread, which can further eliminate the occurrence of synchronization logs generated when synchronizing the upper and lower limits of cleanup from the master database to the slave database. In other words, this disclosure can completely avoid the occurrence of synchronization logs when performing data cleanup, which can greatly reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0225] According to an exemplary embodiment of this disclosure, the data processing apparatus 500 may further include an abnormal archive location recording module and an archive service creation module.

[0226] In the event of an anomaly during the current archiving process, the anomaly archiving location recording module can record the anomaly archiving location in the target data table at the time the anomaly occurred. Then, the archiving service creation module can create a new archiving service and continue data archiving from the anomaly archiving location in the target data table using the new service.

[0227] It should be noted that the archiving service in this disclosure is a stateless service. If the archiving service encounters an anomaly, a new archiving service can be immediately started, and the previous archiving position can be read from the siteMarkTable to continue the archiving service. Furthermore, the archiving service, verification service, and cleanup service in this disclosure can be paused and restarted at any time. When the service restarts, the previous position can be read from the siteMarkTable to continue the service, avoiding the need to start the service from scratch and effectively saving computing resources.

[0228] Furthermore, this disclosure can dynamically adjust the archiving period and archiving size based on the real-time load of the database. For example, when the current access traffic to the database is high, the archiving period can be extended, for instance, from 1 second to 3 seconds; or, when the current access traffic to the database is high, the size of a single archive can be reduced. For instance, the maximum number of rows archived in a single archive can be reduced from 1000 to 500. In this way, by dynamically adjusting the archiving period and archiving size according to the real-time load of the database, when the current access traffic to the database is high, the archiving service can be prevented from preempting computing resources, thereby reducing the impact of the archiving service on online business.

[0229] Figure 6 This is a block diagram illustrating an electronic device 600 according to an exemplary embodiment of the present disclosure.

[0230] Reference Figure 6 The electronic device 600 includes at least one memory 601 and at least one processor 602. The at least one memory 601 stores instructions that, when executed by the at least one processor 602, perform a data processing method according to an exemplary embodiment of the present disclosure.

[0231] As an example, electronic device 600 may be a PC, tablet, personal digital assistant, smartphone, or other device capable of executing the aforementioned instructions. Here, electronic device 600 is not necessarily a single electronic device, but may be a collection of any devices or circuits capable of executing the aforementioned instructions (or instruction sets) individually or in combination. Electronic device 600 may also be part of an integrated control system or system manager, or may be configured to interconnect with a portable electronic device locally or remotely (e.g., via wireless transmission) through an interface.

[0232] In electronic device 600, processor 602 may include a central processing unit (CPU), a graphics processing unit (GPU), a programmable logic device, a dedicated processor system, a microcontroller, or a microprocessor. By way of example and not limitation, processor may also include analog processors, digital processors, microprocessors, multi-core processors, processor arrays, network processors, etc.

[0233] The processor 602 can execute instructions or code stored in the memory 601, which can also store data. Instructions and data can also be sent and received via a network through a network interface device, which can employ any known transmission protocol.

[0234] The memory 601 may be integrated with the processor 602, for example, by placing RAM or flash memory within an integrated circuit microprocessor. Alternatively, the memory 601 may include a separate device, such as an external disk drive, a storage array, or other storage device that can be used by any database system. The memory 601 and the processor 602 may be operatively coupled, or may communicate with each other, for example, via I / O ports, network connections, etc., enabling the processor 602 to read files stored in the memory.

[0235] In addition, the electronic device 600 may also include a video display (such as a liquid crystal display) and a user interaction interface (such as a keyboard, mouse, touch input device, etc.). All components of the electronic device 600 can be interconnected via a bus and / or network.

[0236] According to exemplary embodiments of this disclosure, a computer-readable storage medium may also be provided, which, when executed by a processor of an electronic device, enables the electronic device to perform the aforementioned data processing method. Examples of computer-readable storage media include: read-only memory (ROM), random access programmable read-only memory (PROM), electrically erasable programmable read-only memory (EEPROM), random access memory (RAM), dynamic random access memory (DRAM), static random access memory (SRAM), flash memory, non-volatile memory, CD-ROM, CD-R, CD+R, CD-RW, CD+RW, DVD-ROM, DVD-R, DVD+R, DVD-RW, DVD+RW, DVD-RAM, BD-ROM, BD-R, BD-R LTH, BD-RE, Blu-ray or optical disc storage, hard disk drive (HDD), solid-state drive (SSD), card storage (such as multimedia cards, secure digital (SD) cards, or ultra-fast digital (XD) cards), magnetic tape, floppy disk, magneto-optical data storage device, optical data storage device, hard disk, solid-state drive, and any other device configured to store a computer program and any associated data, data files, and data structures in a non-transitory manner and to provide the computer program and any associated data, data files, and data structures to a processor or computer so that the processor or computer can execute the computer program. The computer program in the aforementioned computer-readable storage medium can run in an environment deployed in computer devices such as clients, hosts, agent devices, servers, etc. Furthermore, in one example, the computer program and any associated data, data files, and data structures are distributed across a networked computer system, such that the computer program and any associated data, data files, and data structures are stored, accessed, and executed in a distributed manner through one or more processors or computers.

[0237] According to exemplary embodiments of the present disclosure, a computer program product may also be provided, including a computer program that, when executed by a processor, implements the data processing method according to the present disclosure.

[0238] According to the data processing method, apparatus, electronic device, and storage medium disclosed herein, because the upper and lower limits for archiving are recorded, i.e., because the archiving progress is recorded, each archiving session does not need to start from the beginning, thus avoiding a significant amount of time wasted searching for data that has not been archived, thereby improving the efficiency of data archiving. Furthermore, because the archiving progress is recorded, this disclosure supports pausing and restarting archiving tasks at any time. After the archiving service restarts, it will resume execution from the recorded last archiving point, avoiding starting from the beginning and effectively saving computing resources.

[0239] Furthermore, logs are only synchronized to the slave database when the upper and lower limits of the archive are updated. Compared to the method in related technologies where a synchronization log is generated for each archived data, this disclosure generates a significantly smaller number of synchronization logs, which can effectively reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0240] Furthermore, because the service progress is recorded, this disclosure can achieve independent and asynchronous execution of data archiving, data verification, and data cleaning. That is, it can decouple data archiving, data verification, and data cleaning, and does not force real-time data verification. In other words, the selection of the archiving database in this disclosure is not limited. It can choose a real-time database or a non-real-time database, that is, it can choose a database with lower cost, making the selection of the archiving database more flexible.

[0241] Furthermore, the upper and lower limits of archiving can be read from the archiving location data table contained in the master database through an independent data archiving thread. This can further eliminate the occurrence of synchronization logs generated when the master database synchronizes the upper and lower limits of archiving to the slave database. In other words, this disclosure can completely avoid the occurrence of synchronization logs when performing data archiving, which can greatly reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0242] Furthermore, this ensures that the row data to be verified can be successfully read from the archive database during actual verification, avoiding situations where the verification fails due to the inability to read the row data to be verified from the archive database.

[0243] Furthermore, the upper and lower limits of cleanup can be read from the master database through a separate data cleanup thread, which can further eliminate the occurrence of synchronization logs generated when synchronizing the upper and lower limits of cleanup from the master database to the slave database. In other words, this disclosure can completely avoid the occurrence of synchronization logs when performing data cleanup, which can greatly reduce the latency between the master and slave databases, reduce the probability of reading invalid data, and reduce the occurrence of dirty reads.

[0244] Furthermore, this disclosure does not rely on third-party components and tools, making it easy to deploy and maintain.

[0245] Furthermore, this disclosure enables real-time archiving without the need for periodic archiving jobs. It allows for one-time configuration and long-term operation, avoiding drastic fluctuations in online database services caused by large amounts of data to be archived during periodic archiving, and ensuring stable and uninterrupted database read and write efficiency.

[0246] Furthermore, when archiving data, the data can be written to a message queue first, and then consumed by a separate thread. This allows the message queue to aggregate larger batches of data written to the archive database, thereby improving the throughput of data writing to the archive database.

[0247] Furthermore, rows in the target data table that are beyond the current archiving limit can be sent to the client. In other words, after the current archiving is complete, the client can be prevented from accessing rows in the target column whose values ​​are before the current archiving limit. This ensures that expired data, i.e., data that has already been archived, is not returned to the client.

[0248] Furthermore, by dynamically adjusting the archiving cycle and archiving scale based on the real-time load of the database, the archiving service can avoid competing for computing resources when the current access traffic to the database is high, thereby reducing the impact of the archiving service on online business.

[0249] Other embodiments of this disclosure will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This disclosure is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this disclosure are indicated by the following claims.

[0250] It should be understood that this disclosure is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this disclosure is limited only by the appended claims.

Claims

1. A data processing method, characterized in that, include: Obtain the target data table to be archived from the original database, wherein the original database is a database in a database cluster, and the target data table has a target column that indicates the time attribute value of each row of data; Obtain the upper and lower limits of the archived data table, wherein the upper and lower limits are used to represent the archived progress, the upper limit is used to indicate the termination archived position of the current archived data table in the target data table, and the lower limit is used to indicate the starting archived position of the current archived data table in the target data table; Obtain the target row data from the target data table whose time attribute value of the target column is between the upper limit of the archive and the lower limit of the archive; Archive the target row data to the archive database; The archiving cycle for each archive is dynamically adjusted based on the real-time load of the original database; the greater the real-time load, the longer the archiving cycle. The data processing method further includes: Obtain the upper and lower limits of the cleanup corresponding to the target data table, wherein the upper limit of the cleanup is used to indicate the termination point of the current cleanup in the target data table, and the lower limit of the cleanup is used to indicate the starting point of the current cleanup in the target data table; In the target data table, the row data whose time attribute value of the corresponding target column is between the cleanup upper limit and the cleanup lower limit is deleted, wherein the step of deleting the row data between the cleanup upper limit and the cleanup lower limit is executed independently and asynchronously with the step of archiving the target row data to the archive database.

2. The data processing method as described in claim 1, characterized in that, The target column is a column with a physical time attribute in the target data table, or the target column is a timestamp hidden column representing the time when each row of data in the target data table was updated.

3. The data processing method as described in claim 2, characterized in that, The step of obtaining the upper and lower limits of archiving for the target data table includes: Every preset period, an upper limit for the current archive is generated based on the current physical time and a preset first time interval, and a lower limit for the current archive is obtained every preset period, wherein the lower limit for the current archive is the upper limit for the previous archive.

4. The data processing method as described in claim 1, characterized in that, The target column is an identifier column set for each row of data in the target data table to indicate the order in which data was written. The step of obtaining the upper and lower limits of archiving for the target data table includes: Obtain a pre-configured first identifier as the upper limit of the archive, and obtain a pre-configured second identifier as the lower limit of the archive, wherein the write order corresponding to the second identifier is before the write order corresponding to the first identifier.

5. The data processing method as described in claim 1, characterized in that, The step of retrieving the target row data from the target data table, where the time attribute value of the target column is between the upper limit of the archive and the lower limit of the archive, includes: If the row data in the target column of the target data table whose time attribute value exceeds the upper limit of the current archive and the lower limit of the current archive exceeds a preset condition, then starting from the lower limit of the current archive, the row data that meets the preset condition is obtained and used as the target row data.

6. The data processing method as described in claim 5, characterized in that, The preset conditions include at least one of the following: The number of rows in the row data is the preset number of rows, and the total number of bytes in the row data is the preset first byte number.

7. The data processing method as described in claim 5, characterized in that, The data processing method further includes: Generate the target archiving lower limit for the next archiving of the current archive, wherein the target archiving lower limit is represented by the maximum time attribute value of the target column corresponding to the target row data; Generate the target archiving limit for the next archiving, wherein the target archiving limit is the same as the archiving limit for the current archiving.

8. The data processing method as described in claim 7, characterized in that, The data processing method further includes: If the current archive location reaches the target archive limit, or if the number of rows of data between the current archive location and the target archive limit is less than a preset number of rows, the archive limit for the next archive of the current archive is generated based on the current physical time and a preset first time interval.

9. The data processing method as described in claim 1, characterized in that, The step of archiving the target row data to the archive database includes: The target row data is migrated to a message queue so that when the message queue determines that the total number of bytes of the acquired target row data has reached a preset second byte number, the target row data with the total number of bytes of the preset second byte number is archived to the archive database.

10. The data processing method as described in claim 1, characterized in that, The original database is the master database, and the database cluster also includes slave databases that store the target data table; After archiving the target row data to the archive database, the method further includes: The upper limit and lower limit of archiving are synchronized to the slave database so that after the slave database obtains the target row data from the target data table, it archives the target row data to the archive database.

11. The data processing method as described in claim 1, characterized in that, The original database is a slave database; The step of obtaining the upper and lower limits of archiving for the target data table includes: The data archiving thread reads the upper limit and lower limit of the current archive from the archive location data table. The archive location data table is stored in the main database of the database cluster and is used to write the upper limit and lower limit of each archive.

12. The data processing method as described in claim 1, characterized in that, The step of retrieving the target row data from the target data table, where the time attribute value of the target column is between the upper limit of the archive and the lower limit of the archive, includes: Based on the upper limit and lower limit of the current archive, generate an archive query statement; The archive query statement retrieves the target row data whose time attribute value of the target column in the target data table is between the upper limit and the lower limit of the current archive.

13. The data processing method as described in claim 1, characterized in that, The data processing method further includes: Obtain the upper and lower limits of the verification corresponding to the target data table, wherein the upper limit of the verification is used to indicate the termination point of the current verification in the target data table, and the lower limit of the verification is used to indicate the starting point of the current verification in the target data table; The rows in the original database whose time attribute values ​​of the target column are between the upper and lower limits of the verification are compared with the rows in the archived database whose time attribute values ​​of the target column are between the upper and lower limits of the verification to obtain a comparison result. If the comparison results are consistent, the row data between the upper and lower limits of the verification is successfully archived. If the comparison results are inconsistent, the row data archiving between the upper and lower verification limits is determined to be unsuccessful.

14. The data processing method as described in claim 13, characterized in that, The step of obtaining the upper and lower limits of the verification corresponding to the target data table includes: The positions less than or equal to the lower limit of the archive are used as the upper limit of the current verification. Obtain the upper limit of the previous verification and use it as the lower limit of the current verification.

15. The data processing method as described in claim 14, characterized in that, The step of obtaining a site that is less than or equal to the lower limit of the archiving data as the upper limit of the current verification includes: The position that is less than the lower limit of archiving and is a distance of a preset second time interval from the lower limit of archiving is obtained as the upper limit of the current verification, wherein the data archived to the archive database is in a readable state after the preset second time interval after the time of archiving.

16. The data processing method as described in claim 1, characterized in that, The step of obtaining the upper and lower limits for cleaning up the target data table includes: The position less than or equal to the latest verification lower limit is taken as the upper limit of the current cleanup; Obtain the upper limit of the previous cleanup and use it as the lower limit of the current cleanup.

17. The data processing method as described in claim 1, characterized in that, The step of obtaining the upper and lower limits for cleaning up the target data table includes: The upper and lower limits of cleanup corresponding to the target data table are obtained by using the data cleanup thread pre-set in the original database. The step of deleting rows in the target data table whose time attribute values ​​for the corresponding target column are between the cleanup upper limit and the cleanup lower limit includes: The data cleaning thread deletes rows in the target data table whose time attribute values ​​for the corresponding target column are between the cleaning upper limit and the cleaning lower limit.

18. The data processing method as described in claim 1, characterized in that, The step of retrieving the target row data from the target data table, where the time attribute value of the target column is between the upper limit of the archive and the lower limit of the archive, includes: If the target row data is not obtained from the target data table, the system checks whether the target row data exists in the target data table every preset period until the target row data is obtained. During each preset period, the archiving service is in a dormant state. The archiving service is used to archive the data in the original database to the archive database.

19. The data processing method as described in claim 1, characterized in that, The data processing method further includes: In the event of an anomaly during this archiving process, the abnormal archiving location in the target data table at the time the anomaly occurred is recorded. Create a new archiving service and use the new archiving service to continue data archiving from the abnormal archiving point in the target data table.

20. The data processing method as described in claim 1, characterized in that, After archiving the target row data to the archive database, the method further includes: In response to a data access request received from the client, the row data requested by the data access request in the target data table, and whose time attribute value of the target column is greater than the archiving limit of this archive, is sent to the client.

21. The data processing method as described in claim 20, characterized in that, Sending the row data requested by the data access request in the target data table, where the time attribute value of the target column is greater than the archiving limit for this archive, to the client includes: The first row of data in the target data table whose time attribute value of the target column is greater than the archiving limit of this archive is compared with the second row of data in the target data table currently contained in the archive database to obtain the comparison result; If the comparison result indicates that there are duplicate rows of data in the first row and the second row, the row of data requested by the data access request in the target data table, and whose time attribute value of the target column is greater than the maximum time attribute value of the target column corresponding to the second row, is sent to the client. If the comparison result indicates that there are no duplicate rows of data between the first row and the second row, the row data requested by the data access request in the target data table, and whose time attribute value of the target column is greater than the archiving limit of this archive, is sent to the client.

22. A data processing apparatus, characterized in that, include: The target data table acquisition module is configured to acquire the target data table to be archived in the original database, wherein the original database is a database in the database cluster, and the target data table has a target column that indicates the time attribute value of each row of data; The archiving upper limit and archiving lower limit acquisition module is configured to acquire the archiving upper limit and archiving lower limit of the target data table, wherein the archiving upper limit and the archiving lower limit are used to represent the archiving progress, the archiving upper limit is used to indicate the termination archiving position of the current archiving in the target data table, and the archiving lower limit is used to indicate the starting archiving position of the current archiving in the target data table; The target row data acquisition module is configured to acquire target row data from the target data table whose time attribute value of the target column is between the upper limit of the archive and the lower limit of the archive; The archiving module is configured to archive the target row data to the archive database; The archiving cycle for each archive is dynamically adjusted based on the real-time load of the original database; the greater the real-time load, the longer the archiving cycle. The data processing device further includes: The upper and lower limit cleaning module is configured to obtain the upper and lower limits cleaning corresponding to the target data table, wherein the upper limit cleaning is used to indicate the termination cleaning point of the current cleaning in the target data table, and the lower limit cleaning is used to indicate the starting cleaning point of the current cleaning in the target data table; The deletion module is configured to delete row data in the target data table whose corresponding target column time attribute value is between the cleanup upper limit and the cleanup lower limit, wherein the step of deleting the row data between the cleanup upper limit and the cleanup lower limit is executed independently and asynchronously with the step of archiving the target row data to the archive database.

23. An electronic device, characterized in that, include: processor; Memory used to store the processor's executable instructions; The processor is configured to execute the instructions to implement the data processing method as described in any one of claims 1 to 21.

24. A computer-readable storage medium, characterized in that, When the instructions in the computer-readable storage medium are executed by the processor of the electronic device, the electronic device is able to perform the data processing method as described in any one of claims 1 to 21.

25. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the data processing method as described in any one of claims 1 to 21.

Images