Computation reuse in analytical job services

By using a combination of standardized signatures and precise signatures in the analytical job service, the overlap calculations in the shared analytics cluster are automatically detected and reused, and the resource waste and high cost problems caused by computing overlap are solved, improving the efficiency and cost control of data analysis.

CN112041832BActive Publication Date: 2024-12-06MICROSOFT TECHNOLOGY LICENSING LLC
View PDF 1 Cites 0 Cited by

Patent Information

Application Number
CN201980025488.4
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Priority Date
2018-04-13
Filing Date
2019-03-28
Publication Date
2024-12-06
Estimated Expiration
2039-03-28

AI Technical Summary

Technical Problem

In shared analysis clusters, the calculation overlap phenomenon leads to resource waste and high operating costs, and the prior art is difficult to effectively detect and reuse overlap calculations, affecting the efficiency and cost control of enterprise data analysis.

Method used

By using a combination of normalized signatures and precise signatures in the analysis job service, overlap calculations are automatically detected and reused, overlapping calculations are identified using normalized signatures when realizing queries, and precise signature matching and reusing subgraphs within specific data instances, Reduce computing resource utilization and query response time.

Benefits of technology

It realizes more efficient and effective query response, reduces the utilization of computing resources and query response time, reduces the operational cost of enterprise data analysis, simplifies user scripts, and improves the transparency and debuggability of computing reuse.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN112041832B_ABST
    Figure CN112041832B_ABST
Patent Text Reader

Abstract

A system and method for detecting and reusing overlapping computations is described herein. Overlapping subgraphs of a query are determined using a normalized signature for a particular subgraph that identifies the particular subgraph across repeated instances of data. A normalized signature for each overlapping subgraph in the determined overlapping subgraphs of the query is provided. For each overlapping subgraph determined to be materialized: whether the particular subgraph has been materialized is determined using an exact signature corresponding to the normalized signature of the particular overlapping subgraph. Within a particular repeated instance of the data, the exact signature identifies the particular subgraph corresponding to the normalized signature. When the particular subgraph has not yet been materialized, the subgraph is materialized and used to respond to the query. When the particular subgraph has been materialized, the materialized subgraph is used to respond to the query.
Need to check novelty before this filing date? Find Prior Art

Description

Background Art

[0001] Shared analytics clusters have become the de facto way for large organizations to analyze and gain insights into their data. Clusters typically include tens of thousands of machines, store exabytes of data, and support thousands of users, collectively running hundreds of thousands of batch jobs every day.

[0002] In the case of a shared analytics cluster, significant overlap can be observed in the computations performed by submitted jobs. Simply computing the same job sub-expression multiple times wastes cluster resources, which has an adverse impact on the operational cost of the cluster. Summary of the invention

[0003] This article describes a system for automatically reusing overlapping computations in an analysis job service: a computer includes a processor and a memory, the memory having computer executable instructions stored thereon, the computer executable instructions causing the computer to: receive a query; determine overlapping subgraphs of the query using stored information about the analyzed workload data, the stored information including a normalized signature for a specific subgraph, the normalized signature identifying the specific subgraph across repeated instances of the data; provide information about the determined overlapping subgraphs of the query, the information about the overlapping subgraphs including a normalized signature for each overlapping subgraph; use the provided information to determine which overlapping subgraph of the overlapping subgraphs is to be materialized; for each overlapping subgraph determined to be materialized: determine whether the specific subgraph has been materialized using an exact signature corresponding to the normalized signature of the specific overlapping subgraph, the exact signature identifying the specific subgraph corresponding to the normalized signature within a specific repeated instance of the data; when the specific subgraph has not been materialized, materialize the subgraph and use the materialized subgraph to respond to the query; and when the specific subgraph has been materialized, use the materialized subgraph to respond to the query.

[0004] This Summary is provided to introduce some concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter. BRIEF DESCRIPTION OF THE DRAWINGS

[0005] Figure 1 is a functional block diagram illustrating a system for automatically detecting and reusing overlapping computations in an analytics job service.

[0006] Figure 2 is a bar graph illustrating overlap among five exemplary clusters.

[0007] Figure 3Ais a graph illustrating the distribution of percentage changes in each of a plurality of virtual clusters for a particular physical cluster.

[0008] Figure 3B is a graph illustrating the distribution of average overlap frequencies across different virtual clusters.

[0009] Figure 4A-4D is a diagram illustrating overlapping computations from virtual clusters in a particular business unit.

[0010] Figure 5A The diagram is for Figure 4A-4D A bar chart showing the percentage of subgraphs of multiple operators that overlap as shown in .

[0011] Figure 5B is a bar chart illustrating the cumulative distribution of the shuffle operator.

[0012] Figure 5C is a bar graph illustrating the cumulative distribution of the filter operator.

[0013] Figure 5D is a bar chart illustrating the cumulative distribution of user-defined processors.

[0014] Figure 6A-6D is a graph that quantifies the impact of overlap in a specific business unit.

[0015] Figure 7 is a diagram illustrating the use of a canonical signature and an exact signature.

[0016] Figure 8 is a diagram of the high-level architecture.

[0017] Fig. 9 It is a functional block of the system, which includes a feedback loop that coordinates compile-time estimates with runtime statistics.

[0018] Fig.10 is a functional block diagram of the runtime components.

[0019] Fig.11 is a schematic diagram illustrating the interaction of various components with the metadata service component at runtime.

[0020] Fig.12 is a diagram illustrating a mechanism for creating and reusing materialized views as part of query processing.

[0021] Fig.13 is a flowchart of a method for reusing overlapping calculations in an analysis job service.

[0022] Fig.14 A further illustration Fig.13Flowchart of the method.

[0023] Fig.15 is a flowchart of a method for reusing overlapping calculations in an analysis job service.

[0024] Fig.16 A further illustration Fig.14 Flowchart of the method.

[0025] Fig.17 is a functional block diagram illustrating an exemplary computing system. DETAILED DESCRIPTION

[0026] Various techniques related to automatically detecting and reusing overlapping calculations in an analysis job service are now described with reference to the accompanying drawings, wherein the same reference numerals are used to refer to the same elements throughout. In the following description, for the purpose of illustration, many specific details are set forth in order to provide a thorough understanding of one or more aspects. However, it may be apparent that such (multiple) aspects can be practiced without these specific details. In other examples, known structures and devices are shown in block diagram form to help describe one or more aspects. In addition, it should be understood that the functionality described as being performed by certain system components can be performed by multiple components. Similarly, for example, a component can be configured to perform the functionality described as being performed by multiple components.

[0027] The present disclosure supports various products and processes that perform or are configured to perform various actions related to automatically detecting and reusing overlapping computations in an analytical job service. The following are one or more exemplary systems and methods.

[0028] Various aspects of the present disclosure relate to the technical problem of detecting and reusing overlapping computations in an analysis job service. Technical features associated with solving the problem relate to: using stored information about the analyzed workload data to determine overlapping subgraphs for a query, the stored information including a normalized signature for a specific subgraph, the normalized signature identifying the specific subgraph across repeated instances of the data; providing information about the determined overlapping subgraphs for the query, the information about the overlapping subgraphs including a normalized signature for each overlapping subgraph; using the provided information to determine which overlapping subgraph in the overlapping subgraphs is to be materialized; for each overlapping subgraph determined to be materialized: using an exact signature corresponding to the normalized signature of the specific overlapping subgraph to determine whether the specific subgraph has been materialized, the exact signature identifying the specific subgraph corresponding to the normalized signature within a specific repeated instance of the data; when the specific subgraph has not been materialized, materializing the subgraph and using the materialized subgraph to respond to the query; and when the specific subgraph has been materialized, using the materialized subgraph to respond to the query. Thus, aspects of these technical features exhibit the technical effect of more efficiently and effectively providing responses to queries, for example, reducing utilization of computing resource(s) and / or reducing query response time.

[0029] Furthermore, the term "or" is intended to mean an inclusive "or" rather than an exclusive "or." That is, unless specified otherwise or clear from the context, the phrase "X employs A or B" is intended to mean any of the natural inclusive permutations. That is, the phrase "X employs A or B" is satisfied by any of the following instances: X employs A; X employs B; or X employs both A and B. In addition, the articles "a" and "an" used in this application and the appended claims should generally be construed to mean "one or more" unless specified otherwise or clear from the context to be directed to the singular.

[0030] As used herein, the terms "component" and "system" and their various forms (e.g., component, system, subsystem) are intended to refer to a computer-related entity, which is hardware, a combination of hardware and software, software, or software in execution. For example, a component can be, but is not limited to: a process running on a processor, a processor, an object, an instance, an executable file, a thread of execution, a program, and / or a computer. As an illustration, both an application running on a computer and a computer can be a component. One or more components can reside within a process and / or thread of execution, and a component can be located on one computer and / or distributed between two or more computers. In addition, as used herein, the term "exemplary" is intended to mean an illustration or example used as something, and is not intended to indicate a preference.

[0031] There has been a recent trend of analytics-as-a-service, also referred to as job services, being offered by the major cloud providers. These services are motivated by the fact that setting up and running data analytics can be a major hurdle for businesses. While platform as a service (PaaS), software as a service (SaaS), and more recently database as a service (DBaaS) have eased the pain of provisioning and scaling hardware and software infrastructure, users are still responsible for managing and tuning their servers. Job services alleviate this pain by providing serverless analytics capabilities that do not require users to provision and manage servers. Instead, the cloud provider is responsible for managing and tuning a query engine that is instant and can scale on demand. Users can get started quickly using an all-familiar SQL interface and pay only for the processing used for each query, rather than for the entire provisioned server infrastructure, regardless of the actual compute resources used.

[0032] Described herein is a computation reuse framework that can be used to address the computation overlap problem in job services. In some embodiments, aspects of the framework can include one or more of the following: (i) computations are reused by creating materialized views on repeated workloads, e.g., periodically executing jobs with the same script template but processing new data each time, (ii) views to be materialized are selected using a feedback loop that coordinates compile-time and runtime statistics and collects accurate measurements of the utility and cost of each overlapping computation, and / or (iii) materialized views are created in an online environment, e.g., without an offline phase for selecting and / or materializing overlapping computations.

[0033] Next, refer to Figure 1 , illustrates a system for automatically detecting and reusing overlapping computations in an analysis job service 100. In some embodiments, the system 100 comprises an end-to-end system for computation reuse in a job service. The system 100 comprises an analyzer component 110 and a runtime component 120, which are discussed in more detail below. In some embodiments, the analyzer component 110 is an offline component for jobs that use its output, and the runtime component 120 is an online component for those jobs. In some embodiments, the analyzer component 110 runs periodically.

[0034] The analyzer component 110 establishes a feedback loop to select the most interesting subgraphs to materialize and reuse. In some embodiments, the analyzer component 110 captures a set of interesting calculations to be reused based on its previous (multiple) runs; inserts a custom view selection method to select views to be materialized given a set of constraints; selects a physical design for the materialized view, and / or mines the expiration of each materialized view in the materialized view. In some embodiments, information can be provided by the analyzer component 110 as a query annotation. In some embodiments, the analyzer component 110 is triggered by an administrator interface. In some embodiments, the analysis performed by the analyzer component 110 is performed only with the explicit consent (e.g., opt-in) of the entity associated with the workload information.

[0035] The runtime component 120 supports online computation reuse using query annotations provided by the analyzer component 110. As discussed in more detail below, the runtime component 120 may include: a metadata service component to obtain metadata about computations related to reuse in a given job; an online view materialization mechanism as part of job execution; a synchronization mechanism to avoid materializing the same view in parallel, make materialized views available early during runtime, use materialized views for automatic query rewrite; and / or job coordination hints to maximize computation reuse.

[0036] introduce

[0037] Given the shift in cloud pricing from provisioned resources to actually consumed resources, enterprises naturally do not want to duplicate their resource consumption and pay excess dollar costs. However, this is a major challenge in modern enterprise data analytics, which includes complex data pipelines written by several users, where parts of the computation end up running over and over again. This computational overlap not only increases the dollar cost, but in some embodiments, it can also be difficult for developers and / or administrators to detect these overlaps across different scripts and / or different users.

[0038] To illustrate, consider an exemplary system that utilizes data analytics deployed on hundreds of thousands of machines, runs hundreds of thousands of production analytics jobs written by thousands of developers every day, processes several exabytes of data every day, and involves hundreds of petabytes of I / O. In some embodiments, almost 40% of the jobs per day are observed to have computational overlap with one or more other jobs. It is further observed that there are more than 10 million overlapping subgraphs (appearing at least twice), with an average overlap frequency of 3.9. These overlaps are caused by 70% of all user entities (e.g., people and machines) on these clusters. Figure 2200, which illustrates, from left to right, overlapping jobs, users with overlapping jobs, and overlapping subgraphs for five exemplary clusters. It can be observed that, except for cluster 3, all clusters have more than 45% of their jobs overlapped. Similarly, more than 65% of users on all clusters end up having computational overlap in their jobs, and the percentage of subgraphs that appear at least twice can be as high as 80%. While the ideal solution is for users to modularize their code and reuse shared script sets and intermediate data, this is not possible in practice because users are distributed across teams, job functions, and geographic locations. Therefore, as described herein, an automatic cloud-scale approach to computational reuse in job services is desired.

[0039] There are many challenges in building a computational reuse framework for analytical job services. First, enterprise data analysis often includes repeated jobs on changing data. In some embodiments, the analytical job service has more than 60% of its jobs in its key clusters as repeated. In the case of these repeated jobs, it may be important to schedule new data and materialize views wisely, which is not a problem in traditional view selection. There is incremental maintenance, but it does not work here because the data may be brand new. In some embodiments, the analytical job service jobs are further packaged in a tight data pipeline, that is, multiple jobs operate in a given time interval with strict completion deadlines. The tight data pipeline leaves almost no room to analyze the repeated workload on the new data in each repetition. Secondly, in some embodiments, a feedback loop needs to be established to analyze previously executed workloads and detect overlapping calculations. Considering the large number of overlaps, it is simply impractical to materialize all overlapping subgraphs for reuse. The selection of the overlap (or view) of interest can depend on the utility and cost of each overlap, that is, the runtime savings and storage costs of each overlap. Unfortunately, however, optimizer estimates of utility and cost are often severely inaccurate due to various factors (e.g., unstructured data, inaccurate operator selectivity, presence of user code. Therefore, in some embodiments, a feedback loop provided by the analyzer component 110 reconciles the logical query tree with actual runtime statistics to obtain a more accurate measure of the utility and cost of each overlap.

[0040] Third, in some embodiments, the job service is online (e.g., always available), and there is no offline phase available for creating materialized views, which is expected with traditional materialized views. It is typically not feasible to stop and / or delay repeated jobs to create materialized views because this carries the risk of not meeting completion deadlines and / or impacting downstream data dependencies. Therefore, in some embodiments, materialized views are created just in time (e.g., in response to queries) and with minimal overhead. In some embodiments, this may be further complicated because multiple jobs can now compete to build the view (build-build interaction) and they depend on each other for the availability of the view (build-consume interaction).

[0041] In some embodiments, the systems and methods described herein provide an end-to-end system for computation reuse that has many requirements, including automatic reuse and transparency to end users, inspired by production environments. In some embodiments, users can write their jobs as before, i.e., zero changes to user scripts, where the system 100 automatically detects and reuses computations. In some embodiments, it is assumed that an exact match is sufficient, and the focus is on exact job subgraph matching, and this makes the problem much simpler without getting into view inclusion complexity.

[0042] In some embodiments, the system 100 takes into account the physical design of materialized views, since it is observed that computation overlap often occurs at shuffle boundaries. In some embodiments, the system 100 enables computation reuse on repeated jobs through a combination of a normalized signature and an exact signature (e.g., a hash) for a computation subgraph. The normalized signature matches computations across repeated instances (e.g., independent of a specific data instance), while the exact signature matches computations within a repeated instance (e.g., specific to a specific data instance). In some embodiments, these two signatures together enable the system 100 to analyze a workload once and repeatedly reuse overlapping computations.

[0043] Reuse Opportunities

[0044] In some embodiments, overlap can be analyzed at different levels of granularity: overlap within a cluster, overlap within a business unit, and / or overlap in terms of operators.

[0045] Overlap within clusters

[0046] Figure 3A3 is a graph 300 illustrating the distribution 310 of percentage changes in each of a plurality of virtual clusters (VCs) of a particular physical cluster. A virtual cluster is a tenant that has allocated computer capacity (called a "token") and controls access privileges to its data. For these exemplary virtual clusters, analysis shows that while 8 VCs have no overlapping jobs, 54% of the VCs have more than 50% of their jobs overlapped, and 6 VCs have 100% of their jobs overlapped. Figure 3B 3 is a graph 320 illustrating a distribution 330 of average overlap frequencies across different virtual clusters. In this example, the average overlap frequencies range from 1.5 to 112 (median frequency is 2.96, 75th percentile is 3.82, and 95th percentile is 7.1). This example illustrates that computation overlap is a cluster-wide phenomenon and is generally not limited to a specific VC or workload.

[0047] In some embodiments, interactions with analytics job service clients revealed two main reasons for the ubiquitous computational overlap seen above: (i) users rarely write their analytics scripts from scratch, but rather they start with others' scripts and extend / modify them to suit their purposes, and (ii) there is a data producer / consumer model involved in the analytics job service, where multiple different consumers process the same inputs (e.g., generated by producers), and they often end up repeatedly performing the same (partial or full) post-processing on these inputs.

[0048] Overlap within business units

[0049] Figure 4A-4D 400, 410, 420, 430 illustrate overlapping computations from all VCs in a particular business unit. Note that business unit is a meaningful granularity because VCs within a business unit create a data pipeline where some VCs produce data (producers) and some VCs process downstream data (consumers).

[0050] Figure 4A-4D Cumulative distributions 440, 450, 460, 470 of overlap per job, per input, per user, and per VC are illustrated, respectively. Surprisingly, it can be observed that most jobs have 10s to 100s of subgraphs that overlap with one or more other jobs. In some embodiments, this suggests that there are a lot of opportunities to improve the data pipeline in order to reduce redundancy. In addition to reusing computation, in some embodiments, it is also possible to consider sharing computation first. Similar observations can be made from the overlap distribution 450 per input, where it can be observed that more than 90% of the inputs are consumed in the same subgraph at least twice, 40% are consumed at least five times, and 25% are consumed at least ten times. In terms of users ( Figure 4C, distribution 460), it can be observed again that each user has 10s to 100s of overlap, with the top 10% having over 1500 overlaps. In some embodiments, these large hits can be consulted separately. Finally, for VC ( Figure 4D , distribution 470), it can be observed that at least three groups have similar amounts of overlap. In general, computation overlap is widespread across jobs, inputs, users, and VCs, and can be addressed by system 100 in a systematic manner.

[0051] Overlap in operators

[0052] Overlap in terms of operators refers to the root operators of the overlapping computation subgraphs. Figure 5A The diagram is for Figure 4A – Figure 4D 500 of the percentage of subgraphs of multiple operators that overlap. It can be observed that "Sort" and "Swap" (Shuffle) constitute the first two most overlapping computations. In some embodiments, this may be of interest because these two are also typically the most expensive operations, so it makes sense to reuse them. In contrast, re-evaluating the next three most overlapping operators (i.e., "Range" (Scan), "ComputeScalar", and "RestrRemap" (typically column remapping)) is expected to be much cheaper because they are closer to the leaf level in the query tree. Among other operators of interest, it can be observed that aggregation into groups, joins, and user-defined functions (including processing, reduction, and even extractors) have significant overlap in some embodiments.

[0053] Figure 5B-5D 510, 520, 530 show the cumulative overlap distribution for three of the operators, namely shuffle (distribution 540), filter (distribution 550), and user-defined processor (distribution 560). Even though it is observed that the shuffle operator has a large number of overlapping calculations, only a small portion of the shuffle has a higher frequency. This changes for the filter operator, where the cumulative distribution is more linear, meaning that more filters have a higher frequency. Finally, for Figure 5D For the user-defined operators in (distribution 560), the curve is flatter. In some embodiments, this is because the user-defined operators may be shared by several users and teams as a library.

[0054] Overlapping influence

[0055] As mentioned above, computation overlaps occur in different clusters, VCs, and operators. The impact of these overlaps can be examined along several dimensions. Figure 6A-6D600, 610, 620, 630 quantify the impact of overlap in a particular business unit. Graphs 600, 610, 620, 630 illustrate the cumulative distribution of the frequency 640, run time 650, output size 660, and view-to-query cost ratio 670 of overlapping computations in a particular business unit, respectively (as described above).

[0056] In terms of frequency, for one day, there are 826,528 calculations that appear at least twice, of which 56,500 appear at least 10 times, 587 appear at least 100 times, and 16 appear at least 1,000 times. However, Ref. Fig. 6A , distribution 640 illustrates that the average overlap frequency is 4.2 (median frequency is 2, 75th percentile is 3, 95th percentile is 14, and 99th percentile is 36). Therefore, in some embodiments, the calculation of the overlap frequency is severely skewed and extra care is required when selecting views to materialize. Compared to frequency, Figure 6B The runtime distribution of 650 and Figure 6C The output size distribution of 660 is much less skewed. Interestingly, 26% of overlaps have runtimes under 1s, indicating an opportunity to prune many reuse candidates, and 99% of overlaps have runtimes under 1000s. In terms of output size, 35% of overlaps have sizes under 0.1MB, which is better for storage space (in cases where they are useful), and 99% of overlaps are below 1TB in size. Finally, in Fig.6D The view-to-query cost ratio illustrated in distribution 670 of is an interesting metric for understanding the relative importance of views to queries. It can be observed that 46% of the overlapping computations have view-to-query cost ratios below 0.01 (1%). These overlaps will not result in significant savings in latency, although specific customers may still be interested in their accumulative resource consumption savings. Overall, this is again a highly skewed distribution, with only 23% of overlaps having view-to-query cost ratios above 0.1, and only 4% of overlaps having ratios above 0.5.

[0057] Reuse of repeated workloads

[0058] In some embodiments, system 100 is used to materialize overlapping calculations for repetitive jobs in an analysis job service, for example, for jobs that recur (e.g., hourly, daily, weekly, and / or monthly), jobs that have template changes in each instance, and / or jobs that operate on new data each time. Conventional systems require a priori knowledge of the workload in order to analyze the workload and select views to materialize. However, as repetitive jobs change in each instance and operate on new data, the exact workload is not available until the next repetitive instance (e.g., next hour, next day, next week, next month). Therefore, in some embodiments, it is completely impractical to run a workload analysis to select views to be materialized within the same repetitive instance before running the actual job.

[0059] In some embodiments, to handle repetitive jobs, a combination of two signatures is collected for each subgraph computation: one signature precisely identifies the computation, and one signature normalizes the precise signature over repetitive changes (e.g., data / time assertions, input names). In some embodiments, any update in the input data results in a different precise signature, automatically invalidating any older materialized views for reuse. In some embodiments, the normalized signature is created by the analyzer component 110 (e.g., offline with respect to a particular job / query), and the precise signature is created by the runtime component 120 (e.g., during compilation associated with a particular job / query).

[0060] In some embodiments, the precise signature may be expanded to further include the input GUID, any user code, and / or any external libraries for custom code.The canonical signature ensures that the system 100 captures canonical computations that remain the same across different repeated instances. Figure 7700 is a schematic diagram illustrating the use of these two signatures in the method. Any repeated instances from the workload history are analyzed by the analyzer component 110, and frequent calculations (views) are selected based on their precise signatures (step 1). Their corresponding normalized signatures are collected (e.g., stored) in the metadata service component, as described below (step 2). In some embodiments, the analysis needs to be run periodically and / or only when there is a change in the workload, thereby removing the need to run workload analysis within each repeated instance. Later, during runtime, the subgraph is materialized based on the normalized signature of the subgraph (step 3), but the precise signature of each materialized view in the materialized view to the physical path of the materialized file is also recorded (step 4). The precise signature is used to match future calculations for reuse (step 5) and for expiring the materialized view (step 6). In summary, the normalized signature identifies the subgraph across repeated instances (for materialization), while the precise signature matches the subgraph in the repeated instance (for reuse). Together, they make it possible to reuse the calculation of repeated workloads.

[0061] System Architecture Overview

[0062] In some embodiments, system 100 may include one or more of the following attributes:

[0063] (1) Automatic: In some embodiments, it may be difficult for developers to coordinate and reuse overlapping computations among themselves. Therefore, in some embodiments, overlapping computations can be automatically detected, materialized, reused, and / or evicted by the system 100.

[0064] (2) Transparency: In some embodiments, with hundreds of thousands of jobs, it is simply not practical to make changes in user scripts and / or their libraries.

[0065] (3) Correct: Reuse of computations without introducing data corruption. In some embodiments, this may be challenging due to the presence of parameters, user code, and / or external libraries.

[0066] (4) Latency sensitivity: Analytical job service users usually cannot afford to slow down their data pipelines. Therefore, in some embodiments, computational reuse performed by system 100 provides better and / or equivalent performance. This requires an accurate estimate of the cost / gain of materialized views. In some embodiments, the optimizer can still discard the view if it turns out to be too expensive.

[0067] (5) Maximizing reuse: In some embodiments, system 100 performs computation reuse as much as possible. In some embodiments, this may be difficult because overlapping jobs may arrive at the same time, and thus views materialized in one job may not end up being reused.

[0068] (6) Debuggability: Some analytical job services have rich debuggability experience and computational reuse preserved by the system 100. Specifically, in some embodiments, customers (and operations teams) are able to replay the job, see which materialized views were created or used, track the job that created any view, and even gain insight into why the view was selected for materialization or reuse in the first place.

[0069] (7) Reporting: Finally, in some embodiments, the system 100 can report the impact of computation reuse on job performance, such as storage cost and / or runtime savings. In some embodiments, metadata related to overlapped computations is queryable.

[0070] Traditional materialized view technology usually has three components, namely, an offline view selection component, an offline view construction component, and an online view matching component. In some embodiments, system 100 has two online components: a periodic workload analyzer for mining overlapping calculations, and a runtime engine for materializing and reusing those calculations.

[0071] Figure 8 A high-level architecture 800 is illustrated. The left side shows an analyzer component 110 (e.g., a periodic workload analyzer) that analyzes the analysis job service workload repository. In some embodiments, the administrator(s) can choose to include and / or exclude different VCs for analysis. In some embodiments, the output of this analysis is a set of annotations that tell future jobs which subgraph computations to instantiate and reuse. Figure 8 The runtime component 120 is shown on the right side of . Here, in some embodiments, each incoming job can be processed in one of three ways: (i) exactly as before, without any job subgraphs being materialized or they are deemed too expensive by the optimizer, (ii) a modified job graph that reads from the materialized view (i.e., there are matching subgraph annotations and they are materialized), and the optimizer determines that reading from the materialized view is more efficient than recomputing; and (iii) a modified job graph that spools and materializes the output of the subgraph (i.e., there are matching subgraph annotations, but they have not yet been materialized). In some embodiments, the analyzer component 110 can be explicitly triggered by the user or scheduled as another recurring job, while the runtime component 120 is triggered by providing a command line flag during job submission. In some embodiments, the end user's job script remains unchanged.

[0072] Analyzer Components

[0073] In some embodiments, features of the analyzer component 110 include: (i) providing a feedback loop for runtime statistics, (ii) selecting a physical design for a selected view to be materialized, (iii) determining expiration of a materialized view, and / or (iv) providing a user interface for tuning and / or visualizing workload analysis.

[0074] Feedback loop

[0075] Choosing the right set of views to materialize can be a difficult problem. In cases where views are to be materialized, some systems rely on what-if optimization to estimate the expected improvement. Unfortunately, due to the presence of complex directed acyclic graphs (DAGs) and / or user code, the optimizer's cost estimates are often far off. The problem can be even more severe in a distributed cloud setting, where virtual hardware and scheduling issues make it more difficult to model the actual gains in terms of job latency. As a result, in some examples, the actual improvement from materialized views may be much lower, while their actual materialization cost may be much higher than the estimated materialization cost. In addition, materializing views that are ultimately not used later wastes customer money in the job service. In some embodiments, the selection of views to materialize is further complicated by dynamic resource allocation within the job graph and additional resource allocation in the analysis job service.

[0076] In some embodiments, system 900 includes a feedback loop that reconciles compile-time estimates with runtime statistics, such as Fig. 9As depicted in . In some embodiments, the feedback mechanism provided by the analyzer component 110 goes beyond learning from the same query and considers arbitrarily fine-grained commonalities across multiple jobs. In some embodiments, this is done by enumerating subgraphs (e.g., all possible subgraphs) of (multiple) jobs (e.g., all jobs) seen within a time window in the past (e.g., a day or a week) and finding common subgraphs across them. For example, queries Q1 and Q2 that read attributes (A, B) and (A, C), respectively, would generate the view (A, B, C) as a candidate even though it is neither a subgraph of Q1 nor a subgraph of Q2. Although in some embodiments, this is more restricted than considering common views, the subgraphs considered have actually been used in the past (e.g., and may also be used in the future) and have runtime statistics available from previous runs (allowing for more accurate predictions of costs). In order to use runtime statistics from previous runs, the job data stream (e.g., the job data stream that was actually executed on a cluster of machines) is connected back to the job query graph (e.g., a tree representation of the input user query). In some embodiments, this is accomplished by linking the operators executed at each stage in the data stream to the operators in the query graph. Then, for each query subgraph, the corresponding runtime statistics are extracted from the data stream. These include latency (the time taken to execute the subgraph), cardinality (the number of output rows in the subgraph), data size (in bytes), and / or resource consumption (CPU, memory, parallelism, IO, etc.). In some embodiments, where multiple operators are pipelined in the data stream, runtime statistics such as resource consumption can be attributed to individual operators, for example by subdividing the total resource consumption of the pipelined operators based on the exclusive run time of each operator in the pipeline.

[0077] In some embodiments, the feedback loop provided by the analyzer component 110 can have one or more benefits. First, in some embodiments, there is an inevitable duplication of analysis in user scripts due to the fact that multiple analyses require common data preparation and / or simply due to the fact that developers usually start with someone else's script before adding their own logic. With the feedback loop in the job service, users do not have to worry about deduplicating their scripts; the system 100 takes care of deduplication automatically at runtime. Second, in some embodiments, runtime statistics can provide a more predictable measure of the cost and benefit of view materialization, thereby giving customers a better understanding of how much the customer will pay for the feature and how much the customer will save. Third, in some embodiments, the feedback loop makes it more likely that the selected (and materialized) subgraphs will actually be used in future jobs, compared to selecting materialized views based on cost estimates and then finding them useless if the estimates prove to be incorrect. Fourth, in some embodiments, as in more general view selection scenarios, the feedback loop considers job subgraphs without considering whether two or more subgraphs are merged. This ensures that materializing a view never requires additional computation (and therefore no additional money) beyond that done in any way by the job using the view. Finally, in some embodiments, runtime statistics observed from a job's subgraphs are shared across future queries with any of those subgraphs. In fact, for any new job that comes in, the system may already know the costs of several of its subgraphs, and may decide not to recompute them.

[0078] Select the subgraph to materialize

[0079] As described above, in some embodiments, the analyzer component 110 can simplify the view selection problem by limiting the selection to common subgraphs. Although this is limited when compared to more generalized view selection, in some embodiments, the analyzer component 110 is able to capture accurate utility and cost estimates because the subgraphs have been executed in the past. Additionally, in some embodiments, during query rewrite, the runtime component 120 can simply scan the materialized views without incurring any other post-processing, and therefore the gains can be more predictable.

[0080] In some embodiments, the analyzer component 110 selects a subgraph to materialize using one or more methods:

[0081] (i) Select the top k subgraphs using one or more heuristics (e.g., total subgraph utility, total utility normalized by storage cost), and / or limit to at most one subgraph per job. In some embodiments, the analyzer component 110 allows users to insert custom heuristics to narrow down to the subgraphs they are interested in.

[0082] (ii) Given a set of constraints (e.g., storage constraints, view interaction constraints), pack the most interesting subgraphs (or subexpressions).

[0083] Physical Design

[0084] In some embodiments, the analyzer component 110 considers the physical design of the materialized views. Conventionally, the physical design of materialized views has not been considered important because views and their physical design are not typically selected at the same time. However, in some embodiments, it has been observed that materialized views with poor physical designs are ultimately not used because the computational savings are overshadowed by any additional repartitioning and / or sorting that the system needs to do to utilize the materialized view. This occurs because in the case of large data sets and large amounts of parallel processing in an analytical job service job, repartitioning and / or sorting is typically the slowest step in the job execution.

[0085] In some embodiments, the system 100 pays close attention to the physical design of (multiple) views. To this end, the analyzer component 110 can extract the output physical properties (e.g., partition type, partition column, partition number, sort column and / or sort direction) of each subgraph in the subgraph when enumerating them. In some embodiments, the output physical properties can be a good hint of the physical design of the view because they are expected by subsequent operators in the job graph. In the absence of explicit physical properties at the root of the subgraph, the system 100 can infer them from the children, i.e., traverse downward until one or more physical properties are encountered. Depending on how overlapping subgraphs are used in different jobs, there may be multiple sets of physical properties for the same subgraph. In some embodiments, the default strategy is to pick the most popular set. However, in some embodiments, in the absence of an explicit selection, multiple physical designs (of the same view) can be treated as different views and fed to the view selection routine.

[0086] Expiration and removal

[0087] Although storage is cheap in some embodiments, the storage space used by materialized views still needs to be reclaimed periodically. In some embodiments, a simple heuristic is to remove all views from previous repeated instances. However, in some embodiments, the output of an hourly job may also be used in a weekly job and / or a monthly job. Therefore, in some embodiments, removing views after every hour / day may be wasteful. In some embodiments, the lineage of the inputs to a view is tracked, i.e., for each of the view inputs, the longest duration it has been used by any repeated job is checked. In some embodiments, the maximum value of all such durations gives a good estimate of the expiration of a view. In addition to using standard analytical job service scripts, this lineage tracking can also be supported using provenance tools. The view expiration thus obtained can be encoded into a physical file, and the storage manager can be responsible for clearing the file after it expires.

[0088] In some embodiments, the cluster administrator(s) may also reclaim a given storage space by running the same view selection routine as above, but replacing the maximum objective function with a minimum objective function, i.e., picking the view with the minimum utility. In the worst case, the materialized view files can simply be wiped from the cluster. However, in some embodiments, both operators described above need to first clear the views from the metadata service (discussed below) before deleting any physical files (to ensure that jobs consuming any of these inputs do not fail).

[0089] user interface

[0090] In some embodiments, the system 100 provides one or more ways to interact with the analyzer component 110. First, a command line interface can be provided to run the analyzer component 110 on a user-specific cluster, VC, and / or time range. In some embodiments, users can also provide their custom constraints, such as storage cost, latency, CPU hours, and / or frequency, to filter overlapping computations. In some embodiments, the system 100 provides a powerful business intelligence dashboard for users to review various (multiple) summaries from the computational overlap analysis, as well as for users to drill down into the top (e.g., 100) overlapping computations in more detail. In summary, the goal is to help users understand the computational overlap in their workloads and adjust computational reuse as needed.

[0091] Runtime components

[0092] The runtime component 120 supports computation reuse during online query processing. Fig. 9 In the case of Fig.10In some embodiments, the runtime component 120 includes one or more of: (i) a metadata service component 1000 for querying relevant overlaps in each incoming job, (ii) an online view materialization component 1010 for materializing views as part of query processing; (iii) a synchronization component 1020 for preventing concurrent jobs from materializing the same view; (iv) early materialization for publishing a materialized view even before the job that generated it is completed, (v) an automatic query rewrite component 1030 for using materialized views whenever possible, and / or (vi) a job scheduler 930 that provides hints to maximize computational reuse.

[0093] Metadata Service Components

[0094] In some embodiments, the metadata service component 1000 provides information about overlapping computations and coordinates the materialization and reuse of those computations. In an online setting (i.e., data batches and jobs arrive continuously), view materialization and reuse are dynamic activities. Therefore, instead of simply looking up the view in the compiler 1010, at runtime, multiple analysis job service components interact with the metadata service component 1000. Fig.11 1100 is a schematic diagram illustrating the interaction of various components with the metadata service component 1100 at runtime. First, the compiler 910 asks the metadata service component 1100 for overlapping calculations (views) for a given job J (step 1). In some embodiments, a naive approach is to have the compiler 910 search each subgraph separately to check whether this is an overlapping calculation. However, since the analysis job service job graph may be very large, the number of search requests may become too large, resulting in higher compilation overhead and higher throughput requirements for the metadata service component 1100. In some embodiments, on the contrary, each job makes a request, and all overlaps that may be associated with the job are obtained. In some embodiments, this can be accomplished by creating a reverse index as follows. For each overlapping calculation instance, a label is extracted from its corresponding job metadata. Labels for repeated jobs are normalized, and a reverse index is created on the label to point to the corresponding normalized signature. The metadata service component 1000 returns a list of normalized signatures related to J to the compiler 910 (step 2). In some embodiments, the signatures returned by the metadata service component 1000 may contain false positives, and the optimizer 920 may still need to match them to the actual signatures in the query tree.

[0095] Secondly, when the optimizer 920 attempts to materialize overlapping computations, it proposes materialization to the metadata service component 1000 (step 3). The metadata service component 1000 attempts to create an exclusive lock to materialize the view. Due to a large number of concurrently running jobs, the same view may have been materialized by another job, i.e., the lock already exists. In this case, the service returns a failure message, otherwise, it returns success (step 4). Note that in some embodiments, the average running time of the view subgraph is mined from past events and used to set the expiration of the exclusive lock. Once the exclusive lock expires, and if the view has not been materialized, another job can attempt to create the same materialized view. This gives the system 100, 900 fault-tolerant behavior for view materialization.

[0096] Finally, the job manager 1110 reports the successful materialization of the view to the metadata service component 1100 (step 5), and the metadata service component 1000 confirms the lock release (step 6). The metadata service component 1000 now makes the materialized view available for reuse by other jobs, i.e., it will appear the next time the compiler 910 requests the relevant view for a job (step 1). In some embodiments, the metadata service component 1000 periodically polls the output of the analyzer component 110 and loads the selected set of overlapping computations whenever a new analysis is available. In some embodiments, computations may expire at regular intervals.

[0097] The system 900 also includes a scheduler 930 that stores execution graphs and resources for queries in a repository 950, and a job manager 940 that stores actual runtime statistics for queries in the repository 950 and provides result(s) to the queries. In some embodiments, the compiler 910 stores the compiled query DAG in the repository 950. In some embodiments, the optimizer 920 stores the optimization plan and estimated statistics in the repository 950. In some embodiments, the analyzer component 110 can utilize at least some of the information stored in the repository 950 when performing its analysis, as discussed above.

[0098] Online physicalization

[0099] Traditional materialized views typically require an offline process where, for example, the database administrator is responsible for first creating all relevant materialized views, a preprocessing step, before the database becomes available to run query workloads. This is impractical for repetitive jobs running in tight data pipelines with strict completion deadlines, where there is little room for preprocessing to create materialized views. Preprocessing can block repetitive jobs, causing them to miss their completion deadlines. Repetitive jobs also have data dependencies between them, i.e., the results of one repetitive job are used in subsequent repetitive jobs. Therefore, missing a completion deadline for one repetitive job can affect the entire data pipeline.

[0100] In some embodiments, the online view materialization component 1010 of the runtime component 120 provides a mechanism for creating and reusing materialized views as part of query processing, such as Fig.12 As depicted in . Fig.12 is a schematic diagram 1200 illustrating a core plan search 1210 and subsequent optimization 1220. After obtaining the relevant normalized signatures from the metadata service component 1000, the compiler 910 provides them to the query optimizer 920 as annotations. In some embodiments, the compiler 910 also retains the annotations as a job resource for future repeatability. In some embodiments, before attempting to materialize one or more views in a subsequent optimization phase as illustrated in the subsequent optimization 1220, the optimizer 920 first checks all reuse opportunities in the plan search phase. This ensures that views that have already been materialized (and are available) are not attempted to be materialized. During the subsequent optimization 1220, the optimizer 920 checks whether the normalized signature of any subgraph matches the signature in the annotation. In some embodiments, the normalized signatures are matched in a bottom-up manner (smaller views are materialized first because they typically have more overlap), and the number of views that can be materialized in the job is limited (which can be changed by the user via job submission parameters). In the event of a match, the optimizer 920 proposes to materialize the view ( Fig.11 3 in ). Upon receiving success from metadata service component 1000, optimizer 920 adjusts the query plan to output a copy of the sub-computation to the materialized view, while keeping the rest of the query plan unmodified as before. The new output operator also enforces the physical design mined by the analyzer for the view. In some embodiments, optimizer 920 is responsible for adding any additional partitioning / sorting operators to meet those physical property requirements. In some embodiments, optimizer 920 stores the exact signature of each materialized view and an identifier of the job that generated the materialized view (for tracking view provenance) in the physical path of the materialized file.

[0101] In some embodiments, the online view materialization component 1010 can perform the following features. First, in some embodiments, the online view materialization component 1010 includes a mechanism for creating materialized views with minimal overhead, introduced as part of query processing, without any upfront preprocessing that would block repeated queries. Second, the first query that hits the view to be materialized causes it to be materialized, and subsequent queries will reuse it as much as possible. As a result, the online view materialization component 1010 materializes views and therefore consumes storage only when they are needed, rather than, for example, creating them a priori long before they will be used. Third, the online view materialization component 1010 does not need to coordinate between queries that materialize views (as part of their execution) and queries that reuse the materialized view; in the event that multiple queries arrive at the same time, the query that completes first materializes the view. Fourth, in the event that there is a change in the query workload starting from a given repeated instance, at this time, the view materialization based on the previous workload analysis is automatically stopped because the signature no longer matches. This avoids paying for and consuming resources for redundant views that will not be used at all. In some embodiments, this can also indicate that it is time to rerun the workload analysis. Finally, in some embodiments, the online view materialization component 1010 does not affect any user infrastructure in its analysis stack. This means that user scripts, data pipelines, query submissions, and / or job scheduling can remain intact.

[0102] In some embodiments, for traditional users who have enough space to perform advance view materialization (e.g., weekly analysis), the system 100 can still provide an offline view materialization mode. In this mode, the optimizer 920 extracts matching overlapping computation subgraphs while excluding any remaining operations in the job. The generated plan only materializes the view and can be executed offline, i.e., before running the actual workload. The offline mode can be configured at the VC level in the metadata service component 1000, and later, depending on the configuration of the metadata service component 1000, the annotations passed to the optimizer 920 are marked as online or offline.

[0103] Query Rewrite 1030

[0104] In some embodiments, to rewrite queries using materialized views, query rewrite component 1030 includes additional tasks in the volcano style plan search. Fig.12As shown in the core plan search 1210 of , the additional task matches the normalized signature retrieved from the metadata service with the normalized signature of each query subgraph in the query subgraph in a top-down manner (e.g., the largest materialized view is matched first). In the case of a match, the optimizer 920 also matches the exact signature. In some embodiments, the materialized view can be reused only if the exact signature matches. In this case, the optimizer 920 adds an alternative sub-expression plan that reads from the materialized view. In some embodiments, the number of materialized views that can be used to answer the query is unlimited. Once all applicable materialized views have been added as alternative sub-expressions, the optimizer 920 selects the best plan based on cost estimates, that is, if the read cost of one or more materialized views is too high, the one or more materialized views may not be used in the end. The plan that reads from the materialized view also loads the actual statistics (for the sub-computation) and propagates these statistics up the query tree. This makes it more confident to decide whether the plan using the materialized view is actually a good plan. Overall, in some embodiments, fully automatic query rewrite using views is provided, with zero changes to user scripts.

[0105] Sync 1020

[0106] In some embodiments, runtime component 120 includes synchronization features including: (i) build-build synchronization, i.e., no multiple jobs materialize the same view, and / or (ii) build-use synchronization, i.e., once a computation is materialized, the computation is reused. In some embodiments, build-build synchronization is handled by attempting to reuse the computation before attempting to materialize it, as described above. For concurrent jobs, exclusive locks can be created via metadata service component 1000, as described above. In some embodiments, metadata service component 1000 is provided by a consistent lock. supported, and only a single job can actually materialize a view at a time. To handle build-to-use synchronization, in some embodiments, the analytics job service job manager 940 is modified to publish the materialized view as soon as it is available. This means that the materialized view output is available even before the job that produces it is completed. This is called early materialization. Early materialization is a semantic change because it breaks the atomicity of the analytics job service job, however it is very useful because the view can be a much smaller subgraph of the entire job graph. In addition, the materialized view is not a user output, but is treated as a system output, and therefore does not affect user contracts. Finally, in some embodiments, early materialization also helps in the event of a job failure, because the job can now be restarted from the materialized view, i.e., the early materialization acts as a checkpoint.

[0107] Operation coordination

[0108] The ideal scenario for computation reuse is when one of the jobs with overlapping computations is scheduled before other jobs, so that the view can be calculated exactly once and reused by other jobs. However, in some embodiments, multiple jobs containing the same overlapping computations can be scheduled concurrently. In this case, multiple jobs will recalculate the same subgraph and even try to materialize it (although only one will prevail). In some embodiments, this can be alleviated by reordering the duplicate jobs in the client job submission system. To this end, in addition to selecting the computations of interest to materialize, in some embodiments, the analyzer component 110 also provides the submission order of the duplicate jobs containing those computations, which will bring the greatest benefit. This can be performed in the following manner: clustering jobs with the same overlap (jobs with multiple overlaps can appear in multiple groups), and selecting the job with the smallest runtime from each group, or in the case of a tie, selecting the job with the least overlap. The deduplication list of the above jobs will create a materialized view that can be used by other jobs, and therefore they will be run first (e.g., sorted by their runtime, and the number of overlaps is used to break the tie). This sorting can be enforced using the analysis job service client-side job submission tool.

[0109] Figure 13-Figure 16 Illustrated is an exemplary method related to detecting and reusing overlapping calculations in an analysis job service. Although these methods are shown and described as a series of actions performed in sequence, it should be understood and appreciated that the method is not limited by the order of the sequence. For example, some actions may appear in a different order than described herein. In addition, an action may appear simultaneously with another action. In addition, in some cases, all actions may not be required to implement the method described herein.

[0110] In addition, the actions described herein may be computer executable instructions that may be implemented by one or more processors and / or stored on one or more computer readable media. Computer executable instructions may include routines, subroutines, programs, execution threads, etc. Further, the results of the actions of the method may be stored in a computer readable medium, displayed on a display device, etc.

[0111] refer to Fig.13 and Fig.14 , illustrates a method for reusing overlapping computations in an analysis job service 1300. In some embodiments, the method 1300 is performed by the system 100 and / or the system 900.

[0112] At 1310, a query is received. At 1320, overlapping subgraphs for the query are determined using stored information about the analyzed workload data. The stored information includes a normalized signature for a particular subgraph. The normalized signature identifies a particular subgraph across repeated instances of the data.

[0113] At 1330, information about the determined overlapping subgraphs of the query is provided. The information about the overlapping subgraphs includes a normalized signature for each overlapping subgraph. At 1340, it is determined which of the overlapping subgraphs is to be materialized.

[0114] At 1350, a determination is made as to whether a particular subgraph has been materialized using an exact signature that corresponds to a normalized signature for a particular overlapping subgraph. The exact signature identifies a particular subgraph that corresponds to the normalized signature within a particular repetitive instance of the data. At 1360, a determination is made as to whether the particular subgraph has been materialized. If the determination at 1360 is "no," then at 1370, the particular subgraph is materialized and used to respond to the query, and processing continues at 1390. If the determination at 1360 is "yes," then at 1380, the materialized particular subgraph is used to respond to the query.

[0115] At 1390 , a determination is made as to whether there are more overlapping subgraphs to be materialized. If the determination at 1390 is “no,” no further processing occurs. If the determination at 1390 is “yes,” processing continues at 1350 .

[0116] refer to Fig.15 and Fig.16 , illustrates a method for reusing overlapping computations in an analysis job service 1500. In some embodiments, the method 1500 is performed by the system 100 and / or the system 900.

[0117] At 1510, a query is received. At 1520, overlapping subgraphs for the query are determined using stored information about the analyzed workload data. The stored information includes a normalized signature for a particular subgraph. The normalized signature identifies a particular subgraph across repeated instances of the data.

[0118] At 1530, information about the determined overlapping subgraphs of the query is determined. The information about the overlapping subgraphs includes a normalized signature for each overlapping subgraph. At 1540, it is determined which of the overlapping subgraphs is to be materialized.

[0119] At 1550, for each overlapping subgraph determined to be materialized: at 1560, determine whether the particular subgraph has been materialized using an exact signature corresponding to the normalized signature of the particular overlapping subgraph, the exact signature identifying the particular subgraph corresponding to the normalized signature within a particular repetitive instance of the data; at 1570, when the particular subgraph has not yet been materialized, materialize the subgraph and use the materialized subgraph to respond to the query; and at 1580, when the particular subgraph has been materialized, use the materialized subgraph to respond to the query.

[0120] This article describes a system for reusing overlapping computations in an analysis job service, the system comprising: a computer comprising a processor and a memory, the memory having computer executable instructions stored thereon, the computer executable instructions causing the computer to: receive a query; determine overlapping subgraphs for the query using stored information about the analyzed workload data, the stored information comprising a normalized signature for a specific subgraph, the normalized signature identifying the specific subgraph across repeated instances of the data; provide information about the determined overlapping subgraphs for the query, the information about the overlapping subgraphs comprising a normalized signature for each overlapping subgraph; use the provided information to determine which overlapping subgraph in the overlapping subgraphs is to be materialized; for each overlapping subgraph determined to be materialized: determine whether the specific subgraph has been materialized using a precise signature corresponding to the normalized signature of the specific overlapping subgraph, the precise signature identifying the specific subgraph corresponding to the normalized signature within a repeated instance of the specific data; when the specific subgraph has not been materialized, materialize the subgraph and use the materialized subgraph to respond to the query; and when the specific subgraph has been materialized, use the materialized subgraph to respond to the query.

[0121] The system may also include: wherein analysis of the workload data is performed prior to receiving the query. The system may include: wherein the normalized signature is independent of a specific instance of the data. The system may also include: the stored information about the analyzed workload data also includes at least one of: a compiled query subgraph, an optimized plan and estimated statistics, an execution graph and resources, or actual runtime statistics of the executed query. The system may include: wherein the stored information about the analyzed workload data also includes information about a physical design for at least one subgraph to be materialized.

[0122] The system may also include: wherein the storage information about the analyzed workload data also includes information about the expiration of the materialized subgraph. The system may include: the storage information about the analyzed workload data is based at least in part on user-selectable settings, and the user-selectable settings are related to at least one of the following: storage cost, latency, CPU hours, or frequency. The system may also include: wherein the storage information about the analyzed workload data is based on analysis of runtime statistics of subgraphs of multiple jobs, and the runtime statistics include at least one of the following: latency, cardinality, data size, or resource consumption of the subgraph.

[0123] This article describes a method for automatically reusing overlapping computations in an analysis job service, comprising: receiving a query; determining overlapping subgraphs for the query using stored information about the analyzed workload data, the stored information including a normalized signature for a specific subgraph, the normalized signature identifying the specific subgraph across repeated instances of the data; providing information about the determined overlapping subgraphs for the query, the information about the overlapping subgraphs including a normalized signature for each overlapping subgraph; using the provided information to determine which overlapping subgraph in the overlapping subgraphs is to be materialized; for each overlapping subgraph determined to be materialized: determining whether the specific subgraph has been materialized using a precise signature corresponding to the normalized signature of the specific overlapping subgraph, the precise signature identifying the specific subgraph corresponding to the normalized signature within repeated instances of the specific data; when the specific subgraph has not been materialized, materializing the subgraph and using the materialized subgraph to respond to the query; and when the specific subgraph has been materialized, using the materialized subgraph to respond to the query.

[0124] The method may also include: wherein prior to receiving the query, analysis of the workload data is performed. The method may include: wherein the normalized signature is independent of a specific instance of the data. The method may also include: wherein the stored information about the analyzed workload data also includes at least one of: a compiled query subgraph, an optimized plan and estimated statistics, an execution graph and resources, or actual runtime statistics of the executed query.

[0125] The method may further include: wherein the stored information about the analyzed workload data further includes information about a physical design for at least one subgraph to be materialized. The method may further include: wherein the stored information about the analyzed workload data further includes information about expiration of the materialized subgraph.

[0126] The method may further include: wherein the storage information about the analyzed workload data is based at least in part on user selectable settings, the user selectable settings being related to at least one of: storage cost, latency, CPU hours, or frequency. The method may include: wherein the storage information about the analyzed workload data is based on analysis of runtime statistics of subgraphs of the plurality of jobs, the runtime statistics comprising at least one of: latency, cardinality, data size, or resource consumption of the subgraphs.

[0127] The present invention describes a computer storage medium that stores computer-readable instructions that, when executed, cause a computing device to: determine overlapping subgraphs for a query using stored information about analyzed workload data, the stored information including a normalized signature for a specific subgraph, the normalized signature identifying the specific subgraph across repeated instances of the data; provide information about the determined overlapping subgraphs for the query, the information about the overlapping subgraphs including a normalized signature for each overlapping subgraph; use the provided information to determine which overlapping subgraph of the overlapping subgraphs is to be materialized; for each overlapping subgraph determined to be materialized: determine whether the specific subgraph has been materialized using a precise signature corresponding to the normalized signature of the specific overlapping subgraph, the precise signature identifying the specific subgraph corresponding to the normalized signature within repeated instances of the specific data; when the specific subgraph has not yet been materialized, materialize the subgraph and use the materialized subgraph to respond to the query; and when the specific subgraph has been materialized, use the materialized subgraph to respond to the query.

[0128] The computer storage medium may also include: wherein the stored information about the analyzed workload data also includes at least one of the following: compiled query subgraphs, optimized plans and estimated statistics, execution graphs and resources, or actual runtime statistics of the executed queries. The computer storage medium may also include: wherein the stored information about the analyzed workload data also includes information about the physical design for at least one subgraph to be materialized. The computer storage medium may also include: wherein the stored information about the analyzed workload data also includes information about the expiration of the materialized subgraphs.

[0129] refer to Fig.17 , illustrates an example general purpose computer or computing device 1702 (e.g., a mobile phone, desktop, laptop, tablet, watch, server, handheld programmable consumer or industrial electronic device, set-top box, gaming system, computing node). For example, computing device 1702 can be used in a system for automatically detecting and reusing overlapping computations in an analysis job service 100.

[0130] The computer 1702 includes one or more processors 1720, a memory 1730, a system bus 1740, (multiple) mass storage devices 1750, and one or more interface components 1770. The system bus 1740 can communicatively couple at least the above-mentioned system components. However, it should be understood that in its simplest form, the computer 1702 can include one or more processors 1720 coupled to the memory 1730, and the one or more processors 1720 execute various computer-executable actions, instructions and / or components stored in the memory 1730. For example, the instructions can be instructions for implementing the functionality described as being performed by one or more components discussed above or instructions for implementing one or more of the methods described above.

[0131] The processor(s) 1720 may be implemented using a general purpose processor, a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof, designed to perform the functions described herein. The general purpose processor may be a microprocessor, but alternatively, the processor may be any processor, controller, microcontroller, or state machine. The processor(s) 1720 may also be implemented as a combination of computing devices, such as a combination of a DSP and a microprocessor, multiple microprocessors, a multi-core processor, one or more microprocessors in combination with a DSP core, or any other such configuration. In one embodiment, the processor(s) 1720 may be a graphics processor.

[0132] Computer 1702 may include or otherwise interact with various computer-readable media to facilitate controlling computer 1702 to implement one or more aspects of the claimed subject matter. Computer-readable media can be any available media that can be accessed by computer 1702, and includes both volatile and nonvolatile media, and removable and non-removable media. Computer-readable media can include two different and mutually exclusive types, namely computer storage media and communication media.

[0133] Computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information, such as computer readable instructions, data structures, program modules or other data. Computer storage media include storage devices, such as memory devices (e.g., random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM)), magnetic storage devices (e.g., hard disks, floppy disks, cassettes, tapes), optical disks (e.g., compact disks (CDs), digital versatile disks (DVDs)) and solid-state devices (e.g., solid-state drives (SSDs), flash drives (e.g., cards, sticks, key drives)), or any other similar media that stores (rather than transmits or transfers) the desired information accessible by the computer 1702. Therefore, computer storage media excludes modulated data signals and what is described with respect to communication media.

[0134] Communication media embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism, and includes any information delivery media. The term "modulated data signal" means a signal that has one or more of its characteristics set or changed in a manner that encodes the information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media.

[0135] Memory 1730 and mass storage device(s) 1750 are examples of computer-readable storage media. Depending on the exact configuration and type of computing device, memory 1730 may be volatile (e.g., RAM), non-volatile (e.g., ROM, flash memory), or some combination of the two. By way of example, a basic input / output system (BIOS), including basic routines to transfer information between elements within computer 1702, such as during startup, may be stored in non-volatile memory, while volatile memory may act as external cache memory to facilitate processing by processor(s) 1720, and the like.

[0136] The mass storage device(s) 1750 include removable / non-removable, volatile / non-volatile computer storage media for storing large amounts of data relative to the memory 1730. For example, the mass storage device(s) 1750 include, but are not limited to, one or more devices such as a magnetic or optical disk drive, a floppy disk drive, a flash memory, a solid state drive, or a memory stick.

[0137] Memory 1730 and mass storage device(s) 1750 may include or have stored therein an operating system 1760, one or more applications 1762, one or more program modules 1764, and data 1766. Operating system 1760 is used to control and allocate resources of computer 1702. Application 1762 includes one or both of system and application software, and may be used by operating system 1760 to perform one or more actions using resource management through program modules 1764 and data 1766 stored in memory 1730 and / or mass storage device(s) 1750. Thus, application 1762 may turn general purpose computer 1702 into a special purpose machine according to the logic provided thereby.

[0138] All or part of the claimed subject matter may be implemented using standard programming and / or engineering techniques to produce software, firmware, hardware, or any combination thereof to control a computer to implement the disclosed functionality. By way of example and not limitation, the system 100 or a portion thereof may be or form part of an application 1762 and include one or more modules 1764 and data 1766 stored in memory and / or (multiple) mass storage devices 1750, which functionality may be implemented when executed by one or more processors 1720.

[0139] According to a specific embodiment, (multiple) processors 1720 may correspond to a system on chip (SOC) or similar architecture, including or in other words, integrating both hardware and software on a single integrated circuit substrate. Here, (multiple) processors 1720 may include one or more processors and a memory at least similar to (multiple) processors 1720 and memory 1730, etc. Conventional processors include a minimum amount of hardware and software and rely extensively on external hardware and software. In contrast, the SOC implementation of the processor is more powerful because it has embedded hardware and software therein, and the hardware and software can implement specific functionality with minimal reliance on external hardware and software or without reliance on external hardware and software. For example, system 100 and / or associated functionality may be embedded in hardware in a SOC architecture.

[0140] The computer 1702 also includes one or more interface components 1770 that are communicatively coupled to the system bus 1740 and support interaction with the computer 1702. By way of example, the interface component 1770 can be a port (e.g., serial, parallel, PCMCIA, USB, FireWire) or an interface card (e.g., sound, video), etc. In one example implementation, the interface component 1770 can be implemented as a user input / output interface to enable a user to input commands and information into the computer 1702 through one or more input devices (e.g., such as a mouse, trackball, stylus, touchpad, keyboard, microphone, joystick, game controller, satellite dish, scanner, camera, other computer pointing device), such as through one or more gestures or voice input. In another example implementation, the interface component 1770 can be implemented as an output peripheral interface to provide output to a display (e.g., LCD, LED, plasma), a speaker, a printer, and / or other computers, etc. Further, the interface component 1770 can be implemented as a network interface to enable communication with other computing devices (not shown), such as through a wired or wireless communication link.

[0141] What has been described above includes examples of various aspects of the claimed subject matter. Of course, for the purposes of describing the claimed subject matter, it is not possible to describe every conceivable combination of components or methods, but one of ordinary skill in the art will recognize that many other combinations and permutations of the disclosed subject matter are possible. Therefore, the disclosed subject matter is intended to encompass all such changes, modifications, and variations that fall within the spirit and scope of the appended claims. Furthermore, to the extent that the term "comprising" is used in the detailed description or in the claims, such term is intended to be inclusive in a manner similar to the term "comprising" as interpreted when "comprising" is used as a transitional word in a claim.

Claims

1. A system for reusing overlapping computations in an analysis job service, the system comprising: A computer, comprising a processor and a memory having stored thereon computer-executable instructions, which when executed by the processor cause the computer to: Receive inquiries; determining overlapping subgraphs for the query using stored information about the analyzed workload data, the stored information including a normalized signature for a particular subgraph, the normalized signature identifying the particular subgraph across repeated instances of the data; providing information about the determined overlapping subgraphs of the query, the information about overlapping subgraphs comprising a normalized signature for each overlapping subgraph; Using the provided information, determining which of the overlapping sub-graphs is to be materialized; For each overlapping subgraph that is determined to be materialized: determining whether the particular subgraph has been materialized using a precise signature corresponding to the canonical signature of the particular subgraph, the precise signature identifying the particular subgraph corresponding to the canonical signature within a particular repetition instance of the data; When the specific subgraph has not been materialized, materializing the specific subgraph and using the materialized specific subgraph to respond to the query; as well as When the specific subgraph has been materialized, the query is responded to using the materialized specific subgraph.

2. The system of claim 1, wherein analysis of workload data is performed prior to receiving the query.

3. The system of claim 1, wherein the canonical signature is independent of a specific instance of data.

4. The system of claim 1 , wherein the stored information about the analyzed workload data further includes at least one of: compiled query subgraphs, optimized plans and estimated statistics, execution graphs and resources, and actual runtime statistics of the executed queries. 5 . The system of claim 1 , wherein the stored information about the analyzed workload data further comprises information about a physical design for at least one subgraph to be materialized. 6 . The system of claim 1 , wherein the stored information about the analyzed workload data further includes information about expiration of the materialized subgraph.

7. The system of claim 1, wherein the storage information regarding the analyzed workload data is based at least in part on user-selectable settings related to at least one of: storage cost, latency, CPU hours, and frequency.

8. A system according to claim 1, wherein the storage information about the analyzed workload data is based on analysis of runtime statistics of subgraphs of multiple jobs, and the runtime statistics include at least one of the following: latency, cardinality, data size, and resource consumption of the subgraphs.

9. A method for automatically reusing overlapping calculations in an analysis job service, the method comprising: Receive inquiries; determining overlapping subgraphs for the query using stored information about the analyzed workload data, the stored information including a normalized signature for a particular subgraph, the normalized signature identifying the particular subgraph across repeated instances of the data; providing information about the determined overlapping subgraphs of the query, the information about overlapping subgraphs comprising a normalized signature for each overlapping subgraph; Using the provided information, determining which of the overlapping sub-graphs is to be materialized; For each overlapping subgraph that is determined to be materialized: determining whether the particular subgraph has been materialized using a precise signature corresponding to the canonical signature of the particular subgraph, the precise signature identifying the particular subgraph corresponding to the canonical signature within a particular repetition instance of the data; When the specific subgraph has not been materialized, materializing the specific subgraph and using the materialized specific subgraph to respond to the query; as well as When the specific subgraph has been materialized, the query is responded to using the materialized specific subgraph.

10. The method of claim 9, wherein analysis of workload data is performed prior to receiving the query.

11. The method of claim 9, wherein the canonical signature is independent of a specific instance of data.

12. The method of claim 9, wherein the stored information about the analyzed workload data further comprises at least one of: compiled query subgraphs, optimized plans and estimated statistics, execution graphs and resources, and actual runtime statistics of the executed queries.

13. The method of claim 9, wherein the stored information about the analyzed workload data further comprises information about a physical design for at least one subgraph to be materialized.

14. The method of claim 9, wherein the stored information about the analyzed workload data further includes information about expiration of the materialized subgraph.

15. The method of claim 9, wherein the storage information about the analyzed workload data is based at least in part on user-selectable settings related to at least one of: storage cost, latency, CPU hours, and frequency.

16. A method according to claim 9, wherein the storage information about the analyzed workload data is based on analysis of runtime statistics of subgraphs of multiple jobs, and the runtime statistics include at least one of the following: latency, cardinality, data size, and resource consumption of the subgraphs.

17. A computer storage medium storing computer readable instructions that, when executed, cause a computing device to: determining overlapping subgraphs for the query using stored information about the analyzed workload data, the stored information including a normalized signature for a particular subgraph, the normalized signature identifying the particular subgraph across repeated instances of the data; providing information about the determined overlapping subgraphs of the query, the information about overlapping subgraphs comprising a normalized signature for each overlapping subgraph; Using the provided information, determining which of the overlapping sub-graphs is to be materialized; For each overlapping subgraph that is determined to be materialized: determining whether the particular subgraph has been materialized using a precise signature corresponding to the canonical signature of the particular subgraph, the precise signature identifying the particular subgraph corresponding to the canonical signature within a particular repetition instance of the data; When the specific subgraph has not been materialized, materializing the specific subgraph and using the materialized specific subgraph to respond to the query; as well as When the specific subgraph has been materialized, the query is responded to using the materialized specific subgraph.

18. The computer storage medium of claim 17, wherein the stored information about the analyzed workload data further comprises at least one of: compiled query subgraphs, optimized plans and estimated statistics, execution graphs and resources, and actual runtime statistics for the executed queries.

19. The computer storage medium of claim 17, wherein the stored information regarding the analyzed workload data further comprises information regarding a physical design for at least one subgraph to be materialized.

20. The computer storage medium of claim 17, wherein the stored information regarding the analyzed workload data further comprises information regarding expiration of the materialized subgraphs.

Citation Information

Patent Citations

  • Method and Apparatus for Optimizing Queries over Vertically Stored Database

    US20090132474A1