Method, system, and apparatus for data management in data warehouse, and device and medium

By adopting a computing storage separation architecture in the data warehouse, we will address the problem of segmented key range disorder of files in the data warehouse, and through merger sorting and splitting technology, the problem of low query efficiency is solved, achieving more efficient query performance and reasonable merger manage.

WO2024239898A1PCT designated stage expired Publication Date: 2024-11-28ALIBABA CLOUD COMPUTING CO LTD

Patent Information

Application Number
PCT/CN2024/089662
Authority / Receiving Office
WO · WO
Patent Type
Applications
Current Assignee / Owner
Priority Date
2023-05-24
Filing Date
2024-04-24
Publication Date
2024-11-28

AI Technical Summary

Technical Problem

The segmentation key range of files in the data warehouse is out of order as the data increases, deletes and changes are caused, resulting in a decrease in query efficiency and difficulty in responding in a timely manner.

Method used

Using the computing storage separation architecture, the files with overlapping are determined from the storage layer as source files, and the files with overlapping are sorted, and the files are split into output files without overlapping are rewrite to the storage layer to eliminate the files. The bond ranges overlap.

Benefits of technology

Improve query performance, reduce the number of files that need to be accessed during query, reasonably control the benefits and overhead brought by mergers, and prevent the proportion of benefits and overhead is too low.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN2024089662_28112024_PF_FP_ABST
    Figure CN2024089662_28112024_PF_FP_ABST
Patent Text Reader

Abstract

Provided is a method for data management in a data warehouse. Data records in the data warehouse are partitioned into a plurality of files based on a specified partition key and stored in a storage layer of the data warehouse, and each file corresponds to a different key range. The method comprises: a computing node in a computing layer of the data warehouse determining at least two files with overlapping corresponding key ranges from a plurality of files as source files to be merged, and determining a number N of output files according to a total number of data records of all the source files and the number of data records contained in a specified output file; merging and sorting data records in each source file again to merge the source files into a target file; and splitting the data records in the target file into N output files without overlapping corresponding key ranges according to sequencing of the data records in the target file, and re-writing N input files into the storage layer for storage.
Need to check novelty before this filing date? Find Prior Art

Description

Data management method, system, device, equipment and medium in data warehouse

[0001] This application claims priority to the Chinese patent application filed with the China Patent Office on May 24, 2023, with application number 202310603473.X and invention name “A data management method, system, device, equipment and medium in a data warehouse”, the entire contents of which are incorporated by reference into this application. Technical Field

[0002] This specification relates to the field of data warehouse technology, and in particular to a data management method, system, device, equipment and medium in a data warehouse. Background Art

[0003] With the development of computer technology, data warehouses are becoming more and more common in mainstream software systems. However, with the use of data warehouses, the number of files used to store data records in the data warehouse will increase, and the size of the files will also increase. This will not only seriously affect the stability of the data warehouse, but also slow down the query speed of the files.

[0004] For example, suppose that initially the files in the data warehouse are divided in order according to the range of the split key. However, as operations such as adding, deleting, and modifying the data records stored in these files accumulate over time, these files will no longer be distributed in an orderly manner. The ranges of the split keys between the files may overlap. Then, when querying data, more files need to be loaded and queried, resulting in reduced query efficiency and difficulty in responding to query operations in a timely manner.

[0005] Therefore, in order to improve query performance and ensure timely response, it is necessary to manage the files in the data warehouse.

[0006] Summary of the Invention

[0007] In view of this, one or more embodiments of this specification provide a data management method, system, apparatus, device, and medium in a data warehouse to solve the problems existing in the related art.

[0008] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

[0009] According to a first aspect of an embodiment of the present specification, a data management method in a data warehouse is provided, wherein the data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are divided into multiple files based on a specified splitting key, and the files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range; the method is applied to a computing node in the computing layer of the data warehouse, and the method includes: determining at least two files whose corresponding key ranges overlap from the multiple files stored in the storage layer as source files to be merged, and determining the number N of output files based on the total number of data records of all source files and the number of data records contained in the specified output file; re-merging and sorting the data records in each source file to merge the source files into a target file; splitting the data records in the target file into N output files whose corresponding key ranges do not overlap according to the arrangement order of the data records in the target file, and rewriting the N input files into the storage layer for storage.

[0010] According to the second aspect of the embodiments of this specification, a data management system in a data warehouse is provided, wherein the data warehouse adopts a computing and storage separation architecture; the data records in the data warehouse are divided into multiple files based on a specified splitting key, and the files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range; the system includes: a determination subsystem, which determines at least two files with overlapping corresponding key ranges from the multiple files stored in the storage layer as source files to be merged, and determines the number N of output files based on the total number of data records of all source files and the number of data records contained in the specified output file; a sorting subsystem, which re-merges and sorts the data records in each source file to merge the source files into a target file; a splitting subsystem, which splits the data records in the target file into N output files with no overlapping corresponding key ranges according to the arrangement order of the data records in the target file, and rewrites the N input files into the storage layer for storage.

[0011] According to the third aspect of the embodiments of this specification, there is provided a data management device in a data warehouse, wherein the data warehouse adopts a computing and storage separation architecture; the data records in the data warehouse are divided into multiple files based on a specified splitting key, and the files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range; the device is applied to a computing node in the computing layer of the data warehouse, and the device includes: a first determination module, which determines at least two files with overlapping corresponding key ranges from the multiple files stored in the storage layer as source files to be merged, and determines the number N of output files based on the total number of data records of all source files and the number of data records contained in the specified output file; a sorting module, which re-merges and sorts the data records in each source file to merge the source files into a target file; a splitting module, which splits the data records in the target file into N output files with non-overlapping corresponding key ranges according to the arrangement order of the data records in the target file, and rewrites the N input files into the storage layer for storage.

[0012] According to the fourth aspect of the embodiments of this specification, an electronic device is provided, including a communication interface, a processor, a memory and a bus, wherein the communication interface, the processor and the memory are interconnected via a bus; the memory stores machine-readable instructions, and the processor executes the above method by calling the machine-readable instructions.

[0013] According to a fifth aspect of the embodiments of this specification, a machine-readable storage medium is provided, wherein the machine-readable storage medium stores machine-readable instructions, and when the machine-readable instructions are called and executed by a processor, the above method is implemented. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] FIG1 is a schematic diagram of a logical architecture of a data warehouse provided by an exemplary embodiment of this specification;

[0015] FIG2 is a flow chart of a data management method in a data warehouse provided by an exemplary embodiment of this specification;

[0016] FIG3 is a schematic diagram of a merge sort provided by an exemplary embodiment of this specification;

[0017] FIG4 is a flowchart of calculating a merge score provided by an exemplary embodiment of this specification;

[0018] FIG5 is a schematic diagram of a data management system in a data warehouse provided by an exemplary embodiment of this specification;

[0019] FIG6 is a schematic diagram of the structure of an electronic device where a data management device in a data warehouse is located, provided by an exemplary embodiment of this specification;

[0020] FIG7 is a block diagram of a data management device in a data warehouse provided by an exemplary embodiment of this specification. DETAILED DESCRIPTION

[0021] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The implementations described in the following exemplary embodiments are not intended to represent all implementations consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with certain aspects of one or more embodiments of this specification, as detailed in the appended claims.

[0022] It should be noted that in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than those described in this specification. In addition, a single step described in this specification may be broken down into multiple steps for description in other embodiments, and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0023] As can be seen from the above, during the use of the data warehouse, with the operations of adding, deleting and modifying data, the data is no longer distributed in an orderly manner, and the ranges of the split keys corresponding to the various files in the data warehouse may overlap. In order to improve query efficiency and ensure timely response, it is necessary to manage the files in the data warehouse.

[0024] At present, in traditional data warehouse management solutions, in order to eliminate the overlapping ranges of split keys between files as much as possible, it is mainly based on the idea of ​​layered merging. By dividing data files into different layers, newly written files are stored in the top layer, and newly written files are merged first, and each merge is performed layer by layer.

[0025] For example, suppose the data warehouse is divided into three layers for storage, with the first layer being the bottom layer and the third layer being the top layer. Newly written files are stored in the third layer. In each merge, the third layer needs to be merged with the second layer first, and then the second layer needs to be merged with the first layer.

[0026] However, using the layered merging method described above, on the one hand, merging layers by layers starting from the top layer will lead to an excessive accumulation of underlying files. The excessive number of files will cause a heavy disk load during the merge, affecting the read and write performance; on the other hand, the merged objects are fixed and the files to be merged cannot be arbitrarily selected, which may result in the merging of files with low merging benefits, which is equivalent to doing useless work.

[0027] In view of this, this specification provides a technical solution for eliminating overlapping key ranges in files by merging and sorting the data records in the source files according to the determined source files with overlapping key ranges, and re-splitting the files based on the merged and sorted order.

[0028] During implementation, the idea of ​​hierarchical merging described above may no longer be adopted. Instead, at least two files with overlapping corresponding key ranges are determined from multiple files stored in the storage layer of the data warehouse as source files to be merged, and the number N of output files is determined based on the total number of data records of all source files and the number of data records contained in the specified output file.

[0029] The data warehouse adopts a computing and storage separation architecture; the data records in the data warehouse are divided into multiple files for storage based on the specified split key, and the files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range.

[0030] In one example, files with overlapping key ranges can be identified from multiple files stored in a data warehouse's storage layer, and at least two of these files can be selected as source files to be merged. The number of output files required can then be determined based on the total number of data records stored in the source files to be merged and the number of data records that the user desires each output file to contain; for example, the number of output files N = the total number of data records ÷ the number of data records contained in the output files.

[0031] Then, the data records in each source file are re-merged and sorted to merge the source files into a target file.

[0032] In one example, a merge sorting method may be used to re-merge and sort the data records in the source files according to the data contents in the data records, so as to merge the source files into a target file.

[0033] Afterwards, according to the arrangement order of the data records in the target file, the data records in the target file are split into N output files with corresponding non-overlapping key ranges, and the N input files are rewritten into the storage layer for storage.

[0034] In one example, the data records in the target file can be split according to the order of the data records arranged in order in the merged target file, and divided into output files of corresponding key ranges according to the key range to which the data content in each data record belongs, and the N input files are rewritten into the storage layer for storage, and the key ranges corresponding to the N output files do not overlap with each other.

[0035] Through the above technical solution, when managing data in a data warehouse, the data records in the source files can be merged and sorted according to the source files with overlapping key ranges, and merged into target files. According to the number of output files determined, the data records in the target files are split into individual output files for storage, so that the key ranges of the individual output files do not overlap. In the above process, by selecting files with overlapping key ranges as source files, and merging the source files by merge sorting and then splitting them in order, multiple output files with non-overlapping key ranges are obtained, thereby eliminating the overlap of files in key ranges as much as possible, reducing the number of files that need to be accessed when querying files, improving query performance, and reasonably controlling the benefits brought by the merger and the overhead caused by the merger to prevent the situation where the ratio of benefits to overhead is too low.

[0036] The data management method in the data warehouse of this specification is described in detail below with reference to the accompanying drawings.

[0037] In this specification, a data warehouse can adopt a compute-storage separation architecture to separate the compute layer from the storage layer. The compute layer can be responsible for data processing and data computation, while the storage layer can be used to store data. This architecture can better support large-scale data storage and processing, while providing a better balance between performance and cost, enabling more efficient data analysis and application development.

[0038] For example, the data management method in this specification can be applied to the computing nodes in the computing layer of the data warehouse, and the computing nodes can read files from the storage nodes in the storage layer to execute subsequent steps 201-203 and write the regenerated output files to the storage layer.

[0039] It is worth noting that in a data warehouse, there can be more than one computing node and storage node.

[0040] Please refer to FIG1 , which is a schematic diagram of the logical architecture of a data warehouse provided by an exemplary embodiment of this specification.

[0041] As shown in Figure 1, the computing layer may include at least one computing node, and the data warehouse may allocate at least one storage engine and memory space corresponding to the computing node to each computing node; while the storage layer may include at least one storage node, each storage node may have at least one shard, and each shard may store at least one file.

[0042] Taking Figure 1 as an example, for storage node S1, computing node C1 can be assigned to undertake the processing and computing functions for the data records stored in storage node S1. When there are multiple shards in storage node S1, a one-to-one corresponding storage engine can be assigned to each shard in computing node C1 to undertake the processing and computing functions for the data records stored in each shard; for example, the data records in shard S1-1 can be handled by storage engine C1-1, and the data records in shard S1-2 can be handled by storage engine C1-2.

[0043] Among them, through the above-mentioned storage engine, operations such as adding, deleting and modifying can be performed on any file in the shard corresponding to the storage engine, and the above-mentioned storage engine can be used to execute the data management method in this solution.

[0044] For example, when executing the data management method in this solution, the storage engine C1-1 can first copy the data records in the shard S1-1 to the memory space corresponding to the computing node 1, and then execute subsequent steps 201-203 in the memory space to rewrite the regenerated output file into the shard S1-1.

[0045] Please refer to Figure 2, which is a flowchart of a data management method in a data warehouse provided by an exemplary embodiment of this specification. The data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are divided into multiple files based on a specified split key. The files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range. The method is applied to a computing node in the computing layer of the data warehouse, as shown in Figure 2, and includes the following execution steps:

[0046] Step 201: Determine at least two files whose corresponding key ranges overlap from multiple files stored in the storage layer as source files to be merged, and determine the number N of output files based on the total number of data records in all source files and the number of data records contained in the specified output file; Step 202: Re-merge and sort the data records in each source file to merge the source files into a target file; Step 203: Split the data records in the target file into N output files whose corresponding key ranges do not overlap according to the arrangement order of the data records in the target file, and rewrite the N input files into the storage layer for storage.

[0047] It should be noted that the data records in the above data warehouse are divided into multiple files based on the specified split key for storage. The files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range.

[0048] For example, suppose a data warehouse stores records of user-related information (such as height and weight). Using user height as the split key, the data records can be split into multiple files based on the range of user heights. During the split, based on the data stored in the height column within each data record, records with a height of 150-160 can be stored in file 1, records with a height of 160-170 can be stored in file 2, and so on.

[0049] In one embodiment shown, the storage layer of the data warehouse is a distributed file system. For example, taking Figure 1 as an example, when storing the above files, the above files can be stored in multiple shards of the distributed file system, and each shard can store multiple files.

[0050] In this embodiment, at least two files with overlapping corresponding key ranges can be determined from multiple files stored in the storage layer as source files to be merged, and the number N of output files can be determined based on the total number of data records of all source files and the number of data records contained in the specified output file.

[0051] For example, assume that two files are stored in shard 1 in the storage layer of Figure 1. The key range of file A is 20-40, and the key range of file B is 35-45. Then it can be determined that there is overlap between file A and file B, and the overlapping range is 35-40.

[0052] After file A and file B are determined as source files to be merged, the number of output files can be determined according to the total number of data records included in file A and file B and the number of data records included in the specified output files.

[0053] For example, assuming that file A contains 300 data records, file B contains 500 data records, and the specified output file contains 200 data records, then based on the total number of data records being 800, the number of output files can be determined to be 800 / 200=4.

[0054] In one embodiment shown, multiple files stored in the storage layer can be periodically read based on a preset data management cycle; it is determined whether the multiple files read contain at least two files whose corresponding key ranges overlap; if so, the at least two files are determined as source files to be merged.

[0055] For example, computing nodes in the computing layer of a data warehouse may periodically read multiple files stored in the storage layer based on a preset data management cycle.

[0056] Taking Figure 1 as an example, when there are multiple shards in the storage layer, each shard stores multiple files. A computing node can be assigned to each shard in the computing layer, and the computing node determines at least two files with overlapping corresponding key ranges from the multiple files stored in the corresponding shard.

[0057] Then, it may be determined whether the multiple files read include at least two files whose corresponding key ranges overlap; if so, the at least two files determined are determined as source files to be merged.

[0058] In this embodiment, the data records in each source file may be merged and sorted again to merge the source files into a target file.

[0059] It should be noted that the above-mentioned merge sort (MERGE-SORT) is an effective sorting algorithm based on the merge operation. The core idea of ​​the algorithm is based on the divide-and-conquer method. By merging ordered subsequences, a completely ordered sequence is obtained. That is to say, each subsequence needs to be ordered first, and then the subsequence segments need to be ordered.

[0060] Next, the following description will be made using Figure 3 as an example. Please refer to Figure 3, which is a schematic diagram of a merge sort provided by an exemplary embodiment of this specification. As shown in Figure 3, the merge sort process may include two processes: merging and merging.

[0061] During the reduction process, the sequence to be sorted can be split into two subsequences, and the subsequences can be split again until only one element remains in each subsequence, completing the reduction process.

[0062] For example, in Figure 3, the sequence to be sorted [9,7,3,4,2,6,8,5] can be split into two subsequences [9,7,3,4] and [2,6,8,5]. Then, it can be further split into [9,7], [3,4], [2,6], and [8,5], and so on until it is [9], [7], [3], [4], [2], [6], [8], and [5]. At this point, there is only one element left in each subsequence, and each subsequence can be considered an ordered subsequence. Then, the merging process can be started to merge two elements at a time.

[0063] In the merging process, two adjacent subsequences can be continuously merged into a large subsequence. The merging process is completed by continuously merging two ordered subsequences until all subsequences are merged.

[0064] For example, in Figure 3, during the merge process, the adjacent subsequences [9] and [7] can be merged to obtain the ordered subsequence [7,9]. The adjacent subsequences [3] and [4] can be merged to obtain the ordered subsequence [3,4]. Then, the adjacent subsequences [7,9] and [3,4] can be merged to obtain the ordered subsequence [3,4,7,9]. Continuing with Figure 3, after merging [3,4,7,9] and [2,5,6,8], the completely ordered sequence [2,3,4,5,6,7,8,9] can be obtained.

[0065] Through the process described in Figure 3 above, the sequence to be sorted [9,7,3,4,2,6,8,5] can be merge sorted to obtain the completely ordered sequence [2,3,4,5,6,7,8,9].

[0066] In one embodiment shown, in response to the split key being a sort key, a merge sort method is adopted to re-merge and sort the data records in the respective source files based on the data content corresponding to the split key to merge the source files into a target file.

[0067] In another embodiment shown, in response to the split key being a non-sort key, a merge sort is adopted according to a sort key specified from keys other than the split key, and based on the data content corresponding to the specified sort key, the data records in the source file are re-merged and sorted to merge the source files into a target file.

[0068] For example, assume that the data record is as shown in Table 1 below:

[0069] Table 1

[0070] Table 1 above may be a data record in a data warehouse in an e-commerce scenario. For the sake of example, it is assumed here that the total range of product IDs is 32800-32999, which will not be further described later.

[0071] Among them, F (Finished) can indicate that the order has been completed, P (Preparing) can indicate that the order is being prepared, and O (Ordered) can indicate that the order has been generated; the unit of the total price can be yuan, and the order priority can be 1-5, with the smaller the number, the higher the level; the above-mentioned various IDs (Identity) are used to represent the unique identifiers corresponding to products, users and merchants respectively.

[0072] In Table 1 above, each data column can be used as a split key, for example, the product ID can be a split key, and the user ID can also be a split key. Each data column can also be designated as a sort key, for example, the product ID can be a sort key, and the user ID can also be a sort key.

[0073] As can be seen from the above, the role of the split key is to divide the data records into different files based on the key range. In other words, the files are ordered.

[0074] For example, when the product ID is used as the specified split key, file 1 can store data records with product IDs ranging from 32800 to 32900, file 2 can store data records with product IDs ranging from 32900 to 32949, and file 3 can store data records with product IDs ranging from 32950 to 32999.

[0075] When the split key is a sort key, when the file stores these data records, these data records need to be arranged in order according to the split key within the file.

[0076] For example, when the product ID is both a split key and a sort key, assuming that file 3 stores data records with product IDs ranging from 32950 to 32999, these data records can be arranged in order from small to large, with the first row containing the data records with product IDs closest to 32950, ​​and the last row containing the data records with product IDs closest to 32999.

[0077] When the split key is a non-sort key, then when the file stores these data records, these data records are not arranged in order by the split key, but are arranged in order by the sort key specified in other keys other than the split key.

[0078] For example, when the product ID is the partition key and the total price is the sort key, compared with the previous example, although file 3 also stores data records with product IDs ranging from 32950 to 32999, these data records are not sorted in order of product ID size, but are sorted from low to high based on total price.

[0079] Combined with the above description of Table 1, it can be understood that when the split key is a sort key, the data content of the column where the split key is located in the data records within each file is arranged in order; when the split key is a non-sort key, the data content of the column where the split key is located in the data records within each file is not arranged in order, but the data content of the column where the sort key specified from keys other than the split key is arranged in order.

[0080] Combined with the relevant description of the merge sort mentioned above, it can be seen that when using merge sort, if the split key is a sort key, then the data records in each source file can be re-merged and sorted based on the data content corresponding to the split key, and a target file arranged in order according to the data content corresponding to the split key can be obtained, and the target file is formed by merging the data records of each source file; and if the split key is a non-sort key, based on the same principle, the data records in each source file can be re-merged and sorted based on the data content corresponding to the sort key specified from keys other than the split key, and a target file arranged in order according to the data content corresponding to the sort key can be obtained, and the target file is formed by merging the data records of each source file.

[0081] In this embodiment, the data records in the target file can be split into N output files with corresponding non-overlapping key ranges according to the arrangement order of the data records in the target file, and the N input files can be rewritten into the storage layer for storage.

[0082] For example, the data records in the target file can be split according to the order of the data records in the merged target file. According to the key range to which the data content in each data record belongs, the data records are divided into output files of corresponding key ranges. The key ranges corresponding to these N output files do not overlap with each other, and these N input files are rewritten into the storage layer for storage.

[0083] Next, corresponding to the above, when splitting the target file, the splitting can be performed based on two situations: the splitting key is a sorting key and the splitting key is a non-sorting key.

[0084] In one embodiment shown, in response to the split key being a sort key, according to the arrangement order of the data records in the target file, starting from the first row of data records in the target file, every M rows of data records are output as an output file, so as to split the data records in the target file into N output files whose corresponding key ranges do not overlap; the value of M is the number of data records contained in the specified output file.

[0085] For example, assume Table 1 contains 999 data records. If the product ID in Table 1 serves as both the split key and the sort key, then the target file will contain 999 data records sorted from lowest to highest product ID. Assuming the number of output files determined in step 201 is 9, to control the size of each output file and ensure that the data records in each output file are distributed as evenly as possible, the data records can be divided into 9 equal parts, resulting in each output file containing 111 data records.

[0086] Continuing with the example, we can output every 111 lines of data records from the 999 lines of data records in the target file as an output file, starting from the first line. For example, the first 111 lines are the first output file, thereby evenly splitting the 999 lines of data records in the target file into 9 output files with no overlap in the corresponding key ranges.

[0087] It is worth noting that when data records are evenly distributed to each output file, there may be partial overlap. For example, the product ID in the last line of the first output file is 32824, and the product ID in the first line of the second output file is also 32824. In order to ensure that there is no overlap in the key range, based on the idea of ​​equal distribution, some adjustments can be made. Complete equal distribution is not required. For example, the data record with product ID 32824 in the second output file can be divided into the first output file.

[0088] Of course, during the partitioning process, it is also possible to determine whether the data content corresponding to the split key of the last data record written in the current output file is consistent with the data content corresponding to the split key of the next data record to be written in the target file. If they are consistent, the next data record to be written is written to the current output file. This specification does not limit this, and those skilled in the art can solve this problem on their own, based on the premise of equal partitioning and eliminating key range overlap.

[0089] In one embodiment shown, statistical results for the data content corresponding to the split key in the data records of each source file may also be obtained, and the obtained statistical results may be aggregated into target statistical results; wherein the statistical results include a key range of the split key and a correspondence between the number of occurrences of the data content corresponding to each key value in the key range in each source file;

[0090] And, based on the number of occurrences, the key range of the split key included in the target statistical result is divided into N sub-intervals with the same number of corresponding occurrences.

[0091] For example, the above statistical results can be based on a histogram, where the base of the histogram is the key range of the split key, and the height is the number of occurrences of the data content corresponding to each key value in the key range in each source file. For example, assuming the split key is the product ID in Table 1, then the base of the histogram is the product ID range, and the height of each histogram is the number of occurrences of these product IDs within the product ID range corresponding to each histogram.

[0092] It is worth noting that the form of the statistical results is not limited to a histogram, but may also be a statistical chart such as a line graph, and this specification does not limit this.

[0093] Continuing with the example, let's assume that Table 1 contains 999 data records. We can first calculate the histogram for each product ID in each source file's data record. By obtaining and aggregating these histograms, we can generate a target histogram. The target histogram then represents the number of occurrences of each of the 999 data records in Table 1 within the corresponding product ID ranges. For example, if a histogram entry has a product ID range of 32850-32899 and a count of 22, this means that there are 22 data records with product IDs in the range.

[0094] As an example, based on the distribution of occurrence counts in the target histogram, the total product ID range of 32800-32999 can be divided into N subranges according to the number N of output files determined in step 201. Similarly, based on the aforementioned equal division principle, the number of occurrences of the product ID range corresponding to each of these N subranges can be the same or approximately the same. The division method used for fine-tuning the approximate equality can be described in the previous description of the division process and will not be repeated here.

[0095] In one embodiment shown, in response to the split key being a non-sort key, starting from the first row of data records in the target file, according to the arrangement order of the data records in the target file, the sub-interval of the key range to which the data record belongs is determined based on the data content in the data record corresponding to the split key, and the data record is written into an output file corresponding to the sub-interval, so as to split the data records in the target file into N output files whose corresponding key ranges do not overlap; the output files correspond one-to-one to the sub-intervals.

[0096] For example, if the product ID in Table 1 is the partition key and the total price is the sort key, then the target file contains 999 data records sorted from lowest to highest by total price. Assuming the number of output files determined in step 201 is 9, the total product ID range of 32800-32999 needs to be divided into 9 subranges. Within these 9 subranges, the number of occurrences of the product ID range in each subrange can be 111, or close to 111.

[0097] Continuing with the example, assuming the first sub-interval is 32800-32830 and the second sub-interval is 32831-32855, then we can start from the first row of data records in the target file and determine the sub-interval of the product ID to which the current row of data records belongs based on the product ID in the current row of data records. If the product ID in the first row of data records is 32800, then this first row of data records is written to the first output file corresponding to the first interval, and if the product ID in the first row of data records is 32835, then this first row of data records is written to the second output file corresponding to the second interval.

[0098] It is worth noting that since the output files correspond one-to-one to the above sub-intervals, the key range of the output file is also the key range of the sub-interval, and there is no overlap between the key ranges of each sub-interval, so the key range of the output file does not overlap either.

[0099] Through the above process, users can select split keys based on actual needs and data characteristics. When the split key is a non-sort key, other keys can be additionally specified as sort keys, making the query method more flexible and improving query performance.

[0100] In one embodiment shown, in response to the total number of data records included in all source files reaching a threshold, all source files may be sorted according to the minimum value of the data content corresponding to the split key in the data records stored in each source file; the source files in the sorting result may be evenly grouped according to the number of source files; the product of the number of groups and the maximum number of source files in each group is greater than or equal to the number of source files;

[0101] Furthermore, the source files in each group are determined as the source files to be merged, and the maximum number of source files in each group is determined as the number of output files.

[0102] It should be noted that in order to reduce the overhead when merging files, reduce the resource consumption during each merge, and avoid insufficient memory caused by excessive data volume during a single merge, group merging can be performed to improve the robustness and stability of the merge.

[0103] For example, a threshold for the total number of data records can be preset to ensure that the size of each merged data does not exceed expectations. When the total number reaches the threshold, all source files can be sorted based on the minimum value of the data content corresponding to the split key in the data records stored in each source file. Still using the product ID as the split key as an example, assuming there are three source files, if the minimum value of the product ID in source file 1 is 32810, the minimum value of the product ID in source file 2 is 32840, and the minimum value of the product ID in source file 3 is 32860, then these three source files can be sorted to obtain source file 1, source file 2, and source file 3.

[0104] Then, the source files in the sorting results can be evenly grouped according to the number of source files.

[0105] For example, assuming that the number of source files is 4, they can be divided into two groups evenly, with the first two source files in the sorting result being divided into one group and the last two source files being divided into another group.

[0106] For example, if the number of source files is P, they can be divided into X groups, and when taking the value of X, it can be ensured that the square of X is closest to P. For example, if the number of source files is 80, they can be divided into 9 groups, and the maximum number of source files in each group is 9.

[0107] Next, each group may be merged, the source files in each group may be determined as source files to be merged, and the maximum number of source files in each group may be determined as the number of output files.

[0108] In one embodiment shown, the data warehouse is divided into a first file storage area and a second file storage area; the first file storage area is used to store newly generated files, and the second file storage area is used to store files generated based on the split key.

[0109] In one embodiment, in response to the first file storage area meeting a preset first merging condition, the files in the first file storage area may be determined as the source files to be merged;

[0110] And, in response to the second file storage area meeting the preset second merging condition, a merging score of each second file storage area with any other second file storage area is calculated, and a source file to be merged in the second file storage area is determined according to the merging score.

[0111] It should be noted that after the source files to be merged are determined, the merging can be performed based on the description of the aforementioned step 202 and step 203, which will not be described in detail here.

[0112] For example, the above-mentioned second file storage area can be used for long-term file storage, storing files generated based on the split key. There can be multiple second file storage areas, while the first file storage area can be used for short-term temporary file storage, storing newly generated files in the data warehouse. In order to save storage space, only one first file storage area can be set.

[0113] In one example, if the first file storage area meets a preset first merging condition, the files in the first file storage area may be determined as source files to be merged.

[0114] Among them, the first merging condition includes at least one or more combinations of the following: the size of the files stored in the first file storage area reaches a threshold; the number of files stored in the first file storage area reaches a threshold; the time since the last file merge in the data warehouse reaches a threshold.

[0115] In another example, if the second file storage area meets the preset second merging condition, the merging score of each second file storage area with any other second file storage area can be calculated, and the source file to be merged in the second file storage area can be determined according to the merging score.

[0116] For example, assuming that there are four second file storage areas, namely Z1, Z2, Z3 and Z4, then the merge scores between these four areas can be calculated, and the two areas with the highest merge scores can be selected as the areas to be merged. If the two areas with the highest merge scores are Z1 and Z4, then the files in Z1 and Z4 are determined as the source files to be merged.

[0117] The second merging condition includes at least one or more of the following combinations: the number of files stored in the second file storage area reaches a threshold; the time from the last file merge in the data warehouse reaches a threshold.

[0118] In the above process, by dividing areas with specific functions and setting preset conditions, you can customize the merge strategy and choose the appropriate time to merge, avoiding the additional overhead caused by unreasonable merge strategies and ensuring that each merge can bring higher benefits.

[0119] In one embodiment shown, for each second file storage area, at least two files in the second file storage area that overlap with the key range in any other second file storage area are determined, and an overlapping file set is determined; the key ranges corresponding to the files in the overlapping file set overlap with each other;

[0120] and, calculating an overlap score of each overlapping file set based on the overlapping file sets of each second file storage area and any other second file storage area, and calculating a combined score of each second file storage area and any other second file storage area based on the overlapping scores;

[0121] In another embodiment shown, a second file storage area to be merged can be determined whose merge score meets a preset condition, and the files in the second file storage area to be merged can be determined as the source files to be merged; the preset condition includes at least that the merge score is a maximum value or that the merge score reaches a threshold.

[0122] Taking Figure 4 as an example, Figure 4 is a flow chart of calculating a merge score provided by an exemplary embodiment of this specification. As shown in Figure 4, the following steps are included:

[0123] Step 401: For each second file storage area, determine at least two files in the second file storage area that overlap with the key range in any other second file storage area, and determine an overlapping file set.

[0124] The key ranges corresponding to the files in the overlapping file set overlap with each other.

[0125] For example, assuming there are second file storage areas Z1 and Z2, if the key ranges of files in Z1 and Z2 overlap, it can be considered that there are files with overlapping key ranges. Assume that the files with overlapping key ranges are F1, F2 and F3, the key range of F1 is 0-100, the key range of F2 is 30-130, and the key range of F3 is 80-150. These three files overlap with each other, and these three files can be determined as the overlapping file set G1.

[0126] Step 402 : Calculate an overlap score of each overlapping file set based on the overlapping file sets between each second file storage area and any other second file storage area.

[0127] For example, when there are multiple second file storage areas, there are likely to be multiple overlapping file sets. It is necessary to calculate the overlap score corresponding to each overlapping file set separately, and then judge the overlap of each overlapping file set based on the score. Generally, a higher score means more overlap.

[0128] Step 403: Calculate a merging score of each second file storage area and any other second file storage area based on the overlapping score.

[0129] For example, assuming that Z1 and Z2 have overlapping file sets G1, G2, and G3, the merge score of Z1 and Z2 can be calculated based on the overlapping scores corresponding to G1, G2, and G3 respectively.

[0130] Step 404: Determine a second file storage area to be merged whose merging score meets a preset condition, and determine the files in the second file storage area to be merged as the source files to be merged.

[0131] The preset condition at least includes that the combined score is a maximum value or the combined score reaches a threshold value.

[0132] For example, assuming that there are four second file storage areas, namely Z1, Z2, Z3 and Z4, a total of 6 merging scores between each of these four areas can be determined, and the storage areas whose merging scores meet the preset conditions are determined as the storage areas to be merged.

[0133] For example, assuming that the merge score of Z1 and Z4 is the maximum of the six merge scores, or only the merge score of Z1 and Z4 reaches the threshold, then Z1 and Z4 can be determined as the storage areas to be merged, and then the files in Z1 and Z4 can be determined as the source files to be merged.

[0134] In one embodiment shown, when executing step 402, overlapping intervals in which key ranges corresponding to files in the overlapping file set overlap, as well as the number of overlaps of each overlapping interval, may be determined, and the overlap score may be calculated based on the length of the overlapping interval and the number of overlaps.

[0135] For example, assuming that in the above-mentioned overlapping file set G1, the overlapping files are F1, F2 and F3, the key range of F1 is 0-100, the key range of F2 is 30-130, and the key range of F3 is 80-150, then it can be determined that in the overlapping file set G1, the overlapping intervals with overlaps are 30-80, 80-100, and 100-130.

[0136] Continuing with the example, assuming it is determined that the interval 30-80 overlaps 2 times, the interval 80-100 overlaps 3 times, and the interval 100-130 overlaps 2 times, then the overlap score can be calculated based on the length of the overlapping intervals and the number of overlaps.

[0137] In one example, the overlap score can be determined based on the sum of the lengths of each overlapping interval * (the number of overlaps of each overlapping interval - 1). The above overlap score = (80-30)*(2-1)+(100-80)*(3-1)+(130-100)*(2-1)=50*1+20*2+30*1=120, that is, the overlap score of the above overlapping file set G1 is 120 points.

[0138] When executing the above-mentioned step 403, the sum of the overlapping scores of all overlapping file sets can be determined based on the overlapping scores of the overlapping file sets of each second file storage area and any other second file storage area; the total length of the intervals of all overlapping file sets can be determined based on the minimum and maximum values ​​of the critical values ​​of the key ranges corresponding to the files in all overlapping file sets; and the merge score of each second file storage area and any other second file storage area can be calculated based on the ratio of the sum of the overlapping scores of all overlapping file sets to the total length of the intervals of all overlapping file sets.

[0139] For example, assuming that Z1 and Z2 have overlapping file sets G1, G2 and G3, we can first calculate the sum of the overlapping scores corresponding to G1, G2 and G3 respectively, and then calculate the total length of the intervals corresponding to G1, G2 and G3 respectively.

[0140] For example, for G1, there are three overlapping files F1, F2, and F3. The key range of F1 is 0-100, the key range of F2 is 30-130, and the key range of F3 is 80-150. The minimum critical value of the key range corresponding to these three files is 0, and the maximum critical value is 150. Therefore, the total interval length of G1 is 150. The total interval length of all overlapping file sets is the sum of the total interval lengths corresponding to G1, G2, and G3 respectively.

[0141] Continuing with the example, the ratio of the sum of the overlap scores corresponding to G1, G2, and G3, respectively, to the sum of the total lengths of the intervals corresponding to G1, G2, and G3, respectively, can be determined as the combined score of Z1 and Z2.

[0142] Through the above technical solution, when managing data in a data warehouse, the data records in the source files can be merged and sorted according to the source files with overlapping key ranges, and merged into target files. According to the number of output files determined, the data records in the target files are split into individual output files for storage, so that the key ranges of the individual output files do not overlap. In the above process, by selecting files with overlapping key ranges as source files, and merging the source files by merge sorting and then splitting them in order, multiple output files with non-overlapping key ranges are obtained, thereby eliminating the overlap of files in key ranges as much as possible, reducing the number of files that need to be accessed when querying files, improving query performance, and reasonably controlling the benefits brought by the merger and the overhead caused by the merger to prevent the situation where the ratio of benefits to overhead is too low.

[0143] In an exemplary embodiment of the present specification, a system capable of implementing the above method is also provided.

[0144] Please refer to Figure 5, which is a schematic diagram of a data management system in a data warehouse provided by an exemplary embodiment of this specification. In which, the data warehouse adopts a computing and storage separation architecture; the data records in the data warehouse are divided into multiple files based on a specified split key, and the files are stored in the storage layer of the data warehouse, and each file corresponds to a different key range. As shown in Figure 5, the system includes a determination subsystem 501, a sorting subsystem 502 and a splitting subsystem 503. The system includes: the determination subsystem 501 is used to determine at least two files with overlapping corresponding key ranges from the multiple files stored in the storage layer as source files to be merged, and determine the number N of output files based on the total number of data records in all source files and the number of data records contained in the specified output files; the sorting subsystem 502 is used to re-merge and sort the data records in each source file to merge the source files into a target file; the splitting subsystem 503 is used to split the data records in the target file into N output files with no overlapping corresponding key ranges according to the arrangement order of the data records in the target file, and rewrite the N input files into the storage layer for storage.

[0145] The implementation process of the functions and effects of each subsystem in the above system is specifically detailed in the implementation process of the corresponding steps in the data management method in the above data warehouse. For relevant matters, please refer to the partial description of the method implementation method, which will not be repeated here.

[0146] In an exemplary embodiment of this specification, a device capable of implementing the above method is also provided.

[0147] FIG6 is a schematic structural diagram of a device provided by an exemplary embodiment. Referring to FIG6 , at the hardware level, the device includes a processor 602, an internal bus 604, a network interface 606, a memory 608, and a non-volatile memory 610, and may also include other necessary hardware. One or more embodiments of this specification may be implemented based on software, such as the processor 602 reading the corresponding computer program from the non-volatile memory 610 into the memory 608 and then running it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but may also be hardware or logic devices.

[0148] Referring to FIG7 , in one software implementation, a data management device 700 in a data warehouse is provided. The data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are split into multiple files based on a specified split key, and the files are stored in the storage layer of the data warehouse, each file corresponding to a different key range. The device 700 is applied to a computing node in the computing layer of the data warehouse. As shown in FIG7 , the device 700 includes: a first determination module 701 that determines at least two files with overlapping corresponding key ranges from multiple files stored in the storage layer as source files to be merged, and determines the number N of output files based on the total number of data records in all source files and the number of data records contained in the specified output files; a sorting module 702 that re-merges and sorts the data records in each source file to merge the source files into a target file; and a splitting module 703 that splits the data records in the target file into N output files with non-overlapping corresponding key ranges according to the order of the data records in the target file, and rewrites the N input files into the storage layer for storage.

[0149] In one embodiment, the sorting module 702 further: in response to the split key being the sorting key, adopts a merge sort method, and re-merges and sorts the data records in the respective source files based on the data content corresponding to the split key to merge the source files into a target file.

[0150] In one embodiment, the splitting module 703 further: in response to the split key being a sorting key, starting from the first row of data records in the target file, outputs every M rows of data records as an output file according to the arrangement order of the data records in the target file, so as to split the data records in the target file into N output files whose corresponding key ranges do not overlap; the value of M is the number of data records contained in the specified output file.

[0151] In one embodiment, the sorting module 702 further: in response to the split key being a non-sorting key, adopts a merge sort method according to a sorting key specified from other keys other than the split key, and re-merges and sorts the data records in the source file based on the data content corresponding to the specified sorting key to merge the source file into a target file.

[0152] In one embodiment, the apparatus 700 further includes: a summarizing module 704 (not shown in the figure) for obtaining statistical results of the data content corresponding to the split key in the data records of each source file, and summarizing the obtained statistical results into target statistical results; wherein the statistical results include a key range of the split key and a correspondence between the number of occurrences of the data content corresponding to each key value in the key range in each source file;

[0153] The sub-interval division module 705 (not shown in the figure) divides the key range of the split key contained in the target statistical result into N sub-intervals with the same number of corresponding occurrences based on the number of occurrences.

[0154] In one embodiment, the splitting module 703 further: in response to the split key being a non-sort key, starting from the first row of data records in the target file, according to the arrangement order of the data records in the target file, determines the sub-interval of the key range to which the data record belongs according to the data content corresponding to the split key in the data record, and writes the data record into the output file corresponding to the sub-interval, so as to split the data records in the target file into N output files whose corresponding key ranges do not overlap; the output files correspond one-to-one to the sub-intervals.

[0155] In one embodiment, the apparatus 700 further includes:

[0156] A grouping module 706 (not shown) sorts all the source files according to the minimum value of the data content corresponding to the split key in the data records stored in each source file in response to the total number of data records included in all the source files reaching a threshold; and evenly groups the source files in the sorting result according to the number of the source files; the product of the number of groups and the maximum number of source files in each group is greater than or equal to the number of the source files;

[0157] The second determining module 707 (not shown in the figure) determines the source files in each group as the source files to be merged, and determines the maximum number of source files in each group as the number of output files.

[0158] In one embodiment, the storage layer of the data warehouse includes a first file storage area and a second file storage area; the first file storage area is used to store newly generated files, and the second file storage area is used to store files generated based on the split key;

[0159] In one embodiment, the apparatus 700 further includes:

[0160] A first determination module 708 (not shown in the figure) determines the files in the first file storage area as the source files to be merged in response to the first file storage area satisfying a preset first merging condition;

[0161] The second judgment module 709 (not shown in the figure) calculates a merge score between each second file storage area and any other second file storage area in response to the second file storage area satisfying a preset second merge condition, and determines the source file to be merged in the second file storage area based on the merge score.

[0162] In one embodiment, the second determining module 709 further:

[0163] For each second file storage area, determining at least two files in the second file storage area that overlap with the key range in any other second file storage area, and determining an overlapping file set; the key ranges corresponding to the files in the overlapping file set overlap with each other;

[0164] Calculating an overlap score for each overlapping file set based on the overlapping file sets between each second file storage area and any other second file storage area, and calculating a combined score for each second file storage area and any other second file storage area based on the overlapping scores;

[0165] Determine a second file storage area to be merged whose merge score meets a preset condition, and determine the files in the second file storage area to be merged as the source files to be merged; the preset condition at least includes that the merge score is a maximum value or that the merge score reaches a threshold.

[0166] In one embodiment, the second determining module 709 further:

[0167] Determine overlapping intervals in which key ranges corresponding to files in the overlapping file set overlap, and the number of overlaps of each overlapping interval, and calculate the overlap score based on the length of the overlapping interval and the number of overlaps;

[0168] Determining the sum of the overlapping scores of all overlapping file sets based on the overlapping scores of each second file storage area and the overlapping file sets of any other second file storage area;

[0169] Determining the total length of the intervals of all overlapping file sets according to the minimum and maximum values ​​of the critical values ​​of the key ranges corresponding to the files in all overlapping file sets;

[0170] The merging score of each second file storage area with any other second file storage area is calculated according to the ratio of the sum of the overlapping scores of all overlapping file sets to the total length of the intervals of all overlapping file sets.

[0171] In one embodiment, the first determining module 701 further:

[0172] Periodically reading multiple files stored in the storage layer based on a preset data management cycle;

[0173] Determining whether the plurality of files read include at least two files whose corresponding key ranges overlap;

[0174] If yes, the at least two files are determined as source files to be merged.

[0175] In one embodiment, the storage layer of the data warehouse is a distributed file system.

[0176] The technical solutions provided by the embodiments of this specification may include the following beneficial effects: through the above technical solutions, when managing data in a data warehouse, the data records in the source files can be merged and sorted according to the determined source files with overlapping key ranges, and merged into target files. According to the determined number of output files, the data records arranged in order in the target files can be split into individual output files for storage, so that the key ranges of the individual output files do not overlap. In the above process, by selecting files with overlapping key ranges as source files, and merging the source files by merge sorting and then splitting them in order, a plurality of output files with non-overlapping key ranges are obtained, thereby eliminating the overlap of files in key ranges as much as possible, reducing the number of files that need to be accessed when querying files, improving query performance, and also reasonably controlling the benefits brought by the merger and the overhead caused by the merger to prevent the situation where the ratio of benefits to overhead is too low.

[0177] The implementation process of the functions and effects of each module in the above-mentioned device 700 is specifically detailed in the implementation process of the corresponding steps in the data management method in the above-mentioned data warehouse. For relevant matters, please refer to the partial description of the method implementation method, which will not be repeated here.

[0178] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical modules, that is, they may be located in one place or distributed across multiple network modules. Some or all of the units or modules may be selected according to actual needs to achieve the purpose of the scheme of this specification. Those of ordinary skill in the art can understand and implement the present invention without inventive effort.

[0179] The systems, devices, modules, or units described in the above embodiments may be implemented by computer chips or entities, or by products having certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email transceiver, game console, tablet computer, wearable device, or any combination of these devices.

[0180] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0181] Memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. Memory is an example of a computer-readable medium.

[0182] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information using any method or technology. Information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory media such as modulated data signals and carrier waves.

[0183] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0184] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0185] The foregoing description of this specification describes specific embodiments. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims can be performed in an order different from that described in the embodiments and still achieve the desired results. Furthermore, the processes depicted in the accompanying drawings do not necessarily require the specific order shown or the sequential order to achieve the desired results. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0186] The terms used in one or more embodiments of this specification are for the purpose of describing specific embodiments only and are not intended to limit one or more embodiments of this specification. The singular forms "a," "an," "the," and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0187] It should be understood that although the terms first, second, third, etc. may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when..." or "when..." or "in response to determining."

[0188] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of this specification shall be included in the scope of protection of one or more embodiments of this specification.

Claims

1. A data management method in a data warehouse, wherein the data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are divided into multiple files based on a specified split key, and the files are stored in a storage layer of the data warehouse, and each file corresponds to a different key range; The method is applied to a computing node in a computing layer of the data warehouse, and the method comprises: Determine at least two files whose corresponding key ranges overlap from the multiple files stored in the storage layer as source files to be merged, and determine the number N of the output files according to the total number of data records of all the source files and the number of data records included in the specified output file; Re-merging and sorting the data records in each source file to merge the source files into a target file; According to the arrangement order of the data records in the target file, the data records in the target file are split into N output files whose corresponding key ranges do not overlap, and the N input files are rewritten into the storage layer for storage.

2. The method according to claim 1, wherein the step of re-merging and sorting the data records in each source file to merge the source files into a target file comprises: In response to the split key being a sort key, a merge sort is adopted to re-merge and sort the data records in the respective source files based on the data content corresponding to the split key, so as to merge the source files into a target file.

3. The method according to claim 2, wherein the step of splitting the data records in the target file into N output files whose corresponding key ranges do not overlap according to the arrangement order of the data records in the target file comprises: In response to the split key being a sort key, according to the arrangement order of the data records in the target file, starting from the first line of data records in the target file, every M lines of data records are output as an output file, so as to split the data records in the target file into N output files with no overlap in corresponding key ranges; the value of M is the number of data records contained in the specified output file.

4. The method according to claim 1, wherein the step of re-merging and sorting the data records in each source file to merge the source files into a target file comprises: In response to the split key being a non-sort key, a merge sort is adopted according to a sort key specified from other keys other than the split key, and based on the data content corresponding to the specified sort key, the data records in the source file are re-merged and sorted to merge the source files into a target file.

5. The method according to claim 4, before further splitting the data records in the target file into N output files whose corresponding key ranges do not overlap according to the arrangement order of the data records in the target file, the method further comprises: Obtain statistical results for the data content corresponding to the split key in the data records of each source file, and summarize the obtained statistical results as target statistical results; wherein the statistical results include the key range of the split key, and the corresponding correlation of the number of occurrences of the data content corresponding to each key value in the key range in each source file. Tie; Based on the number of occurrences, the key range of the split key included in the target statistical result is divided into N sub-intervals with the same number of corresponding occurrences.

6. The method according to claim 5, wherein the step of splitting the data records in the target file into N output files whose corresponding key ranges do not overlap according to the arrangement order of the data records in the target file comprises: In response to the split key being a non-sort key, in accordance with the arrangement order of the data records in the target file, starting from the first row of data records in the target file, the sub-interval of the key range to which the data record belongs is determined according to the data content in the data record corresponding to the split key, and the data record is written into an output file corresponding to the sub-interval, so as to split the data records in the target file into N output files whose corresponding key ranges do not overlap; the output files correspond one-to-one to the sub-intervals.

7. The method according to claim 1, further comprising: In response to the total number of data records included in all the source files reaching a threshold, all the source files are sorted according to the minimum value of the data content corresponding to the split key in the data records stored in each source file; the source files in the sorting result are evenly grouped according to the number of the source files; the product of the number of the groups and the maximum number of source files in each group is greater than or equal to the number of the source files; The source files in each group are determined as the source files to be merged, and the maximum number of source files in each group is determined as the number of output files.

8. The method according to claim 1, wherein the storage layer of the data warehouse comprises a first file storage area and a second file storage area; the first file storage area is used to store newly generated files, and the second file storage area is used to store files generated based on the split key; The method further comprises: In response to the first file storage area satisfying a preset first merging condition, determining the files in the first file storage area as the source files to be merged; In response to the second file storage area satisfying a preset second merging condition, a merging score between each second file storage area and any other second file storage area is calculated, and a source file to be merged in the second file storage area is determined according to the merging score.

9. The method according to claim 8, wherein the step of calculating the merge score of each second file storage area with any other second file storage area comprises: For each second file storage area, determining at least two files in the second file storage area that overlap with the key range in any other second file storage area, and determining an overlapping file set; the key ranges corresponding to the files in the overlapping file set overlap with each other; Calculate the overlap score of each overlapping file set according to the overlapping file sets between each second file storage area and any other second file storage area, and calculate the combined score of each second file storage area and any other second file storage area according to the overlapping score; The step of determining the source files to be merged in the second file storage area according to the merge score includes: Determine a second file storage area to be merged whose merge score meets a preset condition, and determine the files in the second file storage area to be merged as the source files to be merged; The preset condition at least includes that the combined score is a maximum value or the combined score reaches a threshold value.

10. The method according to claim 9, wherein calculating the overlap score of each overlapping file set comprises: Determine overlapping intervals in which key ranges corresponding to files in the overlapping file set overlap, and the number of overlaps of each overlapping interval, and calculate the overlap score according to the length of the overlapping interval and the number of overlaps; The calculating, according to the overlap score, a combined score of each second file storage area and any other second file storage area includes: Determine the sum of the overlapping scores of all overlapping file sets according to the overlapping scores of the overlapping file sets of each second file storage area and any other second file storage area; Determine the total length of the interval of all overlapping file sets according to the minimum value and the maximum value of the critical value of the key range corresponding to the files in the all overlapping file sets; The merging score of each second file storage area with any other second file storage area is calculated according to the ratio of the sum of the overlapping scores of all the overlapping file sets to the total length of the intervals of all the overlapping file sets.

11. The method according to claim 1, determining at least two files with overlapping corresponding key ranges from the plurality of files stored in the storage layer as source files to be merged, comprising: Based on a preset data management cycle, periodically read multiple files stored in the storage layer; Determine whether the multiple files read include at least two files whose corresponding key ranges overlap; If yes, the at least two files are determined as source files to be merged.

12. According to the method of claim 1, the storage layer of the data warehouse is a distributed file system.

13. A data management system in a data warehouse, wherein the data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are divided into multiple files based on a specified split key, and the files are stored in a storage layer of the data warehouse, and each file corresponds to a different key range; The system comprises: A determination subsystem determines, from the plurality of files stored in the storage layer, at least two files whose corresponding key ranges overlap as source files to be merged, and determines the number N of the output files according to the total number of data records of all the source files and the number of data records included in the specified output file; A sorting subsystem, re-merging and sorting the data records in each source file to merge the source files into a target file; The splitting subsystem splits the data records in the target file into N output files with non-overlapping key ranges according to the arrangement order of the data records in the target file, and rewrites the N input files into the storage layer for storage.

14. A data management device in a data warehouse, wherein the data warehouse adopts a computing and storage separation architecture; data records in the data warehouse are divided into multiple files based on a specified split key, and the files are stored in a storage layer of the data warehouse, and each file corresponds to a different key range; The device is applied to a computing node in a computing layer of the data warehouse, and the device includes: A first determination module determines at least two files with overlapping corresponding key ranges from the multiple files stored in the storage layer as source files to be merged, and determines the number N of the output files according to the total number of data records of all the source files and the number of data records included in the specified output file; A sorting module is used to re-merge and sort the data records in each source file so as to merge the source files into a target file; A splitting module splits the data records in the target file into N output files with non-overlapping key ranges according to the arrangement order of the data records in the target file, and rewrites the N input files into the storage layer for storage.

15. An electronic device, comprising: processor; a memory for storing processor-executable instructions; The processor implements the method according to any one of claims 1 to 12 by running the executable instructions.

16. A machine-readable storage medium having machine-readable instructions stored thereon, wherein the instructions, when executed by a processor, implement the steps of the method according to any one of claims 1 to 12.

Citation Information

Patent Citations

  • File merging method, storage device, storage equipment and storage medium

    CN108874297A

  • Data merging method and device applied to key value storage system

    CN112527735A

  • Data query method and device

    CN113961514A

  • Data storage method and device and data search method and device

    CN114416651A

  • Data storage method and device based on log merge tree, equipment and storage medium

    CN114780500A

Cited By

  • Data sorting method and device, electronic equipment and storage medium

    CN121117071A