Persistent querying in a federated database system

Methods and systems are provided, for persistent querying in a federated database system. A federated database system has a plurality of connectable database management system nodes, each database node having at least one database. Persistent querying includes: receiving a query at a first database node and propagating the query from the first database node to other database nodes in the federated database system. A responding database node satisfying the query: obtains a query results set; defines a topic identifier for the query; and publishes subsequent messages relating to the topic identifier when data updates are available. The first database node receives query results sets from one or more responding nodes and the first database node subscribes to topic identifiers for publications of subsequent messages from the responding database nodes. Subsequent messages are published relating to the query along the connection through which the query is returned.

CROSS-REFERENCE TO RELATED APPLICATIONS

This application claims priority to European Patent Application No. 09177311.9, filed Nov. 27, 2009, the disclosure of which is incorporated herein by reference.

BACKGROUND

Aspects of the present invention relate to the field of querying in federated databases, and in particular, to a persistent query mechanism in a federated database.

Smart push technologies, and smart pull technologies provide different paradigms for exchanging information across a network comprised of distributed information sources and users of the exchanged information. An example of a smart push technology is a message broker technology. An example of a smart pull technology is a Distributed Federated Database technology.

Traditional message broker technology uses a publish/subscribe paradigm for distributing information from publishers to subscribers. While such networks are asynchronous, a mechanism is still necessary for discovering which topics are being published. Moreover, traditional message broker technology requires routing to be set up between all publishers and subscribers in the network.

In contrast, federated databases use distributed database query mechanisms to discover distributed information sources that can respond to a query. Further, the federated databases can aggregate query-responsive information across the network to return a single consolidated result-set to the querying node. This synchronous mechanism finds the nodes that can contribute the required information and returns data to the requesting node by the current ‘fastest path’. However, federated databases pay a penalty by using a constrained flood query to discover the sources of information called upon to return query results to a querying node.

BRIEF SUMMARY

Approaches are provided for persistent querying in a federated database system. A federated database system has a plurality of connectable database management system nodes, each database node having at least one database. Persistent querying within the federated database system comprises receiving a query at a first database node and propagating the query from the first database node to other database nodes in the federated database system. A responding database node satisfying the query obtains a query results set, defines a topic identifier for the query and publishes subsequent messages relating to the topic identifier when data updates are available. The first database node receives query results sets from one or more responding nodes and the first database node subscribes to topic identifiers for publications of subsequent messages from the responding database nodes. Subsequent messages are published relating to the query along the connection through which the query is returned.

DETAILED DESCRIPTION

In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of aspects of the present invention. However, it will be understood by those skilled in the art that aspects of the present invention may be practiced without these specific details. In other instances, well-known methods, procedures, and components have not been described in detail so as not to obscure the disclosure herein.

Methods, computer program products and systems are described, in which a hybrid mechanism of push and pull architectures is provided. The hybrid mechanism enables a persistent query to obtain information from distributed sources. The hybrid mechanism also provides a fully distributed mechanism for topic discovery and dynamic topic generation. The hybrid mechanism may further provide for automatically discovering the optimum routes over which topics are published, and the hybrid mechanism may further provide for automatically adapting the optimum routes to changing network conditions and interconnectivity.

The described architectures herein, may be fully distributed and may not require any node to have any knowledge of existing topics other than knowledge of the database data schemas that exist in the network.

As such, certain aspects of the present invention facilitate the ability to obtain a persistent stream of results that correspond to the same query. As an example, a query may be performed to obtain information from a specific set of tables, distributed across a number of database nodes, which are being intermittently updated with new data. The querying node (or nodes) may wish to receive any future data as it is entered into these tables. Aspects of the present invention set out in greater detail herein, can obtain such updated data without requiring a conventional synchronous pull mechanism operation that performs more queries (i.e., polling for new data), which can be expensive, particularly if many polling queries are required to ensure that intermittent data updates are captured in a timely manner.

Referring toFIG. 1A, a system100is shown in which a federated database is made up of database nodes110,120,130. Each database node110,120,130is a distributed database typically formed of a database management system (DBMS)111,121,131operable to access, for example, a first database112,122,132associated with a back end computing system113,123,133and also operable to access a second database114,124,134that is local to the DBMS111,121,131. The system100is simplified for illustrative purposes. In this regard, the database nodes110,120,130are referred to as distributed, as each DBMS111,121,131is operable to access multiple databases.

A distributed database federation is a virtualized database that allows constituent database nodes110,120,130to remain autonomous but provides a uniform front-end user interface, enabling users to store and retrieve data in multiple non-contiguous databases with a single query. The database management systems111,121,131of the database nodes in the federation are fully interconnected to each other. The database management systems111,121,131act as interpreters and intermediaries between the various databases and back-end computing systems. When a query is made within the federated database, one or more database management systems111,121,131decompose the query into sub-queries for submission to the relevant constituent database management systems111,121,131.

In the described system100, the database management systems111,121,131include publish/subscribe applications116,126,136. Moreover, the system100provides a message broker network of publish/subscribe message brokers141,142,143. These message brokers141,142,143can be shared between many database management systems111,121131. In this regard, any configuration of database management systems111,121,131and message brokers141,142,143can be used. However, the configuration with one message broker141,142,143per database management system111,121,131is a generic case. The message brokers141,142,143may be provided in paths between the database management systems111,121,131of the database nodes110,120,130.

Publish/subscribe is an asynchronous messaging paradigm where publishers of messages characterize a message by a “topic” or named logical channel, and publish the characterized message to a message broker. The message broker is an intermediary which registers subscriptions and performs filtering. The message broker may perform a store and forward function to route message from publishers to subscribers. Subscribers express interest in one or more topics, and only receive messages published on that topic.

In the described mechanism, each database node that can provide data in response to a query responds to the initial query with a result set that has a topic identifier uniquely generated by the node. For example, the topic identifier may be a hashing result of a node identifier and an original query identifier (node_id+original_query_id). Any future data that is entered into the database that satisfies the query is then published as a message into a message broker network to which the querying node can subscribe. As the result set of the initial query is returned to the querying node, brokers along the returning routes are instructed to subscribe to any messages on the new published topic and to publish any messages received along the route by which the initial query was returned.

Referring toFIG. 1B, a detail of the system ofFIG. 1Ais shown, with data flow between two database nodes110,120and a message broker141.

A querying database node110sends a query161from a query mechanism151to another database node120. The query mechanism151includes a propagating mechanism152to propagate the query161to other database nodes. The query mechanism151also includes a persistent query definition154to inform the database nodes that publication of subsequent messages is required. The query mechanism151also includes an optional query decomposition component153for decomposing a query into sub-queries before propagation to other database nodes.

The responding database node120searches for the query161and may use a propagating mechanism152to extend the query to other database nodes. The responding database node120defines a topic identifier for the query using a results and topic identifier generator157. The responding database node120further returns the results and the defined topic identifier162to the querying database node110. The responding database node120may include an existing topic identifying component155for identifying existing topic identifiers that satisfy a query received from another node. The responding database node120may also include an aggregating component156for aggregating results from other database nodes before returning a result set to the querying database node110.

The responding database node120has a publish/subscribe application126, which instructs163a broker141to subscribe164to any messages on the topic identifier. The querying database node110has a result receiving mechanism158that activates a publish/subscribe mechanism116which in turn subscribes165to the broker141for any messages on the topic identifier. When a new message is published166by the responding database node120due to a database update on the topic, it is received by the broker141and published167to the querying database node110.

Where the initial query can be satisfied at several nodes, each node generates its own topic identifier and responds in the way described above with routes back to the initial querying node being established. The querying node then subscribes to all of the topic identifiers to get the aggregated set of future data from all sources satisfying the query.

The same query, even when issued from the same querying node, can and often results in different ‘optimum’ return routes being established. If the same process is followed to enable multiple returning message paths, then the described method also provides a high degree of resilience to path disruption and message traffic. It may thus be desirable to issue the same initial query multiple times to establish these additional routes.

If the same or similar query is issued from other nodes in the network, and nodes that can respond to the query can identify that an existing topic satisfies the same query, then the response to the query will be an existing topic which is already being published. As the result set is returned to the querying node then, only brokers along the route that have not previously been enabled to publish and subscribe on the topic need be enabled. As an illustration, the mechanism for identifying that the query is the same can be based on a database caching mechanism.

Referring toFIG. 2, an exemplary system for implementing a database node according to aspects of the invention includes a data processing system200suitable for storing and/or executing program code including at least one processor201coupled directly or indirectly to memory elements through a bus system203. The memory elements can include local memory employed during actual execution of the program code, bulk storage, and cache memories which provide temporary storage of at least some program code in order to reduce the number of times code must be retrieved from bulk storage during execution.

The memory elements may include system memory202in the form of read only memory (ROM)204and random access memory (RAM)205. A basic input/output system (BIOS)206may be stored in ROM204. System software207may be stored in RAM205including operating system software208. Software applications210may also be stored in RAM205.

The system200may also include primary storage211such as a magnetic hard disk drive and secondary storage212such as a magnetic disk drive and an optical disc drive. The drives and their associated computer-readable media provide non-volatile storage of computer-executable instructions, data structures, program modules and other data for the system200. Software applications may be stored by the primary and secondary storage211,212as well as the system memory202.

The computing system200may operate in a networked environment using logical connections to one or more remote computers via a network adapter216.

Input/output devices213can be coupled to the system either directly or through intervening I/O controllers. A user may enter commands and information into the system200through input devices such as a keyboard, pointing device, or other input devices, for example, microphone, joy stick, game pad, satellite dish, scanner, or the like. Output devices may include speakers, printers, etc. A display device214is also connected to system bus203via an interface, such as video adapter215.

Referring toFIG. 3, a flow diagram300shows a method of propagating a persistent query in a federated database environment.

A user queries301any one of the database nodes of the Distributed Federated Database (DFD) network using a Structured Query Language (SQL) query that defines the required information. This query is differentiated302from a standard query that is just required to return a single result set. In an illustrative embodiment, this differentiation is achieved by adding the extension “AND PERSIST QUERY” to the SQL statement e.g., select TOPIC from TABLE and persist query.

The query propagates303to all nodes in the network from the querying node. Those nodes that can, obtain304data in response to the query return and generate a topic identifier. Responding database nodes aggregate305any results sets and topic identifiers from lower database nodes and return these to the querying database node.

The responding database nodes instruct306an associated message broker to subscribe to future messages on the topic identifiers satisfying the query. The responding database nodes publish307any future messages on the topic to the message broker.

The querying database receives the results of the query and subscribes308to messages in the set of topics returned in the result set. Messages returned are processed309to generate a result set that satisfies the initial query, for example, performing the union, distinct database operations.

Referring toFIG. 4, a flow diagram400shows a method that can be implemented at a responding database node. A query is received401. A determination is made402as to whether a topic identifier fitting the query exists. If a topic identifier does not already exist, a topic identifier is created403and the topic identifier is stored404in the local Distributed Federated Database table. This topic identifier is used to identify messages that match the query and that will be published by the local broker. Additional appropriate meta-data may also be stored with the topic identifier in the local Distributed Federated Database table to enable the topic to be discovered via this table and to assist the process of determining if the topic already exists. For example, the invariant components of the query might be stored. If a topic identifier exists steps403and404are skipped.

If the responding database node is an intermediary node with further responding database nodes below it in a hierarchy, the responding database node receives405results sets and topic identifiers from the other lower nodes. A response to the query is sent406in the form of an aggregation of the result sets and topic identifiers.

A message broker is instructed407to subscribe to future messages from the responding database node on the topic identifiers. Future messages are published408by the responding database node on the topic identifiers along the connection though which the query is being returned to enable the querying database node to subscribe to the topic identifiers and receive persistent responses to the query.

As the result set is aggregated at each intermediate node along the route back to the querying node, the associated broker is instructed to subscribe to messages on the returned topic and to publish these to the broker attached along the connection through which the query is being returned. If the associated node is already subscribing to the topic (because of a previous query), it may still be necessary to instruct the broker to publish the topic along the connection through which the query is being returned, if it is not already doing so.

Referring toFIGS. 5A to 5D, schematic diagrams show the process of querying in nodes of a federated database. A federated database is shown500with nodes501-514with each node being a database node with at least one database.

InFIG. 5A, a query550is propagated to all nodes. In this illustrative example, nodes501,504,507,509,512and514generate results and are shown in solid lines. Queries to nodes502,503,505,506,508,510and511do not generate results, and are shown in dashed lines.

FIG. 5Bshows a returned results set555with topic identifiers from the nodes. For example, node512returns topic identifier T1, node514returns topic identifier T2. Node509returns topic identifiers T1and T2and node507returns topic identifier T3. This results in node504returning topic identifiers T1, T2and T3to node501. A message stream560for future messages on the topics is then established using publish/subscribe to subscribe to all topic identifiers T1, T2and T3.

FIG. 5Cshows a further query570being propagated to additional nodes515,516,517as well as existing nodes507,509,512and514. Results are returned from all these nodes507,509,512,514,515,516,517.

FIG. 5Dshows a retuned results set580with topic identifiers from the nodes. Additional node517returns topic identifier T2and node516returns topic identifiers T1, T2and T3. An additional message stream590is established for future messages using publish/subscribe to all topic identifiers T1, T2and T3.

The discovery of new sources of data that might appear in the network that could satisfy the initial query would be discovered by nodes periodically re-issuing the queries and adding any new topic identifiers to the list of topics that are subscribed to get the aggregated result set from the query.

The initial query could be constructed to return both existing data as part of the result set, together with the topic identifier for future data that satisfies the query. For example, select DATA, TOPIC from TABLE and persist query.

In an alternative embodiment of the disclosure, rather than enabling the message broker network as the query returns from the nodes satisfying the initial query, the return route is simply recorded and the resulting path/paths for each topic are reported to a ‘topology manager’ which uses all available information to determine an optimum set of routes. In cases where the route returned from the query may not be available because of policy requirements or other network issues, an alternative route may be planned using the information and the message broker network is then instructed to route messages over these alternative routes.

The systems, methods and computer program products described herein, show various hybrid push/pull architectures. In this regard, aspects described herein, provide for the dynamic creation of topics based on response to queries. Further aspects described herein, provide a distributed topic registry. Still further aspects herein provide for the dynamic creation of routes between publishers and subscribers. Still further aspects provide for the minimization of the number of routes as more subscribers are added to the network. Yet further aspects described herein provide an asynchronous persistent query mechanism.

As will be appreciated by one skilled in the art, aspects of the present invention may be embodied as a system, method or computer program product. Accordingly, aspects of the invention can take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment containing both hardware and software elements. Further, aspects of the invention can be implemented in software, which includes but is not limited to firmware, resident software, microcode, etc.

Aspects of the present invention can take the form of a computer program product accessible from a computer-usable or computer-readable storage medium providing program code for use by or in connection with a computer or any instruction execution system. For the purposes of this description, a computer usable or computer readable medium can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus or device.

Any combination of one or more computer readable medium(s) may be utilized. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system (or apparatus or device). Examples of a computer-readable storage medium include a semiconductor or solid state memory, magnetic tape, a removable computer diskette, a random access memory (RAM), a read only memory (ROM), a rigid magnetic disk and an optical disk. Current examples of optical disks include compact disk read only memory (CD-ROM), compact disk read/write (CD-R/W), and DVD. In the context of this document, a computer readable storage medium may be any tangible storage medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.