Data processing method, electronic device, and storage medium

Abstract

A data processing method, an electronic device, and a storage medium. The method comprises: receiving a query request, wherein the query request comprises a target data table participating in a join operation; on the basis of the query request, executing a logical plan, wherein the execution of the logical plan comprises: constructing a data filtering condition corresponding to column statistical information, the column statistical information being information obtained by means of compiling statistics in advance on a column value range of columns included in the target data table; and executing a physical plan corresponding to the logical plan, wherein the execution of the physical plan comprises: performing partition pruning and / or dynamic filtering on the target data table on the basis of the data filtering condition, so as to generate a processed target data table, and executing the join operation on the processed target data table, so as to obtain an execution result.

Description

Data processing method, electronic device and storage medium

[0001] Cross-references to related publications

[0002] This disclosure claims priority to the Chinese patent application filed with the State Intellectual Property Office on February 26, 2024, with application number CN202410208754.X and invention name “Data processing method, electronic device and storage medium”. The entire contents of this application are incorporated by reference into this disclosure. Technical Field

[0003] The embodiments of the present disclosure relate to, but are not limited to, the field of data management technology, and particularly to a data processing method, an electronic device, and a storage medium. Background Art

[0004] Database queries involve a large number of table join operations. Table join operations are used to query data from multiple (at least two) tables based on the relationship between columns in these tables. Join conditions between multiple tables can be specified to link data in multiple tables together.

[0005] In related technologies, database query execution plans are typically divided into two phases: the logical plan execution phase and the physical plan execution phase. The logical plan execution phase involves query semantic analysis and optimization, generating an abstract, logical query plan without involving specific execution operations. The physical plan execution phase converts the logical plan into specific, executable steps.

[0006] When performing a table join, two table scans are typically performed during physical plan execution: one to construct the filter conditions and the other to perform the join based on the data in the scanned tables. This table scan typically takes a long time, resulting in poor query performance. Summary of the Invention

[0007] The present disclosure provides a data processing method, an electronic device, and a storage medium for improving data processing efficiency and query performance.

[0008] An embodiment of the present disclosure provides a data processing method, comprising: receiving a query request, the query request including: a target data table participating in a join operation; executing a logical plan according to the query request, the execution of the logical plan including: constructing a data filtering condition corresponding to column statistical information; wherein the column statistical information is information obtained by pre-statistical analysis of column value ranges of columns included in the target data table; executing a physical plan corresponding to the logical plan, the execution of the physical plan including: partitioning and / or dynamically filtering the target data table according to the data filtering condition to generate a processed target data table, and performing a join operation on the processed target data table to obtain an execution result.

[0009] An embodiment of the present disclosure provides an electronic device, comprising: one or more processors; a memory on which one or more programs are stored. When the one or more programs are executed by the one or more processors, the one or more processors implement any one of the data processing methods in the embodiments of the present disclosure.

[0010] An embodiment of the present disclosure provides a storage medium storing a computer program. When the computer program is executed by a processor, any one of the data processing methods in the embodiment of the present disclosure is implemented.

[0011] 、 BRIEF DESCRIPTION OF THE DRAWINGS

[0012] FIG1 shows a schematic diagram of a data query scenario according to an embodiment of the present disclosure.

[0013] FIG2 shows a flow chart of a data processing method according to an embodiment of the present disclosure.

[0014] FIG3 shows a schematic diagram of a modified logical execution plan tree according to an embodiment of the present disclosure.

[0015] FIG4 shows a flowchart of data processing according to an exemplary embodiment of the present disclosure.

[0016] FIG. 5 a shows a physical plan generated without using dynamic data filtering for optimization according to an embodiment of the present disclosure.

[0017] FIG. 5 b shows a physical plan generated by optimizing using dynamic data filtering in the related art.

[0018] FIG5 c shows a schematic diagram of a physical plan after optimization by dynamic data filtering according to an embodiment of the present disclosure.

[0019] FIG6 shows a block diagram of a data processing device according to an embodiment of the present disclosure.

[0020] FIG. 7 illustrates a block diagram of a data processing apparatus according to an exemplary embodiment of the present disclosure.

[0021] FIG8 shows a block diagram of an electronic device according to an embodiment of the present disclosure. DETAILED DESCRIPTION

[0022] To make the objectives, technical solutions and advantages of the present disclosure more clearly understood, the embodiments of the present disclosure will be described in detail below with reference to the accompanying drawings. It should be noted that, unless there is a conflict, the embodiments and features in the embodiments of the present disclosure can be combined with each other in any manner.

[0023] In actual scenarios, relational databases use query statements in Structured Query Language (SQL), which makes it very convenient to operate relational databases. Relational databases include, for example, SQL Server databases, MySQL databases, Oracle databases, DB2 databases, and Sybase databases. Queries performed by SQL statements usually involve a large number of table join operations, which may cause a large amount of irrelevant data to be read, resulting in poor query performance. Filtering the table data that is read and processed, for example, minimizing or eliminating the reading and processing of irrelevant data can improve query performance. If the optimizer's optimization operation can use certain specific strategies to eliminate some row data before the table join operation of the SQL query, it will be beneficial to improve query performance.

[0024] For example, the common syntax format of the SQL statement for table join operation is:

[0025] SELECT <query list>

[0026] FROM[connection type]

[0027] JOIN ON <join condition>

[0028] WHERE<query condition>

[0029] The join type refers to the type of join operation, and may include, for example, any of the following: Left Join, Right Join, Inner Join, Full Join, and Cross Join. If the SQL statement does not contain a join type before JOIN, an inner join may be assumed by default.

[0030] For example, the SQL statement may be: SELECT * FROM sales JOIN items ON sales.item_id=items.id WHERE items.price>300and items.price<500;

[0031] In this SQL statement, the left table of the JOIN (corresponding to table name 1, "sales," or simply "sales") is a large table (the fact table), and the right table (corresponding to table name 2, "items," or simply "items") is a smaller table (the dimension table). The table join operation is used to join the columns sales.item_id and items.id. Obviously, scanning the smaller table is much faster than scanning the larger table. Based on the query search conditions on the right table (items.price>300 and items.price<500), a filter condition is generated at runtime. Pruning and / or filtering the left table can reduce the amount of data scanned.

[0032] In related technologies, query optimization can be performed through dynamic partition pruning (DPP) and runtime dynamic filtering (RuntimeFilter). Specifically, DPP optimization includes: constructing a filter expression using non-join key columns in the right table to perform dynamic filtering before scanning, obtaining the partitions that need to be scanned for the fact table, and directly performing partition-level pruning on the large table to avoid scanning useless data; DPP requires the partition columns of the large table to participate in table joins. RuntimeFilter includes: dynamically generating filter conditions at runtime, that is, parsing "JOIN", "ON", and "clause" to determine the filter expression when the query is running, and broadcasting the filter expression to the scanning node that is reading the left table, thereby reducing the amount of data scanned and avoiding unnecessary I / O and network transmission. In an SQL statement, the sentence corresponding to each keyword (such as WHERE, SELECT, FROM, WHERE, ORDER, GROUP BY, LIMIT) is a clause, that is, Clause. Parsing "join on clause" means: parsing the join type of join, parsing the join condition corresponding to on, and parsing the query condition corresponding to the WHERE clause.

[0033] The related art method uses a method that generates an SQL statement during the physical plan execution process to scan the small table and obtain a data set that meets the filtering conditions. For example, select MIN(id), MAX(id) from sales to collect the minimum and maximum values ​​of the id column; or select distinct id from sales group by id to collect all values ​​of the id column as the IN predicate of the filtering condition (used to match values ​​to an unstructured series of items). A waiting time is set, and only during the physical plan execution phase can the data set of the right table that meets the filtering conditions be obtained. If the cluster is busy and there are many resource-intensive or time-consuming queries on the cluster, the waiting time needs to be increased, which will reduce the concurrency of the job. In addition, generating filtering conditions during the physical plan phase may miss the logical plan optimization. In addition, the related implementation mechanism of RuntimeFilter has the problem that the small table needs to be scanned and filtered twice: the first scan is used to construct the filtering conditions, and the second scan is used to scan the data of the small table for table connection.

[0034] As can be seen, related technologies all generate filter conditions by obtaining a dataset from the right table that satisfies the filter criteria during the physical plan execution phase. Constructing filter conditions on large datasets takes a long time, increasing query time and computational overhead, creating a new bottleneck. Reducing the time and space complexity of filter condition construction, improving filter processing efficiency, and reducing or avoiding invalid data scanning are urgent issues that need to be addressed.

[0035] The embodiments of the present disclosure provide a data processing method that can reduce the time required to construct filter conditions and improve query performance.

[0036] Figure 1 shows a schematic diagram of a data query scenario provided by an embodiment of the present disclosure. As shown in Figure 1, the system architecture of the application environment provided by an embodiment of the present disclosure is logically divided into a storage layer 101 and a computing layer 102. The storage layer 101 and the computing layer 102 are connected via a network 103.

[0037] In Figure 1, the storage layer 101 provides data storage capabilities, and the underlying layer uses formats such as Parquet for storage. Parquet is a columnar storage format designed for big data processing and has many advantages and features, such as saving storage space, high-speed access, and easy format conversion. The components of the Parquet file storage format include metadata, data type, row count, and file format. It should be understood that the underlying layer can also use other formats for storage, such as the columnar storage format ORC (OptimizedRC) and RCFile (Record Columnar). The specific format can be determined according to actual needs and is not specifically limited in the embodiments of this disclosure.

[0038] As an example, the storage layer 101 may be cloud storage, local storage, distributed storage, or the like.

[0039] In FIG1 , the computing layer 102 performs data management and access based on the storage layer 101 . The computing layer 102 includes a big data engine 1021 , a data warehouse 1022 , and a table format module 1023 .

[0040] Among them, the big data engine 1021 includes but is not limited to the following as shown in Figure 1: computing engine spark, computing engine flink, and computing engine presto; the data warehouse 1022 is used to uniformly manage the data of various heterogeneous data source databases, and complete the transformation of the source data organization form according to a reasonable modeling method to better support front-end visual analysis; the tabular module 1023 is used to define the data storage format, and the tabular module 1023 is an optional deployment.

[0041] For example, the tabular setup enables database transactions that meet the principles of atomicity (A), consistency (C), isolation (I), and durability (D), known as ACID transactions, and enhances data management capabilities. Figure 1 schematically illustrates open-source systems such as the Iceberg system, a big data table management library; the Hudi system, a large analytical dataset storage system; and DeltaLake, a unified data management system, all part of TableFormat.

[0042] In actual deployment, according to different application scenarios, computing engine deployment may include but is not limited to the following scenarios: a separate big data operating environment (not based on the TableFormat system), a separate big data operating environment (based on the TableFormat system), a separate data warehouse operating environment (based on the TableFormat system), a separate data warehouse operating environment (not based on the TableFormat system), a lake-warehouse integrated operating environment (big data and data warehouse engines based on TableFormat are deployed together), etc. The above operating environments are not limited to physical machine environments, cloud environments, etc.

[0043] 1 , the network 103 may include various connection types, such as wired, wireless communication links, or fiber optic cables, among others.

[0044] In practical application scenarios, the data methods and apparatuses of the embodiments of the present disclosure may be executed by a query engine, which may be deployed in a database server to implement parsing and executing query statements. The database server may include an independent physical server, a server cluster consisting of multiple servers, or a cloud server capable of cloud computing.

[0045] Accordingly, the data processing device provided in the embodiments of the present disclosure may be disposed in a database server. The data processing method provided in the embodiments of the present disclosure may also be executed by a server or server cluster that is different from the database server and capable of communicating with the database server. Accordingly, the data processing device provided in the embodiments of the present disclosure may also be disposed in a server or server cluster that is different from the database server and capable of communicating with the database server.

[0046] In a first aspect, an embodiment of the present disclosure provides a data processing method. FIG2 is a flow chart of a data processing method provided by an embodiment of the present disclosure. Referring to FIG2 , the data processing method may include the following steps.

[0047] S210: Receive a query request, where the query request includes: a target data table participating in the connection operation.

[0048] For example, a query request can be in the form of a query statement, which includes the target data tables involved in the Join operation. The Join operation is used to query data from two or more tables based on the relationships between their columns. To obtain complete results, it is necessary to specify join conditions between multiple tables, thereby linking the data from multiple tables. The two or more tables in the Join operation are the target data tables.

[0049] In the disclosed embodiments, during a Join operation, the table to the right of the Join is referred to as the right table or the first table, and the table to the left of the Join is referred to as the left table or the second table. In this embodiment, there are no restrictions on the types of the first table (right table) and the second table (left table), nor on their sizes. These can be adaptively adjusted and selected based on specific application scenarios to improve data query applicability.

[0050] S220, executing a logical plan according to the query request, wherein the execution of the logical plan includes: constructing a data filtering condition corresponding to the column statistical information; wherein the column statistical information is information obtained by pre-statisticing the column value range of the columns included in the target data table.

[0051] In this step, an abstract, logical query plan is generated during the logical plan execution phase, but no specific execution operations are involved. For example, the logical plan can be used to generate a data structure based on the syntax tree. Based on the syntax tree content, the tables, fields, and expressions involved in the SQL statement need to be parsed and their validity determined.

[0052] In some scenarios, technologies such as RuntimeFilter and DPP use the MIN to MAX (a set of values ​​greater than or equal to a predetermined minimum value and less than or equal to a predetermined maximum value) of column values ​​as statistical information to filter out second-table data that is less than the minimum value and greater than the maximum value when reading data. However, the range of MIN to MAX of column values ​​may be very large, and there is no fine-grained description of the distribution of data, resulting in poor filtering effects. Related technologies use the method of generating IN predicate filter conditions, such as id IN(1,2,3,4,.....), which means that the value range of the identifier (id) is within the IN set "1,2,3,4,.....", but the filtering will fail when the value list in the IN set exceeds a certain number.

[0053] S230, during the execution of the physical plan corresponding to the logical plan, partition pruning and / or dynamic filtering are performed on the target data table according to the data filtering conditions to generate a processed target data table, and a join operation is performed on the processed target data table to obtain an execution result.

[0054] During this step, the logical plan can be converted into specific, executable operational steps during the physical plan execution phase. A physical plan is an ordered set of data operations, parsed from the logical plan, and is the final executable data structure. According to the data processing method of the disclosed embodiment, during the execution of the physical plan, the data in the target data table can be filtered according to the data filtering conditions established during the logical plan execution phase. A join operation can then be performed using the filtered target data table to obtain the join operation execution result.

[0055] According to this method, data filtering conditions can be generated based on pre-built column statistical information in the logical planning stage of the data query request, and data filtering can be directly performed based on the generated data filtering conditions in the physical planning stage to obtain the target data table after filtering. In the related art, filtering conditions are constructed through table scanning during the execution of the physical plan, and table scanning takes a long time. However, the embodiment of the present disclosure constructs filtering conditions based on column statistical information in the logical planning stage, and there is no need for table scanning, so filtering conditions can be constructed more quickly.

[0056] In addition, in the embodiment of the present disclosure, since there is no need to perform table scanning in the physical planning stage, the time for constructing the filter conditions is reduced, thereby reducing the time complexity of constructing the filter conditions; and, in the related art, the filter conditions are constructed through table scanning. The table scanning not only needs to traverse the columns included in the target data table, but may also involve selecting appropriate indexes, etc., while the embodiment of the present disclosure constructs the filter conditions based on statistical information of the column value ranges of the columns included in the target data table. In comparison, the method of the embodiment of the present disclosure involves fewer variables, and the storage space temporarily occupied during operation will be smaller, which is conducive to reducing the space complexity of constructing the filter conditions.

[0057] Furthermore, in the method of the embodiment of the present disclosure, during the execution of the physical plan, after the target data table is partitioned and / or dynamically filtered according to the data filtering conditions, the data in the generated processed target data table has been greatly reduced, thereby greatly reducing the amount of data involved in the table connection operation; and, in the method of the embodiment of the present disclosure, column statistical information is obtained based on the column value range of the columns contained in the target data table. There is no need to perform a table scan when constructing the filtering conditions in the logical plan stage based on the column statistical information. Compared with the prior art that performs a table scan in the physical execution stage to construct the filtering conditions, the number of scans of the data table is reduced, thereby improving data processing efficiency and query performance.

[0058] In the embodiment of the present disclosure, compared with the related art, in which the first table is scanned in the physical plan execution phase to obtain a data set that meets the "where" query condition to construct a filter condition for the second table, if the amount of data in the first table is large, it takes a long time to construct the filter condition, which will affect the performance optimization effect. In the embodiment of the present disclosure, before performing a table scan on the second table, the filter condition can be constructed based on the pre-acquired column statistics of the first table rather than the exact value of the join key, and before actually performing the Join operation in the physical plan execution phase, these statistics can be used to construct a filter condition to filter the data of the second table, thereby reducing or eliminating the impact of the first table scan on performance optimization in the query plan execution phase (corresponding to the physical plan execution phase), and reducing the query cost; and, in the physical plan execution phase, the data of the second table can be filtered or partitioned instead of a full table scan, thereby greatly reducing the number of scans and improving query efficiency.

[0059] In some embodiments, the target data table includes at least two data tables, the data in each data table is stored through multiple underlying data files, and the column statistical information includes: a set of column value ranges of each underlying data file of each data table; before receiving the query request in step S210, the data processing method also includes the following steps.

[0060] S11 , respectively obtaining each underlying data file of each data table, wherein each underlying data file includes a plurality of row groups in the corresponding data table.

[0061] In this step, the underlying data file DataFile can be read through the Application Programming Interface (API). Each data table can have at least one underlying data file, that is, at least one underlying data file is used to store the data in the corresponding data table. Row Groups are a data partitioning method. Specifically, for the underlying data file, data can first be partitioned into row groups by row, and then the data within the row group can be stored in columns.

[0062] S12, obtaining a column value range of each target column of each row group according to a maximum column value and a minimum column value of at least one target column of each row group, wherein the at least one target column includes at least a join key column participating in a join operation.

[0063] For example, the footer of the underlying data file contains a data mapping table attribute information segment (Footer), which stores metadata and statistical information about the underlying data file. This statistical information may include at least one of the following: the number of columns, their relative positions, and data type information. In this step, the underlying data file (DataFile) is read through the API to obtain its Footer information, thereby obtaining metadata and statistical information.

[0064] In some embodiments, the target columns include at least the join key columns involved in the join operation. For example, in the SQL statement example above, the column named "item_id" in the sales table and the column named "id" in the items table. In other embodiments, the target columns may include all columns in each data table.

[0065] In step S12, the target columns are first determined. For example, in the second table (sales), the target columns may include a column named "price" and a column named "item_id." In the first table (items), the target columns may include a column named "id." Then, for each row group in each underlying data file of each data table, the maximum and minimum values ​​of the target column are collected to obtain the column value range for the target column of the corresponding row group. In the disclosed embodiment, not all columns of a table must be generated into a range set, but at least the columns of the join key in each table participating in the join operation are included.

[0066] S13 , merging the column value ranges of each target column of each row group according to the corresponding columns to obtain a column value range set of each underlying data file of the corresponding data table.

[0067] In this step, the target column's column value range set is the union of multiple column value ranges. For example, for an underlying data file of the second table, such as file 1, when merging column value ranges, continuous column value ranges (all connected intervals) in file 1 are merged, and empty intervals (intervals with empty values) are ignored, thereby generating several sets of non-contiguous intervals in file 1 as the column value range set for the target column of file 1 of the second table.

[0068] The method for generating the column value range set of each underlying data file of the first table is basically the same as the method for generating the column value range set of each underlying data file of the second table, and will not be repeated here.

[0069] In some embodiments, the column value range sets of each underlying data file of the second table and the column value range sets of each underlying data file of the first table can be stored respectively as column range sets. The range set is a set of N non-empty, discontinuous intervals, where N is a positive integer, where the interval is used to define the range boundary of a continuous span, and the data type of the interval value includes but is not limited to integer, floating point, etc.

[0070] Through the above steps S11-S13, file-level column statistics of the target data table can be generated, thereby refining the granularity of the statistics to the file level, providing a data basis for subsequent dynamic data filtering based on the file-level column statistics.

[0071] For ease of understanding, the following describes the process of generating file-level column statistics using a specific example. For the file-level column statistics of the second table (sales table), the generation steps may include: S21, reading the underlying data file DataFile of the second table through the API, and reading the Footer information of the file; S22, obtaining the column maximum value and column minimum value of all columns of each row group one by one, and performing the following processing on each column in the currently obtained row group: S221, combining the column maximum value and column minimum value into a closed interval, for example, which can be expressed as: [column minimum value..column maximum value]; S222, merging the closed interval into the file range set corresponding to the column. When merging closed intervals, all connected intervals will be merged, and empty intervals will be ignored, thereby generating several non-continuous interval sets. The maximum number of intervals in the interval set can be set by the system; through the above steps S221 and S222, continue to perform corresponding processing on each column in the next row group until all row groups are processed.

[0072] As an example, Table 1 shows file-level column statistics information of the second table of the exemplary embodiment.

[0073] Table 1 File-level column statistics for the second table

[0074] Table 1

[0075] In some embodiments, the sales table in the database includes at least two underlying data files, such as file 1 and file 2. File 1 and file 2 may belong to the same partition or different partitions.

[0076] Assume that a partition has two underlying data files: File 1 and File 2. As shown in Table 1, the price column in File 1 uses three intervals of price∈[1..200]∪[300..500]∪[500..700], covering the join key ID value interval of [1..300]∪[350..600]∪[950..1200]. In File 2, the price column uses three intervals of price∈[1000..1200]∪[1300..1500]∪[1500..1700], covering the join key ID value interval of [100..300]∪[310..600]∪[610..800].

[0077] In some embodiments, each data table includes multiple partition lists, and each underlying data file is obtained from all underlying data files in its corresponding partition list; the column value range set includes multiple non-empty column value intervals, and the multiple non-empty column value intervals are discontinuous intervals.

[0078] In this embodiment, the statistical column includes at least a join key column, and the range set of the statistical column is a set of N non-empty, discontinuous intervals, where N is a positive integer; the interval can be used to define the range boundary of a continuous span, and the data type of the interval value includes but is not limited to integer, floating point, etc.

[0079] In some embodiments, the multiple underlying data files are underlying data files stored in multiple data partitions, and the column statistical information includes: a column value range set for each data partition of each data table; before receiving the query request in step S210, the data processing method further includes: respectively obtaining a column value range set corresponding to each underlying data file contained in each data partition; for each data partition, merging the column value range sets corresponding to each underlying data file contained in the corresponding data partition to obtain a column value range set for each data partition of the corresponding data table.

[0080] In this embodiment, a list of data partitions of each data table and a list of underlying data files under each partition can be obtained. For each data partition, after generating a set of column value ranges corresponding to each underlying data file (file-level column statistics), the file-level column statistics of all underlying data files under the partition are merged to obtain the partition-level column statistics of the corresponding data table, thereby refining the granularity of the statistical information to the data partition level, providing a data basis for subsequent dynamic partition pruning based on the partition-level column statistics.

[0081] To facilitate understanding, the following describes the process of generating partition-level column statistics using a specific example. For the partition-level column statistics of the second table (sales), the generation steps may include: obtaining a list of partitions in the sales table; for each partition, obtaining the file-level column value ranges for all files in the partition; and merging the file-level column value ranges for all files in the partition; for each column, merging the column value ranges for all files in the partition. During the merging process, N sets of non-contiguous intervals are generated based on all connected intervals, where N is a positive integer.

[0082] As an example, Table 2 shows partition-level column statistics for the sales table of the exemplary embodiment.

[0083] Table 2 Partition-level column statistics for the second table

[0084] As can be seen from Table 2, the embodiment of the present disclosure can use a range set of [1..200], [300..500], [500..700], [1000..1200], [1300..1500], and [1500..1700], a total of 6 discontinuous intervals to describe the data distribution of the price column of partition 1 of the sales table. The interval between the discontinuous intervals is the data skip zone. The data skip zone has no column value distribution and can be partition pruned. Compared with the related art that uses a set of ranges: [1..1700] to describe the data interval of the partition, the column value range set in the embodiment of the present disclosure is small and easy to maintain, and can characterize the data distribution in a fine-grained manner, refining the data distribution to the partition level to better skip data partitions without column value distribution.

[0085] In some embodiments, to ensure the accuracy of column statistical information, when the data in the first table and / or the data in the second table are changed, the column statistical information of the changed data table may be updated.

[0086] In the disclosed embodiment, the column value range set of each underlying data file of each data table and the column value range set of each data partition of each data table can be generated synchronously after the underlying data files of the table are written, or can be generated through an asynchronous mechanism. The specific settings can be customized according to actual needs, and this embodiment does not limit them.

[0087] In the disclosed embodiment, after column statistics are generated, the column statistics can be stored. The amount of data stored in the column statistics is small and the storage space occupied is limited. The column statistics can be stored in a local hard disk, a solid state disk (SSD), or other high-speed storage media; or a key value (KV) cache structure can be used to cache the column statistics in memory. When the optimizer needs to access the column statistics, it first queries the local cache. If there is no corresponding statistics in the local cache, it reads from other media.

[0088] In some embodiments, the query request also includes a query condition; the step of constructing a data filtering condition corresponding to the column statistical information in step S220 may specifically include the following steps.

[0089] S31, extracting a query predicate from the query request, where the query predicate is used to indicate a query condition.

[0090] In this step, when a query request is received, the query predicate can be extracted and the column information involved in the Join operation can be obtained.

[0091] In some embodiments, the query SQL request includes the query search condition "where", and the search condition usually includes a search of several tables. In related technologies, the query optimizer will decompose the search condition according to the table to which it belongs to form a single-table condition, and each corresponding single-table condition is a predicate. In SQL, a predicate is a logical expression whose calculation result conforms to three-valued logic and has three truth values: true (TRUE), false (FALSE) or unknown (UNKNOWN). The predicate will filter the table according to the logical expression, and its filtering effect directly affects the query efficiency.

[0092] For example, the query statement may be parsed to generate an abstract syntax tree corresponding to the query statement, and then a Join node that meets the conditions may be found in the abstract syntax tree, and the query predicate may be extracted.

[0093] S32: Construct a column value range set of the join key column corresponding to the query condition based on the column statistical information.

[0094] The join key columns are columns in the join condition. For example, information such as the names of the join key columns in the first table and the second table that participate in the join operation, and whether they are partition columns, is obtained.

[0095] Let's continue with the SQL statement: SELECT * FROM sales JOIN items ON sales.item_id=items.id WHERE items.price>300and items.price<500 as an example. The query predicate is "items.price>300and items.price<500", and the columns involved in the join operation are items.id and sales.item_id.

[0096] In actual application scenarios, the query statement can specifically be an SQL statement; the abstract syntax tree generation and query condition extraction in the above embodiment can refer to the specific processing method of abstract syntax tree generation and the specific processing method of query condition extraction in related technologies, which will not be repeated here.

[0097] In some embodiments, the query condition includes a query column and a corresponding query value; the above-mentioned step S32 may specifically include: determining a value range of the query value; obtaining a target column value range set from the column statistical information, where the target column value range set and the value range have an intersection; obtaining a column value range set of the join key column corresponding to the first file as the column value range set of the join key column corresponding to the query condition, wherein the first file is the underlying data file corresponding to the target column value range set.

[0098] For example, a predicate logic expression can be calculated based on column statistics to generate a list of files for which the predicate logic expression evaluates to TRUE. For example, the predicate logic expression items.price>300and items.price<500 queries the price column in the second table. The value of the logic expression can be converted to the interval (300, 500). In the statistics, the column value range set of the price column in the second table is accessed, and the column value range that intersects with (300, 500) is found to be [300…500]. The file to which [300…500] belongs is {f1}, where f1 represents file 1.

[0099] Table 3 shows a set of column value ranges of the join key columns of the second table corresponding to the query conditions.

[0100] Table 3 Column value range set of the join key column of the second table corresponding to the query condition

[0101] Table 3 shows that when accessing the column value range set of the price column of the second table in the column statistics information, the list of files that intersect with (300, 500) includes file 1. The column value range set of the join key column corresponding to file 1 is the column value range set corresponding to item_id, which is "[1..300]∪[350..600]∪[950..1200]".

[0102] In this step, for files that satisfy the predicate logic expression, the column value ranges for the join key columns are merged. This merged column value range set is then used as the join key range set. This range set serves as the join key value range set that satisfies the SQL search condition. For example, if the range set for the join key column id in file f1 of the second table is [1..300][350..600][950..1200], then [1..300][350..600][950..1200] is used as the filtered data set for the join key id.

[0103] It can be seen from the above processing steps that the column value range set of the join key column of the data table that meets the SQL search condition is a subset of the value range of the join key column value.

[0104] S33: Use the column value range set of the connection key column as the corresponding data filtering condition.

[0105] In this embodiment, the query condition can be determined according to the query predicate in the query request, and a column value range set of the join key column corresponding to the query condition can be constructed according to the column statistics information, and the column value range set can be used as the corresponding data filtering condition.

[0106] In the embodiment of the present disclosure, the SQL execution plan may include: generation of a logical plan, analysis of the logical plan, optimization of the logical plan, generation of a physical plan, optimization of the physical plan, and generation of execution code; wherein, generation of the logical plan, analysis of the logical plan, and optimization of the logical plan all belong to the logical plan stage; generation of the physical plan, optimization of the physical plan, and generation of execution code belong to the physical plan stage.

[0107] During the query optimization phase, the query predicate in the query request can be converted into a calculation operation on column statistics, a list of files that meet the query predicate is identified, a set of column value ranges of the join key columns that meet the predicate is merged, and a new predicate is extracted. The predicate can identify the filtering conditions that meet the predicate in the join relationship for dynamic partition pruning and / or dynamic filtering. During the execution of dynamic partition pruning and / or dynamic filtering, fine-grained value ranges can be used to skip partitions and / or files and / or row groups that do not meet the filtering conditions.

[0108] In some embodiments, the logical plan includes a logical plan tree, the logical plan tree includes a filter node, and the filter condition corresponding to the filter node includes: a column value range set of a join key column.

[0109] The above-mentioned step S33 may specifically include: obtaining a logical execution plan tree generated in the logical plan stage, the logical execution plan tree including: a node corresponding to the connection operation; adding a filter node in the logical execution plan tree, the filter condition corresponding to the filter node including: a column value range set of the connection key column; the above-mentioned step of filtering the data in the target data table using the column value range set of the connection key column to obtain the filtered target data table may specifically include: in the process of executing the logical execution plan tree, at the filter node, filtering the data in the target data table according to the column value range set of the connection key column.

[0110] For example, if the filtering conditions are met, the logical execution plan tree (logical plan) can be modified to add a Filter expression node. The parent node of the Filter node is the Join node, the child node of the Filter node is the original Join second table data source Relation node, and the connection key range set is packaged as the filtering condition of the filter.

[0111] The method of the embodiment of the present disclosure can obtain a column value range set of a join key column that meets the SQL query constraint condition in the logical planning stage, and can perform calculation (processing) cost estimation in a more fine-grained manner.

[0112] In some embodiments, the above-mentioned filter node is a node added to the logical execution plan tree in the following manner: S41, based on the type of connection operation and the query conditions in the query request, the performance consumption of the connection operation is evaluated to obtain a performance consumption evaluation result; S42, based on the filter conditions, the performance optimization evaluation result of the connection operation is determined; S43, using the performance consumption evaluation result and the performance optimization evaluation result, the optimization benefit evaluation result is determined; S44, when the optimization benefit evaluation result meets the predetermined conditions, the filter node is added to the logical execution plan tree.

[0113] For example, the applicability of DPP and / or RuntimeFilter optimization is checked based on the join operation type and predicate selectivity. The cost of DPP and / or RuntimeFilter pruning is estimated to be sufficient. If the threshold is not met, predicate pushdown is disabled. Predicate pushdown (PPD) involves executing the where predicate logic in SQL statements as early as possible to reduce the amount of data processed downstream.

[0114] In this embodiment, the DPP and / or RuntimeFilter benefit evaluation method can be called. During the SQL logic optimization phase, the DPP and / or RuntimeFilter benefits can be estimated based on the join operation type and predicate selectivity. If the optimization benefit evaluation result meets all predetermined conditions, the logical plan can be modified to construct a Filter node, encapsulating the column range set of the join key columns as the filter conditions of the filter. During the SQL logic optimization phase, other query optimization rules can be applied, including but not limited to predicate pushdown.

[0115] Specifically, based on the join operation type and query conditions, the performance cost of executing the join operation is evaluated, including computational resources and time consumption. Based on the filter conditions, the performance optimization effect of applying a filter node (such as DPP or RuntimeFilter) on the join operation is evaluated, including reductions in data scan volume and computational complexity. The performance optimization evaluation results are compared with the performance cost evaluation results to calculate the net benefit of the optimization. If the performance improvement after optimization (such as reduced query time and reduced resource consumption) exceeds the additional overhead of introducing the filter node (such as the cost of calculating the filter conditions), the optimization benefit evaluation result is considered to meet the predetermined conditions. If the optimization benefit evaluation result meets all predetermined conditions, a filter node is added to the logical execution plan tree, and the join key range set is packaged as the filter condition of the filter. Therefore, the optimization benefit evaluation result comprehensively considers the performance optimization effect and the additional overhead of introducing the filter node, ultimately determining whether to add the filter node to the logical execution plan.

[0116] FIG3 shows a schematic diagram of a modified logical execution plan tree according to an embodiment of the present disclosure.

[0117] In Figure 3, taking the Spark engine as an example, in the logical execution plan tree generated by the user SQL, based on the data filtering conditions items.price>300 and items.price<500, the range set of the price column of the second table is accessed in the column statistics information, and the list of files that intersect with (300,500) is found, including file 1. The column value range set of the join key column corresponding to file 1 is the column value range set corresponding to item_id, which is "[1..300]∪[350..600]∪[950..1200]". A new Filter expression node is inserted on the left side of Join to filter the join key column of the second table.

[0118] In the embodiment of the present disclosure, the filter can be packaged in an IN expression and can be extended to support the Rangeset range set type. For example, the Filter expression node is the following IN expression:

[0119] Filter(In(item_id, Seq(Rangeset([1..300][350..600][950..1200]))), sales).

[0120] In related technologies, when using IN predicates for dynamic filtering, if the length of the IN value list exceeds a threshold, the filtering is invalid. However, through the embodiments of the present disclosure, the IN list can be converted into a range set to avoid the IN predicate from being invalid in subsequent filtering.

[0121] In the disclosed embodiments, filter conditions can be constructed by reading column statistics in real time during the logical planning phase, eliminating the need to scan the first table and set a wait time during the physical planning phase. This method can significantly accelerate many queries in computing engines such as Spark.

[0122] It should be noted that, in the embodiment of the present disclosure, other query optimization rules, including but not limited to predicate pushdown, may continue to be applied to the logical execution plan tree to continue the optimization.

[0123] In some embodiments, the target data table includes a first table and a second table; the step of partitioning the target data table according to the data filtering condition in the above step S230 may specifically include: S51, respectively obtaining the column value range set of each data partition of the second table from the column statistical information; S52, querying the column value range set of the connection key column from the column value range set of the obtained data partition; S53, if the column value range set of the connection key column is not queried, determining the currently obtained data partition as an invalid partition, skipping reading the data in the invalid partition of the second table, and partition pruning the second table.

[0124] For example, assume that the price column of partition P1 of the second table has a minimum value of 700 and a maximum value of 2000. If the column value range set of item_id in the column statistics of partition P1 is [700..900][1300..2000], then the statistical range set of P1 is queried using the interval [1..300][350..600][950..1200] of the column value filter condition. If the query result is not found, the partition can be determined to be invalid and does not need to be read. However, if the interval [700..2000] formed by the minimum value 700 and the maximum value 2000 is directly used for judgment, all partitions need to be scanned, which increases invalid operations and reduces query efficiency.

[0125] In this embodiment, partition-level pruning is performed on the second table (large table, fact table) to obtain the partitions that need to be scanned in the second table, thereby avoiding scanning useless data, reducing the amount of data that needs to be accessed, and improving query efficiency.

[0126] In some embodiments, in the above-mentioned step S230, the step of dynamically filtering the target data table according to the data filtering conditions may specifically include: S61, respectively obtaining the column value range set of each underlying data file of the second table from the column statistical information; S62, querying the column value range set of the connection key column from the column value range set of the obtained underlying data file; S63, if the column value range set of the connection key column is not queried, skipping reading the currently obtained underlying data file to perform data filtering on the target data table.

[0127] For example, assume that the price column of file f1 in the second table has a minimum value of 700 and a maximum value of 3000. If the column range set for item_id in the column statistics of file f1 is [700..900][1600..3000], then a query using the filter condition interval [1..300][350..600][950..1200] against the statistical range set of f1 will result in a search result that does not exist. Therefore, file f1 is determined to be invalid and does not need to be read. However, if the minimum and maximum values ​​are directly used within the interval [1..30000], the file must be scanned, which increases invalid operations and reduces query efficiency.

[0128] In this embodiment, data filtering is performed on the second table (large table, fact table) to obtain the underlying data files that need to be scanned for the second table, thereby avoiding scanning useless data, reducing the amount of data that needs to be accessed, and improving query efficiency.

[0129] In an embodiment of the present disclosure, for example, in a physical plan, the Filter expression node Filter(In(item_id, Seq(Rangeset([1..300][350..600][950..1200]))), sales) can be converted into a physical plan node FilterExec. If the column field of the second table participating in the Join operation is a partition column (a column field in the underlying data file of a data partition of the second table), partition pruning can be performed, or partition pruning can be performed first and then dynamic data filtering. If the column field of the second table participating in the Join operation is a non-partition column, dynamic data filtering can be performed.

[0130] In the disclosed embodiments, when executing a physical execution plan, dynamic partition pruning is performed based on a join key value range set filter and / or dynamic data filtering is performed based on column statistics. During the actual execution of the physical execution plan, partition pruning can be performed within a physical operator (DynamicPartitionPruneFilterExec operator).

[0131] In some embodiments, after step S62, the following steps may also be included: S71, when the column value range set of the connection key column is queried, read the currently acquired underlying data file, and obtain the column value range of each row group from the underlying data file, and the column value range includes: the minimum value and maximum value of each column in the corresponding row group; S72, using the column value range set of the connection key column, query the values ​​of the columns in the row group in the currently acquired underlying data file; S73, when the column value range set of the connection key column is not queried, skip reading the currently acquired row group to filter the row groups in the target data table.

[0132] For example, if data in the underlying data file f1 needs to be read, when obtaining row group-level statistical information, the row group records can be filtered according to the filter condition interval [1..300][350..600][950..1200], and only data that meets the filter condition is read.

[0133] In this embodiment, by filtering the number of row groups, the amount of data read can be further reduced and the data query efficiency can be improved.

[0134] In the embodiment of the present disclosure, if the column field of the second table participating in the Join operation is a partition column (a column field in the underlying data file of a data partition of the second table), partition pruning can be performed; or, partition pruning can be performed first and then dynamic data filtering; or, partition pruning, dynamic data filtering and row group data filtering can be performed in sequence; compared with the related art, the use of coarse-grained statistical information of MIN to MAX for data filtering can reduce the amount of data read and improve data query efficiency while effectively filtering data.

[0135] Figure 4 shows a flow chart of data processing according to an exemplary embodiment of the present disclosure. As shown in Figure 4, in some embodiments, the data processing method includes the following steps.

[0136] S401, receiving a query request, extracting query predicates and obtaining column information involved in a join operation.

[0137] In this step, information such as the column name of the join key column of the first table participating in the Join operation, the column name of the join key column of the second table participating in the Join operation, and whether it is a partition column is obtained.

[0138] S402 , calculating a predicate logic expression based on column statistical information to generate a value range set of a join key that satisfies the SQL search condition constraint.

[0139] In this step, a predicate logic expression can be calculated based on column statistics to generate a list of files whose predicate logic expression evaluates to TRUE. For files that satisfy the predicate logic expression, the column value range sets of the join key columns are merged, and then the merged column range set of the join key columns is used as the join key range set. This range set serves as the value range set of the join key that satisfies the SQL search condition constraints.

[0140] S403: In the SQL logic optimization phase, the logic plan is modified by adding filter nodes according to the type of join operation and predicate selectivity.

[0141] In this step, during the SQL logic optimization phase, DPP and / or RuntimeFilter benefits are estimated based on the join operation type and predicate selectivity. If the optimization benefit evaluation results meet all predetermined conditions, the logical plan is modified and a Filter node is constructed, wrapping the join key range set as the filter condition of the filter.

[0142] S404: Generate and optimize a physical execution plan.

[0143] In this step, in the physical plan, you can add the Filter expression node in the logical plan stage:

[0144] Filter(In(item_id,Seq(Rangeset([1..300][350..600][950..1200]))),sales) will be converted into the physical plan node FilterExec.

[0145] S405 , executing the physical execution plan, and performing dynamic partition pruning and dynamic data filtering according to the value range set filter of the connection key and the column statistical information.

[0146] Specifically, first, obtain the Filter filter condition, where the Filter filter condition can be expressed as the following IN filter condition:

[0147] In(item_id, Seq(Rangeset([1..300][350..600][950..1200]))), sales).

[0148] Secondly, an interval query operation can be performed on the filter condition and the partition-level statistical information of the second table (the column value range set of each data partition) to determine whether the data corresponding to the filter condition is in a certain data partition.

[0149] Specifically, you can perform interval queries on the filter conditions and the statistical range set in the file-level statistics of the second table (the column value range set of each underlying data file) to determine whether the data corresponding to the filter conditions exists in a specific underlying data file. If not, reading the underlying data file is skipped. After scanning the underlying data file, the filter conditions of the range set data are used to filter row groups or row records, reducing the amount of data scanned. For columnar file formats such as Parquet / ORC, which contain MIN (minimum column value) and MAX (maximum column value) statistics, you can perform interval queries on the filter conditions and the MIN and MAX values ​​of the columns of the row groups or records in the data file, forming a [MIN..MAX] interval, to determine data validity and skip scanning invalid data, thereby reducing the amount of data actually scanned.

[0150] During the actual execution of the physical execution plan, the column value range set of the second table can be used in the physical operator to perform data filtering, etc. In some scenarios, such as in a distributed system, operations such as Join can use re-operators (such as Shuffle operators) to ensure that the same data is distributed to the same machine or instance (Instance), and then perform Join operations, which directly affects the efficiency of SQL runtime. It can be seen from the description of the above embodiment that after partition pruning of the data partitions of the second table sales, the actual scanning of the table partitions will be reduced. After file-level dynamic data filtering, files that do not meet the filtering conditions are skipped. After the filtering conditions are pushed down to the storage layer, row groups or row records are filtered, further reducing the amount of data read, greatly reducing the amount of operation data when performing re-operations in the physical stage and the amount of data participating in Join, saving the entire cluster IO, network and CPU resources.

[0151] S406, obtain the execution result and return it.

[0152] In this step, the generated physical plan may be executed by the execution engine and result set data may be returned.

[0153] Through the above steps, the computing engine can determine the validity of the data during the query process based on the column statistics. According to the data processing method of the embodiment of the present disclosure, column statistics can be generated in advance and stored in the form of a column range set; in response to receiving a query request, the query predicate is extracted and the column information participating in the Join operation is obtained, the predicate logic expression is calculated based on the column statistics, and a column value range set of the connection key column that meets the SQL search condition constraint is generated; in the SQL logic optimization stage, the DPP and / or RuntimeFilter benefits are estimated based on the type of the connection operation and the predicate selectivity; if the optimization benefit evaluation result meets all predetermined conditions, the logical plan is modified, a Filter node is constructed, and the column value range set of the connection key column is packaged as the filter condition of the filter, and other query optimization rules can continue to be applied, including but not limited to predicate pushdown, etc.; in the physical execution plan execution stage, dynamic partition pruning and / or dynamic data filtering are performed based on the column value range set filter of the connection key column, and then the execution result is obtained and returned.

[0154] Figure 5a shows a physical plan generated without using dynamic data filtering for optimization according to an embodiment of the present disclosure; Figure 5b shows a physical plan generated with using dynamic data filtering for optimization according to a related art; and Figure 5c shows a schematic diagram of a physical plan after using dynamic data filtering for optimization according to an embodiment of the present disclosure.

[0155] Compared to the physical plan shown in Figure 5a, in Figure 5b, a RuntimeFilter node can be added during the execution of the physical plan to perform data filtering. Specifically, as shown in Figure 5a, a SQL statement can be generated during the execution of the physical plan to scan the first table (sales table) to obtain a data set that meets the filtering conditions. For example, the SQL statement "select MIN(id), MAX(id) from sales" can be used to collect the maximum and minimum values ​​of the id column; or the SQL statement "select distinct id from sales group by id" can be used to collect all values ​​of the id column as the IN predicate of the filtering condition and set a wait time. If the cluster is busy and there are many resource-intensive or time-consuming queries on the cluster, these operations will increase the wait time and reduce the job concurrency.

[0156] As shown in Figure 5c, filtering conditions can be constructed by reading column statistics during the logical planning phase. Therefore, data can be filtered directly based on the generated data filtering conditions during the physical planning phase, avoiding the time-consuming process of scanning the data table during the physical planning phase. This allows for faster construction of filtering conditions and improves query optimization.

[0157] According to the method of the disclosed embodiment, during the logical planning phase of a data query request, data filtering conditions can be generated based on pre-built column statistics. During the physical planning phase, data filtering can be performed directly based on the generated data filtering conditions to obtain a filtered target data table. In related art, filtering conditions are constructed through table scans during physical plan execution, which takes a long time. However, the disclosed embodiment constructs filtering conditions based on column statistics during the logical planning phase, eliminating the need for table scans and thus enabling faster construction of filtering conditions.

[0158] In addition, in the embodiment of the present disclosure, since there is no need to perform table scanning in the physical planning stage, the time for constructing the filter conditions is reduced, thereby reducing the time complexity of constructing the filter conditions; and, in the related art, the filter conditions are constructed through table scanning. The table scanning not only needs to traverse the columns included in the target data table, but may also involve selecting appropriate indexes, etc., while the embodiment of the present disclosure constructs the filter conditions based on statistical information of the column value ranges of the columns included in the target data table. In comparison, the method of the embodiment of the present disclosure involves fewer variables, and the storage space temporarily occupied during operation will be smaller, which is conducive to reducing the space complexity of constructing the filter conditions.

[0159] Furthermore, in the method of the embodiment of the present disclosure, during the execution of the physical plan, after the target data table is partitioned and / or dynamically filtered according to the data filtering conditions, the data in the generated processed target data table has been greatly reduced, thereby greatly reducing the amount of data involved in the table connection operation; and, in the method of the embodiment of the present disclosure, column statistical information is obtained based on the column value range of the columns contained in the target data table. There is no need to perform a table scan when constructing the filtering conditions in the logical plan stage based on the column statistical information. Compared with the prior art that performs a table scan in the physical execution stage to construct the filtering conditions, the number of scans of the data table is reduced, thereby improving data processing efficiency and query performance.

[0160] It is understood that the above-mentioned various method embodiments mentioned in this disclosure can be combined with each other to form combined embodiments without violating the principle logic. Due to space limitations, this disclosure will not go into details. It is understood by those skilled in the art that in the above-mentioned methods of specific implementation, the specific execution order of each step should be determined by its function and possible internal logic.

[0161] In addition, the present disclosure also provides a data processing device, an electronic device, and a computer-readable storage medium, all of which can be used to implement any data processing method provided by the present disclosure. The corresponding technical solutions and descriptions are referred to the corresponding records in the method section and will not be repeated here.

[0162] In a second aspect, an embodiment of the present disclosure provides a data processing device.

[0163] FIG6 is a block diagram of a data processing device provided by an embodiment of the present disclosure. Referring to FIG6 , an embodiment of the present disclosure provides a data processing device, and the data processing device 600 may include the following modules.

[0164] The receiving module 610 is configured to receive a query request, wherein the query request includes: a target data table involved in the connection operation;

[0165] Determining module 620 is configured to execute a logical plan according to the query request. Execution of the logical plan includes: constructing a data filtering condition corresponding to column statistics information; wherein the column statistics information is information obtained by pre-calculating the column value range of the columns included in the target data table;

[0166] The execution module 630 is configured to partition and / or dynamically filter the target data table according to data filtering conditions during the execution of the physical plan corresponding to the logical plan, generate a processed target data table, and perform a join operation on the processed target data table to obtain an execution result.

[0167] In some embodiments, the target data table includes at least two data tables, and the data in each data table is stored through multiple underlying data files. The column statistical information includes: a column value range set of each underlying data file of each data table; the data processing device 600 also includes: a file-level range set acquisition module, which is constructed to respectively obtain each underlying data file of each data table, and each underlying data file includes multiple row groups in the corresponding data table; according to the column maximum value and column minimum value of at least one target column of each row group, the column value range of each target column of each row group is obtained, wherein at least one target column includes at least: a connection key column participating in the connection operation; the column value range of each target column of each row group is merged according to the column value range of the corresponding column to obtain the column value range set of each underlying data file of the corresponding data table.

[0168] In some embodiments, the multiple underlying data files are underlying data files stored in multiple data partitions, and the column statistical information includes: a column value range set for each data partition of each data table; the data processing device 600 also includes: a partition-level range set acquisition module, which is constructed to: before receiving a query request, respectively obtain the column value range set corresponding to each underlying data file contained in each data partition; for each data partition, merge the column value range set corresponding to each underlying data file contained in the corresponding data partition to obtain the column value range set for each data partition of the corresponding data table.

[0169] In some embodiments, the query request also includes query conditions; when constructing the data filtering conditions corresponding to the column statistical information, the determination module 620 is specifically constructed as follows: extracting the query predicate from the query request, the query predicate is used to indicate the query conditions; constructing a column value range set of the join key column corresponding to the query conditions based on the column statistical information; and using the column value range set of the join key column as the corresponding data filtering conditions.

[0170] In some embodiments, the query condition includes a query column and a corresponding query value; when the determination module 620 constructs a column value range set of a join key column corresponding to the query condition based on the column statistical information, it is specifically constructed to: determine the value range of the query value; obtain a target column value range set from the column statistical information, where the target column value range set and the value range have an intersection; obtain the column value range set of the join key column corresponding to the first file as the column value range set of the join key column corresponding to the query condition, wherein the first file is the underlying data file corresponding to the target column value range set.

[0171] In some embodiments, the logical plan includes a logical plan tree, the logical plan tree includes a filter node, and the filter condition corresponding to the filter node includes: a column value range set of a join key column.

[0172] In some embodiments, the data processing device 600 includes: a node adding module, which is constructed to evaluate the performance consumption of the connection operation according to the type of connection operation and the query conditions in the query request to obtain a performance consumption evaluation result; determine the performance optimization evaluation result of the connection operation according to the filtering conditions; use the performance consumption evaluation result and the performance optimization evaluation result to determine the optimization benefit evaluation result; and add a filtering node in the logical execution plan tree when the optimization benefit evaluation result meets the predetermined conditions.

[0173] In some embodiments, the target data table includes a first table and a second table, and the join operation is used to join all row records in the second table with the row records that meet the conditions in the first table; when the execution module 630 partitions the target data table according to the data filtering conditions, it is specifically constructed to: obtain the column value range set of each data partition of the second table from the column statistical information; query the column value range set of the connection key column from the column value range set of the obtained data partition; if the column value range set of the connection key column is not queried, determine the currently obtained data partition as an invalid partition, skip reading the data in the invalid partition of the second table, and partition prune the second table.

[0174] In some embodiments, when the execution module 630 dynamically filters the target data table according to the data filtering conditions, it is specifically constructed as follows: from the column statistical information, the column value range set of each underlying data file of the second table is obtained respectively; from the column value range set of the obtained underlying data file, the column value range set of the connection key column is queried; if the column value range set of the connection key column is not queried, the reading of the currently obtained underlying data file is skipped to dynamically filter the target data table.

[0175] In some embodiments, the execution module 630 is further constructed to: after querying the column value range set of the connection key column from the column value range set of the acquired underlying data file, if the column value range set of the connection key column is queried, read the currently acquired underlying data file and obtain the column value range of each row group from the underlying data file, the column value range including: the minimum value and maximum value of each column in the corresponding row group; use the column value range set of the connection key column to query the values ​​of the columns in the row group in the currently acquired underlying data file; if the column value range set of the connection key column is not queried, skip reading the currently acquired row group to filter the row groups in the target data table.

[0176] According to the data processing device of the embodiment of the present disclosure, data filtering conditions can be generated according to pre-built column statistical information in the logical planning stage of the data query request, and data can be directly filtered according to the generated data filtering conditions in the physical planning stage to obtain the target data table after filtering. In the related art, the filtering conditions are constructed through table scanning during the execution of the physical plan, and the table scanning takes a long time. However, the embodiment of the present disclosure constructs the filtering conditions based on the column statistical information in the logical planning stage, and there is no need to perform table scanning, so the filtering conditions can be constructed more quickly.

[0177] In the embodiment of the present disclosure, since there is no need to perform table scanning in the physical planning stage, the time for constructing the filter conditions is reduced, thereby reducing the time complexity of constructing the filter conditions; and, in the related art, the filter conditions are constructed through table scanning. The table scanning not only needs to traverse the columns included in the target data table, but may also involve selecting appropriate indexes, etc., while the filter conditions are constructed in the embodiment of the present disclosure based on statistical information of the column value ranges of the columns included in the target data table. In comparison, the method of the embodiment of the present disclosure involves fewer variables, and the storage space temporarily occupied during operation will be smaller, which is conducive to reducing the space complexity of constructing the filter conditions.

[0178] According to the method of the embodiment of the present disclosure, during the execution of the physical plan, after the target data table is partitioned and / or dynamically filtered according to the data filtering conditions, the data in the generated processed target data table has been greatly reduced, thereby greatly reducing the amount of data participating in the table connection operation; and, the method of the embodiment of the present disclosure is to obtain column statistical information based on the column value range of the columns contained in the target data table. It is not necessary to perform a table scan when constructing the filtering conditions in the logical plan stage based on the column statistical information. Compared with the prior art that performs a table scan in the physical execution stage to construct the filtering conditions, the number of scans of the data table is reduced, thereby improving data processing efficiency and query performance.

[0179] FIG7 shows a block diagram of a data processing device according to an exemplary embodiment of the present disclosure. In some embodiments, as shown in FIG7 , the data processing device 700 includes:

[0180] The first generating unit 710 is configured to generate column statistics information and store the column statistics information in the form of a column range set.

[0181] Among them, column statistical information is information obtained by pre-statisticing the column value range of the columns contained in the first table and the column value range of the columns contained in the second table; the column value range can be represented as a set of non-empty, discontinuous intervals, which are used to define the range boundaries of continuous spans. The data types of interval values ​​include but are not limited to integers, floating-point types, etc.

[0182] The second generating unit 720 is configured to construct a data filtering condition corresponding to the column statistical information during the logical plan execution phase.

[0183] The processing unit 730 is configured to perform partition pruning and / or dynamic filtering on the target data table according to the data filtering condition during the execution of the physical plan.

[0184] The execution unit 740 is configured to execute the physical plan and return the result.

[0185] Each module in the above-mentioned data processing device can be implemented in whole or in part through software, hardware, or a combination thereof. Each module can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the corresponding operations of each module.

[0186] It should be understood that the present invention is not limited to the specific configurations and processes described in the above embodiments and illustrated in the figures. For the sake of convenience and brevity, detailed descriptions of known methods are omitted here. The specific working processes of the systems, modules, and units described above can be referred to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0187] In practical applications, the query optimization of the execution engine can be applied to multi-table join scenarios, including but not limited to dynamic partition pruning, dynamic file filtering and other scenarios. For big data execution engines (such as spark, hive, flink, presto, etc.), the method of the present invention is used to improve query efficiency during the query process; in the process of querying data warehouse engines (such as Greeplum, Doris, etc.), the method of the present invention is used to improve query efficiency; in the process of querying database engines (such as Postgresql, etc.), the method of the present invention is used to improve query efficiency; under the lake-warehouse integrated architecture, the big data execution engines based on TableFormat (such as spark, hive, flink, presto, etc.) use the method of the present invention to improve query efficiency during the query process; under the lake-warehouse integrated architecture, the data warehouse engines based on TableFormat (such as Greeplum, Doris, etc.) use the method of the present invention to improve query efficiency during the query process; and the cloud-native data warehouse engines (SelectDB Cloud, etc.) under the cloud-native architecture use the method of the disclosed embodiment to improve query efficiency during the query process.

[0188] In a third aspect, an embodiment of the present disclosure further provides an electronic device.

[0189] 8 , the electronic device includes: at least one processor 801; at least one memory 802; and one or more I / O interfaces 803; wherein the memory 802 stores one or more computer programs that can be executed by the at least one processor 801, and the one or more computer programs are executed by the at least one processor 801 so that the at least one processor 801 can perform the above-mentioned data processing method.

[0190] Among them, the processor is a device with data processing capabilities, including but not limited to the central processing unit (CPU); the memory is a device with data storage capabilities, including but not limited to random access memory (RAM, more specifically such as SDRAM, DDR, etc.), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory (FLASH); the I / O interface (read-write interface) can realize information exchange between the memory and the processor, including but not limited to the data bus (Bus), etc.

[0191] The present disclosure also provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program implements the above-mentioned data processing method when executed by the processor / processing core. The computer-readable storage medium may be a volatile or non-volatile computer-readable storage medium.

[0192] Those skilled in the art will appreciate that all or some of the steps, systems, and functional modules / units in the apparatus disclosed above may be implemented as software, firmware, hardware, or a suitable combination thereof.

[0193] In hardware implementations, the division between functional modules / units mentioned in the above description does not necessarily correspond to the division of physical components; for example, one physical component may have multiple functions, or one function or step may be performed by several physical components in cooperation.

[0194] Some or all of the physical components may be implemented as software executed by a processor, such as a central processing unit (CPU), a digital signal processor, or a microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit. Such software may be distributed on a computer-readable medium, which may include a computer storage medium (or non-transitory medium) and a communication medium (or temporary medium). As is well known to those skilled in the art, the term computer storage medium includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media include, but are not limited to, random access memory (RAM, more specifically SDRAM, DDR, etc.), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory (FLASH) or other disk storage; compact disc (CD-ROM), digital versatile disc (DVD) or other optical disc storage; magnetic cassettes, tapes, disk storage or other magnetic storage; any other medium that can be used to store desired information and can be accessed by a computer. Furthermore, as is well known to those skilled in the art, communication media typically embodies computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and may include any information delivery media.

[0195] The present disclosure has disclosed example embodiments, and although specific terms are employed, they are used and should be interpreted only in a general illustrative sense and not for purposes of limitation. In some instances, it will be apparent to those skilled in the art that, unless otherwise expressly indicated, features, characteristics, and / or elements described in conjunction with a particular embodiment may be used alone or in combination with features, characteristics, and / or elements described in conjunction with other embodiments. Therefore, it will be understood by those skilled in the art that various changes in form and detail may be made without departing from the scope of the present disclosure as set forth in the appended claims.

Claims

1. A data processing method, wherein: The method comprises: receiving a query request, wherein the query request includes: a target data table involved in a connection operation; Executing a logical plan according to the query request, wherein executing the logical plan includes: constructing a data filtering condition corresponding to column statistical information; wherein the column statistical information is information obtained by pre-statisticing a column value range of a column included in the target data table; Executing a physical plan corresponding to the logical plan, wherein the execution of the physical plan includes: partitioning and / or dynamically filtering the target data table according to the data filtering condition to generate a processed target data table, and performing the join operation on the processed target data table to obtain an execution result.

2. The method according to claim 1, wherein The target data table includes at least two data tables, the data in each data table is stored in multiple underlying data files, and the column statistical information includes: a column value range set of each underlying data file of each data table; before receiving the query request, the method further includes: Obtaining each underlying data file of each data table respectively, where each underlying data file includes multiple row groups in the corresponding data table; Obtaining a column value range of each target column of each row group according to a column maximum value and a column minimum value of at least one target column of each row group, wherein the at least one target column includes at least a join key column participating in the join operation; The column value ranges of each target column of each row group are merged according to the corresponding columns to obtain a column value range set of each underlying data file of the corresponding data table.

3. The method according to claim 2, wherein: The multiple underlying data files are underlying data files stored in multiple data partitions, and the column statistical information includes: a column value range set of each data partition of each data table; before receiving the query request, the method further includes: Get the column value range set corresponding to each underlying data file contained in each data partition respectively; For each data partition, the column value range sets corresponding to the underlying data files contained in the corresponding data partition are merged to obtain the column value range sets of the data partitions of the corresponding data table.

4. The method according to claim 1, wherein The query request also includes query conditions; the data filtering conditions corresponding to the column statistical information are constructed, including: Extracting a query predicate from the query request, where the query predicate is used to indicate the query condition; Constructing a column value range set of the join key column corresponding to the query condition according to the column statistical information; The column value range set of the connection key column is used as the corresponding data filtering condition.

5. The method according to claim 4, wherein the query condition includes a query column and a corresponding query value; and constructing a column value range set of a join key column corresponding to the query condition based on the column statistical information comprises: Determining a value range of the query value; Obtaining a target column value range set from the column statistical information, where the target column value range set intersects with the value range; A column value range set of a join key column corresponding to a first file is obtained as a column value range set of a join key column corresponding to the query condition, wherein the first file is an underlying data file corresponding to the target column value range set.

6. The method according to claim 1, wherein The logical plan includes a logical plan tree, wherein the logical plan tree includes a filter node, and the filter condition corresponding to the filter node includes: a column value range set of the connection key column.

7. The method according to claim 6, wherein: The filtering node is added to the node in the logical execution plan tree in the following manner, including: Evaluate the performance consumption of the connection operation according to the type of the connection operation and the query condition in the query request to obtain a performance consumption evaluation result; Determining a performance optimization evaluation result of the connection operation according to the filtering condition; Determine an optimization benefit evaluation result by using the performance consumption evaluation result and the performance optimization evaluation result; When the optimization benefit evaluation result meets a predetermined condition, a filter node is added to the logic execution plan tree.

8. The method according to claim 6, wherein: The target data table includes a first table and a second table, and the join operation is used to join all row records in the second table with row records that meet the conditions in the first table; The partitioning of the target data table according to the data filtering condition includes: Obtaining a column value range set of each data partition of the second table from the column statistical information; Querying the column value range set of the join key column from the column value range set of the acquired data partition; If the column value range set of the connection key column is not found, the currently acquired data partition is determined to be an invalid partition, and reading of data in the invalid partition of the second table is skipped to perform partition pruning on the second table.

9. The method according to claim 8, wherein The dynamically filtering the target data table according to the data filtering condition includes: Obtaining, from the column statistical information, a column value range set of each underlying data file of the second table; Querying the column value range set of the connection key column from the column value range set of the acquired underlying data file; In the case that the column value range set of the connection key column is not found in the query, reading of the currently acquired underlying data file is skipped to dynamically filter the target data table.

10. The method according to claim 9, wherein: After querying the column value range set of the connection key column from the column value range set of the acquired underlying data file, the method further includes: When the column value range set of the join key column is found in the query, the currently acquired underlying data file is read, and the column value range of each row group is obtained from the underlying data file, where the column value range includes: the minimum value and the maximum value of each column in the corresponding row group; Using the column value range set of the join key column, query the values ​​of the columns in the row group in the currently acquired underlying data file; In the case that the column value range set of the connection key column is not found in the query, reading of the currently acquired row group is skipped to filter the row groups in the target data table.

11. An electronic device comprising: at least one processor; A memory having at least one program stored thereon, wherein when the at least one program is executed by the at least one processor, the at least one processor implements the method according to any one of claims 1 to 10.

12. A storage medium, wherein: The storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 10 is implemented.

Images