A method for implementing operator storage based on file storage

Abstract

The present invention particularly relates to a method for implementing an operator disk based on file storage. When the amount of calculated data exceeds the memory size limit, the operator disk implementation method based on file storage optimizes the data organization method, file organization method and calculation processing method of the Sort operator, HashAggregator operator and HashJoiner operator. The operator disk implementation method based on file storage enables calculation to be independent of memory resources. When memory resources are limited, SQL statements can be processed based on the disk and the correct results can be output without triggering errors or exceptions, which greatly improves the execution performance.

Description

Technical Field

[0001] The present invention relates to the technical field of database optimization, and in particular to a method for implementing operator storage based on file storage. Background Art

[0002] In most application scenarios, the resources available to database services are limited. The amount of data involved in the calculation of some business scenarios that are not time-sensitive may exceed the resource limit. In the absence of an operator storage mechanism, an error will be returned, and the database service may even exit abnormally, affecting business continuity. In order to avoid this situation, a corresponding mechanism will be introduced to handle it.

[0003] Based on the above situation, the present invention proposes a method for implementing operator storage based on file storage. Summary of the invention

[0004] In order to overcome the defects of the prior art, the present invention provides a simple and efficient method for implementing operator storage based on file storage.

[0005] The present invention is achieved through the following technical solutions:

[0006] A method for implementing operator storage based on file storage, characterized in that: when the amount of calculated data exceeds the memory size limit, the data organization mode, file organization mode and calculation processing mode of the Sort operator, HashAggregator operator and HashJoiner operator are optimized; specifically, the following steps are included:

[0007] Step S1: Implementation of disk calculation of Sort operator

[0008] The Sort operator is used to sort the data and sort the attribute column of an input data set in ascending or descending order; Step S1.1, data organization method of the Sort operator

[0009] When the memory sorting exceeds the limit, each row of data in the input data set is split into two parts: the sorting column and the non-sorting column. The data of the non-sorting column is written to a file, the offset in the file when written is returned, and the offset value and the sorting column are combined into a key-value pair to sort.

[0010] If the memory is sufficient to sort the key-value pairs, the sorted key-value sequence is returned. According to the order of the new key-value sequence, the value values ​​are taken as offsets to read the non-sorted columns from the file, and then combined with the corresponding key values ​​to form a complete data row and returned.

[0011] Step S1.2: File organization of the Sort operator

[0012] A single file is organized by block (a storage granularity smaller than a file). When querying a key-value pair, first query the block metadata to determine the block location to be searched, and then search for the key-value pair data based on the specific block.

[0013] Step S1.3: Calculation of Sort operator

[0014] When the Sort operator performs disk calculation, if the number of columns involved in sorting accounts for 50% of the total number of columns, each sorted row is used as a key-value pair for sorting. At this time, a key-value pair search is performed directly and a row of results is returned.

[0015] If the proportion of sorted columns to the total number of columns is less than 50%, the non-sorted columns are stored separately, and the offset of the non-sorted columns in the file is used as the value and sorted column; at this time, a key-value pair search and a file search are performed, and a row of results is returned;

[0016] Step S2: Implementation of disk calculation of HashAggregator operator

[0017] The HashAggregator operator groups and aggregates data in database queries. When there is no sorting feature, the aggregate column (the column that participates in the keyword group by in the SQL statement) is used.

[0018] Step S2.1: Data organization of HashAggregator operator

[0019] When the memory calculation exceeds the limit, the input data set is split by hash partitioning.

[0020] Step S2.2: File organization of HashAggregator operator

[0021] Each partition is saved as a file, with the grouping column as the key value and the remaining columns as the value. The key-value pairs are compressed and saved in the file.

[0022] Step S2.3: Calculation processing of HashAggregator operator

[0023] Perform aggregation operations on each partition in turn and output the results.

[0024] Step S3: Implementation of disk calculation of HashJoiner operator

[0025] When processing large data sets, the HashJoiner operator uses a hash table to improve the efficiency of the join operation;

[0026] Step S3.1: Data organization of HashJoiner operator

[0027] When the memory calculation exceeds the limit, for the data sets input at both ends, the hash values ​​are calculated for the connection columns (the equivalent columns in the SQL statement) respectively, following the same data organization method as the HashAggregator operator in step 2.1;

[0028] Step S3.2: File organization of HashJoiner operator

[0029] Follow the same file organization as the HashAggregator operator in step 2.1;

[0030] Step S3.3: Calculation and processing of HashJoiner operator

[0031] Perform join operations on each partition in turn and output the results.

[0032] In the step S1.1, if the memory is insufficient to sort the key-value pairs, the key-value pairs are written to the file according to the memory size limit (for example, the memory size limit is 512MB, the full 512MB is sorted, and then written to the file), and the identifier of the written file is recorded, and all the written files are merged and sorted to obtain the final ordered key-value pair file; the key-value pair key-value value is read from the key-value pair file to obtain the offset, and then the non-sorted column values ​​are read from the file and combined into a row for return.

[0033] In step S1.2, the size of a block is 16MB, and the header of each file stores block metadata, including the starting position of each block, the range of key values ​​and the number of data entries.

[0034] In step S2.1, the splitting rule is based on the hash value of the grouping column, a partition number is customized (for example, the default is 64), the hash value of the partition key is modulo the partition number, and the row data is placed in a specific partition and compressed and written to disk according to the result until all data is processed.

[0035] In step S2.3, if a single partition can be accommodated in the memory, a hash table is established in the memory for aggregation operation; if the memory cannot accommodate a single partition, the data is sorted according to the key value and stored on the disk, and then the ordered aggregation method is used for calculation.

[0036] In step S3.3, if the data at one end of the inner table in a single partition can be accommodated by the memory, a hash table is established in the memory, and the data at one end of the outer table in the partition is read to perform a connection operation;

[0037] If the memory of a single partition cannot accommodate the data at one end of the inner table, the data of the inner table and the outer table are sorted and stored on disk according to the key value, and then the sorted merge join method is used for calculation.

[0038] The inner table is a hash table created during the execution of the HashJoiner operator, and the outer table is a hash table of the result used for detecting data in the HashJoiner operator.

[0039] A device for implementing operator storage based on file storage, characterized in that it includes a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the above method steps when executing the computer program.

[0040] A readable storage medium, characterized in that: a computer program is stored on the readable storage medium, and the computer program implements the above method steps when executed by a processor.

[0041] The beneficial effect of the present invention is that the operator disk implementation method based on file storage enables calculation to be independent of memory resources. When memory resources are limited, SQL statements can be processed based on the disk and correct results can be output without triggering errors or exceptions, thereby greatly improving execution performance. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0043] Attached Figure 1 It is a schematic diagram of the disk calculation implementation method of the Sort operator of the present invention.

[0044] Attached Figure 2 It is a schematic diagram of the disk computing implementation method of the HashAggregator operator of the present invention.

[0045] Attached Figure 3 The figure is a schematic diagram of the disk calculation implementation method of the HashJoiner operator of the present invention. DETAILED DESCRIPTION

[0046] In order to enable those skilled in the art to better understand the technical solutions in the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0047] The mechanism of dropping operators to disk increases the fault tolerance of the database and can ensure the continuity of upper-layer business to a certain extent. Although it is necessary to avoid dropping operators to disk for performance reasons, this mechanism is indispensable in some necessary scenarios. In actual resource allocation, the disk space is much larger than the memory space. When memory calculation cannot meet the needs, data can be organized and written to the disk in a specific form. During calculation, the data can be read and calculated in units of files. At the same time, the implementation methods of data dropping to disk and calculation are different in different operators. It is also possible to perform corresponding optimization processing according to the different functions of operators to improve certain computing efficiency.

[0048] The method for implementing the operator storage on disk based on file storage optimizes the data organization mode, file organization mode and calculation processing mode of the Sort operator, HashAggregator operator and HashJoiner operator when the amount of calculated data exceeds the memory size limit. Specifically, it includes the following steps:

[0049] Step S1: Implementation of disk calculation of Sort operator

[0050] The Sort operator is used to sort the data and sort the attribute column of an input data set in ascending or descending order; Step S1.1, data organization method of the Sort operator

[0051] When the memory sorting exceeds the limit, each row of data in the input data set is split into two parts: the sorting column and the non-sorting column. The data of the non-sorting column is written to a file, the offset in the file when written is returned, and the offset value and the sorting column are combined into a key-value pair to sort.

[0052] If the memory is sufficient to sort the key-value pairs, the sorted key-value sequence is returned. According to the order of the new key-value sequence, the value values ​​are taken as offsets to read the non-sorted columns from the file, and then combined with the corresponding key values ​​to form a complete data row and returned.

[0053] Step S1.2: File organization of the Sort operator

[0054] A single file is organized by block (a storage granularity smaller than a file). When querying a key-value pair, first query the block metadata to determine the block location to be searched, and then search for the key-value pair data based on the specific block.

[0055] Step S1.3: Calculation of Sort operator

[0056] When the Sort operator performs disk calculation, if the number of columns involved in sorting accounts for 50% of the total number of columns, each sorted row is used as a key-value pair for sorting. At this time, a key-value pair search is performed directly and a row of results is returned.

[0057] If the proportion of sorted columns to the total number of columns is less than 50%, the non-sorted columns are stored separately, and the offset of the non-sorted columns in the file is used as the value and sorted column; at this time, a key-value pair search and a file search are performed, and a row of results is returned;

[0058] Step S2: Implementation of disk calculation of HashAggregator operator

[0059] The HashAggregator operator groups and aggregates data in database queries. When there is no sorting feature, the aggregate column (the column that participates in the keyword group by in the SQL statement) is used.

[0060] Step S2.1: Data organization of HashAggregator operator

[0061] When the memory calculation exceeds the limit, the input data set is split by hash partitioning.

[0062] Step S2.2: File organization of HashAggregator operator

[0063] Each partition is saved as a file, with the grouping column as the key value and the remaining columns as the value. The key-value pairs are compressed and saved in the file.

[0064] Step S2.3: Calculation processing of HashAggregator operator

[0065] Perform aggregation operations on each partition in turn and output the results.

[0066] Step S3: Implementation of disk calculation of HashJoiner operator

[0067] When processing large data sets, the HashJoiner operator uses a hash table to improve the efficiency of the join operation;

[0068] Step S3.1: Data organization of HashJoiner operator

[0069] When the memory calculation exceeds the limit, for the data sets input at both ends, the hash values ​​are calculated for the connection columns (the equivalent columns in the SQL statement) respectively, following the same data organization method as the HashAggregator operator in step 2.1;

[0070] Step S3.2: File organization of HashJoiner operator

[0071] Follow the same file organization as the HashAggregator operator in step 2.1;

[0072] Step S3.3: Calculation and processing of HashJoiner operator

[0073] Perform join operations on each partition in turn and output the results.

[0074] In the step S1.1, if the memory is insufficient to sort the key-value pairs, the key-value pairs are written to the file according to the memory size limit (for example, the memory size limit is 512MB, the full 512MB is sorted, and then written to the file), and the identifier of the written file is recorded, and all the written files are merged and sorted to obtain the final ordered key-value pair file; the key-value pair key-value value is read from the key-value pair file to obtain the offset, and then the non-sorted column values ​​are read from the file and combined into a row for return.

[0075] In step S1.2, the size of a block is 16MB, and the header of each file stores block metadata, including the starting position of each block, the range of key values ​​and the number of data entries.

[0076] In step S2.1, the splitting rule is based on the hash value of the grouping column, a partition number is customized (for example, the default is 64), the hash value of the partition key is modulo the partition number, and the row data is placed in a specific partition and compressed and written to disk according to the result until all data is processed.

[0077] In step S2.3, if a single partition can be accommodated in the memory, a hash table is established in the memory for aggregation operation; if the memory cannot accommodate a single partition, the data is sorted according to the key value and stored on the disk, and then the ordered aggregation method is used for calculation.

[0078] Ordered Aggregate is a subquery used to calculate aggregate functions in SQL, which sorts the result set in a specified order. Unlike ordinary aggregate functions, Ordered Aggregate can return the first or last values ​​in each group, not just the sum, average, etc. of the entire group.

[0079] In step S3.3, if the data at one end of the inner table (the hash table created during the execution of the HashJoiner operator) in a single partition can be accommodated by the memory, a hash table is created in the memory, and the data at one end of the outer table (the hash table used for detecting data results in the HashJoiner operator) in the partition is read to perform a join operation;

[0080] If the memory of a single partition cannot accommodate the data at one end of the inner table, the data of the inner table and the outer table are sorted and stored on disk according to the key value, and then the sorted merge join method is used for calculation.

[0081] Inner table: Use the INNER JOIN keyword to join two or more tables together and return the matching rows between them. The result set is returned only when the join condition is met.

[0082] Outer table: Use the OUTER JOIN keyword to join two or more tables together and return all the rows between them. If the join condition is not met, a NULL value is returned.

[0083] The file storage-based operator disk implementation device includes a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the above method steps when executing the computer program.

[0084] The readable storage medium stores a computer program, and when the computer program is executed by a processor, the above method steps are implemented.

[0085] The embodiment described above is only one specific implementation of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for implementing operator storage based on file storage, characterized in that: When the amount of calculated data exceeds the memory size limit, the data organization method, file organization method, and calculation processing method of the Sort operator, HashAggregator operator, and HashJoiner operator are optimized; specifically, the following steps are included: Step S1: Implementation of disk calculation of Sort operator The Sort operator is used to sort data and sort an attribute column of an input data set in ascending or descending order; Step S1.1: Data organization of the Sort operator When the memory sorting exceeds the limit, each row of data in the input data set is split into two parts: the sorting column and the non-sorting column. The data of the non-sorting column is written to a file, the offset in the file when written is returned, and the offset value and the sorting column are combined into a key-value pair to sort. If the memory is sufficient to sort the key-value pairs, the sorted key-value sequence is returned. According to the order of the new key-value sequence, the value values ​​are taken as offsets to read the non-sorted columns from the file, and then combined with the corresponding key values ​​to form a complete data row and returned. Step S1.2: File organization of the Sort operator A single file is organized by blocks. When querying a key-value pair, first query the block metadata to determine the block location to be searched, and then search for the key-value pair data based on the specific block. Step S1.3: Calculation of Sort operator When the Sort operator performs disk calculation, if the number of columns involved in sorting accounts for 50% of the total number of columns, each sorted row is used as a key-value pair for sorting. At this time, a key-value pair search is performed directly and a row of results is returned. If the proportion of sorted columns to the total number of columns is less than 50%, the non-sorted columns are stored separately, and the offset of the non-sorted columns in the file is used as the value and sorted column. At this time, a key-value pair search and a file search are performed, and a row of results is returned. Step S2: Implementation of disk calculation of HashAggregator operator The HashAggregator operator groups and aggregates data in database queries. When there is no sorting feature, the aggregate column is used. The aggregate column is the column that participates in the keyword group by in the SQL statement. Step S2.1: Data organization of HashAggregator operator When the memory calculation exceeds the limit, the input data set is split by hash partitioning. The splitting rule is based on the hash value of the grouping column. A custom partition number is set, and the hash value of the partition key is modulo the partition number. Based on the result, the row data is placed in a specific partition and compressed and written to the disk until all data is processed; Step S2.2: File organization of HashAggregator operator Each partition is saved as a file, with the grouping column as the key value and the remaining columns as the value. The key-value pairs are compressed and saved in the file. Step S2.3: Calculation processing of HashAggregator operator Perform aggregation operations on each partition in turn and output the results; If a single partition can be accommodated in the memory, a hash table is created in the memory for aggregation operations. If a single partition cannot be accommodated in the memory, the data is sorted by key value and stored on the disk, and then the ordered aggregation method is used for calculation. Step S3: Implementation of disk calculation of HashJoiner operator When processing large data sets, the HashJoiner operator uses a hash table to improve the efficiency of the join operation; Step S3.1: Data organization of HashJoiner operator When the memory calculation exceeds the limit, for the data sets input at both ends, the hash values ​​are calculated for the connection columns respectively, following the same data organization method as the HashAggregator operator in step 2.1; the connection columns are equivalent columns in the SQL statement; Step S3.2: File organization of HashJoiner operator Follow the same file organization as the HashAggregator operator in step 2.1; Step S3.3: Calculation and processing of HashJoiner operator Perform join operations on each partition in turn and output the results; If the data at one end of the inner table in a single partition can be accommodated in the memory, a hash table is created in the memory and the data at one end of the outer table in the partition is read for join operations; If the memory of a single partition cannot accommodate the data at one end of the inner table, the data of the inner table and the outer table are sorted and stored on disk according to the key value, and then the sorted merge join method is used for calculation.

2. The method for implementing operator storage based on file storage according to claim 1 is characterized in that: In the step S1.1, if the memory is insufficient to sort the key-value pairs, the key-value pairs are written to files according to the memory size limit, and the identifiers of the written files are recorded, and all the written files are merged and sorted to obtain the final ordered key-value pair files; Read the key-value pair key-value value from the key-value file to obtain the offset, and then read the non-sorted column values ​​from the file, combine them into a row and return them.

3. The method for implementing operator storage based on file storage according to claim 1 is characterized in that: In step S1.2, the size of a block is 16MB, and the header of each file stores block metadata, including the starting position of each block, the range of key values ​​and the number of data entries.

4. The method for implementing operator storage based on file storage according to claim 1, characterized in that: The inner table is a hash table created during the execution of the HashJoiner operator, and the outer table is a hash table of the result used for detecting data in the HashJoiner operator.

5. A device for implementing operator storage based on file storage, characterized in that: The method comprises a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the method steps as claimed in any one of claims 1 to 4 when executing the computer program.

6. A readable storage medium, characterized in that: The readable storage medium stores a computer program, and when the computer program is executed by a processor, the method steps according to any one of claims 1 to 4 are implemented.

Images