Data compression method and device of database, electronic equipment and storage medium

Abstract

The application discloses a data compression method and device of a database, electronic equipment and a storage medium, wherein the database is used for storing multiple pieces of data, the method comprises the following steps: acquiring a data table of the database, wherein the data table comprises multiple field names and field values of each piece of data under each field name; determining a to-be-analyzed set from the data table; performing deduplication statistics on the field values of each field name in the to-be-analyzed set to obtain the deduplicated number of the field values of each field name; determining at least one reference field name from the multiple field names by using the deduplicated number; dividing the data corresponding to the to-be-analyzed set into at least two to-be-compressed sets based on the at least one reference field name, wherein the field values of at least one reference field name in each to-be-compressed set are the same; and performing compression processing on the to-be-compressed sets respectively. Through the above method, the data compression rate of the database can be reduced.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a data compression method, apparatus, electronic device, and storage medium for a database. Background Technology

[0002] A database is a data repository with a large storage capacity, capable of holding millions, tens of millions, or even hundreds of millions of data entries. However, currently, databases typically use the default compression parameters of their computing engines to compress data, resulting in a low compression ratio and consequently, high storage costs. Summary of the Invention

[0003] The first aspect of this application provides a data compression method for a database used to store multiple data entries, comprising: obtaining a data table of the database, wherein the data table includes multiple field names and field values ​​for each data entry under each field name; determining a set to be analyzed from the data table; performing deduplication statistics on the field values ​​under each field name in the set to be analyzed to obtain the number of deduplicated field values ​​under each field name; using the deduplicated number, determining at least one reference field name from the multiple field names; based on the at least one reference field name, dividing the data corresponding to the set to be analyzed into at least two sets to be compressed, wherein the field values ​​under at least one reference field name are the same in each set to be compressed; and performing compression processing on the sets to be compressed respectively.

[0004] A second aspect of this application provides a data compression apparatus for a database, the database being used to store multiple data entries, comprising: an acquisition module for acquiring a data table of the database, wherein the data table includes multiple field names and field values ​​for each data entry under each field name; a first determination module for determining a set to be analyzed from the data table; a statistics module for performing deduplication statistics on the field values ​​under each field name in the set to be analyzed to obtain the number of deduplicated field values ​​under each field name; a second determination module for determining at least one reference field name from the multiple field names using the number of deduplicated field values; a partitioning module for partitioning the data corresponding to the set to be analyzed into at least two sets to be compressed based on at least one reference field name; and a compression module for performing compression processing on the sets to be compressed separately.

[0005] A third aspect of this application provides an electronic device including a memory and a processor coupled to each other, the memory for storing program data and the processor for executing the program data to implement the aforementioned method.

[0006] A fourth aspect of this application provides a computer-readable storage medium storing program data, which, when executed by a processor, is used to implement the aforementioned method.

[0007] The beneficial effects of this application are as follows: Unlike the prior art, this application obtains a database table, which includes multiple field names and the field values ​​of each data entry under each field name. Then, it determines a set to be analyzed from the data table, performs deduplication on the field values ​​under each field name in the set to be analyzed, and obtains the number of deduplicated field values ​​under each field name. Using this deduplication count, it determines at least one reference field name from the multiple field names. Based on this at least one reference field name, it divides the data corresponding to the set to be analyzed into at least two sets to be compressed. Finally, it compresses each set to be compressed. Since at least one reference field name in each set to be compressed has the same field value, the higher the data regularity in the set to be compressed, the higher the data compression rate. This reduces the amount of data stored in the database and reduces storage costs. Attached Figure Description

[0008] To more clearly illustrate the technical solutions in this application, the accompanying drawings required in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Among them: Figure 1 This is a flowchart illustrating an embodiment of the data compression method for the database in this application; Figure 2 This is a schematic diagram of the data compression method for the database in this application. Figure 3 yes Figure 2 A schematic diagram of the data analysis interface; Figure 4 yes Figure 1 A flowchart illustrating an implementation method for step S104; Figure 5 yes Figure 4 A flowchart illustrating an embodiment of step S1042; Figure 6 yes Figure 1 A flowchart illustrating another embodiment of step S104; Figure 7 yes Figure 1 A flowchart illustrating an implementation method for step S105; Figure 8 yes Figure 7 A flowchart illustrating an embodiment of step S1052; Figure 9 This is a flowchart illustrating another embodiment of the data compression method for the database in this application; Figure 10 yes Figure 2 A schematic diagram of the database table analysis interface; Figure 11 yes Figure 2 A schematic diagram of the partition analysis interface; Figure 12 yes Figure 2 A schematic diagram of the comparison and analysis interface; Figure 13 This is a schematic block diagram of an embodiment of the data compression device for the database of this application.

[0009] Figure 14 This is a schematic block diagram of the structure of an embodiment of the electronic device of this application; Figure 15 This is a schematic block diagram of an embodiment of the computer-readable storage medium of this application. Detailed Implementation

[0010] In this application, the reference to "embodiment" means that a specific feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0011] The terms "first" and "second" in this application are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this application, "multiple" means at least two, such as two, three, etc., unless otherwise explicitly specified. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to such processes, methods, products, or apparatus.

[0012] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of the embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0013] Please see Figures 1 to 3 , Figure 1 This is a flowchart illustrating an embodiment of the database data compression method of this application. Figure 2This is a schematic diagram of the data compression method for the database in this application. Figure 3 yes Figure 2 A schematic diagram of the data analysis interface. The executing entity of this application is an electronic device such as a mobile phone, computer, or server.

[0014] The method may include the following steps: Step S101: Obtain the data table from the database, which includes multiple field names and the field values ​​for each data entry under each field name.

[0015] The database is used to store multiple data entries, each containing field values ​​under various field names. Optionally, the database can be a Hive database, an Oracle database, or a MySQL database, etc., and correspondingly, the data tables can be Hive tables, Oracle tables, or MySQL tables, etc., used to organize and store data. In this embodiment, a Hive database is used as an example for illustration.

[0016] In some implementations, a database table can be retrieved in response to a user instruction, where the user instruction is used to select a table from the database. The database and the table are simply referred to as a database table.

[0017] like Figure 2 As shown, the electronic device includes a display screen, which provides a display interface 10 for displaying a filtering area 11, a selection area 12, and an analysis area 13. The filtering area 11 is used to filter database tables by region, creation time, etc. Regions can be divided according to the geographical location of the data upload, such as by country (e.g., China, Singapore). Creation time can be accurate to the year, month, or day, such as 2022, October 2022, October 30, 2022, etc. The selection area 12 is used to select the database table name under the filtering area 11. The database table name is generally named according to the business type, such as music.2022. The analysis area 13 displays an analysis button; clicking the analysis button starts the analysis.

[0018] In one application scenario, the user enters "China" and "music.2022" in the filter area 11 and the selection area 12 respectively, and then clicks the analysis button in the analysis area 13. Correspondingly, the electronic device responds to the user's command, determines the corresponding database and data table, and can then obtain the data table from the database.

[0019] In other embodiments, depending on actual needs, filtering area 11 may not be set, and the database table name may be directly entered through selection area 12. Optionally, in addition to clicking the analysis button to start the analysis, the user can also start the analysis through other input methods (such as voice), that is, start the execution of this method; this is not limited here.

[0020] Step S102: Identify a set to be analyzed from the data table.

[0021] In some implementations, the data table includes multiple field names and field values ​​for each data entry under each field name, thereby allowing selection of some or all of the data in the data table as the set to be analyzed.

[0022] In some implementations, a data table may include at least two partition tables. Determining a set to be analyzed from the data table can be achieved by selecting the partition table with the largest data volume from the at least two partition tables. Partitioning is a simple indexing mechanism for data tables. By storing data in a partition, it achieves the goal of quickly accessing data within a certain range, thereby reducing access time and scanning costs. A partition table is typically an HDFS (Hadoop Distributed File System) folder. In one application scenario, the partition table is partitioned by time, specifically by day or hour. Partition tables within the same data table can have the same or different field names. Since the partition table with the largest data volume often contains a large number of deduplicated field names and values, it is the most complete and representative. Therefore, the reference fields obtained from analyzing the partition table with the largest data volume have good generality and can be used for compression processing of the entire data table. Furthermore, it eliminates the need to analyze the entire data table, saving computing resources and reducing runtime.

[0023] Step S103: Perform deduplication statistics on the field values ​​under each field name in the set to be analyzed, and obtain the number of deduplicated field values ​​under each field name.

[0024] In some implementations, the following SQL statement can be used to perform deduplication and statistics on each field name: SELECT COUNT(1), 'field_name1' FROM ( SELECT field_name1 FROM table_name WHERE partition_condition GROUP BY field_name1 ) UNION ALL SELECT COUNT(1), 'field_name2' FROM ( SELECT field_name2 FROM table_name WHERE partition_condition GROUP BY field_name2 ) UNION ALL ………… SELECT COUNT(1), 'field_name_n' FROM ( SELECT field_name_n FROM table_name WHERE partition_condition GROUP BY field_name_n ) Taking the first statement as an example, (select field_name1 from table_name where partition_condition group by field_name1), the partition condition is, for example, dayno=20221011. This means that the statement selects the data corresponding to field_name1 from a partition table in the database, groups it according to field_name1, and then uses the count(1) function to count the results. The purpose of the UNION ALL command is also to merge the results of two SQL statements. Multiple UNION ALL statements can merge the results of n queries from field_name1 to field_namen (where n is a positive integer). Field_name1 to field_namen are the n field names in the set to be analyzed.

[0025] It should be noted that the SQL statements used for deduplication and statistics above are just examples. In actual use cases, they can be set or modified as needed, or other corresponding deduplication and statistics statements can be used depending on the database type. These will not be elaborated here.

[0026] In one example, such as Figure 3 As shown, this is the number of duplicate field values ​​under each field name in the set to be analyzed, sorted in descending order of the number of values.

[0027] Step S104: Using the number of duplicates after deduplication, determine at least one reference field name from multiple field names.

[0028] Step S105: Based on at least one reference field name, divide the data corresponding to the set to be analyzed into at least two sets to be compressed, wherein the field values ​​under at least one reference field name in each set to be compressed are the same.

[0029] In some implementations, a field name with a greater than 1 unique count after deduplication can be selected as the first reference field name, so that the data corresponding to the set to be analyzed can be divided into at least two sets to be compressed based on the first reference field name, and the field values ​​under the first reference field name in each set to be compressed are the same.

[0030] Step S106: Perform compression processing on the sets to be compressed respectively.

[0031] Each set of data to be compressed corresponds to one processing node. Therefore, classifying the set to be analyzed according to the first reference field name allows data with the same field value under the first reference field name to be compressed in the same data node, thus achieving a higher compression ratio.

[0032] In some implementations, the data in the database is distributed, so the data of the collection to be compressed is stored in at least one storage node. When compressing the data of the collection to be compressed, the computing engine needs to read the data of the collection to be compressed from at least one storage node and then send it to the corresponding processing node for compression processing. Examples of computing engines include Spark, Flink, MapReduce, Tez, and Oozie. Processing nodes can be computer devices or devices with data processing capabilities.

[0033] In this embodiment, a database table is obtained, which includes multiple field names and the field values ​​of each data entry under each field name. Then, a set to be analyzed is determined from the data table. Next, the field values ​​under each field name in the set to be analyzed are deduplicated to obtain the number of duplicate field values ​​under each field name. Then, using the number of duplicate values, at least one reference field name is determined from the multiple field names. Then, based on at least one reference field name, the data corresponding to the set to be analyzed is divided into at least two sets to be compressed. Finally, each set to be compressed is compressed. In this case, if the field values ​​under at least one reference field name are the same in each set to be compressed, the higher the data regularity in the set to be compressed, the higher the data compression rate. This can reduce the amount of data stored in the database and reduce storage costs.

[0034] Please see Figures 4 to 5 , Figure 4 yes Figure 1 A flowchart illustrating an embodiment of step S104. Figure 5 yes Figure 4 A flowchart illustrating an implementation method for step S1042.

[0035] Step S104 above involves determining at least one reference field name from multiple field names based on the number of duplicates after deduplication. The at least one reference field name may include a first reference field name and a second reference field name.

[0036] In this embodiment, the first reference field name can be determined from multiple field names by using the number of duplicates. Specifically, step S104 may include steps S1041 to S1042: Step S1041: Obtain the amount of data in the set to be analyzed.

[0037] In this embodiment, the unit of the data volume of the set to be analyzed is GB (Gigabyte). In other optional embodiments, the unit of the data volume of the set to be analyzed can also be MB (Megabyte), TB (Trillionbyte), etc.

[0038] Step S1042: Determine the first reference field name based on the amount of data in the set to be analyzed and the number of duplicates corresponding to each field name in the set to be analyzed.

[0039] In some implementations, step S1042 may include sub-steps S10421-S10422: Step S10421: Determine the ratio of the amount of data in the set to be analyzed to the number of duplicates corresponding to each field name in the set to be analyzed.

[0040] Step S10422: Select the field name whose ratio is closest to the preset ratio reference value as the first reference field name.

[0041] The ratio reference value is determined based on the processing capacity of the processing node. Assuming the processing node has a processing capacity of 1GB of data, if the processing volume exceeds 1GB, it will cause the processing node to lag, thereby affecting the data compression time and making the task unable to complete for a long time. Therefore, the ratio reference value can be set to 1GB.

[0042] In one specific implementation, the ratio reference value is 1, and the data size of the dataset to be analyzed is 10G, such as... Figure 3 As shown, the number of duplicates after deduplication for each field is 20, 10, 8..., 1, 1. When the number of duplicates after deduplication is 10, the ratio of the data volume of the set to be analyzed to the number of duplicates is closest to 1. Therefore, the field name Id corresponding to the number of duplicates after deduplication is taken as the first reference field name. In other implementations, the ratio reference value can also be 1.5, 2, 5, etc., and can be set or modified according to the actual situation.

[0043] The above method determines the first reference field name by combining the data volume of the set to be analyzed and the number of deduplicated values ​​corresponding to each field name in the set. On one hand, based on the number of deduplicated values ​​corresponding to a reference field name, the data in the set to be analyzed can be divided into at least a corresponding number of sets to be compressed. On the other hand, considering the data volume of the set to be analyzed, the data volume of each set to be compressed is taken into account, avoiding the data volume of the set to be compressed exceeding the processing capacity of the processing node, which could lead to the task being unable to complete for a long time. By determining the ratio of the data volume of the set to be analyzed to the number of deduplicated values ​​corresponding to each field name in the set, and then using the field name whose ratio is closest to the preset reference ratio as the first reference field name, the data to be analyzed can be divided according to the processing capacity of the processing node, ensuring that the resulting sets to be compressed are processed normally by the processing node.

[0044] Please see Figure 6 , Figure 6 yes Figure 1 A flowchart illustrating another embodiment of step S104.

[0045] In this embodiment, the second reference field name can be determined from multiple field names by using the number of duplicates. Specifically, step S104 may include steps S1043 to S1044: Step S1043: Sort the field names according to the number of duplicates after deduplication.

[0046] Step S1044: Select the first preset number of field names that appear first in the sorting as the second reference field names.

[0047] In some implementations, such as Figure 3 As shown, field names can be sorted in descending order based on the number of unique occurrences after deduplication. Then, the first preset number of field names that appear at the top of the sorted list are selected as the second reference field names. The second reference field names differ from the first reference field names. Optionally, the first preset number can be set or modified according to actual needs. In one example, the first preset number is 2, meaning that the two orders of magnitude of fields with the highest number of unique occurrences after deduplication (such as event_id and address) are selected as the second reference field names. The first preset number can be an empirical value. Figure 2 and Figure 3 As shown, the first two order of magnitude fields are event_id and id, but id is the first reference field name, so address can be selected as the second reference field name.

[0048] There is no fixed order between steps S1043~S1044 and steps S1041~S1042.

[0049] Please see Figures 7 to 8 , Figure 7 yes Figure 1 A flowchart illustrating an embodiment of step S105. Figure 8 yes Figure 7 A flowchart illustrating an implementation method for step S1052.

[0050] In this embodiment, step S105, which divides the data corresponding to the set to be analyzed into at least two sets to be compressed based on at least one reference field name, may include steps S1051 to S1053: Step S1051: Assign data with the same field value under the first reference field name to the same group.

[0051] Step S1052: Divide at least a portion of the data under the same group into the same set to be compressed.

[0052] In one example, the dataset to be analyzed is 2GB, and the first reference field is gender. Data with a "male" value is grouped into the first group, and data with a "female" value is grouped into the second group. Theoretically, both groups should be 1GB, which should be sufficient for the processing node's capacity. However, in reality, data skew may exist. For example, the first group (male) might have 1.9GB of data, while the second group (female) might only have 0.1GB. If the first group is then grouped into a single compressed dataset, the compressed dataset will exceed the processing node's capacity. Therefore, when data skew exists, the larger group needs to be further divided into at least two compressed datasets.

[0053] In some implementations, the statement `distribute by + first reference field name` can be used to assign data with the same field value under the first reference field name to the same group.

[0054] In this embodiment, step S1052 may include sub-steps S10521~S10522: Step S10521: Determine whether there is data skew in the data after each grouping.

[0055] In some implementations, the following SQL statement can be used to determine this: select count(1), first reference field name from database table Where partition conditions group by the first reference field name.

[0056] The SQL statement above can be used to determine the amount of data corresponding to each field value under the first reference field name, that is, the amount of data in each group. Based on the amount of data in each group, it can be determined whether there is data skew.

[0057] Step S10522: If data skew exists, the data in the group with data skew is divided into a second preset number of sets to be compressed, and then assigned to a second preset number of processing nodes for compression processing.

[0058] The second preset quantity can be determined based on the amount of data within the skewed group. In one example, the second preset quantity can be the ratio of the amount of data within the skewed group to a preset data amount, rounded up. The preset data amount can be determined based on the processing capacity of a single processing node. Specifically, for example, if a single processing node has a processing capacity of 50 million data entries, and the amount of data within the skewed group is 120 million data entries, then 120 million / 0.5 million = 240 million, which, when rounded up, yields a second preset quantity of 3. Therefore, the data within the skewed group can be divided into three smaller sets to be compressed, and each set can be assigned to one of the three processing nodes for compression.

[0059] In another example, the second preset quantity can be twice the ratio of the amount of data in the group with data skew to the preset data amount, rounded up. For example, the preset data amount can be 100 million, and the processing capacity of a single processing node is 50 million data entries. If the amount of data in the group with data skew is 120 million data entries, then 2 * ([1.2 / 1]) = 4, where [1.2 / 1] represents the value of 1.2 / 1 rounded up, thus obtaining the second preset quantity as 4.

[0060] In some specific implementations, the statement `distribute by + first reference field name, cast(rand() * 2n as int)` can be used to assign data with the same field value under the first reference field name to the same group. In addition, by adding the condition `cast(rand() * 2n as int)` after `distribute by`, a random sequence number can be obtained. The total number of random numbers is fixed at 2n. This can divide the data in the group with data skew into smaller sets to be compressed to meet the processing capacity of the processing node.

[0061] In some implementations, if the second preset quantity is greater than a preset quantity threshold, the data within the group exhibiting data skew is divided into a second preset quantity of sets to be compressed, ensuring that the amount of data in the sets to be compressed does not exceed the processing capacity of the processing node, for example, no more than 50 million rows of data, to avoid data skew. The preset quantity threshold can be set or modified according to actual conditions. Each company may set different preset quantity thresholds for different scenarios. In one example, Spark SQL's default concurrency is 400, meaning the preset quantity threshold can be 400. However, when the second preset quantity is greater than 400, to ensure that the task's running speed is not affected after rewriting the SQL, the amount of concurrent resources needs to be increased.

[0062] Step S1053: Sort the data in each group according to the second reference field.

[0063] In some implementations, the `sort by + second reference field name` statement can be used to sort the data within each group, so that data with the same field value under the second reference field name are arranged adjacently, thereby organizing the data within the group, improving the regularity of the data within the group, and thus improving the data compression rate.

[0064] Please see Figures 9 to 12 , Figure 9 This is a flowchart illustrating another embodiment of the data compression method for the database in this application. Figure 10 yes Figure 2 A schematic diagram of the database table analysis interface. Figure 11 yes Figure 2 A schematic diagram of the partition analysis interface. Figure 12 yes Figure 2 A schematic diagram of the comparison and analysis interface.

[0065] Step S201: Obtain the data table from the database, which includes multiple field names and the field values ​​for each data entry under each field name.

[0066] For an explanation of step S201, please refer to the foregoing embodiments; it will not be repeated here.

[0067] Step S202: Determine whether the number of field names in the data table is less than or equal to the preset number threshold.

[0068] In response to a number exceeding the threshold, step S203 is executed.

[0069] In response to a number of items being less than or equal to the number threshold, step S204 is executed.

[0070] Step S203: Generate a prompt message and / or do not perform the step of determining a set to be analyzed from the data table.

[0071] The number threshold can be set or modified according to actual conditions. In this embodiment, the number threshold can be 55. When the number of field names in the data table is greater than 55, step S204 can be skipped, and a prompt message can be generated. Figure 10 As shown, the prompt message might be something like, "Too many database table fields; further compression analysis is not possible." The format and content of the prompt message can be customized based on the specific situation; there are no restrictions here.

[0072] Unlike the previous embodiments, in this embodiment, database table analysis can be performed before step S204. Database table analysis can include steps S202-S203. When other factors remain constant, the more field names a database has, the worse the data compression effect and the more computational resources are consumed during compression. Therefore, when the number of field names in the database is large, step S204 is not executed; only when the number of field names in the database is small is step S204 executed to further compress the data in the database. In addition to analyzing the number of field names in the data table, database table analysis can also analyze the storage method of the data in the data table and whether the data table has a configured lifecycle, as described in detail below.

[0073] In some embodiments, before step S204, the method may further include: determining whether the data in the data table is stored in columnar storage; if the data in the data table is stored in columnar storage, then step S204 is executed; if the data in the data table is not stored in columnar storage, then a prompt message is generated, and step S204 is not executed. Columnar storage may include, but is not limited to, Parquet and ORC storage formats. In one example, when the data in the data table is not stored in columnar storage (e.g., TEXT format, i.e., text storage format), a prompt is given stating "Columnar storage such as ORC format is recommended," and step S204 is not executed, i.e., subsequent compression analysis of the data table is not performed. Columnar storage format has a higher compression ratio than text storage format.

[0074] like Figure 10 As shown, in some embodiments, before step S204, the process may further include: determining whether the data table has a configured lifecycle; if the database has a configured lifecycle, then step S204 is executed directly; if the data table does not have a configured lifecycle, then a prompt message is generated and step S204 is executed, such as "Please configure a lifecycle for the data table to delete unused data." The lifecycle is calculated from the last time the data was modified. If the data remains unchanged after a preset number of days, that data is automatically reclaimed, thereby releasing storage space. Furthermore, since the lifecycle can only be set at the table level and not at the partition level, the lifecycle is not a necessary condition for compressing and analyzing the data table. This ensures that when compressing and analyzing only the data in a partition table, the data volume is small, so whether or not a data table has a configured lifecycle does not affect the compression and analysis of the partition table data. When compressing and analyzing the entire data table, if the data table has a configured lifecycle, long-unused data in the data table can be deleted, thereby reducing the amount of data compression and reducing computational and storage costs.

[0075] Optionally, the specific value of the lifecycle can be set or modified according to the actual situation, such as Figure 10 As shown, the lifecycle can be 30 days, indicating that data that has not been modified for 30 days has been recycled.

[0076] It should be noted that any of the above embodiments can be combined. For example, it can be determined whether the number of field names in the data table is less than or equal to a preset number threshold, and whether the data in the data table is stored in columnar storage. If the number of field names in the data table is less than or equal to the preset number threshold, and the data in the data table is stored in columnar storage, then step S204 is executed. Alternatively, it can be determined whether the data table has been configured with a lifecycle. If the number of field names in the data table is less than or equal to the preset number threshold, and the data in the data table is stored in columnar storage, and the data table has been configured with a lifecycle, then step S204 is executed.

[0077] like Figure 10 As shown, in one embodiment, the analysis results and analysis report corresponding to the database table analysis can also be displayed. The analysis results include specific data such as analysis items, content, and results. The analysis report includes the generated prompt information to provide instructions to the user.

[0078] In other embodiments, in addition to table analysis, partition analysis can be performed before step S204. Specifically, the average partition size in the data table can be determined based on the total data volume and the total number of partitions. The average file size in the data table can also be determined based on the total data volume and the total number of files, where data in the data table can be stored as individual files. Then, if the average partition size is less than or equal to the average partition threshold, a prompt message is generated, such as "Too many small partitions exist; it is recommended to reduce partition conditions or change partition conditions." If the average file size is less than or equal to the average file threshold, a prompt message is generated, such as "Too many small files exist; merging small files can effectively reduce storage." The prompt message can be displayed on a screen to alert the user. Additionally, the specific values ​​of the total data volume, total number of partitions, total number of files, average partition size, and average file size can also be displayed.

[0079] Optionally, the average partition threshold and average file threshold can be set or modified according to actual needs. In one example, the average partition threshold and average file threshold are 5GB and 1GB, respectively.

[0080] Step S204: Identify a set to be analyzed from the data table.

[0081] Step S205: Perform deduplication statistics on the field values ​​under each field name in the set to be analyzed, and obtain the number of deduplicated field values ​​under each field name.

[0082] Step S206: Using the number of duplicates after deduplication, determine at least one reference field name from multiple field names.

[0083] Step S207: Based on at least one reference field name, divide the data corresponding to the set to be analyzed into at least two sets to be compressed, wherein the field values ​​under at least one reference field name in each set to be compressed are the same.

[0084] Step S208: Perform compression processing on the sets to be compressed respectively.

[0085] For a description of steps S204 to S208, please refer to the above embodiments; they will not be repeated here.

[0086] In some implementations, steps S207 may be followed by steps S208 to S211: Step S209: Insert the target file obtained after compressing the set to be compressed into the test table.

[0087] Before proceeding, you can check if the test table exists. If it does, there's no need to create it; otherwise, create a new test table. This can be achieved, for example, using the following SQL statement: SELECT * FROM test_table_name LIMIT 1; -- Checks if the test table exists; creates a new table if it doesn't. `create table test_table_name like original_table` -- Create test table `desc formatted test_table_name;` -- View the HDFS where the test table is located.

[0088] In one example, the target file obtained after compressing the collection to be compressed can be inserted into the test table using the following SQL statement: `insert overwrite table test_database_table_name partition (partition_field)` select * from original database table name Where partition conditions Distribute by the fields obtained above, cast(rand() * 2n as int) The fields obtained by sorting by the above.

[0089] In some implementations, dynamic partitioning can be enabled before creating the test table, allowing the name of the partition to be inferred based on the query parameters. The corresponding SQL statement is as follows: set livy.session.conf.spark.hadoop.hive.exec.dynamic.partition.mode =nonstrict; Step S210: Obtain the compression time for the set to be compressed.

[0090] When compressing a collection, the compression time from start to finish is recorded.

[0091] Step S211: Determine the compression ratio of the test table relative to the set to be analyzed.

[0092] Specifically, the data volume corresponding to the set to be analyzed and the data volume corresponding to the test table can be obtained. Then, the ratio of the data volume corresponding to the test table to the data volume corresponding to the set to be analyzed can be calculated as the compression ratio. The file size of the database tables can be viewed using the `hdfs` command. Specifically, `hdfs dfs -du -h + test table HDFS path` will give the total file size under the test table (i.e., the data volume corresponding to the test table). Similarly, the data volume corresponding to the set to be analyzed can also be obtained.

[0093] When the set to be analyzed is a partitioned table, the compression ratio = the amount of data in the test table / the amount of data in the partitioned table.

[0094] There is no fixed order between steps S211 and S210. Step S211 can be executed before step S210, and step S210 can be executed before step S211. The two steps can also be executed simultaneously.

[0095] In some implementations, the operating cost of the compression process can also be determined. Specifically, this operating cost can be calculated based on the number of core hours and memory used in the application ID test table. The specific calculation method is not limited here, but can be found in relevant technologies. Generally, the higher the number of core hours and memory, the higher the operating cost.

[0096] Step S212: Display compression time and compression ratio.

[0097] like Figure 12As shown, in some implementations, the message "Start creating test table..." can be displayed first. Once the test table is successfully created, the message "Start organizing data and inserting it into the test table... Typically, the running time takes 1 hour" can be displayed. Then, the running time (i.e., compression time) "40 minutes elapsed" can be displayed in real time. When the operation is successful, "Running successful" can be displayed, along with analysis results such as the original partition table size, the optimized partition table size (i.e., test table size), the compression ratio, and the compression time. Thus, users can monitor the data compression progress in real time through the display interface and determine the compression effect based on the final displayed analysis results. A higher compression ratio and shorter running time indicate better compression. In this embodiment, the compression effect considers not only the compression ratio but also the running time. A longer running time consumes more computing resources, resulting in higher operating costs. If the compression ratio can be increased within a reasonable operating cost range, the current first and second reference fields can be used to adjust the data in the entire database. If the operating cost of compression is greater than the storage cost before compression, then compression is unnecessary.

[0098] In some implementations, when the displayed compression time and compression ratio do not meet the compression effect, at least one of the first reference field and the second reference field can be re-determined, and then step S207 can be executed, which is the step of dividing the data corresponding to the set to be analyzed into at least two sets to be compressed based on at least one reference field name. Re-determining at least one of the first reference field and the second reference field can be done by selecting a field name adjacent to the original first reference field as the first reference field and selecting a field name adjacent to the original second reference field as the second reference field. This is only an example; in some implementations, a field name can also be randomly selected from among the field names as the first or second reference field.

[0099] In some implementations, after step S212, at least one reference field name input by the user can be obtained, and based on the at least one reference field name input by the user, the data corresponding to the set to be analyzed is divided into at least two sets to be compressed. The method of user input is not limited, such as voice, touch screen, keyboard input, etc.

[0100] Please see Figure 13 , Figure 13 This is a schematic block diagram of an embodiment of the data compression device for the database in this application. The database is used to store multiple data entries.

[0101] The database compression device 100 includes an acquisition module 110, a first determination module 120, a statistics module 130, a second determination module 140, a partitioning module 150, and a compression module 160. The acquisition module 110 acquires data tables from the database, where each data table includes multiple field names and the field values ​​for each data entry under each field name. The first determination module 120 determines a set to be analyzed from the data table. The statistics module 130 performs deduplication statistics on the field values ​​under each field name in the set to be analyzed, obtaining the number of deduplicated field values ​​under each field name. The second determination module 140 uses the deduplicated field values ​​to determine at least one reference field name from the multiple field names. The partitioning module 150, based on at least one reference field name, partitions the data corresponding to the set to be analyzed into at least two sets to be compressed, wherein the field values ​​under at least one reference field name are identical in each set to be compressed. The compression module 160 performs compression processing on each set to be compressed.

[0102] In some implementations, the data table includes at least two partition tables, and determining a set to be analyzed from the data table includes selecting the partition table with the largest data volume from the at least two partition tables as the set to be analyzed.

[0103] In some implementations, at least one reference field name is determined from multiple field names by using the number of deduplicated field names, including: obtaining the data volume of the set to be analyzed; and determining the first reference field name based on the data volume of the set to be analyzed and the number of deduplicated field names corresponding to each field name in the set to be analyzed.

[0104] In some implementations, determining a first reference field name based on the data volume of the set to be analyzed and the number of duplicates corresponding to each field name in the set to be analyzed includes: determining the ratio of the data volume of the set to be analyzed to the number of duplicates corresponding to each field name in the set to be analyzed; selecting the field name whose ratio is closest to a preset ratio reference value as the first reference field name; and dividing the data corresponding to the set to be analyzed into at least two sets to be compressed based on at least one reference field name, including: assigning data with the same field value under the first reference field name to the same group; and assigning at least a portion of the data under the same group to the same set to be compressed.

[0105] In some implementations, determining at least one reference field name from multiple field names using the number of deduplicated entries includes: sorting the field names according to the number of deduplicated entries; selecting a first preset number of field names that appear at the top of the sort as second reference field names; and further including: sorting the data in each group according to the second reference field name before dividing at least a portion of the data in the same group into the same set to be compressed.

[0106] In some implementations, the data to be compressed is compressed separately, including sending the data of the data to be compressed to the corresponding processing node for compression, wherein the ratio reference value is determined based on the processing capability of the processing node.

[0107] In some implementations, dividing at least a portion of the data under the same group into the same set to be compressed includes: determining whether there is data skew in the data after each group; if there is data skew, dividing the data in the group with data skew into a second preset number of sets to be compressed, and assigning them to a second preset number of processing nodes for compression processing.

[0108] In some implementations, before determining a set to be analyzed from a data table, the method further includes: determining whether the number of field names in the data table is less than or equal to a preset number threshold; in response to the number being less than or equal to the number threshold, performing the step of determining a set to be analyzed from the data table; and in response to the number being greater than the number threshold, generating a prompt message and / or not performing the step of determining a set to be analyzed from the data table.

[0109] In some implementations, after compressing the sets to be compressed, the method further includes: inserting the target file obtained after compressing the sets to be compressed into a test table; obtaining the compression time of the sets to be compressed; determining the compression ratio of the test table relative to the sets to be analyzed; and displaying the compression time and compression ratio.

[0110] In this embodiment, please refer to the corresponding locations in the method embodiment for explanations of the execution steps of each of the above modules, which will not be repeated here.

[0111] Please see Figure 14 , Figure 14 This is a schematic block diagram of the structure of an embodiment of the electronic device of this application.

[0112] The electronic device 200 includes a memory 210 and a processor 220 coupled to each other. The memory 210 is used to store program data, and the processor 220 is used to execute the program data to implement the steps in any of the above method embodiments.

[0113] Electronic device 200 may include, but is not limited to: personal computers (e.g., desktop computers, laptop computers, tablet computers, handheld computers, etc.), mobile phones, servers, wearable devices, as well as augmented reality (AR) and virtual reality (VR) devices, televisions, etc., without limitation.

[0114] Specifically, processor 220 controls itself and memory 210 to implement the steps in any of the above method embodiments. Processor 220 may also be referred to as a Central Processing Unit (CPU). Processor 220 may be an integrated circuit chip with signal processing capabilities. Processor 220 may also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. A general-purpose processor may be a microprocessor or any conventional processor. Furthermore, processor 220 may be implemented by multiple integrated circuit chips.

[0115] Please see Figure 15 , Figure 15 This is a schematic block diagram of an embodiment of a computer-readable storage medium of this application.

[0116] The computer-readable storage medium 300 stores program data 310, which, when executed by a processor, is used to implement the steps in any of the above method embodiments.

[0117] The computer-readable storage medium 300 can be a medium capable of storing computer programs, such as a USB flash drive, portable hard drive, read-only memory (ROM), random access memory (RAM), magnetic disk, or optical disk. It can also be a server storing the computer program, which can send the stored computer program to other devices for execution or run the stored computer program itself.

[0118] In this application, the term "and / or" is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent three cases: A existing alone, A and B existing simultaneously, and B existing alone. Furthermore, the term "at least one" in this application means any combination of at least two of any one or more of a plurality of elements. For example, including at least one of A, B, and C can mean including any one or more elements selected from the set consisting of A, B, and C.

[0119] In the several embodiments provided in this application, it should be understood that the disclosed methods and apparatus can be implemented in other ways. For example, the apparatus implementations described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0120] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment, depending on actual needs.

[0121] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0122] If the integrated unit is implemented as 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 this application, in essence, 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. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods of various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0123] The above description is merely an embodiment of this application and does not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A data compression method for a database, wherein the database is used to store multiple data entries, characterized in that, include: Obtain the data table of the database, wherein the data table includes multiple field names and the field values ​​of each data entry under each field name; A set to be analyzed is determined from the data table; Perform deduplication and counting on the field values ​​under each field name in the set to be analyzed to obtain the number of deduplicated field values ​​under each field name; Using the number of duplicates after deduplication, at least one reference field name is determined from the plurality of field names; this includes: obtaining the data volume of the set to be analyzed; Determining a first reference field name based on the data volume of the set to be analyzed and the number of duplicates corresponding to each field name in the set to be analyzed includes: determining the ratio of the data volume of the set to be analyzed to the number of duplicates corresponding to each field name in the set to be analyzed; and selecting the field name whose ratio is closest to a preset ratio reference value as the first reference field name. Based on at least one of the reference field names, the data corresponding to the set to be analyzed is divided into at least two sets to be compressed, including: assigning data with the same field value under the first reference field name to the same group; and assigning at least a portion of the data under the same group to the same set to be compressed; wherein, in each set to be compressed, at least one of the reference field names has the same field value. The sets to be compressed are compressed separately.

2. The method according to claim 1, characterized in that, The data table includes at least two partition tables, and determining a set to be analyzed from the data table includes: Select the partition table with the largest data volume from the at least two partition tables as the set to be analyzed.

3. The method according to claim 1, characterized in that, The step of determining at least one reference field name from the plurality of field names using the number of deduplicated field names includes: Sort the field names according to the number of duplicates after deduplication; Select the first preset number of field names that appear first in the sorting list as the second reference field names; Before dividing at least a portion of the data under the same group into the same set to be compressed, the method further includes: Sort the data within each group according to the second reference field.

4. The method according to claim 3, characterized in that, The compression process for the sets to be compressed includes: The data of the set to be compressed is sent to the corresponding processing node for compression, wherein the ratio reference value is determined based on the processing capability of the processing node.

5. The method according to claim 4, characterized in that, The step of dividing at least a portion of the data under the same group into the same set to be compressed includes: Determine whether there is data skew in the data after each grouping; If data skew exists, the data within the group with data skew will be divided into a second preset number of sets to be compressed, and then assigned to the second preset number of processing nodes for compression processing.

6. The method according to claim 1, characterized in that, Before determining a set to be analyzed from the data table, the process further includes: Determine whether the number of field names in the data table is less than or equal to a preset threshold number; In response to a number being less than or equal to the threshold, the step of determining a set to be analyzed from the data table is performed. In response to a number exceeding the threshold, a prompt message is generated and / or the step of determining a set to be analyzed from the data table is not performed.

7. The method according to claim 1, characterized in that, After compressing the sets to be compressed, the process further includes: The target file obtained after compressing the set to be compressed is inserted into the test table; Obtain the compression time for compressing the set to be compressed; and Determine the compression ratio of the test table relative to the set to be analyzed; The compression time and the compression ratio are displayed.

8. A data compression device for a database, wherein the database is used to store multiple data entries, characterized in that, include: The acquisition module is used to acquire the data table of the database, wherein the data table includes multiple field names and the field values ​​of each data entry under each field name; The first determining module is used to determine a set to be analyzed from the data table; The statistics module is used to perform deduplication statistics on the field values ​​under each field name in the set to be analyzed, and to obtain the number of deduplicated field values ​​under each field name; The second determining module is used to determine at least one reference field name from the plurality of field names using the number of duplicates after deduplication; including: obtaining the data volume of the set to be analyzed; determining a first reference field name based on the data volume of the set to be analyzed and the number of duplicates corresponding to each of the field names in the set to be analyzed, including: determining the ratio of the data volume of the set to be analyzed to the number of duplicates corresponding to each of the field names in the set to be analyzed; and selecting the field name whose ratio is closest to a preset ratio reference value as the first reference field name; The partitioning module is used to partition the data corresponding to the set to be analyzed into at least two sets to be compressed based on at least one of the reference field names, including: assigning data with the same field value under the first reference field name to the same group; and partitioning at least a portion of the data under the same group to the same set to be compressed; wherein, in each set to be compressed, at least one reference field name has the same field value. The compression module is used to compress the collection to be compressed.

9. An electronic device, characterized in that, The electronic device includes a memory and a processor coupled to each other, the memory being used to store program data and the processor being used to execute the program data to implement the method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores program data, which, when executed by a processor, is used to implement the method as described in any one of claims 1-7.

Images