A database upper-layer application analysis optimization method

Abstract

The application provides a database upper-layer application analysis optimization method and relates to the technical field of data analysis. The database upper-layer application analysis optimization method specifically comprises the following steps: S1. forming a topological sequence according to a workflow; S2. marking nodes; and S3. performing materialized calculation on a node to be optimized. Through the database upper-layer application analysis optimization method, the problem that Pandas lacks analysis classification for functions when driving a database to execute an SQL statement operation according to an application function, and the problem that when the number of application functions is large, the interaction between Pandas and the database is frequent and the communication resources are excessively occupied are solved. Meanwhile, the occupation of communication resources is reduced, and the use of computing, storage and other resources is also reduced based on the replacement optimization processing of multiple nodes, so that the database upper-layer application analysis efficiency and performance are improved.

Description

A Database Upper-Layer Application Analysis and Optimization Method Technical Field

[0001] This invention relates to the field of data analysis technology, specifically to a method for optimizing upper-level database applications. Background Technology

[0002] A database is a "repository for organizing, storing, and managing data according to data structures." It is a large, organized, shareable, and centrally managed collection of data stored permanently in a computer. The data explosion of the new era has led to a surge in demand for data analysis. Traditional mainstream data analysis tools like Pandas, as applications built on top of databases, provide users with application functions based on SQL statements. This allows users to drive the database to output the required data by calling these functions, without needing to consider the SQL statement's computational process.

[0003] However, the existing Pandas lacks analysis and classification of functions when executing SQL statements based on applied functions. When there are many applied functions, it leads to frequent interactions between Pandas and the database, consuming too much communication resources. Summary of the Invention

[0004] (a) Technical problems to be solved

[0005] To address the shortcomings of existing technologies, this invention provides a database upper-layer application analysis and optimization method, which solves the problem of insufficient communication resources caused by each node needing to access a function once during execution.

[0006] (II) Technical Solution

[0007] To achieve the above objectives, the present invention provides the following technical solution: a database upper-layer application analysis and optimization method, specifically comprising the following steps:

[0008] S1. Generate a topology sequence based on the workflow:

[0009] The server processing end determines the number of nodes in the workflow and the corresponding API functions of each node based on the workflow created by the user based on the upper-layer application end, and forms a topology sequence. Based on the topology sequence and the input-output relationship between each node in the topology sequence, it determines the dependent objects, function operation types and operation values ​​of each node.

[0010] S2. Mark the nodes:

[0011] Based on the dependent objects of each node, the materialized state and process stage of each node in the topology sequence are marked;

[0012] S3. Perform materialization calculations on the nodes to be optimized:

[0013] The server processor receives the data processing flow execution instructions from the application, identifies nodes according to the topological sequence order and performs reverse operations based on the topological sequence, and performs materialization processing on each node according to the materialization state of each node in the marked topological sequence and the mark of the process stage, generates an operation set, sends the operation set to the database, completes the materialization calculation of the last node marked as to be optimized in the process stage, and returns the calculation result.

[0014] Preferably, step S1 specifically comprises:

[0015] Users create workflows based on upper-layer applications, wherein the workflow includes multiple nodes, and each node encapsulates the API functions;

[0016] The server processing end detects the workflow. When it detects that the workflow created by the user includes data processing API functions, it performs a depth-first traversal of each node in the workflow to obtain the number of nodes in the workflow and the API functions corresponding to each node, forming the topology sequence, and identifying the input-output relationship between each node in the topology sequence.

[0017] The server processing end determines and records the dependent objects, function operation types, and operation values ​​of each node based on the topology sequence and the input-output relationships between each node in the topology sequence.

[0018] Preferably, the materialization state in step S2 includes: unmaterialized and materialized, and the process stage includes: no optimization required and to be optimized.

[0019] Preferably, the materialization state in step S2 includes: unmaterialized and materialized, and the process stage includes: no optimization required and to be optimized.

[0020] Preferably, step S2 further includes:

[0021] The server processing end analyzes the branching situation of each node in the topology sequence based on the dependent objects of each node, and marks the process stage of each node, specifically:

[0022] If the node has branch nodes, and the node has two or more dependent objects, and both dependent objects are branch terminal nodes, then the process stages between the node and the branch points before the node are marked as to be optimized.

[0023] If the node does not have any branch nodes, mark the process stages between the node and the branch points preceding the node as not requiring optimization.

[0024] Preferably, step S3 specifically comprises:

[0025] The server processing end identifies the materialized state of each node in the marked topology sequence and the marker of the process stage according to the topology sequence order.

[0026] If the process stage of a node is marked as not requiring optimization, then the function operation type corresponding to the node is sent to the database, the node is materialized, and the materialization status of the node is marked as materialized.

[0027] If the process stage of a node is marked as needing optimization, then

[0028] Starting from the node, traverse and search according to the topological sequence until the last node marked as needing optimization in the process stage is found;

[0029] Based on all nodes marked as needing optimization in the process stage, the final node is materialized to generate the operation set of the final node.

[0030] The operation set of the last node is sent to the database to complete the materialization calculation of the last node and return the calculation result.

[0031] Preferably, the step of traversing and searching according to the topological sequence order starting from the node until the last node marked as to be optimized in the process stage includes:

[0032] The server processor searches for the next node based on the nodes marked as needing optimization in the aforementioned process stage and the topology sequence order of the topology sequence.

[0033] If the process stage of the next node is marked as needing optimization, the search continues until the last node marked as needing optimization is found.

[0034] If the process stage of the next node is marked as not requiring optimization, then the node preceding the next node is the last node.

[0035] Preferably, the step of materializing the last node based on all nodes marked as to be optimized in the process stage to generate the operation set of the last node specifically includes:

[0036] The server processing end performs a reverse materialization operation on the last node based on the topological sequence order of the topological sequence, and replaces the dependency object of the last node with the dependency object and operation type of the previous node of the last node.

[0037] After replacement, the operation types of the last node, together with the operation types and dependent objects of the previous node, form the operation set of the last node.

[0038] Preferably, the set of operations for the last node:

[0039] If the materialized state of the dependent objects in the operation set of the last node is materialized, then the operation set of the last node is sent to the database.

[0040] If the materialization state of the dependent object in the operation set of the last node is unmaterialized, then the dependent object and operation type of the previous node of the dependent object will continue to be used to replace the dependent object of the dependent object.

[0041] Repeat the above process until there are no unmaterialized dependency objects in the operation set of the last node, and then send the operation set of the last node to the database.

[0042] Preferably, in step S3, after completing the materialization calculation of the last node marked as to be optimized in the process stage, the calculation result is returned to the upper-layer application, and the materialization status of the last node and all its dependent objects is marked as materialized.

[0043] (III) Beneficial Effects

[0044] This invention provides a method for analyzing and optimizing upper-layer database applications, which has the following beneficial effects:

[0045] This invention addresses the problems of Pandas lacking function analysis and classification, and excessive communication resource consumption due to frequent interactions between Pandas and the database when a large number of application functions are used, by generating a topology sequence, analyzing the workflow branching based on the dependencies of each node, and optimizing each node according to the optimization marks during topology sequence generation. During execution, starting from the output node, the nodes to be optimized are replaced with operation objects (operation objects are replaced with operation types (operation values)) based on these optimization marks, generating an operation set containing multiple nodes to be optimized, and sending it to the database for execution (materialization). This solves the problems of Pandas lacking analysis and classification of functions when executing SQL statements based on application functions, and the problems of frequent interactions between Pandas and the database when a large number of application functions are used. At the same time, it reduces the consumption of communication resources, and the replacement optimization based on multiple nodes also reduces the use of computing and storage resources, thereby improving the efficiency and performance of upper-layer database application analysis. Attached Figure Description

[0046] Figure 1 shows the data processing flow built by the user based on the visualization platform according to the present invention;

[0047] Figure 2 is a diagram showing the traversal order of the present invention, which identifies the input-output relationship between nodes.

[0048] Figure 3 is a schematic diagram of how nodes are marked as nodes in the optimization stage according to the present invention;

[0049] Figure 4 is a schematic diagram of the materialization operation of the present invention. Detailed Implementation

[0050] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0051] As shown in Figures 1-4, this embodiment of the invention provides a method for analyzing and optimizing upper-layer database applications, specifically including the following steps:

[0052] S1. Generate a topology sequence based on the workflow:

[0053] The server processing end determines the number of nodes in the workflow and the corresponding API functions of each node based on the workflow created by the user based on the upper-layer application end, and forms a topology sequence. Based on the topology sequence and the input-output relationship between each node in the topology sequence, it determines the dependent objects, function operation types and operation values ​​of each node.

[0054] S2. Mark the nodes:

[0055] Based on the dependent objects of each node, the materialization state and process stage of each node in the topology sequence are marked. The materialization state includes: unmaterialized and materialized, and the process stage includes: no optimization required and to be optimized.

[0056] S3. Perform materialization calculations on the nodes to be optimized:

[0057] The server processor receives data processing flow execution instructions from the application, identifies nodes according to the topological sequence order and performs reverse operations based on the topological sequence, and performs materialization processing on each node according to the materialization state of each node in the marked topological sequence and the mark of the process stage, generates an operation set, sends the operation set to the database, completes the materialization calculation of the last node marked as to be optimized in the process stage, and returns the calculation result. Specifically, it identifies nodes according to the topological sequence order and performs reverse operations based on the topological sequence.

[0058] Step S1 is as follows:

[0059] Users create workflows based on the upper-layer application, where the workflow includes multiple nodes, and each node encapsulates API functions;

[0060] The server-side processing detects the workflow. When it detects that the user-created workflow includes data processing API functions, it performs a depth-first traversal of each node in the workflow to obtain the number of nodes and the corresponding API functions of each node, forming a topology sequence, and identifying the input-output relationships between nodes in the topology sequence.

[0061] The server processor determines and records the dependent objects, function operation types, and operation values ​​of each node based on the topology sequence and the input-output relationships between nodes in the topology sequence.

[0062] The materialization states in step S2 include: unmaterialized and materialized, and the process stages include: no optimization required and to be optimized.

[0063] Step S2 includes:

[0064] Based on the dependencies of each node, the server processor marks all materialized states of each node in the topology sequence as unmaterialized.

[0065] Step S2 also includes:

[0066] The server processing end analyzes the branching situation of each node in the topology sequence based on the dependent objects of each node, and marks the process stage of each node, specifically:

[0067] If a node has branch nodes, and the node has two or more dependent objects, and both dependent objects are the end nodes of the branches, then the process stages between the node and the branch points before the node are marked as to be optimized.

[0068] If a node does not have any branch nodes, mark the process stages between the node and the branch points before the node as not requiring optimization.

[0069] Step S3 is as follows:

[0070] The server processing end identifies the materialized state and process stage markers of each node in the marked topology sequence according to the topology sequence order.

[0071] If the process stage of a node is marked as not requiring optimization, the corresponding function operation type of the node is sent to the database, the node is materialized, and the materialization status of the node is marked as materialized.

[0072] If the process stage of a node is marked as needing optimization, then

[0073] Starting from the nodes, traverse and search according to the topological sequence until the last node marked as needing optimization in the process stage is found;

[0074] Based on all nodes marked as needing optimization in the process phase, materialize the last node to generate the operation set for the last node.

[0075] Send the set of operations for the last node to the database, complete the materialization calculation of the last node, and return the calculation result.

[0076] The steps involved in traversing the topological sequence from the nodes until the final node marked as needing optimization in the process phase include:

[0077] The server-side processor searches for the next node based on the nodes marked as needing optimization at each stage of the process and the topological sequence order of the topology sequence.

[0078] If the process stage of the next node is marked as needing optimization, the search continues until the last node marked as needing optimization is found.

[0079] If the process stage of the next node is marked as not requiring optimization, then the node preceding the next node is the last node.

[0080] The specific steps for materializing the final node based on all nodes marked as needing optimization in the process phase, and generating the operation set for the final node, are as follows:

[0081] The server-side processing performs reverse materialization operations on the last node based on the topological sequence order of the topological sequence, replacing the last node's dependency object with the dependency object and operation type of the previous node.

[0082] After replacement, the operation types of the last node, together with the operation types of the previous node and the dependent objects, form the operation set of the last node.

[0083] The set of operations for the last node:

[0084] If the materialized state of the dependent objects in the operation set of the last node is materialized, then the operation set of the last node is sent to the database.

[0085] If the materialized state of the dependent object in the operation set of the last node is unmaterialized, then the dependent object and operation type of the previous node of the dependent object will continue to be used to replace the dependent object of the dependent object.

[0086] Repeat the above process until there are no unmaterialized dependency objects in the operation set of the last node, then send the operation set of the last node to the database.

[0087] In step S3, after completing the materialization calculation of the last node marked as to be optimized in the process phase, the calculation result is returned to the upper-layer application, and the materialization status of the last node and all its dependent objects is marked as materialized.

[0088] After materializing the previous node, the next node is searched based on the topological sequence. If the next node is in the optimization stage, the last node in the optimization stage is found. For this last node, the dependent objects and operation types (operation values) of the previous node are used to replace the dependent objects of this node. After replacement, the operation types (operation values) of the last node, together with the operation types (operation values) and dependent objects of the previous node, form the operation set of the last node. For nodes appearing in the operation set, if the node is an unmaterialized node, the dependent objects and operation types (operation values) of the previous node are used to replace the dependent objects of this node. This process is repeated until there are no unmaterialized nodes in the operation set. When there are no unmaterialized nodes in the operation set, the operation set is sent to the database, completing the materialization calculation of the last node in the optimization stage.

[0089] Figure 1 shows the data processing flow built by the user based on the visualization platform. The application uses depth-first traversal to form the traversal order shown in Figure 2, thereby identifying the input-output relationships between nodes. Then, it records the dependent objects and operation types (operation values) of each node.

[0090] Node T1 is the first node, with no dependent objects. Its operation type is read operation, and the operation value is the corresponding identifier of the data table. T1 = read(data table);

[0091] The dependency of node T2 is T1, the operation type is to retrieve the first row, and the operation value is the corresponding identifier of the data table. T2 = head(data table);

[0092] The dependency of node T3 is T2, the operation type is to extract a column, the operation value is column c1, and T3 = T2[c1].

[0093] The dependency of node T4 is T3, the operation type is multiplication, the operation value is 10, and T4 = T3 * 10.

[0094] The dependency of node T5 is T2, the operation type is to extract a column, the operation value is column c2, and T5 = T2[c2];

[0095] The dependency of node T6 is T5, the operation type is division, the operation value is 3, and T6 = T5 / 3;

[0096] The dependencies of node T7 are T4 and T6. The operation type is division, and the operation values ​​are T4 and T6. T7 = T4 / T6.

[0097] By comparing the dependencies of each node with those of other nodes, it is identified that the dependency of nodes T3 and T5 is T2, thus confirming that there is a branch in the workflow; T4 is a node of the T3 branch, and T6 is a node of the T5 branch; and it is found that T7 has two dependencies, which come from two different branches. Therefore, nodes T7, T4, T3, T6, and T5 are marked as nodes to be optimized. Figure 3 is obtained.

[0098] When an execution command is detected, the read data table operation of node T1 is sent to the database to complete the materialization of T1; the retrieve data table first row operation of node T2 is sent to the database to complete the materialization operation of T2.

[0099] When node T3 is identified as a node in the optimization stage, the final node T7 in the optimization stage is found, and a reverse iteration operation is performed based on the depth-first search (DFS) sequence. T4, which T7 depends on, is replaced with the dependent object and operation type (operation value) of T4. The other dependent node T6 is replaced with the dependent object and operation type (operation value) of T6. Therefore, T7 = T4 / T6 is replaced with T7 = {(T3*10) / (T5 / 3)}, resulting in the operation set {(T3*10) / (T5 / 3)} for T7. T3 and T5 in the current set have not yet been materialized, so they are further replaced, resulting in T7 = {(T2[c1]*10) / (T2[c2] / 3)}. T2 has already been materialized. The operation set {(T2[c1]*10) / (T2[c2] / 3)} is sent to the database for the materialization of T7.

[0100] This solution utilizes a server-side processing mechanism that can replace Pandas. This server-side processing mechanism provides data processing API functions that encapsulate SQL statements. Unlike the Pandas application, this server-side processing mechanism records the dependent objects of the node containing the data processing API functions, the types of function operations (operation values; some operation types require operation based on operation values), and the materialization state of the node containing the function (initially, the materialization state of the node containing the function is unmaterialized). Based on the aforementioned three parameters, it performs data analysis differently from Pandas (a materialized view is a database object that includes a query result; it is a local copy of remote data or used to generate a summary table based on the sum of data tables. Materialized views store data based on remote tables and can also be called snapshots. Materialized views can be based on table queries, views, and other materialized views. Typically, in a replication environment, a materialized view is called the master table, and in a data warehouse, it is called a detail table).

[0101] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for analyzing and optimizing upper-layer database applications, characterized in that, Specifically, the following steps are included: S1. Forming a topology sequence based on the workflow: The server processing end determines the number of nodes in the workflow and the corresponding API functions of each node based on the workflow created by the user based on the upper-layer application end, and forms a topology sequence. Based on the topology sequence and the input-output relationship between each node in the topology sequence, the server processing end determines the dependent objects, function operation types and operation values ​​of each node. S2. Mark the nodes: Mark the materialized state and process stage of each node in the topology sequence according to the dependent objects of each node; S3. Perform materialization calculation on the node to be optimized: The server processor receives the data processing flow execution instruction from the application, identifies nodes according to the topology sequence order and performs reverse operations based on the topology sequence, and performs materialization processing on each node according to the materialization state of each node in the marked topology sequence and the marking of the process stage, generates an operation set, and sends the operation set to the database, completes the materialization calculation of the last node marked as to be optimized in the process stage, and returns the calculation result; wherein, step S2 further includes: the server processor analyzes the branching situation of each node in the topology sequence according to the dependency objects of each node, and marks the process stage of each node, specifically: if the node has a branch node, and the node has two or more dependency objects, and both dependency objects are branch end nodes, the process stage between the node and the branch points before the node is marked as to be optimized; if the node If no branch nodes exist, the process stages between the node and the branch points preceding it are marked as not requiring optimization. Step S3 specifically involves: the server processing end identifying the materialized state and process stage markings of each node in the marked topology sequence according to the topology sequence order. If the process stage marking of a node is not requiring optimization, the function operation type corresponding to the node is sent to the database, the node is materialized, and the materialized state of the node is marked as materialized. If the process stage marking of a node is to be optimized, a traversal search is performed starting from the node according to the topology sequence order until the last node marked as to be optimized is found. Based on all nodes marked as to be optimized, the last node is materialized to generate the operation set of the last node. The operation set of the last node is sent to the database to complete the materialization calculation of the last node and return the calculation result.

2. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, Step S1 specifically involves: the user creating a workflow based on the upper-layer application, wherein the workflow includes multiple nodes, and each node encapsulates the API function; the server processing end detects the workflow, and when it detects that the user-created workflow includes data processing API functions, it performs a depth-first traversal of each node in the workflow to obtain the number of nodes in the workflow and the API function corresponding to each node, forming the topology sequence, and identifying the input-output relationship between each node in the topology sequence; the server processing end determines and records the dependent object, the function operation type, and the operation value of each node based on the topology sequence and the input-output relationship between each node in the topology sequence.

3. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, The materialization state in step S2 includes: unmaterialized and materialized, and the process stage includes: no optimization required and to be optimized.

4. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, Step S2 includes: the server processing end marks all materialized states of each node in the topology sequence as unmaterialized according to the dependent objects of each node.

5. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, The step of traversing and searching according to the topological sequence order starting from the node until the last node marked as needing optimization in the process stage is found includes: the server processing end searches for the next node based on the nodes marked as needing optimization in the process stage and the topological sequence order of the topological sequence; if the process stage of the next node is marked as needing optimization, the search continues until the last node marked as needing optimization in the process stage is found; if the process stage of the next node is marked as not needing optimization, then the node preceding the next node is the last node.

6. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, The step of materializing the last node based on all nodes marked as to be optimized in the process stage and generating the operation set of the last node is specifically as follows: the server processing end performs a reverse materialization operation on the last node based on the topological sequence order of the topological sequence, and replaces the dependency object of the last node with the dependency object and operation type of the previous node of the last node. After replacement, the operation types of the last node, together with the operation types and dependent objects of the previous node, form the operation set of the last node.

7. The database upper-layer application analysis and optimization method according to claim 6, characterized in that, For the operation set of the last node: if the materialization state of the dependent object in the operation set of the last node is materialized, then the operation set of the last node is sent to the database; if the materialization state of the dependent object in the operation set of the last node is not materialized, then the dependent object and operation type of the previous node of the dependent object are used to replace the dependent object of the dependent object; repeat the above process until there are no unmaterialized dependent objects in the operation set of the last node, then the operation set of the last node is sent to the database.

8. The database upper-layer application analysis and optimization method according to claim 1, characterized in that, In step S3, after completing the materialization calculation of the last node marked as to be optimized in the process stage, the calculation result is returned to the upper-layer application, and the materialization status of the last node and all its dependent objects is marked as materialized.

Images