A method and apparatus for performing a connection operation, a storage medium, and an electronic device

Abstract

The embodiment of the present specification discloses a method for executing a connection operation. When the data in each to-be-connected data table is sequentially stored according to a connection key, the data in each to-be-connected data table is sequentially and batched acquired according to the storage order of the data in each to-be-connected data table, the batch of data in the to-be-connected data table acquired each time is stored in the storage area corresponding to the to-be-connected data table, and the operable connection key is determined according to the connection key and the data storage order of all the data currently stored in the storage area corresponding to each to-be-connected data table. The connection operation is performed on all the data corresponding to the operable connection key stored in the storage area corresponding to each to-be-connected data table. Through the above method, the acquired data can be connected in a streaming manner while the data in each to-be-connected data table is acquired in a streaming manner, and the execution efficiency of the connection operation can be effectively improved.

Description

Technical Field

[0001] This specification relates to the field of computer technology, and in particular to a method, apparatus, storage medium, and electronic device for performing connection operations. Background Technology

[0002] With the development of the times, database systems are widely used in various fields. Using SQL statements, you can query the required data in the database instance stored in the database system, and the join operation is one of the common operations when querying data using SQL statements.

[0003] In a database instance, data is often managed in the form of tables. A join operation is the operation that combines two different tables. For example, a database instance may have a user table and an order table. The user table stores user ID and user name, while the order table stores user ID, order ID, order amount, and order date. These two tables can be joined together using the JOIN clause in an SQL statement, with the condition that the user ID in the user table is equal to the user ID in the order table. The resulting new table will contain the user ID, user name, order ID, order amount, and order date.

[0004] For the above join operations, how to efficiently join these tables when there is a large amount of data in each table being joined is an urgent problem to be solved. Summary of the Invention

[0005] This specification provides a method, apparatus, storage medium, and electronic device for performing connection operations, in order to partially solve the problems existing in the prior art.

[0006] The embodiments in this specification adopt the following technical solutions:

[0007] This specification provides a method for performing a join operation, wherein the data in each table to be joined is stored in order according to the join key; the method includes:

[0008] For each table to be joined, retrieve the data from the table in batches according to the storage order of the data in the table;

[0009] For each batch of data obtained from the data table to be joined, the batch of data obtained is stored in the storage area corresponding to the data table to be joined, and the connection key of all data stored in the storage area corresponding to the data table to be joined is determined as the current connection key of the data table to be joined.

[0010] The operable connection keys are determined based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected.

[0011] For each operable join key, perform a join operation on all data corresponding to that operable join key stored in the storage area corresponding to each data table to be joined.

[0012] This specification provides an execution device for a connection operation, wherein data in each table to be connected is stored in an ordered manner according to the connection key; the device includes:

[0013] The streaming table scanning module is used to sequentially and in batches retrieve data from each table to be connected, according to the storage order of the data in the table.

[0014] The caching module is used to store each batch of data obtained from the data table to be connected in the current storage area of ​​the data table to be connected, and to determine the connection key of all data stored in the storage area of ​​the data table to be connected, which is used as the current connection key of the data table to be connected.

[0015] The operable range determination module is used to determine the operable connection key based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected.

[0016] The connection module is used to perform a connection operation on all data corresponding to each operable connection key stored in the storage area corresponding to each data table to be connected, for each operable connection key.

[0017] This specification provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described method for performing the connection operation.

[0018] This specification provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described connection operation execution method.

[0019] This specification provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described connection operation execution method.

[0020] The above-described at least one technical solution adopted in the embodiments of this specification can achieve the following beneficial effects:

[0021] This specification discloses a method for performing join operations. When the data in each table to be joined is stored in order according to the join key, the method sequentially and in batches retrieves the data from each table according to the storage order of the data. Each batch of data retrieved from the tables to be joined is stored in the corresponding storage area of ​​that table. Based on the join keys and data storage order of all data currently stored in the storage area of ​​each table, operable join keys are determined. Join operations are then performed on all data corresponding to the operable join keys stored in the storage area of ​​each table. This method allows for simultaneous streaming retrieval of data from each table to be joined and streaming join operations on the retrieved data, effectively improving the efficiency of join operations. Attached Figure Description

[0022] The accompanying drawings, which are included to provide a further understanding of this specification and form part of this specification, illustrate exemplary embodiments and are used to explain this specification, but do not constitute an undue limitation thereof. In the drawings:

[0023] Figure 1 This is a flowchart illustrating the execution method of the connection operation provided in the embodiments of this specification;

[0024] Figure 2 A schematic diagram of an execution device for a connection operation provided in an embodiment of this specification;

[0025] Figure 3 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this specification. Detailed Implementation

[0026] To make the objectives, technical solutions, and advantages of this specification clearer, the technical solutions of this specification will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this specification, and not all of them. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this specification.

[0027] The technical solutions provided in the various embodiments of this specification are described in detail below with reference to the accompanying drawings.

[0028] Figure 1 The flowchart of the connection operation execution method provided in the embodiments of this specification specifically includes the following steps:

[0029] S100: For each data table to be connected, retrieve the data from the data table in batches according to the storage order of the data in the data table to be connected.

[0030] A join operation is an operation that combines two different data tables together. For example, suppose a database instance includes two data tables as shown in Table 1 and Table 2.

[0031]

[0032] Table 1

[0033]

[0034] Table 2

[0035] Table 1 is the user table, containing two columns: User ID and User Name, referred to as Table C. Table 2 is the order table, containing four columns: Order ID, User ID, Order Amount, and Order Date, referred to as Table O.

[0036] Let's assume the SQL statement for querying data in this database instance is:

[0037] SELECT

[0038] c.CustomerName,

[0039] o.OrderID,

[0040] o.OrderAmount,

[0041] o.OrderDate

[0042] FROM Customers c

[0043] INNER JOIN Orders o ON c.CustomerID = o.CustomerID

[0044] This SQL statement means joining tables c and o based on the condition that the user IDs are equal, and querying the new table after the join to get the user name, order ID, order amount, and order date. The query results are shown in Table 3.

[0045]

[0046] Table 3

[0047] Inner join is a type of join operation that combines two tables and outputs all data that meets the join condition. For example, in the example above, there is no data in table o with the same user ID of 4, so the data in table c with user ID 4 is not output. Similarly, there is no data in table c with user ID 5, so it is also not output. Therefore, only the data shown in Table 3 is output. The key used as the join condition in this SQL statement (i.e., c.CustomerID = o.CustomerID in the SQL statement above) is the join key; in the example above, the join key is the user ID.

[0048] In addition to the inner join mentioned above, join operations also include left join, right join, and full join. In a given SQL statement, a join clause can only connect two tables: a left table and a right table. In the example above, the user table (Customersc, or table c) following the FROM clause is the left table, and the order table (Orderso, or table o) following the INNER JOIN clause is the right table.

[0049] The purpose of a left join is to output any data in the left table, regardless of whether there is data in the right table that can be joined with the left table data. If there is no data in the right table that can be joined with the left table data, the column values ​​in the right table data are filled with null values ​​(i.e., NULL) when outputting the left table data.

[0050] The purpose of a right join is to output any data in the right table, regardless of whether there is data in the left table that can be joined with the data in the right table. If there is no data in the left table that can be joined with the data in the right table, the column values ​​in the left table data are filled with null values ​​(i.e., NULL) when outputting the data in the right table.

[0051] The function of a full join is the combination of a left join and a right join. That is, it outputs all data from both the left and right tables. Data that cannot be joined will have its column values ​​in the other table filled with null values.

[0052] For example, if the join operation in the SQL statement above is not inner join but left join, the output result is as shown in Table 4.

[0053]

[0054] Table 4

[0055] In Table 4, although there is no data in table o that satisfies the join condition to join with the data of users C and D in table c, the data of users C and D is still output, but the column values ​​of these two data in table o are filled with NULL.

[0056] During the above join operation, if the data in the tables to be joined is relatively small, the database system's storage engine can retrieve all the data from these tables at once and return it to the computing engine cache. The computing engine can then perform join operations on all the data in the cached tables according to the join operation type. However, if the data in the tables to be joined is large, the storage engine needs to retrieve the data from each table in a streaming manner, returning each batch of data to the computing engine cache. If the computing engine needs to wait until all the data in each table is cached before performing the join operation, not only will the join operation be inefficient, but the cache pressure on the computing engine will also increase dramatically.

[0057] In view of this, embodiments of this specification provide a method for streaming join operations, which aims to improve the efficiency of join operations by having the computing engine perform join operations on the currently acquired data in a streaming manner while the storage engine acquires data from each data table to be joined in multiple batches in a streaming manner, without waiting to acquire all the data from each data table to be joined before performing the join operation.

[0058] When using the streaming join operation provided in this manual, the data in each table to be joined must be stored in order according to the join key. That is, in a database system using... Figure 1 Before performing a join operation, the method shown requires determining the join conditions and the tables to be joined based on the SQL statement used for data querying. The join key is then determined according to the join conditions. Finally, it's checked whether all tables are stored in order according to the join key. If so, the join operation can proceed. Figure 1 The method shown performs a join operation. The ordered storage described in the embodiments of this specification includes ordered storage by join key from smallest to largest, or ordered storage by join key from largest to smallest.

[0059] For ease of description, this manual will only use the example of storing data in ascending order by connecting the keys as an illustration.

[0060] For example, in the above example, based on the SQL statement, we can determine that tables c and o are the tables to be joined. Based on the join condition in the SQL statement, we can determine that the join key is user ID. The database system can then determine whether tables c and o are stored in ascending order of user ID. According to Table 1, we can determine that table c is stored in ascending order of user ID. According to Table 2, we can determine that table o is not stored in ascending order of user ID. However, if table o is stored in the order shown in Table 5, then we can determine that table o is stored in ascending order of user ID.

[0061]

[0062] Table 5

[0063] As shown in Table 5, the values ​​in the user ID column (the join key) are sorted in ascending order. Therefore, the database system can determine that table O is also stored in ascending order of user IDs, and can therefore adopt... Figure 1 The connection operation is performed using the method shown.

[0064] In Adoption Figure 1 In the streaming join operation method shown, the database system still needs to retrieve data from each table to be joined in batches, according to the storage order of the data in that table. Each batch of data retrieved is a portion of the data from that table.

[0065] S101: For each batch of data obtained from the data table to be joined, store the batch of data obtained in this time in the storage area corresponding to the data table to be joined, and determine the connection key of all data stored in the storage area corresponding to the data table to be joined, as the current connection key corresponding to the data table to be joined.

[0066] Each time the storage engine retrieves a batch of data from a table to be connected, it returns this batch of data to the computing engine, which then stores the data in the storage area corresponding to the table to be connected.

[0067] The computing engine has a corresponding storage area for each different data table to be joined. This storage area can be a cache area or a memory area. After the computing engine stores this batch of data in the storage area corresponding to the data table to be joined, since the data currently stored in the storage area corresponding to a data table to be joined has been updated, the computing engine needs to determine the join key of all the data stored in the storage area corresponding to that data table, and use this as the current join key for that data table.

[0068] Continuing with the previous example, suppose that for table c shown in Table 1, the storage engine retrieves the first two data points from Table 1 in a streaming manner, according to the storage order of the data in table c. These first two data points are used as the first batch of data retrieved from table c. That is, the retrieved data points are the two data points with user IDs 1 and 2. The storage engine then returns these two data points to the computing engine, which stores them in the storage area corresponding to table c, as shown in Table 6.

[0069]

[0070] Table 6

[0071] At this point, the data stored in the storage area corresponding to table c includes the data shown in Table 6. The connection key between these two data is the user ID of each data. Therefore, the current connection key for table c is 1 and 2.

[0072] Similarly, for table o shown in Table 5, the storage engine also uses a streaming approach, according to the storage order of the data in table o, to first obtain the first two data in Table 5 as the first batch of data obtained from table o. The storage engine then returns these two data to the computing engine, which stores these two data in the storage area corresponding to table o, as shown in Table 7.

[0073]

[0074] Table 7

[0075] At this point, the data stored in the storage area corresponding to table o includes the data shown in Table 7. The connection key between these two data is also the user ID of each data. Therefore, the current connection key for table o is 1.

[0076] S102: Determine the operable connection key based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected.

[0077] In this embodiment of the specification, since the data in each data table to be joined is stored in ascending order of the join key, and the storage engine retrieves the data in each data table in a streaming manner, it also retrieves the data in batches according to the storage order of the data in each data table to be joined. Therefore, in step S102, the calculation engine can determine the minimum current join key and the maximum current join key corresponding to each data table to be joined. Then, the minimum value among the maximum current join keys corresponding to each data table to be joined is determined as the specified join key. According to the ascending order of the join keys, the previous join key before the specified join key is determined as the endpoint join key. At the same time, the minimum value among the minimum current join keys corresponding to each data table to be joined is determined as the starting join key. Finally, the range of operable join keys formed from the above-mentioned starting join key to the above-mentioned endpoint join key is determined, and all join keys falling within the range of operable join keys are determined as operable join keys.

[0078] Continuing with the previous example, after determining that the current connection keys for table c are 1 and 2, and the current connection key for table o is 1 through step S102, the calculation engine can determine that the minimum current connection key for table c is 1, the maximum current connection key is 2, and the minimum and maximum current connection keys for table o are both 1.

[0079] Then, when determining the endpoint join key, since the maximum current join key corresponding to table c is 2 and the maximum current join key corresponding to table o is 1, the minimum of the two is 1. This means the specified join key is 1. Furthermore, following the ascending order of join keys, the previous join key preceding the specified join key is 0. Therefore, the calculation engine can determine the endpoint join key as 0. Simultaneously, when determining the starting join key, since the minimum current join key corresponding to table c is 1 and the minimum current join key corresponding to table o is 1, the minimum of the two is 1, therefore the starting join key is 1.

[0080] Therefore, the range of operable connection keys determined by the calculation engine at this time, from the starting connection key to the ending connection key, is from 1 to 0, that is, the range greater than or equal to 1 and less than or equal to 0. This range does not contain any connection keys. Therefore, at this time, there are no operable connection keys.

[0081] The reason for using the above method to determine the range of operable join keys is that the join operation is essentially a Cartesian join of the data in the left table and the right table with the same join key (for example, in the join results shown in Table 3 or Table 4, user A has 1 data in the left table and 2 data in the right table, so after the Cartesian join, there should be 1×2=2 data). Assuming that the data in each table to be joined is obtained sequentially according to the join key in ascending order, for a join key with a fixed value, we need to wait until all the data in the left and right tables with the join key of that fixed value has been obtained and stored in the corresponding storage area before we can perform the join. In short, in the example above, for table c (the left table), the maximum value of its current join key is already 2, indicating that all data with join keys less than 2 are already in the storage space corresponding to table c. For table o (the right table), the maximum value of its current join key is 1, meaning that it is not yet known whether the next batch of data stored in the storage space of table o will still contain data with a join key of 1. Only when data with a join key of 2 appears in the storage space corresponding to table o can it be proven that all data with join keys less than 2 in table o has been stored in the storage space corresponding to table o. Therefore, in the example above, a join operation cannot be performed on data with a join key of 1.

[0082] Continuing with the previous example, suppose that in step S100, the storage engine continues to retrieve data in a streaming manner. The second batch of data retrieved from table c only contains the third data shown in Table 1 (i.e., user C's data), and the second batch of data retrieved from table o also only contains the third data shown in Table 5 (i.e., the data with order ID 102). Then, the storage engine returns the second batch of data from table c to the calculation engine, causing the calculation engine to also store the second batch of data in the storage area corresponding to table c. Therefore, the storage area corresponding to table c will then store both the data shown in Table 6 and the data shown in Table 8.

[0083]

[0084] Table 8

[0085] At this point, in step S101, the current connection keys corresponding to table c are updated from 1 and 2 to 1, 2, and 3.

[0086] At the same time, the storage engine returns the data of the second batch of table o to the computing engine, so that the computing engine also stores the data of the second batch of table o in the corresponding storage area. Then, the storage area corresponding to table o will store both the data shown in Table 7 and the data shown in Table 9 below.

[0087]

[0088] Table 9

[0089] At this point, in step S101, the current connection key corresponding to table o is updated from 1 to 1 and 2.

[0090] In step S102, when determining the range of operable join keys, for the endpoint join key, the minimum value in the largest current join key corresponding to table c and table o becomes 2. That is, if the specified join key is 2, then the endpoint join key is the previous join key before the specified join key, which is 1. For the starting join key, the minimum value in the smallest current join key corresponding to table c and table o is still 1. Therefore, the range of operable join keys is from 1 to 1, meaning that only join key 1 is an operable join key. This also illustrates that for a join key with a fixed value of n, join key n is an operable join key only when data with a join key greater than n appears in the storage areas corresponding to the tables to be joined.

[0091] The above describes the method for determining the range of operable join keys under normal circumstances. When all the data in the table to be joined has been retrieved, the following special method for determining the range of operable join keys needs to be used to further determine the range of operable join keys.

[0092] Specifically, when a join clause in an SQL statement can only connect two tables (i.e., the left table and the right table), this specification refers to the table whose data is retrieved first as the first table and the table whose data is retrieved later as the second table. In step S100, after the database system's storage engine has retrieved the last batch of data from the first table, and in step S101, the database system's computation engine has also stored this last batch of data in the storage area corresponding to the first table, the largest current join key corresponding to the first table is the largest join key in this last batch of data, and it will not change subsequently. When executing step S102 to determine the range of operable join keys, the following two cases need to be discussed.

[0093] In the first scenario, if the storage engine retrieves the last batch of data from the second data table in step S100, and the calculation engine stores this last batch of data in the storage area corresponding to the second data table in step S101, and the maximum current join key of the second data table is not greater than the maximum current join key of the first data table, then the maximum current join key of the first data table is determined as the endpoint join key. Simply put, when all the data in the first data table has been retrieved, but the retrieval of data in the second data table is not yet complete, and during the period when the retrieval of data in the second data table is not complete and the maximum current join key of the second data table is not greater than the maximum current join key of the first data table, the normal method for determining the range of operable join keys is still used. However, if the retrieval of data in the second data table ends, and the maximum current join key of the second data table is still not greater than the maximum current join key of the first data table, then the endpoint join key of the operable join key range is directly determined as the maximum current join key of the first data table.

[0094] For example, suppose table c in the above example only contains the first two data entries shown in Table 1, and table o only contains the first three data entries shown in Table 5. After the storage engine retrieves the data shown in Table 6 from table c, all the data in table c has been retrieved. The computing engine stores Table 6 in the storage area corresponding to table c. At this point, the maximum current join key for table c is 2, and it will not change further. However, after the storage engine retrieves the data shown in Table 7 from table o, not all the data in table o has been retrieved, and the maximum current join key for table o is 1, which is not greater than the maximum current join key for table c (2). Therefore, the normal method for determining the range of operable join keys continues in this case.

[0095] After the storage engine retrieves the data shown in Table 9, all the data in Table O is retrieved. At this point, the maximum current join key corresponding to Table O is 2, which is still no greater than the maximum current join key corresponding to Table C of 2. Therefore, this belongs to the first case mentioned above, and the maximum current join key corresponding to Table C of 2 is directly determined as the end join key of the range of operable join keys at this time.

[0096] In the second scenario, if the maximum current join key corresponding to the second data table determined by the calculation engine in step S101 is greater than the maximum current join key corresponding to the first data table, and the storage engine has not yet obtained the last batch of data in the second data table in step S100, then the maximum current join key corresponding to the second data table is determined as the endpoint join key. Simply put, when all the data in the first data table has been retrieved, the retrieval of data in the second data table is not yet complete. During the period when the retrieval of data in the second data table is not yet complete and the maximum current join key corresponding to the second data table is not greater than the maximum current join key corresponding to the first data table, the normal method for determining the range of operable join keys is still used. However, if the maximum current join key corresponding to the second data table is already greater than the maximum current join key corresponding to the first data table, and the retrieval of data in the second data table is still not complete, then the endpoint join key of the operable join key range is directly determined as the maximum current join key corresponding to the second data table.

[0097] For example, suppose table c in the above example only contains the first two data entries shown in Table 1, and table o contains all the data shown in Table 5, plus other data with larger user IDs. After the storage engine retrieves the data shown in Table 6 from table c, all the data in table c has been retrieved. The computing engine stores Table 6 in the storage area corresponding to table c. At this point, the maximum current join key for table c is 2, and it will not change further. However, after the storage engine retrieves the data shown in Tables 7 and 9 from table o, not all the data in table o has been retrieved. Furthermore, the maximum current join key for table o is still 2, which is not greater than the maximum current join key of table c (2). Therefore, the normal method for determining the range of operable join keys is used in this case.

[0098] After the storage engine retrieves the data for user ID 5 from Table 5, it hasn't yet fully retrieved all the data from Table O. At this point, the maximum current join key for Table O is 5, which is greater than the maximum current join key for Table C (2). Therefore, this falls under the second scenario described above, and the maximum current join key for Table O (5) is directly determined as the endpoint join key within the range of operable join keys at this time. Subsequently, as the maximum current join key for Table O continues to increase, the computing engine will continue to directly determine the maximum current join key for Table O as the endpoint join key within the range of operable join keys at this time.

[0099] Once the range of operable connection keys is determined using the above method, all connection keys within that range can be identified as operable connection keys.

[0100] S103: For each operable join key, perform a join operation on all data corresponding to that operable join key stored in the storage area corresponding to each data table to be joined.

[0101] After determining all operable join keys through the above step S102, the calculation engine can perform the corresponding join operation on all data corresponding to the operable join key stored in the storage area of ​​each table to be joined, according to the type of join operation contained in the SQL statement (one of inner join, left join, right join, or full join).

[0102] Using the above method, while the database system's storage engine continuously retrieves each data table to be joined in batches in a streaming manner through step S100, the database system's computing engine also continuously performs join operations on each batch of stored data through steps S101 to S103. In other words, in this specification... Figure 1 Steps S100 and S101~S103 shown are executed synchronously. There is no need to wait for the storage engine to retrieve all the data in each data table to be connected before the computing engine performs the connection operation, which can effectively improve the efficiency of the connection operation.

[0103] Regarding the storage pressure on the storage areas corresponding to the data tables to be connected in the computing engine, in this embodiment of the specification, the data in the storage area that has undergone the connection operation can be cleared after the connection operation is performed on the data corresponding to a certain operable connection key.

[0104] Specifically, for any operable join key, after all the data corresponding to the operable join key stored in the storage area corresponding to each data table to be joined has been joined, the operable join key can be marked as an operable join key, and the data corresponding to the operable join key stored in the storage area corresponding to each data table to be joined can be cleared.

[0105] In step S100, when the storage engine retrieves data from a table to be joined in batches using a streaming method, each batch of data retrieved can be considered a data block. Therefore, in step S101, when the computing engine stores the batch of data retrieved by the storage engine in the storage area corresponding to the table to be joined, it can also store that batch of data retrieved by the storage engine as a data block in the storage area corresponding to the table to be joined. In other words, the storage area corresponding to a table to be joined stores data in units of data blocks. For example, in the example above, for table c as shown in Table 1, the first batch of data retrieved by the storage engine is the data shown in Table 6, and the computing engine stores Table 6 as a data block in the storage space corresponding to table c. The second batch of data retrieved by the storage engine is the data shown in Table 8, and the computing engine stores Table 8 as another data block in the storage space corresponding to table c. Similarly, for table o as shown in Table 5, the first batch of data obtained by the storage engine is the data shown in Table 7, and the computing engine stores Table 7 as a data block in the storage space corresponding to table o. The second batch of data obtained by the storage engine is the data shown in Table 9, and the computing engine stores Table 9 as another data block in the storage space corresponding to table o.

[0106] Therefore, when clearing data corresponding to the operated join keys stored in the storage area corresponding to the data tables to be joined, the computing engine can determine whether there are data blocks in the storage space corresponding to each data table that meet the first specified condition. Data blocks that meet the first specified condition include data blocks in which all data corresponds to the operated join keys. If data blocks that meet the first specified condition exist, then these data blocks are cleared from the storage area corresponding to the data table to be joined. That is, this specification describes the clearing process on a data block basis.

[0107] Furthermore, in step S103, when the computing engine performs join operations on the data corresponding to each operable join key, it can still perform join operations on all the data corresponding to that operable join key stored in the storage area corresponding to each data table to be joined, in ascending order of operable join keys. For example, assuming that the range of operable join keys determined in step S102 is from 1 to 3, then the operable join keys falling into this range are 1, 2, and 3. In step S103, the computing engine can first perform join operations on all the data corresponding to operable join key 1 stored in the storage area corresponding to each data table to be joined, then perform join operations on all the data corresponding to operable join key 2 stored in the storage area corresponding to each data table to be joined, and so on, in ascending order of operable join keys.

[0108] When performing join operations on the data corresponding to each operable join key in an orderly manner according to the above method, the computing engine can remove the operable join key from the operable join key range determined in step S102 after performing join operations on all data corresponding to an operable join key, so as to update the operable join key range, that is, update the starting join key of the operable join key range. Then, based on the updated operable join key range, the data to be cleared stored in the storage area corresponding to each data table to be joined is determined, and the data to be cleared is cleared.

[0109] Specifically, when the computing engine stores data in storage areas in units of data blocks, it determines the data to be cleared stored in the storage areas corresponding to each table to be joined, based on the updated range of operable join keys. The method for clearing this data can be as follows: For each table to be joined, if a data block satisfying a second specified condition exists in the storage space corresponding to that table, all data in that data block is identified as data to be cleared, and the data block satisfying the second specified condition is cleared. The data block satisfying the second specified condition includes data blocks whose largest join key is less than the starting join key of the operable join key range. In other words, if the largest join key of the data in a data block is less than the starting join key of the current operable join key range, then all data in that data block is data to be cleared, and the entire data block can be treated as a data block to be cleared without marking the already operated join keys.

[0110] Whether using the method of marking the operated join key to clear the data blocks already stored in the computing engine's storage area, or using the method of updating the starting join key of the operable join key range to clear the data blocks already stored in the computing engine's storage area, both methods allow the storage engine to continuously and batch-follow data from each table to be joined in a streaming manner, while the computing engine follows... Figure 1 In steps S101 to S103, the batches of data stored in the storage area are continuously connected in a streaming manner. At the same time, the computing engine is also constantly clearing the data in the storage area that has already undergone connection operations. This can improve the efficiency of performing connection operations while minimizing the required storage costs.

[0111] The above is a method for performing a connection operation provided by an embodiment of this specification. Based on the same idea, this specification also provides corresponding devices, storage media, and electronic devices.

[0112] Figure 2 This is a schematic diagram of an execution device for a connection operation provided in an embodiment of this specification. Data in each data table to be connected is stored in an ordered manner according to the connection key. The device includes:

[0113] The streaming table scanning module 201 is used to sequentially and in batches retrieve data from each data table to be connected, according to the storage order of the data in the data table to be connected.

[0114] The caching module 202 is used to store the batch of data obtained each time in the data table to be connected in the storage area corresponding to the data table to be connected, and to determine the connection key of all data stored in the storage area corresponding to the data table to be connected, as the current connection key corresponding to the data table to be connected.

[0115] The operable range determination module 203 is used to determine the operable connection key based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected.

[0116] The connection module 204 is used to perform a connection operation on all data corresponding to the operable connection key stored in the storage area corresponding to each operable connection key for each operable connection key.

[0117] Optionally, the device further includes:

[0118] The clearing module 205, after performing a connection operation, sets the operable connection key as the already operated connection key; and clears the data corresponding to the already operated connection key stored in the storage area corresponding to each data table to be connected.

[0119] Optionally, the caching module 202 is specifically used to store the batch of data obtained this time as a data block in the storage area corresponding to the data table to be connected;

[0120] The clearing module 205 is specifically used to, for each data table to be connected, when there are data blocks in the storage area corresponding to the data table to be connected that satisfy the first specified condition, clear the data blocks that satisfy the first specified condition from the storage area corresponding to the data table to be connected; wherein, the data blocks that satisfy the first specified condition include: data blocks in which all the data contained are connected keys that have been operated.

[0121] Optionally, the data in each table to be joined is stored in ascending order according to the join key;

[0122] The operable range determination module 203 is specifically used to: for each data table to be joined, determine the minimum current connection key and the maximum current connection key corresponding to that data table; determine the minimum value among the maximum current connection keys corresponding to each data table to be joined, as the specified connection key; determine the previous connection key before the specified connection key according to the ascending order of the connection keys, as the endpoint connection key; determine the minimum value among the minimum current connection keys corresponding to each data table to be joined, as the starting connection key; determine the operable connection key range formed from the starting connection key to the endpoint connection key, and determine all connection keys falling within the operable connection key range as operable connection keys.

[0123] Optionally, the data table to be connected includes a first data table and a second data table;

[0124] The operable range determination module 203 is further configured to, when the last batch of data in the first data table has been obtained and stored in the storage area corresponding to the first data table, before determining the operable connection key range formed from the starting connection key to the ending connection key, if the last batch of data in the second data table has been obtained and stored in the storage area corresponding to the second data table, and the maximum current connection key corresponding to the second data table is not greater than the maximum current connection key corresponding to the first data table, then the maximum current connection key corresponding to the first data table is determined as the ending connection key; if the maximum current connection key corresponding to the second data table is greater than the maximum current connection key corresponding to the first data table, and the last batch of data in the second data table has not yet been obtained, then the maximum current connection key corresponding to the second data table is determined as the ending connection key.

[0125] Optionally, the connection module 204 is specifically used to perform connection operations on all data corresponding to the operable connection key stored in the storage area corresponding to each operable connection key in ascending order of operable connection keys;

[0126] The device further includes:

[0127] The clearing module 205 is used to remove the operable connection key from the operable connection key range after performing a connection operation, so as to update the operable connection key range; and to determine the data to be cleared stored in the storage area corresponding to each data table to be connected according to the updated operable connection key range, and to clear the data to be cleared.

[0128] Optionally, the caching module 202 is specifically used to store the batch of data obtained this time as a data block in the storage area corresponding to the data table to be connected;

[0129] The clearing module 205 is specifically used for each data table to be connected. When there is a data block in the storage space corresponding to the data table to be connected that meets the second specified condition, all the data in the data block is identified as data to be cleared, and the data block that meets the second specified condition is cleared. The data block that meets the second specified condition includes: a data block in which the largest connection key of the connection keys corresponding to all the data contained is less than the starting connection key of the range of operable connection keys.

[0130] This specification also provides a computer-readable storage medium storing a computer program that, when executed by a processor, can be used to perform the execution method of the connection operation provided above.

[0131] This specification also provides a computer program product comprising a computer program that, when executed by a processor, implements the above-described method for performing the connection operation.

[0132] based on Figure 1 The embodiment of this specification also provides the method for performing the connection operation shown. Figure 3 The diagram shows the structure of the electronic device. Figure 3 At the hardware level, the electronic device includes a processor, internal bus, network interface, memory, and non-volatile memory, and may also include other hardware required for business operations. The processor reads the corresponding computer program from the non-volatile memory into memory and then runs it to implement the above-mentioned connection operation execution method.

[0133] The above description is merely an embodiment of this specification and is not intended to limit this specification. Various modifications and variations can be made to this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of the claims of this specification.

Claims

1. A method for performing a join operation, wherein data in each table to be joined is stored in order according to the join key; the method includes: For each table to be joined, retrieve the data from the table in batches according to the storage order of the data in the table; For each batch of data obtained from the data table to be joined, the batch of data obtained is stored in the storage area corresponding to the data table to be joined, and the connection key of all data stored in the storage area corresponding to the data table to be joined is determined as the current connection key for the data table to be joined. The operable connection keys are determined based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected. For each operable join key, perform a join operation on all data corresponding to that operable join key stored in the storage area corresponding to each data table to be joined.

2. The method of claim 1, further comprising: After the connection operation is performed, the operable connection key is set as the operated connection key; Clear the data stored in the storage area corresponding to the operation key of each table to be joined.

3. The method as described in claim 2, wherein the batch of data obtained is stored in the storage area corresponding to the data table to be connected, specifically includes: The batch of data obtained this time is stored as a data block in the storage area corresponding to the data table to be connected; Clear the data stored in the storage area corresponding to the operated join key for each table to be joined, specifically including: For each data table to be joined, if there is a data block in the storage area corresponding to the data table that meets the first specified condition, the data block that meets the first specified condition is cleared from the storage area corresponding to the data table to be joined; wherein, the data block that meets the first specified condition includes: data blocks in which all the data corresponds to the join key that has been operated.

4. As described in claim 1, the data in each data table to be joined are stored in ascending order according to the join key; Based on the current join key corresponding to each table to be joined and the storage order of the data in each table, the operable join keys are determined, specifically including: For each table to be joined, determine the minimum and maximum current join keys for that table. Determine the minimum value among the largest current join keys for each table to be joined, and use it as the specified join key. Based on the ascending order of join keys, determine the previous join key that precedes the specified join key, and use it as the endpoint join key. Then, determine the minimum value among the smallest current join keys for each table to be joined, and use it as the starting join key. Determine the range of operable connection keys from the starting connection key to the ending connection key, and determine all connection keys falling within the range of operable connection keys as operable connection keys.

5. The method as described in claim 4, wherein the data table to be connected includes a first data table and a second data table; When the last batch of data in the first data table has been obtained and stored in the storage area corresponding to the first data table, before determining the range of operable connection keys formed from the starting connection key to the ending connection key, the method further includes: If the last batch of data in the second data table is obtained and stored in the storage area corresponding to the second data table, and the maximum current connection key corresponding to the second data table is not greater than the maximum current connection key corresponding to the first data table, then the maximum current connection key corresponding to the first data table is determined as the endpoint connection key. If the maximum current join key corresponding to the second data table is greater than the maximum current join key corresponding to the first data table, and the last batch of data in the second data table has not yet been obtained, then the maximum current join key corresponding to the second data table will be determined as the endpoint join key.

6. The method as described in claim 4 or 5, wherein for each operable join key, a join operation is performed on all data corresponding to that operable join key stored in the storage area corresponding to each data table to be joined, specifically including: In ascending order of operable connection keys, for each operable connection key, perform connection operations on all data corresponding to that operable connection key stored in the storage area of ​​each data table to be connected. The method further includes: After performing the connection operation, the operable connection key is removed from the operable connection key range in order to update the operable connection key range; Based on the updated range of operable connection keys, determine the data to be cleared stored in the storage area corresponding to each data table to be connected, and clear the data to be cleared.

7. The method as described in claim 6, wherein the batch of data obtained is stored in the storage area corresponding to the data table to be connected, specifically includes: The batch of data obtained this time is stored as a data block in the storage area corresponding to the data table to be connected; Based on the updated range of operable connection keys, determine the data to be cleared stored in the storage area corresponding to each data table to be connected, and clear the data to be cleared, specifically including: For each data table to be joined, when there is a data block in the storage space corresponding to the data table that meets the second specified condition, all data in the data block is identified as data to be cleared, and the data block that meets the second specified condition is cleared; wherein, the data block that meets the second specified condition includes: data blocks in which the largest connection key of the connection keys corresponding to all the data contained is less than the starting connection key of the range of operable connection keys.

8. An execution device for a join operation, wherein data in each table to be joined is stored in an ordered manner according to a join key; the device comprises: The streaming table scanning module is used to sequentially and in batches retrieve data from each table to be connected, according to the storage order of the data in the table. The caching module is used to store each batch of data obtained from the data table to be connected in the storage area corresponding to the data table to be connected, and to determine the connection key of all data stored in the storage area corresponding to the data table to be connected, as the current connection key for the data table to be connected. The operable range determination module is used to determine the operable connection key based on the current connection key corresponding to each data table to be connected and the storage order of the data in each data table to be connected. The connection module is used to perform a connection operation on all data corresponding to each operable connection key stored in the storage area corresponding to each data table to be connected, for each operable connection key.

9. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the method described in any one of claims 1-7.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the method described in any one of claims 1-7.

11. A computer program product comprising a computer program that, when executed by a processor, implements the method described in any one of claims 1-7.

Images