Apparatus and method for utilizing pre-computed results for query processing in a distributed database
A pre-computed result module computes a result prior to receiving a query. The pre-computed result module includes instructions executed by a processor to assess a pre-computation query to designate each identified database source that contributes to the answer to the pre-computation query and corresponding database source metadata. A metadata signature is computed for each identified database source to create a store of identified database sources and corresponding metadata signatures. The query is evaluated to identify accessed database sources responsive to the query. A current metadata signature for each accessed database source is compared to the metadata signatures to identify each updated database source. Re-computed results are formed for each updated database source. Pre-computed results are utilized for each database source that is not updated. A response is supplied to the query using the re-computed results and the pre-computed results.
1. A system, comprising;
a database with multiple data sources, wherein the database is a distributed database implemented on a collection of network connected worker machines, each worker machine maintaining a respective database partition storing individual source files and associated source metadata identifying a file name, file size, update timestamp and corresponding database partition; and
a query support machine coupled to the worker machines and having a pre-computed result module to pre-compute results responsive to one or more queries from a networked client computer, and store the pre-computed results in a table of the database, wherein the pre-computed result module includes instructions executed by a processor to:
execute a pre-computation query to determine which input files contribute to each of the pre-computed results, wherein the pre-computation query indirectly references database files constituting base tables named in the query and stored in one or more database partitions of the worker machines;
group pre-computed results according to corresponding input files;
generate a metadata checksum from the associated source metadata for each corresponding input file;
store the generated metadata checksum for each corresponding input file with a corresponding pre-computed result;
evaluate a new query from the client to identify an accessed data source;
determine whether a result of the new query corresponds to a pre-computed result by comparing a current metadata checksum of the accessed data source to a stored generated metadata checksum for a pre-computed result;
use the pre-computed result as the result of the new query if the current metadata checksum matches the stored generated metadata checksum; otherwise
update the generated metadata checksum for input files changed or created after the pre-computation query to generate stored updated metadata checksums, and re-computing the result to generate a re-computed result using a corresponding stored updated metadata checksum; and
return at least one of the pre-computed result or re-computed result to the client in response to the new query.
2. The system of claim 1 wherein the accessed data source is a file, and wherein the file name of the file has embedded in it partitioning values of a database partition storing the file, the partitioning values comprising a year, month, and day of the file.
3. The system of claim 2 wherein the metadata is automatically updated whenever the file is changed, and wherein the corresponding metadata checksum is also automatically changed.
4. The system of claim 3 wherein the stored metadata checksum and the current metadata checksum each comprise a checksum value based upon one of an exclusive-OR function, a cyclic redundancy check, and a message-digest algorithm.
5. The system of claim 1 further comprising a master machine coupled to the client machine connected to the network to perform master node operations associated with the distributed database.
6. The system of claim 5 wherein the query support machine is implemented in the master machine.