Processing method and device for cyclic dependence between tables and computer program product
By constructing and decomposing a directed graph, determining and sorting the dependencies between lookup tables, the problem of circular dependencies between lookup tables is solved, thus achieving accuracy and consistency in data processing.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- 中国邮政储蓄银行股份有限公司
- Filing Date
- 2025-12-31
- Publication Date
- 2026-04-21
AI Technical Summary
In existing technologies, circular dependencies between lookup tables are difficult to sort effectively, leading to problems with the accuracy and consistency of data queries. Manual sorting is prone to errors, and directed acyclic graphs cannot solve the problems in real-world development scenarios when querying historical data from the same table.
By obtaining the dependencies between lookup tables, a directed graph is constructed. It is then determined whether there are circular dependencies. If circular dependencies exist, the graph is sorted according to the hierarchy of the dependencies, and the directed cyclic graph is decomposed into a directed acyclic graph. After merging, the SQL statements are executed in hierarchical order.
It effectively avoids the sorting of circular dependency paths, ensures the accuracy and consistency of data processing, and solves the circular dependency problem that cannot be solved in existing technologies.
Smart Images
Figure CN121901289A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of database data processing technology, and more specifically, to a method, apparatus, computer-readable storage medium, and computer program product for processing circular dependencies between tables. Background Technology
[0002] In database applications and data processing projects, lookup tables are crucial for storing and managing data. However, complex dependencies often exist between multiple lookup tables. For example, the value of a field in one lookup table may depend on the primary key of another lookup table. A specific order in which lookup tables are processed is necessary to ensure data consistency and accuracy.
[0003] Currently, the main problems in sorting out dependencies between lookup tables and in sorting based on dependencies are as follows:
[0004] 1. Sort according to the order of manual input: If the input order is incorrect, it may affect the accuracy of the query data.
[0005] 2. Using directed acyclic graphs and topological sorting: Effective sorting can be performed when there are no queries on the same table for data. However, once the same table is used to query historical data, it becomes a cyclic graph, making sorting impossible and failing to fully solve the problems in real-world development scenarios. Summary of the Invention
[0006] The main objective of this application is to provide a method, apparatus, computer-readable storage medium, and computer program product for handling circular dependencies between tables, so as to at least solve the problem of circular dependencies that cannot be solved in the prior art.
[0007] To achieve the above objectives, according to one aspect of this application, a method for handling circular dependencies between tables is provided, comprising: obtaining dependencies between multiple lookup tables to obtain multiple inter-table dependencies, wherein the lookup tables include a temporary table and a target table, the temporary table being a table structure for temporarily storing intermediate results, and the target table being a table structure for storing the result data of data processing; determining whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies; and, in the case where there is a circular dependency between the multiple lookup tables, sorting the dependencies according to their hierarchy to obtain a dependency ranking result, wherein the hierarchy is the number of lookup tables in which the target table is a dependent path.
[0008] Optionally, determining whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies includes: constructing a directed graph of inter-table dependencies using the lookup tables as nodes and the multiple inter-table dependencies as directed edges, wherein the directed edges point from the dependent node to the dependent node; if the directed graph is a directed cyclic graph, determining that there is a circular dependency between the multiple lookup tables; if the directed graph is a directed acyclic graph, determining that there is no circular dependency between the multiple lookup tables.
[0009] Optionally, when there are circular dependencies between multiple lookup tables, the dependency ranking is obtained by sorting according to the dependency order, including: decomposing the dependencies of the directed graph to obtain a dependency graph where each lookup table is a dependent; removing all nodes in the dependency graph that are not dependents to obtain multiple new dependency graphs; merging the multiple new dependency graphs to obtain a target directed acyclic graph; sorting the dependencies in the target directed acyclic graph according to their hierarchy to obtain a dependency sequence; obtaining the dependencies where nodes are removed as dependents to obtain removed dependencies; and inserting all the removed dependencies into the dependency sequence and sorting them to obtain the dependency ranking result.
[0010] Optionally, the dependency sequence is obtained by sorting the dependencies according to the hierarchy of the target directed acyclic graph, including: determining the hierarchy of the dependency relationship where the target table is a dependent as the first level in the dependency relationship of the directed acyclic graph; determining the dependent table of the target table as the next level table in the dependency relationship of the directed acyclic graph, determining the hierarchy of the dependency relationship where the next level table is a dependent as the next level, until the dependency relationship of the last level is obtained; and sorting the dependencies according to the order from the last level to the first level to obtain the dependency sequence.
[0011] Optionally, all the removed dependencies are inserted into the dependency sequence and sorted to obtain a dependency sorting result, including: determining the level of the removed dependencies where the target table is a dependent as the first level; determining the dependent table of the target table as the next level table, determining the level of the removed dependencies where the next level table is a dependent as the next level, until the last level of removed dependencies is obtained; inserting the removed dependencies into the dependency sequence in the order from the last level to the first level to obtain the dependency sequence.
[0012] Optionally, the method further includes: when there is no circular dependency between the multiple lookup tables, in the dependency relationship of the directed graph, determining the level of the dependency relationship where the target table is the dependent as the first level; in the dependency relationship of the directed graph, determining the dependent table of the target table as the next level table, determining the level of the dependency relationship where the next level table is the dependent as the next level, until the dependency relationship of the last level is obtained; sorting the dependency relationships according to the order from the last level to the first level to obtain the dependency relationship sorting result.
[0013] Optionally, obtaining the dependencies between multiple lookup tables to obtain multiple table dependencies includes: obtaining the table names of multiple lookup tables and the table names of the corresponding dependent tables; and combining the table names of each lookup table and the table names of the dependent tables in a one-to-one correspondence to obtain multiple table dependencies.
[0014] According to another aspect of this application, a processing apparatus for inter-table circular dependencies is provided, comprising: an acquisition unit, configured to acquire dependencies between multiple lookup tables to obtain multiple inter-table dependencies, wherein the lookup tables include a temporary table and a target table, the temporary table being a table structure for temporarily storing intermediate results, and the target table being a table structure for storing the result data of data processing; a determination unit, configured to determine whether there is an inter-table circular dependency among the multiple lookup tables based on the multiple inter-table dependencies; and a sorting unit, configured to sort the multiple lookup tables according to the hierarchy of the dependencies to obtain a dependency ranking result, wherein the hierarchy is the number of lookup tables in which the target table is a dependency path.
[0015] According to another aspect of this application, a computer-readable storage medium is provided, the computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform any of the methods described.
[0016] According to another aspect of this application, a computer program product is provided, comprising a computer program that, when executed by a processor, implements any of the methods described.
[0017] By applying the technical solution of this application, in the above-mentioned method for handling circular dependencies between tables, it is determined whether there is a circular dependency between multiple lookup tables based on the multiple table dependencies. If there is a circular dependency between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result, which avoids sorting along the circular dependency path. The corresponding SQL statements are executed according to the order of the dependency relationship sorting result, thus solving the problem of circular dependencies that cannot be solved in the prior art. Attached Figure Description
[0018] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an undue limitation of this application. In the drawings:
[0019] Figure 1 A hardware structure block diagram of a mobile terminal for processing inter-table circular dependencies according to an embodiment of this application is shown.
[0020] Figure 2 A flowchart illustrating a method for handling circular dependencies between tables according to an embodiment of this application is shown.
[0021] Figure 3 A schematic diagram of a directed cyclic graph provided according to an embodiment of this application is shown;
[0022] Figure 4 A schematic diagram of the dependencies between lookup tables provided according to an embodiment of this application is shown;
[0023] Figure 5 A schematic diagram illustrating the dependencies between lookup tables after removing some lookup table nodes, according to an embodiment of this application, is shown.
[0024] Figure 6 A schematic diagram of a directed acyclic graph provided according to an embodiment of this application is shown;
[0025] Figure 7 A structural block diagram of a table-based circular dependency processing apparatus provided according to an embodiment of this application is shown.
[0026] The above figures include the following reference numerals:
[0027] 102. Processor; 104. Memory; 106. Transmission device; 108. Input / output device. Detailed Implementation
[0028] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.
[0029] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.
[0030] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this application described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.
[0031] As described in the background section, existing technologies cannot resolve circular dependencies. To address this issue, embodiments of this application provide a method, apparatus, computer-readable storage medium, and computer program product for handling inter-table circular dependencies.
[0032] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.
[0033] The methods and embodiments provided in this application can be executed on a mobile terminal, computer terminal, or similar computing device. Taking running on a mobile terminal as an example, Figure 1 This is a hardware structure block diagram of a mobile terminal for a method of handling circular dependencies between tables according to an embodiment of the present invention. Figure 1 As shown, a mobile terminal may include one or more ( Figure 1 Only one is shown in the diagram. A processor 102 (which may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.) and a memory 104 for storing data are also shown. The mobile terminal may further include a transmission device 106 for communication functions and an input / output device 108. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the mobile terminal described above. For example, the mobile terminal may also include components that are more... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.
[0034] The memory 104 can be used to store computer programs, such as application software programs and modules, like the computer program corresponding to the table circular dependency processing method in this embodiment of the invention. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, thereby implementing the above-described method. The memory 104 may include high-speed random access memory and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the mobile terminal via a network. Examples of the aforementioned networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof. The transmission device 106 is used to receive or send data via a network. Specific examples of the aforementioned networks may include wireless networks provided by the mobile terminal's communication provider. In one example, the transmission device 106 includes a network interface controller (NIC), which can be connected to other network devices via a base station to communicate with the Internet. In one example, the transmission device 106 may be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.
[0035] This embodiment provides a method for handling circular dependencies between tables that runs on a mobile terminal, computer terminal, or similar computing device. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Also, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.
[0036] Figure 2 This is a flowchart of a method for handling circular dependencies between tables according to an embodiment of this application. Figure 2 As shown, the method includes the following steps:
[0037] Step S201: Obtain the dependency relationship between multiple lookup tables. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing.
[0038] Specifically, the lookup table is functionally divided into source table, temporary table, and target table. Then, the dependencies between these tables are obtained. The source table is the starting point of the data processing flow, storing the raw data and serving as the data source for subsequent data extraction, transformation, and loading (ETL / ELT) operations. The temporary table is a short-lived table structure used to temporarily store intermediate results, primarily for simplifying complex queries, improving computational efficiency, or isolating temporary data. It is typically automatically destroyed after the session ends or after a specific period, and does not occupy storage resources for an extended period. The target table is the "endpoint" of the data processing flow, containing the final data after normalization, aggregation, and business-specific processing.
[0039] Step S202: Determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies mentioned above.
[0040] Specifically, the existence of circular dependencies between tables can be determined based on the inter-table dependencies. For example, if table A depends on table B, table B depends on table C, and table C depends on table A, then there is a circular dependency between tables.
[0041] Step S203: In the case of circular dependencies between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. The hierarchy is the number of lookup tables in the dependency path of the target table.
[0042] Specifically, if there is a circular dependency between multiple lookup tables, there will be at least one circular path forming a loop, making it impossible to sort the SQL statements corresponding to the dependency relationship. By sorting according to the hierarchy of the dependency relationship, the corresponding SQL statements can be executed in the order of the dependency relationship sorting results.
[0043] In this embodiment, the above-mentioned method for handling circular dependencies between tables determines whether there is a circular dependency between the multiple lookup tables based on the inter-table dependencies. If there is a circular dependency between the multiple lookup tables, the dependency is sorted according to the hierarchy of the dependency relationship to obtain the dependency sorting result. That is, the dependency relationship is sorted according to the order of the dependency path, which avoids sorting along the circular dependency path. The corresponding SQL statement is executed according to the order of the dependency sorting result, thus solving the problem of circular dependencies that cannot be solved in the prior art.
[0044] In an optional implementation, to query the dependencies between tables, step S201 above includes:
[0045] Step S2011: Obtain the table names of the above lookup tables and the table names of the corresponding dependent tables;
[0046] Step S2012: Combine the table names of the above lookup tables and the table names of the above dependent tables one by one to obtain multiple table dependencies.
[0047] In the above implementation, constructing dependencies requires obtaining the table names of the lookup tables and the table relationships that each lookup table depends on. For example, as shown in Table 1, g depends on a and f, e depends on d, f depends on b, d depends on h and c, and h depends on g and h. The contents of the mapping relationship Excel are parsed to obtain an initial set of multiple inter-table dependencies of [{g,a},{g,f},{e,d},{f,b},{d,h},{d,c},{h,g},{h,e}].
[0048] Table 1
[0049]
[0050] To provide a clearer understanding of circular dependencies between tables, in one optional implementation, step S202 includes:
[0051] Step S2021: Use the above lookup table as a node and the multiple dependencies between the above tables as directed edges to construct a directed graph of dependencies between lookup tables. The directed edges are from the dependent node to the dependent node.
[0052] Step S2022: In the case that the above directed graph is a directed cyclic graph, determine that there is a circular dependency between multiple lookup tables.
[0053] Step S2023: If the directed graph is a directed acyclic graph, determine that there is no circular dependency between the multiple lookup tables.
[0054] In the above implementation, each lookup table is treated as a node in a directed graph, and the node's identifier uses the table name as a unique identifier. In the data development scenario, since the target table is the final data processing result, it is the only node with an out-degree of 0, meaning it has no dependency relationship as a dependent. The source table, as the carrier of the original data, becomes a node with an in-degree of 0, also meaning it has no dependency relationship as a dependent. If lookup table A depends on lookup table B (i.e., lookup table B needs to be processed before lookup table A can be processed), a directed edge is added to the directed graph from node B to node A to represent this dependency relationship. By parsing the mapping relationships filled in the Excel file, the dependency relationships between the various lookup tables are constructed, and a directed graph representing the dependency relationships between lookup tables is built according to the above definitions of nodes and edges. The graph is constructed based on the association relationships in Table 1, as follows... Figure 3As shown, if the target table h serves as both the target table and the source table, a directed cyclic graph will be obtained, indicating a circular dependency between tables. In this case, further sorting is not possible. Conversely, if it were a directed acyclic graph, there would be no circular dependencies between tables, and the existence of such dependencies could be intuitively determined through analysis.
[0055] To achieve the sorting of dependencies with circular dependencies between tables, in one optional implementation, step S203 above includes:
[0056] Step S2031: Decompose the dependencies of the above directed graph to obtain a dependency graph in which each of the above lookup tables is a dependent.
[0057] Step S2032: Remove all nodes in the above dependency graph that are not dependent parties to obtain multiple new dependency graphs;
[0058] Step S2033: Merge multiple new dependency graphs to obtain the target directed acyclic graph;
[0059] Step S2034: Sort the dependencies according to the hierarchy of the target directed acyclic graph to obtain the dependency sequence;
[0060] Step S2035: Obtain the dependency relationship of the node to be removed as the dependent party, and obtain the dependency relationship to be removed;
[0061] Step S2036: Sort all the above-mentioned removed dependencies into the above dependency sequence to obtain the dependency sorting result.
[0062] In the above embodiments, the dependencies of the directed graph are decomposed to obtain a dependency graph in which each lookup table serves as a dependent party, such as... Figure 4 As shown, in the program, you can Figure 4 The data is stored in a hashmap format. For ease of subsequent processing, the table name column in Table 1 is used as the key, and a collection of objects in the form of {"table name", "dependent table"} is used as the value to obtain a map storing the lookup table dependencies: "g":[{g,a},{g,f}], "f":[{f,b}], "d":[{d,c},{d,h}], "e":[{e,d}], "h":[{h,g},{h,e}]. According to Table 1, the types of each table are: target table: h, source tables: a, b, c, h, temporary tables: g, f, e, d. Considering the business scenario, to ensure the creation order of each table, only nodes g, f, d, e, and h need to be sorted. That is, the source table, as the original data carrier, does not depend on other tables, so only the target table and the temporary table need to be sorted. Therefore, we can... Figure 4 Remove the source table nodes to obtain, as shown below. Figure 5As shown, operations are performed on the hashmap to remove objects containing the source table from the value, and a new collection is created to store the deleted objects. This results in the processed map: "g":[{g,f}], "e":[{e,d}], "h":[{h,g},{h,e}], and a collection storing objects containing dependencies of the source table: [{g,a},{f,b},{d,c},{d,h}]. Figure 5 It contains all the nodes used for sorting, Figure 5 By merging the subgraphs, a directed acyclic graph can be obtained, such as... Figure 6 As shown, for Figure 6 Performing a topological sort yields the sorted results for f, d, g, e, h, resulting in the dependency sequence: [{g,f},{e,d},{h,g},{h,e}]. Then, [{g,a},{f,b},{d,c},{d,h}] is inserted into [{g,f},{e,d},{h,g},{h,e}], resulting in [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0063] To achieve the ordering of dependencies, in one optional implementation, step S2034 above includes:
[0064] Step S20341: In the above-mentioned directed acyclic graph dependency relationship, the level of the dependency relationship where the target is the dependent party is determined as the first level.
[0065] Step S20342: In the above-mentioned directed acyclic graph dependency relationship, the dependent party of the target table is determined as the next level table, and the level of the dependency relationship where the next level table is the dependent party is determined as the next level, until the dependency relationship of the last level is obtained.
[0066] Step S20343: Sort the dependencies according to the order from the last layer to the first layer to obtain the dependency sequence.
[0067] In the above embodiments, with Figure 6 For example, the dependency relationship, such as Figure 6 As shown, {h,g},{h,e} are the dependencies of the first level, and {g,f},{e,d} are the dependencies of the second level. The dependencies are sorted in the order from the last level to the first level to obtain the dependency sequence, namely [{g,f},{e,d},{h,g},{h,e}].
[0068] To achieve the ordering of dependencies, in one optional implementation, step S2036 above includes:
[0069] Step S20361: Determine the level of the above-mentioned dependency removal relationship where the target table is a dependent as the first level;
[0070] Step S20362: Determine the dependent party of the target table as the next level table, and determine the level of the above-mentioned removal of dependency relationship where the next level table is the dependent party as the next level, until the last level of the above-mentioned removal of dependency relationship is obtained.
[0071] Step S20363: Insert the removed dependencies into the dependency sequence in the order from the last layer to the first layer to obtain the dependency sequence.
[0072] In the above implementation, in order to obtain the final sorting result, it is also necessary to insert [{g,a},{f,b},{d,c},{d,h}] into the dependency sequence [{g,f},{e,d},{h,g},{h,e}]. {h,g},{h,e} are the first-level dependencies, {g,f},{e,d} are the second-level dependencies, {g,a} is also a second-level dependency, and {f,b}{d,c},{d,h} are the third-level dependencies. By inserting the aforementioned dependency removal dependencies into the dependency sequence in the order from the last level to the first level, we can obtain [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0073] Of course, the insertion position can also be determined based on in-degree and association relationships. The {f,b} object f only depends on the source table b. Figure 6 We can see that after removing the source table b, the in-degree of node f is 0. Therefore, {f,b} is inserted into the head of the set [{f,b},{g,f},{e,d},{h,g},{h,e}]. Similarly, combining... Figure 6 The in-degree of node d is also 0. Since {d,h} and {d,c} are related, and considering Table 1, since h is the primary table associated with c, {d,c} is placed after {d,h} and inserted at the head of the set, resulting in [{d,h},{d,c},{f,b},{g,f},{e,d},{h,g},{h,e}]. Because {g,f} exists in the set, and table a is associated with table f, and considering Table 1, table a is the primary table associated with f, {g,a} is inserted before {g,f}. The final sorted result is: [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0074] To achieve the ordering of dependencies, in one optional implementation, the above method further includes:
[0075] Step S301: In the absence of circular dependencies between multiple lookup tables, the hierarchy of the dependency relationship where the target table is the dependent is determined as the first level in the dependency relationship of the directed graph.
[0076] Step S302: In the above-mentioned directed graph dependency relationship, the dependent party of the target table is determined as the next level table, and the level of the dependency relationship where the next level table is the dependent party is determined as the next level, until the dependency relationship of the last level is obtained.
[0077] Step S303: Sort the dependencies according to the order from the last layer to the first layer to obtain the dependency sorting result.
[0078] In the above implementation, there are no circular dependencies between the multiple lookup tables. Therefore, there is no need to remove nodes; the tables are directly sorted based on the directed graph. The dependencies are sorted according to the order from the last level to the first level to obtain the sorted dependency result. There is no impact from circular dependencies, and the sorting method is the same as... Figure 6 same.
[0079] This application also provides a device for processing inter-table circular dependencies. It should be noted that this device can be used to execute the processing method for inter-table circular dependencies provided in this application. This device is used to implement the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0080] The following describes the apparatus for handling circular dependencies between tables provided in the embodiments of this application.
[0081] Figure 7 This is a schematic diagram of a processing apparatus for inter-table circular dependencies according to an embodiment of this application. Figure 7 As shown, the device includes:
[0082] The acquisition unit 10 is used to acquire the dependency relationship between multiple lookup tables and obtain the dependency relationship between multiple tables. The lookup tables include temporary tables and target tables. The temporary tables are table structures that temporarily store intermediate results, and the target tables are table structures that store the result data of data processing.
[0083] Specifically, the lookup table is functionally divided into source table, temporary table, and target table. Then, the dependencies between these tables are obtained. The source table is the starting point of the data processing flow, storing the raw data and serving as the data source for subsequent data extraction, transformation, and loading (ETL / ELT) operations. The temporary table is a short-lived table structure used to temporarily store intermediate results, primarily for simplifying complex queries, improving computational efficiency, or isolating temporary data. It is typically automatically destroyed after the session ends or after a specific period, and does not occupy storage resources for an extended period. The target table is the "endpoint" of the data processing flow, containing the final data after normalization, aggregation, and business-specific processing.
[0084] The first determining unit 20 is used to determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies.
[0085] Specifically, the existence of circular dependencies between tables can be determined based on the inter-table dependencies. For example, if table A depends on table B, table B depends on table C, and table C depends on table A, then there is a circular dependency between tables.
[0086] The first sorting unit 30 is used to sort the lookup tables according to the hierarchy of the dependency relationship when there is a circular dependency between the multiple lookup tables, where the hierarchy is the number of lookup tables in which the target table is a dependency path.
[0087] Specifically, if there is a circular dependency between multiple lookup tables, there will be at least one circular path forming a loop, making it impossible to sort the SQL statements corresponding to the dependency relationship. By sorting according to the hierarchy of the dependency relationship, the corresponding SQL statements can be executed in the order of the dependency relationship sorting results.
[0088] In this embodiment, the above-mentioned inter-table circular dependency processing device determines whether there is an inter-table circular dependency among the multiple lookup tables based on the inter-table dependency relationships. If there is an inter-table circular dependency, the dependency relationships are sorted according to the hierarchy to obtain the dependency relationship sorting result, that is, the dependency relationships are sorted according to the order of the dependency path. This avoids sorting along the circular dependency path and executes the corresponding SQL statements according to the order of the dependency relationship sorting result, thus solving the problem of circular dependency that cannot be solved in the prior art.
[0089] In an optional implementation for querying inter-table dependencies, the aforementioned acquisition unit includes:
[0090] The first acquisition module is used to acquire the table names of the above lookup tables and the table names of the corresponding dependent tables;
[0091] The combination module is used to combine the table names of the above lookup tables and the table names of the above dependent tables one by one to obtain multiple inter-table dependencies.
[0092] In the above implementation, constructing dependencies requires obtaining the table names of the lookup tables and the table relationships that each lookup table depends on. For example, as shown in Table 1, g depends on a and f, e depends on d, f depends on b, d depends on h and c, and h depends on g and h. The contents of the mapping relationship Excel are parsed to obtain an initial set of multiple inter-table dependencies of [{g,a},{g,f},{e,d},{f,b},{d,h},{d,c},{h,g},{h,e}].
[0093] To provide a clearer understanding of circular dependencies between tables, in one optional implementation, the determining unit includes:
[0094] The construction module is used to construct a directed graph of the dependencies between the lookup tables as nodes and the dependencies between multiple lookup tables as directed edges. The directed edges are from the dependent node to the dependent node.
[0095] The first determining module is used to determine the inter-table circular dependency when the above directed graph is a directed cyclic graph.
[0096] The second determining module is used to determine, when the directed graph is a directed acyclic graph, that there is no circular dependency between the multiple lookup tables.
[0097] In the above implementation, each lookup table is treated as a node in a directed graph, and the node's identifier uses the table name as a unique identifier. In the data development scenario, since the target table is the final data processing result, it is the only node with an out-degree of 0, meaning it has no dependency relationship as a dependent. The source table, as the carrier of the original data, becomes a node with an in-degree of 0, also meaning it has no dependency relationship as a dependent. If lookup table A depends on lookup table B (i.e., lookup table B needs to be processed before lookup table A can be processed), a directed edge is added to the directed graph from node B to node A to represent this dependency relationship. By parsing the mapping relationships filled in the Excel file, the dependency relationships between the various lookup tables are constructed, and a directed graph representing the dependency relationships between lookup tables is built according to the above definitions of nodes and edges. The graph is constructed based on the association relationships in Table 1, as follows... Figure 3 As shown, if the target table h serves as both the target table and the source table, a directed cyclic graph will be obtained, indicating a circular dependency between tables. In this case, further sorting is not possible. Conversely, if it were a directed acyclic graph, there would be no circular dependencies between tables, and the existence of such dependencies could be intuitively determined through analysis.
[0098] To achieve sorting of dependencies with circular dependencies between tables, in one optional implementation, the sorting unit includes:
[0099] The decomposition module is used to decompose the dependencies of the above directed graph to obtain a dependency graph in which each of the above lookup tables is a dependent.
[0100] The removal module is used to remove all nodes in the above dependency graph that are not dependent on it, resulting in multiple new dependency graphs.
[0101] The merge module is used to merge multiple new dependency graphs to obtain the target directed acyclic graph;
[0102] The first sorting module is used to sort the dependencies according to the hierarchy of the aforementioned target directed acyclic graph to obtain a dependency sequence.
[0103] The second acquisition module is used to acquire the dependency relationship of the node to be removed as the dependent party, and obtain the dependency relationship to be removed;
[0104] The second sorting module is used to sort all the above-mentioned removed dependencies inserted into the above-mentioned dependency sequence to obtain the dependency sorting result.
[0105] In the above embodiments, the dependencies of the directed graph are decomposed to obtain a dependency graph in which each lookup table serves as a dependent party, such as... Figure 4 As shown, in the program, you can Figure 4 The data is stored in a hashmap format. For ease of subsequent processing, the table name column in Table 1 is used as the key, and a collection of objects in the form of {"table name", "dependent table"} is used as the value to obtain a map storing the lookup table dependencies: "g":[{g,a},{g,f}], "f":[{f,b}], "d":[{d,c},{d,h}], "e":[{e,d}], "h":[{h,g},{h,e}]. According to Table 1, the types of each table are: target table: h, source tables: a, b, c, h, temporary tables: g, f, e, d. Considering the business scenario, to ensure the creation order of each table, only nodes g, f, d, e, and h need to be sorted. That is, the source table, as the original data carrier, does not depend on other tables, so only the target table and the temporary table need to be sorted. Therefore, we can... Figure 4 Remove the source table nodes to obtain, as shown below. Figure 5As shown, operations are performed on the hashmap to remove objects containing the source table from the value, and a new collection is created to store the deleted objects. This results in the processed map: "g":[{g,f}], "e":[{e,d}], "h":[{h,g},{h,e}], and a collection storing objects containing dependencies of the source table: [{g,a},{f,b},{d,c},{d,h}]. Figure 5 It contains all the nodes used for sorting, Figure 5 By merging the subgraphs, a directed acyclic graph can be obtained, such as... Figure 6 As shown, for Figure 6 Performing a topological sort yields the sorted results for f, d, g, e, h, resulting in the dependency sequence: [{g,f},{e,d},{h,g},{h,e}]. Then, [{g,a},{f,b},{d,c},{d,h}] is inserted into [{g,f},{e,d},{h,g},{h,e}], resulting in [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0106] To achieve the ordering of dependencies, in one optional implementation, the first ordering module includes:
[0107] The first determining submodule is used to determine the level of the dependency relationship where the target is a dependent party in the above directed acyclic graph as the first level.
[0108] The second determining submodule is used to determine the dependent party of the target table as the next level table in the above-mentioned directed acyclic graph dependency relationship, and to determine the level of the dependency relationship where the next level table is the dependent party as the next level, until the dependency relationship of the last level is obtained.
[0109] The first sorting submodule is used to sort the above dependencies in the order from the last layer to the first layer, so as to obtain the above dependency sequence.
[0110] In the above embodiments, with Figure 6 For example, the dependency relationship, such as Figure 6 As shown, {h,g},{h,e} are the dependencies of the first level, and {g,f},{e,d} are the dependencies of the second level. The dependencies are sorted in the order from the last level to the first level to obtain the dependency sequence, namely [{g,f},{e,d},{h,g},{h,e}].
[0111] To achieve the ordering of dependencies, in one optional implementation, the second ordering module includes:
[0112] The third determining submodule is used to determine the level of the above-mentioned dependency removal relationship where the target is a dependent as the first level;
[0113] The fourth determination submodule is used to determine the dependent party of the target table as the next level table, and to determine the level of the above-mentioned removal of dependency relationship where the next level table is the dependent party as the next level, until the above-mentioned removal of dependency relationship of the last level is obtained.
[0114] The second sorting submodule is used to insert the aforementioned removed dependencies into the aforementioned dependency sequence in the order from the last layer to the first layer, thereby obtaining the aforementioned dependency sequence.
[0115] In the above implementation, in order to obtain the final sorting result, it is also necessary to insert [{g,a},{f,b},{d,c},{d,h}] into the dependency sequence [{g,f},{e,d},{h,g},{h,e}]. {h,g},{h,e} are the first-level dependencies, {g,f},{e,d} are the second-level dependencies, {g,a} is also a second-level dependency, and {f,b}{d,c},{d,h} are the third-level dependencies. By inserting the aforementioned dependency removal dependencies into the dependency sequence in the order from the last level to the first level, we can obtain [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0116] Of course, the insertion position can also be determined based on in-degree and association relationships. The {f,b} object f only depends on the source table b. Figure 6 We can see that after removing the source table b, the in-degree of node f is 0. Therefore, {f,b} is inserted into the head of the set [{f,b},{g,f},{e,d},{h,g},{h,e}]. Similarly, combining... Figure 6 The in-degree of node d is also 0. Since {d,h} and {d,c} are related, and considering Table 1, since h is the primary table associated with c, {d,c} is placed after {d,h} and inserted at the head of the set, resulting in [{d,h},{d,c},{f,b},{g,f},{e,d},{h,g},{h,e}]. Because {g,f} exists in the set, and table a is associated with table f, and considering Table 1, table a is the primary table associated with f, {g,a} is inserted before {g,f}. The final sorted result is: [{d,h},{d,c},{f,b},{g,a},{g,f},{e,d},{h,g},{h,e}].
[0117] To achieve the ordering of dependencies, in one optional implementation, the above-mentioned apparatus further includes:
[0118] The second determining unit is used to determine the level of the dependency relationship where the target table is the dependent party as the first level in the above directed graph dependency relationship when there is no circular dependency between multiple lookup tables.
[0119] The third determining unit is used to determine the dependent party of the target table as the next level table in the above directed graph dependency relationship, and to determine the level of the dependency relationship where the next level table is the dependent party as the next level, until the dependency relationship of the last level is obtained.
[0120] The second sorting unit is used to sort the dependencies in the order from the last layer to the first layer to obtain the dependency sorting result.
[0121] In the above implementation, there are no circular dependencies between the multiple lookup tables. Therefore, there is no need to remove nodes; the tables are directly sorted based on the directed graph. The dependencies are sorted according to the order from the last level to the first level to obtain the sorted dependency result. There is no impact from circular dependencies, and the sorting method is the same as... Figure 6 same.
[0122] The aforementioned processing device for inter-table circular dependencies includes a processor and a memory. The aforementioned acquisition unit, first determination unit, and first sorting unit are all stored as program units in the memory, and the processor executes the aforementioned program units stored in the memory to implement the corresponding functions. All of the aforementioned modules are located in the same processor; or, the aforementioned modules are located in different processors in any combination.
[0123] The processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and by adjusting kernel parameters, circular dependencies that are currently unsolvable in existing technologies can be addressed.
[0124] The memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.
[0125] This invention provides a computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device where the computer-readable storage medium is located to perform the method for processing inter-table circular dependencies.
[0126] Specifically, methods for handling circular dependencies between tables include:
[0127] Step S201: Obtain the dependency relationship between multiple lookup tables. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing.
[0128] Step S202: Determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies mentioned above.
[0129] Step S203: In the case of circular dependencies between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. The hierarchy is the number of lookup tables in the dependency path of the target table.
[0130] This invention provides a processor for running a program, wherein the program executes the method for handling circular dependencies between tables.
[0131] Specifically, methods for handling circular dependencies between tables include:
[0132] Step S201: Obtain the dependency relationship between multiple lookup tables. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing.
[0133] Step S202: Determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies mentioned above.
[0134] Step S203: In the case of circular dependencies between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. The hierarchy is the number of lookup tables in the dependency path of the target table.
[0135] This invention provides a device including a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, it performs at least the following steps:
[0136] Step S201: Obtain the dependency relationship between multiple lookup tables. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing.
[0137] Step S202: Determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies mentioned above.
[0138] Step S203: In the case of circular dependencies between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. The hierarchy is the number of lookup tables in the dependency path of the target table.
[0139] This application also provides a computer program product, which, when executed on a data processing device, is suitable for executing an initialization program having at least the following method steps:
[0140] Step S201: Obtain the dependency relationship between multiple lookup tables. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing.
[0141] Step S202: Determine whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies mentioned above.
[0142] Step S203: In the case of circular dependencies between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. The hierarchy is the number of lookup tables in the dependency path of the target table.
[0143] It is obvious to those skilled in the art that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. They can be implemented using computer-executable program code, and thus can be stored in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those described herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.
[0144] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0145] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0146] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0147] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0148] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0149] Memory may include non-persistent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, like read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.
[0150] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.
[0151] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0152] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.
[0153] As can be seen from the above description, the embodiments of this application achieve the following technical effects:
[0154] 1) In the method for handling circular dependencies between tables in this application, it is determined whether there is a circular dependency between multiple lookup tables based on the inter-table dependency relationships. If there is a circular dependency between multiple lookup tables, the dependency relationship is sorted according to the hierarchy to obtain the dependency relationship sorting result. That is, the dependency relationship is sorted according to the order of the dependency path, which avoids sorting along the circular dependency path. The corresponding SQL statement is executed according to the order of the dependency relationship sorting result, which solves the problem of circular dependency that cannot be solved in the prior art.
[0155] 2) In the table circular dependency processing device of this application, the existence of table circular dependency among multiple lookup tables is determined based on the table dependency relationship. If there is table circular dependency among multiple lookup tables, the dependency relationship is sorted according to the hierarchy of the dependency relationship to obtain the dependency relationship sorting result. That is, the dependency relationship is sorted according to the order of the dependency path, which avoids sorting along the circular dependency path. The corresponding SQL statement is executed according to the order of the dependency relationship sorting result, which solves the problem of circular dependency that cannot be solved in the prior art.
[0156] The above description is merely a preferred embodiment of this application and is not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.
Claims
1. A method for handling circular dependencies between tables, characterized in that, include: Obtain the dependencies between multiple lookup tables to obtain the inter-table dependencies. The lookup tables include a source table, a temporary table, and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of the data processing. Determine whether there is a circular dependency between the multiple lookup tables based on the multiple table dependencies; In the case of circular dependencies between multiple lookup tables, the dependency ranking is obtained by sorting according to the hierarchy of the dependency relationship, where the hierarchy is the number of lookup tables in the dependency path of the target table.
2. The method according to claim 1, characterized in that, Determining whether there is a circular dependency between the multiple lookup tables based on the multiple inter-table dependencies includes: The lookup table is used as a node, and the multiple inter-table dependencies are used as directed edges to construct a directed graph of inter-table dependencies. The directed edges are from the dependent node to the dependent node. In the case that the directed graph is a directed cyclic graph, it is determined that there is a circular dependency between the multiple lookup tables; In the case that the directed graph is a directed acyclic graph, it is determined that there is no circular dependency between the multiple lookup tables.
3. The method according to claim 2, characterized in that, In the case of circular dependencies between multiple lookup tables, the dependency ranking results are obtained by sorting according to the order of dependencies, including: The dependencies of the directed graph are decomposed to obtain a dependency graph in which each lookup table is a dependent. Remove all nodes in the aforementioned dependency graph that are not dependent parties to obtain multiple new dependency graphs; Merge multiple new dependency graphs to obtain the target directed acyclic graph; The dependencies are sorted according to their hierarchy in the target directed acyclic graph to obtain a dependency sequence. Obtain the dependency relationship of the node to be removed as the dependent node, and obtain the dependency relationship to be removed; All the removed dependencies are inserted into the dependency sequence and sorted to obtain the dependency sorting result.
4. The method according to claim 3, characterized in that, The dependencies are sorted hierarchically according to the target directed acyclic graph to obtain a dependency sequence, including: In the dependency relationship of the directed acyclic graph, the hierarchy of the dependency relationship where the target is represented as the dependent party is determined as the first level; In the dependency relationship of the directed acyclic graph, the dependent table of the target table is determined as the next level table, and the hierarchy of the dependency relationship where the next level table is the dependent table is determined as the next level, until the dependency relationship of the last level is obtained. The dependencies are sorted in order from the last layer to the first layer to obtain the dependency sequence.
5. The method according to claim 3, characterized in that, All the removed dependencies are inserted into the dependency sequence and sorted to obtain the dependency sorting result, including: The level at which the target table is a dependent is determined as the first level; The dependent table of the target table is determined as the next level table, and the level of the dependency removal relationship where the next level table is the dependent table is determined as the next level, until the dependency removal relationship of the last level is obtained; The dependency removal relationships are inserted into the dependency sequence in the order from the last layer to the first layer to obtain the dependency sequence.
6. The method according to claim 2, characterized in that, The method further includes: In the absence of circular dependencies between multiple lookup tables, the hierarchy of the dependency relationship where the target table is the dependent is determined as the first level in the dependency relationship of the directed graph. In the dependency relationship of the directed graph, the dependent table of the target table is determined as the next level table, and the level of the dependency relationship where the next level table is the dependent table is determined as the next level, until the dependency relationship of the last level is obtained. The dependencies are sorted in order from the last layer to the first layer to obtain the dependency sorting result.
7. The method according to any one of claims 1 to 6, characterized in that, Retrieve the dependencies between multiple lookup tables, obtaining the dependencies between multiple tables, including: Obtain the table names of the multiple lookup tables and the table names of the corresponding dependent tables; The table names of each lookup table and the table names of the dependent tables are combined in a one-to-one correspondence to obtain multiple table dependencies.
8. A processing apparatus for inter-table circular dependencies, characterized in that, include: The acquisition unit is used to acquire the dependency relationship between multiple lookup tables, and to obtain the dependency relationship between multiple tables. The lookup table includes a source table, a temporary table and a target table. The source table is a table structure that stores the original data to be processed. The temporary table is a table structure that temporarily stores intermediate results. The target table is a table structure that stores the result data of data processing. The first determining unit is configured to determine whether there is a circular dependency between the multiple lookup tables based on the multiple table dependencies. The first sorting unit is used to sort the lookup tables according to the hierarchy of the dependency relationship when there is a circular dependency between the multiple lookup tables, wherein the hierarchy is the number of lookup tables in which the target table is a dependency path.
9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform the method according to any one of claims 1 to 7.
10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the method described in any one of claims 1 to 7.