Client-side statement routing in distributed database

A system includes reception of a first query from a client device at a first database node of a database instance comprising two or more database nodes, determination of a second database node of the two or more database nodes associated with the first query, compilation of the first query at the first database node to generate first compiled code, and transmission of the first compiled code and a first identifier of the second database node from the first database node to the client device.

BACKGROUND

A distributed database system includes two or more database nodes. Each node executes one or more database processes and is associated with respective data storage. To retrieve data from a distributed database, a client application transmits a query to a database node which is designated to receive such queries. The designated database node determines whether it should execute the query or route the query to another database node for execution, and then executes or routes the query based on the determination.

DETAILED DESCRIPTION

The following description is provided to enable any person in the art to make and use the described embodiments and sets forth the best mode contemplated for carrying out some embodiments. Various modifications, however, will remain readily apparent to those in the art.

FIG. 1is a block diagram of system100. System100represents a logical architecture for describing some embodiments, and actual implementations may include more, fewer and/or different components arranged in any manner. The elements of system100may represent software elements, hardware elements, or any combination thereof. For example, system100may be implemented using any number of computing devices, and one or more processors within system100may execute program code to cause corresponding computing devices to perform processes described herein.

Generally, each logical element described herein may be implemented by any number of devices coupled via any number of public and/or private networks. Two or more of such devices may be located remote from one another and may communicate with one another via any known manner of network(s) and/or via a dedicated connection.

System100includes database instance110, which is a distributed database including database nodes112,114and116. Each of database nodes112,114and116includes at least one processor and a memory device. The memory devices of database nodes112,114and116need not be physically segregated as illustrated inFIG. 1, rather,FIG. 1is intended to illustrate that each of database nodes112,114and116is responsible for managing a dedicated portion of physical memory, regardless of where that physical memory is located. The data stored within the memories of database nodes112,114and116, taken together, represent the full database of database instance110.

In some embodiments, the memory of database nodes112,114and116is implemented in Random Access Memory (e.g., cache memory for storing recently-used data) and one or more fixed disks (e.g., persistent memory for storing their respective portions of the full database). Alternatively, one or more of nodes112,114and116may implement an “in-memory” database, in which volatile (e.g., non-disk-based) memory (e.g., Random Access Memory) is used both for cache memory and for storing its entire respective portion of the full database. In some embodiments, the data of the full database may comprise one or more of conventional tabular data, row-based data, column-based data, and object-based data. Database instance100may also or alternatively support multi-tenancy by providing multiple logical database systems which are programmatically isolated from one another.

According to some embodiments, database nodes112,114and116each execute a database server process to provide the data of the full database to database applications. More specifically, database instance110may communicate with one or more database applications executed by client120over one or more interfaces (e.g., a Structured Query Language (SQL)-based interface) in order to provide data thereto. Client120may comprise one or more processors and memory storing program code which is executable by the one or more processors to cause client120to perform the actions attributed thereto herein.

Client120may thereby comprise an application server executing database applications to provide, for example, business reporting, inventory control, online shopping, and/or any other suitable functions. The database applications may, in turn, support presentation applications executed by end-user devices (e.g., desktop computers, laptop computers, tablet computers, smartphones, etc.). Such a presentation application may simply comprise a Web browser to access and display reports generated by a database application.

The data of database instance110may be received from disparate hardware and software systems, some of which are not interoperational with one another. The systems may comprise a back-end data environment employed in a business or industrial context. The data may be pushed to database instance110and/or provided in response to queries received therefrom.

Database instance110and each element thereof may also include other unshown elements that may be used during operation thereof, such as any suitable program code, scripts, or other functional data that is executable to interface with other elements, other applications, other data files, operating system files, and device drivers. These elements are known to those in the art, and are therefore not described in detail herein.

FIG. 2comprises a flow diagram of process200according to some embodiments. Process200may be executed by any database node of a distributed database instance according to some embodiments. Process200and all other processes mentioned herein may be embodied in computer-executable program code read from one or more non-transitory computer-readable media, such as a floppy disk, a CD-ROM, a DVD-ROM, a Flash drive, a fixed disk and a magnetic tape, and then stored in a compressed, uncompiled and/or encrypted format. In some embodiments, hard-wired circuitry may be used in place of, or in combination with, program code for implementation of processes according to some embodiments. Embodiments are therefore not limited to any specific combination of hardware and software.

Initially, at S210, a query is received from a client. For example, database node112of instance110may receive a database query from client120at S210. The query may conform to any suitable compliable query language that is or becomes known, such as, for example, SQL.

Next, the receiving database node compiles the query at S220. According to some embodiments of S220, the database node executes a compiler associated with the language of the query, and compilation of the query results in compiled code. The compiled code is executable by any database node to execute the query on the data managed by the database node.

In this regard, a database node associated with the query is determined at S230. The determined database node may be a database node that is determined to be suited to execute the query. For example, if the query queries Table T of the database instance, the determined database node may be a database node that manages and/or stores Table T.

The compiled query is transmitted to the client at S240. Also transmitted to the client at S240is an identifier of the determined database node. As will be described below with respect to process300, the identifier may allow the client to route subsequent executions of the query to an appropriate database node (i.e., to the database node identified by the identifier).

FIG. 3comprises a flow diagram of process300according to some embodiments. Process300may be executed by a client device of a distributed database instance, such as but not limited to an application server, according to some embodiments.

Flow initially cycles at S310until an instruction to execute a query is received. The instruction may be generated by internal processes of an application executing on an application server and/or received from a user device at S310.

Once a query is received, it is determined at S320whether the client possesses compiled code corresponding to the query, as discussed above with respect to process200. In one example of S320, a client checks a locally-stored library (e.g., an SQLDBC client library) to determine whether the compiled code resides in the library.

FIG. 4illustrates system400according to some embodiments. For purposes of the present example, it is assumed that client420executes process300, and that library425of client420contains no compiled query code. Accordingly, the query to be executed is transmitted to a first database node at S330.

In theFIG. 4example, the query “Select . . . from T1” is transmitted to database node412at S330. Client420may transmit the query by calling a “Prepare Query” API exposed by database node412. According to some embodiments, one or both of database nodes414and416also expose the Prepare Query API and therefore the query could alternatively be transmitted to either of these nodes at S330.

As described with respect to S210through S240of process200, database node412may proceed to compile the query, determine a database node associated with the query (i.e., “N3”—referring to node416in which associated Table T1 is stored), and transmit the compiled code and an identifier of the database node to the client. Returning to process300, compiled code corresponding to the query and an identifier of a database node are received at S340.FIG. 4illustrates transmission of the query to database node412at S330and reception of the compiled code and identifier at S340.

The compiled code is stored in association with the identifier at S350.FIG. 5illustrates storage of the compiled code427in association with the identifier (i.e., “N3”) in library425according to some embodiments. In this regard, “in association” indicates that the identifier may be located in memory by reference to the query Q1and/or to the corresponding compiled code.

Next, at S360, an identifier associated with the compiled query is identified. The identifier N3 is identified in the present example, and the compiled query is transmitted to a database node associated with the identifier at S370, as shown inFIG. 5. According to some embodiments, client420transmits the compiled query to the identified database node by calling an “Execute Query” API exposed by database node416and passing the compiled code as a parameter thereof.

Database node416, in response, executes the compiled code to perform the query and returns the query results to client420. Client420receives the query results at S380and flow returns to S310to await another instruction.

FIG. 6illustrates a scenario in which an instruction to execute another query (e.g., Select . . . from T3 . . . ) is received at S310. Continuing with the present example, library425does not include compiled code corresponding to the query so the query is transmitted to a database node at S330.

The query is transmitted to database node414in order to illustrate that process200may be independently executable by more than one node of a database instance. More specifically, database node414then compiles the query, determines a database node associated with the query (i.e., “N2”—referring to node414in which associated Table T3 is stored), and transmits the compiled code and an identifier of the database node to the client. Accordingly, the identifier may identify the same database node used to compile the query.

The compiled code is stored in association with the identifier at S350, as shown inFIG. 7. Next, at S360, an identifier associated with the compiled query is identified, and the compiled query is transmitted to a database node associated with the identifier at S370. In theFIG. 7case, the identified node is the same node from which the compiled code and the identifier were received. The query results are received at S380and flow returns to S310to await another instruction.

It will now be assumed that an instruction to execute query Q1is received at S310. Referring toFIG. 8, the determination at S320is affirmative because library425includes compiled code corresponding to query Q1. Accordingly, flow proceeds directly to S360to identify an identifier associated with the compiled code, and on to S370to transmit the compiled query to a database node associated with the identifier, as illustrated inFIG. 8. New query results are then received from the database node at S380.

Therefore, according to some embodiments, second and subsequent executions of a query may avoid S330, S340and S350of process300, since the client will already possess both the compiled query and an identifier of a database node which is suitable for executing the query.

FIG. 9is a block diagram of system900according to some embodiments. System900illustrates one hardware architecture implementing system100and/or400as described above, but implementations of either system100or400are not limited thereto. Elements of system900may therefore operate to execute process200and/or300as described above.

Database master910and each of database slaves912,914and916may comprise a multi-processor “blade” server. Each of database master910and database slaves912,914and916may operate as described herein with respect to database nodes, and database master910may perform additional transaction management functions and other master server functions which are not performed by database slaves912,914and916as is known in the art.

Database master910and database slaves912,914and916are connected via network switch920, and are thereby also connected to shared storage930. Shared storage930and all other memory mentioned herein may comprise any appropriate non-transitory storage device, including combinations of magnetic storage devices (e.g., magnetic tape, hard disk drives and flash memory), optical storage devices, Read Only Memory (ROM) devices, etc.

Shared storage930may comprise the persistent storage of a database instance distributed among database master910and database slaves912,914and916. As such, various portions of the data within shared storage930may be allotted (i.e., managed by) one of database master910and database slaves912,914and916.

Application server940may also comprise a multi-processor blade server. Application server940, as described above, may execute database applications to provide functionality to end users operating user devices. Application server940may also execute process300to store compiled query code and associated node identifiers in local memory (not shown) for use in routing and executing database queries.