This invention relates generally to the subject of retrieving data from large computer databases.
Many data processing systems include tasks to extract large quantities of information from an operational datastore and then deploy the extracted information to an external and independently operating machine or system by means of a variety of network protocols. There can also be intermediate steps where extracted information undergoes one or more transformations.
In many cases the datastore where deployment data is gathered from may undergo updates during a deployment process. However, this can cause complications where a consistent view of the complete operational datastore data must be deployed in order to maintain relationships between items of data.
As an example, a factory which maintains operational information on customer orders, parts and sub-assemblies; production facilities; and builds a shop floor schedule periodically describing tasks to be completed for individual facilities and required parts for each facility may then deploy this to the factory production system.
As another example, the process by which dynamic Internet Protocol (IP) data managed by an IP Address Manager (IPAM) is deployed to a Dynamic Host Configuration Protocol (DHCP) server is called an IP network deployment. For instance, a DHCP server may run software that is provided by the Internet Systems Consortium (ISC). The IPAM can store dynamic IP data in categories such as networks and DHCP pools. This data can be frequently updated by network administrators and can also be deployed to DHCP servers in order for users to obtain leases of IP addresses.
Based on the deployment target information, data objects required for deployment can be extracted from a data store. In the factory example these objects can include customer orders, parts and machine tools. In the network example these can include networks and their DHCP pools, static IP addresses and DHCP options.
Next the data objects can be organized into groupings for analysis. These groupings can broadly correspond to the external system organization as determined by pre-existing topological and functional configurations of the external system. For example, groupings can be machine tools or networks for the provided examples. Information related to the topological and functional configurations of the external system can be obtained by analyzing a design and operation of the external system to determine the configuration and data sets required for a deployment. Each grouping can be transformed using the target information into part of a document or a set of activities which expresses the intent of the deployment. This expression can occur in a language according to a required output specification of a receiving agent. The intent typically describes the deployment in terms which are meaningful to the deployment target external system. This can be similar to a computer programming language with procedural semantics or can merely be descriptive, such as an XML file. The document or set of activities can be the combined set of all transformed groupings. The required output specification can describe the content, syntax and order of items within the document or set of activities. It can also unambiguously describe the nature of the document's required contents. A particular output specification need not state a list of numbers and words for a specific deployment since the list of numbers and words can change from one deployment to another. However, the output specification can include rules and requirements to produce numbers and words for specific deployments.
The document or set of activities can be transmitted to a receiving agent that can process the document or perform the activities, including optionally updating a service running on a target external machine or system. As an example, a supervisor program executing on at least one machine acting as a receiving agent can update a target external machine or system configuration including all associated data based on received, updated information and restart the target external machine or system configuration using the received, updated information. The document or set of activities can be consistent from one iteration to another. For example, day-to-day updates for a single deployment target can appear similar for a single deployment target. However, documents or sets of activities for different deployment targets can be highly diverse or distinct.
Building a deployment task can be difficult in dynamic environments where data is constantly being updated. Since the data is constantly being updated, extracting required data from an operational datastore in an efficient manner can be difficult. Consistency of values of the returned set of data (i.e. internal self-consistency) is a property of the values such that these values all existed in the database at a single point in time. Consistency is essential to solve this problem. Numerous methods have been developed to address this problem, each suffering from disadvantages.
As early as the early 1970's, a deployment task was accomplished by scheduling a job to run on its own at a time when regular users were not using the system. For example, a weekly payroll process could be a job. Thus, the job could run with exclusive use of an associated system and datastore including most or all associated resources. The time period when this could occur was referred to as a batch window and usually extended from the end of working hours on one day until the start of working hours on the next day, a fairly long period of time. However, many companies now operate globally and have employees working around the clock, running ever increasing numbers of jobs. Thus, the traditional batch window is no longer a viable option.
Since at least the 1980's, datastores with concurrency controls such as relational databases have made it possible to run jobs against an operational datastore while users are updating the datastore. This process has issues as well, including costs such as delays to jobs and users caused by serialization and locking mechanisms that are employed to ensure integrity and consistency in the system.
One method of ensuring internal self-consistency while reducing locking of data records for the exclusive use of one process is to copy or “snapshot” relevant tables of the operational datastore in a separate set of tables. Operations and calculations can then be performed using the copy while the original datastore continues to be updated. Unfortunately, with the increasing size of operational datastores, snapshots impose an excessive overhead that can result in significant system delays. Additionally, if multiple deployment jobs are scheduled to run at times which overlap, individual snapshots may be required for each job or temporary serialization for jobs using a single snapshot will be required. Thus, each deployment job may need to finish with a previous snapshot before proceeding to a current snapshot.
Advanced relational databases can provide consistency when reading a production operational datastore by mimicking the effects of a snapshot without the overhead required to actually take a snapshot. In an example embodiment, PostgreSQL [POSTGRESQL] can provide a “read committed” isolation level. In effect, a database query using Structured Query language (SQL) can “see” a snapshot of the production operational datastore as of an instant the query begins processing. However, “read committed” applies to a single SQL statement, whereas in a complex deployment there can be tens, hundreds, or even more statements, each fetching a distinct type of data.
If the size of a particular enterprise, the size of an associated operational datastore and the frequency of update activity are growing, then the disadvantages described above can become more serious as time passes. For example, performance of a deployment process can cause enough delay as to impact operational efficiency of the enterprise due to delays between operational datastore changes and deployment of those changes to external machines and systems. During the delay, the machines and systems can operate at less than peak efficiency.
Thus, needs exist for improved techniques of building a deployment process to fetch selective data from an authoritative, operational and dynamic relational datastore needed by the deployment process.
Provided herein are embodiments of systems and methods of building a deployment process to fetch selective data from an authoritative, operational and dynamic (frequently updated) relational datastore needed by the deployment process. The systems can include a processing computation for a single query statement, a process for transforming and decorating the deployment output data and a transfer process for conveying the data to one or more target deployment devices or systems. The methods can include deriving a single, optimized Structured Query Language (SQL) statement that uses a read committed isolation level provided by many relational databases from the output specification and the structure of data in the datastore. Additionally, the method can include testing the single SQL statement by executing the statement on an operational datastore that stores an arbitrarily large data set from which data is selected, applying at least one transformation rule used to decorate and format an output to be deployed and transferring the decorated data to a target device or system. Also, the method can include verifying and optimizing the deployment process by iteration. A computer program product such as a program stored in non-transitory computer readable memory and executable by a process can allow a user to iteratively develop, test and optimize a single SQL statement.
Other systems, devices, methods, features and advantages of the subject matter described herein will be or will become apparent to one with skill in the art upon examination of the following figures and detailed description. It is intended that all such additional systems, methods, features and advantages be included within this description, be within the scope of the subject matter described herein, and be protected by the accompanying claims. In no way should the features of the example embodiments be construed as limiting the appended claims, absent express recitation of those features in the claims.
The details of the subject matter set forth herein, both as to its structure and operation, may be apparent by study of the accompanying figures, in which like reference numerals refer to like parts. The components in the figures are not necessarily to scale, emphasis instead being placed upon illustrating the principles of the subject matter. Moreover, all illustrations are intended to convey concepts, where relative sizes, shapes and other detailed attributes may be illustrated schematically rather than literally or precisely.
Before the present subject matter is described in detail, it is to be understood that this disclosure is not limited to the particular embodiments described, as such may, of course, vary. It is also to be understood that the terminology used herein is for the purpose of describing particular embodiments only, and is not intended to be limiting, since the scope of the present disclosure will be limited only by the appended claims.
Provided herein are systems and methods for retrieving consistent data from a very large data store by means of a single SQL statement that uses a read committed isolation level.
The preferred embodiment of this invention can be demonstrated using the example of DHCP network data deployment.
In an embodiment of this invention an operational datastore can be a database that is a source of a plurality of N deployment data sets. While in practice the number of deployment data sets can be arbitrarily large, a value of N is 3 will be used as a non-limiting example herein.
The method of this invention includes one or more iterations of generating, on a computer operably connected to the operational datastore, a single, composite SQL statement to satisfy a desired, preset output specification as determined by a target service or agent; the output specification can be defined by at least one of semantic content, syntax, and order of items in the output to be generated. As an example, if an output is a DHCP configuration then semantic content can be network information to which the DHCP configuration applies. Syntax and order of appearance can be defined by the DHCP target service, as illustrated in
In a first iteration, a series of steps can occur including:
From the output specification identifying an answer set for generation by Select clauses and an estimation of a feasible order for the answer set, which can be a non-optimized order. Each select clause can be constructed to define homogenous tuples (i.e. sets of values) to appear in an answer set row, where all tuples in each answer set row have an identical schema of column names and column definitions, even though data represented by the tuples in a row can be different from data in each other row. An example of a column schema can have dimensions of d=6 columns, representing elements of at least one data type. To illustrate,
Where N sets of tuples are deployed, the Select clauses can be represented by a set {Si} [i=1 . . . N], referencing fields from a set of tables {Ti} according to a set of conditions {Ci}. Within each set Si an output tuple ti of dimension d can include values according to the column schema, such as integers or strings, or computation statements resulting in such values, for example a difference of 2 integers or a concatenation of 2 strings.
From a desired answer set {Ai} [i=1 . . . N] of tuples ai, of dimension d+1 where, for each i, ordering parameter zi can be an integer from the set of integers [1 . . . N] and the tuple ai=zi U ti selected from tables {Ti} with conditions {Ci} derive a single, composite SQL statement.
A first select clause S1 601 can be designed to extract for deployment all networks associated with the target service location from the database table ‘networks’ shown in
A second Select clause S2 602 can be designed to extract DHCP pools for deployment from the database table ‘pools’ shown in
A third Select clause S3 603 can be designed to extract static addresses for deployment from the database table ‘static_addr’ shown in
Add the ordering parameters zi to form the answer-set tuples: A1={z1, id, ‘network’, name, <null_string>, <null_string>, <null_string>}, A2={z2, id, ‘pool’, name, start_address, end_address, max_lease_time}, A3={z3, id, ‘static’, name, addr, mac_addr, <null_string>}. Note that <null> constants must be inserted to keep the column names and meanings consistent.
Derive a single, composite SQL statement from the answer-set tuples {Ai} by generating a SQL statement for each Ai using values {Si} selected from tables {Ti} with conditions {Ci} and performing the UNION of the resulting clauses and ordering the output first by the order parameter zi in column 1 and then by the other columns as may be specified by the user. This is made possible because all elements of the answer set {Ai} conform to the same output schema 700 shown in
Following the derivation of the single SQL statement, test the statement by executing it on a computer that has access to the said database to determine at least the following results: time taken to produce the output content; output content as generated by the database; transformation or decoration of the output content to generate the deployment data format desired by use in the deployment, which may include transforming the data into a data structure, for example XML, or a set of executable statements to be executed by the recipient, or both; and deployment procedure needed to invoke the single SQL statement, process the output content into the deployment format and transfer the resulting deployment data to the desired deployment destination.
These results are used to verify correctness and to decide which ordering parameters, if any, to change to optimize deployment elapsed time.
In the second iteration perform the following steps; verify that the output content contains the values needed to satisfy the deployment. From this output the format transformation used in the final stage of deployment may be applied. In the example of the preferred embodiment the output may be similar to
The service being deployed, e.g. the DHCP service, factory shop-floor, etc., may have its own capability of verifying whether the deployment presented to it is valid or not whereby this verification capability may not be accessible other than by means of the end destination service or an external validation process.
For example, the ISC DHCP service provides a command for verifying a candidate DHCP configuration. Although a deployment analyst may detect obvious errors, the target being deployed is the final arbiter of correctness. If this verification fails, then the single SQL statement may be revised and the test re-executed until the desired result is achieved.
Once functional correctness has been verified, modify the order parameter in the first, numeric column of the answer-set tuples to rearrange the order of the Select clause result in the output content. Modify the order of the tuples in the answer set (i.e. the column positions) to control the order of the output within each Select clause result to optimize the operation of the SQL statement. On each reordering a new unique SQL statement may be derived, the test may be re-executed and the verification undertaken, wherein upon successful verification, the time taken to produce the output may be monitored. Repeat this until a satisfactory output is achieved in a satisfactory time, whereby satisfactory time may be less than a predetermined threshold, for example as determined by a service level agreement.
In the third iteration it can be possible to optimize the SQL syntax to improve performance even further by performing the following steps: examine the set of select clauses {Si}, conditions {Ci} and tables {Ti} and determine if there are repeated elements that are common to two or more clauses; create SQL common table expressions (CTE), for example WITH statements, that may be inserted at the front of the single SQL statement; revise and optimize the individual select clauses {Si}, conditions {Ci} and tables {Ti} to use the CTE.
For example a known technique involving use of a recursive CTE called “winners” may be used as follows: a first WITH clause may recursively descend the hierarchy from the Network item, selecting all those network objects of type Subnet which either are directly associated with a tag such as “EUROPE” or are descendants of those objects are directly associated with a tag such as “EUROPE;” a second WITH clause may group the results of the first WITH clause; a third WITH clause may filter results of the first WITH clause based on the groupings produced by the second WITH clause to determine the values to be deployed (i.e. the “winners”).
For a large hierarchical data set this leads to a significant reduction in processing time compared to repetitively evaluating the results to determine the “winners” and also allows the database to perform an operation in the single SQL statement that may be more time-consuming for the deployment process if performed external to the database.
After the optimization, the test may be re-executed and the verification undertaken, wherein upon successful verification, the time taken to produce the output may be monitored and used in later optimization. Repeat this revision until a satisfactory time is achieved. Note that the example shown above is comparatively simple, and a more comprehensive deployment may comprise many more Select clauses resulting in a single SQL statement that may extend over multiple pages, were it to be printed out. For example, whereas the current example includes Select clauses networks, pools and static addresses, a more complete deployment may include Select clauses for, but not limited to; device identifications, device options, configuration, configuration options, subnets, subnet options, pools, pool options, static addresses, static address options and others. Such a statement cannot ordinarily be developed and optimized on an ad hoc basis without use of this method of developing such large SQL statements on an iterative basis.
With reference to the System Components shown in
As used herein and in the appended claims, the singular forms “a”, “an”, and “the” include plural referents unless the context clearly dictates otherwise.
The publications discussed herein are provided solely for their disclosure prior to the filing date of the present application. Nothing herein is to be construed as an admission that the present disclosure is not entitled to antedate such publication by virtue of prior disclosure. Further, the dates of publication provided may be different from the actual publication dates which may need to be independently confirmed.
It should be noted that all features, elements, components, functions, and steps described with respect to any embodiment provided herein are intended to be freely combinable and substitutable with those from any other embodiment. If a certain feature, element, component, function, or step is described with respect to only one embodiment, then it should be understood that that feature, element, component, function, or step can be used with every other embodiment described herein unless explicitly stated otherwise. This paragraph therefore serves as antecedent basis and written support for the introduction of claims, at any time, that combine features, elements, components, functions, and steps from different embodiments, or that substitute features, elements, components, functions, and steps from one embodiment with those of another, even if the following description does not explicitly state, in a particular instance, that such combinations or substitutions are possible. It is explicitly acknowledged that express recitation of every possible combination and substitution is overly burdensome, especially given that the permissibility of each and every such combination and substitution will be readily recognized by those of ordinary skill in the art.
In many instances entities are described herein as being coupled to other entities. It should be understood that the terms “coupled” and “connected” (or any of their forms) are used interchangeably herein and, in both cases, are generic to the direct coupling of two entities (without any non-negligible (e.g., parasitic) intervening entities) and the indirect coupling of two entities (with one or more non-negligible intervening entities). Where entities are shown as being directly coupled together, or described as coupled together without description of any intervening entity, it should be understood that those entities can be indirectly coupled together as well unless the context clearly dictates otherwise.
While the embodiments are susceptible to various modifications and alternative forms, specific examples thereof have been shown in the drawings and are herein described in detail. It should be understood, however, that these embodiments are not to be limited to the particular form disclosed, but to the contrary, these embodiments are to cover all modifications, equivalents, and alternatives falling within the spirit of the disclosure. Furthermore, any features, functions, steps, or elements of the embodiments may be recited in or added to the claims, as well as negative limitations that define the inventive scope of the claims by features, functions, steps, or elements that are not within that scope.
This application claims priority to U.S. Provisional Application, No. 62/030,534, entitled “Method, Device and System for retrieving data from a very large data store” filed Jul. 29, 2014, which application is hereby incorporated in its entirety by reference.
Number | Date | Country | |
---|---|---|---|
62030534 | Jul 2014 | US |