Executing stored procedures at parallel databases

The present invention extends to methods, systems, and computer program products for executed stored procedures at parallel databases. Stored procedures are transformed so that execution of the stored procedure is split between a standalone database server and a parallel database coordinator. Execution of the stored procedure is initiated at the standalone database server. At execution time, control-flow statements, variable assignment, expression evaluation, etc., are handled by the standalone database server. SQL statements are passed from the standalone database server to the database for the execution. Results from executed SQL statements can be returned to the standalone database server or to a client. The parallel database coordinator can be added as a linked server to the standalone database server. In some embodiments, a session token is used to share session state between different parties.

CROSS-REFERENCE TO RELATED APPLICATIONS

Not Applicable.

BACKGROUND

Background and Relevant Art

Computer systems and related technology affect many aspects of society. Indeed, the computer system's ability to process information has transformed the way we live and work. Computer systems now commonly perform a host of tasks (e.g., word processing, scheduling, accounting, etc.) that prior to the advent of the computer system were performed manually. More recently, computer systems have been coupled to one another and to other electronic devices to form both wired and wireless computer networks over which the computer systems and other electronic devices can transfer electronic data. Accordingly, the performance of many computing tasks is distributed across a number of different computer systems and/or a number of different computing environments.

In some environments, queries are issued against a corpus of data to facilitate targeted information retrieval from the corpus of data. A user (or even a computer system) formulates a query using constructs from a query language. A query language typically includes a number of constructs that can be grouped into different combinations to express a logical intent for retrieving data. The query is issued to a data management system for processing. The data management system translates the query into a corresponding set of compatible physical operations (sometimes and hereinafter referred to as a “query plan”) for realizing the expressed logical intent. The query plan can then be executed to retrieve data from the corpus of data in accordance with the expressed logical intent. Retrieved data can be returned to the query issuer.

For example, SQL can be used to formulate a query for retrieving data from a relational database. The query is issued to a database management system that controls access to the relational database. The database management system translates the query into a query plan. The query plan is then executed to retrieve data from the relational database. The retrieved database data can be returned to the query issuer.

Some database systems are standalone (or single node) database systems where all data and optimization data is physically stored at the same machine. Other database systems are parallel database systems. In a parallel database system, database storage is spread across a number of compute nodes. Each compute node stores one or more portions of a database locally. Other modules (e.g., at a control node) abstract the distributed nature of the database from users such that it appears as a single unified database. As such, in a parallel database system, data relevant to a query as well as data used for query plan optimization can be spread out across a number of different nodes.

Supporting the execution of batched and stored procedures against a parallel database has at least a number of difficulties and/or inefficiencies. At least one difficulty is preserving equivalent single system behavior within a parallel database execution environment. The same behavior can be implemented at each compute node in a parallel database. However, implementing the same behavior at each compute can result in duplicated effort and performance of redundant operations.

BRIEF SUMMARY

The present invention extends to methods, systems, and computer program products for executing stored procedures at parallel databases. A parallel database coordinator is configured to issue SQL statements against a parallel database. The parallel database includes a plurality of database partitions spread across a plurality of compute nodes. The parallel database coordinator is also connected to an instance of standalone database server.

The parallel database coordinator receives a request to create a stored procedure. The creation request includes a name and stored procedure statements. The stored procedure is to include one or more SQL statements. The stored procedure can optionally also include at least one other non-SQL statement (e.g., control flow, variable assignment, expression evaluation, etc.). The parallel database coordinator transforms the stored procedure statements so that the one or more SQL statements are passed backed to the parallel database coordinator when the stored procedure is executed. The request to create the stored procedure is then forwarded to the standalone database server. The standalone database server creates and stores the transformed stored procedure.

Subsequently, the parallel database coordinator receives an execution request from a client. The execution request requests execution of the stored procedure. The parallel database coordinator forwards the execution request to the standalone database server. The standalone database server executes the transformed stored procedure. During execution at the standalone database server, the transformed stored procedure sends a request to execute a SQL statement (from among the one or more SQL statements) to the parallel database coordinator. The parallel database coordinator receives the request to execute the SQL statement form the standalone database server

The parallel database coordinator executes the SQL statement against the parallel database. The parallel database coordinator receives results from executing the SQL statement against the parallel database. Depending on the type of the SQL statement, the parallel database coordinator returns the results to the standalone database server or to the client. When appropriate, the standalone database server receives the results from the parallel database coordinator. The standalone database server uses the results during execution of the at least one other non-SQL statement at the standalone database server.

Accordingly, execution of statements in the transformed stored procedure is split between the parallel database coordinator and the standalone database server. The parallel database coordinator executes SQL statements while the standalone database server handles other non-SQL statements, such as, flow control statements, variable assignments, expression evaluation, etc.

DETAILED DESCRIPTION

The present invention extends to methods, systems, and computer program products for executing stored procedures at parallel databases. A parallel database coordinator is configured to issue SQL statements against a parallel database. The parallel database includes a plurality of database partitions spread across a plurality of compute nodes. The parallel database coordinator is also connected to an instance of standalone database server.

The parallel database coordinator receives a request to create a stored procedure. The stored procedure includes one or more SQL statements. The stored procedure can optionally also include at least one other non-SQL statement (e.g., control flow, variable assignment, expression evaluation, etc.). The parallel database coordinator transforms the stored procedure so that the one or more SQL statements are passed backed to the parallel database coordinator when the stored procedure is executed. The request to create the stored procedure is then forwarded to the standalone database server. The standalone database server creates and stores the transformed stored procedure.

Subsequently, the parallel database coordinator receives an execute request from a client. The execution request requests execution of the stored procedure. The parallel database coordinator forwards the execution request to the standalone database server. The standalone database server executes the transformed stored procedure. During execution at the standalone database server, the transformed stored procedure sends a request to execute a SQL statement (from among the one or more SQL statements) to the parallel database coordinator. The parallel database coordinator receives the request to execute the SQL statement form the standalone database server

The parallel database coordinator executes the SQL statement against the parallel database. The parallel database coordinator receives results from executing the SQL statement against the parallel database. Depending on the type of the SQL statement, the parallel database coordinator returns the results to the standalone database server or to the client. When appropriate, the standalone database server receives the results from the parallel database coordinator. The standalone database server uses the results to execute the at least one other non-SQL statement at the standalone database server.

Accordingly, execution of statements in the transformed stored procedure is split between the parallel database coordinator and the standalone database server. The parallel database coordinator executes SQL statements while the standalone database server handles other non-SQL statements, such as, flow control statements, variable assignments, expression evaluation, etc.

A cloud computing model can be composed of various characteristics such as, for example, on-demand self-service, broad network access, resource pooling, rapid elasticity, measured service, and so forth. A cloud computing model can also expose various service models, such as, for example, Software as a Service (“SaaS”), Platform as a Service (“PaaS”), and Infrastructure as a Service (“IaaS”). A cloud computing model can also be deployed using different deployment models such as private cloud, community cloud, public cloud, hybrid cloud, and so forth. In this description and in the claims, a “cloud computing environment” is an environment in which cloud computing is employed.

In general, embodiments of the invention transform a stored procedure so that execution of the stored procedure is split between a standalone database server (e.g., a shell database) and a parallel database coordinator (e.g., a parallel data warehouse (PDW) engine). Execution of the stored procedure is initiated at the standalone database server. At execution time, control-flow statements, variable assignment, expression evaluation, etc., are handled by (or centralized at) the standalone database server. SQL statements (e.g., SELECT, CREATE, DELETE, UPDATE, INSERT, etc.) are passed from the standalone database server to the parallel database coordinator for the execution. Results from executed SQL statements can be returned to the standalone database server or to a client. The parallel database coordinator can be added as a linked server to the standalone database server. Statements, such as, for example, OPENQUERY, EXECUTE AT, can be used to pass SQL from the standalone database server to the parallel database coordinator. In some embodiments, a session token is used to share session state between different parties.

FIGS. 1A and 1Billustrates an example computer architecture100that facilitates crating and executing stored procedures at parallel databases. Referring toFIG. 1A, computer architecture100includes client101, parallel database coordinator102, standalone database server103, database141, and storage107. Each of client101, parallel database coordinator102, standalone database server103, database141, and storage107can be connected to one another over (or be part of) a network, such as, for example, a Local Area Network (“LAN”), a Wide Area Network (“WAN”), and even the Internet. Accordingly, client101, parallel database coordinator102, standalone database server103, database141, and storage107as well as any other connected computer systems and their components, can create message related data and exchange message related data (e.g., Internet Protocol (“IP”) datagrams and other higher layer protocols that utilize IP datagrams, such as, Transmission Control Protocol (“TCP”), Hypertext Transfer Protocol (“HTTP”), Simple Mail Transfer Protocol (“SMTP”), etc. or using other non-datagram protocols) over the network.

Referring now toFIG. 1A, client101can be a database client. Client101can provide a user interface for interacting with data stored in database131. Client101can send requests to create stored procedures to parallel database coordinator102. Stored procedure creation requests can include a name to use for the stored procedure and stored procedure statements that are to be executed.

Parallel database coordinator102(e.g., a Parallel Data Warehouse (PDW) engine) can receive requests from client101. When a stored procedure creation request is received, procedure transformation module104can transform stored procedure statements of the request. The transformation can cause any SQL statements in the stored procedure statements to be returned to parallel database coordinator102for processing when the stored procedure is executed. Parallel database coordinator102can then send the stored procedure creation request with the name and the transformed stored procedure statements to standalone database server103.

Standalone database server130(e.g., a shell database) can receive the stored procedure creation request. Stored procedure creation module103can create an executable procedure from the transformed stored procedure statements. Standalone database server130can store the name along with the executable procedure in storage107.

Database131includes database portions131A,131B, and131C and compute nodes121,122, and123. Compute nodes121,122, and123can be configured in a shared-nothing architecture. As such, each of compute nodes121,122, and123maintain separate memory (e.g., RAM) and separate storage resources (e.g., disk drives) from other nodes. Database131is distributed across computer node121,122, and123, which maintain database portions131A,131B, and131C respectively. Parallel database coordinator102can include abstraction hardware and/or software components that abstract the distributed nature of database131. Parallel database coordinator109can use the abstraction hardware and/or software components to present database131as a standalone database to client101. Thus, client101can interact with database131using commands compatible with a standalone database (including submitting queries).

FIG. 2illustrates a flow chart of an example method200for creating and executing stored procedures at parallel databases. Method200will be described with respect to the components and data of computer architecture100.

Method200includes sending a creation request to create a stored procedure to a parallel database coordinator, the creation request including a name and stored procedure statements, the stored procedure to include one or more SQL statements (201). The stored procedure can also optionally include at least one non-SQL statement. For example, client101can send created request111to parallel database coordinator102. Create request111contains name112and stored procedure statements113. Name112indicates the name to be given to the stored procedure. Stored procedure statements113are to be used to create an executable procedure. Stored procedure statements113can include SQL statements and potentially also non-SQL statements (e.g., control flow statements, variable assignments, expression evaluations, etc.). Method200includes receiving the creation request from the client (202). For example, parallel database coordinator102can receive create request111from client101.

Method200includes transforming the stored procedure statements so that the one or more SQL statements are passed backed to the parallel database coordinator when the stored procedure is executed (203). For example, procedure transformation module104can transform stored procedure statements113into transformed stored procedure statements113T. Any SQL statements in stored procedure statements113can be transformed in transformed stored procedure statements113T so that the SQL statements are passed back to parallel database coordinator103during execution.

FIGS. 4A-4Fdepict various transforms that facilitate transferring SQL statements back to a parallel database coordinator. In some embodiments, SELECT statements are transformed using either an EXECUTE statement or an OPENQUERY statement. A parallel database coordinator can be added into stored procedure statements as a linked database (e.g., “[LINKED DB]”) so that EXECUTE or OPENQUERY statements are passed back to the parallel database coordinator during execution.

FIG. 4Adepicts two possible transformations for SELECT query without any parameters.FIG. 4Bdepicts a possible transformation for SELECT query with parameters.FIG. 4Cdepicts two possible transformations for SELECT query with variable assignments but no parameters.FIG. 4Ddepicts a possible transformation for scalar subquery without parameters.FIG. 4Edepicts a possible transformation for a scalar query within a control flow statement.FIG. 4Fdepicts a possible transformation for a SELECT query with an INTO statement without any parameters.

Any number of other transformations is also possible. In some embodiments, transformations can be facilitated by extending the grammar accepted by a parallel database coordinator's parser. In other embodiments, OPENQUERY can be extended to accept parameters.

Returning toFIG. 2, method200includes forwarding a further creation request to a standalone database server, the further creation request including the name and the transformed stored procedure statements (204). For example, parallel database coordinator102can forwards create request114to standalone database server103. As depicted, create request114includes name112and transformed stored procedure statements113T. Method200includes receiving the further creation request from the parallel database coordinator (205). For example, standalone database server103can receive created request114.

Method200includes creating and storing a stored procedure from the transformed stored procedure statements (206). For example, stored procedure creation module106can create executable procedure116from transformed stored procedure statements113T. Executable procedure116includes executable code representing SQL statements117and potentially also representing non-SQL statements118. Standalone database server103can store executable procedure116along with name112in storage107.

Turning toFIG. 1B, client101can also send requests to execute stored procedures to parallel database coordinator102. Stored procedure execution requests can include the name of a stored procedure that is to be executed. Parallel database coordinator102can receive the stored procedure execution request from client101. In response, parallel database coordinator102can forward the stored procedure execution request, including the name of a stored procedure that is to be executed, to standalone database server103.

When appropriate, session correlation module152can generate a session token. Parallel database coordinator102can attach the session token to the forwarded stored procedure execution request. The session token can be used to share session state the client may have, including transactions, database context, etc. as well as authorize the request without having to store and pass user credentials to parallel database coordinator when the stored procedure is subsequently executed.

Standalone database server103can receive the stored procedure execution request from parallel database coordinator102. Procedure execution module153can refer to store107to identify a stored procedure having the included name. The stored procedure can be a stored procedure created from transformed stored procedure statements such that any SQL statements are passed to parallel database coordinator102during execution. Procedure execution module153can initiate execution of the stored procedure at standalone database server103.

During execution, non-SQL statements (e.g., control flow, variable assignments, expression evaluation, etc.) are executed at standalone database server103. When SQL statements (e.g., enclosed in EXECUTE AT or OPENQUERY) are encountered, the SQL statements are forwarded to parallel database coordinator102(based on parallel database coordinator102being a linked database). When appropriate, a session token (previously received from parallel database coordinator102) can be attached to any SQL statement passed to parallel database coordinator102.

As such, during execution of a stored procedure, parallel database coordinator102can receive SQL statements, possibly along with a session token, from standalone database server103. When a session token is received, session correlation module152can use the session token to align execution of the SQL statement with state for client101. That is, the SQL statement can be executed under the same session as the stored procedure execution request originally received from client101. SQL statement processor151can execute the SQL statement to submit a query to database131. Database131can return query results back to parallel database coordinator102.

The query results can be returned to standalone database server103or can be returned to client101. Whether results are returned standalone database server103or to client101can be based on the type of the SQL statement. For example, a scalar subquery result can be returned to standalone database server103. On the other hand, a normal SELECT statement's result can be returned to client101.

Procedure execution module153can use returned results during the execution of other non-SQL statements, such as, for example, to make control flow decisions, assign a value to a variable, or evaluate an expression.

During execution of a stored procedure, any number of SQL statements can be set to parallel database coordinator102and results returned back to standalone database server103or to client101.

Returning again toFIG. 2, method200includes sending an execution statement to the parallel database coordinator, the execution request requesting execution of the stored procedure (207). For example, client101can send execute request141to parallel database coordinator102. Execution request141can request execution of a stored procedure with name112. Method200includes receiving the execution request from the client (208). For example, parallel database coordinator102can receive execution request141from client101.

Method200includes forwarding the execution request to the standalone database server (209). For example, parallel database coordinator102can parse execution request141and forward corresponding execution request142to standalone database server103. When appropriate, session correlation module152can generate session token143for the session between client101and parallel database coordinator102. Session correlation module152can attach session token143to execution request142. Method200includes receiving the execution request from the parallel database coordinator (210). For example, standalone database server130can receive execution request142from parallel database coordinator102.

Method200includes executing the stored procedure (211). For example, in response to receiving execution request142, procedure execution module153can determine that name112corresponds to executable procedure116. As such, procedure execution module153can initiate execution of executable procedure116. Execution of SQL statements117and non-SQL statements118can be split between parallel database coordinator102and standalone database server103respectively.

Method200includes sending a request to execute a SQL statement to the parallel database coordinator (212). During execution of executable procedure116, SQL statements117and non-SQL statements118can be encountered. Encountered SQL statements (e.g., wrapped inside EXECUTE AT or OPENQUERY) can be send to parallel database coordinator102for execution. For example, procedure execution module153can send SQL statement117A (an SQL statement included in SQL statements117) to parallel database coordinator102. When appropriate, session token143can be sent along with SQL statement117A to parallel database coordinator102.

Method200includes receiving the request to execute the SQL statement from the standalone database server (213). For example, parallel database coordinator102can receive SQL statement117A from standalone database server103. When appropriate, parallel database coordinator102can receive session token143along with SQL statement117A. Session correlation module152can use session token143to align SQL statement117A to the session between client101and parallel database coordinator102.

Method200includes executing the SQL statement against a parallel database (214). For example, SQL statement processor151can execute SQL statement117A against database131. Executing SQL statement117A can include parallel database coordinator102submitting query144to database131. Method200includes receiving results from executing the SQL statement against the parallel database (215). For example, parallel database coordinator102can receive results146(for query144) from executing SQL statement117A against database131.

Method200includes returning the results to an appropriate destination based on the type of the SQL statement (216). For example, parallel database coordinator102can return results146to standalone database server103. Alternately, results146can be returned to client101. Whether results are return to standalone database103to client101can be depending on the type of SQL statement117A. For example, if SQL statement117A returns a scalar subquery result, results146can be returned to standalone database server103. On the other hand, if SQL statement117A is a normal SELECT statement, results146can be returned to client101.

Method200includes receiving the results from the parallel database coordinator (at the standalone database server) (217). For example, standalone database server103can receive results146from parallel database coordinator102. Method200includes using the results during execution of at least one other non-SQL statement (218). For example, procedure execution module153can use results146during execution of at least one of non-SQL statements118. Alternately, method200includes receiving the results from the parallel database coordinator (at the client) (219). For example, client101can receive results146.

As appropriate,212through218can be repeated for any other SQL statements included in SQL statements117.

FIG. 3illustrates a more detailed example of splitting stored procedure execution between standalone database server103and a parallel database coordinator102. As depicted, procedure execution module153is executing procedure116. During execution, SQL statement301is sent to parallel database coordinator102. SQL statement processor151processes SQL statement301to submit query302to database131. Query302can request the number of entries in a customer table. SQL statement processor can receive back results303in response to submitted query302. Results303can include the number of entries in the customer table.

SQL statement processor151can return results303to procedure execution module153. Procedure execution module153can receive results303from SQL statement processor151. Procedure execution module153can use results303in evaluating conditional statement304at standalone database server103. As such, during execution of procedure116, SQL statement301is executed at parallel database coordinator102and conditional statement304is executed at standalone database103.

To support additional stored procedure specific behavior, embodiments of the invention can used a dedicated callback port hosted by parallel database coordinator102. The callback port is hosted exposing a parallel database coordinator endpoint internally to a control node SQL Server. The control node SQL server has a linked server registered connecting to that endpoint via its OLEDB provider. Exposure of the port can be limited to the boundary of the control node so that no client authentication is used for the linked server to communicate to the port. Instead, a session token can be generated at the time of user session creation and can be appended to any query sent from the SQL Server to Engine process via the linked server.

Since a different port boundary is exposed, responsibility of the host behind the port can be extended to be able to strip the session token from an incoming query, validate the query, and attach the query execution on to the associated session. As such, any session state a user may have can be shared, including transactions, database context, etc. The session token can also be used to authorize the request without having to store and pass users credentials to the linked server all the time. The session token can be disposed of at the end of the user session. Logic to parse session tokens can be disabled for the external requests and enabled for the internal port.