Remote connection system and method in a distributed environment

Abstract

A remote join system and method in a distributed environment are disclosed. The system includes a plurality of database servers including a first database server for generating an execution plan for a remote join when a user inputs a user query, a plurality of second database servers for executing the remote join according to the execution plan of the first database server, a data collection unit for collecting data from the plurality of database servers, and a data distribution unit for generating a temporary table for the remote join and storing data collected by the data collection unit in the generated temporary table, wherein the data collection unit transmits a cluster query related to connection execution of the temporary table to the plurality of database servers, the plurality of database servers individually execute connection according to the cluster query, and then transmits connection execution results to the data collection unit. Thus, the remote join can be performed even in a non-remote join condition.

Description

Technical Field

[0001] This invention relates to a remote connection system and method in a distributed environment, and more specifically, to a remote connection system and method in a distributed environment capable of performing remote connection processing under non-remote connection conditions. Background Technology

[0002] With societal development, data is experiencing explosive growth. This necessitates more efficient and flexible data services, information processing, and data management capabilities.

[0003] Database Management Systems (DBMS) emerged to meet these requirements. A collection of collected data is called a database (DB), and the system that manages these databases is called a DBMS. A DBMS allows an organization to integrate the data it needs into a database for storage and management, and it can solve the problems caused by data duplication that exist in existing file systems.

[0004] The method used to search for data in a DBMS is the join. A join is a method of searching for data by combining two or more tables, typically using the common values ​​of two or more rows.

[0005] In existing join methods, when not using remote join conditions, all data from the tables requiring the join is imported locally before the join is executed. For example, even when the join conditions consist of a shard key, the data corresponding to the join conditions for each table is distributed to different nodes depending on the sharding strategy. Therefore, in the existing method, all data from the tables requiring the join is imported locally for the join.

[0006] If all the data from the tables required for the join is imported locally and then joined, the indexes generated on the tables cannot be used, and there are issues with the network cost of importing the data required for the join from all nodes locally, as well as the increased resource usage for processing the imported data locally.

[0007] [Existing Technical Documents]

[0008] [Patent Documents]

[0009] Korean Patent Document 0001 Publication No. 10-2021-0053831 (Published on May 12, 2021) Summary of the Invention

[0010] Technical challenges to be addressed

[0011] To address the aforementioned problems, the technical objective of this invention is to provide a remote connection system and method in a distributed environment, so that even when the connection conditions are not remote connection conditions in a distributed environment, the connection result can still be imported to the local machine after a remote connection is executed.

[0012] The problems solved by the present invention are not limited to those mentioned above. Those skilled in the art can clearly understand other problems not mentioned through the following description.

[0013] Technical solution

[0014] To address the aforementioned problems, an embodiment of the present invention provides a remote connection system in a distributed environment, comprising: multiple database servers, including a first database server for generating an execution plan for remote connection when a user inputs a query; multiple second database servers for executing remote connection according to the execution plan of the first database server; a data collection unit for collecting data from the multiple database servers; and a data allocation unit for generating a temporary table for remote connection and storing the data collected by the data collection unit in the generated temporary table; wherein the data collection unit sends a cluster query related to the connection execution of the temporary table to the multiple database servers, the multiple database servers execute the connection according to the cluster query, and then send the connection execution result to the data collection unit.

[0015] Preferably, the first database server and the second database server can allocate and store data into tables that apply the first allocation strategy and tables that apply the second allocation strategy, respectively.

[0016] Preferably, the data collection unit can send cluster queries to multiple second database servers respectively for collecting data from tables that have applied the first allocation strategy, receive execution results of the execution plan from the first database server, and receive execution results of the cluster queries from the second database servers.

[0017] Preferably, the data allocation unit can generate temporary tables on multiple database servers respectively, and allocate the collected data to the temporary tables of the multiple database servers according to the allocation strategy.

[0018] Preferably, the data allocation unit can allocate the collected data to a temporary table according to the second allocation strategy.

[0019] Additionally, according to other embodiments of the present invention, a remote connection method in a distributed environment is provided, wherein the distributed environment includes multiple database servers. The method includes the following steps: one of the multiple database servers receives a user query from a user; the database server that received the user query generates an execution plan for remote connection; collects data from the multiple database servers; generates a temporary table for remote connection; stores the collected data in the generated temporary table; sends cluster queries related to the connection execution of the temporary table to the multiple database servers; the multiple database servers execute the connection according to the cluster queries; and receives the results of the connection execution from the multiple database servers.

[0020] Preferably, the first database server and the second database server can allocate and store data into tables that apply the first allocation strategy and tables that apply the second allocation strategy, respectively.

[0021] Preferably, the data collection step may include: sending a cluster query to each of the multiple database servers that have not received user queries for collecting data from tables to which the first allocation strategy applies; receiving the execution results of the execution plan from the database servers that have received user queries; and receiving the execution results of the cluster query from each of the database servers that have not received user queries.

[0022] Preferably, in the step of generating temporary tables, temporary tables can be generated separately on multiple database servers.

[0023] Preferably, in the step of storing data in a temporary table, the collected data can be allocated to the temporary table according to a second allocation strategy.

[0024] Beneficial effects

[0025] According to the present invention, by using execution nodes for generating temporary tables, connections can be executed on each node even in the absence of remote connection conditions, thereby providing a remote connection system and method in a distributed environment capable of distributed execution of connections.

[0026] Furthermore, in the tables involved in the join, indexes can be used in all tables except temporary tables, and resource usage is also distributed due to distributed execution. In particular, the number of joins performed in a distributed manner is greatly reduced, thus lowering network costs.

[0027] The effects of the present invention are not limited to those described above. Those skilled in the art will clearly understand other effects not mentioned from the following description. Attached Figure Description

[0028] The accompanying drawings are provided to describe the invention in more detail to those skilled in the art, but the technical concept of the invention is not limited thereto.

[0029] Figure 1 This is a network structure diagram of a remote connection system in a distributed environment provided by a preferred embodiment of the present invention.

[0030] Figure 2 It is shown Figure 1 The diagram shows the structure of multiple database servers.

[0031] Figures 3a to 3d This is a diagram illustrating the operation of a connection system in a distributed environment provided according to a preferred embodiment of the present invention.

[0032] Figure 4 This is a flowchart of an operation method for a remote connection system in a distributed environment provided by a preferred embodiment of the present invention.

[0033] [Explanation of Labels in the Attached Image]

[0034] 100: Data Collection Unit

[0035] 110: Data Distribution Unit

[0036] 200: Multiple database servers

[0037] 210: First Database Server

[0038] 220, 230: Second database server Detailed Implementation

[0039] The above-described objects, other objects, features, and advantages of the invention will be readily understood through the following preferred embodiments related to the accompanying drawings. However, the invention is not limited to the embodiments described herein and may be embodied in other forms. Rather, the embodiments described herein are intended to make the disclosure clear and complete, and to fully convey the spirit of the invention to those skilled in the art.

[0040] In this specification, when it is mentioned that a component is located on top of another component, it means that it can be directly mounted on the other components, or that a third component can be inserted between them. Additionally, the thickness of the components in the accompanying drawings is exaggerated to effectively depict the technical content.

[0041] In this specification, when terms such as "first" and "second" are used to describe constituent elements, such terms are not intended to limit those constituent elements. These terms are merely used to distinguish one constituent element from other constituent elements. The embodiments described and illustrated herein also include complementary embodiments.

[0042] Furthermore, when it is mentioned that the first element (or constituent element) operates or performs on (ON) the second element (or constituent element), it should be understood that the first element (or constituent element) operates or performs in the environment in which the second element (or constituent element) operates or performs, or operates or performs by interacting directly or indirectly with the second element (or constituent element).

[0043] If a component, element, device, or system is mentioned as containing a component consisting of a program or software, then even if not explicitly mentioned, it should be understood that the component, element, device, or system contains hardware (such as memory, CPU, etc.) or other programs or software (such as drivers required to drive an operating system or hardware) necessary for the program or software to run or function.

[0044] In addition, unless otherwise specified in the implementation of a component (or element), it should be understood that the component (or element) can be implemented in software, hardware, or any form of software and hardware.

[0045] Furthermore, the terminology used in this specification is for illustrative purposes and does not constitute a limitation of the invention. In this specification, singular forms include plural forms unless explicitly stated otherwise. The terms "comprises" and / or "comprising" as used in this specification mean that the mention of a constituent element does not exclude the presence or addition of one or more other constituent elements.

[0046] Figure 1 This is a network structure diagram of a remote connection system in a distributed environment according to a preferred embodiment of the present invention. Figure 2 It is shown Figure 1 The diagram shows the structure of multiple database servers.

[0047] like Figure 1 As shown, the remote connection system in a distributed environment provided according to a preferred embodiment of the present invention includes multiple database servers 200, a data collection unit 100, and a data distribution unit 110. The remote connection system in a distributed environment may include other configurations, but only the configuration required for remote connection is illustrated here.

[0048] Multiple databases can be bundled into a cluster, and these clusters can form their own database servers. Here, a cluster is analogous to an object with storage space, similar to a table or index, but a higher-level concept than a table. Furthermore, tables are generated within objects created by a cluster, and data cannot be input into a cluster that does not contain tables.

[0049] Reference Figure 2View multiple database servers 200. In this embodiment, the structure of multiple database servers 200 in a "3 by 1" cluster is illustrated, and the databases of each database server are referred to as G1, G2, and G3.

[0050] This is based on the following scenario: each database server 210, 220, and 230 performs hash sharding, the sharding key is "sk", and the number of hash shards to be created (hash shardcount) is defined as 3.

[0051] Multiple database servers 210, 220, and 230 each generate a table named "H1". If the input data is "101, 102, 103, 201, 202, 203, 301, 302, 303", then, as shown in the figure, the data is allocated and stored in the "H1" table on each of the database servers 210, 220, and 230. Each database server 210, 220, and 230 can operate as a node. At this point, data can be allocated and stored in the tables according to a preset allocation strategy.

[0052] One of the multiple database servers 200 can receive user queries. Hereinafter, the server receiving user queries will be referred to as the first database server, and the remaining database servers will be referred to as the second database servers. In this embodiment, the multiple database servers 200 are distinguished as the first database server and the second database server simply to differentiate between database servers that receive user query input and those that do not; they can all perform the same operation. That is, any one of the multiple database servers 200 can receive user query input.

[0053] Data collection unit 100 is a node that executes queries and collects data from local or remote locations within the cluster. To do this, data collection unit 100 can collect the relevant data by sending cluster queries to the nodes constituting the collection targets.

[0054] That is, the data collection unit 100 collects data from multiple database servers 200. More specifically, the data collection unit 100 sends a cluster query for collecting data of tables that have applied the first allocation strategy to remote ends of the multiple database servers 200 that have not received user queries, and receives the execution results of the execution plan from the nodes that have received user queries, as well as the execution results of the cluster query from the remote ends.

[0055] Data allocation unit 110, like data collection unit 100, is a node that executes queries. As shown in the figure, data allocation unit 110 can operate as a concept subordinate to data collection unit 100. However, it should be understood that data allocation unit 110 does not necessarily operate as a concept subordinate to data collection unit 100. For example, data allocation unit 110 and data collection unit 100 can also operate as equivalent concepts.

[0056] The function of the data allocation unit 110 is to generate a temporary table for remote connection on the corresponding node and allocate data on the temporary table so that the data collection unit 100 can perform efficient queries with quality. When allocating data to the temporary table, the data allocation unit 110 can allocate data according to a second allocation strategy.

[0057] In this embodiment, the example of multiple database servers 200 using a first allocation strategy and a second allocation strategy is used. However, the same approach can be applied when using a single allocation policy.

[0058] The operation of data collection unit 100 is a concept used in existing local connection methods, while the operation of data allocation unit 110 is a concept not used in existing local connection methods. The operation of these data collection units 100 and data allocation units 110 will be explained later. Figures 3a to 3d Please provide a detailed explanation.

[0059] Figures 3a to 3d This is a diagram illustrating the operation of a connection system in a distributed environment provided according to a preferred embodiment of the present invention.

[0060] In this embodiment, as described in the previous embodiments, the operation of the connection system of the distributed environment of the present invention is described in the structure of multiple database servers 200 in a "3 by 1" cluster.

[0061] For ease of explanation, the database server including G1 will be referred to as the first database server 210, and the database servers including G2 and G3 will be referred to as the second database servers 220 and 230, respectively. That is, the first database server 210 is equivalent to the database server that receives user query A.

[0062] First database server 210 and second database servers 220 and 230 respectively generate tables named "HS3" and "HS6". Tables HS3 and HS6 are tables with different data allocation strategies, which specify how data is allocated and stored to G1, G2, and G3. Table HS3 specifies the number of hash shards to be divided as 3, while table HS6 specifies the number of hash shards to be divided as 6. Additionally, each table contains columns named "SK" and "NK" respectively.

[0063] Taking the data storage status in the table as an example, according to different allocation strategies, the data of "303" is stored in G1 according to the HS3 strategy and in G2 according to the HS6 strategy.

[0064] In the first database server 210, when user query A is input, the input user query A is executed. In order to execute user query A, the first database server 210 generates an execution plan B for executing user query A. Figure 3a The execution plan B is shown.

[0065] As shown in the figure, user query A is input into the first database server 210, and the first database server 210 generates an execution plan B for user query A. Blocks in execution plan B represent execution nodes. Execution nodes correspond to the execution units of the execution plan that process data by executing the query. In execution plan B, execution nodes begin execution from the lower position on the graph.

[0066] Because each table has a different allocation strategy, the data used to compare the condition "HS3.sk=HS6.sk" is configured on different nodes. For example, the 303 data for HS3.sk is configured on G1, while the 303 data for HS6.sk is configured on G2. Therefore, to compare the condition "HS3.sk=HS6.sk", an execution plan B can be generated that sends the data for HS3.sk to each node according to the allocation strategy of HS6, thereby executing a remote connection.

[0067] Figure 3b It shows Figure 3a The operation corresponding to the "PLAN BASED CLUSTER" execution node in execution plan B.

[0068] Data collection unit 100 sends a cluster query C to the second database servers 220 and 230 for importing (or synchronizing) HS3 table data locally. More specifically, data collection unit 100 sends the cluster query C, which imports HS3 table data locally according to a cluster-based execution plan, to the second database servers 220 and 230, thereby collecting data. This is marked with a solid arrow.

[0069] Subsequently, the data collection unit 100 receives the execution results of the execution plan used for the user query from the first database server 210, and the execution results of the cluster query C from the second database servers 220 and 230. This is marked with a dashed arrow.

[0070] Figure 3c It shows Figure 3a The operation corresponding to the "Data Allocation Unit Operation" execution node in execution plan B.

[0071] Data allocation unit 110 sends a cluster query D to each of the multiple database servers 200. This cluster query D is used to generate a temporary table, which is used to store the data of the tables involved in the connection to the node performing the remote connection. This is marked with a solid arrow. Thus, a temporary table named "SESSION_SCHEMA._$NI_5" is generated in each of the multiple database servers 200.

[0072] After temporary tables are generated on multiple database servers 200, the data allocation unit 110 loads the data previously imported locally into the temporary tables of the corresponding nodes according to the HS6 allocation strategy. This is marked with a dashed arrow. Once the process of loading data into the temporary tables is complete, each node has completed the data construction required for establishing a connection.

[0073] Figure 3d It shows Figure 3a The operation corresponding to the "Single Clubter" execution node in execution plan B.

[0074] The data collection unit 100 sends cluster queries E related to the execution of temporary table connections to the nodes performing remote connections, i.e., multiple database servers 200. This is marked with a solid arrow.

[0075] Upon receiving the connection request for the temporary table, multiple database servers 200 execute the connection between the temporary table "SESSION_SCHEMA._$NI_5" and the HS6 table, respectively. At this time, each of the multiple database servers 200 executes the connection according to the same execution plan.

[0076] Subsequently, the data collection unit 100 receives the execution results of cluster query E from multiple database servers 200. This is marked with a dashed arrow.

[0077] Figure 4 This is a flowchart of an operation method for a remote connection system in a distributed environment provided by a preferred embodiment of the present invention.

[0078] One of the multiple database servers 200 receives a user query from the user (S310). The first database server 210 of the multiple database servers 200 can receive user queries.

[0079] The first database server 210 generates an execution plan for remote connection based on the user query input by the user (S320). The execution plan for remote connection and... Figure 3a The content described in the text is the same.

[0080] Data collection unit 100 collects data from multiple database servers 200 (S330). More specifically, data collection unit 100 sends a cluster query to second database servers 220 and 230 that have not received user queries. This cluster query is used to collect data from tables that have been assigned to the first allocation strategy.

[0081] The data allocation unit 110 generates a temporary table for remote connection (S340). This generates temporary tables on multiple database servers 200, namely, the first database server 210 and the second database servers 220 and 230.

[0082] After generating a temporary table, the data allocation unit 110 allocates and stores the data collected by the data collection unit 100 according to a predetermined allocation strategy into the temporary table (S350).

[0083] After data storage is completed in the temporary table, the data collection unit 100 sends connections to the temporary table to multiple database servers 200 to execute related cluster queries (S360).

[0084] After receiving cluster queries related to temporary table connection execution from the data collection unit 100, multiple database servers 200 execute the connection (S370) respectively and send the connection execution results to the data collection unit 100 (S380).

[0085] This operation enables connections to be executed on multiple database servers 200, i.e., on each node. Specifically, the data allocation unit 110 generates a temporary table, and each node executes connections through this temporary table, thus allowing for distributed connection execution and achieving the effect of parallel connection processing.

[0086] Those skilled in the art will understand that this invention can be implemented in other specific forms without altering its technical concept or essential features. Therefore, it must be understood that the embodiments described above are exemplary in all respects and not limiting. The scope of this invention is defined by the claims, not by the detailed description above, and the meaning and scope of the claims, as well as all modifications or variations derived from their equivalents, should be interpreted as being included within the scope of this invention.

Claims

1. A remote connection system in a distributed environment, comprising: Multiple database servers, including: a first database server for generating an execution plan for remote connections when a user enters a user query, to determine the participating nodes and execution paths of the remote connection; Multiple second database servers are used to execute remote connections according to the execution plan of the first database server; A data collection unit for collecting data from the plurality of database servers according to the execution plan; as well as A data allocation unit is used to generate a temporary table for the remote connection according to the execution plan, and to store the data collected by the data collection unit in the generated temporary table; Specifically, the data collection unit sends cluster queries related to the connection execution of the temporary table to the multiple database servers. The multiple database servers each execute a connection according to the cluster query, and then send the connection execution results to the data collection unit.

2. The remote connection system in a distributed environment as described in claim 1, characterized in that, The first database server and the second database server allocate and store data into tables that apply the first allocation strategy and tables that apply the second allocation strategy, respectively.

3. The remote connection system in a distributed environment as described in claim 2, characterized in that, The data collection unit sends cluster queries to the plurality of second database servers respectively for collecting data from tables that are subject to the first allocation strategy, receives execution results based on the execution plan from the first database server, and receives execution results of the cluster queries from the second database servers.

4. The remote connection system in a distributed environment as described in claim 2, characterized in that, The data allocation unit generates temporary tables on the multiple database servers respectively, and allocates the collected data to the temporary tables on the multiple database servers according to the second allocation strategy.

5. The remote connection system in a distributed environment as described in claim 4, characterized in that, The data allocation unit allocates the collected data to the temporary table according to the second allocation strategy.

6. A remote connection method in a distributed environment, wherein the distributed environment includes multiple database servers, the method comprising the following steps: User queries are received by the first database server; The execution plan for remote connection is generated by the first database server; The data collection unit collects data from multiple second database servers according to the execution plan; The data allocation unit generates temporary tables on multiple second database servers according to the execution plan; The data distribution unit stores the collected data in the generated temporary table; The data collection unit sends connections to the temporary table to multiple second database servers to execute related cluster queries. The multiple second database servers each execute the connection based on the cluster query; as well as The data collection unit receives the results of the connection execution from the plurality of second database servers.

7. The remote connection method in a distributed environment as described in claim 6, characterized in that, The first database server and the second database server allocate and store data into tables that apply the first allocation strategy and tables that apply the second allocation strategy, respectively.

8. The remote connection method in a distributed environment as described in claim 7, characterized in that, The steps for collecting data include: Send a cluster query to each of the plurality of database servers that has not received the user query, for collecting data from tables to which the first allocation strategy applies; Receive the execution results of the execution plan from the database server that received the user query; and Each database server that has never received the user query receives the execution results of the cluster query.

9. The remote connection method in a distributed environment as described in claim 7, characterized in that, In the step of generating the temporary table, the temporary table is generated on each of the multiple database servers.

10. The remote connection method in a distributed environment as described in claim 9, characterized in that, In the step of storing the data in the temporary table, the collected data is allocated to the temporary table according to the second allocation strategy.

Images