A multi-layer storage and query method and system for petabyte-scale unstructured data

Abstract

This invention belongs to the technical field of storage query, specifically relating to a multi-layer storage query method and system for petabyte-scale unstructured data. It addresses the technical problems of existing technologies, such as failure in complex predicate pushdown, lack of pushdown cost evaluation, and inability to resolve data skew and computational hotspots caused by intermediate result sets. The query method includes: S1, obtaining the query statement to be executed and decomposing it into a set of atomic predicates; S2, selecting an equivalent atomic predicate combination that minimizes execution cost by combining query history and storage medium performance indicators; S3, generating predicate pushdown decisions, which specify whether each atomic predicate should be executed in the storage layer or the computation layer; and S4, generating and executing a query plan. This invention improves the overall query performance and stability of petabyte-scale unstructured data in a multi-layer storage architecture.

Description

Technical Field

[0001] This invention belongs to the technical field of storage and query, specifically relating to a multi-layer storage and query method and system for petabyte-level unstructured data. Background Technology

[0002] With the rapid development of big data technology, data volume has reached the petabyte (PB) and even exabyte (EB) levels, with unstructured data dominating. During query execution, the computing layer needs to read massive amounts of data from the storage layer, and the network I / O and data transfer volume between the two have become key bottlenecks restricting query performance.

[0003] The core idea of ​​predicate pushdown technology is to push data filtering conditions down from the computation layer to the storage layer, which is closer to the data source. By filtering at the source of data reading, the scale of data that needs to be transmitted over the network to the upper layer for computation can be reduced, thereby reducing network load and I / O overhead and improving the overall query response speed.

[0004] However, when queries contain complex functions, specific data types, or non-standard expressions that the storage engine cannot directly parse, the traditional pushdown mechanism fails, forcing a fallback to the inefficient mode of pulling all data to the computation layer for filtering.

[0005] Furthermore, existing pushdown decision models are often too static and simplistic, typically only determining whether a predicate is "pushdownable" without assessing the cost of pushdown execution. This ignores the significant differences in processing power among different storage media and fails to consider the computational complexity of the predicate itself, potentially leading to situations where the local computational overhead of the pushdown operation outweighs the benefits of data transfer.

[0006] Finally, after the pushdown operation is completed, the size and data distribution of the generated intermediate result set are unknown. This makes it difficult for the upper-level computing engine to adjust the parallelism and optimize task scheduling for subsequent operators, which can easily lead to computational hotspots and long-tail problems, thereby affecting the execution efficiency of the entire query plan. Summary of the Invention

[0007] This invention provides a multi-layer storage and query method and system for petabyte-scale unstructured data to solve the technical problems of existing technologies such as failure in complex predicate pushdown, lack of pushdown cost evaluation, and inability to solve data skew and computational hotspots caused by intermediate result sets.

[0008] In a first aspect, the present invention provides a multi-layered storage and query method for petabyte-level unstructured data, comprising the following steps:

[0009] S1, obtain the query statement to be executed and decompose it into a set of atomic predicates;

[0010] S2, for each atomic predicate in the atomic predicate set, if it is not natively supported on a certain pushdown storage layer, then traverse the pre-built rewrite rule base, select an equivalent atomic predicate combination that estimates the minimum execution cost by combining query history and storage medium performance indicators, and use it as the rewrite result in the storage layer.

[0011] S3, based on natively supported atomic predicates or rewritten results, calculates the pushdown execution cost for each atomic predicate in each pushdownable storage layer; based on the pushdown execution cost of each atomic predicate in each storage layer and the preset cost threshold, generates a predicate pushdown decision, which specifies whether each atomic predicate is executed in the storage layer or the computation layer.

[0012] S4. Based on the predicate pushdown decision, using the statistical metadata of the data object, estimate the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed in the storage layer complete the filtering; using the cardinality and data skewness as input, adjust the cost model and parallelism of the atomic predicates executed in the computing layer, thereby generating a query plan that includes data partitioning strategy and cross-layer operator execution order, and execute the query plan.

[0013] Furthermore, select an equivalent atomic predicate combination that minimizes execution cost by combining query history and storage media performance metrics, including:

[0014] Establish and maintain a least recently used cache to store the historical execution times of equivalent atomic predicate combinations;

[0015] When the atomic predicate of the query is hit in the least recently used cache, the historical execution time is used as the historical cost.

[0016] When the queried atomic predicate is not hit in the least recently used cache, the theoretical execution time is calculated as the theoretical cost based on the performance indicators of the storage medium, which are 4K random read IOPS of solid-state drive, 1MB sequential read bandwidth of mechanical hard drive or API requests per second of object storage.

[0017] The execution cost is calculated using a weighted formula: Execution cost = 0.7 × historical cost + 0.3 × theoretical cost.

[0018] Furthermore, the pushdown execution cost is calculated for each atomic predicate in each pushdownable storage layer, including:

[0019] The pushdown execution cost is calculated using a multi-factor cost model. Push down execution cost The calculation formula is:

[0020] ;

[0021] in, , , These are dimensionless weighting coefficients. The number of rows to be processed. The number of CPU instructions processed per line. This refers to the number of instructions executed by the CPU per second. The number of data blocks scanned. The number of I / O operations per second for the storage medium. This represents the size of the intermediate result set after filtering. This refers to network bandwidth.

[0022] Furthermore, based on the pushdown execution cost of each atomic predicate in each storage layer and a preset cost threshold, a predicate pushdown decision is generated. The predicate pushdown decision specifies whether each atomic predicate should be executed in the storage layer or the computation layer, including:

[0023] Compare the pushdown execution costs of each atomic predicate in each storage layer. And the preset cost threshold;

[0024] When the pushdown execution cost When the cost is less than the preset cost threshold, the predicate pushdown decision is to push the atomic predicate down to the storage layer for execution;

[0025] When the pushdown execution cost When the cost is not less than the preset cost threshold, the predicate pushdown decision is executed at the computation layer.

[0026] Furthermore, by utilizing the statistical metadata of the data objects, the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed at the storage layer complete the filtering are estimated, including:

[0027] The statistical metadata is an equal-depth histogram of the data columns;

[0028] By querying the value range of the atomic predicate, the covered bucket interval is located in the equal depth histogram. The frequency of the fully covered buckets is accumulated, and the frequency of the partially covered buckets is calculated by linear interpolation according to the coverage ratio. The calculation results of all buckets are summed to obtain the cardinality.

[0029] Based on the distribution of the estimated base value across different data partitions, the ratio of the standard deviation to the mean is calculated to obtain the coefficient of variation, which is then used as the data skewness.

[0030] Furthermore, using the cardinality and data skewness as input, the cost model and parallelism of the atomic predicates executed at the computation layer are adjusted, including:

[0031] When the data skewness exceeds the preset skewness threshold, data skewness is determined to exist, and the parallelism of subsequent computation tasks is adjusted.

[0032] Adjusted parallelism The calculation formula is Where V is the coefficient of variation, which serves as an indicator of data skewness. Using the baseline parallelism, the computational load for processing skewed data is distributed by increasing the parallelism.

[0033] Furthermore, a query plan is generated based on this, including data partitioning strategy and cross-layer operator execution order, including:

[0034] When the data skewness is less than the preset skewness threshold, the data partitioning strategy in the query plan adopts the default hash partitioning strategy;

[0035] When the data skewness is greater than the preset skewness threshold, the data partitioning strategy is switched from the hash partitioning strategy to the salted partitioning strategy. In the salted partitioning strategy, the key value that causes data skewness is concatenated with an integer randomly selected from the interval [0, N-1] to form a new key value, and then hash calculation and partitioning are performed, where N is the number of partitions.

[0036] Secondly, the present invention provides a multi-layered storage and query system for petabyte-level unstructured data, comprising the following modules:

[0037] The module retrieves the query statement to be executed and decomposes it into a set of atomic predicates.

[0038] The selection module, for each atomic predicate in the atomic predicate set, if it is not natively supported on a certain pushdown storage layer, it traverses the pre-built rewrite rule base and selects the equivalent atomic predicate combination with the minimum execution cost estimated by combining query history and storage medium performance indicators, as the rewrite result in the storage layer.

[0039] The execution module calculates the pushdown execution cost for each atomic predicate in each pushdownable storage layer, based on the natively supported atomic predicates or the rewritten results. Based on the pushdown execution cost of each atomic predicate in each storage layer and the preset cost threshold, it generates a predicate pushdown decision, which specifies whether each atomic predicate should be executed in the storage layer or the computation layer.

[0040] The generation module, based on the predicate pushdown decision, uses the statistical metadata of the data object to estimate the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed in the storage layer have completed filtering; using the cardinality and data skewness as input, it adjusts the cost model and parallelism of the atomic predicates executed in the computation layer, thereby generating a query plan that includes data partitioning strategy and cross-layer operator execution order, and then executes the query plan.

[0041] Furthermore, select an equivalent atomic predicate combination that minimizes execution cost by combining query history and storage media performance metrics, including:

[0042] Establish and maintain a least recently used cache to store the historical execution times of equivalent atomic predicate combinations;

[0043] When the atomic predicate of the query is hit in the least recently used cache, the historical execution time is used as the historical cost.

[0044] When the queried atomic predicate is not hit in the least recently used cache, the theoretical execution time is calculated as the theoretical cost based on the performance indicators of the storage medium, which are 4K random read IOPS of solid-state drive, 1MB sequential read bandwidth of mechanical hard drive or API requests per second of object storage.

[0045] The execution cost is calculated using a weighted formula: Execution cost = 0.7 × historical cost + 0.3 × theoretical cost.

[0046] Furthermore, the pushdown execution cost is calculated for each atomic predicate in each pushdownable storage layer, including:

[0047] The pushdown execution cost is calculated using a multi-factor cost model. Push down execution cost The calculation formula is:

[0048] ;

[0049] in, , , These are dimensionless weighting coefficients. The number of rows to be processed. The number of CPU instructions processed per line. This refers to the number of instructions executed by the CPU per second. The number of data blocks scanned. The number of I / O operations per second for the storage medium. This represents the size of the intermediate result set after filtering. This refers to network bandwidth.

[0050] The beneficial effects are as follows: This invention utilizes a predicate rewriting mechanism to convert complex predicates that are not natively supported by the storage layer into equivalent executable forms based on query history and optimal media performance costs. This expands the range of pushdown predicates, enabling more thorough data filtering at the data source end, thereby minimizing cross-layer network data transmission. The cost evaluation model of this invention provides a basis for the pushdown decision of each atomic predicate on different storage layers, avoiding the performance trap of blindly pushing down computationally expensive predicates to low-performance storage layers, and achieving optimal allocation of filtering tasks between the computation and storage layers. By predicting the cardinality and data skewness of the intermediate result set after pushdown filtering, the predicate pushdown operation is deeply coupled with the query plan optimization of the upper-layer computing engine. This allows the computing layer to anticipate the characteristics of the data to be processed and plan a more reasonable data partitioning strategy, operator execution order, and parallelism accordingly. This avoids computational hotspots and long-tail problems caused by uneven data distribution, improving the overall query performance and stability of petabyte-level unstructured data in a multi-layer storage architecture. Attached Figure Description

[0051] Figure 1 A flowchart illustrating a multi-layered storage and query method for petabyte-scale unstructured data;

[0052] Figure 2 This is a histogram of the data for column 'A'. Detailed Implementation

[0053] An embodiment of the multi-level storage and query method for petabyte-scale unstructured data provided by this invention:

[0054] like Figure 1 As shown, a multi-layered storage query method for petabyte-scale unstructured data includes the following steps:

[0055] S1: Obtain the query statement to be executed and decompose it into a set of atomic predicates.

[0056] Specifically, the system receives user-submitted SQL-like query statements, such as `SELECT A, B FROM T WHERE A>100 AND startsWith(B, 'prefix')`. A query parser, such as one based on Apache Calcite, parses this SQL query statement into a logical plan or abstract syntax tree. It then traverses the `WHERE` clause in the logical plan or abstract syntax tree, using the logical connectors `AND` or `OR` as delimiters, extracting the smallest indivisible filtering units. In this embodiment, these are the two atomic predicates, `A>100` and `startsWith(B, 'prefix')`, and stores them in a set, which is the atomic predicate set.

[0057] S2. For each atomic predicate in the atomic predicate set, when it is not natively supported on a push-downable storage layer, traverse the preset rewrite rule library to select an equivalent atomic predicate combination that combines the query history and storage medium performance metrics to estimate the minimum execution cost as the rewrite result on the storage layer.

[0058] Specifically, query the capability list of each storage layer: For example, an object storage layer natively supports prefix scanning based on object keys but does not support executing the startsWith function on object content. For the atomic predicate startsWith(B, 'prefix'), it is determined that it is not natively supported on the object storage layer. Access a preset rewrite rule library that stores transformation rules such as <startsWith(str, pre), {str >= pre AND str < increment(pre)}. Subsequently, use the performance metrics of the storage medium (such as the GET request latency and bandwidth of object storage) and the performance data of similar range scans in the query history to estimate the cost of executing the rewritten combined predicate. If there are multiple rewrite possibilities, calculate the cost for each and select the one with the lowest cost as the rewrite result.

[0059] In an optional embodiment, selecting an equivalent atomic predicate combination that combines the query history and storage medium performance metrics to estimate the minimum execution cost includes:

[0060] Establish and maintain a least recently used (LRU) cache to store the historical execution time of equivalent atomic predicate combinations;

[0061] When the queried atomic predicate hits in the least recently used cache, use the historical execution time as the historical cost;

[0062] When the queried atomic predicate does not hit in the least recently used cache, calculate the theoretical execution time as the theoretical cost according to the performance metrics of the storage medium, where the performance metrics are the 4K random read IOPS of a solid-state drive, the 1MB sequential read bandwidth of a mechanical hard drive, or the number of API requests per second of object storage;

[0063] The execution cost is calculated by a weighted formula: Execution cost = 0.7 × historical cost + 0.3 × theoretical cost.

[0064] Specifically, a Least Recently Used (LRU) cache with a capacity of 1000 records is first established and maintained to store equivalent atomic predicate combinations and their most recent execution time. When a queried atomic predicate combination (e.g., column_A > 100) exists in the cache, its historical execution time is directly read, for example, 50ms, and used as the historical cost for this evaluation. If the atomic predicate combination does not exist in the cache, the theoretical cost is calculated based on the storage medium's performance metrics. For example, if the underlying storage is a solid-state drive with 4K random read IOPS of 80,000 and an estimated 2000 data blocks need to be read, the theoretical cost is calculated to be 25ms.

[0065] After obtaining the historical and theoretical costs, a weighted formula is used to calculate the final execution cost. Taking the cache hit scenario above as an example, with a historical cost of 50ms and a theoretical cost of 25ms, the execution cost is 42.5ms. The calculated execution cost is then compared with the costs of other equivalent combinations of atomic predicates, and the optimizer selects the combination with the lowest execution cost. After execution, the actual execution time is updated in the LRU cache as a historical cost reference for the next query.

[0066] S3, based on natively supported atomic predicates or rewritten results, calculates the pushdown execution cost for each atomic predicate in each pushdownable storage layer; based on the pushdown execution cost of each atomic predicate in each storage layer and the preset cost threshold, generates a predicate pushdown decision, which specifies whether each atomic predicate is executed in the storage layer or the computation layer.

[0067] Specifically, all atomic predicates have an executable form on each pushdownable storage layer; an execution cost is computed for each atomic predicate-storage layer pair. The cost model is a weighted function, with input factors including the computational complexity of the atomic predicate itself (e.g., a simple integer comparison has a low CPU overhead coefficient, while a complex regular expression match has a high coefficient); the I / O characteristics of the storage medium (e.g., solid-state drives have significantly better IOPS and latency than hard disk drives); and the impact of data layout (e.g., filtering a column in a columnar storage format such as Parquet is extremely efficient).

[0068] For example, the cost of an atomic predicate A > 100 is calculated as 10 in the solid-state drive (SSD) layer, but might be 150 in the object storage layer due to network overhead. For each atomic predicate, its lowest execution cost across all pushdownable storage layers is compared to a preset cost threshold. This preset cost threshold represents the cost of not pushing down, i.e., the time and resource overhead required to read all relevant data from the storage layer to the compute layer and then filter it; it is primarily determined by network bandwidth and the CPU performance of the compute nodes. If the lowest pushdown cost of an atomic predicate is less than this preset cost threshold, a decision is made to push it down, and it is assigned to the storage layer with the lowest cost for execution. Conversely, if the cost of pushing down is greater than pulling the data to the upper layer for processing, a decision is made to execute it in the compute layer. This ultimately generates a list of explicit execution locations, such as A > 100 executing in the SSD layer and startsWith(B, 'prefix') executing in the compute layer.

[0069] In an optional embodiment, the pushdown execution cost is calculated for each atomic predicate at each pushdownable storage layer, including:

[0070] The pushdown execution cost is calculated using a multi-factor cost model. Push down execution cost The calculation formula is:

[0071] ;

[0072] in, , , These are dimensionless weighting coefficients. The number of rows to be processed. The number of CPU instructions processed per line. This refers to the number of instructions executed by the CPU per second. The number of data blocks scanned. The number of I / O operations per second for the storage medium. This represents the size of the intermediate result set after filtering. This refers to network bandwidth.

[0073] Specifically, the first step is to collect various parameters for calculating the cost of a pushdown: Assuming we want to evaluate the cost of pushing down an atomic predicate `age > 30` to a certain storage node, we need to obtain metadata and performance metrics: number of rows to be processed. For 2 million lines, the number of CPU instructions processed per line The number of instructions per second that the CPU of this storage node can execute is 400. For 1 billion records, the number of data blocks that need to be scanned The number of I / O operations per second for the storage medium is 500. The size of the filtered intermediate result set is 5000. The estimated network bandwidth between the compute layer and the storage layer is 2MB. 50MB / s. Set the weighting coefficient. It is 0.6 0.3 It is 0.1.

[0074] The second step is to calculate the costs for the CPU, I / O, and network components separately: CPU cost is 0.48, I / O cost is 0.03, and network cost is 0.004. The total pushdown execution cost is then obtained by adding the three costs together. That is, 0.514. This value is the representation cost of pushing down the atomic predicate age>30 to this storage node for execution.

[0075] In an optional embodiment, a predicate pushdown decision is generated based on the pushdown execution cost of each atomic predicate in each storage layer and a preset cost threshold. The predicate pushdown decision specifies whether each atomic predicate should be executed in the storage layer or the computation layer, including:

[0076] Compare the pushdown execution costs of each atomic predicate in each storage layer. And the preset cost threshold;

[0077] When the pushdown execution cost When the cost is less than the preset cost threshold, the predicate pushdown decision is to push the atomic predicate down to the storage layer for execution;

[0078] When the pushdown execution cost When the cost is not less than the preset cost threshold, the predicate pushdown decision is executed at the computation layer.

[0079] Specifically, assuming a global preset cost threshold of 0.5, which represents the maximum acceptable cost for the pushdown operation, pushdown evaluation is performed on two different atomic predicates in the same storage layer: the first atomic predicate is "price < 50", and the pushdown execution cost is calculated using a multi-factor cost model. The value is 0.32. 0.32 is compared with the preset cost threshold. Since 0.32 < 0.5, the decision is to push down the atomic predicate "price < 50" to this storage layer for execution.

[0080] Evaluate the second atomic predicate: The second atomic predicate is a complex string matching operation "name LIKE′%pattern%′". Due to its high computational complexity, the calculated pushdown execution cost is... The value is 0.75. This is compared to a preset cost threshold of 0.5. Since 0.75 > 0.5, the decision is made not to execute the atomic predicate in the storage layer, but rather to reserve it for execution in the computationally powerful computing layer. In this way, an execution location decision is generated for each atomic predicate in each possible storage layer.

[0081] S4. Based on the predicate pushdown decision, using the statistical metadata of the data object, estimate the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed in the storage layer complete the filtering; using the cardinality and data skewness as input, adjust the cost model and parallelism of the atomic predicates executed in the computing layer, thereby generating a query plan that includes data partitioning strategy and cross-layer operator execution order, and execute the query plan.

[0082] Specifically, first query the metadata center to obtain column-level statistical information about the data table, such as the data histogram, the number of different values, the maximum and minimum values, etc.

[0083] For the atomic predicate A > 100 in the decision pushdown, analyze the histogram of column A, such as... Figure 2 As shown, the proportion of data with values ​​greater than 100 is estimated, and then multiplied by the total number of rows to obtain the estimated cardinality of the intermediate result set after filtering, i.e., the number of rows.

[0084] Further analysis is conducted on the numerical distribution of other key columns (such as the join key C in subsequent JOIN operations) within the data that satisfies A > 100. Data skewness is represented by comparing the distribution of the join key in the filtered subset with the global distribution; for example, by calculating the variance of the distribution or the ratio of the largest partition size to the average partition size.

[0085] The estimated lower cardinality will update the cost model of subsequent operators in the computation layer. For example, an operator that was originally expected to use a large-scale shuffle join may be changed to a broadcast join due to the smaller input data volume. The estimated high data skewness will directly affect the data partitioning strategy. For example, salting techniques can be used on key values ​​that are expected to cause data skew, that is, appending a random number to the key value to distribute them across more partitions and avoid overloading a single computational task.

[0086] Finally, the number of parallel tasks executing this part of the data is adjusted according to the skewness. A complete execution plan that integrates predicate pushdown position, computational layer operator selection optimized based on prediction results, partitioning strategy and parallelism is generated and submitted to a distributed execution engine (such as Spark) for scheduling and execution.

[0087] In an optional embodiment, the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed at the storage layer complete filtering are estimated using statistical metadata of the data objects, including:

[0088] The statistical metadata is an equal-depth histogram of the data columns;

[0089] By querying the value range of the atomic predicate, the covered bucket interval is located in the equal depth histogram. The frequency of the fully covered buckets is accumulated, and the frequency of the partially covered buckets is calculated by linear interpolation according to the coverage ratio. The calculation results of all buckets are summed to obtain the cardinality.

[0090] Based on the distribution of the estimated base value across different data partitions, the ratio of the standard deviation to the mean is calculated to obtain the coefficient of variation, which is then used as the data skewness.

[0091] Specifically, suppose a histogram with equal depth for the "order_amount" column has 10 buckets, each representing 10,000 rows of data. For the query predicate "order_amount≥150 AND order_amount<450": this range completely covers the two buckets with values ​​from 200 to 300 and from 300 to 400, with an estimated number of rows of 2 × 10,000 = 20,000. This range also partially covers the bucket with values ​​from 100 to 200, with a coverage ratio of 0.5, and an estimated number of rows of 5,000; simultaneously, this range also partially covers the bucket with values ​​from 400 to 500, with a coverage ratio of 0.5, and an estimated number of rows of 5,000. The total estimated number of rows is 20,000 + 5,000 + 5,000 = 30,000.

[0092] Performing the cardinality estimation above on each of the four data partitions yielded the following results: Partition 1 has 25,000 rows, Partition 2 has 2,000 rows, Partition 3 has 1,800 rows, and Partition 4 has 1,200 rows. The mean is calculated to be 7,500 rows. The standard deviation is calculated to be approximately 11,051. Dividing the standard deviation (11,051) by the mean (7,500) gives the coefficient of variation (V) of approximately 1.47. This 1.47 represents the data skewness, reflecting the degree of uneven distribution of data across different partitions.

[0093] In an optional embodiment, the cardinality and data skewness are used as input to adjust the cost model and parallelism of the atomic predicates executed at the computation layer, including:

[0094] When the data skewness exceeds the preset skewness threshold, data skewness is determined to exist, and the parallelism of subsequent computation tasks is adjusted.

[0095] Adjusted parallelism The calculation formula is Where V is the coefficient of variation, which serves as an indicator of data skewness. Using the baseline parallelism, the computational load for processing skewed data is distributed by increasing the parallelism.

[0096] Specifically, based on the above calculations, a data skewness indicator for the intermediate result set regarding the "user_id" field is obtained, namely, the coefficient of variation V is 1.47, and the preset skewness threshold is set to 1.5. Comparing this value with the preset skewness threshold of 1.5, since 1.47 < 1.5, it is determined that subsequent calculations around "user_id" do not have a serious data skew problem.

[0097] In another example, if the coefficient of variation V is 2.0, since 2.0 > 1.5, the subsequent computation task around "user_id" is determined to have a severe data skew problem. In this case, the originally planned baseline parallelism for the computation task... The initial value is 200. Upon detecting data skew, the parallelism adjustment mechanism is activated. According to the formula, the new parallelism is calculated to be 600. The query plan executor will allocate 600 concurrent tasks to specific computation stages instead of the original 200, thereby distributing the computational pressure that would have been concentrated on a few tasks more widely and addressing the performance bottleneck caused by data skew.

[0098] In an optional embodiment, a query plan is generated accordingly, including a data partitioning strategy and the execution order of cross-layer operators, comprising:

[0099] When the data skewness is less than the preset skewness threshold, the data partitioning strategy in the query plan adopts the default hash partitioning strategy;

[0100] When the data skewness is greater than the preset skewness threshold, the data partitioning strategy is switched from the hash partitioning strategy to the salted partitioning strategy. In the salted partitioning strategy, the key value that causes data skewness is concatenated with an integer randomly selected from the interval [0, N-1] to form a new key value, and then hash calculation and partitioning are performed, where N is the number of partitions.

[0101] Specifically, with the preset skew threshold still set at 1.5, for a query aggregating by "product_id", the calculated quantitative indicator V of data skewness is 2.5. A value greater than 1.5 indicates data skewness. When generating the query plan, the query optimizer decides not to use the default hash partitioning, but instead switches to a salted partitioning strategy to address the issue of a small number of "product_id" records having excessively large amounts of data.

[0102] Suppose we need to redistribute data across N=10 partitions. For a large key-value pair "product_A", we don't directly hash it. Instead, for each "product_A" record, we randomly select a number from the integer range 0 to 9 (for example, 3), and concatenate "product_A" with this number to form a new key "product_A_3". We then hash the new salted key "product_A_3" and distribute the data to the corresponding partitions based on the hash result. The data that originally belonged to "product_A" is now randomly distributed across all 10 partitions, achieving load balancing.

[0103] An embodiment of the multi-layered storage and query system for petabyte-level unstructured data provided by this invention:

[0104] A multi-tiered storage and query system for petabyte-scale unstructured data includes the following modules:

[0105] The module retrieves the query statement to be executed and decomposes it into a set of atomic predicates.

[0106] The selection module, for each atomic predicate in the atomic predicate set, if it is not natively supported on a certain pushdown storage layer, it traverses the pre-built rewrite rule base and selects the equivalent atomic predicate combination with the minimum execution cost estimated by combining query history and storage medium performance indicators, as the rewrite result in the storage layer.

[0107] The execution module calculates the pushdown execution cost for each atomic predicate in each pushdownable storage layer, based on the natively supported atomic predicates or the rewritten results. Based on the pushdown execution cost of each atomic predicate in each storage layer and the preset cost threshold, it generates a predicate pushdown decision, which specifies whether each atomic predicate should be executed in the storage layer or the computation layer.

[0108] The generation module, based on the predicate pushdown decision, uses the statistical metadata of the data object to estimate the cardinality and data skewness of the intermediate result set generated after the atomic predicates executed in the storage layer have completed filtering; using the cardinality and data skewness as input, it adjusts the cost model and parallelism of the atomic predicates executed in the computation layer, thereby generating a query plan that includes data partitioning strategy and cross-layer operator execution order, and then executes the query plan.

[0109] In an optional embodiment, an equivalent atomic predicate combination that minimizes execution cost by combining query history and storage media performance metrics is selected, including:

[0110] Establish and maintain a least recently used cache to store the historical execution times of equivalent atomic predicate combinations;

[0111] When the atomic predicate of the query is hit in the least recently used cache, the historical execution time is used as the historical cost.

[0112] When the queried atomic predicate is not hit in the least recently used cache, the theoretical execution time is calculated as the theoretical cost based on the performance indicators of the storage medium, which are 4K random read IOPS of solid-state drive, 1MB sequential read bandwidth of mechanical hard drive or API requests per second of object storage.

[0113] The execution cost is calculated using a weighted formula: Execution cost = 0.7 × historical cost + 0.3 × theoretical cost.

[0114] In an optional embodiment, the pushdown execution cost is calculated for each atomic predicate at each pushdownable storage layer, including:

[0115] The pushdown execution cost is calculated using a multi-factor cost model. Push down execution cost The calculation formula is:

[0116] ;

[0117] in, , , These are dimensionless weighting coefficients. The number of rows to be processed. The number of CPU instructions processed per line. This refers to the number of instructions executed by the CPU per second. The number of data blocks scanned. The number of I / O operations per second for the storage medium. This represents the size of the intermediate result set after filtering. This refers to network bandwidth.

[0118] In addition, in the description of this specification, "multiple" means at least two, such as two, three or more, etc., unless otherwise expressly and specifically defined.

Claims

1. A multi-tiered storage query method for PB-scale unstructured data, characterized in that, The method comprises the following steps: S1, obtaining a query statement to be executed and decomposing the query statement into a set of atomic predicates: receiving a SQL-like query statement submitted by a user, parsing the SQL-like query statement into a logical plan or an abstract syntax tree by a query parser, traversing a WHERE clause in the logical plan or the abstract syntax tree, taking a logical connection word AND or OR as a delimiter, extracting two atomic predicates that cannot be further divided as minimum filtering units, and storing the two atomic predicates in a set, which is the set of atomic predicates; S2, for each atomic predicate in the set of atomic predicates, when the atomic predicate is not natively supported on a certain pushable storage layer, traversing a preset rewriting rule library to select an equivalent atomic predicate combination with the minimum execution cost estimated by combining query history and storage medium performance indicators, as a rewriting result on the storage layer; S3, based on the atomic predicates natively supported or the rewriting result, calculating a push execution cost for each atomic predicate on each pushable storage layer; based on the push execution cost of each atomic predicate on each storage layer and a preset cost threshold, generating a predicate push decision that specifies the execution of each atomic predicate in the storage layer or the computing layer; S4, according to the predicate push decision, estimating the cardinality and data skew of an intermediate result set generated after the atomic predicate executed on the storage layer completes filtering by using statistical metadata of data objects; Taking the cardinality and the data skew as inputs, adjusting the cost model and parallelism of the atomic predicate executed on the computing layer, and generating a query plan including a data partition strategy and an execution order of cross-layer operators according to the query plan, and executing the query plan; The statistical metadata of the data objects includes an equi-depth histogram of a data column. The histogram is located by locating a covered bucket interval in the equi-depth histogram according to a value range in the query atomic predicate, the frequency of a completely covered bucket is accumulated, the frequency of a partially covered bucket is calculated by linear interpolation according to a coverage ratio, and the sum of the calculation results of all the buckets is obtained to obtain the cardinality; and the coefficient of variation is obtained by calculating the ratio of the standard deviation to the mean according to the distribution of the estimated cardinality on different data partitions, and the coefficient of variation is taken as the data skew.

2. The method for multi-layer storage query of PB-level unstructured data according to claim 1, characterized in that, The equivalent atomic predicate combination with the minimum execution cost estimated by combining query history and storage medium performance indicators comprises: establishing and maintaining a least recently used cache to store historical execution time of the equivalent atomic predicate combination; when the atomic predicate of the query hits in the least recently used cache, the historical execution time is taken as the historical cost; when the atomic predicate of the query does not hit in the least recently used cache, a theoretical execution time is calculated as a theoretical cost according to a performance indicator of the storage medium, and the performance indicator is a 4K random read IOPS of a solid state disk, a 1MB sequential read bandwidth of a mechanical hard disk, or a number of API requests per second of object storage; the execution cost is calculated by a weighted formula: execution cost = 0.7 × historical cost + 0.3 × theoretical cost.

3. The method for multi-tier storage query of PB-scale unstructured data according to claim 1, characterized in that, The push execution cost for each atomic predicate on each pushable storage layer is calculated, comprising: A multi-factor cost model is used to calculate the pushdown execution cost The formula for calculating the pushdown execution cost is: ； wherein, , , is a dimensionless weight coefficient, is the number of rows to be processed, is the number of CPU instructions for single row processing, is the number of instructions executed by the CPU per second, is the number of data blocks scanned, is the number of IO operations per second of the storage medium, is the size of the intermediate result set after filtering, is the network bandwidth.

4. The method for multi-tier storage query of PB-scale unstructured data according to claim 3, characterized in that, The predicate pushdown decision is generated based on the pushdown execution cost of each atomic predicate at each storage layer and a preset cost threshold, and the predicate pushdown decision specifies execution of each atomic predicate in a storage layer or a computing layer, including: Comparing the pushdown execution cost of each atomic predicate at each storage tier with a preset cost threshold Current execution cost When the cost is less than the preset cost threshold, the predicate pushdown decision is to push the atomic predicate down to the storage layer for execution; When the push execution cost When the push execution cost is not less than the preset cost threshold, the predicate push decision is executed at the calculation layer.

5. The method for multi-tier storage query of PB-scale unstructured data according to claim 1, characterized in that, The cardinality and data skewness are taken as inputs to adjust the cost model and parallelism of the atomic predicate executed in the computing layer, including: When the data skewness is greater than a preset skew threshold, it is determined that there is data skew, and the parallelism of a subsequent computing task is adjusted; Adjusted parallelism The calculation formula is Where V is the coefficient of variation, which is an index of data skewness, is the reference parallelism, and the parallelism is increased to disperse the calculation load of processing skewed data.

6. The multi-tiered storage query method for PB-scale unstructured data according to claim 5, wherein, Accordingly, a query plan including a data partitioning strategy and a cross-layer operator execution sequence is generated, including: When the data skewness is less than the preset skew threshold, the data partitioning strategy in the query plan adopts a default hash partitioning strategy; When the data skewness is greater than the preset skew threshold, the data partitioning strategy is switched from the hash partitioning strategy to a salted partitioning strategy, in which a key value causing data skew is concatenated with a random integer selected from the interval [0, N-1] to form a new key value, and then hash calculation and partitioning are performed, where N is the number of partitions.

7. A multi-tiered storage query system for PB-scale unstructured data, characterized in that, The following modules are included: The obtaining module obtains a to-be-executed query statement and decomposes it into an atomic predicate set: a SQL-like query statement submitted by a user is received, a query parser parses the SQL-like query statement into a logical plan or an abstract syntax tree, a WHERE clause in the logical plan or the abstract syntax tree is traversed, a logical conjunction word AND or OR is taken as a delimiter, two atomic predicates that cannot be further divided are extracted as minimum filtering units, and the two atomic predicates are stored in a set, which is the atomic predicate set; The selection module, for each atomic predicate in the atomic predicate set, when not natively supported on a certain pushable storage layer, traverses a preset rewrite rule library, selects an equivalent atomic predicate combination with the lowest execution cost estimated by combining query history and storage medium performance indicators, and takes the equivalent atomic predicate combination as a rewrite result on the storage layer; The execution module calculates a pushdown execution cost for each atomic predicate at each pushable storage layer based on the natively supported atomic predicate or the rewrite result; and generates a predicate pushdown decision for each atomic predicate to specify execution in a storage layer or a computing layer based on the pushdown execution cost of each atomic predicate at each storage layer and a preset cost threshold; The generation module estimates the cardinality and data skewness of an intermediate result set generated after filtering of an atomic predicate executed in a storage layer using statistical metadata of a data object based on the predicate pushdown decision; The cardinality and data skewness are taken as inputs to adjust the cost model and parallelism of the atomic predicate executed in the computing layer, and a query plan including a data partitioning strategy and a cross-layer operator execution sequence is generated and executed according to the query plan; The statistical metadata of the data object is used to estimate the cardinality and data skewness of an intermediate result set generated after filtering of an atomic predicate executed in a storage layer, including: the statistical metadata is an equi-depth histogram of a data column; The covered bucket interval is located in the isobathic histogram by querying the value range in the atomic predicate, the frequency is accumulated for the completely covered bucket, the linear interpolation calculation is performed on the partially covered bucket according to the coverage proportion, the calculation results of all the buckets are summed to obtain the cardinality; the ratio of the standard deviation to the mean is calculated to obtain the coefficient of variation according to the distribution of the estimated cardinality on different data partitions, and the coefficient of variation is used as the data skewness.

8. The multi-tiered storage query system for PB-scale unstructured data of claim 7, wherein, An equivalent atomic predicate combination with the minimum execution cost is selected by combining the query history and the storage medium performance index, including: A least recently used cache is established and maintained to store the historical execution time of the equivalent atomic predicate combination; When the atomic predicate of the query hits in the least recently used cache, the historical execution time is used as the historical cost; When the atomic predicate of the query does not hit in the least recently used cache, the theoretical execution time is calculated as the theoretical cost according to the performance index of the storage medium, and the performance index is the 4K random read IOPS of the solid state disk, the 1MB sequential read bandwidth of the mechanical hard disk or the API request number per second of the object storage; The execution cost is calculated by a weighted formula: execution cost = 0.7 * historical cost + 0.3 * theoretical cost.

9. The multi-tiered storage query system for PB-scale unstructured data of claim 7, wherein, The down-push execution cost is calculated for each atomic predicate at each down-pushable storage layer, including: A multi-factor cost model is used to calculate the pushdown execution cost The formula for calculating the pushdown execution cost is: ； Wherein, , , is a dimensionless weight coefficient, is the number of rows to be processed, is the number of CPU instructions for single row processing, is the number of instructions executed by the CPU per second, is the number of scanned data blocks, is the number of IO operations per second of the storage medium, is the size of the intermediate result set after filtering, is the network bandwidth.

Images