Method and equipment for adding filtering condition for SQL (Structured Query Language) query statement

Abstract

The embodiment of the invention aims at providing a method and equipment for adding filtering conditions for SQL query statements, a computer program product and a computer program storage medium. Wherein when the SQL query statements connecting the partition table and the dimension table do not comprise the filtering conditions, the SQL query statements are rewritten, so that the filtering conditions are added into the SQL query statements; wherein the filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table. The embodiment of the invention provides a method and equipment for adding a filtering condition to an SQL (Structured Query Language) query statement, which are particularly suitable for processing a scene of connection between a partition large table and a dimension table, and particularly, when the initial SQL query statement does not contain the filtering condition, the efficiency of query execution is greatly improved by automatically adding the filtering condition to the SQL query statement.

Description

Technical Field

[0001] The embodiments of the present disclosure relate to the field of big data analysis technology, and in particular, to a technology for adding filtering conditions to SQL query statements. Background Art

[0002] Apache Spark 3 introduced the Adaptive Query Execution (AQE) feature to optimize query plans and improve query performance. AQE can dynamically adjust query execution plans. However, although AQE can significantly improve performance in many scenarios, it may not be able to effectively perform partition pruning in some specific scenarios, resulting in extremely high computing space consumption and extremely low performance when Spark 3 runs these types of SQL (Structured Query Language). Summary of the invention

[0003] The purpose of each embodiment of the present disclosure is to provide a method, device, computer program product and computer program storage medium for adding filter conditions to an SQL query statement.

[0004] According to one aspect of the present disclosure, a method for adding a filter condition to an SQL query statement is provided, wherein the method comprises the following steps: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

[0005] In some embodiments, the method further includes executing the rewritten SQL query statement to enable dynamic partition pruning.

[0006] In some embodiments, the method further includes parsing the SQL query statement; when it is found that the SQL query statement does not include a filtering condition, jumping to the step of rewriting the SQL query statement.

[0007] In some embodiments, the partition table and the dimension table are connected based on a JOIN operation with a connection condition ON, and a filter condition WHERE is as follows: ; in, BigTable is the table name of the partition table; SmallTable is the table name of the dimension table; Pt_id is the partition field of the partition table and the corresponding connection field of the dimension table.

[0008] In some embodiments, a filter is created based on metadata information of the partition table and the dimension table to add the filter condition to the SQL query.

[0009] According to one aspect of the present disclosure, a device for adding a filter condition to an SQL query statement is also provided, wherein the device includes a memory and a processor, the memory stores computer program instructions, and when the computer program instructions are executed by the processor, the device is configured to perform the following operations: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

[0010] According to one aspect of the present disclosure, a computer program product is further provided, comprising computer program instructions, wherein when the computer program instructions are executed by a computer device, the computer device is configured to perform a method for adding a filter condition to an SQL query statement, wherein the method comprises the following steps: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

[0011] According to one aspect of the present disclosure, a computer program storage medium is further provided, wherein computer executable instructions are stored. When the computer executable instructions are executed by a computer device, the computer device is configured to execute a method for adding a filter condition to an SQL query statement, wherein the method comprises the following steps: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

[0012] The embodiments of the present disclosure provide a method and device for adding filter conditions to SQL query statements, which are particularly suitable for processing scenarios where a partitioned large table is connected to a dimension table. In particular, when the initial SQL query statement does not contain a filter condition, the efficiency of query execution is greatly improved by automatically adding the filter condition to the SQL query statement. Moreover, after the filter condition is added to the SQL query statement, Spark 3 can automatically enable the dynamic partition pruning function to optimize the query execution plan, thereby greatly improving the execution efficiency without the user having to manually modify the SQL statement. This automatic optimization capability is particularly important when processing complex SQL queries involving large amounts of data.

[0013] Furthermore, the embodiments of the present disclosure significantly reduce the consumption of computing resources. For SQL queries involving large tables with billions of data volumes, by pruning partitions that are irrelevant to the query conditions, the embodiments of the present disclosure effectively reduce unnecessary data scans, thereby greatly shortening the query execution time and reducing I / O operations and memory consumption. This not only improves the overall query performance, but also reduces resource usage.

[0014] In certain scenarios, the optimized query reduced execution time from 43 minutes to 42 seconds, a performance improvement of more than 60 times. This significant performance optimization is of great significance for real-time query and analysis in a big data environment, and can significantly improve the system's response speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Other features, objects and advantages of the present disclosure will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings: Figure 1 A flow chart showing a method for adding a filter condition to an SQL query statement according to an embodiment of the present disclosure is shown; Figure 2 A schematic diagram showing a portion of code in Spark 3.4 related source code that fails to start dynamic partition pruning according to an example of the present disclosure; Figure 3A schematic diagram of a code portion in which a filter is added to start dynamic partition pruning in the source code of Spark 3.4 according to an example of the present disclosure is shown.

[0016] The same or similar reference numerals in the drawings represent the same or similar components. DETAILED DESCRIPTION

[0017] The specific embodiments of the present disclosure will be further described below in conjunction with the accompanying drawings.

[0018] Before discussing the exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments of the present disclosure are described as devices represented by block diagrams and processes or methods represented by flow charts. Although the flow charts describe the operating processes of the various embodiments of the present disclosure as sequential processing, many of the operations therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of the operations can be rearranged. The processes of the various embodiments of the present disclosure can be terminated when their operations are completed, but can also include additional steps not shown in the flow charts. The processes of the various embodiments of the present disclosure can correspond to methods, functions, procedures, subroutines, subprograms, etc.

[0019] The methods illustrated by the flow charts and the devices illustrated by the block diagrams discussed below may be implemented by hardware, software, firmware, middleware, microcode, hardware description language, or any combination thereof. When implemented in software, firmware, middleware, or microcode, the program code or code segments that perform the necessary tasks may be stored in a machine or computer readable medium such as a storage medium. (One or more) processors may perform the necessary tasks.

[0020] Similarly, it will also be understood that any flow charts, flow diagrams, state transition diagrams, and the like represent various processes that can be fully described as program code stored in a computer readable medium and thereby executed by a computer device or processor, whether or not such computer device or processor is explicitly shown.

[0021] As used herein, the term "storage medium" may refer to one or more devices for storing data, including read-only memory (ROM), random access memory (RAM), magnetic RAM, kernel memory, magnetic disk storage media, optical storage media, flash memory devices and / or other machine-readable media for storing information. The term "computer-readable medium" may include, but is not limited to, portable or fixed storage devices, optical storage devices, and various other media capable of storing and / or containing instructions and / or data.

[0022] A code segment may represent a procedure, function, subprogram, program, routine, subroutine, module, software package, class, or any combination of instructions, data structures, or program descriptions. A code segment may be coupled to another code segment or hardware circuit by passing and / or receiving information, data, arguments, parameters, or storage contents. Information, arguments, parameters, data, etc. may be passed, forwarded, or transmitted via any suitable means including storage sharing, information passing, token passing, network transmission, etc.

[0023] The "computer device" referred to in this context refers to an electronic device that can perform predetermined processing procedures such as numerical calculations and / or logical calculations by running predetermined programs or instructions, and may include at least a processor and a memory, wherein the processor executes program instructions pre-stored in the memory to perform the predetermined processing process, or the predetermined processing process is performed by hardware such as ASIC, FPGA, DSP, or a combination of the above two.

[0024] The above-mentioned "computer device" is usually expressed in the form of a general-purpose computer device, and its components may include but are not limited to: one or more processors or processing units, system memory. The system memory may include a computer-readable medium in the form of a volatile memory, such as a random access memory (RAM) and / or a cache memory. The "computer device" may further include other removable / non-removable, volatile / non-volatile computer-readable storage media. The memory may include at least one computer program product having a set (e.g., at least one) of program modules that are configured to perform the functions and / or methods of the various embodiments of the present disclosure. The processor executes various functional applications and data processing by running the program stored in the memory.

[0025] For example, a computer program for executing various functions and processes of multiple embodiments of the present disclosure is stored in the memory, and when the processor executes the corresponding computer program, multiple embodiments of the present disclosure are implemented.

[0026] Typically, a computer device may be, for example, a user device or a network device, or even a combination of the two. The user device includes, but is not limited to, a personal computer (PC), a laptop, a mobile terminal, etc. The mobile terminal includes, but is not limited to, a smart phone, a tablet computer, etc. The network device includes, but is not limited to, a single network server, a server group consisting of multiple network servers, or a cloud consisting of a large number of computers or network servers based on cloud computing (Cloud Computing), wherein cloud computing is a type of distributed computing, a super virtual computer consisting of a group of loosely coupled computer sets. The computer device may be run alone to implement the various embodiments of the present disclosure, or may be connected to a network and implement the various embodiments of the present disclosure through interactive operations with other computer devices in the network. The network in which the computer device is located includes, but is not limited to, the Internet, a wide area network, a metropolitan area network, a local area network, a VPN network, etc.

[0027] It should be noted that the user equipment, network equipment and network are only examples. Other existing or future computing devices or networks that are applicable to the embodiments of the present disclosure should also be included in the scope of protection of the present disclosure and are included here by reference.

[0028] The specific structural and functional details disclosed herein are merely representative and are for the purpose of describing exemplary embodiments of the present disclosure. However, the various embodiments of the present disclosure may be implemented in many alternative forms and should not be interpreted as being limited to only the embodiments set forth herein.

[0029] It should be understood that, although the terms "first", "second", etc. may be used herein to describe various units, these units should not be limited by these terms. These terms are used only to distinguish one unit from another unit. For example, without departing from the scope of the exemplary embodiments, the first unit may be referred to as the second unit, and similarly the second unit may be referred to as the first unit. The term "and / or" used herein includes any and all combinations of one or more of the listed associated items.

[0030] The terms used herein are only for describing specific embodiments and are not intended to limit exemplary embodiments. Unless the context clearly indicates otherwise, the singular forms "one", "one" and "item" used herein are also intended to include plural numbers. It should also be understood that the terms "include" and / or "comprise" used herein specify the existence of stated features, integers, steps, operations, units and / or components, without excluding the existence or addition of one or more other features, integers, steps, operations, units, components and / or combinations thereof.

[0031] It should also be noted that, in some alternative implementations, the functions / actions mentioned may occur in a different order than that indicated in the accompanying drawings. For example, depending on the functions / actions involved, two figures shown in succession may actually be performed substantially simultaneously or may sometimes be performed in the reverse order.

[0032] Spark 3 introduced Dynamic Partition Pruning (DPP), which is a very important query optimization feature, especially when large data sets and partitioned tables are involved. It dynamically prunes irrelevant partitions based on the data already processed in the query, thereby reducing the amount of data that needs to be scanned and improving query efficiency. In some scenarios, especially when the query involves filtering conditions on small tables (such as dimension tables), dynamic partition pruning can reduce the scanning of large table partitions, thereby significantly improving performance.

[0033] According to an example, a query statement is shown as follows: ; Assume that the filter condition dim_iteblog.othercol>10 of the dim_iteblog table (dimension table) filters out relatively few data. However, the previous version of Spark could not dynamically trim the partitions of the fact table according to the filtering results of the dimension table, resulting in a large number of partitions of the fact_iteblog table (fact table) being scanned unnecessarily, and thus processing a lot of invalid data. After the introduction of dynamic partition pruning in Spark 3, useless data in the fact_iteblog table can be filtered out during operation. After this optimization, the data scanned by Spark 3 queries is greatly reduced, and the performance is improved by dozens of times.

[0034] When a query involves partitioned tables and join operations, Spark 3's dynamic partition pruning function generates filter conditions at runtime and scans only the partitions related to the query conditions instead of scanning the entire table. Specifically, when Spark 3 executes a query, it prunes unnecessary partitions of large tables (such as fact tables) based on the filter conditions of small tables (such as dimension tables) to avoid reading irrelevant partitions, thereby reducing I / O operations and memory consumption.

[0035] In Spark 3, dynamic partition pruning is widely used in big data analysis scenarios, especially those related to partitioned tables and complex join queries. During query execution, Spark 3 dynamically determines which partitions need to be scanned based on the actual filter conditions obtained from the small table, that is, it further performs partition pruning based on the information inferred at run time. This process is called "dynamic partition pruning". This is different from traditional static partition pruning, which determines which partitions will be scanned during query compilation.

[0036] Dynamic partition pruning mainly depends on the filtering conditions of the small table. When Spark 3 executes a query, it will first filter out the partition keys that meet the conditions from the small table (dimension table), and then use these partition keys to prune irrelevant partitions in the large table to avoid unnecessary data scanning.

[0037] If there is no filter condition in the SQL query, dynamic partition pruning is usually not triggered. This is because the core of dynamic partition pruning is to use filter conditions to prune irrelevant partitions, thereby reducing I / O and computing. If there is no filter condition in the query, Spark 3 cannot determine which partitions are irrelevant based on a specific value or condition, and naturally cannot perform partition pruning. In this case, all partitions will be scanned, resulting in poor SQL query execution performance, especially for full table scans of large tables, where I / O and computing overhead are very large.

[0038] The following is an exemplary SQL query statement A: SELECT count(1) FROM ( SELECT t1.cust_id, t1.crowd_id FROM upms.cust_crowd_rela t1 INNER JOIN upms.key_crowd_id t ON t.id = t1.crowd_id ) There is no filtering condition in the above SQL statement A.

[0039] in: upms.key_crowd_id is a dimension table, i.e., a small table. Dimension tables usually have a small amount of data and contain descriptive information, such as group name, category, date, location, etc. In queries, small tables are usually conditional tables used for filtering and searching.

[0040] upms.cust_crowd_rela is a fact table, or a large table. The fact table stores a large amount of transactional data or log data. The amount of data in a large table may be very large, and the number of records contained far exceeds that of a dimension table. The large table is partitioned and stored by partition fields, which are used to divide the data of the large table into multiple physical partitions to improve query performance and management efficiency.

[0041] The id field records the partition field value of the large table upms.cust_crowd_rela.

[0042] In the absence of filter conditions in SQL, Spark needs to scan all partition tables because there are no filter conditions to prune away irrelevant partitions. Therefore, dynamic partition pruning does not work in such queries, resulting in very high I / O and computational overhead.

[0043] To this end, the embodiments of the present disclosure propose an SQL rewriting solution, so that in the SQL query connecting the partition table and the dimension table, the initial SQL query statement without filtering conditions is rewritten to add filtering conditions, so that the rewritten SQL query can still use dynamic partition pruning technology to prune irrelevant partitions to avoid unnecessary data scanning.

[0044] See also Figure 1 , which shows the process of adding filtering conditions to a SQL query statement according to an embodiment of the present disclosure.

[0045] See also Figure 1 In step S100, when the SQL query statement connecting the partition table and the dimension table does not include a filter condition, the computer device rewrites the SQL query statement to add a filter condition therein, where the filter condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table.

[0046] Specifically, in step S100, the computer device, more specifically, Spark3 running on the computer device, rewrites the SQL query statement for connecting the partition table and the dimension table to add the filter condition therein if the initial SQL query statement does not include a filter condition, thereby starting dynamic partition pruning. The filter condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table.

[0047] Here, according to an example, the SQL query statement A above is still used: SELECT count(1) FROM ( SELECT t1.cust_id, t1.crowd_id FROM upms.cust_crowd_rela t1 INNER JOIN upms.key_crowd_id t ON t.id = t1.crowd_id ) There is no filter condition WHERE. The JOIN operation connects the partition table upms.cust_crowd_rela and the dimension table upms.key_crowd_id. The connection condition ON connects the field id (connection field) of the dimension table with the partition field crowd_id.

[0048] When parsing the initial SQL query statement A and detecting that there is no filter condition in it, Spark 3 jumps to the rewriting step, that is, rewriting the query. The rewritten SQL query statement B is as follows: SELECT count(1) FROM ( SELECT t1.cust_id, t1.crowd_id FROM upms.cust_crowd_rela t1 INNER JOIN upms.key_crowd_id t ON t.id = t1.crowd_id ) WHERE t1.crowd_id>(SELECT MIN(t.id) FROM upms.key_crowd_id); Add a filter condition in SQL query statement B: The partition field value (crowd_id value) of the partition table upms.cust_crowd_rela is greater than the minimum value of the corresponding connection field (id) in the dimension table upms.key_crowd_id.

[0049] In this filter condition, first, the subquery SELECT MIN(t.id) from upms.key_crowd_id returns the smallest t.id value in the dimension table upms.key_crowd_id. Then, this minimum value is used in the filter condition of the target query to ensure that only records with a crowd_id greater than this value in upms.cust_crowd_rela are selected.

[0050] The function of this filter condition is to obtain a threshold through a subquery and filter out the data that meets the specific condition (crowd_id is greater than the threshold) in the query table.

[0051] By running the test, we found that in the physical execution plan of SQL statement A in Spark 3.4, the large table upms.cust_crowd_rela scanned all partitions, and the number of scanned records was: 1559573350 rows.

[0052] In the execution plan of SQL statement B, Spark 3.4 broadcasts the content of the filter condition (select min(id) fromupms.key_crowd_id) (10 partitions, number of output rows: 10) to the large table upms.cust_crowd_rela, and the large table no longer performs a full table scan of all partitions (only scans the broadcasted partitions). The number of records scanned in the 10 partitions is 14,071.

[0053] Furthermore, it is observed that the execution results of the two SQL statements remain unchanged, the execution time of SQL statement A is 43 minutes, while the execution time of SQL statement B is reduced to 42 seconds.

[0054] Obviously, the optimized SQL query reduces the execution time from the original 43 minutes to 42 seconds, and the performance is improved by more than 60 times, effectively reducing unnecessary data scanning, thereby greatly shortening the query execution time and reducing I / O operations and memory consumption. This not only improves the overall query performance, but also reduces resource usage. This substantial performance optimization is of great significance for real-time query and analysis in a big data environment, and can significantly improve the system's response speed.

[0055] In some embodiments, Spark 3 uses the metadata information of the partition table and dimension table to create filters so that the execution plan of the SQL query can enter the dynamic partition pruning process. The effect of the filter is to add a filter condition to the SQL query: Where BigTable.Pt_id>Select min(pt_id) from SmallTable.

[0056] The complete SQL statement is as follows: ; in, BigTable is the name of the large table (partition table); SmallTable is the name of the small table (dimension table); Pt_id is the partition field of the large table and the connection field corresponding to the partition field in the small table.

[0057] Based on this, the filtering condition is that the value of the partition field of the big table is greater than the minimum value of the corresponding connection field in the small table, so as to ensure that only the data with Pt_id in BigTable greater than the minimum pt_id in SmallTable is queried. This helps to reduce unnecessary data scanning and optimize query performance.

[0058] Spark 3.4 related source code: The relevant code in class org.apache.spark.sql.execution.dynamicpruning.PartitionPruning.scala is as follows Figure 2 As shown, from the analysis of the code, we can see that FilterableScan indicates that dynamic partition pruning (insertPredicate) requires an explicit filter to start.

[0059] The rewritten code is as follows Figure 3 As shown, when there is no filter condition in the initial SQL query, Spark 3.4 creates a condition filter. The main implementation function is: makeFilterableTableScanWithGreaterOrEqualThanMin. The effect is equivalent to adding a filter condition to the SQL query: where BigTable.pt_id>select min(pt_id) from SmallTable; When Spark 3 executes this type of SQL (joining a partitioned large table with a dimension table, where the field on in the dimension table is the partition key of the large table) and there is no filter condition in the SQL, you can use the method of forcibly creating filters. The created filters enable Spark 3 to use the partition pruning function, thereby achieving high-performance computing.

[0060] After rewriting the SQL query statement and adding filter conditions to it, Spark 3 executes the rewritten SQL query statement and enables dynamic partition pruning.

[0061] To enable dynamic partition pruning, you need to ensure that the filter is applied on the small table. This filter will pass the partition information to the large table through the broadcast mechanism to avoid scanning unnecessary partitions.

[0062] In this query, BigTable.pt_id>select min(pt_id) from SmallTable is the filter condition. Since the partition field of the partitioned table BigTable is pt_id, this filter condition allows Spark 3 to automatically filter out unnecessary partitions during execution, thereby enabling dynamic partition pruning.

[0063] Small table filtering: Apply filtering conditions on SmallTable to obtain the minimum value of its connection field pt_id.

[0064] Broadcast filtering information: Filtering information will be broadcast to BigTable.

[0065] Partition pruning: When querying BigTable, Spark 3 scans only the partitions that meet the conditions based on the received filter information, rather than scanning the entire table.

[0066] It should be noted that the various embodiments of the present disclosure may be implemented in software and / or a combination of software and hardware, for example, may be implemented using an application specific integrated circuit (ASIC), a general purpose computer, or any other similar hardware device. In one embodiment, the software program of the various embodiments of the present disclosure may be executed by a processor to implement the steps or functions described above. Similarly, the software program of the various embodiments of the present disclosure (including related data structures) may be stored in a computer-readable recording medium, such as a RAM memory, a magnetic or optical drive, or a floppy disk and the like. In addition, some steps or functions of the various embodiments of the present disclosure may be implemented using hardware, for example, as a circuit that cooperates with a processor to perform various steps or functions.

[0067] In addition, at least a portion of the embodiments of the present disclosure may be applied as a computer program product, such as a computer program instruction, which, when executed by a computing device, can call or provide the method and / or technical solution according to the embodiments of the present disclosure through the operation of the computing device. The program instruction for calling / providing the method of the embodiments of the present disclosure may be stored in a fixed or removable recording medium, and / or transmitted through a data stream in a broadcast or other signal-bearing medium, and / or stored in a working memory of a computing device that runs according to the program instruction.

[0068] For those skilled in the art, it is obvious that the embodiments of the present disclosure are not limited to the details of the above exemplary embodiments, and the embodiments of the present disclosure can be implemented in other specific forms without departing from the spirit or basic features of the embodiments of the present disclosure. Therefore, no matter from which point of view, the embodiments should be regarded as exemplary and non-restrictive, and the scope of the embodiments of the present disclosure is limited by the attached claims rather than the above description, so it is intended to include all changes within the meaning and scope of the equivalent elements of the claims in the embodiments of the present disclosure. Any figure mark in the claims should not be regarded as limiting the claims involved. In addition, it is obvious that the word "including" does not exclude other units or steps, and the singular does not exclude the plural. Multiple units or devices stated in the system claim can also be implemented by one unit or device through software or hardware. The words first, second, etc. are used to indicate names, and do not indicate any specific order.

Claims

1. A method for adding filtering conditions to an SQL query statement, wherein: The method comprises the following steps: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

2. The method according to claim 1, wherein: The method further comprises the following steps: The rewritten SQL query statement is executed to enable dynamic partition pruning.

3. The method according to claim 1, wherein: The method further comprises the following steps: Parsing the SQL query statement; When it is found that the SQL query statement does not include a filtering condition, jump to the step of rewriting the SQL query statement.

4. The method according to claim 1, wherein: The partition table and the dimension table are connected based on the JOIN connection operation with the connection condition ON, and the filter condition WHERE is as follows: in, BigTable is the table name of the partition table; SmallTable is the table name of the dimension table; Pt_id is the partition field of the partition table and the corresponding connection field of the dimension table.

5. The method according to claim 1, wherein: A filter is created according to metadata information of the partition table and the dimension table to add the filter condition to the SQL query.

6. A device for adding filtering conditions to SQL query statements, wherein: The device comprises a memory and a processor, wherein the memory stores computer program instructions, and when the program instructions are executed by the processor, the device is configured to perform the following operations: When the SQL query statement connecting the partition table and the dimension table does not include the filter condition, rewrite the SQL query statement to add the filter condition therein; The filtering condition is that the partition field value of the partition table is greater than the minimum value of the corresponding connection field in the dimension table; The connection between the partition table and the dimension table is based on the partition field of the partition table and the corresponding connection field in the dimension table, and the connection field is the same as the partition field in the dimension table.

7. A computer program product comprising computer program instructions, wherein: When the computer program instructions are executed by a computer device, the computer device is configured to perform the method according to any one of claims 1 to 5.

8. A computer program storage medium storing computer executable instructions, wherein when the computer executable instructions are executed by a computer device, the computer device is configured to perform the method according to any one of claims 1 to 5.

Images