Data table storage period determination method and apparatus, device, and storage medium

Abstract

The application provides a data table storage period determination method and device, equipment and a storage medium. It relates to the technical field of big data processing. The method comprises the following steps: obtaining the start date and end date of a plurality of sub-partitions of a target data table from the metadata table of a database, sequentially determining a first sub-partition in ascending order of partition time, and probing the data of the first sub-partition from the first end date to the first start date until the minimum storage date of the last piece of data is determined, sequentially determining a second sub-partition in descending order of partition time, and probing the data of the second sub-partition from the second start date to the second end date until the maximum storage date of the last piece of data is determined, and taking the minimum storage date to the maximum storage date as the actual storage period of the target data table, which can avoid full scanning of a large number of data files, so that the storage period can be obtained using fewer computing resources.

Description

Technical Field

[0001] This application relates to the field of big data processing technology, and in particular to a method, apparatus, device and storage medium for determining the storage period of a data table. Background Technology

[0002] In existing data platforms, large amounts of data are typically processed and stored in layers, requiring management of table storage periods. Current methods for collecting storage periods for large data tables often encounter issues. For some tables, only partial dates may be available, making accurate date information unavailable. In such cases, probing the original tables is necessary. Common techniques utilize the database's `count()` function to group and count dates within the table's date field, calculating the dates for which data exists. However, for large tables, this method requires scanning numerous data files, consuming significant computational resources, and may even fail to yield results due to resource constraints. Summary of the Invention

[0003] This application provides a method, apparatus, device, and storage medium for determining the storage period of a data table, aiming to at least partially solve one of the technical problems in the related art.

[0004] In a first aspect, this application provides a method for determining the storage period of a data table, comprising: obtaining the start date and end date of multiple sub-partitions of a target data table from the metadata table of a database; determining the first sub-partition in ascending order based on the partition time, and probing the data of the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined; determining the second sub-partition in descending order based on the partition time, and probing the data of the second sub-partition from the second start date to the second end date until the maximum storage date of the last data is determined; and using the minimum storage date to the maximum storage date as the actual storage period of the target data table.

[0005] Secondly, this application provides a data table storage period determination apparatus, comprising: an acquisition module, configured to acquire the start date and end date of multiple sub-partitions of a target data table from a metadata table of a database; a first probing module, configured to sequentially determine a first sub-partition based on the partition time in ascending order, and to probe the data of the first sub-partition from the first end date to the first start date, until the minimum storage date of the last data is determined; a second probing module, configured to sequentially determine a second sub-partition based on the partition time in descending order, and to probe the data of the second sub-partition from the second start date to the second end date, until the maximum storage date of the last data is determined; and a first determination module, configured to use the minimum storage date to the maximum storage date as the actual storage period of the target data table.

[0006] Thirdly, this application provides an electronic device, including: a processor; and a memory for storing processor-executable instructions; wherein the processor is configured to execute instructions to implement a data table storage cycle determination method.

[0007] Fourthly, this application provides a computer-readable storage medium that, when the instructions in the computer-readable storage medium are executed by the processor of an electronic device, enables the electronic device to execute a data table storage cycle determination method.

[0008] Fifthly, this application provides a computer program product, including a computer program, wherein the computer program is executed by a processor and a method for determining the storage cycle of a data table.

[0009] The data table storage period determination method, apparatus, device, and storage medium provided in this application obtain the start and end dates of multiple sub-partitions of the target data table from the database metadata table, determine the first sub-partition sequentially based on the partition time in ascending order, and probe the data of the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined. Then, the second sub-partition is determined sequentially based on the partition time in descending order, and probe the data of the second sub-partition from the second start date to the second end date until the maximum storage date of the last data is determined. The minimum storage date to the maximum storage date is used as the actual storage period of the target data table. This method can probe the storage period of the data table based on the information recorded in the database metadata, avoiding a full scan of a large number of data files, and thus obtaining the storage period with less computing resources. Attached Figure Description

[0010] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0011] Figure 1 This is a flowchart illustrating a method for determining the storage period of a data table according to the first embodiment of this application;

[0012] Figure 2 This is a flowchart illustrating a method for determining the storage period of a data table according to a second embodiment of this application;

[0013] Figure 3 This is a flowchart illustrating a method for determining the storage period of a data table according to the third embodiment of this application;

[0014] Figure 4 This is a block diagram of a data table storage period determination device according to this application;

[0015] Figure 5A block diagram of an exemplary electronic device suitable for implementing embodiments of the present application is shown.

[0016] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0017] The embodiments of this application are described in detail below, examples of which are illustrated in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain this application, and should not be construed as limiting this application. Rather, the embodiments of this application include all variations, modifications, and equivalents falling within the spirit and scope of the appended claims.

[0018] It should be noted that the execution subject of the data table storage period determination method in this embodiment can be a data table storage period determination device. This device can be implemented by software and / or hardware. This device can be configured in an electronic device, which may include, but is not limited to, a terminal, a server, etc.

[0019] Figure 1 This is a flowchart illustrating a method for determining the storage period of a data table according to the first embodiment of this application. This method can, for example, be executed by a workflow engine. Figure 1 As shown, the method includes:

[0020] S101: Retrieve the start and end dates of multiple sub-partitions of the target data table from the database's metadata table.

[0021] Among them, the large data table that requires calculation of the data storage period can be called the target data table. The target data table can store data generated within a certain period of time under any possible big data scenario, such as a large amount of commodity transaction data or a large amount of web browsing data, without any restrictions. In practical applications, the target data table is usually stored in partitions, using the data date as the partition key. That is, the target data table is divided into multiple sub-partitions according to the date, where each sub-partition contains data from 1 to N consecutive dates.

[0022] The metadata table is used to record basic information about each data table in the database, such as data table partition information. Partition information includes, for example, the start date and end date of the partition storage data, and there are no restrictions on this.

[0023] In this embodiment of the disclosure, the partition information of multiple sub-partitions of the target data table is first obtained from the metadata table, namely: the start date (START) and end date (END) of each sub-partition.

[0024] For example, taking the Greenplum database as an example, the partition information of the target data table in the Greenplum database is shown in the following format:

[0025] PARTITION partition_name1 START('2022-03-01'::date) END('2022-04-01'::date);

[0026] PARTITION partition_name 2 START('2022-04-01'::date) END('2022-05-01'::date);

[0027] PARTITION partition_name 3START('2022-05-01'::date)END('2022-06-01'::date);

[0028] The above partitioning information indicates that the target data table has three sub-partitions, including sub-partition 1, sub-partition 2, and sub-partition 3. Sub-partition 1 stores data from March 1, 2022 (inclusive) to April 1, 2022 (exclusive), meaning that the start and end dates for sub-partition 1 are March 1, 2022 and March 31, 2022, respectively. Similarly, the start and end dates for sub-partition 2 are April 1, 2022 and April 30, 2022, respectively; and the start and end dates for sub-partition 3 are May 1, 2022 and May 31, 2022, respectively.

[0029] S102: Determine the first sub-partition in ascending order based on the partition time, and explore the data in the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined.

[0030] As described in the background section, a data table (multiple sub-partitions) may only contain data for a portion of the dates, failing to provide accurate date information directly and requiring further investigation. Therefore, embodiments of this disclosure can investigate based on the start and end dates of data stored in each sub-partition within the metadata to determine the actual data storage period of the target data table.

[0031] Specifically, in this embodiment, the first sub-partition can be determined sequentially based on the partition time in ascending order, and the data in the first sub-partition can be explored from the first end date to the first start date until the minimum storage date of the last piece of data is determined.

[0032] In this implementation, the partitions are ordered by time, meaning multiple sub-partitions are sorted in ascending order based on the time the data was stored. This embodiment selects a sub-partition as the first sub-partition based on this order. That is, the sub-partition with the earliest time is selected as the first sub-partition. Then, the data in this sub-partition is explored to determine the earliest date the data was stored; this date is called the minimum storage date. If the minimum storage date cannot be found in the earliest sub-partition (the sub-partition is empty), the next sub-partition is selected as the first sub-partition and explored again, and so on, until the minimum storage date is found. The start date corresponding to the first sub-partition is called the first start date, and the end date is called the first end date.

[0033] In this embodiment, during the exploration of each determined first sub-partition, a forward exploration approach can be adopted, that is, exploring from the first end date to the first start date. In practical applications, an initial exploration date is first set, namely, the first end date (END). Using `select 1 from sub-partition table where date = exploration date`, it checks whether there is data at the first end date. If there is data on that date, the exploration date is changed to the day before the first end date, and so on, until a date with no data is found. The day after that date with no data is the last data record, and the storage date of this data record is recorded as the minimum storage date. If no data is found at the first end date of the current first sub-partition, it means that the sub-partition is empty. In this case, the next partition of that sub-partition is taken as the first sub-partition and the exploration is restarted, and so on, until the minimum storage date among multiple sub-partitions is found. It is understood that the dates on which data is stored in each sub-partition table are consecutive.

[0034] For example, the aforementioned sub-partitions include sub-partition 1, sub-partition 2, and sub-partition 3, and the partition time is sorted in ascending order as follows: sub-partition 1, sub-partition 2, and sub-partition 3. In this case, this embodiment first uses sub-partition 1 as the first sub-partition, and then probes from the first end date (2022-03-31) of sub-partition 1 to the first start date (2022-03-01) using the select method. The `FROM subpartition1 table WHERE date = 2022-03-31 (probe date)` statement checks if there is data on this first end date. If there is data, the probe date is changed to the day before 2022-03-31 (i.e., 2022-03-30), and so on, until the minimum storage date of the earliest stored data in subpartition1 is determined. For example, if there is no data on 2022-03-14 but data on 2022-03-15, then 2022-03-15 is used as the minimum storage date. If there is no data on 2022-03-31, then subpartition2 is used as the first subpartition and probed. If there is no data on the end date of subpartition2, then subpartition3 is used as the first subpartition and probed, until the minimum storage date is determined. The probing method for subpartitions 2 and 3 is the same as that for subpartition 1, and there are no restrictions on this.

[0035] S103: Determine the second sub-partition in descending order of partition time, and explore the data in the second sub-partition from the second start date to the second end date until the maximum storage date of the last data is determined.

[0036] In this implementation, the partition time descending order means that multiple subpartitions are sorted in descending order of the time the data was stored. This embodiment can sequentially select a subpartition as the second subpartition based on this order. That is, the subpartition with the latest time is first selected as the second subpartition. Then, the data in this subpartition is explored to determine the date of the last stored data in that subpartition; this date is called the maximum storage date. If the maximum storage date cannot be found in the latest subpartition (the subpartition is empty), the next subpartition is selected as the second subpartition and explored again, and so on, until the maximum storage date of the target data table is found. The start date corresponding to the second subpartition is called the second start date, and the end date is called the second end date.

[0037] In this embodiment, during the exploration of each determined second sub-partition, a backward exploration approach can be adopted, that is, exploring from the second start date to the second end date. In practical application, first, an initial exploration date is set, namely, the second start date (START). By using `select 1 from sub-partition table where date = exploration date`, it is checked whether there is data on the second start date. If there is data on that date, the exploration date is changed to the day after the second start date, and so on, until a date with no data is found. The day before the date with no data is the last data record, and the storage date of that data record is recorded as the maximum storage date. If no data is found on the second start date of the current second sub-partition, it means that the sub-partition is empty. In this case, the next partition of the sub-partition is taken as the second sub-partition and the exploration is restarted, and so on, until the maximum storage date among multiple sub-partitions is found.

[0038] For example, the above-mentioned multiple sub-partitions include sub-partition 1, sub-partition 2, and sub-partition 3. The partition time sorting result in descending order is: sub-partition 3, sub-partition 2, and sub-partition 1. In this scenario, this embodiment first designates sub-partition 3 as the second sub-partition. Then, it probes from the second start date (2022-05-1) to the second end date (2022-05-31) of sub-partition 3. It uses `select 1 from sub-partition 1 table where date = 2022-05-1 (probing date)` to check if there is data on the second start date. If there is data, the probing date is changed to the day after 2022-05-1 (i.e., 2022-05-2), and so on, until the maximum storage date of the last stored data in sub-partition 3 is determined. For example, if there is no data on 2022-05-14 but data on 2022-05-13, then 2022-05-13 is set as the maximum storage date. If there is no data on 2022-05-1, sub-partition 2 is designated as the second sub-partition and probed. If there is no data on the start date of sub-partition 2, sub-partition 1 is designated as the second sub-partition and probed, until the maximum storage date is determined. The exploration methods for sub-partition 2 and sub-partition 1 are the same as those for sub-partition 3, and there are no restrictions on them.

[0039] In some implementations, special dates defined in the table, such as the beginning of the month, the end of the month, and the beginning of the year, are excluded during the exploration process.

[0040] S104: Use the minimum storage date to the maximum storage date as the actual storage period for the target data table.

[0041] In other words, the minimum storage date (e.g., 2022-03-15) to the maximum storage date (e.g., 2022-05-13) is used as the actual storage period for the target data table.

[0042] It should be noted that the acquisition, storage, and application of data and information involved in the technical solution of this application all comply with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0043] In this embodiment, the start and end dates of multiple sub-partitions of a target data table are obtained from the database's metadata table. The first sub-partition is determined sequentially in ascending order of partition time. The data in the first sub-partition is explored from its first end date to its first start date until the minimum storage date of the last data record is determined. The second sub-partition is determined sequentially in descending order of partition time. The data in the second sub-partition is explored from its second start date to its second end date until the maximum storage date of the last data record is determined. The storage period from the minimum storage date to the maximum storage date is used as the actual storage period of the target data table. This method can explore the storage period of the data table based on the information recorded in the database metadata, avoiding a full scan of a large number of data files, and thus obtaining the storage period with less computing resources.

[0044] Figure 2 This is a flowchart illustrating a method for determining the storage period of a data table according to the second embodiment of this application, as shown below. Figure 2 As shown, the method includes:

[0045] S201: Retrieve the start and end dates of multiple sub-partitions of the target data table from the database's metadata table.

[0046] S202: Determine the first sub-partition in ascending order based on the partition time, and explore the data in the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined.

[0047] S203: Determine the second sub-partition in descending order of partition time, and explore the data in the second sub-partition from the second start date to the second end date until the maximum storage date of the last data is determined.

[0048] S204: Use the minimum storage date to the maximum storage date as the actual storage period for the target data table.

[0049] For detailed explanations of S201-S204, please refer to the above embodiments, which will not be repeated here.

[0050] S205: Determine the start date and end date of the demand storage cycle.

[0051] After determining the actual storage period of the target data table (March 15, 2022 to May 13, 2022), this embodiment can further clean up the data in the target data table according to the actual needs of the data, that is: clean up overdue data.

[0052] Specifically, this embodiment can determine the start date and end date of the required storage cycle.

[0053] The period during which the demand data is stored is called the demand storage period. The start date of this demand storage period is called the demand start date, and the end date is called the demand end date. For example, a user needs data stored in the target data table from April 1, 2022 to April 30, 2022, that is: the demand start date is April 1, 2022, and the demand end date is April 30, 2022.

[0054] In some embodiments, each required storage cycle can be encoded, and each cycle encoding includes:

[0055] A 1-digit data retention period identifier, divided into:

[0056] F: Permanent preservation identifier;

[0057] L: Non-permanent storage identifier;

[0058] A 2-digit metric identifier, where the first digit can be any of the following:

[0059] Y: Year identifier;

[0060] Q: Season indicator;

[0061] M: Month symbol;

[0062] P: Ten-day period indicator;

[0063] W: Weekly identifier;

[0064] D: Day identifier;

[0065] Secondary identifiers can be any of the following:

[0066] B: Initial identifier;

[0067] E: End identifier;

[0068] G: Weekday indicator;

[0069] X: Weekend indicator;

[0070] S: Specifies the date identifier;

[0071] A: Whole identifier;

[0072] A 4-digit measurement identifier, with values ​​ranging from 0000 to 9999;

[0073] In this embodiment, the encoding rule for each required storage period is: data retention period identifier + measurement identifier + metering identifier. For example, FYB9999 indicates that the data on the first day of each year needs to be permanently retained, and LME0036 indicates that the data at the end of 36 months needs to be retained. Furthermore, multiple required storage period encoding segments can be combined. For example, LME0036LDA0030 indicates that the data at the end of 36 months + the data of each day on the 30th day needs to be retained.

[0074] In this embodiment, during the process of determining the start date and end date of the demand storage cycle, the start date of the demand and at least one cycle code can be directly obtained; furthermore, the end date of the demand is determined based on the start date of the demand and the cycle code.

[0075] For example, if the demand start date is 2022-04-01 and the period code is LDA0030, then this embodiment can determine the demand end date as 2022-04-30. That is to say, the demand storage period is from 2022-04-01 to 2022-04-30.

[0076] S206: In response to a minimum storage date being less than the demand start date and / or a maximum storage date being greater than the demand end date, clean up the data in the target data table from the minimum storage date to the demand start date and / or from the demand end date to the maximum storage date.

[0077] Furthermore, this embodiment can compare the required storage period with the actual storage period to determine whether there is any overdue data where the actual storage period exceeds the required storage period. Specifically, it determines whether the minimum storage date is less than the start date of the requirement and / or whether the maximum storage date is greater than the end date of the requirement. If the minimum storage date (March 15, 2022) is less than the start date of the requirement (April 1, 2022) and / or the maximum storage date (May 13, 2022) is greater than the end date of the requirement (April 30, 2022), the data in the target data table from the minimum storage date to the start date of the requirement and / or from the end date of the requirement to the maximum storage date is cleaned. That is, the data from March 15, 2022 to April 1, 2022, and / or April 30, 2022 to May 13, 2022 in the target data table is cleaned. For example, the cleaned data is transferred to the archive area. In addition, this embodiment does not clean data on special dates (such as the beginning of the month, the end of the month, or the end of the year) during the cleanup process.

[0078] Therefore, this embodiment can clean up overdue data to obtain the required data, and can directly calculate and clean up overdue data using periodic coding.

[0079] This embodiment of the disclosure obtains the start and end dates of multiple sub-partitions of a target data table from the database's metadata table. The first sub-partition is determined sequentially in ascending order of partition time. The data in the first sub-partition is then probed from its first end date to its first start date until the minimum storage date of the last data record is determined. The second sub-partition is then determined sequentially in descending order of partition time. The data in the second sub-partition is then probed from its second start date to its second end date until the maximum storage date of the last data record is determined. The storage period from the minimum storage date to the maximum storage date is used as the actual storage period of the target data table. This allows for probing the storage period of the data table based on information recorded in the database metadata, avoiding a full scan of a large number of data files, thus obtaining the storage period with less computational resources. Furthermore, this embodiment can clean up expired data to obtain the required data, and can directly calculate and clean up expired data using periodic encoding.

[0080] Figure 3 This is a flowchart illustrating a method for determining the storage period of a data table according to the third embodiment of this application, as shown below. Figure 3 As shown, the method includes:

[0081] S301: Retrieve the start and end dates of multiple sub-partitions of the target data table from the database's metadata table.

[0082] S302: Determine the first sub-partition in ascending order based on the partition time, and explore the data in the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined.

[0083] S303: Determine the second sub-partition in descending order of partition time, and explore the data in the second sub-partition from the second start date to the second end date until the maximum storage date of the last data is determined.

[0084] S304: Use the minimum storage date to the maximum storage date as the actual storage period for the target data table.

[0085] For detailed descriptions of S301-S304, please refer to the above embodiments, which will not be repeated here.

[0086] S305: In response to the current time reaching the update time and the minimum storage date having no data, update the minimum storage date.

[0087] In practical applications, the target data table will add new data or clean up old data over time, so the actual storage period may change. Therefore, embodiments of this disclosure can also periodically update the minimum storage date.

[0088] Specifically, in this embodiment, the update time can be preset, for example, once every 24 hours. When the current time reaches the update time, it is further determined whether the minimum storage date needs to be updated. If there is still data on the minimum storage date in the target data table, then the minimum storage date does not need to be updated. However, if there is no data on the minimum storage date, then the minimum storage date is updated. In other words, if the current time reaches the update time and there is no data on the minimum storage date, then the minimum storage date is updated.

[0089] The absence of data on the minimum storage date can be categorized into two scenarios: either the first sub-partition containing the minimum storage date does not exist, or the data on the minimum storage date has been cleared.

[0090] In some embodiments, if a first subpartition containing the minimum storage date exists (i.e., the data with the original minimum storage date has been cleaned up), this embodiment can probe backwards from the minimum storage date to the first end date for the data in the first subpartition until the storage date of the first piece of data is determined as the updated minimum storage date.

[0091] For example, if the minimum storage date is 2022-03-15, and the first sub-partition containing this date is sub-partition 1, then in this embodiment, if sub-partition 1 exists, it can probe the data in sub-partition 1 from the minimum storage date (i.e., 2022-03-15) to the first end date (2022-03-31) to determine the first piece of data. For example, if the first piece of data is stored on 2022-03-25, then the storage date of this data is used as the updated minimum storage date; that is, the new minimum storage date is 2022-03-25. It is understood that if sub-partition 1 has no data, then the next partition is used as the first sub-partition, and the new first sub-partition is probed, and so on, until the storage date of the first piece of data is determined as the updated minimum storage date. The probing process is the same as in the first embodiment described above, and will not be repeated here.

[0092] In other embodiments, if the first subpartition (subpartition 1) containing the minimum storage date (2022-03-15) does not exist, i.e., subpartition 1 is cleaned up, and the target data table has a new subpartition at the current time, this embodiment determines a subpartition as the third subpartition based on the current partition time in ascending order. The start date for storing data in this third subpartition is called the third start date, and the end date is called the third end date. Further, the data in the third subpartition is probed from the third end date to the third start date. If there is data in the third subpartition, the probe continues until the last piece of data in the sequence is found, i.e., the earliest stored data in the third subpartition, and the storage time of this data is used as the updated minimum storage date. If no data is found in the third subpartition, the next subpartition at the current time is used as the third subpartition and probed until the storage time of the earliest piece of data among the multiple subpartitions at the current time is determined, and this storage time is used as the updated minimum storage date.

[0093] For example, if the current time's multiple sub-partitions are in ascending order as sub-partition 2, sub-partition 3, and sub-partition 4, this example first treats sub-partition 2 as the third sub-partition. Then, it probes each date sequentially from the third end date to the third start date of sub-partition 2 until the very last piece of data is found, and the storage date of that data is used as the updated minimum storage date. If sub-partition 2 has no data, the next sub-partition (i.e., sub-partition 3) is treated as the third sub-partition and probed again. Similarly, if sub-partition 3 has no data, the next sub-partition (i.e., sub-partition 4) is treated as the third sub-partition and probed again, and so on, until the storage date of the last piece of data in the current third sub-partition from the third end date to the third start date is determined as the updated minimum storage date. The specific probing process is the same as the probing process for the first sub-partition in the above embodiment, and there are no limitations on it.

[0094] S306: In response to the current time reaching the update time and data being available on the next date after the maximum storage date, the maximum storage date is updated.

[0095] Furthermore, the maximum storage date can be updated periodically in this embodiment.

[0096] Specifically, in this embodiment, the update time can be preset, for example, once every 24 hours. When the current time reaches the update time, it is further determined whether the maximum storage date needs to be updated. If there is still no data on the next date after the maximum storage date, then the maximum storage date does not need to be updated. However, if there is data on the next date after the maximum storage date, then the maximum storage date is updated. In other words, if the current time reaches the update time and there is data on the next date after the maximum storage date, then the maximum storage date is updated.

[0097] The presence of data on the next date after the maximum storage date can be categorized into two scenarios. The first scenario is that if there is no data on the second end date of the second sub-partition containing the maximum storage date, the updated data remains in the current second sub-partition. The second scenario is that there is data on the second end date of the second sub-partition containing the maximum storage date, indicating that the updated data exceeds the current second sub-partition and may be stored in the next partition. Therefore, this embodiment can employ different update methods for different scenarios.

[0098] In some embodiments, if there is no data on the second end date, this embodiment can probe the data in the second sub-partition from the next date after the maximum storage date to the second end date until the storage date of the last piece of data is determined as the updated maximum storage date.

[0099] For example, the maximum storage date is 2022-05-13, and the second sub-partition is sub-partition 3 (the second end date is 2022-05-31). If there is no data on the second end date, this embodiment can start probing sub-partition 3 from the next date after 2022-05-13 (i.e., 2022-05-14) to 2022-05-31 until the storage date of the last piece of data is determined, for example, 2022-05-25. Then, this storage date is used as the updated maximum storage date.

[0100] In other embodiments, if data is available on the second end date, this embodiment can determine the descending order of multiple sub-partitions at the current time (i.e., the current partition time in descending order), and sequentially designate each sub-partition as the fourth sub-partition based on the current partition time in descending order. The start date for storing data in the fourth sub-partition is called the fourth start date, and the end date is called the fourth end date. Further, the data in the fourth sub-partition is probed from the fourth start date to the fourth end date until the last piece of data is determined, and the storage date of that data is used as the updated maximum storage date. If there is no data on the fourth start date of the current fourth sub-partition, the next sub-partition is designated as the fourth sub-partition and probed, and so on, until the maximum storage date of the last piece of data is determined.

[0101] For example, if the current descending order of multiple sub-partitions is: sub-partition 4, sub-partition 3, sub-partition 2, and sub-partition 1, in this embodiment, sub-partition 4 is first designated as the fourth sub-partition. Then, the data in sub-partition 4 is probed from its start date to its end date. By using `select 1 from sub-partition 4 table where date = fourth start date (probing date)`, it is checked whether there is data at the fourth start date. If there is data, the probing date is modified to the day after the fourth start date, and so on, until the storage date of the last piece of data in sub-partition 4 is determined as the maximum storage date for the update. If there is no data at the fourth start date, sub-partition 3 is designated as the fourth sub-partition and probed. If there is no data at the start date of sub-partition 3, sub-partition 2 is designated as the fourth sub-partition and probed, until the storage date of the last piece of data is determined as the updated maximum storage date.

[0102] Therefore, the embodiments of this disclosure can also periodically update the actual storage period of the target data table to ensure the real-time nature of the storage period.

[0103] This embodiment of the disclosure obtains the start and end dates of multiple sub-partitions of a target data table from the database's metadata table. The first sub-partition is determined sequentially in ascending order of partition time. The data in the first sub-partition is then probed from its first end date to its first start date until the minimum storage date of the last data record is determined. The second sub-partition is then determined sequentially in descending order of partition time. The data in the second sub-partition is then probed from its second start date to its second end date until the maximum storage date of the last data record is determined. The storage period from the minimum storage date to the maximum storage date is used as the actual storage period of the target data table. This allows for the exploration of the data table's storage period based on information recorded in the database metadata, avoiding a full scan of a large number of data files and thus obtaining the storage period with less computing resources. Furthermore, this embodiment of the disclosure can periodically update the actual storage period of the target data table to ensure the real-time nature of the storage period.

[0104] Figure 4 This is a block diagram of a data table storage period determination device according to this application, such as... Figure 4 As shown, the data table storage cycle determination device 40 includes:

[0105] The acquisition module 401 is used to obtain the start and end dates of multiple sub-partitions of the target data table from the metadata table of the database;

[0106] The first exploration module 402 is used to determine the first sub-partition in ascending order based on the partition time, and to explore the data of the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined.

[0107] The second probing module 403 is used to sequentially determine the second sub-partition based on the partition time in descending order, and to probe the data in the second sub-partition from the second start date to the second end date until the maximum storage date of the last piece of data is determined; and

[0108] The first determining module 404 is used to determine the minimum storage date to the maximum storage date as the actual storage period of the target data table.

[0109] In some embodiments, the apparatus 40 further includes: a second determining module for determining the demand start date and demand end date of the demand storage period; and a cleaning module for cleaning up data in the target data table from the minimum storage date to the demand start date, and / or from the demand end date to the maximum storage date, in response to the minimum storage date being less than the demand start date and / or the maximum storage date being greater than the demand end date.

[0110] In some embodiments, the apparatus 40 further includes: a first update module, configured to update the minimum storage date in response to the current time reaching the update time and the minimum storage date having no data; and / or a second update module, configured to update the maximum storage date in response to the current time reaching the update time and the next date after the maximum storage date having data.

[0111] In some embodiments, the first update module is specifically configured to: in response to the existence of a first sub-partition containing the minimum storage date, probe the data of the first sub-partition from the minimum storage date to the first end date until the storage date of the first piece of data is determined as the updated minimum storage date; or in response to the absence of a first sub-partition containing the minimum storage date, determine a third sub-partition in ascending order based on the current partition time, and probe the data of the third sub-partition from the third end date to the third start date until the storage date of the last piece of data is determined as the updated minimum storage date.

[0112] In some embodiments, the second update module is specifically used for: if there is no data on the second end date, probing the data in the second sub-partition from the next date after the maximum storage date toward the second end date until the storage date of the last piece of data is determined as the updated maximum storage date; if there is data on the second end date, determining the fourth sub-partition in descending order of the current partition time, and probing the data in the fourth sub-partition from the fourth start date to the fourth end date until the storage date of the last piece of data is determined as the updated maximum storage date.

[0113] In some embodiments, the second determining module is specifically used to: obtain the demand start date and at least one period code, wherein each period code includes: a 1-digit data retention period identifier, which is divided into a permanent retention identifier or a non-permanent retention identifier; a 2-digit measurement identifier, the first digit of which is divided into any of the following: year identifier, quarter identifier, month identifier, ten-day identifier, week identifier, day identifier, and the second digit of which is divided into any of the following: beginning identifier, end identifier, working day identifier, weekend identifier, specified date identifier, and whole number identifier; a 4-digit measurement identifier, which is a number ranging from 0000 to 9999; and determine the demand end date based on the demand start date and the period code.

[0114] In this embodiment, the start and end dates of multiple sub-partitions of the target data table are obtained from the database's metadata table. The first sub-partition is determined sequentially based on the ascending order of partition time. The data in the first sub-partition is explored from the first end date to the first start date until the minimum storage date of the last data is determined. The second sub-partition is determined sequentially based on the descending order of partition time. The data in the second sub-partition is explored from the second start date to the second end date until the maximum storage date of the last data is determined. The storage period from the minimum storage date to the maximum storage date is taken as the actual storage period of the target data table. This method can explore the storage period of the data table based on the information recorded in the database metadata, avoiding a full scan of a large number of data files, thus obtaining the storage period with less computing resources.

[0115] According to embodiments of this application, this application also provides an electronic device, a readable storage medium, and a computer program product.

[0116] Figure 5 This is a block diagram of an electronic device according to this application. For example, electronic device 500 may be a mobile phone, computer, digital broadcasting terminal, messaging device, game console, tablet device, medical device, fitness equipment, personal digital assistant, etc.

[0117] Reference Figure 5The electronic device 500 may include one or more of the following components: processing component 502, memory 504, power supply component 506, multimedia component 508, audio component 510, input / output (I / O) interface 512, sensor component 514, and communication component 516.

[0118] Processing component 502 typically controls the overall operation of electronic device 500, such as operations associated with display, telephone calls, data communication, camera operation, and recording. Processing component 502 may include one or more processors 520 to execute instructions to perform all or part of the steps of the methods described above. Furthermore, processing component 502 may include one or more modules to facilitate interaction between processing component 502 and other components. For example, processing component 502 may include a multimedia module to facilitate interaction between multimedia component 508 and processing component 502.

[0119] Memory 504 is configured to store various types of data to support the operation of electronic device 500. Examples of such data include instructions for any application or method operating on electronic device 500, contact data, phonebook data, messages, pictures, videos, etc. Memory 504 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk.

[0120] Power supply component 506 provides power to various components of electronic device 500. Power supply component 506 may include a power management system, one or more power supplies, and other components associated with generating, managing, and distributing power to electronic device 500.

[0121] Multimedia component 508 includes a touch display screen that provides an output interface between electronic device 500 and user. In some embodiments, the touch display screen may include a liquid crystal display (LCD) and a touch panel (TP). The touch panel includes one or more touch sensors to sense touches, swipes, and gestures on the touch panel. The touch sensors may sense not only the boundaries of touch or swipe actions but also the duration and pressure associated with the touch or swipe operation. In some embodiments, multimedia component 508 includes a front-facing camera and / or a rear-facing camera. When electronic device 500 is in an operating mode, such as a shooting mode or video mode, the front-facing camera and / or rear-facing camera may receive external multimedia data. Each front-facing camera and rear-facing camera may be a fixed optical lens system or have focal length and optical zoom capabilities.

[0122] Audio component 510 is configured to output and / or input audio signals. For example, audio component 510 includes a microphone (MIC) configured to receive external audio signals when electronic device 500 is in an operating mode, such as call mode, recording mode, and voice recognition mode. The received audio signals may be further stored in memory 504 or transmitted via communication component 516.

[0123] In some embodiments, the audio component 510 further includes a speaker for outputting audio signals.

[0124] I / O interface 512 provides an interface between processing component 502 and peripheral interface modules, such as keyboards, click wheels, buttons, etc. These buttons may include, but are not limited to, home buttons, volume buttons, power buttons, and lock buttons.

[0125] Sensor assembly 514 includes one or more sensors for providing state assessments of various aspects of electronic device 500. For example, sensor assembly 514 may detect the on / off state of electronic device 500, the relative positioning of components such as the display and keypad of electronic device 500, changes in position of electronic device 500 or a component of electronic device 500, the presence or absence of user contact with electronic device 500, orientation or acceleration / deceleration of electronic device 500, and temperature changes of electronic device 500. Sensor assembly 514 may include a proximity sensor configured to detect the presence of nearby objects without any physical contact. Sensor assembly 514 may also include a light sensor, such as a CMOS or CCD image sensor, for use in imaging applications. In some embodiments, sensor assembly 514 may also include an accelerometer, gyroscope, magnetometer, pressure sensor, or temperature sensor.

[0126] Communication component 516 is configured to facilitate wired or wireless communication between electronic device 500 and other devices. Electronic device 500 can access wireless networks based on communication standards, such as WiFi, 2G, or 3G, or combinations thereof. In one exemplary embodiment, communication component 516 receives broadcast signals or broadcast-related information from an external broadcast management system via a broadcast channel. In one exemplary embodiment, communication component 516 also includes a near-field communication (NFC) module to facilitate short-range communication. For example, the NFC module may be implemented based on radio frequency identification (RFID) technology, Infrared Data Association (IrDA) technology, ultra-wideband (UWB) technology, Bluetooth (BT) technology, and other technologies.

[0127] In an exemplary embodiment, the electronic device 500 may be implemented by one or more application-specific integrated circuits (ASICs), digital signal processors (DSPs), digital signal processing devices (DSPDs), programmable logic devices (PLDs), field-programmable gate arrays (FPGAs), controllers, microcontrollers, microprocessors, or other electronic components to perform the above-described data table storage cycle determination method.

[0128] In an exemplary embodiment, a computer-readable storage medium including instructions is also provided, such as a memory 504 including instructions, which can be executed by a processor 520 of an electronic device 500 to perform the above-described method. Optionally, the computer-readable storage medium may be a ROM, random access memory (RAM), CD-ROM, magnetic tape, floppy disk, and optical data storage device, etc.

[0129] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application 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 application are indicated by the following claims.

[0130] It should be understood that this application is not limited to the precise structure 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 application is limited only by the appended claims.

Claims

1. A method for determining the storage period of a data table, characterized in that, include: Retrieve the start and end dates of multiple sub-partitions of the target data table from the database's metadata table; The first sub-partition is determined sequentially based on the partition time in ascending order. The data in the first sub-partition is explored from the first end date to the first start date until the minimum storage date of the last data is determined. Specifically, the sub-partition with the earliest time is first selected as the first sub-partition. Then, the data in the first sub-partition is explored to determine the date of the earliest data storage in the first sub-partition as the minimum storage date. The second sub-partition is determined sequentially in descending order of partition time. Then, the data in the second sub-partition is explored from its second start date to its second end date until the maximum storage date of the last piece of data is determined. Specifically, the sub-partition with the latest time is first designated as the second sub-partition. Then, the data in the second sub-partition is explored to determine the date of the last stored data in the second sub-partition, which is then used as the maximum storage date. The minimum storage date to the maximum storage date is used as the actual storage period of the target data table. When the current time reaches the update time and there is no data on the minimum storage date, the minimum storage date is updated; and / or when the current time reaches the update time and there is data on the next date of the maximum storage date, the maximum storage date is updated.

2. The method according to claim 1, characterized in that, After setting the minimum storage date to the maximum storage date as the actual storage period of the target data table, the method further includes: Determine the start and end dates of the demand storage cycle; and In response to the minimum storage date being less than the demand start date, and / or the maximum storage date being greater than the demand end date, the data in the target data table from the minimum storage date to the demand start date, and / or from the demand end date to the maximum storage date is cleaned up.

3. The method according to claim 1, characterized in that, Updating the minimum storage date includes: In response to the existence of a first sub-partition containing the minimum storage date, probe the data in the first sub-partition from the minimum storage date to the first end date until the storage date of the first piece of data is determined as the updated minimum storage date; or In response to the absence of a first sub-partition containing the minimum storage date, a third sub-partition is determined sequentially based on the current partition time in ascending order. The data in the third sub-partition is then probed from the third end date to the third start date until the storage date of the last piece of data is determined as the updated minimum storage date.

4. The method according to claim 1, characterized in that, Updating the maximum storage date includes: If there is no data on the second end date, the data in the second sub-partition is probed from the next date after the maximum storage date toward the second end date until the storage date of the last piece of data is determined as the updated maximum storage date; If there is data on the second end date, the fourth sub-partition is determined sequentially in descending order of the current partition time, and the data in the fourth sub-partition is explored from the fourth start date to the fourth end date until the storage date of the last piece of data is determined as the updated maximum storage date.

5. The method according to claim 2, characterized in that, The determination of the start date and end date of the demand storage period includes: Obtain the start date of the demand and at least one cycle code, wherein each cycle code includes: A 1-digit data retention period identifier, which can be either a permanent retention identifier or a non-permanent retention identifier; The two-digit measurement identifier has the first digit divided into any of the following: year, quarter, month, ten-day period, week, or day; and the second digit divided into any of the following: beginning of the week, end of the week, weekday, weekend, specified date, or whole day. A 4-digit measurement identifier, with values ​​ranging from 0000 to 9999; and The end date of the demand is determined based on the demand start date and the cycle code.

6. A device for determining the storage period of a data table, characterized in that, include: The retrieval module is used to retrieve the start and end dates of multiple sub-partitions of a target data table from the database's metadata table; The first exploration module is used to determine the first sub-partition in ascending order based on the partition time, and to explore the data in the first sub-partition from the first end date to the first start date until the minimum storage date of the last data is determined. The sub-partition with the earliest time is first taken as the first sub-partition, and then the data in the first sub-partition is explored to determine the date of the earliest data storage in the first sub-partition as the minimum storage date. The second probing module is used to determine the second sub-partitions sequentially based on the partition time in descending order, and to probe the data in the second sub-partitions from the second start date to the second end date until the maximum storage date of the last piece of data is determined. Specifically, the sub-partition with the latest time is first selected as the second sub-partition, and then the data in the second sub-partition is probed to determine the date of the last stored data in the second sub-partition as the maximum storage date; and The first determining module is used to take the minimum storage date to the maximum storage date as the actual storage period of the target data table; The device further includes: The first update module is configured to update the minimum storage date in response to the current time reaching the update time and the minimum storage date having no data; and / or The second update module is used to update the maximum storage date in response to the current time reaching the update time and the existence of data on the next date after the maximum storage date.

7. The apparatus according to claim 6, characterized in that, The device further includes: The second determining module is used to determine the start date and end date of the demand storage cycle; and The cleanup module is used to clean up the data in the target data table from the minimum storage date to the start date of the demand, and / or from the end date of the demand to the maximum storage date, in response to the minimum storage date being less than the demand start date and / or the maximum storage date being greater than the demand end date.

8. The apparatus according to claim 6, characterized in that, The first update module is specifically used for: In response to the existence of a first sub-partition containing the minimum storage date, the data in the first sub-partition is probed from the minimum storage date to the first end date until the storage date of the first piece of data is determined as the updated minimum storage date; or In response to the absence of a first sub-partition containing the minimum storage date, a third sub-partition is determined sequentially based on the current partition time in ascending order. The data in the third sub-partition is then probed from the third end date to the third start date until the storage date of the last piece of data is determined as the updated minimum storage date.

9. The apparatus according to claim 6, characterized in that, The second update module is specifically used for: If there is no data on the second end date, the data in the second sub-partition is probed from the next date after the maximum storage date toward the second end date until the storage date of the last piece of data is determined as the updated maximum storage date; If there is data on the second end date, the fourth sub-partition is determined sequentially in descending order of the current partition time, and the data in the fourth sub-partition is explored from the fourth start date to the fourth end date until the storage date of the last piece of data is determined as the updated maximum storage date.

10. The apparatus according to claim 7, characterized in that, The second determining module is specifically used for: Obtain the start date of the demand and at least one cycle code, wherein each cycle code includes: A 1-digit data retention period identifier, which can be either a permanent retention identifier or a non-permanent retention identifier; The two-digit measurement identifier has the first digit divided into any of the following: year, quarter, month, ten-day period, week, or day; and the second digit divided into any of the following: beginning of the week, end of the week, weekday, weekend, specified date, or whole day. A 4-digit measurement identifier, with values ​​ranging from 0000 to 9999; and The end date of the demand is determined based on the demand start date and the cycle code.

11. An electronic device, characterized in that, include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in any one of claims 1-5.

12. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1-5.

13. A computer program product, characterized in that, Includes a computer program that, when executed by a processor, implements the method of any one of claims 1-5.

Images