Routing SQL Statements to Elastic Compute Nodes Using Workload Classes

Abstract

To provide a technology for routing structured query language (SQL) statements to elastic compute nodes (ECNs) using workload classes within a distributed database environment.SOLUTION: Elastic compute nodes do not store persistent database tables. For example, a SQL statement can be received for execution within the distributed database environment. A workload class can be identified that matches properties of the SQL statement. Based on the workload class, a routing location hint can be obtained that identifies a set of elastic compute nodes. The SQL statement can then be routed to one of the identified elastic compute nodes for execution. Execution of the SQL statement at the elastic compute node can involve retrieving database data from other nodes which store persistent database tables.SELECTED DRAWING: Figure 4

Description

[Technical field]

[0001] The present application relates to distributed databases, and more particularly to techniques for routing Structured Query Language (SQL) statements to elastic compute nodes (ECN) using workload classes in a distributed database environment. [Background technology]

[0002] In a distributed database system, it is important to route a structured query language (SQL) statement to the appropriate worker node (e.g., to a worker node that can process the SQL statement most efficiently). Distributed database systems typically route SQL statements to worker nodes based on data affinity. For example, if an SQL statement uses data from a particular database table, the SQL statement may be routed to a worker node on which the particular database table is stored.

[0003] In the SAP® Hana® distributed database system, there exists an automatic SQL statement routing system called client-side statement routing. At SQL compilation time, the client-side statement routing mechanism determines which worker node has the required data set for a given SQL statement, and then caches the location of that worker node on the client side. When the same SQL statement is executed in the future, the client side automatically routes the SQL statement directly to the determined worker node. Although such automatic client-side statement routing can be efficient in some circumstances, it does not provide flexibility in controlling the distribution of the workload. Summary of the Invention [Means for solving the problem]

[0004] This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0005] Described herein are various techniques for routing Structured Query Language (SQL) statements to elastic compute nodes (ECNs) using workload classes in a distributed database environment. Elastic compute nodes do not store persistent database tables. For example, an SQL statement may be received for execution in a distributed database environment. A workload class matching properties of the SQL statement may be identified. Based on the workload class, a routing location hint may be obtained that identifies a set of elastic compute nodes. The SQL statement may then be routed to one of the identified elastic compute nodes for execution. Execution of the SQL statement on the elastic compute node may include retrieving database data from other nodes that store persistent database tables. [Brief description of the drawings]

[0006] [Figure 1] FIG. 1 illustrates an exemplary prior art environment for routing SQL statements, including a coordinator node and worker nodes. [Diagram 2] FIG. 1 illustrates an example environment for routing SQL statements, including a coordinator node and an elastic compute node. [Diagram 3] 1 is a diagram illustrating an example routing scenario for routing SQL statements to elastic compute nodes. [Figure 4]1 is a flowchart of an example process for routing Structured Query Language (SQL) statements to Elastic Compute Nodes (ECNs) using workload classes in a distributed database environment. [Diagram 5] 1 is a flowchart of an exemplary process for routing SQL statements to ECNs using workload classes in a distributed database environment, including executing the SQL statements in the ECN. [Figure 6] 1 is a schematic diagram of an exemplary computing system in which some described embodiments may be implemented. [Figure 7] FIG. 1 illustrates an exemplary cloud computing environment that may be used in conjunction with the techniques described herein. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0007] Overview The following description is directed to techniques for routing Structured Query Language (SQL) statements to Elastic Compute Nodes (ECNs) using workload classes in a distributed database environment. For example, an SQL statement may be received for execution in the distributed database environment. A workload class matching properties of the SQL statement may be identified. Based on the workload class, a routing location hint may be obtained that identifies a set of elastic compute nodes. The elastic compute nodes do not store persistent database tables. The SQL statement may then be routed to one of the identified elastic compute nodes for execution. Execution of the SQL statement on the elastic compute node may include retrieving database data from other nodes that store persistent database tables.

[0008] In some conventional solutions, users can route a particular statement to a particular node. For example, a SAP "route_to" hint can be entered for a given query to route the query to a given node. However, the "route_to" hint needs to be set for each query, which is time-consuming and inconvenient to use. In addition, this hint cannot be used to control the routing of workload classes.

[0009] Using the techniques described herein, workload class mapping may be used to control the routing of SQL statements to particular nodes, including elastic compute nodes. Using this technique, a user can define workload classes (and associated mappings and routing location hints) to control how particular groups of SQL statements are to be routed (e.g., based on properties such as session, or other properties). For example, a user can identify particular compute-intensive workloads and route them to particular elastic compute nodes, while routing other types of workloads (e.g., not compute-intensive) to other nodes (e.g., to coordinator nodes, worker nodes, and / or other types of nodes). This solution allows control over SQL statement routing and improves the efficiency of distributed database environments (e.g., reducing congestion, improving response times, etc.). In addition, the techniques described herein using workload class mapping provide advantages over the use of techniques such as "route_to" hints. For example, when an application needs to be changed (e.g., rewritten), modifying a query string that includes "route_to" may not be possible. In addition, the use of workload class mapping provides flexibility since users can only route queries when certain conditions are met (for example, queries can be routed if the application's name starts with "BW").

[0010] Elastic Compute Nodes In the technology described herein, various types of nodes participate in executing queries (e.g., SQL queries) in a distributed database environment. Generally, the nodes run software to perform data processing and / or storage tasks that run on the node's computing resources (e.g., processor resources, memory resources, and / or storage resources).

[0011] One node type is a coordinator node. A coordinator node can act as a default node to execute SQL queries and also provide persistent storage of data (e.g., database tables). A coordinator node can also provide routing location hint information (e.g., to client applications and / or client libraries).

[0012] Another node type is a worker node. Worker nodes maintain persistent storage of data (e.g., database tables). In some solutions, SQL queries are directed to a worker node that stores the data used by the SQL query.

[0013] Another node type is an elastic compute node. In the technology described herein, an elastic compute node receives SQL queries and performs compute tasks. However, unlike a worker node, an elastic compute node does not persistently store data used by SQL queries (i.e., an elastic compute node does not store persistent database tables). In some implementations, an elastic compute node can store replica tables (e.g., read-only replica tables) even if it does not store persistent database tables. Additionally, in some implementations, an elastic compute node stores a cache of intermediate query results (e.g., results obtained from query operations performed on other nodes where persistent database tables are stored).

[0014] Elastic compute nodes can provide advantages and improve efficiency in distributed database environments. For example, compute-intensive workloads can be directed to elastic compute nodes, thereby freeing up resources in other nodes (e.g., freeing up resources in coordinator nodes that may otherwise become overloaded and result in slow response times). Elastic compute nodes can use intermediate query results (e.g., from query operators performed in other nodes) to perform compute-intensive tasks that would otherwise cause performance issues in other nodes. Compute-intensive tasks can include query operators such as sort operators, join operators, and groups by operators. In some implementations, compute-intensive tasks include query operators other than table access operators.

[0015] Workload Class In the techniques described herein, workload classes are used to direct SQL statements to specific nodes. Specifically, a user defines a workload class that controls the routing behavior for a set of SQL statements. In some implementations, the workload classes are defined and modified using SQL statements.

[0016] A workload class is created by defining its name and its associated workload mapping and routing location hints. Below is an example of creating a workload class named "ECN_WC". For example, a user can enter the following exemplary SQL statement to create a workload class "ECN_WC". CREATE WORKLOAD CLASS “ECN_WC”

[0017] A workload mapping is then associated with the workload class. The workload mapping defines what types of SQL statements will be mapped to the workload class. Below is an example of creating a workload class mapping "ECN_WC_mapping" for the workload class "ECN_WC". For example, a user may enter the following exemplary SQL statement to create a workload class mapping "ECN_WC_mapping". CREATE WORKLOAD MAPPING “ECN_WC_mapping” WORKLOAD CLASS “ECN_WC” SET 'APPLICATION NAME'='BW*'

[0018] This workload class mapping "ECN_WC_mapping" maps applications whose names start with "BW" to the workload class "ECN_WC". Using this mapping, SQL statements initiated by applications whose names start with "BW" will be routed according to the workload class "ECN_WC".

[0019] In general, a workload class can have any number of workload mappings. The workload mappings can be based on various properties associated with the database environment, such as session properties. For example, the workload mappings can be based on properties (e.g., session variables and / or other types of properties) including one or more of the following: application properties (e.g., application properties associated with the application initiating the SQL statement), database object properties (e.g., schema names, table names, etc.), database user names (e.g., the user name of the user initiating the SQL statement), application user names, and / or application component names.

[0020] The ECN nodes that will be used to execute the workload class are then grouped using a group label. Below is an example of grouping two ECN nodes (called "ECN_node1" and "ECN_node2") and assigning the label "ECN_group1" to the group. For example, a user may enter the following exemplary SQL statement to create a group "ECN_group1": ALTER SYSTEM ALTER TABLE PLACEMENT LOCATION ECN_group1 SET (INCLUDE=>'ECN_node1, ECN_node2') WITH RECONFIGURE

[0021] Finally, a routing location hint for the workload class is defined. A routing location hint associates a given workload class with a given group of one or more ECN nodes. Below is an example of a routing location hint that associates the workload class "ECN_WC" with ECN nodes grouped by the label "ECN_group1". For example, a user may enter the following exemplary SQL statement to create a routing location hint for the workload class "ECN_WC". ALTER WORKLOAD CLASS “ECN_WC” SET 'ROUTING LOCATION HINT'='ECN_group1'

[0022] The routing location hint for a given workload class can be disabled or reenabled at any time. For example, a user can enter the following example SQL statement to disable the routing location hint for the "ECN_WC" workload class (and later reenable it or enable it with a different group of ECN nodes using the ALTER WORKLOAD CLASS statement described above): ALTER WORKLOAD CLASS “ECN_WC” UNSET 'ROUTING LOCATION HINT'

[0023] Using workload classes, users can define which workloads (e.g., groups of related SQL statements, such as those associated with a given application) will be processed by a particular elastic compute node. This allows users to offload workloads to elastic compute nodes (e.g., rather than being processed by a coordinator node). In addition, by using workload classes to route workloads, routing information is not included in the SQL statements. This allows routing to be changed for a set of SQL statements (e.g., defined by a workload mapping), even for SQL statements (or operators) that are compiled. In addition, routing can be changed for a set of SQL statements without the need to modify the SQL statements (e.g., application software does not need to be revised to include SQL statement routing details).

[0024] Exemplary Environment Using Coordinator and Worker Nodes In some prior art distributed database solutions, SQL routing is performed using a coordinator node and a worker node. Figure 1 is an exemplary prior art environment 100 for routing SQL statements, including a coordinator node and a worker node. Specifically, the exemplary prior art environment 100 illustrates a portion of a distributed database environment including a coordinator node 120, a worker node 130, and a worker node 140.

[0025] The exemplary prior art environment 100 includes a client application 110 and an associated client library 115. The client application 110 includes software resources for interacting with a distributed database system. The client application 110 may be implemented using various types of software resources (e.g., web browser resources, database client software, etc.) and may run on various types of hardware resources (e.g., computing devices such as desktops, servers, and laptops, virtual computing resources, cloud computing resources, etc.). The client application 110 uses the client library 115 to access the distributed database system. In some implementations, the client library 115 includes an application programming interface (API) for accessing the distributed database system. For example, the client library 115 may be implemented at least in part by an API such as Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), and / or SQL Database Connectivity (SQLDBC).

[0026] The exemplary prior art environment 100 illustrates a portion of a distributed database environment (e.g., a portion of an SAP Hana distributed database environment) in which SQL statements are routed to nodes based on which nodes have the data (e.g., one or more database tables) required to execute the SQL statements. Specifically, in the exemplary prior art environment, a set of persistent tables 122 (in this example, persistent tables t1, t2, and t3) is stored on a coordinator node 120, a set of persistent tables 132 (in this example, persistent tables t4 and t5) is stored on a worker node 130, and a set of persistent tables 142 (in this example, persistent tables t6 and t7) is stored on a worker node 140. When a client application 110 submits an SQL statement for execution, a client library 115 routes the SQL statement to a node that stores the data required to execute the SQL statement. As one example, if client library 115 has a SQL statement that uses data from persistent table t1, client library 115 will submit the SQL statement to coordinator node 120, as shown at 150. As another example, if client library 115 has a SQL statement that uses data from persistent table t6, client library 115 will submit the SQL statement to worker node 140, as shown at 155.

[0027] In some implementations, the client library 115 uses client-side statement routing to determine which node a given SQL statement should be sent to for execution (e.g., any of nodes 120, 130, or 140) based at least in part on which node stores the data (e.g., one or more database tables) required for execution. For example, the client library 115 can determine the node at SQL compilation time. The client library 115 can cache the determined node so that the same SQL statement can be automatically routed if it is encountered at a later time.

[0028] Example Environment Using Coordinator and Elastic Compute Nodes In the techniques described herein, SQL statement routing is performed in a distributed database environment using workload classification, including elastic compute nodes. In addition, an SQL statement may be routed to an elastic compute node for execution even if a table used by the SQL statement is not located on the elastic compute node.

[0029] 2 is an exemplary environment 200 for routing SQL statements, including a coordinator node and an elastic compute node. Specifically, the exemplary environment 200 illustrates a portion of a distributed database environment including a coordinator node 220, an elastic compute node 230, and an elastic compute node 240. In general, a distributed database environment can have any number of elastic compute nodes and / or any number of other nodes (e.g., worker nodes). The exemplary environment 200 illustrates how query routing and query execution occurs in a distributed database environment using elastic compute nodes.

[0030] The exemplary environment 200 includes a client application 210 and an associated client library 215. The client application 210 includes software resources for interacting with a distributed database system. The client application 210 may be implemented using various types of software resources (e.g., web server resources, database client software, etc.) and may run on various types of hardware resources (e.g., computing devices such as desktops, servers, and laptops, virtual computing resources, cloud computing resources, etc.). The client application 210 uses the client library 215 to access the distributed database system. In some implementations, the client library 215 includes an application programming interface (API) for accessing the distributed database system. For example, the client library 215 may be implemented at least in part by an API such as Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), and / or SQL Database Connectivity (SQLDBC). In some implementations, the client application 210 is a hosted application (e.g., a hosted web application) of the distributed database environment that provides database services to end-user clients (e.g., end-user clients running web browsers).

[0031] In the exemplary environment 200, a set of persistent tables 222 (persistent tables t1, t2, t3, t4, and t5) are stored on the coordinator node 220. When a client application 210 submits an SQL statement for execution, the client library 215 routes the SQL statement to one of the nodes for execution (e.g., to the coordinator node 220, the elastic compute node 230, or the elastic compute node 240). The client library 215 makes its routing decision based on various criteria (e.g., based on workload classification, current node utilization, etc.). For example, the client library 215 may receive an SQL statement that uses data from persistent table t4. The client library 215 may route the SQL statement to the elastic compute node 230 (e.g., based on workload classification and / or other criteria), as shown at 260, even if persistent table t4 is not stored on the elastic compute node 230. In this situation, elastic compute node 230 can retrieve data for executing the query from coordinator node 220 (which stores persistent table t4). In some implementations, elastic compute node 230 retrieves data for executing the query using distributed query processing interface 250.

[0032] In some implementations, execution of an SQL statement on an elastic compute node includes determining a set of query operators (e.g., operator, select operator from another group of operators, etc.) for the SQL statement. When a query operator needs to access data from a persistent table, the query operator may be executed on a node other than the elastic compute node (because the persistent table is not stored on the elastic compute node). In some implementations, the query operator is executed on a coordinator node or another type of node (other than the elastic compute node) where the persistent table is stored. In some implementations, a distributed query processing interface is used to execute the query operator on other nodes and provide results (e.g., intermediate query results) to the elastic compute node for further processing. For example, with reference to the exemplary environment 200, the client library 215 can submit the SQL statement to the elastic compute node 230 for execution, as shown at 260. The elastic compute node 230 can determine a set of query operators for the SQL statement. If one of the query operators (e.g., a filter operator) needs to access data in persistent table t4, the elastic compute node 230 uses the distributed query interface 250 to read the data from the coordinator node 220 (e.g., the elastic compute node 230 sends the query operator to the coordinator node 220 for execution of the query operator using persistent table t4 and receives intermediate query results from the coordinator node 220). The elastic compute node 230 receives the intermediate query results and performs additional query operators (e.g., a sort operator, a join operator, a group by operator, etc.) to complete the execution of the SQL statement. The elastic compute node 230 can send the results of executing the SQL statement back to the client library 215 (e.g., for display by the client application 210 or for sending to an end-user client). Cut.

[0033] In some implementations, each node has its own query compiler and query optimizer. For example, when the elastic compute node 230 receives an SQL statement from the client library 215, the elastic compute node 230 compiles the SQL statement. The query optimizer running on the elastic compute node 230 then decides which query operators to send to other nodes for processing (e.g., based on cost). For example, the query optimizer can send a filter query operator to the coordinator node 220 to retrieve intermediate query results using one of the persistent tables 222 stored on the coordinator node 220. The intermediate query results can be used on the elastic compute node 230 to execute other query operators of the SQL statement.

[0034] In some implementations, elastic compute nodes have local caches that store intermediate query results. For example, elastic compute node 230 has local cache 231 that stores intermediate query results for SQL statements executed on elastic compute node 230, and elastic compute node 240 has local cache 241 that stores intermediate query results for SQL statements executed on elastic compute node 240. The intermediate query results can be reused from the cache (e.g., without having to submit the query operator to coordinator node 220) if the same SQL statement (or the same query operator) is received again.

[0035] In the exemplary environment 200, the client library 215 determines where to submit the SQL statement for execution. To make the determination, the client library 215 uses workload class information. For example, when the client library 215 receives an SQL statement that needs to be routed, the client library 215 checks for a matching workload class (e.g., checks the workload class mappings for one or more workload classes to determine if one of the mappings matches the properties of the SQL statement). Upon finding a matching workload class, the client library 215 uses a routing location hint from the identified workload class to determine to which node the SQL statement should be submitted. For example, the routing location hint may identify an elastic compute node 240, and in response, the client library 215 may submit the SQL statement to the elastic compute node 240 for execution.

[0036] In some circumstances, the client library 215 selects a particular elastic compute node from several available elastic compute nodes for execution of a given SQL statement. This situation occurs when a workload class is determined for a given SQL statement, and the determined workload class has a routing location hint that identifies multiple elastic compute nodes. When this occurs, one elastic compute node is selected from the multiple elastic compute nodes associated with the routing location hint for execution of the given SQL statement. In some implementations, the selected elastic compute node is determined by randomly selecting one of the multiple elastic compute nodes associated with the routing location hint. For example, if the routing location hint for the determined workload class identifies elastic compute nodes 230 and 240, one of them may be selected randomly. In other implementations, the elastic compute node is determined by availability (e.g., current load) from among the multiple elastic compute nodes associated with the routing location hint (e.g., the elastic compute node with the lowest load is selected). If no elastic compute node is available for selection (e.g., if the elastic compute node is overloaded), the SQL statement may be routed to the coordinator node. In other implementations, a combination of these and / or other techniques is used.

[0037] Example SQL Statement Routing 3 is a diagram illustrating an example routing scenario 300 for routing SQL statements to elastic compute nodes. Specifically, in the example routing scenario 300, several operations are illustrated for routing SQL statements between the client library 215, the coordinator node 220, and the elastic compute nodes 230 and 240.

[0038] In a first operation, a new SQL statement (e.g., not previously seen in the client library 215) is routed from the client library 215 to the coordinator node 220, as shown at 310. At the coordinator node 220, the new SQL statement 312 is compiled to generate a compiled SQL plan 314. For example, because the client library 215 does not currently have a matching workload class for the new SQL statement 312 (e.g., there is no routing location hint for the new SQL statement), the new SQL statement 312 may be routed to the coordinator node 220 (e.g., the coordinator node 220 may be used as a default for routing the SQL statement), and the new SQL statement 312 uses a persistent table t1 located at the coordinator node 220. After the new SQL statement 312 is compiled, the compiled SQL plan 314 is stored in a cache located at the coordinator node. In addition, a workload class 332 matching the SQL statement 312 is stored in a workload class repository 330 at the coordinator node 220 (e.g., the workload class 332 may be created or may already exist). The new SQL statement 312 is then executed by the coordinator node 220 based on the compiled SQL plan 314.

[0039] In a second operation, the result of executing the new SQL statement 312 at the coordinator node 220 is sent back to the client library 215 along with the routing location (indicating the coordinator node 220). The routing location is stored in the client library 215 as a routing location hint in the client-side routing location hint cache 320. If the same SQL statement is received again at the client library 215 (e.g., based on some matching criteria such as the statement object identifier), the SQL statement will be routed according to the stored routing location hint in the client-side routing location hint cache 320 (the SQL statement will be routed to the coordinator node 220 according to the currently stored routing location hint).

[0040] In a third operation, an alter workload class command is executed at the coordinator node 220 to alter the routing location hints for the workload class 332 stored at the coordinator node 220. For example, the command may be received from a user (e.g., from a database administrator) as an SQL command (e.g., an ALTER WORKLOAD CLASS command). The command alters the routing location hints. In this example, the routing location hints are changed from the coordinator node 220 to the elastic compute nodes 230 and 240.

[0041] In a fourth operation, when the same SQL statement is received again at the client library 215, the client library will route the SQL statement to the coordinator node 220 (based on the routing location hints currently stored in the client-side routing location hint cache 320). The coordinator node 220 will execute the SQL statement. The coordinator node 220 will also determine that the workload class 332 (matching the SQL statement) has changed (based on the changed routing location hints from the third operation) and will send updated routing location hints for the workload class 332 back to the client library 215 along with the results of executing the SQL statement. Alternatively, rather than the coordinator node 220 executing the SQL statement in this operation, the coordinator node 220 can send updated routing location hints for the workload class 332 back to the client library 215 along with an indication that the client library 215 should execute the SQL statement using the updated routing location hints. The client library 215 will then reroute the SQL statement based on the updated routing location hints (in this example, the updated routing location hints will point to elastic compute nodes 230 and 240).

[0042] In a fifth operation, the client library 215, upon receiving the updated routing location hints, will update the routing location hints stored in its client-side routing location hint cache 320. In this example, the updated routing location hints identify elastic compute nodes 230 and 240.

[0043] In a sixth operation, when the same SQL statement is received again at client library 215, client library 215 will use the updated routing location hints from its client-side routing location hint cache 320 to route the SQL statement to one of the identified elastic compute nodes 230 or 240. For example, client library 215 will select elastic compute node 230 (e.g., based on the load of elastic compute nodes 230 and 240) and submit the SQL statement, as shown at 340.

[0044] In some implementations, the client library (e.g., client library 215) performs SQL statement routing based on prepared statements. In this case, the client library caches the prepared statements for reuse if the same prepared statement needs to be executed in the future. In addition, the routing location hints stored in the client-side routing location hint cache 320 are associated with the prepared statements. For example, the client library 215 can receive a routing location hint from the coordinator node 220, where the routing location hint is associated with a given prepared statement. The client library 215 can store the routing location hint in the client-side routing location hint cache 320 along with an indication of which prepared statement it is associated with. When the client library 215 receives an SQL statement that uses the same prepared statement, the client library 215 finds the routing location hint that matches the same prepared statement in the client-side routing location hint cache 320 and sends it to the indicated node (e.g., one of the elastic compute nodes 230 or 240) for execution.

[0045] A method for using workload classes to route SQL statements to ECN In the techniques described herein, a method may be provided for routing SQL statements to elastic compute nodes using workload classes in a distributed database environment. An exemplary method may be implemented using software and / or hardware resources. For example, the method may be implemented by one or more elements of the distributed database environment, such as a client application (e.g., client application 210), a client library (e.g., client library 215), a coordinator node (e.g., coordinator node 220), an elastic compute node (e.g., elastic compute node 230 or 240), and / or other elements of the distributed database environment.

[0046] 4 is a flow diagram of an example process 400 for routing SQL statements to elastic compute nodes using workload classes in a distributed database environment. At 410, an SQL statement is received for execution in the distributed database environment.

[0047] At 420, a workload class that matches the properties of the SQL statement is identified. The workload class is identified from a number of available workload classes. For example, the workload class may be identified by matching one or more properties (e.g., application name, user name, etc.) to a workload mapping of the workload class.

[0048] A routing location hint is retrieved from the identified workload class at 430. The routing location hint identifies one or more elastic compute nodes associated with the workload class.

[0049] At 440, the SQL statement is routed to a selected elastic compute node from among the one or more elastic compute nodes identified by the routing location hint. The SQL statement is executed by the selected elastic compute node. In addition, the selected elastic compute node does not store any persistent database tables. Therefore, if the execution of the SQL statement at the elastic compute node needs to access any persistent table data, the persistent table data needs to be accessed via a different node (e.g., a coordinator node or another node of the distributed database environment that is not an elastic compute node). For example, the elastic compute node can send at least a portion of the SQL statement (e.g., one or more query operators) to the different node to retrieve database data (e.g., in the form of intermediate results) from persistent database tables stored in the different node.

[0050] 5 is a flow diagram of an example process 500 for routing SQL statements to elastic compute nodes using workload classes in a distributed database environment. At 510, an SQL statement is received for execution in the distributed database environment.

[0051] At 520, a workload class that matches the properties of the SQL statement is identified. The workload class is identified from a number of available workload classes. For example, the workload class may be identified by matching one or more properties (e.g., application name, user name, etc.) to a workload mapping of the workload class.

[0052] A routing location hint is retrieved from the identified workload class at 530. The routing location hint identifies one or more elastic compute nodes associated with the workload class.

[0053] At 540, the SQL statement is routed to a selected elastic compute node from among the one or more elastic compute nodes identified by the routing location hints. The SQL statement is routed to the selected elastic compute node even if the persistent database table is not stored on the selected elastic compute node.

[0054] At 550, the SQL statement is executed by the selected elastic compute node. The SQL statement uses database data from persistent database tables. Because the elastic compute node does not store the persistent database tables, the database data is obtained from other nodes in the distributed database environment. For example, the elastic compute node can submit at least a portion of the SQL statement (e.g., one or more query operators) to a different node to retrieve database data (e.g., in the form of intermediate results) from persistent database tables stored in the different node.

[0055] In some implementations, database data read by an elastic compute node from other nodes is stored in the elastic compute node (e.g., as a result cache, such as an intermediate result cache). If the same SQL statement is received again at the elastic compute node, the elastic compute node can obtain the database data from the cache rather than reading the database data from other nodes.

[0056] Computing Systems 6 illustrates a schematic example of a suitable computing system 600 in which the described innovations may be implemented. The computing system 600 is not intended to suggest any limitation as to scope of use or functionality, as the innovations may be implemented in a variety of general purpose or special purpose computing systems.

[0057] Referring to FIG. 6, a computing system 600 includes one or more processing units 610, 615 and memories 620, 625. In FIG. 6, this basic configuration 630 is included within the dashed line. The processing units 610, 615 execute computer-executable instructions. The processing units can be general-purpose central processing units (CPUs), processors in application specific integrated circuits (ASICs), or any other type of processor. In a multi-processing system, multiple processing units execute computer-executable instructions to increase processing power. For example, FIG. 6 shows a central processing unit 610 as well as a graphics processing unit or co-processing unit 615. The tangible memories 620, 625 may be volatile memories (e.g., registers, cache, RAM) that the processing units can access, non-volatile memories (e.g., ROM, EEPROM, flash memory, etc.), or some combination of the two. The memories 620, 625 store software 680 in the form of computer-executable instructions suitable for execution by a processing unit, which implements one or more innovations described herein.

[0058] The computing system may have additional functionality. For example, computing system 600 includes storage 640, one or more input devices 650, one or more output devices 660, and one or more communication connections 670. An interconnection mechanism (not shown), such as a bus, controller, or network, interconnects the components of computing system 600. Typically, operating system software (not shown) provides an operating environment for other software executing within computing system 600 and coordinates the activities of the components of computing system 600.

[0059] Tangible storage 640 may be removable or non-removable and includes magnetic disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium usable to store information in a non-transitory manner and accessible within computing system 600. Storage 640 stores instructions for software 680 that implements one or more innovations described herein.

[0060] The input device(s) 650 may be a touch input device such as a keyboard, mouse, pen, or trackball, a voice input device, a scanning device, or another device that provides input to the computing system 600. For video encoding, the input device 650 may be a camera, a video card, a TV tuner card, or similar device that accepts video input in analog or digital form, or a CD-ROM or CD-RW that reads video samples into the computing system 600. The output device(s) 660 may be a display, a printer, speakers, a CD writer, or another device that provides output from the computing system 600.

[0061] The communications connection 670 enables communication over a communications medium to another computing entity. The communications medium conveys information such as computer-executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communications media may use electrical, optical, RF, or other carriers.

[0062] The innovations may be described in the general context of computer-executable instructions, such as those contained in program modules, being executed in a computing system on a target real or virtual processor. Generally, program modules include routines, programs, libraries, objects, classes, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The functionality of the program modules may be combined or divided among program modules as described in various embodiments. The computer-executable instructions for the program modules may be executed in a local computing system or in a distributed computing system.

[0063] The terms "system" and "device" are used interchangeably herein. Unless otherwise indicated in the context, neither term implies any limitation as to the type of computing system or computing device. In general, a computing system or computing device may be local or distributed and may include any combination of software and dedicated and / or general-purpose hardware that implements the functions described herein.

[0064] For purposes of presentation, the detailed description uses terms such as "determine" and "use" to describe computer operations within a computing system. These terms are high-level abstractions of operations performed by a computer and should not be confused with human actions. The actual computer operations that correspond to these terms will vary depending on the implementation.

[0065] Cloud Computing Environment 7 illustrates an exemplary cloud computing environment 700 in which the described techniques may be implemented. The cloud computing environment 700 includes a cloud computing service 710. The cloud computing service 710 may include various types of cloud computing resources, such as computer services, data storage repositories, database resources, networking resources, etc. The cloud computing service 710 may be centrally located (e.g., provided by a data center of an enterprise or organization) or distributed (e.g., provided by various computing resources located in different locations, such as different data centers, and / or located in different cities or countries).

[0066] Cloud computing services 710 are utilized by various types of computing devices (e.g., client computing devices), such as computing devices 720, 722, and 724. For example, the computing devices (e.g., 720, 722, and 724) can be computers (e.g., desktop computers or laptop computers), mobile devices (e.g., tablet computers or smartphones), or other types of computing devices. For example, the computing devices (e.g., 720, 722, and 724) can utilize cloud computing services 710 to perform computing operations (e.g., data processing, data storage, etc.).

[0067] Exemplary Implementations Although certain operations of the disclosed methods have been described in a particular order for convenience of presentation, it should be understood that this description scheme encompasses rearrangements unless a particular ordering is required by specific language described below. For example, operations described as sequential may in some cases be rearranged or performed simultaneously. Moreover, for simplicity, the accompanying figures may not show the various ways in which the disclosed methods can be used in conjunction with other methods.

[0068] Any of the disclosed methods may be implemented as computer-executable instructions or computer program products stored in one or more computer-readable storage media and executed on a computing device (i.e., any available computing device, including a smartphone or other mobile device that includes computing hardware). A computer-readable storage medium is a tangible medium accessible within a computing environment (one or more optical media disks, such as a DVD or CD, a volatile memory (such as a DRAM or SRAM), or a non-volatile memory (such as a flash memory or hard drive). As an example, referring to FIG. 6, a computer-readable storage medium includes memory 620 and 625, and storage 640. The term computer-readable storage medium does not include signals and carrier waves. In addition, the term computer-readable storage medium does not include communication connections, such as 670.

[0069] Any of the computer-executable instructions for implementing the disclosed techniques, as well as any data created and used during the implementation of the disclosed embodiments, may be stored in one or more computer-readable storage media. The computer-executable instructions may be part of, for example, a dedicated software application, or a software application accessed or downloaded via a web browser, or other software application (such as a remote computing application). Such software may be executed, for example, on a single local computer (e.g., any suitable commercially available computer) using one or more networked computers, or in a networked environment (e.g., via the Internet, a wide area network, a local area network, a client / server network (such as a cloud computing network), or other such network).

[0070] For clarity, only certain selected aspects of the software-based implementation are described. Other details well known in the art are omitted. For example, it should be understood that the disclosed technology is not limited to any particular computer language or program. By way of example, the disclosed technology may be implemented by software written in C++, Java, Perl, or any other suitable programming language. Similarly, the disclosed technology is not limited to any particular computer or any particular type of hardware. Specific details of suitable computers and hardware are well known and need not be described in detail in this disclosure.

[0071] Moreover, any of the software-based embodiments (e.g., including computer-executable instructions for causing a computer to perform any of the disclosed methods) may be uploaded, downloaded, or remotely accessed through appropriate communications means including, for example, the Internet, the World Wide Web, an intranet, a software application, a cable (including fiber optic cable), a magnetic communication section, an electromagnetic communication section (including RF, microwave, and infrared communication sections), an electronic communication section, or other such communications means.

[0072] The disclosed methods, apparatus, and systems should not be considered limiting in any way. Instead, the disclosure is directed to all novel and unobvious features and aspects of the various disclosed embodiments, both alone and in various combinations and subcombinations with each other. The disclosed methods, apparatus, and systems are not limited to any particular aspect or feature, or combination thereof, nor do the disclosed embodiments require that any one or more particular advantages exist or problems be solved.

[0073] Techniques from any example can be combined with techniques described in any one or more of the other examples. In view of the many possible embodiments to which the principles of the disclosed technology may be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be construed as limitations on the scope of the disclosed technology. Rather, the scope of the disclosed technology includes that which is covered by the scope and spirit of the appended claims. [Explanation of symbols]

[0074] 100 Prior Art Environment 110 Client Applications 115 Client Library 120 coordinator nodes 122, 132, 142 Persistent table set 130, 140 worker nodes t1, t2, t3, t4, t5, t6, t7 Persistent Tables 200 Example Environment 210 Client Applications 215 Client Library 220 Coordinator Node 222 Persistent Table Set 230, 240 Elastic Compute Nodes 231, 241 Local cache 250 Distributed Query Processing Interface 300 Routing Scenario 312 New SQL Statement 314 Compiled SQL Plans 320 Client-side routing location hint cache 330 Workload Class Repository 332 Workload Class 400, 500 processes 600 Computing Systems 610 Processing Unit 615 Graphics Processing Unit or Co-Processing Unit 620, 625 Memory 640 Storage 650 Input Devices 660 Output Device 670 Communication Connection 680 Software 700 Cloud Computing Environment 710 Cloud Computing Services 720, 722, 724 Computing devices

Claims

1. 1. A method, performed by one or more computing devices, for routing structured query language (SQL) statements using workload classes to elastic compute nodes (ECN) in a distributed database environment, the method comprising: receiving an SQL statement for execution within the distributed database environment; identifying a workload class from a plurality of available workload classes, the identified workload class matching one or more properties of the SQL statement; retrieving a routing location hint from the identified workload class, the routing location hint identifying one or more elastic compute nodes associated with the workload class; routing the SQL statement to a selected elastic compute node of the one or more elastic compute nodes identified by the routing location hint, where the SQL statement is executed by the selected elastic compute node, and the selected elastic compute node does not store persistent database tables necessary for execution of the SQL statement; Including, The method, wherein executing the SQL statement on the selected elastic compute node includes retrieving database data from other nodes that store the persistent database table.

2. 2. The method of claim 1 , wherein the distributed database environment includes a coordinator node and the one or more elastic compute nodes, and the SQL statement uses data from persistent database tables stored on the coordinator node that are necessary for execution of the SQL statement.

3. The method of claim 1 , wherein the workload classes are created by users using SQL commands.

4. 2. The method of claim 1, further comprising: executing an SQL statement on a selected elastic compute node after the SQL statement is routed to the selected elastic compute node, the method comprising: submitting at least a portion of the SQL statement to another node for execution, the other node storing persistent database tables necessary for execution of the SQL statement; receiving from the other node a result of execution of the at least a portion of the SQL statement; A method comprising:

5. 2. The method of claim 1, further comprising: executing an SQL statement on a selected elastic compute node after the SQL statement is routed to the selected elastic compute node, the method comprising: determining a set of query operators for executing the SQL statement; submitting at least one query operator of the set of query operators to another node for execution, the other node storing persistent database tables necessary for execution of the at least one query operator; executing all remaining query operators other than the at least one query operator on the selected elastic compute node; A method comprising:

6. receiving, from the other node, intermediate query results from execution of the at least one query operator at the other node; 6. The method of claim 5, further comprising:

7. 2. The method of claim 1, further comprising: executing an SQL statement on a selected elastic compute node after the SQL statement is routed to the selected elastic compute node, the method comprising: determining a set of query operators for executing the SQL statement; determining that one or more of the set of query operators accesses data in a persistent database table located at another node; in response to determining that the one or more query operators access data in a persistent database table located at another node, submitting the one or more query operators to the other node for execution; receiving a result of executing the one or more query operators from the other node; executing, on the selected elastic compute node, all remaining query operators other than the one or more query operators; A method comprising:

8. the one or more computing devices include a client library and / or a coordinator node; 2. The method of claim 1, wherein the coordinator node is a node capable of acting as a default node to execute SQL statements and capable of storing persistent database tables required for the execution of the SQL statements.

9. determining which elastic compute node to select from the one or more elastic compute nodes based at least in part on a current load of the one or more elastic compute nodes; The method of claim 1, further comprising:

10. The method of claim 1 , wherein the selected elastic compute node retrieves database data necessary to execute the SQL statement from other nodes that store persistent database tables in the distributed database environment.

11. The method of claim 10 , wherein the selected elastic compute node retrieves the database data from the other nodes via a distributed query processing interface.

12. A processor, and Memory and one or more computing devices that execute a computer-executable program to perform operations for routing structured query language (SQL) statements using workload classes to elastic compute nodes (ECN) in a distributed database environment, the operations comprising: receiving an SQL statement for execution within the distributed database environment; an act of identifying a workload class from a plurality of available workload classes, the identified workload class matching one or more properties of the SQL statement; an operation of retrieving a routing location hint from the identified workload class, the routing location hint identifying one or more elastic compute nodes associated with the workload class; routing the SQL statement to a selected elastic compute node of the one or more elastic compute nodes identified by the routing location hint; Including, the SQL statement is executed by the selected elastic compute node; the selected elastic compute node does not store a persistent database table required to execute the SQL statement; executing the SQL statement on the selected elastic compute node includes retrieving database data from another node that stores the persistent database table; One or more computing devices.

13. 13. An elastic compute node operable to execute SQL statements selected and routed by one or more computing devices as recited in claim 12, comprising: the SQL statement uses one or more persistent database tables that are not stored on the selected elastic compute node, and database data required to execute the SQL statement is obtained from other nodes in the distributed database environment; an operation of sending at least a portion of the SQL statement to one of the other nodes for execution, the other node storing persistent database tables necessary for execution of at least a portion of the SQL statement; receiving from the other node a result of execution of the at least a portion of the SQL statement; An elastic compute node that performs operations including:

14. storing the received execution result of the at least a portion of the SQL statement in a cache located on the selected elastic compute node. and reusing the stored results when the same SQL statement is received at the elastic compute node. The elastic compute node of claim 13.

15. 13. An elastic compute node operable to execute SQL statements selected and routed by one or more computing devices as recited in claim 12, comprising: the SQL statement uses one or more persistent database tables that are not stored on the selected elastic compute node, and database data required to execute the SQL statement is obtained from other nodes in the distributed database environment; determining a set of query operators for executing the SQL statement; an operation of sending at least one query operator of the set of query operators to one of the other nodes for execution, the other node storing persistent database tables necessary for the execution of the at least one query operator; receiving, from the other node, intermediate query results from execution of the at least one query operator at the other node; executing, on the selected elastic compute node, all remaining query operators other than the at least one query operator; An elastic compute node that performs operations including:

16. 13. The one or more computing devices of claim 12, wherein the selected elastic compute node retrieves database data from other nodes that store the persistent database table via a distributed query processing interface.

17. One or more computer-readable storage media having stored thereon a computer-executable program for causing one or more computing devices to perform operations for routing structured query language (SQL) statements using a workload class to elastic compute nodes (ECN) in a distributed database environment, the operations comprising: receiving an SQL statement for execution within the distributed database environment; an act of identifying a workload class from a plurality of available workload classes, the identified workload class matching one or more properties of the SQL statement; an operation of retrieving a routing location hint from the identified workload class, the routing location hint identifying one or more elastic compute nodes associated with the workload class; routing the SQL statement to a selected elastic compute node of the one or more elastic compute nodes identified by the routing location hint; Including, the SQL statement is executed by the selected elastic compute node; the selected elastic compute node does not store a persistent database table required to execute the SQL statement; executing the SQL statement on the selected elastic compute node includes retrieving database data from another node that stores the persistent database table; One or more computer-readable storage media.

18. an operation of executing the SQL statement, the SQL statement using one or more persistent database tables that are not stored on the selected elastic compute node, and database data required to execute the SQL statement is obtained from other nodes of the distributed database environment; and further storing a computer-executable program for causing the selected elastic compute node to perform the the act of executing the SQL statement comprising: an operation of sending at least a portion of the SQL statement to one of the other nodes for execution, the other node storing persistent database tables necessary for execution of the at least a portion of the SQL statement; receiving from the other node a result of execution of the at least a portion of the SQL statement; 20. One or more computer readable storage media as recited in claim 17, comprising:

19. The operation, storing the received execution result of the at least a portion of the SQL statement in a cache located on the selected elastic compute node. and wherein the stored results are reused when the same SQL statement is received at the elastic compute node.

20. One or more computer-readable storage media as recited in claim 18.

20. an operation of executing the SQL statement, the SQL statement using one or more persistent database tables that are not stored on the selected elastic compute node, and database data required to execute the SQL statement is obtained from other nodes of the distributed database environment; and further storing a computer-executable program for causing the selected elastic compute node to perform the the act of executing the SQL statement comprising: determining a set of query operators for executing the SQL statement; an operation of submitting at least one query operator of the set of query operators to a coordinator node for execution, the coordinator node storing persistent database tables necessary for execution of the at least one query operator; receiving, from the coordinator node, intermediate query results from execution of the at least one query operator at the coordinator node; storing the intermediate query results in a cache on the selected elastic compute node; executing, on the selected elastic compute node, all remaining query operators other than the at least one query operator; 20. One or more computer readable storage media as recited in claim 17, comprising:

Images