Hive table automatic sorting and compression method, device, equipment and medium

By acquiring and analyzing Hive table data information and selecting the optimal sorting field for sorting and compression, the problem of unsatisfactory compression effect of Hive table data files in the prior art is solved, and a higher compression ratio and storage efficiency are achieved.

CN118643015BActive Publication Date: 2024-11-05HANGZHOU YOUZAN TECH CO LTD
View PDF 8 Cites 0 Cited by

Patent Information

Application Number
CN202411107570.0
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-13
Publication Date
2024-11-05
Estimated Expiration
2044-08-13

AI Technical Summary

Technical Problem

In the prior art, the compression effect of Hive table data files is not ideal, and the content distribution cannot be optimized using business data characteristics, resulting in wasted storage space.

Method used

By obtaining Hive table data information, including field characteristics, field blood relationship information and operation configuration information, detecting the specified sorting field, filtering candidate sorting fields, calculating field correlation, selecting the optimal sorting field for sorting compression, adapting to compression algorithms, achieving higher compression ratio.

Benefits of technology

Selecting the optimal sorting field through custom algorithms and data features, sorting the Hive table data significantly improves the compression ratio, achieving the best compression storage effect and saving storage space.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118643015B_ABST
    Figure CN118643015B_ABST
Patent Text Reader

Abstract

The present invention discloses a method, device, equipment and medium for automatic sorting and compression of Hive tables. The method comprises: obtaining Hive table data information; when the current Hive table does not specify a sorting field, obtaining the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields according to field lineage information, and determining the sorting fields of the Hive table based on the lineage sorting fields; screening the sorting fields based on field characteristics and screening strategies to obtain candidate sorting fields; performing field correlation calculation on the candidate sorting fields based on a correlation calculation strategy to obtain correlation values ​​of each candidate sorting field; selecting several sorting fields based on the correlation values, and comparing pre-compression results of several sorting fields to select the optimal sorting field therefrom; sorting and compressing the Hive table based on the optimal sorting field to achieve the best compression storage effect, thereby saving storage space.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing, and in particular to a method, device, equipment and medium for automatically sorting and compressing a Hive table. Background Art

[0002] In a big data system, the corresponding data in the Hive table is stored in a distributed file system (Hadoop Distributed File System, HDFS). In order to save space, the files need to be stored in a compressed format. In existing solutions, files are usually compressed using an indifferent algorithm based on character indexes and encodings.

[0003] However, although existing solutions have performed storage optimization at the basic component level, they have not utilized business data characteristics to optimize content distribution, which has greatly reduced the final compression effect.

[0004] Therefore, no effective solution has been proposed for the problem that the compression effect of Hive table data files in the prior art methods is not ideal. Summary of the invention

[0005] The embodiments of the present invention provide a method, device, equipment and medium for automatic sorting and compression of Hive tables, aiming to solve the problem of unsatisfactory compression effect on Hive table data files in the prior art methods.

[0006] In a first aspect, an embodiment of the present invention provides a method for automatically sorting and compressing a Hive table, the method comprising:

[0007] Obtain Hive table data information; wherein the Hive table data information includes Hive table metadata, field characteristics, field lineage information, and operation configuration information;

[0008] Based on the running configuration information, a detection result of a designated sorting field of the current Hive table is obtained. When the detection result of the designated sorting field is that the current Hive table has no designated sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field;

[0009] Performing screening processing on the sorting fields of the Hive table based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table;

[0010] Perform field correlation calculation processing on the candidate sorting fields based on the correlation calculation strategy to obtain correlation values ​​of each candidate sorting field;

[0011] Sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on pre-compression results of the several sorting fields based on a preset comparison strategy to obtain analysis results, and selecting the optimal sorting field of the Hive table from the several sorting fields based on the analysis results;

[0012] The Hive table is sorted and compressed based on the optimal sort field to obtain a compression execution result and store the compression execution result in a preset storage area.

[0013] In a second aspect, an embodiment of the present invention provides a Hive table automatic sorting and compression device, which includes:

[0014] An acquisition unit, used to acquire Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information;

[0015] A field screening unit is used to detect and obtain a specified sorting field detection result of the current Hive table based on the running configuration information. When the specified sorting field detection result is that the current Hive table has no specified sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field.

[0016] The sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table.

[0017] Based on the correlation calculation strategy, the candidate sorting fields are subjected to field correlation calculation processing to obtain correlation values ​​of each candidate sorting field.

[0018] Sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on pre-compression results of the several sorting fields based on a preset comparison strategy to obtain analysis results, and selecting the optimal sorting field of the Hive table from the several sorting fields based on the analysis results;

[0019] A compression unit is used to sort and compress the Hive table based on the optimal sort field, obtain a compression execution result, and store the compression execution result in a preset storage area.

[0020] In a third aspect, an embodiment of the present invention further provides a computer device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method described in the first aspect when executing the computer program.

[0021] In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the processor executes the method described in the first aspect above.

[0022] The embodiment of the present invention provides a method and device for automatically sorting and compressing a Hive table. Obtain Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information; obtain the designated sorting field detection result of the current Hive table based on the operation configuration information detection, and when the designated sorting field detection result is that the current Hive table has no designated sorting field, obtain the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields according to the field lineage information, and determine the sorting field of the Hive table based on the lineage sorting field; filter the sorting fields of the Hive table based on the field characteristics and the filtering strategy to obtain the candidate sorting fields of the Hive table; perform field correlation calculation on the candidate sorting fields based on the correlation calculation strategy to obtain the correlation values ​​of each candidate sorting field; sort the candidate sorting fields based on the correlation values, select several sorting fields from the sorted candidate sorting fields, and compare and analyze the pre-compression results of several sorting fields based on the preset comparison strategy to obtain the analysis result, and select the optimal sorting field of the Hive table from the several sorting fields based on the analysis result; sort and compress the Hive table based on the optimal sorting field to obtain the compression execution result and store the compression execution result in the preset storage area.

[0023] In the above method, after obtaining data features through analysis of the original Hive table file data, the sorting fields of the Hive table are screened based on the custom algorithm and data features, and the optimal sorting fields are selected. Finally, the Hive table data content is sorted by the optimal sorting fields, and its record order is changed to adapt to the compression algorithm. Therefore, compressing the Hive table through the optimal sorting fields can achieve a higher compression ratio and achieve the best compression storage effect, thereby saving storage space. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0025] Figure 1 A flow chart of a method for automatically sorting and compressing a Hive table provided in an embodiment of the present invention;

[0026] Figure 2 A schematic diagram of a sub-process of a Hive table automatic sorting and compression method provided by an embodiment of the present invention;

[0027] Figure 3 A schematic diagram of another sub-process of the Hive table automatic sorting and compression method provided by an embodiment of the present invention;

[0028] Figure 4 A schematic block diagram of a Hive table automatic sorting and compression device provided by an embodiment of the present invention;

[0029] Figure 5 A schematic block diagram of a computer device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0030] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are 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 are within the scope of protection of the present invention.

[0031] It should be understood that when used in this specification and the appended claims, the terms "include" and "comprises" indicate the presence of described features, integers, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0032] It should also be understood that the terms used in this specification of the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the specification of the present invention and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0033] It should be further understood that the term "and / or" used in the present description and the appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.

[0034] The embodiment of the present invention provides a method for automatically sorting and compressing a Hive table. Figure 1 A flow chart of a method for automatically sorting and compressing a Hive table provided by an embodiment of the present invention is shown in FIG. Figure 1 As shown, the method includes steps S110 to S160.

[0035] S110, obtaining Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information.

[0036] Preferably, before obtaining the Hive table data information, the metadata of the Hive table is collected and obtained; wherein the metadata of the Hive table includes the basic information, fields, field types, and partition information of the Hive table, and the basic information includes the update time, file size, total number of rows, table name, etc. of the Hive table. It should be noted that the update time and file size can be obtained through the client provided by the distributed file system, and the total number of rows can be calculated through the SQL database. In addition, if the current Hive table is a partitioned table, it is also necessary to obtain the data of each partition.

[0037] Then, based on the file size and compression ratio calculation strategy, the compression ratio of the Hive table is calculated to obtain the compression ratio calculation value of the Hive table; specifically, assuming that the space occupied by the plain text of the Hive table is raw_size, and the space occupied by the compressed text of the Hive table is compressed_size, then the file compression ratio is compress_ratio=raw_size / compressed_size. It should be noted that the compression ratio in this embodiment is the ratio of the size of the Hive table before and after sorting and compression, and the larger the compression ratio, the better the compression effect.

[0038] Based on the metadata of the Hive table, the maximum length, maximum data value, and corresponding repetition and tilt of each field in the Hive table are calculated to determine the field characteristics of each field. Among them, the repetition is calculated by dividing the total number of rows of a field by the deduplication count. For example, if the total number of rows of a field is total_cnt and the deduplication count is distinct_cnt, then the repetition repetition = total_cnt / distinct_cnt. The tilt is calculated by first counting the number corresponding to each value in the field, and taking the maximum number and median number, then the tilt tilt = maximum number / median number. It should be noted that if the tilt is too high, it will lead to uneven resource usage, which may cause compression failure.

[0039] Get the SQL data of the business task and obtain the field lineage information in the Hive table by parsing the SQL data.

[0040] Based on the actual situation, manually set the specified sorting fields, effective compression ratio, duplication threshold range and maximum supported processing data volume of the Hive table in the running configuration. It should be noted that if the duplication of the field is too low, the compression effect will be poor; if the duplication of the field is too high, it will exceed the task processing capacity or take too long.

[0041] In this embodiment, the Hive table data information is obtained through the above steps, mainly including field characteristics, field lineage information, and operation configuration information.

[0042] S120. Obtain the designated sort field detection result of the current Hive table based on the running configuration information. When the designated sort field detection result is that the current Hive table has no designated sort field, obtain the lineage fields of all fields in the Hive table and the lineage sort fields contained in the lineage fields according to the field lineage information, and determine the sort field of the Hive table based on the lineage sort fields.

[0043] In this embodiment, the specified sorting field detection result of the current Hive table is obtained based on the running configuration information detection. When the specified sorting field detection result is that the current Hive table has no specified sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field. For example, assuming that field c1 of table C comes from field a1 of table A1; field c2 comes from field b1 of table B1, and a1 and b1 are clearly specified as sorting fields of tables A1 and B1 respectively, then c1 and c2 are likely to be used as sorting fields of table C.

[0044] S130 , screening the sorting fields of the Hive table based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table.

[0045] Preferably, in this embodiment, the sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table, including:

[0046] Get the sorting field of the Hive table, and then filter the sorting fields contained in the sorting field of the Hive table based on the repetition value of the field, remove the fields with repetition lower than the preset threshold, filter the sorting fields contained in the sorting field of the Hive table based on the maximum duplicate record value of the field, remove the fields with the maximum duplicate record value higher than the preset threshold, filter the sorting fields contained in the sorting field of the Hive table based on the inclination of the field, remove the fields with inclination greater than the preset threshold, and finally obtain the candidate sorting fields.

[0047] S140 . Perform field correlation calculation processing on the candidate sorting fields based on the correlation calculation strategy to obtain correlation values ​​of the candidate sorting fields.

[0048] In this embodiment, field correlation calculation processing is performed on the candidate sorting fields based on the correlation calculation strategy to obtain the correlation value of each candidate sorting field.

[0049] In one embodiment, if Figure 2 As shown, step S140 includes sub-steps S141-S144.

[0050] S141. Obtain candidate sorting fields.

[0051] S142. Based on the coding strategy, the candidate sorting fields are subjected to coding conversion processing to obtain an array of uniform field types, and the correlation coefficient of the array of uniform field types is calculated using a Spearman correlation coefficient model to obtain a correlation matrix between the candidate sorting fields.

[0052] Specifically, assume that the candidate sorting fields are c1, c2, c3, c4, and c5, among which the field types are numeric and string. Then, after converting all string field codes into hash code values, convert the numeric and hash code values ​​into a dynamic array of double-precision floating-point types. Finally, the Spearman correlation coefficient model is used to calculate the correlation coefficient of the dynamic array of double-precision floating-point types to obtain the correlation matrix between the candidate sorting fields.

[0053] S143. While calculating the number of correlation coefficients between each candidate sorting field and other candidate sorting fields that exceed a preset threshold based on the correlation matrix, a weight of each candidate sorting field is set based on the field type.

[0054] Specifically, the number of correlation coefficients between each candidate sorting field and other candidate sorting fields exceeding a threshold (e.g., 0.5) is determined based on the correlation matrix, and the weight of each candidate sorting field is set based on the field type. For example, for a string field, the longer the field, the higher the weight is set.

[0055] S144. Based on the weights of the candidate sorting fields whose correlation coefficients with the current candidate sorting field exceed the preset threshold, and the number of correlation coefficients between the current candidate sorting field and other candidate sorting fields that exceed the preset threshold, calculate the correlation value to obtain the correlation value of the current candidate sorting field.

[0056] Specifically, assuming that wi is the weight of the i-th candidate sorting field whose correlation coefficient with the current candidate sorting field exceeds the preset threshold, and n is the number of correlation coefficients between the current candidate sorting field and other candidate sorting fields that exceed the preset threshold, then the correlation value calculation formula of the current candidate sorting field is r = (w1 + w2+ ... wn) / n.

[0057] S150, sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on the pre-compression results of the several sorting fields based on a preset comparison strategy to obtain analysis results, and selecting the optimal sorting field of the Hive table from the several sorting fields based on the analysis results.

[0058] In this embodiment, the candidate sorting fields are sorted based on the size of the corresponding relevant values ​​of the candidate sorting fields, and several sorting fields are selected from the sorted candidate sorting fields. Then, the pre-compression results of several sorting fields are compared and analyzed based on the preset comparison strategy to obtain the analysis results, and the optimal sorting field of the Hive table is selected from several sorting fields based on the analysis results. For example, the compression ratio, compression time, resource usage and other information obtained when the Hive table is pre-compressed through several sorting fields are obtained. Then, the various pre-compression results are compared and analyzed according to certain comparison rules and information such as compression ratio, compression time and resource usage to obtain the analysis results, and the optimal sorting field of the Hive table is selected from several sorting fields according to the analysis results. It should be noted that the higher the compression ratio, the shorter the time consumption, the less resource usage, and the better the compression effect. When specifically analyzing the pre-compression results, factors such as compression ratio, compression time consumption and resource usage can be weighted and calculated, and the pre-compression result with the highest score as the optimal sorting field is taken.

[0059] S160. Sort and compress the Hive table based on the optimal sort field, obtain a compression execution result, and store the compression execution result in a preset storage area.

[0060] In one embodiment, before sorting and compressing the Hive table based on the optimal sorting field, a data path corresponding to the current Hive table or partition is obtained, and the update time and file size of the Hive table are obtained based on the data path; a historical compression result is queried based on the current Hive table, and a compression state of the Hive table is judged based on the historical compression result, the update time and file size of the Hive table to obtain a compression state judgment result;

[0061] When the compression status judgment result shows that the current Hive table is not compressed successfully, the current Hive table is sorted and compressed based on the optimal sort field. When the compression status judgment result shows that the current Hive table is compressed successfully, the current Hive table is not sorted and compressed.

[0062] In this embodiment, after sorting and compressing the Hive table based on the optimal sort field, a compression execution result is obtained and stored in a preset storage area.

[0063] In one embodiment, if Figure 3As shown, step S160 includes sub-steps S161, S162, S163 and S164.

[0064] S161. Construct a database table for storing Hive table sorting.

[0065] S162. Sort and compress the original Hive table based on the optimal sort field to obtain a compression execution result, and store the compression execution result in a database table to obtain a temporary table for storing the compressed data of the original Hive table.

[0066] S163. Compare and judge the file size, table update time, and data content between the original Hive table and the temporary table based on the verification comparison strategy to obtain a compression result.

[0067] Preferably, the file size, table update time and data content between the original Hive table and the temporary table are compared and judged based on the verification comparison strategy to obtain the compression result, including:

[0068] Get the file sizes of the original Hive table and the temporary table, and determine the compression result based on the file size comparison result between the original Hive table and the temporary table. If the file sizes between the original Hive table and the temporary table are inconsistent, the compression result is compression failure.

[0069] Get the initial table update time of the original Hive table before sorting and compression, and get the current table update time of the original Hive table after sorting and compression. When the initial table update time is inconsistent with the current table update time, the compression result is compression failure;

[0070] The compression ratio of the temporary table is calculated based on the file size of the temporary table and the compression ratio calculation strategy to obtain a compression ratio calculation value of the temporary table. When the compression ratio calculation value of the temporary table is less than the minimum compression ratio in the running configuration information, the compression result is compression failure.

[0071] Obtain the data content of the original Hive table and the temporary table, compare the data content between the original Hive table and the temporary table based on the preset verification strategy, and obtain the verification result. When the verification result shows that there is a difference in the data content, the compression result is compression failure; the verification strategy is to compare the number of table records, the number of deduplication of each field, the maximum data value, and the minimum data value;

[0072] The compression result is successful when the file sizes of the original Hive table and the temporary table are the same, the initial table update time of the original Hive table is the same as the current table update time, the calculated compression ratio of the temporary table is greater than the minimum compression ratio in the running configuration information, and the data content verification result between the original Hive table and the temporary table is that the data content is consistent.

[0073] S164. When the compression result is compression failure, the failure record is stored in a preset storage area. When the compression result is compression success, the data in the temporary table is swapped with the data in the original Hive table to complete the compression task, and the compressed data is stored in the preset storage area.

[0074] Specifically, when swapping data, if the original Hive table is a non-partitioned table, you can swap the table name to achieve data swap. If the original Hive table is a single-level partitioned table, you can swap the partitions to achieve data swap. If the original Hive table is a multi-level partitioned table, you can swap the corresponding first-level directories of the distributed file system HDFS to achieve data swap.

[0075] In addition, it should be noted that the temporary table is the backup data of the original Hive table. The temporary table will be cleaned up every preset time period to release storage resources.

[0076] In the Hive table automatic sorting and compression method provided in the embodiment of the present invention, Hive table data information is obtained; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information; based on the operation configuration information, the specified sorting field detection result of the current Hive table is detected, and when the specified sorting field detection result is that the current Hive table has no specified sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting fields of the Hive table are determined based on the lineage sorting fields; the Hive table is sorted based on the field characteristics and the screening strategy. The method comprises the following steps: screening the fields to obtain candidate sorting fields of the Hive table; performing field correlation calculation on the candidate sorting fields based on the correlation calculation strategy to obtain the correlation values ​​of each candidate sorting field; sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on the pre-compression results of several sorting fields based on the preset comparison strategy to obtain the analysis results, and selecting the optimal sorting field of the Hive table from several sorting fields based on the analysis results; sorting and compressing the Hive table based on the optimal sorting field to obtain the compression execution result and store the compression execution result in the preset storage area. In the above method, after obtaining the data features by analyzing the original Hive table file data, the sorting fields of the Hive table are screened by the custom algorithm and data features, and the optimal sorting field is selected. Finally, the Hive table data content is sorted by the optimal sorting field, and its record order is changed to adapt to the compression algorithm. Therefore, compressing the Hive table by the optimal sorting field can achieve a higher compression ratio, achieve the best compression storage effect, and save storage space.

[0077] The embodiment of the present invention also provides a Hive table automatic sorting and compression device, Figure 4 A schematic block diagram of a Hive table automatic sorting and compression device provided by an embodiment of the present invention. Figure 4 As shown, the Hive table automatic sorting and compression device 100 includes an acquisition unit 110, a field screening unit 120 and a compression unit 130.

[0078] The acquisition unit 110 is used to acquire Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information.

[0079] Preferably, before obtaining the Hive table data information, the metadata of the Hive table is collected and obtained; wherein the metadata of the Hive table includes the basic information, fields, field types, and partition information of the Hive table, and the basic information includes the update time, file size, total number of rows, table name, etc. of the Hive table. It should be noted that the update time and file size can be obtained through the client provided by the distributed file system, and the total number of rows can be calculated through the SQL database. In addition, if the current Hive table is a partitioned table, it is also necessary to obtain the data of each partition.

[0080] Then, based on the file size and compression ratio calculation strategy, the compression ratio of the Hive table is calculated to obtain the compression ratio calculation value of the Hive table; specifically, assuming that the space occupied by the plain text of the Hive table is raw_size, and the space occupied by the compressed text of the Hive table is compressed_size, then the file compression ratio is compress_ratio=raw_size / compressed_size. It should be noted that the compression ratio in this embodiment is the ratio of the file size of the Hive table before sorting and compression to the file size after sorting and compression, and the larger the compression ratio, the better the compression effect.

[0081] Based on the metadata of the Hive table, the maximum length, maximum data value, and corresponding repetition and tilt of each field in the Hive table are calculated to determine the field characteristics of each field. Among them, the repetition is calculated by dividing the total number of rows of a field by the deduplication count. For example, if the total number of rows of a field is total_cnt and the deduplication count is distinct_cnt, then the repetition repetition = total_cnt / distinct_cnt. The tilt is calculated by first counting the number corresponding to each value in the field, and taking the maximum number and median number, then the tilt tilt = maximum number / median number. It should be noted that if the tilt is too high, it will lead to uneven resource usage, which may cause compression failure.

[0082] Get the SQL data of the business task and obtain the field lineage information in the Hive table by parsing the SQL data.

[0083] Based on the actual situation, manually set the specified sorting fields, effective compression ratio, duplication threshold range and maximum supported processing data volume of the Hive table in the running configuration. It should be noted that if the duplication of the field is too low, the compression effect will be poor; if the duplication of the field is too high, it will exceed the task processing capacity or take too long.

[0084] In this embodiment, the Hive table data information is obtained through the above steps, mainly including field characteristics, field lineage information, and operation configuration information.

[0085] The field screening unit 120 is used to obtain the designated sorting field detection result of the current Hive table based on the running configuration information detection. When the designated sorting field detection result is that the current Hive table has no designated sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting fields of the Hive table are determined based on the lineage sorting fields. The sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table. The candidate sorting fields are subjected to field correlation calculation processing based on the correlation calculation strategy to obtain correlation values ​​of each candidate sorting field. The candidate sorting fields are sorted based on the correlation values. Several sorting fields are selected from the sorted candidate sorting fields. The pre-compression results of several sorting fields are compared and analyzed based on the preset comparison strategy to obtain analysis results. The optimal sorting field of the Hive table is selected from the several sorting fields based on the analysis results.

[0086] The specified sorting field detection result of the current Hive table is obtained based on the running configuration information detection. When the specified sorting field detection result is that the current Hive table has no specified sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field. For example, assuming that field c1 of table C comes from field a1 of table A1; field c2 comes from field b1 of table B1, and a1 and b1 are clearly specified as sorting fields of tables A1 and B1 respectively, then c1 and c2 are likely to be used as sorting fields of table C.

[0087] Preferably, in this embodiment, the sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table, including:

[0088] Get the sorting field of the Hive table, and then filter the sorting fields contained in the sorting field of the Hive table based on the repetition value of the field, remove the fields with repetition lower than the preset threshold, filter the sorting fields contained in the sorting field of the Hive table based on the maximum duplicate record value of the field, remove the fields with the maximum duplicate record value higher than the preset threshold, filter the sorting fields contained in the sorting field of the Hive table based on the inclination of the field, remove the fields with inclination greater than the preset threshold, and finally obtain the candidate sorting fields.

[0089] Based on the correlation calculation strategy, the candidate sorting fields are subjected to field correlation calculation processing to obtain the correlation values ​​of each candidate sorting field, including:

[0090] Get candidate sort fields.

[0091] Based on the encoding strategy, the candidate sorting fields are encoded and converted to obtain an array of uniform field types, and the correlation coefficient of the array of uniform field types is calculated through the Spearman correlation coefficient model to obtain the correlation matrix between the candidate sorting fields.

[0092] Specifically, assume that the candidate sorting fields are c1, c2, c3, c4, and c5, among which the field types are numeric and string. Then, after converting all string field codes into hash code values, convert the numeric and hash code values ​​into a dynamic array of double-precision floating-point types. Finally, the Spearman correlation coefficient model is used to calculate the correlation coefficient of the dynamic array of double-precision floating-point types to obtain the correlation matrix between the candidate sorting fields.

[0093] The number of correlation coefficients between each candidate sorting field and other candidate sorting fields exceeding a preset threshold is calculated based on the correlation matrix, and the weight of each candidate sorting field is set based on the field type.

[0094] Specifically, the number of correlation coefficients between each candidate sorting field and other candidate sorting fields exceeding a threshold (e.g., 0.5) is determined based on the correlation matrix, and the weight of each candidate sorting field is set based on the field type. For example, for a string field, the longer the field, the higher the weight is set.

[0095] Based on the weights of the candidate sorting fields whose correlation coefficients with the current candidate sorting field exceed the preset threshold, and the number of correlation coefficients between the current candidate sorting field and other candidate sorting fields exceeding the preset threshold, the correlation value is calculated to obtain the correlation value of the current candidate sorting field.

[0096] Specifically, assuming that wi is the weight of the i-th candidate sorting field whose correlation coefficient with the current candidate sorting field exceeds the preset threshold, and n is the number of correlation coefficients between the current candidate sorting field and other candidate sorting fields that exceed the preset threshold, then the correlation value calculation formula of the current candidate sorting field is r = (w1 + w2+ ... wn) / n.

[0097] Finally, the candidate sorting fields are sorted based on the size of the corresponding relevant values ​​of the candidate sorting fields, and several sorting fields are selected from the sorted candidate sorting fields. Then, the pre-compression results of several sorting fields are compared and analyzed based on the preset comparison strategy to obtain the analysis results, and the optimal sorting field of the Hive table is selected from several sorting fields based on the analysis results. For example, the compression ratio, compression time consumption, resource usage and other information obtained when the Hive table is pre-compressed through several sorting fields are obtained. Then, the various pre-compression results are compared and analyzed according to certain comparison rules and information such as compression ratio, compression time consumption and resource usage to obtain the analysis results, and the optimal sorting field of the Hive table is selected from several sorting fields according to the analysis results. It should be noted that the higher the compression ratio, the shorter the time consumption, the less resource usage, and the better the compression effect. When specifically analyzing the pre-compression results, factors such as compression ratio, compression time consumption and resource usage can be weighted and calculated, and the pre-compression result of the optimal sorting field with the highest score is taken.

[0098] The compression unit 130 is used to sort and compress the Hive table based on the optimal sort field, obtain the compression execution result, and store the compression execution result in a preset storage area.

[0099] In one embodiment, before sorting and compressing the Hive table based on the optimal sorting field, a data path corresponding to the current Hive table or partition is obtained, and the update time and file size of the Hive table are obtained based on the data path; a historical compression result is queried based on the current Hive table, and a compression state of the Hive table is judged based on the historical compression result, the update time and file size of the Hive table to obtain a compression state judgment result;

[0100] When the compression status judgment result shows that the current Hive table is not compressed successfully, the current Hive table is sorted and compressed based on the optimal sort field. When the compression status judgment result shows that the current Hive table is compressed successfully, the current Hive table is not sorted and compressed.

[0101] In this embodiment, after sorting and compressing the Hive table based on the optimal sort field, a compression execution result is obtained and the compression execution result is stored in a preset storage area, including:

[0102] Build a database table for storing Hive table sorts.

[0103] The original Hive table is sorted and compressed based on the optimal sort field to obtain a compression execution result, and the compression execution result is stored in a database table to obtain a temporary table for storing the compressed data of the original Hive table.

[0104] Based on the verification comparison strategy, the file size, table update time and data content between the original Hive table and the temporary table are compared and judged to obtain the compression result.

[0105] Preferably, the file size, table update time and data content between the original Hive table and the temporary table are compared and judged based on the verification comparison strategy to obtain the compression result, including:

[0106] Get the file sizes of the original Hive table and the temporary table, and determine the compression result based on the file size comparison result between the original Hive table and the temporary table. If the file sizes between the original Hive table and the temporary table are inconsistent, the compression result is compression failure.

[0107] Get the initial table update time of the original Hive table before sorting and compression, and get the current table update time of the original Hive table after sorting and compression. When the initial table update time is inconsistent with the current table update time, the compression result is compression failure;

[0108] The compression ratio of the temporary table is calculated based on the file size of the temporary table and the compression ratio calculation strategy to obtain a compression ratio calculation value of the temporary table. When the compression ratio calculation value of the temporary table is less than the minimum compression ratio in the running configuration information, the compression result is compression failure.

[0109] Obtain the data content of the original Hive table and the temporary table, compare the data content between the original Hive table and the temporary table based on the preset verification strategy, and obtain the verification result. When the verification result shows that there is a difference in the data content, the compression result is compression failure; the verification strategy is to compare the number of table records, the number of deduplication of each field, the maximum data value, and the minimum data value;

[0110] The compression result is successful when the file sizes of the original Hive table and the temporary table are the same, the initial table update time of the original Hive table is the same as the current table update time, the calculated compression ratio of the temporary table is greater than the minimum compression ratio in the running configuration information, and the data content verification result between the original Hive table and the temporary table is that the data content is consistent.

[0111] If the compression result is compression failure, the failed record is stored in the preset storage area. If the compression result is compression success, the data in the temporary table is swapped with the data in the original Hive table to complete the compression task, and the compressed data is stored in the preset storage area.

[0112] Specifically, when swapping data, if the original Hive table is a non-partitioned table, you can swap the table name to achieve data swap. If the original Hive table is a single-level partitioned table, you can swap the partitions to achieve data swap. If the original Hive table is a multi-level partitioned table, you can swap the corresponding first-level directories of the distributed file system HDFS to achieve data swap.

[0113] In addition, it should be noted that the temporary table is the backup data of the original Hive table. The temporary table will be cleaned up every preset time period to release storage resources.

[0114] The Hive table automatic sorting and compression device provided in the embodiment of the present invention is applied to the above-mentioned Hive table automatic sorting and compression method to obtain Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information; based on the operation configuration information, the designated sorting field detection result of the current Hive table is detected, and when the designated sorting field detection result is that the current Hive table has no designated sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting fields of the Hive table are determined based on the lineage sorting fields; based on the field characteristics and the screening strategy The sorting fields of the Hive table are screened to obtain candidate sorting fields of the Hive table; field correlation calculation is performed on the candidate sorting fields based on the correlation calculation strategy to obtain the correlation values ​​of each candidate sorting field; the candidate sorting fields are sorted based on the correlation values, and several sorting fields are selected from the sorted candidate sorting fields, and the pre-compression results of several sorting fields are compared and analyzed based on the preset comparison strategy to obtain the analysis results, and the optimal sorting field of the Hive table is selected from the several sorting fields based on the analysis results; the Hive table is sorted and compressed based on the optimal sorting field to obtain the compression execution result and the compression execution result is stored in the preset storage area. In the above method, after obtaining the data features by analyzing the original Hive table file data, the sorting fields of the Hive table are screened by the custom algorithm and data features, and the optimal sorting field is selected, and finally the Hive table data content is sorted by the optimal sorting field, and its record order is changed to adapt to the compression algorithm. Therefore, compressing the Hive table by the optimal sorting field can achieve a higher compression ratio, achieve the best compression storage effect, and save storage space.

[0115] The above Hive table automatic sorting and compression method can be implemented in the form of a computer program. The computer program can be used in Figure 5 Runs on the computer device shown.

[0116] See also Figure 5 , Figure 51 is a schematic block diagram of a computer device provided by an embodiment of the present invention. The computer device may be used to execute a Hive table automatic sorting and compression method.

[0117] See also Figure 5 The computer device 500 includes a processor 502 , a memory and a network interface 505 connected via a system bus 501 , wherein the memory may include a storage medium 503 and an internal memory 504 .

[0118] The storage medium 503 may store an operating system 5031 and a computer program 5032. When the computer program 5032 is executed, the processor 502 may execute the Hive table automatic sorting and compression method, wherein the storage medium 503 may be a volatile storage medium or a non-volatile storage medium.

[0119] The processor 502 is used to provide computing and control capabilities to support the operation of the entire computer device 500 .

[0120] The internal memory 504 provides an environment for the operation of the computer program 5032 in the storage medium 503. When the computer program 5032 is executed by the processor 502, the processor 502 can execute the Hive table automatic sorting and compression method.

[0121] The network interface 505 is used for network communication, such as providing data information transmission, etc. Those skilled in the art will understand that Figure 5 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present invention, and does not constitute a limitation on the computer device 500 to which the solution of the present invention is applied. The specific computer device 500 may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0122] The processor 502 is used to run the computer program 5032 stored in the memory to implement the corresponding functions in the above-mentioned Hive table automatic sorting and compression method.

[0123] Those skilled in the art will understand that Figure 5 The embodiments of the computer device shown in the figure do not constitute a limitation on the specific composition of the computer device. In other embodiments, the computer device may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently. For example, in some embodiments, the computer device may only include a memory and a processor. In such embodiments, the structure and function of the memory and the processor are the same as those of the embodiment of the present invention. Figure 5 The embodiments shown are consistent and will not be described again here.

[0124] It should be understood that in the embodiment of the present invention, the processor 502 may be a central processing unit (CPU), and the processor 502 may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0125] In another embodiment of the present invention, a computer-readable storage medium is provided. The computer-readable storage medium may be a volatile or non-volatile computer-readable storage medium. The computer-readable storage medium stores a computer program, wherein when the computer program is executed by a processor, the steps included in the above-mentioned Hive table automatic sorting and compression method are implemented.

[0126] Those skilled in the art can clearly understand that, for the convenience and simplicity of description, the specific working process of the above-described equipment, devices and units can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here. Those of ordinary skill in the art can appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented with electronic hardware, computer software or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to the function. Whether these functions are executed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to exceed the scope of the present invention.

[0127] In the several embodiments provided by the present invention, it should be understood that the disclosed equipment, devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. Units with the same function may also be combined into one unit. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. In addition, the mutual coupling or direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection through some interfaces, devices or units, or may be an electrical, mechanical or other form of connection.

[0128] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the embodiments of the present invention.

[0129] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0130] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a computer-readable storage medium, including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned computer-readable storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a magnetic disk or an optical disk.

[0131] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed by the present invention, and these modifications or replacements should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.

Claims

1. A method for automatic sorting and compression of Hive tables. It is characterized in that The method comprises: Obtain Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information; Based on the running configuration information, a detection result of a designated sorting field of the current Hive table is obtained. When the detection result of the designated sorting field is that the current Hive table has no designated sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field; Performing screening processing on the sorting fields of the Hive table based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table; Perform field correlation calculation processing on the candidate sorting fields based on the correlation calculation strategy to obtain correlation values ​​of each candidate sorting field; Sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on pre-compression results of the several sorting fields based on a preset comparison strategy to obtain analysis results, and selecting the optimal sorting field of the Hive table from the several sorting fields based on the analysis results; The Hive table is sorted and compressed based on the optimal sort field to obtain a compression execution result and store the compression execution result in a preset storage area.

2. The method according to claim 1, It is characterized in that Before obtaining Hive table data information, the method includes: Collect and obtain metadata of the Hive table; wherein the metadata of the Hive table includes basic information, fields, field types, and partition information of the Hive table, and the basic information includes update time, file size, and total number of rows of the Hive table; Performing compression ratio calculation processing on the Hive table based on the file size and the compression ratio calculation strategy to obtain a compression ratio calculation value of the Hive table; Calculating the maximum length, maximum data value, and corresponding duplication and skew of each field in the Hive table based on the metadata of the Hive table to determine the field characteristics of each field; Obtain SQL data of the business task, and obtain field lineage information in the Hive table by parsing the SQL data; Based on actual conditions, manually set the specified sorting fields, effective compression ratio, field duplication threshold range, and maximum supported processing data volume of the Hive table in the running configuration.

3. The method according to claim 1 or 2, It is characterized in that The sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table, including: Based on the repetition value of the field, the sorting fields contained in the sorting fields of the Hive table are screened, and the fields whose repetition is lower than the preset threshold are eliminated. The sorting fields in the Hive table are screened based on the maximum duplicate record value of the field, and the fields whose maximum duplicate record value is higher than a preset threshold are removed. The sorting fields included in the sorting fields of the Hive table are screened based on the inclination of the field, and the fields whose inclination is greater than a preset threshold are eliminated to obtain the candidate sorting fields.

4. The method according to claim 1, It is characterized in that The performing field correlation calculation processing on the candidate sorting fields based on the correlation calculation strategy to obtain the correlation value of each candidate sorting field includes: Obtain the candidate sorting field; Based on the coding strategy, the candidate sorting fields are subjected to coding conversion processing to obtain a dynamic array of a unified field type, and the correlation coefficient of the dynamic array of the unified field type is calculated by a Spearman correlation coefficient model to obtain a correlation matrix between the candidate sorting fields; While calculating the number of correlation coefficients between each candidate sorting field and other candidate sorting fields exceeding a preset threshold based on the correlation matrix, a weight of each candidate sorting field is set based on the field type; Based on the weights of the candidate sorting fields whose correlation coefficients with the current candidate sorting field exceed the preset threshold, and the number of correlation coefficients between the current candidate sorting field and other candidate sorting fields exceeding the preset threshold, correlation value calculation is performed to obtain the correlation value of the current candidate sorting field.

5. The method according to claim 1, It is characterized in that Before sorting and compressing the Hive table based on the optimal sort field, the method includes: Obtain the data path corresponding to the current Hive table or partition, and obtain the update time and file size of the Hive table based on the data path; Querying historical compression results based on the current Hive table, and judging the compression status of the Hive table based on the historical compression results, the update time of the Hive table, and the file size, to obtain a compression status judgment result; When the compression status judgment result is that the current Hive table is not compressed successfully, the current Hive table is sorted and compressed based on the optimal sort field; when the compression status judgment result is that the current Hive table has been compressed successfully, the current Hive table is not sorted and compressed.

6. The method according to claim 1, It is characterized in that The step of sorting and compressing the Hive table based on the optimal sort field, obtaining a compression execution result and storing it in a preset storage area includes: Build a database table for storing Hive table sorting; Sorting and compressing the original Hive table based on the optimal sorting field to obtain a compression execution result, and storing the compression execution result in the database table to obtain a temporary table storing the compressed data of the original Hive table; Based on the verification comparison strategy, the file size, table update time and data content between the original Hive table and the temporary table are compared and judged to obtain the compression result; When the compression result is a compression failure, the failure record is stored in a preset storage area. When the compression result is a compression success, the data in the temporary table is swapped with the data in the original Hive table to complete the compression task, and the compressed data is stored in the preset storage area.

7. The method according to claim 6, It is characterized in that The file size, table update time and data content between the original Hive table and the temporary table are compared and judged based on the verification comparison strategy to obtain the compression result, including: Obtaining the file sizes of the original Hive table and the temporary table, and judging the compression result based on the file size comparison result between the original Hive table and the temporary table. When the file sizes between the original Hive table and the temporary table are inconsistent, the compression result is compression failure. Obtaining the initial table update time of the original Hive table before sorting and compression, and obtaining the current table update time of the original Hive table after sorting and compression. When the initial table update time is inconsistent with the current table update time, the compression result is compression failure; Performing compression ratio calculation processing on the temporary table based on the file size of the temporary table and the compression ratio calculation strategy to obtain a compression ratio calculation value of the temporary table, and when the compression ratio calculation value of the temporary table is less than the minimum compression ratio in the running configuration information, the compression result is compression failure; Obtaining data contents of the original Hive table and the temporary table, comparing the data contents between the original Hive table and the temporary table based on a preset verification strategy, and obtaining a verification result. When the verification result shows that there is a difference in the data contents, the compression result is compression failure; When the file sizes of the original Hive table and the temporary table are consistent, the initial table update time of the original Hive table is consistent with the current table update time, the calculated compression ratio of the temporary table is greater than the minimum compression ratio in the running configuration information, and the data content verification result between the original Hive table and the temporary table is that the data content is consistent, the compression result is compression success.

8. A Hive table automatic sorting and compression device, It is characterized in that The device comprises: An acquisition unit, used to acquire Hive table data information; wherein the Hive table data information includes field characteristics, field lineage information, and operation configuration information; A field screening unit is used to detect and obtain a specified sorting field detection result of the current Hive table based on the running configuration information. When the specified sorting field detection result is that the current Hive table has no specified sorting field, the lineage fields of all fields in the Hive table and the lineage sorting fields contained in the lineage fields are obtained according to the field lineage information, and the sorting field of the Hive table is determined based on the lineage sorting field. The sorting fields of the Hive table are screened based on the field characteristics and the screening strategy to obtain candidate sorting fields of the Hive table. Based on the correlation calculation strategy, the candidate sorting fields are subjected to field correlation calculation processing to obtain correlation values ​​of each candidate sorting field. Sorting the candidate sorting fields based on the correlation values, selecting several sorting fields from the sorted candidate sorting fields, and performing comparative analysis on pre-compression results of the several sorting fields based on a preset comparison strategy to obtain analysis results, and selecting the optimal sorting field of the Hive table from the several sorting fields based on the analysis results; A compression unit is used to sort and compress the Hive table based on the optimal sort field, obtain a compression execution result, and store the compression execution result in a preset storage area.

9. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the computer program, the method according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium, It is characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Citation Information

Patent Citations

  • Hive table consistency verification method, system and device and storage medium

    CN111291130A

  • Method and device for processing data based on column type storage format

    CN111723089A

  • File compression method, device and equipment applied to massive files, and storage medium

    CN111767258A

  • Distributed data processing method and device, electronic equipment, and storage medium

    CN113886491A

  • File compression processing method and device, equipment and medium

    CN116303297A