Computer system and query processing method

Abstract

To provide a technique capable of reducing a processing load when joining a plurality of tables by means of hash join.SOLUTION: In a computer system 1, CPUs 12, 22 of nodes 10, 20 are configured to: create a partial bloom filter 151 that manages a first table hash value of a joining key of a row corresponding to a query in an assigned row of a build table; create an integrated bloom filter 161 by integrating a plurality of partial bloom filters 151; calculate a second table hash value of the joining key of the row corresponding to the condition of the query among the rows of a probe table; if an identical first table hash value is present in the integrated bloom filter 161, transmit the row of the probe table to the node storing a row of the build table of a join hash value for that row; if the row of the probe table is received, create a joined table by joining a field of the row of the probe table and a field of the row of the corresponding build table; and integrate a plurality of joined tables to return it to a request source of the query.SELECTED DRAWING: Figure 1

Description

[Technical Field]

[0001] The present invention relates to techniques for processing queries involving multiple tables. [Background technology]

[0002] A join process is sometimes performed using a database to combine two tables. In a join process, the smaller of the two tables to be combined is called the build table, and the larger table is called the probe table. A well-known example of a join process is a hash join. In a hash join, the build table is read, filtered, and a hash value is calculated to create a hash table for the build table. The probe table is then read, filtered, and a hash value is calculated, which is then compared with the hash table for the build table to join the tables. In this way, when comparing the build table with the hash table, if the computer system has a multi-node configuration, the hash value of the probe table must be transferred to the node that has the hash table for the build table that manages the corresponding values, which poses a problem of increased communication costs between nodes.

[0003] In response to this, there is known a technique for reducing the load of join processing by using a Bloom filter that determines whether or not matching data exists in a node during table join processing (see, for example, Patent Document 1). [Prior art documents] [Patent documents]

[0004] [Patent Document 1] U.S. Patent Application Publication No. 2021 / 0049173 Summary of the Invention [Problem to be solved by the invention]

[0005] In the technology disclosed in Patent Document 1, Bloom filters for both the build table and the probe table are created simultaneously at each node during a hash join, and the Bloom filters are periodically replaced and updated. Rows filtered by applying a Bloom filter are then cached, and when the Bloom filter is updated, the rows must be applied to the same Bloom filter again. Therefore, the same rows must be applied to the Bloom filter multiple times, which increases the processing load.

[0006] The present invention has been made in view of the above circumstances, and its object is to provide a technique that can reduce the processing load when joining multiple tables using a hash join. [Means for solving the problem]

[0007] In order to achieve the above object, a computer system according to one aspect is a computer system having a storage for storing a plurality of tables and a plurality of nodes for processing queries relating to the plurality of tables, wherein the query is a query for joining fields of the plurality of tables according to a value of a predetermined join key, and a processor of each of the plurality of nodes creates a Bloom filter that manages information of a first table hash value, which is a first type of hash value corresponding to the join key of a row that satisfies the condition of the query among rows assigned to the node in a first table that is the smallest of the tables to be joined, integrates the plurality of Bloom filters created by the plurality of nodes to create an integrated Bloom filter that manages information of the first table hash value that corresponds to the join key of a row that satisfies the condition of the query among all rows of the first table, and integrates the information of the first table hash value corresponding to the join key of a row of a second table that is different from the first table to be joined, and a second table hash value, which is the first type hash value corresponding to the join key of a row that satisfies a condition; and, if the integrated Bloom filter contains the first table hash value that is identical to the calculated second table hash value, a node among the plurality of nodes that stores the row of the first table with the second type hash value based on the second type hash value of the row data of the second table, and transmits the data of the row of the second table to the node; and, upon receiving the data of the row of the second table from another node, creates a joined table by joining a field specified in the query of the row of the second table with a field specified in the query of the row of the first table that corresponds to the same second type hash value as the second type hash value of the row of the second table; and a processor of any one of the plurality of nodes integrates the multiple joined tables created at each node and returns the integrated Bloom filter to the source of the query. [Effects of the Invention]

[0008] According to the present invention, it is possible to reduce the processing load when joining multiple tables using hash join. [Brief explanation of the drawings]

[0009] [Figure 1] FIG. 1 is a diagram showing the overall configuration of a computer system according to an embodiment. [Figure 2] FIG. 2 is a diagram illustrating an example of a configuration of a query according to the first embodiment. [Figure 3] FIG. 3 is a diagram illustrating the creation of a Bloom filter according to the first embodiment. [Figure 4] FIG. 4 is a diagram illustrating the use of a Bloom filter according to the first embodiment. [Figure 5] FIG. 5 is a diagram illustrating the creation of an integrated Bloom filter according to the first embodiment. [Figure 6] FIG. 6 is a configuration diagram of table statistical information according to the first embodiment. [Figure 7] FIG. 7 is a diagram showing the structure of table storage information according to the first embodiment. [Figure 8] FIG. 8 is a diagram showing the configuration of a hash table for join of a build table according to the first embodiment. [Figure 9] FIG. 9 is a diagram showing the configuration of a hash table for joining probe tables according to the first embodiment. [Figure 10] FIG. 10 is a diagram showing the structure of a join table according to the first embodiment. [Figure 11] FIG. 11 is a flowchart of hash join request processing according to the first embodiment. [Figure 12] FIG. 12 is a flowchart of the build table / probe table determination process according to the first embodiment. [Figure 13] FIG. 13 is a flowchart of the partial Bloom filter creation process according to the first embodiment. [Figure 14] FIG. 14 is a flowchart of the probe table necessary range determination process according to the first embodiment. [Figure 15] FIG. 15 is a flowchart of the matching process according to the first embodiment. [Figure 16] FIG. 16 is a configuration diagram of a Bloom filter for each node according to the second embodiment. [Figure 17] FIG. 17 is a flowchart of hash join request processing according to the second embodiment. [Figure 18]FIG. 18 is a flowchart of the Bloom filter application process for each node according to the second embodiment. [Figure 19] FIG. 19 is a diagram illustrating a specific example of the Bloom filter application process for each node according to the second embodiment. DETAILED DESCRIPTION OF THE INVENTION

[0010] The following description of the embodiments will be given with reference to the drawings. Note that the embodiments described below do not limit the scope of the invention as claimed, and not all of the elements and combinations thereof described in the embodiments are necessarily essential to the solution of the invention.

[0011] In the following explanation, information may be described using the expression "AAA table", but the information may be expressed in any data structure. In other words, to show that the information does not depend on the data structure, the "AAA table" can be called "AAA information".

[0012] In the following description, processing may be described with a "program" as the subject of operation. However, since a program is executed by a processor (e.g., a CPU (Central Processing Unit)) to perform a predetermined process using a storage unit (e.g., a memory) and / or an interface (e.g., a port) as appropriate, the program may also be the subject of the processing operations. Processing described with a program as the subject of operation may also be processing performed by a processor or a computer having the processor (e.g., a server). It may also include a hardware circuit that performs some or all of the processing performed by the processor. A program may also be installed from a program source. The program source may be, for example, a program distribution server or a computer-readable (e.g., non-transitory) recording medium. In the following description, two or more programs may be realized as one program, or one program may be realized as two or more programs.

[0013] FIG. 1 is a diagram showing the overall configuration of a computer system according to an embodiment.

[0014] The computer system 1 includes a main node 10, one or more sub-nodes 20, and an object storage 30. The main node 10 and the sub-nodes 20 are connected via a network 2. The main node 10, the sub-nodes 20, and the object storage 30 are connected via a network 3.

[0015] The master node 10 and the slave node 20 configure a distributed database using the storage area of ​​the object storage 30.

[0016] The master node 10 accepts queries to the database from a user terminal (not shown), executes processing in accordance with the queries together with the slave node 20, and returns the processing results to the user terminal.

[0017] The main node 10 includes a memory 11 and a CPU 12 as an example of a processor. The CPU 12 executes various processes according to programs stored in the memory 11. The memory 11 is, for example, a RAM (RANDOM ACCESS MEMORY), and stores the programs executed by the CPU 12 and necessary information.

[0018] The memory 11 stores a DBMS (database management system) 100. The DBMS 100 includes a build program (in the figure, the program may be abbreviated as PG) 110, a probe program 120, a main node query control program 130, an inter-node communication program 140, a partial Bloom filter control program 150, an integrated Bloom filter control program 160, a table management area 170, table statistical information 180, and a storage-node communication program 190.

[0019] The build program 110 is executed by the CPU 12 to perform various processes related to the build table (Table 1). The probe program 120 is executed by the CPU 12 to perform various processes related to the probe table (Table 2). The main node query control program 130 is executed by the CPU 12 to control the processing as the main node 10 to execute queries. The processing as the main node 10 includes processing to distribute queries to its own node and the secondary nodes 20 for execution. The main node query control program 130 includes a query reception program 131. The query reception program 131 is executed by the CPU 12 to receive queries from user terminals.

[0020] The inter-node communication program 140 is executed by the CPU 12 to perform communication between the main node 10 and the secondary node 20 .

[0021] The partial Bloom filter control program 150 is executed by the CPU 12 to create a partial Bloom filter 151, manage the partial Bloom filter, and execute processing for using the partial Bloom filter.

[0022] The integrated Bloom filter control program 160 is executed by the CPU 12 to create an integrated Bloom filter 161, manage the integrated Bloom filter 161, and execute processing for using the integrated Bloom filter 161.

[0023] The table management area 170 is an area for managing tables stored in the object storage 30, and stores a table storage program 171 and table storage information 172.

[0024] The table storage program 171 is executed by the CPU 12 to store a table read from the object storage 30 in the memory 11. The table storage information 172 stores information on the table stored in the object storage 30 (for example, a part of the table, etc.).

[0025] The table statistical information 180 stores statistical information of tables stored in the object storage 30.

[0026] The storage-node communication program 190 is executed by the CPU 12 to perform communication with the object storage 30.

[0027] The sub-node 20 receives a query from the main node 10, executes processing according to the query, and returns the processing result to the main node 10.

[0028] The sub node 20 includes a memory 21 and a CPU 22 as an example of a processor. The CPU 22 executes various processes in accordance with programs stored in the memory 21. The memory 21 is, for example, a RAM, and stores the programs executed by the CPU 22 and necessary information.

[0029] The memory 21 stores the DBMS 200. The DBMS 200 includes a build program 110, a probe program 120, a sub-node query control program 230, an inter-node communication program 140, a partial Bloom filter control program 150, an integrated Bloom filter control program 160, a table management area 170, and a storage-to-node communication program 190. Here, components in the memory 21 that are assigned the same reference numerals as those in the memory 11 of the main node 10 are the same or similar to the components in the memory 11. Note that the information stored in the partial Bloom filter 151 is not the same, but is information corresponding to the respective states of the main node 10 and the other sub-nodes 20.

[0030] The sub-node query control program 230 is executed by the CPU 22 to control the processing of the sub-node 20 to execute queries. The processing of the sub-node 20 includes the processing of executing queries sent from the main node 10 and returning the query execution results to the main node 10.

[0031] The object storage 30 includes a storage device (not shown) and stores data (DB data 32) such as tables managed by a DBMS as objects 31.

[0032] Next, a query sent from a user terminal to the master node 10 will be described.

[0033] FIG. 2 is a diagram illustrating an example of a configuration of a query according to the first embodiment.

[0034] This query is for performing a hash join in SQL format, and it selects and joins the fields (items) O_Orderdate and L_itemnumber from the Order and Lineitem tables (tables to be joined) listed in the FROM clause, where O_orderdate is earlier than 2000-1-1 (an example of a selection condition) and O_orderkey matches L_orderkey. Note that in this query, O_orderkey and L_orderkey are the join keys.

[0035] Next, the creation and use of Bloom filters such as the partial Bloom filter 151 and the integrated Bloom filter 161 will be described.

[0036] Fig. 3 is a diagram illustrating the creation of a Bloom filter according to the first embodiment. Fig. 4 is a diagram illustrating the use of a Bloom filter according to the first embodiment.

[0037] The Bloom filters (151, 161) include a hash value row 151a containing Bloom filter hash values ​​(BF hash values: first-type hash values: first-table hash values) within a range of possible values ​​obtained by a predetermined hash calculation (Bloom filter hash calculation) on the value of a predetermined target item (target item: join key) in a table row (entry), and a bit row 151b indicating whether one or more target rows (comparison target rows) contain a value of an item corresponding to each value in the hash value row 151a. For example, if a target item in a comparison target row contains a value of an item corresponding to a predetermined value in the hash value row 151a, a 1 is set in the bit in the bit row 151b corresponding to that value. Here, in this embodiment, the Bloom filter hash calculation is a hash calculation that requires a lighter computational load than the hash calculation for hash join (described later) and narrows the possible range of the calculated hash value. This reduces the amount of data required for the Bloom filter and the processing load, such as the processing required for hash value calculation, compared to when Bloom filters are managed using join hash values. The hash calculation for the Bloom filter may be the same as the hash calculation for the hash join, that is, the hash value for the BF and the hash value for the join may be the same.

[0038] When the partial Bloom filter control program 150 or the integrated Bloom filter control program 160 creates (learns) a Bloom filter, it prepares in advance a range of hash values ​​that can be obtained by the Bloom filter hash calculation for a predetermined target item as a hash value row 151a, calculates a hash value by the Bloom filter hash calculation for each target item (learning data: O_orderkey in the example of FIG. 3) in the comparison target row, and sets the bit in the bit row 151b corresponding to the calculated hash value in the hash value row 151a to 1. For example, if the hash values ​​calculated by the Bloom filter hash calculation for O_orderkey is 101,505,378, are 1, 2, 5, the bits corresponding to the hash values ​​1, 2, 5 are set to 1.

[0039] When the partial Bloom filter control program 150 or the integrated Bloom filter control program 160 uses a Bloom filter, it calculates a hash value by Bloom filter hash calculation for a specific item (item to be checked: new data: L_orderkey in the example of Figure 4) of the row to be checked for existence, references the Bloom filters 151, 161, identifies the bit in the bit row 151b that corresponds to the calculated hash value in the hash value row 151a, and determines whether a target item with the same hash value exists in the row to be compared based on the value of this bit.

[0040] Next, a process in which the integrated Bloom filter control program 160 creates the integrated Bloom filter 161 will be described.

[0041] FIG. 5 is a diagram illustrating the creation of an integrated Bloom filter according to the first embodiment.

[0042] The integrated Bloom filter control program 160 receives partial Bloom filters 151 from other nodes and integrates the partial Bloom filter 151 of its own node with the partial Bloom filters 151 of the other nodes to create the integrated Bloom filter 161. Specifically, the integrated Bloom filter control program 160 sets to 1, in the integrated Bloom filter 161, a bit corresponding to a hash value for which a bit in any of the partial Bloom filters 151 is set to 1. For example, if the bit corresponding to the hash value 1, 2, 5, . . . 200 in the partial Bloom filter 151-1 of node 1 is set to 1, and the bit corresponding to the hash value 3, . . . is set to 1 in the partial Bloom filter 151-2 of node 2, the bit corresponding to the hash value 1, 2, 3, 5, . . . 200 is set to 1 in the integrated Bloom filter 161. In this way, the integrated Bloom filter 161 indicates whether or not the target item of each hash value exists in the compared row of any of the nodes.

[0043] Next, the table statistical information 180 will be described.

[0044] FIG. 6 is a configuration diagram of table statistical information according to the first embodiment.

[0045] The table statistical information 180 manages statistical information for each table (table) managed by the DBMS 100, 200, and stores rows (entries) for each table. Each piece of information in the table statistical information 180 can be identified by referencing the DB data 32 in the object storage 30. A row of the table statistical information 180 stores the following items: table name 180a, number of rows 180b, and size 180c.

[0046] The table name 180a stores the name of the table corresponding to the entry. The number of rows 180b stores the number of rows in the table corresponding to the entry. The size 180c stores the data size of the table corresponding to the entry.

[0047] Next, the table storage information 172 will be described.

[0048] FIG. 7 is a diagram showing the structure of table storage information according to the first embodiment.

[0049] Table storage information 172 stores information on items (columns, fields) stored in each table. Table storage information 172 stores a row for each table. A row of table storage information 172 stores the items of table name 172a and table information 172b.

[0050] The table name 172a stores the name of the table (table name) corresponding to the entry. The table information 172b stores information on the items included in the table corresponding to the entry (for example, item name, data type), etc.

[0051] Next, the build table join hash table 173, probe table join hash table 174, and join table 175 that are created in the table management area 170 by executing a query will be described.

[0052] FIG. 8 is a diagram showing the configuration of a hash table for join of a build table according to the first embodiment.

[0053] The build table join hash table 173 is a table that manages the join hash values ​​(join hash values) of rows in the build table that satisfy the query conditions, and stores rows corresponding to each row that satisfies the conditions. Note that Figure 8 conveniently shows the integrated state of the build table join hash table 173 that is distributed across the main node 10 and sub-nodes 20, but in reality, multiple rows of the build table join hash table 173 shown in Figure 8 are distributed across the main node 10 and sub-nodes 20.

[0054] A row of the build table join hash table 173 includes the items of hash value 173a, O_orderdate 173b, and O_orderkey 173c.

[0055] Hash value 173a stores a join hash value (second type hash value) calculated by a join hash calculation on the value of the target item (join key) of the row in the build table corresponding to the entry. In this embodiment, O_orderkey is the target item. O_orderdate 173b stores the value of O_orderdate of the row in the build table corresponding to the entry. O_orderkey 173c stores the value of O_orderkey of the row in the build table corresponding to the entry.

[0056] FIG. 9 is a diagram showing the configuration of a hash table for joining probe tables according to the first embodiment.

[0057] The probe table join hash table 174 is a table that manages hash values ​​for joining rows that satisfy the query conditions among the rows of the probe table, and stores rows corresponding to each row that satisfies the conditions. Note that Figure 9 conveniently shows the integrated state of the probe table join hash table 174 that is distributed across the main node 10 and the secondary node 20, but in reality, multiple rows of the probe table join hash table 174 shown in Figure 9 are distributed across the main node 10 and the secondary node 20.

[0058] A row of the probe table join hash table 174 includes the items of hash value 174a, L_itemnumber 174b, and L_orderkey 174c.

[0059] The hash value 174a stores a join hash value (second type hash value) calculated by a join hash calculation on the value of the target item (join key) of the row in the probe table corresponding to the entry. In this embodiment, the target item is L_orderkey. The L_itemnumber 174b stores the value of L_itemnumber of the row in the probe table corresponding to the entry. The L_orderkey 174c stores the value of L_orderkey of the row in the probe table corresponding to the entry.

[0060] FIG. 10 is a diagram showing the structure of a join table according to the first embodiment.

[0061] The join table 175 is a table that manages rows containing items from multiple tables that have been joined by executing a query, and stores rows (entries) containing items joined by the query. Note that while Figure 10 shows the final join table that is finally integrated at the main node 10, before being integrated at the main node 10, the join table exists in a distributed manner at the main node 10 and the secondary nodes 20.

[0062] Next, the processing operation of the computing system 1 according to the first embodiment will be described.

[0063] FIG. 11 is a flowchart of hash join request processing according to the first embodiment.

[0064] The hash join request process is a process that is executed when a hash join query is sent from a user terminal to the main node 10.

[0065] First, the query receiving program 131 of the main node query control program 130 of the main node 10 receives a hash join query sent from a user terminal (S201).

[0066] The main node query control program 130 then determines the build and probe tables. Build table / probe table determination processing (see FIG. 12) is executed (S202).

[0067] Next, main node query control program 130 uses table storage information 172 to determine the items of the tables (build table and probe table) to be read by each node (main node 10 and secondary node 20) (S203). Specifically, main node query control program 130 references the query and table storage information 172 to identify the items to be obtained from the build table and the items to be obtained from the probe table.

[0068] Next, main node query control program 130 determines the allocation of table rows to each node that will read the determined table items, and notifies each node of the range (items and rows) to be read (S204). For example, main node query control program 130 determines the allocation so that the number of table rows to be allocated to each node is approximately equal.

[0069] Upon receiving notification of the range to be read, the storage-node communication program 190 of each node reads the build table data within the notified range (rows and items) from the object storage 30 (S205), and the table storage program 171 stores the data within the range read as a build table (part of the build table) in the table management area 170 (S206).

[0070] Next, the partial Bloom filter control program 150 of each node executes a partial Bloom filter creation process (see FIG. 13) for creating a partial Bloom filter using the build table of each node (S207).

[0071] Next, the inter-node communication program 140 of each node exchanges the partial Bloom filters between each node, thereby collecting the partial Bloom filters at each node (S208).

[0072] Next, the integrated Bloom filter control program 160 creates an integrated Bloom filter by merging the partial Bloom filters at all nodes (S209).

[0073] Next, the build program 110 of each node calculates a hash value for each row of the node's build table (part) to create a build table join hash table 173 (S210).

[0074] Next, the build program 110 distributes each row of the build table join hash table 173 to multiple nodes based on the hash value of each row using the inter-node communication program 140 (S211). Here, the build program 110 distributes the rows so that the number of rows in the build table join hash table 173 managed by each node is equal. In this embodiment, the build program 110 divides the possible range of join hash values ​​by the number of nodes to which rows are distributed, so that the range of join hash values ​​(management range) handled by each node is equal.

[0075] Next, the storage-node communication program 190 of each node reads the probe table data within the range (rows and items) notified in step S204 from the object storage 30 (S212), and stores the data within the range read by the table storage program 171 in the table management area 170 as a probe table (part of the probe table) (S213).

[0076] Next, the integrated Bloom filter control program 160 of each node executes a probe table required range determination process (see FIG. 14) that determines the required range of the probe table based on the integrated Bloom filter 161 (S214). This probe table required range determination process removes rows in the probe table that do not have a corresponding row in the build table from the probe table stored in each node, thereby appropriately preventing information on rows in the probe table that do not satisfy the query conditions from being sent to other nodes.

[0077] Next, the probe program 120 of each node calculates a join hash value by performing a join hash calculation on each row of the probe table, and creates a probe table join hash table 174 (S215).

[0078] Next, the probe program 120 of each node distributes each row of the probe table join hash table 174 to a plurality of nodes based on the join hash value of each row using the inter-node communication program 140 (S216).

[0079] Next, the probe program 120 of each node executes a matching process (see FIG. 15) that matches each row of the probe table join hash table 174 with the build table join hash table 173 (S217).

[0080] Next, the probe program 120 of each node sends the matching results from the matching process, i.e., a combined table containing one or more rows combining items in the build table that are determined to correspond through the matching with items in the probe table, to the main node 10 via the inter-node communication program 140 (S218).

[0081] Next, the probe program 120 of the main node 10 integrates the matching results (joining tables) of each node (multiple nodes) including itself to create an integrated result (joining table 175) (S219), returns the joining table 175 to the user terminal that requested the query (S220), and terminates the processing.

[0082] Next, the build table / probe table determination process (S202) will be described.

[0083] FIG. 12 is a flowchart of the build table / probe table determination process according to the first embodiment.

[0084] The main node query control program 130 references the table statistical information 180, acquires table information for the two tables that are the target of the query (S301), and determines the table with the smaller table size of the two tables as the build table, and the table with the larger table size as the probe table (S302). For example, if the query targets two tables, Order and Lineitem, as shown in Figure 2, and the table statistical information 180 is in the state shown in Figure 6, Order is determined to be the build table, and Lineitem is determined to be the probe table.

[0085] Next, the main node query control program 130 returns information on the table to be used as the build table and the table to be used as the probe table to the hash join request processing (S303).

[0086] Next, the partial Bloom filter creation process (S207) will be described.

[0087] 13 is a flowchart of the partial Bloom filter creation process according to the first embodiment. The partial Bloom filter creation process is executed in each node.

[0088] The partial Bloom filter control program 150 extracts the join key of each row of the build table stored in the own node (S401).

[0089] Next, the partial Bloom filter control program 150 calculates a Bloom filter hash value by performing a Bloom filter hash calculation on each of the extracted combined keys (S402).

[0090] Next, the partial Bloom filter control program 150 creates the partial Bloom filter 151 based on the calculated Bloom filter hash value (S403).

[0091] Next, the probe table necessary range determination process (S214) will be described.

[0092] FIG. 14 is a flowchart of the probe table necessary range determination process according to the first embodiment.

[0093] The integrated Bloom filter control program 160 determines whether there are any unprocessed rows in the probe table (the probe table stored in its own node) (S501), and if there are any unprocessed rows (S501: YES), it refers to one unprocessed row in the probe table (referred to as the target row in this process) (S502).

[0094] Next, the integrated Bloom filter control program 160 determines whether or not there is a row in the build table that corresponds to the target row, based on the integrated Bloom filter 161 (S503). Specifically, the integrated Bloom filter control program 160 performs a Bloom filter hash calculation on the join key of the target row to calculate a Bloom filter hash value (first type hash value, second table hash value), and determines whether or not there is a corresponding row in the build table based on whether or not the flag in the integrated Bloom filter 161 that corresponds to the calculated Bloom filter hash value is 1. In this embodiment, the Bloom filter hash calculation is made lighter processing than the join hash calculation, so it is possible to lighten the determination process of determining whether or not there is a build table row that corresponds to the target row.

[0095] As a result, if it is determined that there is a row in the build table corresponding to the target row (S503: YES), the integrated bloom filter control program 160 leaves the target row in the probe table (S504) and proceeds to step S501, whereas if it is determined that there is no row in the build table corresponding to the target row (S503: NO), the integrated bloom filter control program 160 deletes the target row from the probe table (S505) and proceeds to step S501.

[0096] On the other hand, in step S501, if it is determined that there are no unprocessed rows in the probe table (S501: NO), the integrated Bloom filter control program 160 ends the probe table necessary range determination process.

[0097] This probe table required range determination process allows rows that do not have a corresponding row in the build table, i.e., rows that are not relevant to the query, to be deleted from the probe table stored in the node, and these rows do not need to be sent to other nodes.

[0098] Next, the matching process (S217) will be described.

[0099] FIG. 15 is a flowchart of the matching process according to the first embodiment.

[0100] The probe program 120 determines whether there are any unprocessed rows in the hash table for probe table join (S601), and if there are any unprocessed rows (S601: YES), it references the one unprocessed row in the hash table for probe table join and the one row in the corresponding hash table for build table join (S602), and creates one row in the joined table by combining the item of the target row in the hash table for probe table join with the item of the one row in the corresponding hash table for build table join according to the conditions specified in the query (S603), and proceeds to step S601.

[0101] On the other hand, if there are no unprocessed rows (S601: NO), the probe program 120 returns the join result to the hash join request processing (S604) and ends the processing.

[0102] Next, a computer system according to the second embodiment will be described. The computer system according to the second embodiment will be described with reference to Fig. 1, and functional parts similar to those in the computer system according to the first embodiment will be described using the same reference numerals.

[0103] In the computer system 1 according to the second embodiment, the integrated Bloom filter control program 160 further stores Bloom filters 162 for each of a plurality of nodes, and has functions different from those of the integrated Bloom filter control program of the computer system according to the first embodiment. The different functions of the integrated Bloom filter control program will be described later.

[0104] FIG. 16 is a configuration diagram of a Bloom filter for each node according to the second embodiment.

[0105] The per-node Bloom filter 162 (162-1, 162-2, 162-3) is a filter for determining whether a row in the build table corresponding to the value of a target item in a row of the probe table exists in the target node, and includes a hash value row 162a containing join hash values ​​(second type hash values) within the range that can be obtained by a join hash calculation for the value of a specified target item (target item) in the row of the build table, and a bit row 162b indicating whether a row exists in the build table of the target node that contains the value of the item that is the respective value of the hash value row 162a. In the example of Figure 16, the per-node Bloom filter 162-1 corresponding to node 1 (main node 10) stores in the hash value row 162a the range of join hash values ​​managed by node 1, which is 1 to 150, the per-node Bloom filter 162-2 corresponding to node 2 (one of the secondary nodes 20) stores in the hash value row 162a the range of join hash values ​​managed by node 2, which is 151 to 300, the per-node Bloom filter 162-3 corresponding to node 3 (a secondary node 20 different from node 2) stores in the hash value row 162a the range of join hash values ​​managed by node 3, which is 301 to 450.

[0106] For example, the integrated Bloom filter 161 uses a Bloom filter hash value to determine whether a corresponding build table row exists. However, in this embodiment, the Bloom filter hash value has a narrower range of possible values ​​than the join hash value, increasing the likelihood of hash collisions. Therefore, even if the integrated Bloom filter 161 determines that a corresponding build table row exists, this may result in a false positive, indicating that a corresponding build table does not actually exist. In contrast, the per-node Bloom filter 162 can more accurately determine whether a build table row corresponding to the target node exists.

[0107] Next, the processing operation of the computing system according to the second embodiment will be described.

[0108] 17 is a flowchart of hash join request processing according to the second embodiment. Note that the same processing steps as those in the hash join request processing according to the first embodiment shown in FIG. 11 are denoted by the same reference numerals.

[0109] In step S701, the build program 110 distributes each row of the build table join hash table 173 to multiple nodes using the inter-node communication program 140 based on the hash value of each row, and the build program 110 of each node creates a build table join hash table 173. Next, the integrated Bloom filter control program 160 creates a per-node Bloom filter 162 corresponding to that node based on the build table join hash table 173, and transmits the per-node Bloom filter 162 to other nodes.

[0110] In step S702, the integrated Bloom filter control program 160 executes a node-by-node Bloom filter application process (see FIG. 18 ) that applies a node-by-node Bloom filter 162 to each row of the probe table and narrows down the rows to those for which a row in the build table corresponding to the node exists.

[0111] Next, the Bloom filter application process for each node (S702) will be described.

[0112] FIG. 18 is a flowchart of the Bloom filter application process for each node according to the second embodiment.

[0113] The integrated Bloom filter control program 160 determines whether there are any unprocessed rows in the probe table (S801), and if there are any unprocessed rows (S801: YES), it refers to one unprocessed row in the probe table (referred to as the target row in this process) (S802).

[0114] Next, the integrated Bloom filter control program 160 uses the probe program 120 to perform a join hash calculation on the join key of the target row to calculate a join hash value (S803), and determines the Bloom filter 162 for each node to use based on the calculated join hash value (S804).

[0115] Next, the integrated Bloom filter control program 160 determines whether or not there is a row in the build table that corresponds to the target row based on the determined per-node Bloom filter (S805).

[0116] As a result, if it is determined that there is a row in the build table corresponding to the target row (S805: YES), the integrated bloom filter control program 160 leaves the target row in the probe table (S806) and proceeds to step S801, whereas if it is determined that there is no row in the build table corresponding to the target row (S805: NO), the integrated bloom filter control program 160 deletes the target row from the probe table (S807) and proceeds to step S801.

[0117] On the other hand, if it is determined in step S801 that there are no unprocessed rows in the probe table (S801: NO), the integrated Bloom filter control program 160 ends the Bloom filter application process for each node.

[0118] According to this node-by-node Bloom filter application process, for rows that have been determined by the integrated Bloom filter 161 to have a corresponding build table row and remain in the probe table, a more detailed determination is made as to whether or not the corresponding build table row exists in a specific node, thereby narrowing down the probe table to only those rows for which the corresponding build table row exists in a specific node. This makes it possible to prevent unnecessary probe table rows, i.e., rows for which there is no corresponding build table row, from being transferred to other nodes, thereby more effectively preventing unnecessary communication between nodes.

[0119] Next, a specific example of the Bloom filter application process for each node (S702) will be described.

[0120] FIG. 19 is a diagram illustrating a specific example of the Bloom filter application process for each node according to the second embodiment.

[0121] Here, it is assumed that each node manages a per-node Bloom filter 162-1 for node 1, a per-node Bloom filter 162-2 for node 2, and a per-node Bloom filter 162-3 for node 3.

[0122] For example, if in step S803 the join hash value for a row in the probe table is calculated to be 55, then in step S804 the node-specific Bloom filter 162-1 for node 1 that includes the calculated join hash value 55 in its management range is selected and used.

[0123] The present invention is not limited to the above-described embodiment, and can be modified appropriately without departing from the spirit of the present invention.

[0124] For example, in the above embodiment, one of the plurality of nodes is set as the main node 10, but any one of the plurality of nodes may be dynamically set as the main node.

[0125] Furthermore, in the above embodiment, a per-node Bloom filter 162 corresponding to the node is created based on the build table join hash table 173, and the per-node Bloom filter 162 is sent to other nodes, so that each node stores per-node Bloom filters 162 for all nodes. However, the present invention is not limited to this. For example, when each node distributes each row of the build table join hash table 173 to multiple nodes based on the hash value of each row, it may send information to each node indicating which node manages the hash value of each row, and each node may create a per-node Bloom filter 162 for each node based on this information. [Explanation of symbols]

[0126] 1...computer system, 10...main node, 11...memory, 12...CPU, 20...sub-node, 21...memory, 22...CPU, 30...object storage, 100, 200...DBMS, 110...build program, 120...probe program, 130...main node query control program, 140...inter-node communication program, 150...partial bloom filter control program, 160...integrated bloom filter control program, 170...table management area, 171...table storage program, 172...table storage information, 180...table statistical information, 190...storage-to-node communication program

Claims

1. A computer system having a storage for storing a plurality of tables and a plurality of nodes for processing queries relating to the plurality of tables, the query is a query that joins fields of multiple tables according to a predetermined join key value; The processor of each node of the plurality of nodes creating a Bloom filter that manages information on a first table hash value, which is a first type hash value corresponding to a join key of a row that satisfies the query condition among rows assigned to the node in the first table that is the smallest of the tables to be joined; Integrating the plurality of Bloom filters created by the plurality of nodes to create an integrated Bloom filter that manages information on the hash values ​​of the first table corresponding to join keys of rows that satisfy the query conditions among all rows of the first table; calculating a second table hash value, which is the first type hash value corresponding to a join key of a row that satisfies the query condition among rows of a second table that is different from the first table to be joined; if the integrated Bloom filter contains the first table hash value that is identical to the calculated second table hash value, identify a node among the plurality of nodes that stores the row of the first table corresponding to the second type hash value based on the second type hash value of the row data of the second table, and transmit the row data of the second table to the node; when receiving data of a row of the second table from another node, creating a joined table by joining a field specified in the query of the row of the second table with a field specified in the query of the row of the first table that corresponds to the same second-type hash value as the second-type hash value of the row of the second table; A processor of any one of the plurality of nodes Integrates the multiple join tables created at each node and returns the combined table to the requester of the query. The first type hash value is a hash value that requires a smaller processing load when calculated than the second type hash value and has a narrower range of possible values. Computer system.

2. The processor: A second type hash value of the join key of the row of the first table assigned to the node is calculated, and the data of the row of the first table assigned to the node is distributed to each node based on the calculated second type hash value.

2. The computer system of claim 1.

3. a management range is determined for each of the plurality of nodes, the range being a range of the second type hash values ​​of join keys of rows in the first table that are managed by each node; The processor allocates the row data of the first table to each node based on the management range.

3. The computer system according to claim 2.

4. Each of the processors creating a Bloom filter for each node that manages the second type hash value of the join key of the row of the first table managed in each node; If the integrated Bloom filter contains the first table hash value that is identical to the calculated second table hash value, identify a node among the plurality of nodes that stores a row of the first table that corresponds to the second type of hash value that is identical to the second type of hash value, based on the second type of hash value of data of the row of the second table, determine whether or not a per-node Bloom filter corresponding to the identified node includes a row of the first table that corresponds to the second type of hash value that is identical to the second type of hash value of data of the row of the second table, and transmit the row data of the second table to the node if the per-node Bloom filter includes a row of the first table that corresponds to the second type of hash value that is identical to the second type of hash value of data of the row of the second table, and do not transmit the row data of the second table to the node if the per-node Bloom filter does not include a row of the first table that corresponds to the second type of hash value that is identical to the second type of hash value of data of the row of the second table.

2. The computer system of claim 1.

5. The query includes selection conditions for the items in the rows of the first table.

2. The computer system of claim 1.

6. 1. A query processing method for a computer system having a storage for storing a plurality of tables and a plurality of nodes for processing queries relating to the plurality of tables, comprising: the query is a query that joins fields of multiple tables according to a predetermined join key value; The computer system creating a Bloom filter that manages information on a first table hash value, which is a first type hash value corresponding to a join key of a row that satisfies the query condition among rows assigned to the node in the first table that is the smallest of the tables to be joined; Integrating the plurality of Bloom filters created by the plurality of nodes to create an integrated Bloom filter that manages information on the hash values ​​of the first table corresponding to join keys of rows that satisfy the query conditions among all rows of the first table; calculating a second table hash value, which is the first type hash value corresponding to a join key of a row that satisfies the query condition among rows of a second table that is different from the first table to be joined; if the integrated Bloom filter contains the first table hash value that is identical to the calculated second table hash value, identify a node among the plurality of nodes that stores the row of the first table corresponding to the second type hash value based on the second type hash value of the row data of the second table, and transmit the row data of the second table to the node; when receiving data of a row of the second table from another node, creating a joined table by joining a field specified in the query of the row of the second table with a field specified in the query of the row of the first table that corresponds to the same second-type hash value as the second-type hash value of the row of the second table; Integrates the multiple join tables created at each node and returns the combined table to the requester of the query. The first type hash value is a hash value that requires a smaller processing load when calculated than the second type hash value and has a narrower range of possible values. Query processing methods.

Images