Method and apparatus for optimizing execution of a query

Abstract

Embodiments of the present specification provide a method for optimizing execution of a query, comprising: receiving a query statement, wherein the query statement comprises definitions of a plurality of common table expressions (CTEs) and a plurality of subquery references referring to the CTEs; generating a physical execution plan of the query statement, wherein the physical execution plan comprises a first execution operator generated for a first subquery reference referring to a first CTE, and the first execution operator is configured to store a first data result corresponding to the first CTE into a first cache in a memory; and determining a plurality of first execution stages depending on the first CTE according to the first execution operator during execution of the physical execution plan, and sharing the first data result through the first cache between the plurality of first execution stages.

Description

Technical Field

[0001] This specification relates to one or more embodiments in the field of computer data storage and retrieval technology, and in particular to an optimized query execution method and apparatus. Background Technology

[0002] With the advent of the big data era, the demand for data processing and analysis is growing rapidly. In response, the industry has developed and provided various databases and database query engines for data storage and retrieval.

[0003] For example, Spark is a popular open-source big data processing framework, characterized by high performance, ease of use, and high scalability. SparkSQL is a high-level Spark module for structured data processing, providing a query interface compatible with SQL queries. This not only simplifies and makes data querying more intuitive but also inherits Spark's fast computation capabilities. In practical applications of SparkSQL, query efficiency is a crucial performance indicator. The same applies to other query engines.

[0004] Therefore, improving query efficiency is one of the technical problems that needs to be solved when using query tools such as SparkSQL to perform data queries. Summary of the Invention

[0005] One or more embodiments of this specification describe a query optimization execution method and apparatus that performs optimization analysis on common table expressions (CTEs) existing in the query statement, and directly shares CTE result data in memory between execution stages with the same CTE dependencies, reducing the occurrence of disk caching, thereby improving query execution performance and solving the above-mentioned technical problems.

[0006] Based on the first aspect, an optimized query execution method is provided, including:

[0007] It receives a query statement, which contains the definitions of several Common Expressions (CTEs) and several subquery references that reference the CTEs.

[0008] The physical execution plan for the query statement is generated. The physical execution plan includes a first execution operator generated for a first subquery reference that references the first CTE. The first execution operator is used to store the first data result corresponding to the first CTE into a first cache in memory.

[0009] During the execution of the physical execution plan, several first execution stages are determined based on the first execution operator, and each of the several first execution stages depends on the first CTE; among the several first execution stages, the first data result is shared through the first cache.

[0010] According to one implementation method, the physical execution plan for the query statement is generated, including:

[0011] Generate a logical execution plan for the query statement. The logical execution plan includes a sequence of logical nodes that expand the first subquery reference according to the definition of the first CTE, and a first logical node that identifies the expansion.

[0012] Converting the logical execution plan into a physical execution plan includes converting the first logical node into the first execution operator.

[0013] According to one implementation, the first execution operator has a first number corresponding to the first CTE, and the determination of several first execution stages includes:

[0014] Based on the first number, the execution phase that includes the reference to the first subquery is identified as the first execution phase.

[0015] According to one implementation, sharing the first data result through the first cache among the plurality of first execution stages includes:

[0016] The plurality of first execution stages are merged to obtain a first merge stage, and the first merge stage has a plurality of dataset outputs corresponding to the plurality of first execution stages.

[0017] The first merging phase is executed using a single process, in which the first data result is shared through a first cache.

[0018] According to one implementation, sharing the first data result through the first cache includes:

[0019] Several first data queues corresponding to the aforementioned first execution stages are established in the first cache.

[0020] For any first data queue, data records are pulled from the first data result for use in the corresponding first execution phase.

[0021] In one scenario of the above implementation, the plurality of first execution stages need to use the first data result multiple times through cross-dependencies, and the method further includes:

[0022] The first data result is stored in the first disk via the first cache.

[0023] The step of retrieving data records from the first data result includes: in response to a data usage request in a certain first execution stage, retrieving data records from the first disk storage and storing them in the first data queue corresponding to the first execution stage in the first cache.

[0024] In one scenario of the above implementation, retrieving data records from the first data result includes:

[0025] In a single data retrieval, the retrieved data records are stored in each first data queue.

[0026] According to one implementation of the above scenario, each first data queue has a preset fixed cache space; in the single data retrieval, the retrieved data records are stored in each first data queue, including:

[0027] In the plurality of first data queues, if any data queue does not have enough cache space to fetch the next data record, fetching is paused until the data queue regains enough cache space due to the data usage of the corresponding first execution phase.

[0028] According to the second aspect, an optimized query execution apparatus is provided, the apparatus comprising:

[0029] The receiving module is configured to receive query statements, which contain definitions of several Common Expressions (CTEs) and several subquery references that reference the CTEs.

[0030] The generation module is configured to generate a physical execution plan for the query statement. The physical execution plan includes a first execution operator generated for a first subquery reference that references the first CTE. The first execution operator is used to store the first data result corresponding to the first CTE into a first cache in memory.

[0031] The execution module is configured to, during the execution of the physical execution plan, determine a plurality of first execution stages based on the first execution operator, wherein the plurality of first execution stages all depend on the first CTE; and share the first data result among the plurality of first execution stages through the first cache.

[0032] According to a third aspect, a computer program product is provided, including a computer program / instructions that, when executed by a processor, implement the steps of the method described in the first aspect.

[0033] According to a fourth aspect, a computing device is provided, including a memory and a processor, characterized in that the memory stores executable code, and when the processor executes the executable code, it implements the method described in the first aspect.

[0034] In summary, by using the methods and apparatus disclosed in the embodiments of this specification, during the execution of a query, the result data of the CTE can be directly shared in memory for execution stages with the same CTE dependency, thereby reducing disk I / O and greatly improving data query efficiency. Attached Figure Description

[0035] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0036] Figure 1 This is a schematic diagram of the SparkSQL query execution process disclosed in this specification;

[0037] Figure 2A This is a schematic diagram illustrating the logical plan of an exemplary query statement disclosed in this specification.

[0038] Figure 2B This is a schematic diagram illustrating the logical plan of an exemplary query statement disclosed in this specification.

[0039] Figure 3A A schematic diagram illustrating the execution plan parsing of an exemplary query provided in the embodiments of this specification;

[0040] Figure 3B A schematic diagram illustrating the execution plan parsing of an exemplary query provided in the embodiments of this specification;

[0041] Figure 4 This is a flowchart of an optimized query execution method provided according to an embodiment of this specification;

[0042] Figure 5 This is a schematic diagram illustrating the construction of an execution plan according to an embodiment of this specification;

[0043] Figure 6A This is a schematic diagram of a CTE data result sharing method provided by a related technology;

[0044] Figure 6B This is a schematic diagram illustrating the CTE data result sharing method provided in the embodiments of this specification;

[0045] Figure 7A This is a schematic diagram of an exemplary execution plan provided according to an embodiment of this specification;

[0046] Figure 7B This is a schematic diagram of an exemplary execution plan provided according to an embodiment of this specification;

[0047] Figure 7C This is a schematic diagram of an exemplary execution phase provided according to an embodiment of this specification;

[0048] Figure 8A This is an example of the MultiShuffleMapStage definition provided according to the embodiments of this specification;

[0049] Figure 8B This is an example of a MultiShuffleMapTask definition provided according to the embodiments of this specification;

[0050] Figure 9A This is an example of a BufferRDD data caching mechanism provided according to embodiments of this specification;

[0051] Figure 9B This is an example of a BufferRDD data caching mechanism provided according to embodiments of this specification;

[0052] Figure 9C This is an example of a BufferRDD data caching mechanism provided according to embodiments of this specification;

[0053] Figure 10 This is a schematic diagram of an optimized execution device for querying according to an embodiment of this specification. Detailed Implementation

[0054] The solutions provided in the embodiments of this specification will now be described with reference to the accompanying drawings.

[0055] In one or more embodiments of this specification, the Spark computing framework will be used as an example to illustrate the additional computational overhead that common expression code segments (CTEs) and their references may cause during big data queries. Technical solutions to address this problem will also be explained in conjunction with the Spark computing framework. It should be noted that although some embodiments of this specification are described in conjunction with the Spark computing framework, this does not imply a limitation on the application scenarios or technical tools of the embodiments of this invention. The technical concepts embodied in the various embodiments of this specification can be applied to other query engines that support SQL query statements and CTE code segments.

[0056] As mentioned earlier, with the development of computing technology, application data is expanding rapidly. Some applications require massive computing power to perform data analysis and data mining, necessitating significant computational capabilities to complete related data queries. If centralized computing is used, the query process can be extremely time-consuming. Against this backdrop, distributed computing has emerged to improve data processing efficiency. Distributed computing decomposes data into multiple smaller data partitions, which are then processed in parallel by multiple computing nodes, thereby improving data processing efficiency. Spark, for example, is a big data distributed computing framework. SparkSQL, as an important module in the Spark ecosystem, provides structured data processing and SQL query analysis capabilities, enabling analysts in different business domains to transparently utilize Spark (Spark Core) to process massive amounts of data simply through SQL query statements.

[0057] In the process of parallel computing of big data in the Spark computing framework, the shuffle operation is used to redistribute the data. The shuffle operation can exchange data between different computing nodes. According to the needs of downstream tasks, the data is re-divided into multiple data partitions and distributed to various computing nodes for parallel computing of downstream tasks.

[0058] However, the shuffle process requires data re-aggregation and partitioning, inevitably involving significant data exchange between different compute nodes in the cluster. Since different compute nodes are running different computational processes, data exchange requires writing data to disk first, and then sharing the data between the compute nodes by reading from the disk, resulting in substantial disk I / O. In some practical applications, different compute nodes are often deployed on different computing devices, communicating and collaborating with each other via a network. Therefore, the shuffle process also inevitably generates significant network communication I / O, as well as data serialization / deserialization overhead. This leads to the shuffle process being extremely time-consuming and inefficient in big data computing scenarios.

[0059] Since SparkSQL is a data query module built on Spark Core, it receives SQL structured query statements and transforms them into execution plans that Spark Core can execute. Therefore, by exploring the shuffle execution nodes that may exist in SparkSQL during the execution of SQL query statements, targeted optimizations can be made to improve the query efficiency of SparkSQL.

[0060] Figure 1This document provides a schematic of the Spark SQL query execution flow, which can be broadly divided into a planning phase and an execution phase. During the planning phase, the SQL query statement is parsed, generating a logical execution plan followed by a physical execution plan. In the execution phase, the physical execution plan is executed by Spark Core, ultimately producing an RDD (Resilient Distributed Dataset) object.

[0061] Specifically, after receiving an SQL query, the SparkSQL module parses the SQL query (combined with DataFrames and DataSets defined in the program) into a logical execution plan. This involves parsing the query elements such as keywords, expressions, field mappings, and data sources in the SQL query (using sqlParser), mapping the data operations involved in the query to corresponding logical nodes, and arranging these logical nodes according to the query execution order to generate the logical execution plan. Then, the logical execution plan (using SparkPlanner) is converted into a physical execution plan. This involves applying a series of transformation strategies to convert the logical nodes in the logical execution plan into corresponding execution operators, and finally executing them to generate an RDD.

[0062] It should be understood that the more detailed process of generating the logical execution plan also includes the generation of an unresolved / parsed logical plan, the generation of an analyzed logical plan through the Analyzer, and the generation of an optimized logical plan through the Optimizer. This will not be elaborated on in the embodiments of this specification.

[0063] Both the logical execution plan and the physical execution plan described above can be constructed in the form of a tree. Figure 2AThe diagram illustrates the logical plan parsing of an exemplary query statement. Referring to the attached diagram, this SQL query statement contains two identical subqueries. After merging the data results from the subqueries, the results are aggregated using the `subject` field value to calculate the sum of the scores. During the parsing of this SQL query statement, SparkSQL first parses the data source and operations in the subqueries, constructing a logical execution plan using the corresponding logical nodes. For example, in `SELECT subject, score FROM tbl_Score`, the `SELECT` operation corresponds to the logical node `Project` (mapping), `subject` and `score` are the mapped fields, and the `FROM` operation corresponds to the logical node `scan` (scanning), with `tbl_Score` being the data source for the scan. Then, the data results from the subqueries are merged. In the logical execution plan, the logical node `Union` (merge) is used to combine the results of the two subquery execution plans. Finally, the `Aggregate` (aggregate) logical node is used to aggregate and calculate the sum of the scores from the merged data results. The logical execution plan output by SparkSQL can be seen in the console output example shown in the black box in the attached diagram. This logical execution plan can be represented using a tree structure, as shown in the logical execution plan tree on the right side of the attached diagram. In the embodiments of this specification, a more readable tree structure will be used to describe the execution plan.

[0064] It should be noted that, for the sake of simplicity and readability, the SQL query statements and logical execution plans in the above examples have been simplified and do not contain any business meaning or business logic. They are used only as examples to illustrate the logical execution plan.

[0065] In some specific practices, for duplicate subqueries in the above scenarios, they are usually extracted from the query statement as independent subqueries, encapsulated using a Common Table Expression (CTE), and formed into a data result set with a temporary name, which is then referenced in subsequent queries.

[0066] A Common Expression (CTE) is a temporarily named result set that can be referenced in SELECT, INSERT, UPDATE, DELETE, or MERGE statements. A CTE begins with the WITH keyword, followed by the CTE name and its definition. The syntax is: WITH expression_name AS (CTE_query_definition), for example: WITH c AS (SELECT a FROM t), which defines a CTE whose query is CTE_query_definition (in this example, filtering data from column a in table t), and whose result set is named expression_name (temporarily named c in this example). When using a CTE, the result set can be referenced within the execution scope of the SQL query immediately following the WITH clause: SELECT [Column1, Column2, ...] FROM expression_name, for example: SELECT * FROM c, which means retrieving all data from the CTE result set c.

[0067] Figure 2B This illustrates the logical plan parsing of an exemplary query statement. Corresponding to the previous example, in this example, a reusable subquery is encapsulated using a CTE, and the data result of this CTE is named 'c'. In subsequent queries, 'c' is referenced multiple times as the data source. Referring to the attached diagram, during the parsing of the logical execution plan for this query statement, SparkSQL resolves the CTE references using the CTERelationRef node, which points to the definition section of the corresponding CTE, i.e., CTERelationDef.

[0068] The inventors discovered that when SparkSQL parses SQL queries containing Common Expressions (CTEs), it parses the CTEs as a separate stage. This stage produces the data results of the CTEs and outputs them in a round-robin manner (using the ShuffleExchangeExec operator) for reuse by tasks in other stages (using the ReusedExchangeExec operator).

[0069] The reason for this is that SparkSQL generates an ExchangeExec execution node in the execution plan for CTEs and their reuse. This node triggers a shuffle operation (shuffle write on the map side). When a CTE is reused, the result data is read through the shuffle operation (shuffle read on the reduce side) for reuse. These shuffle operations generated for CTEs and their reuse can be optimized through technical means, which will be explained in detail below.

[0070] Figure 3A and Figure 3B The execution plan parsing process of an exemplary query statement is shown. In this example SQL query statement, the definition of the CTE (shown as CTERelationDef in the figure) and the reuse of the CTE (shown as CTERelationRef in the figure) are included.

[0071] See appendix Figure 3A The document discloses a parsing diagram of the logical execution plan for an example SQL query, revealing that the query contains two references to the same CTE. When SparkSQL parses the CTE reference node (CTERelationRef), the execution plan optimizer (Catalyst) executes the ReplaceCTEWithRepartition rule, replacing the CTE reference node with a Repartition logical node (the Repartition node in the logical execution plan on the right side of the attached diagram), and expanding the CTE according to its definition (shown as a dashed box in the logical execution plan on the right side of the attached diagram).

[0072] At this point, the logical execution plan for the CTE node is complete. Next, please refer to the appendix. Figure 3BThis document discloses a breakdown of the physical execution plan for the example SQL query. Based on the logical execution plan of the SQL query, the Repartition logical node is converted into an Exchange execution operator (ExchangeExec) in the physical execution plan. This operator shuffles and partitions the data results of the CTEs (by default, using a Round-Robin partitioning method), generating a shuffle operation. During the Adaptive Query Execution (AQE) phase, SparkSQL updates Exchange nodes with the same CTE references to the ReusedExchange execution operator (ReusedExchangeExec) by executing the Exchange reuse rule, allowing the Exchange nodes to reuse CTE data results, as shown in the rightmost physical execution plan in the attached diagram. From the physical execution plan of this example, it can be seen that SparkSQL will generate one shuffle write (Exchange execution operator) and two shuffle reads (Exchange and ReusedExchange execution operators) during the execution of the SQL query with two identical CTE references.

[0073] The above example illustrates that Spark SQL incurs additional shuffle overhead when executing queries with CTE references. The example, based on two CTE references, demonstrates this overhead generation process. In practice, a data query statement combined with business logic may contain numerous CTE references, resulting in significant shuffle overhead and severely impacting query execution efficiency. To address these CTE-related shuffle operations, the inventors propose optimization techniques. Specifically, during physical execution plan generation, a custom execution operator is used to mark the computation process of CTE references. This operator instructs the Spark engine to cache the CTE data results in memory. In subsequent execution, execution stages with the same CTE dependencies are merged. These merged stages can directly share the cached CTE data results in memory, thus avoiding additional shuffle overhead and improving the execution efficiency of Spark SQL queries.

[0074] Following the above technical concept, in Figure 4The diagram illustrates an optimized query execution method according to embodiments of this specification. It is understood that this method can be executed by any device, equipment, platform, or cluster of devices with computing and processing capabilities. See also... Figure 4 In one embodiment, the method includes at least the following steps: S401: Receiving a query statement, which includes definitions of several common expression types (CTEs) and several subquery references that reference the CTEs. S403: Generating a physical execution plan for the query statement, the physical execution plan including a first execution operator generated for a first subquery reference that references a first CTE, the first execution operator being used to store a first data result corresponding to the first CTE into a first cache in memory. S405: During the execution of the physical execution plan, determining several first execution stages based on the first execution operator, the several first execution stages all depending on the first CTE; sharing the first data result among the several first execution stages through the first cache.

[0075] The specific execution method of each of the above steps will be described in detail below with reference to the accompanying drawings.

[0076] In step S401, a query statement is received, which contains the definitions of several common expression CTEs and several subquery references that reference the CTEs.

[0077] The definition of CTEs in SQL query statements, as well as the format and examples of CTE references, have been explained in detail in the previous text and will not be repeated here.

[0078] It should be noted that, for the sake of brevity, the embodiments in this specification use an SQL query statement that references the same CTE twice as an example to describe one or more embodiments. However, it should be understood that in practice, the number of CTE references is not limited to two. Depending on the data query and processing requirements, big data engineers often write SQL query statements with more subquery references, including SQL query statements with multiple CTE definitions and multiple references to each CTE. One or more embodiments in this specification aim to provide an optimized execution method for query statements containing CTEs, and are not limited to a specific query statement structure. The method provided by the embodiments of this invention can be applied to any scenario related to the technical concept provided in the embodiments of this invention.

[0079] Next, in step S403, a physical execution plan for the query statement is generated. The physical execution plan includes a first execution operator generated for a first subquery reference that references the first CTE. The first execution operator is used to store the first data result corresponding to the first CTE into a first cache in memory.

[0080] Taking the Spark computing engine as an example, in this step, to avoid shuffle operations during the execution of the physical execution plan, a custom new execution operator is used to interpret the CTE references within the physical execution plan. This execution operator instructs the Spark engine to store the data results corresponding to the CTE in an in-memory cache object during subsequent execution, without incurring shuffle operations. In a specific implementation, this execution operator is named CTEExec, and the cache object is named BufferRDD, which is an implementation based on RDD datasets used to cache intermediate data in memory and can be used by multiple downstream tasks.

[0081] According to one implementation, during the construction of the logical execution plan, a custom logical node can be used to identify the CTE reference to avoid generating a Repartition node. Then, during the conversion of the logical execution plan into the physical execution plan, the logical node can be converted into the first execution operator to generate the physical execution plan required for this step.

[0082] The implementation can be summarized as follows: First, a logical execution plan for the query statement is generated. This logical execution plan includes a sequence of logical nodes that expand the first subquery reference according to the definition of the first CTE, and a first logical node identifying the expansion. Then, the logical execution plan is converted into a physical execution plan, which includes converting the first logical node into the first execution operator.

[0083] Figure 5 This diagram illustrates the execution plan construction based on this implementation. The SQL query statement in this implementation includes the definition of the first CTE (shown as a gray box in diagram 1 in the attached figure), which includes Scan, Exchange, and Project operations on the data. It should be noted that, to compare and explain the working mechanisms of BufferRDD and RDD in subsequent steps, a data shuffling (Exchange) operation is added to this CTE, which will be described in detail in later steps. Furthermore, the SQL query statement also includes two references to the first CTE (the first subquery reference). Correspondingly, a CTE reference logical node is generated in the logical execution plan (shown as CTERelationRef in diagram 1 in the attached figure).

[0084] Next, the first subquery reference will be expanded according to the definition of the first CTE, and the expansion will be identified using the first logical node. In this implementation, the first logical node is named a CTE node. This node will not generate Exchange execution operators during the subsequent physical execution plan transformation process, and therefore will not trigger a shuffle operation. Referring to the attached figure, block diagram 2 shows the logical execution plan after the above steps. It can be seen that in this logical execution plan, each first subquery reference is expanded according to the definition of the first CTE, resulting in a corresponding sequence of logical nodes, and the sequence of logical nodes obtained by the expansion is identified using a CTE logical node.

[0085] Then, during the process of converting the logical execution plan into a physical execution plan, the first logical node (CTE logical node) is transformed into the first execution operator (CTEExec). The resulting physical execution plan is shown in block diagram 3 of the attached figure. It can be seen that through the above execution plan construction steps, in the labeling of the CTE reference node, the Exchange execution operator that originally generated a shuffle operation is replaced with the custom CTEExec execution operator. CTEExec will instruct the Spark engine in subsequent steps to cache the data results of the CTE in memory, thus avoiding shuffle operations.

[0086] Although step S403 has been described above using the Spark computing engine as an example, the technical concepts embodied therein can be applied to the execution plan construction process of other computing engines for data query statements containing CTE code segments.

[0087] After the above steps, a corresponding physical execution plan can be constructed based on the received SQL query statement. Next, in step S405, during the execution of the physical execution plan, several first execution stages are determined according to the first execution operator, and each of the several first execution stages depends on the first CTE; the first data result is shared among the several first execution stages through the first cache.

[0088] Taking the Spark computing engine as an example, the execution stage is a key concept in Spark's query execution process. A program submitted to the Spark engine will have its query execution process divided into several execution stages by the DAGScheduler based on a DAG (Directed Acyclic Graph). Typically, within an execution stage, a series of transformation operations are performed on the data across different partitions of an RDD. When the DAGScheduler detects that an operation requires data shuffling across multiple partitions (i.e., a wide dependency, shuffle dependency), it will divide the execution stage based on that operation. In other words, the boundaries between execution stages are wide dependencies, and shuffle operations also occur between execution stages. To eliminate shuffle operations between execution stages, it's necessary not only to eliminate data shuffling but also to consider how to correctly transfer the data required by downstream execution stages.

[0089] Therefore, in this step, among the several first execution stages that all depend on the first CTE, the data results of the first CTE are directly shared through memory to avoid generating shuffle operations that consume disk I / O. Specifically, this data sharing can be achieved as follows: First, identify several execution stages (i.e., the first execution stages) that have a common CTE (i.e., the first CTE) dependency. Then, merge the several first execution stages so that the merged stage can be executed in a single process. In this way, the several first execution stages contained in the merged stage can share the same cache in memory and reuse the cached data results of the first CTE. This step will be explained in detail below.

[0090] Figure 6A and Figure 6B Using the Spark computing engine as an example, each example illustrates a CTE data result sharing method based on a related technology and the embodiment provided. In these examples, a query statement with two CTE references is used to explain how CTE data results are shared during query execution. However, it's important to understand that in practice, a data query statement may have numerous CTE references. Each CTE reference will share its data results as shown in the examples, which will not be elaborated upon further in this example. Please refer to [the relevant documentation / reference]. Figure 6AIn the execution of a Spark job, there are three execution stages. Stage 0 produces an RDD containing the data results of the first Conditional Exchange Execution (CTE), based on the definition of the first CTE. Tasks in each partition of Stage 1 and Stage 2 rely on the first CTE for their execution operator operations. In one related technique, when the AQE stage detects that both Stage 1 and Stage 2 can reuse the data results of the first CTE, it generates a `ReusedExchangeExec` execution operator. This operator reads the data results of the first CTE output from Stage 0 through a shuffle operation. Therefore, in this example, reusing the data results of the first CTE will result in one shuffle write and two shuffle reads.

[0091] Continue reading Figure 6B According to this embodiment, with Figure 6A Unlike other execution phases, in the AQE phase, when both execution phase 1 and execution phase 2 are identified as having a dependency on the first CTE, these two execution phases with the same CTE (the first CTE) dependency can be merged to obtain an executable merged phase. Since no Exchange execution operator is generated for the first CTE in this embodiment, the data result calculation of the first CTE will not be extracted as an independent execution phase (i.e.,...). Figure 6A In this embodiment, the Spark engine prioritizes the computation of the first CTE based on dependencies, obtaining the corresponding data result, which is shown as an RDD labeled "CTE Data Result" in the attached figure. This data result can be cached in the first cache (BufferRDD) in memory. Execution stages 1 and 2, both dependent on the first CTE, can directly share the cached first CTE data result in the BufferRDD during the merged execution phase, avoiding shuffle overhead. This is because the merged phase is executed in a single process with independent resource scheduling capabilities, allowing data sharing between execution tasks within the same memory space. Furthermore, it should be noted that in this embodiment, due to the merging of execution stages, only the executable merged phase exists; execution stages 1 and 2 do not exist. In the attached figures and the above description, for ease of comparison with execution stages in related technologies, execution stages 1 and 2 are marked and circled with dashed lines in the attached figures, but this does not mean that execution stages 1 and 2 exist within the merged phase.

[0092] Furthermore, corresponding to the CTEExec execution operator, similar to the ReusedExchangeExec execution operator mentioned earlier, during the AQE phase, when a reusable CTE reference is identified, a ReusedCTEExec execution operator will be generated corresponding to the CTEExec execution operator. In one example, corresponding to... Figure 5 The physical execution plan shown, after analysis of the reusable CTE, yields the following physical execution plan: Figure 7A As shown.

[0093] According to one implementation, in order to determine the plurality of first execution stages, the number of the CTE (first CTE) corresponding to the first execution operator (the first number) can be added to the first execution operator, and the execution stages containing the same number are determined as the first execution stages. Figure 7B An exemplary execution plan for this implementation is provided. As can be seen, in this implementation, the number of the first CTE is embedded in the first execution operator (shown as number 1 in the gray box in the attached figure). Thus, in the AQE phase, the first execution operator corresponding to the same CTE can be found based on this number, and the execution stage containing this execution operator can be determined as the first execution stage. Referring to the attached figure, based on the wide dependencies in the DAG, the illustrated physical execution plan is divided into four stages (QueryStage). It should be noted that due to the existence of the AQE phase, the Spark engine also dynamically optimizes the physical execution plan between the physical execution plan and the final execution (the execution of AdaptiveSparkPlanExec). Therefore, before determining the final execution stage, the Spark engine pre-divides the physical execution plan into multiple QueryStages based on dependencies and submits them for execution sequentially. The AQE will re-optimize the execution plan based on the execution results, optimizing the QueryStages into the final executable execution stages.

[0094] Please refer to the appendix for further details. Figure 7B In the AQE phase, when it is detected that both QueryStage1 and QueryStage2 depend on the first CTE based on the first number in the first execution operator, these two phases can be identified as the first execution phase and merged. The merged phase can be executed in a single process, and during execution, the data results of the first CTE are shared through the first cache in memory.

[0095] According to one implementation, the above steps can be summarized as follows: The plurality of first execution stages are merged to obtain a first merge stage, the first merge stage having several dataset outputs corresponding to the plurality of first execution stages. The first merge stage is executed using a single process, and in this single process execution, first data results are shared through a first cache. According to this implementation, Figure 7C An execution diagram of the merge phase is provided.

[0096] Referring to the attached diagram, let's first take the standalone QueryStage 0 as an example to briefly illustrate the execution method of Spark engine execution stages in some related technologies. As shown in the diagram, QueryStage 0 has no prerequisites and can be executed first. Therefore, QueryStage 0 is submitted as an execution stage (shown as Stage 0 in the diagram) for execution. An execution stage (ShuffleMapStage) that does not carry the final result of the SQL query will perform various transformation operations on the data in the RDD to generate the result data of this stage (shown as RDD 0 in the diagram). This result data is then supplied to downstream execution stages through shuffle operations. As shown in the diagram, the data shuffle id0 produced by execution stage 0 is supplied to downstream execution stages.

[0097] Next, taking the mergeable QueryStage 1 and QueryStage 2 as examples, the execution method of the merging stage in this embodiment will be described. Referring to the attached figures, both QueryStage 1 and QueryStage 2 depend on the first CTE. Therefore, in the AQE stage, these two QueryStages can be merged to obtain the merging stage (shown as Stage 1 in the attached figures). In the merging stage, the required data (shuffle id0) is first obtained from the upstream execution stage. After data transformation (Project operation in the attached figures), the data result of the first CTE is obtained and stored in BufferRDD. The data in BufferRDD can be used by multiple downstream processes. As shown in the attached figures, corresponding to QueryStage 1, after retrieving the data result of the first CTE from BufferRDD, it is output through an Exchange operation (shuffle id1); corresponding to QueryStage 2, after retrieving the data result of the first CTE from BufferRDD, after data transformation (Filter→Project in the attached figures), the resulting data (RDD 2) is output through an Exchange operation (shuffle id2). In this way, the execution of QueryStage 1 and QueryStage 2 in the physical execution plan is completed during the merging phase. During this execution, the data results of the first CTE are shared only through the first cache (BufferRDD) in memory, avoiding the generation of additional shuffle operations, thereby improving the execution efficiency of SparkSQL queries.

[0098] Furthermore, as can be seen from the above description and accompanying figures, in some related technologies, the execution stage (ShuffleMapStage) only supports single-path output, that is, the last RDD (final RDD) belonging to that execution stage, and the data of the final RDD can represent the output of that execution stage. However, within the merge stage, it contains several execution stages. If ShuffleMapStage from related technologies is used, it will not support outputting data from multiple execution stages. Therefore, it is necessary to establish an execution stage type that can be used for the merge stage to support multi-path data output (i.e., support multiple final RDDs). In a specific implementation, an execution stage type that supports multi-path output is established, named MultiShuffleMapStage, which inherits from the Stage type of the Spark framework so that it can be executed in the Spark framework. The definition of MultiShuffleMapStage is attached. Figure 8AAs shown, the `rdd` (final RDD belonging to the stage), `callSite` (call stack), and `shuffleDep` (wide dependency) of this type are modified to support array (Seq) data, thus enabling `MultiShuffleMapStage` to support multi-path output. Furthermore, the task transformed during the merge stage, in conjunction with `MultiShuffleMapStage`, also needs corresponding modifications to support multiple data partitions. It is named `MultiShuffleMapTask`, inheriting from the Spark framework's `Task` type. (Appendix) Figure 8B The definition of MultiShuffleMapTask is shown, where partitions in this type are defined as array (Seq) data to support multiple data partitions.

[0099] The above is a description of the main flow of an optimized query execution method provided in the embodiments of this specification. Although the above embodiments mainly use the Spark computing engine as an example to illustrate the method flow, the technical concepts embodied can also be applied to the execution plan construction and execution process of data query statements in other computing engines.

[0100] In addition, some embodiments of this specification also provide a method for caching data in BufferRDD to ensure that each downstream task can correctly obtain the CTE data results it depends on.

[0101] The CTE data results cached in BufferRDD need to be used by multiple downstream tasks. In the Spark framework, downstream tasks can be executed in parallel. In order not to block the parallel execution of downstream tasks, in one implementation, several first data queues corresponding to the several first execution stages can be established in the first cache. For any first data queue, data records are pulled from the first data results for use by the corresponding first execution stage. Figure 9A An example of a BufferRDD data caching mechanism is provided. Referring to the attached diagram, for the three downstream tasks that depend on the CTE data results in the BufferRDD, the BufferRDD establishes three data queues, each providing data to one downstream task. The BufferRDD pulls data records from the first data result and places them into all the data queues. Each downstream task retrieves data from its corresponding data queue. This approach ensures that each downstream task can correctly obtain its dependent CTE data results without data read waiting between them, guaranteeing the parallel execution of downstream tasks.

[0102] In a specific scenario, the data queue cache space can be set within a preset threshold to ensure that the memory space occupied by the BufferRDD is controllable. Specifically, during a single data retrieval of the BufferRDD, the retrieved data records are stored in each first data queue. Each first data queue has a preset fixed cache space. If any data queue does not have enough cache space to retrieve the next data record, retrieval is paused until that data queue regains sufficient cache space due to data usage in the corresponding first execution phase. Figure 9B An example of the BufferRDD data caching mechanism in this scenario is provided. Referring to the attached diagram, in this scenario, each data queue has a pre-set cache space that can hold two data records. Among the three downstream tasks with a common CTE dependency, the downstream task performing the Project operation processes faster than the other two. When the Project operation starts processing data record #2, the Filter operation has just finished processing data record #1 and is preparing to fetch data record #2, while the Aggregate operation is just preparing to fetch data record #1. At this time, even if BufferRDD fetches data record #3 from the first data result, this data record cannot be placed into any data queue because the data queue corresponding to the downstream task of the Aggregate operation has already cached two data records and has not yet been used. It does not have enough cache space to store the next data record. To prevent data loss, even if other data queues have free cache space, data record #3 cannot be asynchronously fetched and stored. Therefore, in this scenario, the fetching of data records can be paused until the downstream task of the Aggregate operation consumes the data in its data queue and regains sufficient cache space. In other words, the prerequisite for pulling data records and storing them in each data queue is that all data queues have enough cache space to store the next data record.

[0103] In another specific scenario, the several first execution stages need to use the first data result multiple times through cross-dependencies, for example, by referencing a self-join query of the first CTE. In this case, if multiple data queues are used to synchronously pull data records, a deadlock will occur. To solve this problem, the first data result can be stored in the first disk storage via the first cache; in response to a data usage request from a certain first execution stage, data records can be pulled from the first disk storage and stored in the first data queue corresponding to that first execution stage in the first cache. Figure 9CAn example of the BufferRDD data caching mechanism in this scenario is provided. Referring to the attached diagram, in this scenario, both data inputs of the downstream task performing the Join operation depend on the first CTE, and the Join operation performs a Cartesian product calculation on the data result of the first CTE (interleaving calculations between data records). Therefore, it is necessary to asynchronously retrieve data records from the two data queues corresponding to this downstream task. In this case, the first data result retrieved by BufferRDD can be written to disk (the first disk storage). When downstream tasks use data records, they retrieve data from the first disk storage. Here, BufferRDD supports asynchronous data retrieval by downstream tasks, avoiding deadlock.

[0104] The foregoing description, based on one or more embodiments, details an optimized query execution method. By employing the method provided in the embodiments of this specification, during query execution, for execution stages with the same CTE dependency, the result data of the CTE can be directly shared in memory, thereby reducing disk I / O and significantly improving data query efficiency.

[0105] In this specification, the terms "first" in the first CTE, first execution operator, etc., and the corresponding terms "second" and "third" (if they exist) in the text are merely for the convenience of distinction and description, and do not have any limiting meaning.

[0106] The foregoing description describes specific embodiments of this specification; other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than those shown in the embodiments, and the desired result may still be achieved. Furthermore, the processes depicted in the drawings do not necessarily need to follow the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0107] Figure 10 This is a schematic diagram of an optimized query execution device according to an embodiment of this specification. The device 1000 is deployed in a computing device, which can be implemented using any device, equipment, platform, device cluster, etc., with computing and processing capabilities. This device embodiment is similar to... Figure 4 The method embodiment shown corresponds to this. The apparatus 1000 includes:

[0108] The receiving module 1001 is configured to receive a query statement, which contains the definitions of several common expression TEs and several subquery references that reference the CTEs.

[0109] The generation module 1002 is configured to generate a physical execution plan for the query statement. The physical execution plan includes a first execution operator generated for a first subquery reference that references the first CTE. The first execution operator is used to store the first data result corresponding to the first CTE into a first cache in memory.

[0110] The execution module 1003 is configured to, during the execution of the physical execution plan, determine a plurality of first execution stages based on the first execution operator, wherein the plurality of first execution stages all depend on the first CTE; and share the first data result among the plurality of first execution stages through the first cache.

[0111] According to another embodiment, this specification also provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the foregoing combinations. Figure 4 The steps of the method are described.

[0112] According to yet another embodiment, this specification also provides a computing device including a memory and a processor, characterized in that the memory stores executable code, and when the processor executes the executable code, it implements the foregoing combination. Figure 4 The steps of the method are described.

[0113] Those skilled in the art will recognize that the functions described in the embodiments of the present invention in one or more of the above examples can be implemented using hardware, software, firmware, or any combination thereof. When implemented in software, these functions can be stored in a computer-readable medium or transmitted as one or more instructions or code on a computer-readable medium.

[0114] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, or improvements made based on the technical solutions of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for optimizing execution of a query, comprising: receiving a query statement including definitions of a plurality of common table expressions (CTEs) and a plurality of subquery references referencing the CTEs; generating a physical execution plan for the query statement, the physical execution plan including a first execution operator generated for a first subquery reference referencing a first CTE, the first execution operator being configured to store a first data result corresponding to the first CTE in a first cache in a memory; during execution of the physical execution plan, determining a plurality of first execution stages based on the first execution operator, the plurality of first execution stages all depending on the first CTE, and sharing the first data result among the plurality of first execution stages via the first cache.

2. The method of claim 1, wherein, generating a physical execution plan for the query statement, comprising: generating a logical execution plan for the query statement, the logical execution plan including a sequence of logical nodes for expanding the first subquery reference based on the definition of the first CTE, and a first logical node identifying the expansion; converting the logical execution plan to the physical execution plan, including converting the first logical node to the first execution operator.

3. The method of claim 1, wherein, the first execution operator having a first number corresponding to the first CTE, the determining the plurality of first execution stages comprising: based on the first number, determining an execution stage containing the first subquery reference as a first execution stage.

4. The method of claim 1, wherein, the sharing the first data result among the plurality of first execution stages via the first cache comprising: merging the plurality of first execution stages to obtain a first merged stage, the first merged stage having a plurality of data set outputs corresponding to the plurality of first execution stages; executing the first merged stage using a single process, in which the first data result is shared via the first cache.

5. The method of claim 1, wherein, the sharing the first data result via the first cache comprising: establishing a plurality of first data queues in the first cache corresponding to the plurality of first execution stages; for any first data queue, pulling data records from the first data result for use by a corresponding first execution stage.

6. The method of claim 5, wherein, the plurality of first execution stages requiring multiple cross-dependent uses of the first data result, the method further comprising: storing the first data result in a first disk storage via the first cache; the pulling data records from the first data result comprising: in response to a data use request from a certain first execution stage, pulling data records from the first disk storage and storing the data records in a first data queue in the first cache corresponding to the certain first execution stage.

7. The method of claim 5, wherein, the pulling data records from the first data result comprising: in a single data pulling, storing the pulled data records in each first data queue.

8. The method of claim 7, wherein, each first data queue having a preset fixed cache space; the storing the pulled data records in each first data queue in a single data pulling comprising: In the first data queues, if any data queue does not have enough cache space to pull the next data record, the pulling is suspended until the data queue regains enough cache space due to data usage of the corresponding first execution stage. 9.An apparatus for optimizing execution of a query, comprising: a receiving module configured to receive a query statement, wherein the query statement comprises definitions of a plurality of common table expressions (CTEs) and a plurality of sub-queries referencing the CTEs; a generating module configured to generate a physical execution plan of the query statement, wherein the physical execution plan comprises a first execution operator generated for a first sub-query referencing a first CTE, and the first execution operator is configured to store a first data result corresponding to the first CTE into a first cache in a memory; an executing module configured to determine a plurality of first execution stages depending on the first CTE according to the first execution operator during execution of the physical execution plan, and share the first data result through the first cache between the plurality of first execution stages. 10.A computer program product comprising computer programs / instructions, which, when executed by a processor, implement the steps of the method of any one of claims 1-8.

11. A computing device comprising a memory and a processor, wherein: The memory stores executable codes, and the processor executes the executable codes to implement the method of any one of claims 1-8. The memory stores executable codes, and the processor executes the executable codes to implement the method of any one of claims 1-8.

Images