Join table query methods, devices, electronic equipment, distributed systems, and storage media

Abstract

This invention provides a method, apparatus, electronic device, distributed system, and storage medium for join table queries. The distributed system includes a cluster of compute nodes with separate storage and compute operations, and a cluster of shared storage nodes. It can receive original join table query tasks initiated by clients, rewrite these tasks into distributed join table query tasks, distribute the tasks to each compute node, and enable each compute node to execute the corresponding distributed join table query task based on the sharded table data stored on each shared storage node. It retrieves the original join table query data obtained from executing the corresponding distributed join table query tasks from each compute node, summarizes the original join table query data to obtain the target join table query data, and then feeds back the target join table query data to the client. This invention employs a separate storage and compute architecture, giving the system strong scalability; the use of shared storage eliminates the need for data exchange and redistribution, saving network overhead and improving query performance.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of distributed storage technology, and in particular to a join table query method, apparatus, electronic device, distributed system and storage medium. Background Technology

[0002] Big data typically employs distributed storage, where data from a table is distributed across various nodes. A join query is used to combine data from two or more tables, matching records based on join conditions to obtain the desired result set. In distributed systems, because data from a table is distributed across different nodes, data transfer and synchronization are necessary between these nodes during a join query to ensure the accuracy of the final result.

[0003] In the process of implementing this invention, the inventors discovered that when the tables involved in the join query are all relatively large, each table will be divided into multiple data partitions. These data partitions will exchange data between worker nodes. Since the tables are relatively large, the amount of data exchange will be relatively large, resulting in high network overhead and low query performance. Summary of the Invention

[0004] This invention provides a method, apparatus, electronic device, distributed system, and storage medium for join table queries, which is suitable for join queries of large tables, saves network overhead, and improves query performance.

[0005] In a first aspect, the join table query method provided in this embodiment of the invention is applied to a distributed system, which includes a compute node cluster with separate storage and a shared storage node cluster, and the method includes:

[0006] Receive the original table join query task initiated by the client, and rewrite the original table join query task into a distributed table join query task;

[0007] The distributed join query task is issued to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join query task based on the shard table data stored in each shared storage node in the shared storage node cluster.

[0008] The original data of the join table query obtained from executing the corresponding distributed join table query task is obtained from each computing node, and the original data of the join table query is summarized to obtain the target data of the join table query.

[0009] The target data for the join query is fed back to the client.

[0010] Secondly, the join table query device provided in this embodiment of the invention is applied to a distributed system, the distributed system including a computing node cluster with separate storage and a shared storage node cluster, the device comprising:

[0011] The task rewriting module is used to receive the original table join query task initiated by the client and rewrite the original table join query task into a distributed table join query task.

[0012] The task distribution module is used to distribute the distributed join table query task to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join table query task based on the shard table data stored in each shared storage node in the shared storage node cluster.

[0013] The data aggregation module is used to obtain the original data of the join table query obtained by executing the corresponding distributed join table query task from each computing node, and to aggregate the original data of the join table query to obtain the target data of the join table query.

[0014] The feedback module is used to provide feedback to the client regarding the target data of the join query.

[0015] Thirdly, the electronic device provided in the embodiments of the present invention includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the join table lookup method as described in any embodiment of the present invention.

[0016] Fourthly, the distributed system provided in the embodiments of the present invention includes a computing node cluster with separate storage and computing and a shared storage node cluster, wherein any computing node in the computing node cluster is used to execute the join table query method as described in any embodiment of the present invention.

[0017] Fifthly, the computer-readable storage medium provided in the embodiments of the present invention stores a computer program thereon, which, when executed by a processor, implements the join table lookup method as described in any embodiment of the present invention.

[0018] The solution in this invention applies to a distributed system including a compute node cluster with separate storage and compute operations and a shared storage node cluster. After receiving the original join query task initiated by the client, the original join query task can be rewritten into a distributed join query task. Distributed join query tasks are then distributed to each compute node in the compute node cluster, enabling each compute node to execute the corresponding distributed join query task based on the sharded table data stored on each shared storage node in the shared storage node cluster. The original join query data obtained from executing the corresponding distributed join query task is retrieved from each compute node, and the original join query data is summarized to obtain the join query target data. The join query target data is then fed back to the client. In other words, this invention separates compute nodes and storage nodes, giving the system strong scalability. Furthermore, since the storage nodes use shared storage, each compute node can see the data stored on all storage nodes. Therefore, during join queries, there is no need for data exchange between compute nodes, and the queried data does not need to be redistributed, making it suitable for joint queries of large tables, saving network overhead, and improving query performance. Attached Figure Description

[0019] To more clearly illustrate the technical solution of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 This is a schematic diagram illustrating the effect of the currently used join table query method;

[0021] Figure 2 This is a flowchart illustrating a join table query method provided in an embodiment of the present invention. Figure 1 ;

[0022] Figure 3 This is an example diagram of the architecture of a distributed system provided in an embodiment of the present invention;

[0023] Figure 4 This is a flowchart illustrating a join table query method provided in an embodiment of the present invention. Figure 2 ;

[0024] Figure 5 This is a schematic diagram illustrating the effect of a join table query method provided in an embodiment of the present invention. Figure 1 ;

[0025] Figure 6 This is a schematic diagram illustrating the effect of a join table query method provided in an embodiment of the present invention. Figure 2 ;

[0026] Figure 7This is a schematic diagram of a linked table query device provided in an embodiment of the present invention;

[0027] Figure 8 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

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

[0029] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention 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 so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a 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.

[0030] Currently, the most commonly used method for table joins is the Shuffle Join method. Shuffle Join is a table join operation that requires data partitioning, then distributing the data across different worker nodes for merging. In Shuffle Join, each table is divided into multiple data partitions, and these partitions exchange data between worker nodes to complete the join operation, thus requiring a significant amount of data transfer and network exchange.

[0031] like Figure 1 As shown, Figure 1 This is a schematic diagram illustrating the effect of the currently used join method (i.e., Shuffle Join). Figure 1Currently, Shuffle Join methods are generally applied in in-memory compute architectures, where compute nodes and storage nodes are tightly coupled, with each node responsible for querying a fixed amount of data. For example, compute node 1 receives a distributed join query task that performs a join query on tables 1 and 2 with the join key A=B. In this case, compute node 1 is responsible for querying and aggregating the data from tables 1 (A=10) and 2 (B=10), while compute node 2 is responsible for querying and aggregating the data from tables 1 (A=11) and 2 (B=11). Then, compute node 1 summarizes the results and returns them to the client. However, the data for table 2 (B=10) is stored locally on compute node 2, and the data for table 2 (B=11) is stored locally on compute node 1. Therefore, data exchange is required. Compute node 1 needs to send the data for table 2 (B=11) from its local storage to compute node 2, and compute node 2 needs to send the data for table 2 (B=10) from its local storage to compute node 1. The larger the data volume and the more join tables there are, the higher the data exchange cost and the lower the query efficiency.

[0032] The following describes the join table query method provided by the embodiments of the present invention, such as... Figure 2 As shown, Figure 2 This is a flowchart illustrating a join table query method provided in an embodiment of the present invention. Figure 1 The join table query method provided in this embodiment of the invention can be executed by the join table query device provided in this embodiment of the invention, which can be implemented in software and / or hardware. In a specific embodiment, the device can be integrated into an electronic device, such as a computer or server.

[0033] To adapt to the join table query method provided in the embodiments of the present invention, the embodiments of the present invention also provide a distributed system, that is, the join table query method of the embodiments of the present invention can be applied to the distributed system provided in the embodiments of the present invention, such as... Figure 3 As shown, a distributed system can include compute node clusters with separate storage and compute capabilities and shared storage node clusters. A compute node cluster can consist of multiple compute nodes. Figure 3 As shown, a compute node cluster consists of n compute nodes, where n is an integer greater than 2. These compute nodes can have identical or different hardware and software configurations. In a distributed system, these compute nodes are primarily used to perform computational processing tasks. A shared storage node cluster can consist of multiple shared storage nodes. These shared storage nodes can also have identical or different hardware and software configurations. In a distributed system, these shared storage nodes are primarily used to perform storage processing tasks. Additionally, as... Figure 3 As shown, a distributed system may also include a coordination service node. The coordination service node is mainly used to perform distributed coordination services in the distributed system. The distributed coordination services provided include, but are not limited to, cluster state management services and data synchronization services.

[0034] It should be noted that, Figure 3 The distributed system architecture shown is for illustrative purposes only and does not constitute a limitation on actual application scenarios, deployment methods, etc. In practical applications, it can be adapted according to specific application scenarios and actual needs. Figure 3 The distributed system architecture shown is adaptively adjusted.

[0035] The following is combined with Figure 3 The distributed system architecture shown illustrates the join table query method provided in this embodiment of the invention. Continue reading... Figure 2 This embodiment will be described in detail from the following steps.

[0036] S101. Receive the original task of joining table query initiated by the client, and rewrite the original task of joining table query into a distributed joining table query task.

[0037] The original task of a join query is a task that performs a joint query on data from at least two tables. In this embodiment, after any computing node in the distributed system receives the original task of a join query initiated by the client, it can rewrite the received original task of a join query to obtain a distributed join query task. There can be multiple distributed join query tasks.

[0038] Specifically, the original task of a join query includes an original query statement, which can be a Structured Query Language (SQL) statement. The original query statement includes keywords that identify the query method; in this embodiment, the keyword can be a join query. Each compute node can treat shared storage as local storage. Taking the current compute node as an example, assuming the current node is the receiving node of the current original join query task, that is, the current compute node is the primary or coordinating node of the original join query task (other compute nodes in the compute node cluster are secondary or auxiliary nodes of the original join query task), after receiving the original join query task, the current compute node can modify the keywords in the original query statement included in the original join query task. For example, it can change "join query" to "local join query," that is, change Join to Local Join. After modification, the task is split to obtain a distributed join query task. Each compute node executes the rewritten and split distributed join query task, enabling each compute node to perform data queries by scanning the full amount of data in shared storage.

[0039] For example, consider three tables: Table 1 contains class ID, student name, and student grades; Table 2 contains teacher name, teacher's years of experience, teacher's gender, and class ID; and Table 3 contains class ID and class ranking. All three tables reside in a shared storage repository and contain class IDs, allowing for association via class ID. In a distributed system, any computing node, such as Computing Node 1, receives a join query task initiated by a client (i.e., Computing Node 1 is the primary node responsible for the join query task). This task, for example, involves querying all teachers, all students, and class rankings for classes 1, 2, and 3 in the three tables. Computing Node 1 treats the shared storage as local storage. It can modify the keywords in the original query statement to "local join query," rewrite the task, and split it into distributed join query tasks. These distributed join query tasks are then distributed to Computing Nodes 2 and 3. Computing Nodes 2 and 3, as auxiliary nodes for the original join query task, can directly execute their assigned distributed join query tasks. For example, by rewriting the split query statement, compute node 1 can be responsible for querying all teachers, all students and class ranking of class 1 by scanning the full data in the shared storage; compute node 2 can be responsible for querying all teachers, all students and class ranking of class 2 by scanning the full data in the shared storage; and compute node 3 can be responsible for querying all teachers, all students and class ranking of class 3 by scanning the full data in the shared storage, thus avoiding data exchange between the three compute nodes.

[0040] S102. Distribute distributed join query tasks to each computing node in the computing node cluster, so that each computing node can execute the corresponding distributed join query task based on the sharded table data stored by each shared storage node in the shared storage node cluster.

[0041] Specifically, data partitioning can be achieved through consistent hashing, or by allocating data sequentially, randomly, or according to the performance of the compute nodes, thus determining the query tasks assigned to each compute node. After determining the query tasks assigned to each compute node, the corresponding distributed join query tasks can be issued to each compute node. Because each compute node treats shared storage as local storage, and the keyword in the query statement of the distributed join query task is "local join query," any compute node in the distributed system can scan the sharded table data stored on each shared storage node in the shared storage node cluster to perform its assigned data query.

[0042] For example, in a shared storage node cluster, each storage node stores Table 1, Table 2, and Table 3 in sequence. When compute node 1 is responsible for querying all teachers, all students, and class rankings of Class 1, it can directly scan the data stored in Table 1, Table 2, and Table 3 in each storage node of the shared storage node cluster to perform data query.

[0043] S103. Obtain the original data of the join table query obtained from each computing node by executing the corresponding distributed join table query task, and summarize the original data of the join table query to obtain the target data of the join table query.

[0044] In this embodiment, after each computing node completes its corresponding join query task, the main node responsible for the original join query task summarizes the data obtained by each node to obtain the join query target data.

[0045] For example, compute node 1 is the primary node responsible for the original task of a join query, while compute nodes 2 and 3 are secondary or collaborating nodes of the same task. Compute node 1 queries data in tables 1, 2, and 3 to obtain all teachers, all students, and class rankings for class 1. Compute node 2 queries data in tables 1, 2, and 3 to obtain all teachers, all students, and class rankings for class 2. Compute node 3 queries data in tables 1, 2, and 3 to obtain all teachers, all students, and class rankings for class 3. Compute nodes 2 and 3 can send the retrieved data to compute node 1, which then aggregates the data from classes 1, 2, and 3 to obtain the target data for the join query.

[0046] S104. Feedback the target data from the join query to the client.

[0047] In this embodiment, after the computing nodes of the distributed system obtain the target data through integration, they feed the target data back to the client. For example, after computing node 1 summarizes the data queried by computing nodes 1, 2, and 3, computing node 1 can feed the summarized join query target data back to the client.

[0048] It is understood that in this embodiment, the applied distributed system includes a compute node cluster with separate storage and compute operations, and a shared storage node cluster. After receiving the original join query task initiated by the client, the original join query task can be rewritten into a distributed join query task. Distributed join query tasks are then distributed to each compute node in the compute node cluster, enabling each compute node to execute the corresponding distributed join query task based on the sharded table data stored on each shared storage node in the shared storage node cluster. The original join query data obtained from executing the corresponding distributed join query task is retrieved from each compute node, and the original join query data is summarized to obtain the join query target data. The join query target data is then fed back to the client. In other words, this invention separates compute nodes and storage nodes, giving the system strong scalability. Furthermore, since the storage nodes use shared storage, each compute node can see the data stored on all storage nodes. Therefore, during join queries, there is no need for data exchange between compute nodes, and the queried data does not need to be redistributed, making it suitable for joint queries of large tables, saving network overhead, and improving query performance.

[0049] The following further describes the join table query method provided by the embodiments of the present invention, such as... Figure 4 As shown, Figure 4 This is a flowchart illustrating a join table query method provided in an embodiment of the present invention. Figure 2 This method may specifically include the following steps:

[0050] S201. Obtain the original table data and slice the original table data to obtain sharded table data.

[0051] Because single-machine storage capacity, connection count, and processing power are limited, when the data volume of a single table is large, the original table data can be sliced ​​into sharded tables. The specific number of shards depends on actual needs, such as the hardware and software resources of the single machine. For example, when the data volume of a single table reaches 10 million+ or 100GB+, due to the large number of query dimensions, even adding slave databases and optimizing indexes cannot fundamentally change the situation. At this point, it is necessary to consider sharding the table data to reduce the burden on the database and shorten query time.

[0052] Specifically, raw table data can be obtained from HDFS servers, HBase servers, Hive servers, etc. The raw table data can be sliced ​​through vertical or horizontal sharding to obtain sharded table data. Each sharded table data can be labeled with an identifier (such as a number or sequence number) to facilitate subsequent storage.

[0053] S202. Determine the number of shared storage nodes in the shared storage node cluster.

[0054] Specifically, the number of shared storage nodes can be obtained by traversing the shared storage nodes in the shared storage node cluster; alternatively, it can be obtained by querying the coordination service node.

[0055] S203. Perform a hash modulo operation on the sharded table data based on the number of shared storage nodes to obtain the hash modulo value of the sharded table data.

[0056] Hash modulo operation is a special algorithm that uses a hash function to map an input of arbitrary length to an output of fixed length.

[0057] S204. Determine the shared storage node corresponding to the shard table data based on the hash modulo value of the shard table data.

[0058] In this embodiment, after obtaining the number of shared storage nodes, a hash modulo operation can be performed on the identification information of each shard table data (the identification information of the shard table data can be the number and sequence number of the shard table data, etc.) in combination with the number of shared storage nodes to obtain the hash modulo value corresponding to each shard table data. Based on the hash modulo value, the shared storage nodes to be allocated to the shard table data in the future can be determined.

[0059] For example, the shared storage node corresponding to the sharded table data can be calculated using formula (1):

[0060] H1 = hash(key) mod K(1)

[0061] In formula (1), H1 is the hash modulo value of the sharded table data, and K is the number of shared storage nodes. Based on the hashing method above, the shared storage nodes to be allocated to the sharded table data can be determined. For example, if the hash modulo value corresponding to a certain sharded table data is 2, since each shared storage node has a corresponding number, the hash modulo value 2 corresponding to the sharded table data can be matched with the number of each shared storage node to find the shared storage node corresponding to the sharded table data.

[0062] S205. Request a distributed lock from the coordination service node for storing sharded table data.

[0063] Specifically, each sharded table data can correspond to a storage task (or write task), and a corresponding storage task can be generated for each sharded table data. When any compute node needs to execute a storage task, it can combine the identification information of the storage task with the identification information of the corresponding shared storage node to obtain a distributed lock identifier. The task identifier can be an automatically assigned identification number (ID), and the shared storage node identifier can be the IP address of the server corresponding to the shared storage node; that is, the distributed lock identifier can be represented as ID_IP. After obtaining the distributed lock identifier, a lock identifier write request can be sent to the coordinating service node to request a distributed lock for storing sharded table data. After receiving a lock identifier write request, the coordination service node can query whether the distributed lock identifier already exists in a preset database (e.g., a distributed lock management database). If it does exist, it means another node has already held the distributed lock for this task, and a write failure notification is returned. A failed lock identifier write indicates that the current task's request for a distributed lock has failed. If the coordination service node finds that the distributed lock identifier does not exist in the preset database, it writes the distributed lock identifier to the preset database and returns a write success notification. A successful write indicates that the distributed lock has been successfully requested for the current task. Optionally, to avoid deadlocks, an expiration time can be set for the distributed lock. For example, if a write task crashes while holding the distributed lock without actively unlocking it, causing task blockage, an expiration time can be set in the preset database. When a task holds the distributed lock for longer than the expiration time, it will automatically unlock.

[0064] S206. Upon successfully acquiring the distributed lock, the sharded table data is stored in the corresponding shared storage node.

[0065] That is, the storage task corresponding to the data in the sharded table is allowed to be executed only when the distributed lock is held, so as to prevent write errors caused by concurrent execution of the same task.

[0066] In this embodiment, the sharded table data can also be stored in the shared storage node in other conventional ways. It is not necessary to pre-distribute the data according to the association key. That is, it is not necessary to support local queries by storing data with the same association key in the same node. Even if data with the same association key is stored in different nodes, due to the use of shared storage, each computing node can regard the shared storage as a local node, thereby realizing local queries without data pre-distribution and improving the flexibility and convenience of data storage.

[0067] S207, Release the distributed lock.

[0068] For example, after the data in the sharded table is successfully written, an unlock request can be sent to the coordination service node to request the deletion of the lock identifier for the current task stored in the preset database, thereby reducing the database's memory usage. Additionally, if the write operation to the preset database fails, i.e., acquiring the distributed lock fails, a second request to acquire the distributed lock for the current task can be made at preset intervals (e.g., 2 seconds or 3 seconds).

[0069] S208. Receive the original task of table join query initiated by the client, and determine the main table, the secondary table and the join conditions corresponding to the original task of table join query.

[0070] The client-initiated join query task can be distributed to various compute nodes in the compute node cluster according to a certain strategy (such as load balancing, task ordering, randomness, etc.). The compute node that receives the original join query task is the primary responsible node for that task, while the other compute nodes in the cluster are auxiliary nodes. The primary responsible node is mainly responsible for parsing, rewriting, splitting, executing its own distributed join query task, and data aggregation, while the auxiliary nodes are mainly responsible for executing the distributed join query task obtained by rewriting the original join query task.

[0071] Specifically, taking the current compute node as the primary responsible node for the original join query task as an example, the current compute node can parse the original join query task to obtain the original query statement. By analyzing the original query statement, the primary table, secondary tables, and join conditions corresponding to the original join query task can be determined. For example, the original query statement can be parsed to obtain the multiple tables involved in this join query. The table with the largest amount of data among the multiple tables can be designated as the primary table, and the other tables as secondary tables. The join conditions can be determined by parsing the primary keys and foreign keys of the primary and secondary tables.

[0072] S209. Rewrite the original task of the join query based on the main table, the secondary table, and the association conditions corresponding to the original task of the join query to obtain the distributed join query task.

[0073] In this embodiment, after determining the main table, the secondary table, and the association conditions, the current computing node can rewrite the original query statement in the original join query task. The current computing node can treat shared storage as local storage and rewrite the keyword "join query" in the original query statement as "local join query". The rewritten query task is then split to obtain distributed join query tasks. Each distributed join query task can be identified (e.g., numbered or sequenced) for subsequent processing.

[0074] S210. Determine the number of computing nodes in the computing node cluster.

[0075] Specifically, the number of compute nodes can be obtained by traversing the compute nodes in the compute node cluster; alternatively, it can be obtained by querying the coordination service node.

[0076] S211. Perform a hash modulo operation on the distributed join query task based on the number of computing nodes to obtain the hash modulo value of the distributed join query task.

[0077] S212. Determine the distributed join query task to be executed on each computing node based on the hash modulo value of the distributed join query task.

[0078] Specifically, after obtaining the number of computing nodes, the hash modulo operation is performed on the identifier information of each distributed join query task using formula (2) in combination with the number of computing nodes, to obtain the hash modulo value corresponding to each distributed join query task.

[0079] H2 = hash(key) mod L (2)

[0080] In formula (2), H2 is the hash modulo value of the distributed join query task, and L is the number of computing nodes. Based on the hash method above, the distributed join query task that each computing node should execute can be determined. For example, if the hash modulo value corresponding to a certain distributed join query task is 2, since each computing node has a corresponding number, the hash modulo value 2 corresponding to the distributed join query task can be matched with the number of each computing node, and the computing node that executes the distributed join query task can be found.

[0081] S213. Distribute the corresponding distributed join table query tasks to each computing node.

[0082] In this embodiment, due to the existence of a computing node cluster, each computing node in the cluster can execute the corresponding distributed query task in parallel to improve query efficiency. For example... Figure 5 As shown, Figure 5 This is a schematic diagram illustrating the effect of a join table query method provided in an embodiment of the present invention. Figure 1 ,exist Figure 5 In this context, when performing join table data queries, tasks can be allocated using methods such as consistent hashing to determine the distributed join table query task to be executed by each computing node. For example, when there are 4 computing nodes, each computing node can read one-quarter of the data, and all 4 nodes can execute their respective read data tasks simultaneously. This fully utilizes the computing and network resources of each computing node, improving the overall data query efficiency.

[0083] S214. Obtain the original data of the join table query obtained from each computing node by executing the corresponding distributed join table query task, and summarize the original data of the join table query to obtain the target data of the join table query.

[0084] Specific summarization methods may include merging, filtering, and standardizing formats, etc., and no specific limitations are made here.

[0085] S215. Feedback the target data from the join query to the client.

[0086] Understandably, in this embodiment, slicing the original data to obtain sharded table data improves storage efficiency, reduces storage burden, and shortens query time. Furthermore, by applying for a distributed lock for storing sharded table data, concurrent execution of the same task can be prevented, improving data storage success rate. Releasing the distributed lock after storing the sharded table data into the corresponding shared storage node reduces resource consumption. Separating compute nodes and storage nodes gives the system strong scalability; and since the storage nodes use shared storage, each compute node can see the data stored on all storage nodes. Therefore, during join queries, compute nodes do not need to exchange data, and query data does not need to be redistributed, making it suitable for join queries on large tables, saving network overhead, and improving query performance.

[0087] The following examples illustrate the main differences between the join table query method provided in this invention and current join table query methods, such as... Figure 6 As shown, Figure 6 This is a schematic diagram illustrating the effect of a join table query method provided in an embodiment of the present invention. Figure 2 Compared to Figure 1 , Figure 6 The join table query method is applied in a storage-compute separation architecture. Figure 6 In the process, compute node 1 receives a raw join query task, which performs a join query on tables 1 and 2 with the join key A = B. Compute node 1 splits the raw join query task into two distributed join query tasks (assuming task 1 and task 2, where task 1 queries data 10 and task 2 queries data 11) by modifying keywords in the query statement (e.g., changing "join query" to "local join query"). Tables 1 and 2 are stored in a distributed shared storage warehouse. Because each node can see all the data in tables 1 and 2 through the distributed shared storage warehouse, when compute node 1 executes its own distributed join query task (e.g., task 1), it can directly scan tables 1 and 2 in the distributed shared storage warehouse to query data 10. When compute node 2 executes its own distributed join query task (e.g., task 2), it can directly scan tables 1 and 2 in the distributed shared storage warehouse to query data 11, avoiding... Figure 1 The data exchange operation between compute node 1 and compute node 2 is completed, and finally compute node 1 summarizes the queried data and returns it to the client. Figure 6In this system, because storage and computing resources are separated, computing and storage can be expanded separately. At the same time, computing and storage resources are cloud-native and efficiently managed, which can greatly improve resource utilization, reduce operating costs, and aggregate and analyze petabyte-level data with a response time of seconds or even milliseconds.

[0088] Figure 7 This is a schematic diagram of a joint table query device provided in an embodiment of the present invention, as shown below. Figure 7 As shown, the device may specifically include:

[0089] The task rewriting module 701 is used to receive the original task of the join table query initiated by the client and rewrite the original task of the join table query into a distributed join table query task.

[0090] The task distribution module 702 is used to distribute the distributed join table query task to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join table query task based on the shard table data stored in each shared storage node in the shared storage node cluster.

[0091] The data aggregation module 703 is used to obtain the original data of the table query obtained by executing the corresponding distributed table query task from each computing node, and to aggregate the original data of the table query to obtain the target data of the table query.

[0092] Feedback module 704 is used to provide feedback to the client regarding the target data of the join query.

[0093] In one embodiment, the task rewriting module 701 is specifically used for:

[0094] Determine the primary table, secondary table, and association conditions corresponding to the original task of the join query;

[0095] The original join query task is rewritten based on the main table, the secondary table, and the association conditions corresponding to the original join query task to obtain the distributed join query task.

[0096] In one embodiment, the task issuing module 702 is specifically used for:

[0097] Determine the number of computing nodes in the computing node cluster;

[0098] The hash modulo operation is performed on the distributed join query task based on the number of computing nodes to obtain the hash modulo value of the distributed join query task;

[0099] The distributed join query task executed by each computing node is determined based on the hash modulo value of the distributed join query task.

[0100] The corresponding distributed join table query task is issued to each computing node.

[0101] In one embodiment, the join table query device further includes a data storage module, which is used for:

[0102] Before the task rewriting module 701 receives the original task of joining table query initiated by the client, it obtains the original table data and slices the original table data to obtain the sharded table data; the sharded table data is stored in each shared storage node in the shared storage node cluster.

[0103] In one embodiment, the data storage module is specifically used for:

[0104] Determine the number of shared storage nodes in the shared storage node cluster;

[0105] The hash modulo operation is performed on the shard table data according to the number of shared storage nodes to obtain the hash modulo value of the shard table data;

[0106] The shared storage node corresponding to the shard table data is determined based on the hash modulo value of the shard table data;

[0107] The sharded table data is stored in the corresponding shared storage node.

[0108] In one embodiment, the data storage module is specifically used for:

[0109] The coordination service node requests a distributed lock for storing the sharded table data.

[0110] Upon successfully acquiring the distributed lock, the step of storing the sharded table data into the corresponding shared storage node is triggered.

[0111] After storing the sharded table data into the corresponding shared storage node, the distributed lock is released.

[0112] The apparatus of this invention is applied in a distributed system including a compute node cluster with separate storage and compute nodes and a shared storage node cluster. After receiving the original join query task initiated by the client, the original join query task can be rewritten into a distributed join query task. Distributed join query tasks are then distributed to each compute node in the compute node cluster, enabling each compute node to execute the corresponding distributed join query task based on the sharded table data stored on each shared storage node in the shared storage node cluster. The original join query data obtained from executing the corresponding distributed join query task is retrieved from each compute node, and the original join query data is summarized to obtain the join query target data. The join query target data is then fed back to the client. In other words, this invention separates compute nodes and storage nodes, giving the system strong scalability. Furthermore, since the storage nodes use shared storage, each compute node can see the data stored on all storage nodes. Therefore, during join queries, there is no need for data exchange between compute nodes, and the queried data does not need to be redistributed, making it suitable for joint queries of large tables, saving network overhead, and improving query performance.

[0113] This invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, it implements the join table lookup method provided in any of the above embodiments.

[0114] This invention also provides a computer-readable medium storing a computer program thereon, which, when executed by a processor, implements the join table lookup method provided in any of the above embodiments.

[0115] The following is for reference. Figure 8 It shows a schematic diagram of the structure of a computer system 800 suitable for implementing an electronic device according to embodiments of the present invention. Figure 8 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of the invention.

[0116] like Figure 8 As shown, the computer system 800 includes a central processing unit (CPU) 801, which can perform various appropriate actions and processes based on programs stored in read-only memory (ROM) 802 or programs loaded from storage section 808 into random access memory (RAM) 803. The RAM 803 also stores various programs and data required for the operation of the computer system 800. The CPU 801, ROM 802, and RAM 803 are interconnected via a bus 804. An input / output (I / O) interface 805 is also connected to the bus 804.

[0117] The following components are connected to I / O interface 805: an input section 806 including a keyboard, mouse, etc.; an output section 807 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and speakers, etc.; a storage section 808 including a hard disk, etc.; and a communication section 809 including a network interface card such as a LAN card, modem, etc. The communication section 809 performs communication processing via a network such as the Internet. A drive 810 is also connected to I / O interface 805 as needed. A removable medium 811, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on drive 810 as needed so that computer programs read from it can be installed into storage section 808 as needed.

[0118] In particular, according to the embodiments disclosed in this invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this invention include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via communication section 809, and / or installed from removable medium 811. When the computer program is executed by central processing unit (CPU) 801, it performs the functions defined above in the system of this invention.

[0119] It should be noted that the computer-readable medium shown in this invention can be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium can be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In this invention, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media can also be any computer-readable medium other than computer-readable storage media, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted using any suitable medium, including but not limited to: wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.

[0120] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0121] The modules and / or units described in this invention can be implemented in software or hardware. The described modules and / or units can also be housed in a processor; for example, a processor can be described as including a task rewriting module, a task distribution module, a data aggregation module, and a feedback module. The names of these modules do not necessarily limit the module itself.

[0122] In another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiments; or it may exist independently and not assembled into the device. The computer-readable medium carries one or more programs, which, when executed by the device, cause the device to include:

[0123] Receive the original table join query task initiated by the client, and rewrite the original table join query task into a distributed table join query task;

[0124] The distributed join query task is issued to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join query task based on the shard table data stored in each shared storage node in the shared storage node cluster.

[0125] The original data of the join table query obtained from executing the corresponding distributed join table query task is obtained from each computing node, and the original data of the join table query is summarized to obtain the target data of the join table query.

[0126] The target data for the join query is fed back to the client.

[0127] The solution in this invention applies to a distributed system including a compute node cluster with separate storage and compute operations and a shared storage node cluster. After receiving the original join query task initiated by the client, the original join query task can be rewritten into a distributed join query task. Distributed join query tasks are then distributed to each compute node in the compute node cluster, enabling each compute node to execute the corresponding distributed join query task based on the sharded table data stored on each shared storage node in the shared storage node cluster. The original join query data obtained from executing the corresponding distributed join query task is retrieved from each compute node, and the original join query data is summarized to obtain the join query target data. The join query target data is then fed back to the client. In other words, this invention separates compute nodes and storage nodes, giving the system strong scalability. Furthermore, since the storage nodes use shared storage, each compute node can see the data stored on all storage nodes. Therefore, during join queries, there is no need for data exchange between compute nodes, and the queried data does not need to be redistributed, making it suitable for joint queries of large tables, saving network overhead, and improving query performance.

[0128] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0129] It should be noted that the collection, gathering, updating, analysis, processing, use, transmission, and storage of user personal information involved in this disclosed technical solution all comply with relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. Necessary measures are taken to prevent unauthorized access to user personal information data and to safeguard user personal information security, network security, and national security.

[0130] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A join table lookup method, characterized in that, Applied to a distributed system, the distributed system including a compute node cluster with separate storage and a shared storage node cluster, the method includes: Receive the original table join query task initiated by the client, and rewrite the original table join query task into a distributed table join query task; The distributed join query task is issued to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join query task based on the sharded table data stored in each shared storage node in the shared storage node cluster; the shared storage node is regarded as local storage by each computing node. The original data of the join table query obtained from executing the corresponding distributed join table query task is obtained from each computing node, and the original data of the join table query is summarized to obtain the target data of the join table query. The target data of the join query is fed back to the client. The step of rewriting the original join query task into a distributed join query task includes: The primary table, secondary table, and join conditions corresponding to the original task of the join query are determined. The primary table is the table with the largest amount of data among the multiple tables involved in the original query statement of the original task of the join query. The secondary tables are the other tables besides the primary table among the multiple tables. The join conditions are determined based on the primary key or foreign key of the primary table and the primary key or foreign key of the secondary table. The original join query task is rewritten based on the main table, the secondary table, and the association conditions corresponding to the original join query task to obtain the distributed join query task; the rewriting includes changing the keyword "join query" in the original query statement of the original join query task to "local join query".

2. The join table query method according to claim 1, characterized in that, The step of issuing the distributed join query task to each computing node in the computing node cluster includes: Determine the number of computing nodes in the computing node cluster; The hash modulo operation is performed on the distributed join query task based on the number of computing nodes to obtain the hash modulo value of the distributed join query task; The distributed join query task executed by each computing node is determined based on the hash modulo value of the distributed join query task. The corresponding distributed join table query task is issued to each computing node.

3. The join table lookup method according to claim 1, characterized in that, Before receiving the original join query task initiated by the client, it also includes: Obtain the original table data and slice the original table data to obtain the sharded table data; The sharded table data is stored in each shared storage node of the shared storage node cluster.

4. The join table lookup method according to claim 3, characterized in that, The step of storing the sharded table data into each shared storage node in the shared storage node cluster includes: Determine the number of shared storage nodes in the shared storage node cluster; The hash modulo operation is performed on the shard table data according to the number of shared storage nodes to obtain the hash modulo value of the shard table data; The shared storage node corresponding to the shard table data is determined based on the hash modulo value of the shard table data; The sharded table data is stored in the corresponding shared storage node.

5. The join table query method according to claim 4, characterized in that, The distributed system also includes a coordination service node, which, before storing the sharded table data into the corresponding shared storage node, further includes: The coordination service node requests a distributed lock for storing the sharded table data. Upon successfully acquiring the distributed lock, the step of storing the sharded table data into the corresponding shared storage node is triggered. After storing the sharded table data into the corresponding shared storage node, the process further includes: Release the distributed lock.

6. A table lookup device, characterized in that, Applied to a distributed system, the distributed system including a compute node cluster with separate storage and a shared storage node cluster, the device includes: The task rewriting module is used to receive the original table join query task initiated by the client and rewrite the original table join query task into a distributed table join query task. The task distribution module is used to distribute the distributed join query task to each computing node in the computing node cluster, so that each computing node executes the corresponding distributed join query task based on the sharded table data stored in each shared storage node in the shared storage node cluster; the shared storage node is regarded as local storage by each computing node. The data aggregation module is used to obtain the original data of the join table query obtained by executing the corresponding distributed join table query task from each computing node, and to aggregate the original data of the join table query to obtain the target data of the join table query. The feedback module is used to provide feedback to the client regarding the target data of the join query. Specifically, the task rewriting module is used for: The primary table, secondary table, and join conditions corresponding to the original task of the join query are determined. The primary table is the table with the largest amount of data among the multiple tables involved in the original query statement of the original task of the join query. The secondary tables are the other tables besides the primary table among the multiple tables. The join conditions are determined based on the primary key or foreign key of the primary table and the primary key or foreign key of the secondary table. The original join query task is rewritten based on the main table, the secondary table, and the association conditions corresponding to the original join query task to obtain the distributed join query task; the rewriting includes changing the keyword "join query" in the original query statement of the original join query task to "local join query".

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the join table lookup method as described in any one of claims 1 to 5.

8. A distributed system, characterized in that, It includes a compute node cluster with separate storage and compute operations and a shared storage node cluster, wherein any compute node in the compute node cluster is used to execute the join table query method as described in any one of claims 1 to 5.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the join table lookup method as described in any one of claims 1 to 5.

Images