Data distribution control method, device, and computer readable storage medium

Abstract

Embodiments of the present description provide a data distribution control method, a device, and a computer readable storage medium. The method may comprise: on the basis of historical query statements submitted to a data management system, generating predicate application statistical information for a plurality of data tables in the data management system; on the basis of the predicate application statistical information, determining a hot data table among the plurality of data tables and at least one hot data column group in the hot data table; on the basis of a preset algorithm, generating a data distribution optimization rule for the hot data table, wherein the data distribution optimization rule comprises an optimization rule for the at least one hot data column group; and on the basis of the data distribution optimization rule, redistributing the data in the hot data table.

Description

Data distribution control method, device and computer-readable storage medium

[0001] Cross-references

[0002] This application claims priority to the Chinese patent application filed with the China Patent Office on March 6, 2024, with application number 202410256287.8 and application name “Data distribution control method, device and computer-readable storage medium”. The entire contents of the application are incorporated by reference into this application. Technical Field

[0003] The present application relates to the field of data management, and in particular to a data distribution control method, device, and computer-readable storage medium. Background Art

[0004] Data skipping is a technique for performing Structured Query Language (SQL) analysis on structured data. This technique uses column statistics (such as minimum and maximum values) in data files as column indexes. If the query data range doesn't overlap with the column index value range corresponding to a data file in a table, the query engine will skip that data file during scanning, thereby reducing the number of data files that need to be scanned during the query.

[0005] However, related technologies use the same data distribution rules for all data tables in the same data management system, resulting in some data tables having column statistics in each data file that are close to or equal to the column statistics corresponding to the entire data table. This creates a dilemma where no data file can be skipped when querying the data table. Data skipping is ineffective for queries on the data table, and the data skipping effect is poor, affecting query efficiency. Summary of the Invention

[0006] Embodiments of the present application provide a data distribution control method, device, and computer-readable storage medium.

[0007] The embodiment of the present application is implemented as follows:

[0008] In a first aspect, a data distribution control method is provided, comprising: generating predicate application statistical information for multiple data tables in a data management system based on historical query statements submitted to the data management system; determining a hot data table among the multiple data tables and at least one hot column group in the hot data table based on the predicate application statistical information; generating data distribution optimization rules for the hot data table based on a preset algorithm, wherein the data distribution optimization rules include optimization rules for the at least one hot column group; and rearranging the data distribution of the hot data table based on the data distribution optimization rules.

[0009] In a second aspect, an electronic device is provided, comprising: a processor; and a memory for storing instructions executable by the processor; wherein the processor is configured to execute the instructions to implement the method described in the first aspect.

[0010] According to a third aspect, a computer-readable storage medium is provided. When the instructions in the storage medium are executed by a processor of an electronic device, the electronic device is enabled to execute the method according to the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In order to more clearly illustrate the technical solutions in the embodiments of this specification or related technologies, the following briefly introduces the drawings required for use in the embodiments or related technical descriptions. Obviously, the drawings described below are only some embodiments recorded in this specification. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0012] FIG1 is a schematic diagram of an application environment of a data distribution control method provided in an embodiment of the present application.

[0013] FIG2 is a flow chart of a data distribution control method provided in an embodiment of the present application.

[0014] FIG3 is a schematic diagram of an SQL abstract syntax tree provided in an embodiment of the present application.

[0015] FIG4 is a schematic diagram showing the principle of determining at least one hotspot column group in a data distribution control method provided by an embodiment of the present application.

[0016] FIG5 is a flow chart of a data distribution control method provided in another embodiment of the present application.

[0017] FIG6 is a schematic diagram of a data writing process using a data distribution control method provided in an embodiment of the present application.

[0018] FIG7 is a schematic structural diagram of a data distribution control device provided in an embodiment of the present application.

[0019] FIG8 is a schematic structural diagram of a data distribution control device provided in another embodiment of the present application.

[0020] FIG9 is a schematic structural diagram of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0021] In order to help those skilled in the art better understand the technical solutions in the embodiments of this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below in conjunction with the drawings in one or more embodiments of this specification. Obviously, the described embodiments are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments in this specification, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of this document.

[0022] The terms "first," "second," and the like in this specification and claims are used to distinguish similar objects and are not intended to describe a particular order or precedence. It should be understood that such terms are interchangeable where appropriate, so that the embodiments of this specification can be implemented in orders other than those illustrated or described herein. Furthermore, the term "and / or" in this specification and claims refers to at least one of the connected objects, and the character " / " generally indicates that the connected objects are in an "or" relationship.

[0023] In an increasing number of scenarios, users expect to quickly analyze data in an interactive manner. Query methods have evolved from traditional batch queries to exploratory, needle-in-a-haystack queries (such as point queries). The size of the datasets being queried ranges from a few GB to hundreds of PB. Linearly scanning these massive datasets is prohibitively expensive in terms of time and computational cost. In practical applications, multidimensional analysis typically involves filtering conditions, and the analysis results may only come from a very small portion of the dataset. In theory, it is possible to skip irrelevant data during data scanning, scanning only the minimal required data, thereby improving query and analysis efficiency.

[0024] Although column statistics such as minimum and maximum values ​​have the ability to skip data, this capability is highly dependent on the data distribution across data files. When the data in a data table is evenly distributed across all data files, the column statistics in each data file may be close to or equal to the column statistics corresponding to the entire data table. This creates a dilemma where no data file can be skipped when querying the data table. Data skipping is ineffective for queries on this data table, resulting in a large query volume for the data table, affecting query efficiency.

[0025] In the related art, through aggregation algorithms such as Z-Order and Hibert Curve Order, data can be aggregated and stored according to multiple fields (columns), so that the attributes such as the minimum-maximum value (MIN-MAX) of each data file are distributed in an orderly manner, relatively non-intersecting, and the skipping effect is improved. However, since the columns involved in the aggregation and their relative order determine the tightness of the data aggregation, the user must carefully select how to determine the columns that need to participate in the aggregation. Poor adjustments will lead to performance degradation, which is one of the pain points that have not yet been solved in the related art. A data distribution control method proposed in an embodiment of the present application attempts to solve this pain point by extracting predicate features from historical query statements, finding the hot data columns involved in sorting or aggregation, and the order of hot data columns.

[0026] Furthermore, in related technologies, any new data ingested after running data aggregation commands like Z-Order is not automatically aggregated, requiring the user to re-run the data aggregation command to merge the old and new data before re-aggregating. However, running Z-Order multiple times consumes significant resources and significantly impacts existing businesses. Therefore, optimizing data layout to minimize the impact on existing businesses and enable the computing engine to efficiently skip all logically unnecessary data is a key factor in achieving an interactive analysis experience and another pain point that urgently needs to be addressed.

[0027] To address at least one of the aforementioned pain points, improve the effectiveness of data skipping, and thereby improve query efficiency, this application proposes a data distribution control method, device, and computer-readable storage medium. The method can be executed by an electronic device or software installed in the electronic device.

[0028] A data distribution control method provided in an embodiment of the present application can be applied to an application environment as shown in Figure 1. As shown in Figure 1, the application environment can be logically divided into a storage layer 13 and a computing layer 11. Among them, the storage layer 13 provides data storage capabilities, and the bottom layer uses formats such as Parquet to store data. The storage layer 13 can be any of cloud storage, local storage, and distributed storage. The computing layer 11 manages and accesses data based on the storage layer 13. The computing layer 11 can specifically manage and access data based on a big data execution engine. The data table format (TableFormat) in the computing layer 11 is optionally deployed. The storage layer 13 and the computing layer 11 are connected via a network 12.

[0029] Among them, the TableFormat is a table format that enables the data lake to have ACID and strengthen the data management capabilities. Open source systems such as Iceberg, Hudi, and DeltaLake are all open source products of TableFormat. ACID is the abbreviation of the four basic elements for the correct execution of database transactions. ACID includes: Atomicity, Consistency, Isolation, and Durability. A database that supports transactions needs to have these four characteristics, otherwise the correctness of the data cannot be guaranteed during the transaction processing.

[0030] In the application environment shown in FIG1 , a data distribution control method provided in an embodiment of the present application can be executed by a big data computing engine deployed in the computing layer 11. The big data computing engine may include, but is not limited to, one of Spark, Flink, and Presto. The database used in the embodiment of the present application may include, but is not limited to, Greeplum.

[0031] In actual deployment, based on the application scenario, the big data computing engine can be deployed in but not limited to one of the following scenarios:

[0032] A separate big data operating environment (not based on the TableFormat system);

[0033] A separate big data operating environment (based on the TableFormat system);

[0034] A separate data warehouse operating environment (based on the TableFormat system);

[0035] A separate data warehouse operating environment (not based on the TableFormat system);

[0036] Lake-warehouse integrated operating environment (co-deployment of big data and data warehouse engines based on TableFormat).

[0037] The operating environment of the big data computing engine is not limited to physical machine environments, cloud environments, etc.

[0038] A data distribution control method provided in an embodiment of the present application is described below with reference to the accompanying drawings.

[0039] As shown in FIG2 , an embodiment of the present application provides a data distribution control method, which may include:

[0040] Step 201 : generating predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system.

[0041] The data management system may be the big data computing engine mentioned above. The multiple data tables may be all or part of the data tables managed by the data management system. Data management systems often categorize and manage data records in the form of data tables. Typically, a data management system can manage multiple data tables, and a data table is split into multiple data files for storage. The data management system can provide users with data query and analysis functions.

[0042] The historical query statements can be structured query statements (SQL). In SQL queries, the WHERE clause specifies search conditions to filter the rows returned by the FROM clause. The WHERE clause only returns rows where the search conditions evaluate to TRUE. The search conditions are logical expressions or combinations of multiple logical expressions. In many real-world workloads, particularly those such as reporting and ETL (Extract, Transform, and Load) jobs, similar queries are often run repeatedly over different timeframes. Many queries use recurring search conditions, with only a small fraction of these conditions being newly introduced over time. Because search conditions recur, perhaps 20% of the search conditions are used for 80% of the queries. Therefore, the majority of future search conditions can be predicted based on past workload conditions. This means that designing a data layout based on these few recurring search conditions can benefit the majority of queries. Guided by this approach, embodiments of the present application provide a data distribution control method that extracts predicate features based on historical query mining to optimize data layout, and automatically generates data distribution optimization rules to cover a wider range of query scenarios.

[0043] The data management system can record the execution of SQL statements in real time and, based on the historical SQL statements, generate predicate application statistics for multiple data tables in the data management system. The predicate application statistics may include, but are not limited to, predicate selectivity and predicate usage frequency. The predicate selectivity is used to characterize the ability to skip data when scanning data files using the predicate. The predicate selectivity is negatively correlated with the data skipping ability; that is, the higher the predicate selectivity, the weaker the data skipping ability when scanning data files using the predicate, and vice versa.

[0044] As an example, step 201 may include:

[0045] Step 201.1: extract multiple predicates for the multiple data tables from historical query statements submitted to the data management system, wherein a single-table condition in a historical query statement corresponds to a predicate.

[0046] A query statement usually contains multiple data tables. The query optimizer will decompose the search conditions according to the data tables to which they belong, forming single-table conditions. Correspondingly, each single-table condition is a predicate.

[0047] In an exemplary embodiment, in step 201.1, the abstract syntax tree of the query statement may be parsed and predicates may be extracted from the abstract syntax tree. An abstract syntax tree is a syntax tree generated by parsing a query statement. In practical applications, a query statement may refer to an SQL statement. Abstract syntax tree generation and predicate extraction are prior art techniques and will only be briefly described here without further details.

[0048] In SQL, a predicate is a logical expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates filter data tables based on the logical expression, and the filtering effect directly affects query efficiency. The definition of a predicate's logical expression uses SQL syntax and is in the form of:<table.column> <operator> <value>.

[0049] Among them, table.column is the data column in the logical expression of the predicate, and at least one data column is included as part of the predicate expression.

[0050] Value is the value in the logical expression of the predicate. The value in the predicate expression can be a string literal or a numeric literal.

[0051] Among them, Operator is an operator. Comparison operators and logical operators are used in the logical expression of the predicate, including comparison (=, <, >, >=, !=, <>, !>, !<, etc.), character matching (like), range determination (between), set determination (in), null value (isnull), multiple conditions (and, or, not), etc.

[0052] Take SELECT a,d FROM R,S WHERE Rb=Sc AND Ra=5 AND Sd>200 as an example. The abstract syntax tree generated by the example SQL is shown in Figure 3. The two predicates generated are: a=5 and d>200.

[0053] Step 201.2: Statistics are performed on the multiple predicates to obtain predicate application statistical information for the multiple data tables, wherein the predicate application statistical information includes the selectivity of the predicate and the frequency of use of the predicate. The selectivity of the predicate is used to characterize the data skipping ability during the scanning of data files using the predicate, and the selectivity of the predicate is negatively correlated with the data skipping ability.

[0054] In an exemplary embodiment, step 201.2 may include: calculating the selectivity of each predicate for the plurality of predicates; and counting the usage frequency of each predicate for the plurality of predicates.

[0055] The selectivity of a predicate can be the ratio of the actual number of rows scanned in a single table to the total number of rows in the table when the query executes the predicate condition. The more rows skipped during the table scan, the lower the selectivity and the better the data skipping capability.

[0056] In practical applications, the query timestamp, original query statement, and predicate information corresponding to the query statement can be stored in the log system in advance for subsequent statistical use. In an exemplary embodiment, it can be stored in the form of a table, such as shown in Table 1.

[0057] Table 1 Example of historical query statement information

[0058] Furthermore, predicate application statistics generated based on the query timestamp, data table name, predicate, and predicate selectivity corresponding to the query statement can also be stored in the log system for subsequent use. In an exemplary embodiment, the statistics can be stored in a table, such as shown in Table 2.

[0059] Table 2 Examples of predicate application statistics for multiple data tables in the data management system

[0060] Step 202 : Determine a hot data table among the multiple data tables and at least one hot column group in the hot data table according to the predicate application statistical information.

[0061] As an example, step 202 may include:

[0062] Step 202.1: Based on the predicate application statistics, select from the multiple data tables a data table whose predicate usage frequency is greater than or equal to a first threshold and whose predicate selection rate is greater than or equal to a second threshold as a hot data table.

[0063] Through step 202.1, at least one data table with high access frequency and high predicate selectivity (poor data skipping effect) can be screened out from the multiple data tables as a hot data table.

[0064] The first threshold and the second threshold can be flexibly set according to experience and actual needs. For example, the first threshold can be 50 times / s, and the second threshold can be 50%.

[0065] As an implementation method, in step 202.1, the multiple data tables can be first sorted in descending order of predicate usage frequency based on the predicate usage frequency recorded in the predicate application statistics of the multiple data tables, and the top N data tables can be used as candidate hot data tables, where N is a positive integer; then, based on the predicate selectivity recorded in the predicate application statistics of the multiple data tables, the average predicate selectivity of the candidate hot data tables can be determined, and the candidate hot data tables can be sorted in descending order of the average predicate selectivity, and the top M candidate data tables can be used as hot data, where M is a positive integer. It is not difficult to see that one purpose of step 202.1 is to filter out at least one data table with high access frequency and high predicate selectivity (poor data skipping effect) from the multiple data tables as a hot data table.

[0066] As another implementation, in step 202.1, the weighted values ​​(such as average values) of the usage frequencies and selectivities of the predicates recorded in the predicate application statistics of the multiple data tables may be first determined; then the multiple data tables may be sorted in descending order according to the weighted values ​​of the predicates, and the top N data tables may be selected as candidate hotspot data tables, where N is a positive integer.

[0067] It is not difficult to see that one purpose of step 202.1 is to select at least one data table with high access frequency and high predicate selectivity (poor data skipping effect) from the multiple data tables as a hot data table.

[0068] Furthermore, step 202 may also include:

[0069] Step 202.2: Determine the hotspot predicates according to the usage frequency of the predicates in the predicate application statistics of the hotspot data table.

[0070] In one embodiment, step 202.2 may include:

[0071] 1) Formatting the predicate values ​​in the predicate application statistical information according to the predicate type, wherein for predicates of the same type, the formatted expressions are consistent.

[0072] In an exemplary embodiment, the predicate can be parsed according to grammatical rules and the value of the predicate's logical expression can be uniformly formatted. For example, for a string, "***" can be used to replace it, and for a number, "#" can be used to replace it. For example, for the search condition in the predicate's logical expression: c1<5and(c2=2orc3=3), it can be formatted as: c1<#and(c2=#orc3=#); for the search condition in the predicate's logical expression: c1<51and(c2=12orc3=13), it can be formatted as: c1<#and(c2=#orc3=#).

[0073] 2) Merge the same predicates after formatting.

[0074] 3) Determine predicate application statistics of the hot data table after merging predicates.

[0075] Optionally, the formatted predicate list is sorted, and then a group of predicates with the same format are merged. The average selectivity of all predicates in the merged group of predicates is used as the selectivity of the merged predicates. Of course, the median or maximum selectivity of all predicates in the merged group of predicates can also be used as the selectivity of the merged predicates. Similarly, the average usage frequency of all predicates in the merged group of predicates is used as the usage frequency of the merged predicates. Of course, the median or maximum usage frequency of all predicates in the merged group of predicates can also be used as the usage frequency of the merged predicates.

[0076] Optionally, a predicate type is generated according to the predicate operator. The predicate type may include but is not limited to point query, range query, etc.

[0077] Optionally, the data columns in the logical expression of the predicate are parsed to generate at least one column group. For example, for the following search condition in the logical expression of the predicate: c1 < # and (c2 = # or c3 = #), the corresponding parsed column group is: (c1, c2, c3), where c1, c2, and c3 are the names of the data columns. In an exemplary embodiment, the predicate application statistics of the hot data table after the predicates are merged may be as shown in Table 3.

[0078] Table 3 Predicate application statistics of hot data tables after merging predicates

[0079] 4) Determine the hot predicate according to the usage frequency of the predicate in the predicate application statistical information of the hot data table after the predicates are merged.

[0080] As an example, predicates recorded in the predicate application statistics of the hot data table after the predicates are merged and whose usage frequency is greater than a third preset value can be determined as hot predicates. In an exemplary embodiment, the predicates in the predicate application statistics of the hot data table after the predicates are merged can be sorted from high to low by access frequency, and the top L predicates in the sorting can be obtained to generate a hot predicate list, where L is a positive integer.

[0081] Step 202.3: Determine multiple column groups of the hot data table queried by the hot predicate.

[0082] Step 202.4: Determine at least one column group among the multiple column groups whose support is greater than or equal to a third threshold as at least one hotspot column group in the hotspot data table.

[0083] The third threshold value may be flexibly set according to experience and actual needs. For example, as shown in FIG. 4 , the third threshold value may be 50%.

[0084] In addition to the method described in step 202.4, in another embodiment, multiple column groups of the hot data table queried by the hot predicate can also be directly determined as hot column groups. Of course, hot column groups can also be determined in other ways, and hot column groups are related to hot predicates.

[0085] In an exemplary embodiment, in step 202.4, based on a K-frequent item mining algorithm, K-frequent item sets having a support greater than or equal to a first threshold value can be mined from multiple column groups of the hot data table queried by the hot predicate; and the column groups included in the K-frequent item sets are determined as at least one hot column group in the hot data table.

[0086] Figure 4 illustrates a schematic diagram of the process of generating K frequent items with support greater than or equal to 50%, where K ≤ 3. As shown in Figure 4, for multiple column groups of a hot data table queried by a hot predicate, candidate 1-item sets and their supports are first generated; then, frequent 1-item sets with support greater than or equal to 50% are filtered out from the candidate 1-item sets; then, candidate 2-item sets and their supports are generated, and frequent 2-item sets with support greater than or equal to 50% are filtered out from the candidate 2-item sets; then, candidate 3-item sets and their supports are generated, and frequent 3-item sets with support greater than or equal to 50% are filtered out from the candidate 3-item sets. If more frequent itemsets are needed, the process continues in this manner.

[0087] It should be noted that the process of generating K frequent itemsets is an existing technology. The Apriori algorithm, the frequent itemset algorithm for mining association rules, etc. can be used to generate K frequent itemsets, which will not be elaborated in this article.

[0088] As mentioned above, since the columns involved in the aggregation and their relative order determine the tightness of the data aggregation, the user must carefully select the columns that need to participate in the aggregation. Improper adjustments will lead to performance degradation, which is one of the unresolved pain points in the relevant technology. A data distribution control method proposed in an embodiment of the present application attempts to solve this pain point by extracting predicate features from historical query statements to find the hot data columns and the order of hot data columns involved in sorting or aggregation. This method makes the columns involved in the aggregation more reasonable. After clustering these columns, the aggregation effect can be improved, thereby improving the data skipping effect during query.

[0089] Step 203 : Generate data distribution optimization rules for the hot data table according to a preset algorithm, wherein the data distribution optimization rules include optimization rules for the at least one hot column group.

[0090] Optionally, if the hotspot data table satisfies at least one of the following conditions, step 203 is performed:

[0091] 1) First data table reorganization condition: The proportion of data files corresponding to invalid column statistics in the hot data table to the total data files exceeds a threshold, indicating that there is a significant deviation in the data distribution of the hot data table, low data skipping efficiency during table scans, and low filtering efficiency of column statistics. Therefore, the data in the hot data table needs to be rearranged.

[0092] 2) Second data table reorganization condition: The proportion of small data files in the hot data table exceeds the threshold. Each insertion contains a small number of insertion records (far smaller than the default file block size), and each insertion adds a new row to the first statistical information. In stream processing scenarios, continuously inserting data into the table or performing operations such as merge and update will generate a large number of small files. Excessive small files will slow down queries and cause system scalability issues, requiring the data file size of the hot data table to be adjusted.

[0093] How to determine whether a hotspot data table satisfies the first data table reorganization condition and how to determine whether a hotspot data table satisfies the second data table reorganization condition will be described below with examples.

[0094] In some embodiments, before step 203, the data distribution control method provided in embodiments of the present application may further include: determining column statistics in the data file of the hot data table and the validity of the column statistics; and determining whether the hot data table meets a first data table reorganization condition based on the validity of the column statistics in the data file of the hot data table. If the hot data table meets the first data table reorganization condition, step 203 is executed.

[0095] The column statistics information may include but is not limited to at least one of the following:

[0096] Column maximum and column minimum;

[0097] Leblom filter value.

[0098] A data distribution control method provided in an embodiment of the present application takes into account the validity of the column statistical information of the hot data table when generating the data distribution optimization rules of the hot data table, and rearranges the data distribution of the hot column group with invalid column statistical information, thereby improving the effectiveness of data skipping.

[0099] As an exemplary implementation, a set of statistical data of the hot data table can be directly obtained as the second statistical information, where the second statistical information is column statistical information in each data file of the hot data table. The second statistical information does not necessarily include all data columns in the hot data table. The results of the second statistical information can be stored in the form of a table, where each row in the table corresponds to a data file of the hot data table. Each row in the table may include three attributes: the minimum value of the column, the maximum value of the column, and the column Bloom filter value, as shown in Table 4.

[0100] Table 4 Schematic table of the second statistical information

[0101] In Table 4, MIN_Ci represents the column minimum value of the i-th column, MAX_Ci represents the column maximum value of the i-th column, and Bloom_Ci represents the column Bloom filter value of the i-th column.

[0102] The second statistic is most effective during data skipping when data is clustered around highly selective, commonly used predicate columns. However, if the minimum and maximum values ​​in the column statistics for a data file define a very large range, especially if they are close to or equal to the entire range of the column in the hotspot data table, the statistics are invalid. Similarly, the greater the number of values ​​represented in a Bloom filter, the greater the likelihood that the Bloom filter will have a high density of "1s," resulting in inefficient filtering (and excessive false positives). In extreme cases, a Bloom filter may set all or nearly all bits to 1, also resulting in invalid statistics. Similar issues exist for other statistics, such as bitmaps.

[0103] Therefore, in one example, assuming that the column statistics include the column maximum value and the column minimum value, determining the column statistics in the data file of the hot data table and the validity of the column statistics may include: for any data column in any data file of the hot data table, determining the maximum value and minimum value of the data column in the data file, and determining whether the range between the minimum value and the maximum value of the data column in the data file is close to or equal to the range between the minimum value and the maximum value of the data column in the hot data table; if so, determining that the minimum value and the maximum value of the data column in the data file are invalid for data skipping.

[0104] In another example, assuming that the column statistics include a column Bloom filter value, determining the column statistics in the data file of the hot data table and the validity of the column statistics may include: determining, for any data column in any data file of the hot data table, the column Bloom filter value of the data column in the data file; determining whether the proportion of positions in the data file where the column Bloom filter value of the data column is 1 is greater than or equal to a fourth threshold, and if so, determining that the column Bloom filter value of the data column in the data file is invalid for data skipping.

[0105] The fourth threshold value may be flexibly set according to experience and actual needs. For example, the fourth threshold value may be 50%.

[0106] Optionally, after determining the validity of the column statistics of each data column in each data file of the hot data table, the number of data files corresponding to invalid statistics of each column of the hot data table may also be recorded.

[0107] Based on the above, as an example, determining whether the hot data table meets the first data table reorganization condition based on the validity of the column statistical information in the data file of the hot data table may include:

[0108] 1) According to the validity of the column statistical information in the data file of the hot data table, determine the proportion of data files corresponding to the invalid column statistical information of each data column to all data files of the hot data table, and obtain the first proportion corresponding to each data column, that is, determine the proportion of data files corresponding to the invalid column statistical information of the hot data table to the total data files of the hot data table.

[0109] In an exemplary embodiment, the first ratio=the number of data files in the hot data table corresponding to the column invalid statistical information / the total number of files in the hot data table.

[0110] 2) According to the first ratio corresponding to each data column of the hot data table, determine the ratio of the data files corresponding to the invalid column statistical information corresponding to the hot data table to the total data files, and obtain the second ratio, that is, determine the ratio of the data files corresponding to the invalid column statistical information of the hot data table to the total data files of the hot data table.

[0111] There are many ways to determine the second ratio, two of which are described below.

[0112] In a first embodiment, the average value of the first ratios corresponding to the data columns in the hot data table is directly determined as the ratio of the data files corresponding to the invalid column statistical information corresponding to the hot data table to the total data files.

[0113] In a second embodiment, for any data column of the hot data table, determine whether the data column is in the at least one hot column group (for example, for any data column of the hot data table, determine whether the data column is in the K frequent item sets determined above), and if so, determine the data column as a hot column; and determine the average value of the first ratio corresponding to all hot columns in the hot data table as the ratio of data files corresponding to the invalid column statistical information corresponding to the hot data table to all data files.

[0114] 3) When the second ratio is greater than or equal to the fifth threshold, it is determined that the hot data table meets the first data table reorganization condition; when the second ratio is less than the fifth threshold, it is determined that the hot data table does not meet the first data table reorganization condition.

[0115] The fifth threshold value may be flexibly set according to experience and actual needs. For example, the fifth threshold value may be 20%.

[0116] Furthermore, when the hotspot data table meets the first data table reorganization condition, step 203 may include:

[0117] Step 203.1: Filter out a target hotspot column group from the at least one hotspot column group according to a preset rule, that is, determine the column group in the hotspot data table that participates in data re-layout.

[0118] There are many ways to filter out the target hotspot column group from the at least one hotspot column group according to preset rules, and several of them are described below.

[0119] As an implementation manner, screening out the target hotspot column group from the at least one hotspot column group according to a preset rule may include: determining all of the at least one hotspot column group as the target hotspot column group.

[0120] As another embodiment, filtering out a target hotspot column group from the at least one hotspot column group according to a preset rule may include: determining predicate application statistical information for the at least one hotspot column group to obtain first statistical information, wherein the first statistical information includes the support corresponding to the at least one hotspot column group and the selectivity of the predicate, as shown in Table 5; determining a ranking weight of the first column group based on the support corresponding to the first column group, the selectivity of the predicate, and a third ratio in the first statistical information, wherein the first column group is any column group in the at least one hotspot column group, and the third ratio is the ratio of the number of data columns included in the first column group to the total number of data columns in the hotspot data table; filtering out a column group with a ranking weight greater than or equal to a sixth threshold from the at least one hotspot column group as the target hotspot column group, or selecting the column group with the largest ranking weight in the at least one hotspot column group as the target hotspot column group. The sixth threshold can be flexibly set based on experience and actual needs, for example, the sixth threshold can be 0.6.

[0121] Table 5 Schematic table of the first statistical information of hotspot column groups (such as the above K frequent item sets)

[0122] For example, the ranking weight of the first column group = (K value of K frequent itemsets / total number of columns in the hot data table) * α + support of K frequent itemsets * β + selection rate of K frequent itemsets * γ;

[0123] Wherein, α+β+γ=1, illustratively, α=0.2, β=0.4, γ=0.4;

[0124] Among them, α represents the weight of the number of columns in the K frequent itemsets, β represents the weight of the support of the K frequent itemsets, and γ represents the weight of the selection rate of the K frequent itemsets. The weights can be adjusted according to the business scenario.

[0125] Optionally, by default K<=3, if the hotspot column group includes more than four data columns, the aggregated data columns may be limited to two or three to obtain a better aggregation effect.

[0126] For example, for the first statistic in Table 5, calculated according to the above rules, the weighted value of {C2, C3, C4} with K = 3 is the largest. Therefore, {C2, C3, C4} can be selected as the target hotspot column group. This way, when a query contains any filter predicate combining C2, C3, and C4, data is effectively skipped. Meanwhile, the data layout of other columns remains unchanged, and the query is unaffected.

[0127] As a third implementation, filtering out a target hot column group from the at least one hot column group according to a preset rule may include: determining predicate application statistical information for the at least one hot column group to obtain first statistical information, wherein the first statistical information includes the support corresponding to the at least one hot column group and the selectivity of the predicate, as shown in Table 5; determining a sorting weight of the first column group based on one or two of the support corresponding to the first column group in the first statistical information, the selectivity of the predicate, and a third ratio, wherein the first column group is any column group in the at least one hot column group, and the third ratio is the ratio of the number of data columns included in the first column group to the total number of data columns in the hot data table; filtering out a column group with a sorting weight greater than or equal to a sixth threshold from the at least one hot column group as the target hot column group, or selecting the column group with the largest sorting weight in the at least one hot column group as the target hot column group.

[0128] As a fourth implementation, the preset rule may be a randomly selected rule, and filtering out the target hotspot column group from the at least one hotspot column group according to the preset rule may include: randomly filtering out the target hotspot column group from the at least one hotspot column group.

[0129] Optionally, before executing step 203, the partition column included in the at least one hot column group may be deleted. That is, it is determined whether the data column in the K frequent itemset is a partition column. If so, it is deleted from the K frequent itemset and the partition column does not participate in data re-layout.

[0130] In step 203.2, if the number of data columns in the target hotspot column group is one, a sorting rule for the data elements in the target hotspot column group is generated according to a first preset sorting algorithm. It will be appreciated that the number of data columns in the target hotspot column group is one, indicating that most or all queries filter only on a single column, and the default sorting algorithm is used, i.e., sorting the data elements within the data column.

[0131] The first preset sorting algorithm may include but is not limited to sorting by numerical value.

[0132] Step 203.3: If the number of data columns in the target hotspot column group is greater than 1, perform at least one of the following:

[0133] 1) Generating a sorting rule for the data columns in the target hotspot column group according to a second preset sorting algorithm;

[0134] Exemplarily, the second preset sorting algorithm may include but is not limited to at least one of the following:

[0135] ① Sort in descending order according to the first proportion of the data columns in the target hotspot column group, that is, sort by the degree of deviation of the data distribution;

[0136] ② Sort the data columns in the target hotspot column group in descending order based on their cardinality. Data tables are usually filtered by high-cardinality columns. Clustering on fields with a very small value distribution range is not very advantageous because the values ​​of different rows are already close to each other. Columns with high cardinality (a large number of different values) are more suitable for clustering.

[0137] ③ Sort the data columns in the target hotspot column group in descending order according to the number of queries; and

[0138] ④Sort by business dimension, etc.

[0139] For example, for the target hotspot column group {C2, C3, C4}, if the data columns are sorted in descending order according to the first ratio corresponding to the data columns, the final generated order is: C2, C4, C3, that is, the final column group that needs to be aggregated is: {C2, C4, C3}.

[0140] 2) Generate aggregation rules for the data elements in the target hotspot column group based on a preset aggregation algorithm. For example, for multi-dimensional target hotspot column groups, the preset aggregation algorithm defaults to Z-Order. Z-Order is suitable for data with similar distribution and range. Furthermore, in business scenarios with small incremental data, the Z-Order layout has a minimal impact on performance.

[0141] It can be understood that by sorting and multi-dimensionally clustering the data columns that are frequently used in the SQL where clause (i.e., the hot column group), closely clustering the related columns, and skipping data in conjunction with the second statistical information during querying, the amount of data that needs to be scanned can be reduced, thereby improving query efficiency.

[0142] In other embodiments, before step 203, a data distribution control method proposed in an embodiment of the present application may further include: determining the data file size statistical information of the hot data table; determining whether the hot data table meets the second data table reorganization condition based on the data file size statistical information; and executing step 203 if the hot data table meets the second data table reorganization condition.

[0143] The data file size statistics information of the hot data table includes but is not limited to the size information of each data file in the hot data table.

[0144] As an example, determining whether the hot data table meets the second data table reorganization condition based on the data file size statistics includes: determining a fourth ratio based on the data file size statistics of the hot data table, wherein the fourth ratio is the ratio of data files whose file size is less than or equal to a seventh threshold among all the data files in the hot data table; determining that the hot data table meets the second data table reorganization condition when the fourth ratio is greater than or equal to an eighth threshold; and determining that the hot data table does not meet the second data table reorganization condition when the fourth ratio is less than the eighth threshold. In other words, if the proportion of small data files in the hot data table among all the data files in the hot data table is greater than or equal to the eighth threshold, the hot data table is determined to meet the second data table reorganization condition. Small data files are data files whose file size is less than or equal to the seventh threshold. The seventh and eighth thresholds can be flexibly set based on experience and actual needs. For example, the seventh threshold can be 256MB and the eighth threshold can be 30%.

[0145] On this basis, the above step 203 may include: generating a data file size adjustment rule for the hot data table according to a preset file size adjustment algorithm.

[0146] The preset file adjustment algorithm may include but is not limited to at least one of the following:

[0147] Data file merging algorithm for data table dimensions;

[0148] Data file merging algorithm for partition dimension;

[0149] Data file adjustment algorithm based on data table size.

[0150] For example, in the data table dimension, automatic merging rules for data files in hot data tables are generated to automatically merge small files and generate data files of balanced sizes from the data in the hot data tables. This optimizes the size and effectiveness of column statistics (the second statistical information mentioned above), avoids excessive column statistics entries, and ensures data skipping effectiveness.

[0151] For example, data file resizing rules are generated based on the size of the hotspot data table, where the data file size is positively correlated with the size of the hotspot data table. Specifically, smaller data files are used for smaller hotspot data tables, while larger data files are used for larger hotspot data tables. This prevents the number of data files in a hotspot data table from becoming excessively large. This also optimizes the size and validity of column statistics, preventing an excessive number of column statistics entries and ensuring data skipping effectiveness.

[0152] It can be known from the above embodiment that one or more of the following data distribution optimization rules can be generated through step 203:

[0153] Rule 1: Data distribution optimization rules for hot column groups, such as at least one of the sorting and aggregation rules for hot column groups. It can be appreciated that multi-dimensional aggregation or sorting of data columns frequently used in SQL where clauses (i.e., hot column groups) can be performed to tightly cluster related columns. This, combined with the aforementioned second statistical information, can be used to skip data during queries, reducing the amount of data to be scanned and improving query efficiency.

[0154] Rule 2: Automatic merging of small data files. As you can see, automatically merging small files can generate data files of balanced size from the data in the hotspot data table. This optimizes the size and effectiveness of column statistics (the second statistical information mentioned above), avoids excessive column statistics entries, and ensures data skipping effectiveness.

[0155] Rule 3: Automatic data file resizing rules. Data file resizing rules are generated based on the size of the hotspot data table, where the data file size is positively correlated with the size of the hotspot data table. Specifically, smaller data files are used for smaller hotspot data tables, while larger data files are used for larger hotspot data tables. This prevents the number of data files in a hotspot data table from becoming excessively large. This also optimizes the size and validity of column statistics, preventing an excessive number of column statistics entries and ensuring data skipping effectiveness.

[0156] In actual applications, corresponding data distribution optimization rules can be generated based on whether the hotspot data table meets at least one of the first data table reorganization condition and the second data table reorganization condition, and the above-mentioned Rule 3 can be used in conjunction with at least one of the above-mentioned Rules 1 and 2. In an exemplary embodiment, if the hotspot data table meets the first data table reorganization condition, the above-mentioned Rule 1 is generated by default; if the hotspot data table meets the second data table reorganization condition, the above-mentioned Rule 2 is generated by default. Optionally, if the hotspot data table meets at least one of the first data table reorganization condition and the second data table reorganization condition, the above-mentioned Rule 3 can also be generated.

[0157] Step 204: Rearrange the data distribution of the hot data table according to the data distribution optimization rule.

[0158] Optionally, before executing step 204, the data distribution optimization rule generated in step 203 may be subjected to a validity check according to a preset conflict check strategy. For example, the preset conflict check strategy may include, but is not limited to: one hotspot data table corresponds to one aggregation rule, and the same data column can only be aggregated once, etc.

[0159] Optionally, after performing a validity check on the data distribution optimization rules generated in step 203 according to a preset conflict check strategy, before executing step 204, as shown in FIG5 , a data distribution control method provided in an embodiment of the present application may further include: converting the data distribution optimization rules into metadata, and storing the metadata as an attribute of the hot data table in a metadata system. Accordingly, step 204 may include: reading the metadata from the metadata operating system and executing it to rearrange the data distribution of the hot data table according to the data distribution optimization rules, thereby improving the efficiency of data rearrangement.

[0160] For example, as shown in FIG6 , the data distribution optimization rules and their corresponding hot spot data tables and target hot spot column groups are stored as table attributes in the metadata system 15 of the big data computing engine. When the executor 14 of the big data computing engine is executed, the target hot spot column group information is obtained from the metadata system 15, and the data in the target hot spot column group is reorganized according to the corresponding data distribution optimization rules.

[0161] In an exemplary embodiment, taking the big data computing engine 14 as iceberg as an example, as shown in Figure 6, iceberg's metadata tracks the table architecture, configuration, custom attributes and snapshots of the table content, and can write data distribution optimization rules and target hot spot column groups as table attributes into the metadata text of the iceberg table for storage.

[0162] Optionally, if the hotspot data table satisfies at least one of the first data table reorganization condition and the second data table reorganization condition, step 204 is performed.

[0163] The following examples illustrate how to rearrange the data distribution of hot data tables based on data distribution optimization rules.

[0164] 1) In the first example, assuming that the hot data table satisfies the first data table reorganization condition, and the data distribution optimization rule includes a sorting rule and an aggregation rule of the hot column group, step 204 may include: performing a sorting operation and an aggregation operation on the target hot column group in the hot data table according to the aggregation rule of the hot column group.

[0165] Optionally, for newly added partitions, the SQL logical plan is rewritten by querying metadata in the table attributes to create a sorting operator (order) and an aggregation operator (cluster). Within the partition, the columns in the target hot column group are sorted by the sorting operator, and the data in the sorted target hot column group is aggregated using the aggregation algorithm to generate a new data layout.

[0166] Optionally, when it is detected that the hot data table generates a new data file, the data distribution of the hot data table is rearranged according to the data distribution optimization rule. For example, when generating a new data file, the columns in the target hot column group are sorted according to the sorting operator, and the data in the sorted target hot column group is aggregated according to the aggregation algorithm to generate a new data layout. Optionally, the amount of data sorted or aggregated each time does not exceed the ninth threshold to reduce the computational burden. The ninth threshold can be set according to the total amount of data to be sorted or aggregated, or can be flexibly set according to experience and actual needs. For example, the ninth threshold can be 50TB.

[0167] In an exemplary embodiment, taking FIG6 as an example, when the executor 14 of a big data computing engine (such as spark) writes data, the write task (Task) generates a corresponding FileAppender according to the file format through the partition writer partitionWriter. FileAppender completes the actual file write operation, and fileAppender writes the data to the data file by calling different file format components. When the executor 14 of a big data computing engine (such as iceberg) writes, the write file target size can be adjusted by setting the write.target-file-size-bytes table attribute. When writing, the data distribution optimization rules are executed to sort or aggregate, generate sorted or aggregated data files, and update various statistical information of the metadata system. In FIG6, the data files are stored in different partitions of the storage engine 16, and the partitions can be divided according to low-cardinality columns, and high-cardinality columns can be laid out according to sorting or aggregation rules.

[0168] Optionally, you can use the incremental generation mode to build incremental aggregation or sorting tasks for real-time new data, and layout the data files in an incremental manner.

[0169] It can be understood that by building incremental aggregation or sorting tasks for newly generated data files or real-time new data, any new data ingested after running data aggregation commands such as Z-Order can be automatically aggregated without the user having to re-run the data aggregation command, thereby reducing the impact on existing businesses and enabling the computing engine to efficiently skip all logically unnecessary data, thereby improving the interactive analysis experience.

[0170] Furthermore, unlike related technologies, this embodiment also provides a method for dynamically and flexibly laying out data. New data can automatically adjust the data layout based on the data layout algorithm, while data clustered using the original clustering columns remains unaffected, minimizing the impact on related systems. In other words, if the data distribution optimization rules change, the data distribution can be adjusted more flexibly and efficiently without affecting existing data.

[0171] Optionally, the amount of data sorted or aggregated each time does not exceed a ninth threshold to reduce computational burden.

[0172] Optionally, for existing partitions: no adjustment is made, and existing data clustered using the original clustering columns is not affected; or, data distribution is generated according to the new data clustering algorithm generated in step 203 by running an external optimization command such as OPTIMIZE.

[0173] 2) In the second example, assuming that the hot data table satisfies the second data table reorganization condition, and the data distribution optimization rule includes a small file automatic merging rule for the data table dimension, step 204 may include: merging the small data files of the hot data table according to the small file automatic merging rule. For another example, assuming that the hot data table satisfies the second data table reorganization condition, and the data distribution optimization rule includes a small file automatic merging rule for the data partition dimension, step 204 may include: merging the small data files in each partition of the hot data table according to the small file automatic merging rule.

[0174] Optionally, you can trigger the automatic merging of small data files through a scheduled task.

[0175] 3) In the third example, the data distribution optimization rule includes a data file automatic adjustment rule, then step 204 may include: automatically adjusting the size of the data file according to the size of the hot data table, using smaller data files for smaller hot data tables, and using larger data files for larger hot data tables, thereby preventing the number of data files in the hot data table from becoming very large. By generating data files of consistent size, data skew can also be prevented.

[0176] For example, when the size of the entire hotspot data table is less than 1TB, the data file size will be 256MB. When the size of the entire hotspot data table is between 1-10TB, the size of the data file will increase linearly. When the size of the entire hotspot data table is greater than 10TB, the data file size will be 1GB.

[0177] Optionally, you can trigger the adjustment of the data file size through a scheduled task.

[0178] It can be understood that in the process of performing sorting operations and clustering operations on the target hot spot column group in the hot spot data table according to the clustering rules of the hot spot column group, the step of automatically adjusting the size of the data file according to the size of the hot spot data table can be superimposed; in the process of merging the small data files of the hot spot data table according to the automatic merging rules of small files, the step of automatically adjusting the size of the data file according to the size of the hot spot data table can also be superimposed.

[0179] Optionally, since data rearrangement involves reading and writing data, in order to prevent impact on existing jobs, the hot data table is locked during the process of rearranging the data distribution of the hot data table according to the data distribution optimization rules.

[0180] Optionally, after completing the data distribution rearrangement of the hot data table, update the column statistics in the data file of the hot data table, such as updating the maximum value, minimum value and number of null values, etc., to ensure the data skipping effect during the data query process and improve the query efficiency.

[0181] Table 6 Updated column statistics for col1 and col2

[0182] A data distribution control method provided by an embodiment of the present application can obtain predicate application statistical information of multiple data tables in the data management system based on historical query statements submitted to the data management system, and then determine the hot data table and at least one hot column group in the hot data table that needs to be optimized in layout based on the predicate application statistical information, and automatically generate data distribution optimization rules for the hot data table according to a preset algorithm, which can cover more query scenarios, wherein the data distribution optimization rules include optimization rules for the at least one hot column group; then, the data distribution of the hot data table is rearranged according to the data distribution optimization rules, so that the data distribution in the hot data table is more reasonable, instead of using the same layout rules for all data tables in the data management system. Therefore, the data skipping effect when querying the hot data table can be improved, and the query efficiency for the hot data table is ultimately improved.

[0183] In addition, a data distribution control method provided by an embodiment of the present application can, on the one hand, extract predicate features based on historical query statements to automatically identify hot data tables and at least one hot column group in the hot data tables, and automatically generate data distribution optimization rules, and automatically optimize the data distribution of hot data tables according to the data distribution optimization rules, thereby covering more query scenarios. On the other hand, unlike related technologies, this embodiment also provides a method for dynamically and flexibly laying out data. New data can automatically adjust the data layout according to the data layout algorithm, and data aggregated using the original aggregation columns will not be affected, which can reduce the impact on the existing system.

[0184] The above describes a data distribution control method provided by an embodiment of the present application. Corresponding to the above data distribution control method, an embodiment of the present application also provides a data distribution control device, which will be described below.

[0185] FIG7 is a schematic diagram of a data distribution control device 700 according to an embodiment of the present application. Referring to FIG7 , in a software implementation, the data distribution control device 700 may include: a first generation module 701 , a first determination module 702 , a second generation module 703 , and a re-layout module 704 .

[0186] The first generating module 701 is configured to generate predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system.

[0187] As an example, the first generation module 701 can be specifically used to: extract multiple predicates for the multiple data tables from historical query statements submitted to the data management system, wherein a single-table condition in a historical query statement corresponds to a predicate; perform statistics on the multiple predicates to obtain predicate application statistical information for the multiple data tables, wherein the predicate application statistical information includes the selectivity of the predicate and the frequency of use of the predicate, and the selectivity of the predicate is used to characterize the data skipping ability during the scanning of data files using the predicate.

[0188] The first determining module 702 is configured to determine a hot data table and at least one hot column group in the hot data table among the multiple data tables according to the predicate application statistical information.

[0189] As an example, the first determination module 702 can be specifically used to: select, from the multiple data tables, a data table whose predicate usage frequency is greater than or equal to a first threshold and whose predicate selection rate is greater than or equal to a second threshold as a hot data table based on the predicate application statistical information; determine a hot predicate based on the predicate usage frequency in the predicate application statistical information; determine multiple column groups of the hot data table queried by the hot predicate; and determine at least one column group among the multiple column groups whose support is greater than or equal to a third threshold as at least one hot column group in the hot data table.

[0190] Furthermore, the first determination module 702 can be specifically used to: format the value of the predicate in the predicate application statistical information according to the type of the predicate; merge the same predicates after formatting; determine the predicate application statistical information of the hot data table after the predicates are merged; and determine the hot predicate based on the usage frequency of the predicate in the predicate application statistical information of the hot data table after the predicates are merged.

[0191] The second generating module 703 is configured to generate data distribution optimization rules for the hot data table according to a preset algorithm, wherein the data distribution optimization rules include optimization rules for the at least one hot column group.

[0192] Optionally, in some embodiments, the device 700 may further include: a first judgment module, used to determine the column statistical information in the data file of the hot data table and the validity of the column statistical information before generating data distribution optimization rules for the hot data table according to a preset algorithm; determine whether the hot data table meets the first data table reorganization condition based on the validity of the column statistical information in the data file of the hot data table; and trigger the second generation module 703 when the hot data table meets the first data table reorganization condition.

[0193] The column statistics information includes at least one of the following:

[0194] Column maximum and column minimum;

[0195] Leblom filter value.

[0196] In an exemplary embodiment, if the column statistical information includes a column maximum value and a column minimum value, the first judgment module can be specifically used to: for any data column in any data file of the hot data table, determine the maximum value and minimum value of the data column in the data file, and determine whether the range between the minimum value and the maximum value of the data column in the data file is close to or equal to the range between the minimum value and the maximum value of the data column in the hot data table; if so, determine that the minimum value and the maximum value of the data column in the data file are invalid for data skipping.

[0197] In an exemplary embodiment, if the column statistics include a column Bloom filter value, the first judgment module can be specifically used to: determine, for any data column in any data file of the hot data table, the column Bloom filter value of the data column in the data file; determine whether the proportion of positions in the data file where the column Bloom filter value of the data column is 1 is greater than or equal to a fourth threshold, and if so, determine that the column Bloom filter value of the data column in the data file is invalid for data skipping.

[0198] Furthermore, the first judgment module can be specifically used to: determine the proportion of data files corresponding to invalid column statistical information of each data column to all data files of the hot data table according to the validity of column statistical information in the data file of the hot data table, and obtain a first proportion corresponding to each data column; determine the proportion of data files corresponding to invalid column statistical information corresponding to the hot data table to all data files according to the first proportion corresponding to each data column of the hot data table, and obtain a second proportion; when the second proportion is greater than or equal to the fifth threshold, determine that the hot data table meets the first data table reorganization condition; when the second proportion is less than the fifth threshold, determine that the hot data table does not meet the first data table reorganization condition.

[0199] Among them, the method of determining the proportion of data files corresponding to the invalid column statistical information corresponding to the hot data table to the total data files based on the first proportion corresponding to each data column of the hot data table includes: for any data column of the hot data table, determining whether the data column is in the at least one hot column group, and if so, determining the data column as a hot column; and determining the average value of the first proportions corresponding to all hot columns in the hot data table as the proportion of data files corresponding to the invalid column statistical information corresponding to the hot data table to the total data files.

[0200] Accordingly, the second generating module 703 may be specifically configured to: filter out a target hotspot column group from the at least one hotspot column group according to a preset rule; generate a sorting rule for the data elements in the target hotspot column group according to a first preset sorting algorithm when the number of data columns in the target hotspot column group is 1; and perform at least one of the following when the number of data columns in the target hotspot column group is greater than 1:

[0201] Generating a sorting rule for the data columns in the target hotspot column group according to a second preset sorting algorithm;

[0202] An aggregation rule for the data elements in the target hotspot column group is generated according to a preset aggregation algorithm.

[0203] Wherein, the filtering out a target hot column group from the at least one hot column group according to a preset rule includes: determining predicate application statistical information for the at least one hot column group to obtain first statistical information, wherein the first statistical information includes the support corresponding to the at least one hot column group and the selectivity of the predicate; determining a sorting weight of the first column group based on the support corresponding to the first column group in the first statistical information, the selectivity of the predicate, and a third ratio, wherein the first column group is any column group in the at least one hot column group, and the third ratio is the ratio of the number of data columns included in the first column group to the total number of data columns in the hot data table; and filtering out a column group having a sorting weight greater than or equal to a sixth threshold from the at least one hot column group as the target hot column group.

[0204] Optionally, the apparatus 700 may further include: a partition column deleting module, configured to delete the partition columns included in the at least one hot column group before generating the data distribution optimization rule for the hot data table according to a preset algorithm.

[0205] Optionally, in other embodiments, the device 700 may also include: a second judgment module, used to determine the data file size statistical information of the hot data table before generating the data distribution optimization rules for the hot data table according to the preset algorithm; determine whether the hot data table meets the second data table reorganization condition based on the data file size statistical information; and trigger the second generation module 703 when the hot data table meets the second data table reorganization condition.

[0206] The second judgment module is specifically configured to: determine a fourth ratio based on the data file size statistics of the hot data table, wherein the fourth ratio is the ratio of data files whose file sizes are less than or equal to a seventh threshold value among all the data files of the hot data table; determine that the hot data table satisfies the second data table reorganization condition when the fourth ratio is greater than or equal to an eighth threshold value; and determine that the hot data table does not satisfy the second data table reorganization condition when the fourth ratio is less than the eighth threshold value.

[0207] Accordingly, the second generating module 703 may be specifically configured to generate a data file size adjustment rule for the hotspot data table according to a preset file size adjustment algorithm.

[0208] The re-layout module 704 is configured to re-layout the data distribution of the hot data table according to the data distribution optimization rule.

[0209] Optionally, as shown in FIG8 , the apparatus 700 may further include: a metadata conversion module 705 and a metadata storage module 706 .

[0210] The metadata conversion module is used to convert the data distribution optimization rules into metadata.

[0211] The metadata storage module is used to store the metadata as attributes of the hot data table into a metadata system.

[0212] On this basis, the rearrangement module 704 can be specifically used to: read the metadata from the metadata operating system and execute it to rearrange the data distribution of the hot data table according to the data distribution optimization rule to improve data layout efficiency.

[0213] Optionally, the re-layout module 704 may be specifically configured to: when detecting that the hot data table generates a new data file, re-layout the data distribution of the hot data table according to the data distribution optimization rule to avoid affecting existing data.

[0214] Optionally, the device 700 may further include: a locking module for locking the hot data table when the data distribution of the hot data table is rearranged according to the data distribution optimization rule, so as to avoid the data rearrangement operation from affecting existing business.

[0215] Optionally, the apparatus 700 may further include: an updating module, configured to update column statistics in the data file of the hot data table after completing the data distribution rearrangement of the hot data table, so as to ensure the data skipping effect.

[0216] The data distribution control device 700 provided in the embodiment of the present application can obtain predicate application statistical information of multiple data tables in the data management system based on historical query statements submitted to the data management system, and then determine the hot data table and at least one hot column group in the hot data table that needs to be optimized according to the predicate application statistical information, and automatically generate data distribution optimization rules for the hot data table according to a preset algorithm, which can cover more query scenarios, wherein the data distribution optimization rules include optimization rules for the at least one hot column group; then, the data distribution of the hot data table is rearranged according to the data distribution optimization rules, so that the data distribution in the hot data table is more reasonable, instead of using the same layout rules for all data tables in the data management system. Therefore, the data skipping effect when querying the hot data table can be improved, and the query efficiency for the hot data table is ultimately improved.

[0217] In addition, the data distribution control device provided by the embodiment of the present application can, on the one hand, extract predicate features based on historical query statements to automatically identify hot data tables and at least one hot column group in the hot data tables, and automatically generate data distribution optimization rules, and automatically optimize the data distribution of hot data tables using the data distribution optimization rules, thereby covering more query scenarios. On the other hand, unlike related technologies, this embodiment also provides a device that can dynamically and flexibly layout data. New data can automatically adjust the data layout according to the data layout algorithm, and data clustered using the original clustering columns will not be affected, which can reduce the impact on the existing system.

[0218] The data distribution control device 700 provided in the embodiment of the present application can also execute the method of Figure 2, and realize the functions of the embodiment shown in Figure 2, and achieve the same technical effects. The embodiments of the present application are not described in detail here.

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

[0220] FIG9 is a schematic diagram of the structure of an electronic device according to an embodiment of the present application. Referring to FIG9 , at the hardware level, the electronic device includes a processor, and optionally an internal bus, a network interface, and a memory. The memory may include a memory, such as a high-speed random access memory (RAM), and may also include a non-volatile memory (non-volatile memory), such as at least one disk storage device. Of course, the electronic device may also include hardware required for other services.

[0221] The processor, network interface, and memory can be interconnected via an internal bus, such as an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus, or an EISA (Extended Industry Standard Architecture) bus. These buses can be classified as address buses, data buses, and control buses. For ease of illustration, FIG9 shows only one bidirectional arrow, but this does not imply that there is only one bus or only one type of bus.

[0222] The memory is used to store programs. Specifically, the program may include program code, which includes computer operating instructions. The memory may include internal memory and non-volatile memory, and provides instructions and data to the processor.

[0223] The processor reads the corresponding computer program from the non-volatile memory into the internal memory and then runs it, forming a data distribution control device at the logical level. The processor executes the program stored in the memory and is specifically used to perform the following operations:

[0224] Generate predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system;

[0225] determining a hot data table among the plurality of data tables and at least one hot column group in the hot data table according to the predicate application statistical information;

[0226] Generating a data distribution optimization rule for the hot data table according to a preset algorithm, wherein the data distribution optimization rule includes an optimization rule for the at least one hot column group;

[0227] The data distribution of the hot data table is rearranged according to the data distribution optimization rule.

[0228] The method performed by the data distribution control device disclosed in the embodiment shown in FIG. 9 of the present application can be applied to a processor or implemented by a processor. The processor may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the method can be completed by hardware integrated logic circuits in the processor or by software instructions. The processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be 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. The methods, steps, and logic block diagrams disclosed in the embodiments of the present application can be implemented or executed. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in the embodiments of the present application can be directly implemented and executed by a hardware decoding processor, or by a combination of hardware and software modules in the decoding processor. The software module can be located in a storage medium well-known in the art, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, etc. The storage medium is located in the memory, and the processor reads the information in the memory and, in conjunction with its hardware, completes the steps of the above method.

[0229] The electronic device can also execute the method of Figure 2 and realize the functions of the data distribution control device in the embodiment shown in Figure 2, and the embodiment of this application will not be described in detail here.

[0230] Of course, in addition to software implementation, the electronic device of this application does not exclude other implementation methods, such as logic devices or a combination of software and hardware, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0231] The present application also provides a computer-readable storage medium storing one or more programs. The one or more programs include instructions. When executed by a portable electronic device including multiple target applications, the instructions enable the portable electronic device to perform the method of the embodiment shown in FIG. 2 , and are specifically configured to perform the following operations:

[0232] Generate predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system;

[0233] determining a hot data table among the plurality of data tables and at least one hot column group in the hot data table according to the predicate application statistical information;

[0234] Generating a data distribution optimization rule for the hot data table according to a preset algorithm, wherein the data distribution optimization rule includes an optimization rule for the at least one hot column group;

[0235] The data distribution of the hot data table is rearranged according to the data distribution optimization rule.

[0236] In short, the above description is only a preferred embodiment of the present application and is not intended to limit the scope of protection of the present application. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application shall be included in the scope of protection of the present application.

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

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

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

[0240] The various embodiments in this application are described in a progressive manner. Similar parts between the various embodiments can be referred to in conjunction with each other. Each embodiment focuses on the differences between the other embodiments. In particular, the system embodiment is generally similar to the method embodiment, so the description is relatively simple. For relevant parts, refer to the partial description of the method embodiment.< / value> < / operator>

Claims

1. A data distribution control method, comprising: Generate predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system; determining a hot data table among the plurality of data tables and at least one hot column group in the hot data table according to the predicate application statistical information; Generating a data distribution optimization rule for the hot data table according to a preset algorithm, wherein the data distribution optimization rule includes an optimization rule for the at least one hot column group; The data distribution of the hot data table is rearranged according to the data distribution optimization rule.

2. The method according to claim 1, wherein The generating of predicate application statistics for multiple data tables in the data management system based on historical query statements submitted to the data management system includes: Extracting a plurality of predicates for the plurality of data tables from historical query statements submitted to the data management system, wherein a single-table condition in a historical query statement corresponds to a predicate; Statistics are collected for the multiple predicates to obtain predicate application statistics for the multiple data tables, wherein the predicate application statistics include a selectivity of the predicate and a usage frequency of the predicate. The selectivity of the predicate is used to characterize data skipping capability during scanning of data files using the predicate.

3. The method according to claim 2, wherein: The determining, based on the predicate application statistical information, a hot data table among the multiple data tables and at least one hot column group in the hot data table includes: According to the predicate application statistical information, a data table having a predicate usage frequency greater than or equal to a first threshold and a predicate selection rate greater than or equal to a second threshold is selected from the plurality of data tables as a hot data table; determining hot predicates according to usage frequencies of predicates in the predicate application statistics; Determining a plurality of column groups of the hot data table queried by the hot predicate; At least one column group of the plurality of column groups whose support is greater than or equal to a third threshold is determined as at least one hotspot column group in the hotspot data table.

4. The method according to claim 3, wherein: The determining of the hotspot predicates according to the usage frequency of the predicates in the predicate application statistics of the hotspot data table includes: formatting the predicate value in the predicate application statistics according to the predicate type; Merge the same predicates after formatting; Determining predicate application statistics of the hot data table after merging predicates; The hot predicate is determined according to the usage frequency of the predicate in the predicate application statistical information of the hot data table after the predicates are merged.

5. The method according to any one of claims 1 to 4, further comprising: Determining column statistics in a data file of the hot data table and validity of the column statistics; determining, based on validity of column statistics in a data file of the hot data table, whether the hot data table satisfies a first data table reorganization condition; In a case where the hot data table meets the first data table reorganization condition, the step of generating a data distribution optimization rule for the hot data table according to a preset algorithm is performed.

6. The method according to claim 5, wherein: The column statistics information includes at least one of the following: Column maximum and column minimum; Leblom filter value.

7. The method according to claim 6, wherein the column statistics information includes a column maximum value and a column minimum value, wherein: The determining of the column statistical information in the data file of the hot data table and the validity of the column statistical information includes: For any data column in any data file of the hot data table, determine the maximum value and minimum value of the data column in the data file, and determine whether the range between the minimum value and the maximum value of the data column in the data file is close to or equal to the range between the minimum value and the maximum value of the data column in the hot data table. If so, determine that the minimum value and the maximum value of the data column in the data file are invalid for data skipping.

8. The method of claim 6, wherein the column statistics include column Bloom filter values, The determining of the column statistical information in the data file of the hot data table and the validity of the column statistical information includes: For any data column in any data file of the hot data table, determining a column Bloom filter value of the data column in the data file; Determine whether the proportion of positions where the column Bloom filter value of the data column in the data file is 1 is greater than or equal to a fourth threshold; if so, determine that the column Bloom filter value of the data column in the data file is invalid for data skipping.

9. The method according to claim 5, wherein: The determining, based on the validity of the column statistical information in the data file of the hot data table, whether the hot data table meets the first data table reorganization condition includes: According to the validity of the column statistical information in the data files of the hot data table, determining the proportion of data files corresponding to the invalid column statistical information of each data column to all data files of the hot data table, and obtaining a first proportion corresponding to each data column; Determine, based on the first ratio corresponding to each data column of the hot data table, the ratio of data files corresponding to the invalid column statistical information corresponding to the hot data table to all data files, to obtain a second ratio; When the second ratio is greater than or equal to a fifth threshold, determining that the hotspot data table meets a first data table reorganization condition; When the second ratio is smaller than the fifth threshold, it is determined that the hotspot data table does not meet the first data table reorganization condition.

10. The method according to claim 9, wherein: The determining, based on the first ratio corresponding to each data column of the hot data table, the ratio of data files corresponding to the invalid column statistical information corresponding to the hot data table to the total data files includes: For any data column of the hot data table, determining whether the data column is in the at least one hot column group, and if so, determining the data column as a hot column; An average value of the first ratios corresponding to all hot columns in the hot data table is determined as the ratio of data files corresponding to the invalid column statistical information corresponding to the hot data table to all data files.

11. The method according to claim 5, wherein: The generating of data distribution optimization rules for the hot data table according to a preset algorithm includes: Filtering a target hotspot column group from the at least one hotspot column group according to a preset rule; When the number of data columns in the target hotspot column group is 1, generating a sorting rule for the data elements in the target hotspot column group according to a first preset sorting algorithm; When the number of data columns in the target hotspot column group is greater than 1, perform at least one of the following: Generating a sorting rule for the data columns in the target hotspot column group according to a second preset sorting algorithm; An aggregation rule for the data elements in the target hotspot column group is generated according to a preset aggregation algorithm.

12. The method according to claim 11, wherein The step of selecting a target hotspot column group from the at least one hotspot column group according to a preset rule includes: Determining predicate application statistics for the at least one hot column group to obtain first statistical information, wherein the first statistical information includes support corresponding to the at least one hot column group and a selectivity of the predicate; determining a sorting weight of the first column group based on the support corresponding to the first column group in the first statistical information, the selectivity of the predicate, and a third ratio, wherein the first column group is any column group in the at least one hot column group, and the third ratio is the ratio of the number of data columns included in the first column group to the total number of data columns in the hot data table; A column group whose ranking weight is greater than or equal to a sixth threshold is screened out from the at least one hot spot column group as a target hot spot column group.

13. The method according to claim 11, before generating the data distribution optimization rule for the hot data table according to the preset algorithm, the method further comprises: The partition columns included in the at least one hot column group are deleted.

14. The method according to any one of claims 1 to 4, further comprising: before generating a data distribution optimization rule for the hot data table according to a preset algorithm; Determine data file size statistics of the hot data table; determining, based on the data file size statistical information, whether the hot data table meets a second data table reorganization condition; In a case where the hot data table meets the second data table reorganization condition, the step of generating a data distribution optimization rule for the hot data table according to a preset algorithm is performed.

15. The method according to claim 14, wherein The determining, based on the data file size statistics of the hot data table, whether the hot data table satisfies the second data table reorganization condition includes: Determining a fourth ratio based on data file size statistics of the hot data table, wherein the fourth ratio is the ratio of data files whose file sizes are less than or equal to a seventh threshold among all data files in the hot data table; When the fourth ratio is greater than or equal to an eighth threshold, determining that the hotspot data table meets a second data table reorganization condition; When the fourth ratio is smaller than the eighth threshold, it is determined that the hotspot data table does not meet the second data table reorganization condition.

16. The method according to claim 14, wherein The generating of data distribution optimization rules for the hot data table according to a preset algorithm includes: A data file size adjustment rule for the hotspot data table is generated according to a preset file size adjustment algorithm.

17. The method according to any one of claims 1-4, 6-13, and 15-16, before rearranging the data distribution of the hot data table according to the data distribution optimization rule, the method further comprises: Converting the data distribution optimization rule into metadata; The metadata is stored in a metadata system as an attribute of the hotspot data table.

18. The method according to claim 17, wherein The rearranging of the data distribution of the hot data table according to the data distribution optimization rule includes: The metadata is read from the metadata operating system and executed to rearrange the data distribution of the hot data table according to the data distribution optimization rule.

19. The method according to any one of claims 1-4, 6-13, 15-16, wherein The rearranging of the data distribution of the hot data table according to the data distribution optimization rule includes: When it is detected that the hot data table generates a new data file, the data distribution of the hot data table is rearranged according to the data distribution optimization rule.

20. The method according to any one of claims 1-4, 6-13, 15-16, further comprising: When the data distribution of the hot data table is rearranged according to the data distribution optimization rule, the hot data table is locked.

21. The method according to claim 5, further comprising: After the data distribution re-layout of the hot data table is completed, the column statistics information in the data file of the hot data table is updated.

22. An electronic device comprising: processor; a memory for storing instructions executable by the processor; The processor is configured to execute the instructions to implement the method according to any one of claims 1 to 21.

23. A computer-readable storage medium, when instructions in the storage medium are executed by a processor of an electronic device, enables the electronic device to perform the method according to any one of claims 1 to 21.

Images