A method and apparatus for updating wide tables
By monitoring database table update information and performing serialization processing, and using join operations to synchronize the update information to the wide table in real time, the problem of low update efficiency of wide tables is solved, and more efficient data synchronization and querying are achieved.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2022-12-02
- Publication Date
- 2026-03-13
AI Technical Summary
In existing technologies, when the results of multi-table joins are stored in a wide table, they cannot be updated synchronously in a timely manner, resulting in low update efficiency and poor real-time performance of the wide table.
By monitoring table update information in the database, using serialization and a preset wide table update model, the table update information is converted into a preset description format, and left outer join or inner join operations are performed to synchronize it to the wide table in real time.
It improves query efficiency, reduces database pressure, makes data display more real-time, and enhances the real-time performance and efficiency of wide table updates.
Smart Images
Figure CN116150167B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of Internet technology, and in particular to a method and device for updating wide tables. Background Technology
[0002] In the early stages of a business, when the data volume is small, we typically categorize and store business data in different tables. When the results require multi-table joins, we use SQL join operations. However, as the business grows and the data volume increases, the time required for each SQL join operation becomes longer. Currently, we usually store the results of multi-table joins in a new wide table, and the business only needs to query the wide table directly. However, when one of the multiple data tables is updated, the update cannot be synchronized to the wide table in a timely manner, resulting in poor real-time performance and low update efficiency for the wide table. Summary of the Invention
[0003] This application provides a method and apparatus for updating a wide table, which solves the problem that the table cannot be updated and synchronized to the wide table in a timely manner, resulting in poor real-time performance and low efficiency of wide table updates.
[0004] The embodiments of this application adopt the following technical solutions:
[0005] On one hand, this application provides a wide table update method, which includes: monitoring update information of a first table in a database; wherein the database includes multiple data tables corresponding to the wide table; serializing the update information of the first table to convert it into update information of a second table in a preset description format; processing the update information of the second table according to a preset wide table update model to obtain wide table update information; and updating the wide table according to the wide table update information.
[0006] In one example, the step of processing the update information of the second table according to a preset wide table update model to obtain the wide table update information specifically includes: retrieving the first data table corresponding to the update information of the second table in a pre-built backup library; updating the first data table according to the update information of the second table to obtain a first updated data table; determining the second data table to be joined with the first data table according to the join relationship of the wide table; and performing a left outer join operation between the first updated data table and the second data table to obtain the update information of the wide table.
[0007] In one example, the step of performing a left outer join operation between the first data update table and the second data table to obtain the update information of the wide table specifically includes: determining that the first data table is the main table and the second data table is the subordinate table; if the update information of the second table is inserting table data into a specified field of the first data table, then performing a left outer join operation between the update information of the second table and the second data table to obtain a first join query sub-table; inserting the first join query sub-table into the wide table is determined as the update information of the wide table.
[0008] In one example, the method further includes: if the update information of the second table is to delete table data in a specified field of the first data table, then performing a left outer join operation between the update information of the second table and the second data table to obtain a second join sub-table; deleting the second join sub-table from the wide table to determine the update information of the wide table.
[0009] In one example, the step of performing a left outer join operation between the first data update table and the second data table to obtain the update information of the wide table specifically includes: determining that the first data table is the subordinate table and the second data table is the primary table; if the update information of the second table is inserting table data in a specified field of the first data table, then performing an inner join operation between the update information of the second table and the second data table to obtain a third join query sub-table; determining the data in the second data table that can be associated with the update information of the second table; deleting the data in the wide table and then inserting it into the third join query sub-table to determine the update information of the wide table.
[0010] In one example, the method further includes: if the update information of the second table is to delete table data in a specified field of the first data table, then perform an inner join operation between the update information of the second table and the second data table to obtain a fourth join sub-table; determine the data in the second data table that can be associated with the update information of the second table; delete the fourth join sub-table in the wide table, and then insert the data to determine the update information of the wide table.
[0011] In one example, before serializing the update information of the first table and converting it into update information of the second table in a preset description format, the method further includes: obtaining the join query fields of the wide table; comparing the table fields of the update information of the first table with the join query fields of the wide table to determine that the update information of the first table is valid information.
[0012] In one example, the method further includes: if the update information of the second table consists of multiple update messages at different time points, then determining the update time of each update message of the second table; and processing each update message of the second table according to the order of the update times from earliest to latest, based on a preset wide table update model, to obtain the wide table update information.
[0013] In one example, the method further includes: monitoring the update information of a first table in the database via Flink CDC and pushing the update information of the first table to Fink; in Flink, serializing the update information of the first table and converting it into update information of a second table in a preset description format; and processing the update information of the second table according to a preset wide table update model to obtain wide table update information.
[0014] On the other hand, embodiments of this application provide a wide table update device, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: monitor first table update information in a database; wherein the database includes multiple data tables corresponding to the wide table; serialize the first table update information to convert it into second table update information in a preset description format; process the second table update information according to a preset wide table update model to obtain wide table update information; and update the wide table according to the wide table update information.
[0015] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects:
[0016] Given the ability to support multiple table joins, the system can monitor data table updates and use join operations to incrementally synchronize these updates to the wide table in real time. This advances the join operations to the time the data table is updated, allowing for direct querying of results during searches, thus improving query efficiency, reducing database load, and ensuring timely synchronization of update information to the wide table, resulting in more real-time data display and improved wide table update efficiency. Attached Figure Description
[0017] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which:
[0018] Figure 1 A flowchart illustrating a wide table update method provided in an embodiment of this application;
[0019] Figure 2A schematic diagram of the framework of a wide table update system provided in an embodiment of this application;
[0020] Figure 3 This is a schematic diagram of a wide table update device provided in an embodiment of this application. Detailed Implementation
[0021] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application 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 application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0022] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.
[0023] Figure 1 This is a flowchart illustrating a wide table update method provided in an embodiment of this application. This method can be applied to various business domains, such as internet finance, e-commerce, instant messaging, gaming, and government services. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.
[0024] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.
[0025] It should be noted that the server can be a single device or a system composed of multiple devices, i.e., a distributed server. This application does not make any specific limitations on this.
[0026] Figure 1 The process may include the following steps:
[0027] S101: Monitor the update information of the first table in the database; wherein, the database includes multiple data tables corresponding to the wide table.
[0028] Specifically, by using Flink CDC to monitor updates to multiple data tables, the underlying Flink CDC will disguise itself as a MySQL slave database, obtain the update information of the first table in the MySQL database, and push the update information of the first table to Flink as input data for Flink.
[0029] It's important to note that Flink is a distributed, open-source computing framework for both stream and batch data processing, supporting both application types. Using Flink, you can process and output input streaming data in real time.
[0030] S102: By serializing the update information of the first table, the update information of the first table is converted into update information of the second table in a preset description format.
[0031] In some embodiments of this application, table update information that is unrelated to the wide table exists, so the table update information is filtered by Flink nodes.
[0032] Based on this, the join query fields of the wide table are obtained, and the update information of the first table is compared with the join query fields of the wide table to determine whether the update information of the first table is valid. Specifically, if the table field corresponding to the update information of the first table matches the join query field of the wide table, it is considered that the update information of the first table is valid; otherwise, the update information of the first table is discarded.
[0033] Specifically, when converting the update information of the first table into the update information of the second table in a preset description format, the update information of the first table is first unified into insert data and delete data. For example, the update statement is split into one delete data and one insert data.
[0034] S103: Based on the preset wide table update model, process the update information of the second table to obtain the wide table update information.
[0035] In some embodiments of this application, if there are multiple updates to the second table, the update time of each update is determined. Flink processes each update according to its update time from earliest to latest, based on a preset wide table update model, to obtain the wide table update information. That is, the update information with the earliest update time is processed first.
[0036] Furthermore, when processing the update information of the second table, the first data table corresponding to the update information of the second table is first retrieved from the pre-built backup database. Based on the update information of the second table, the first data table is updated to obtain the first updated data table.
[0037] In other words, data updates are synchronized to the data tables in the backup database to achieve data persistence. Flink updates the data tables based on the update information of the first table it is currently processing. For example, if the update information for the second table is the insertion of data into table A and then the deletion of data, Flink will first process the inserted data in table A and then update the table A in the backup database with the updated data.
[0038] Then, based on the join relationships of the wide table, the second data table to be joined with the first data table is determined. Finally, a left outer join operation is performed between the first updated data table and the second data table to obtain the update information of the wide table.
[0039] In some embodiments of this application, a wide table update method is determined using set theory. Based on this, the update information of the wide table is obtained by performing a left outer join operation between the first data update table and the second data table, specifically including the following cases:
[0040] When the first data table is the master table and the second data table is the slave table, if the update information of the second table is a specified field of the first data table and the data is inserted into the table, then the update information of the second table is joined with the second data table to obtain the first union query sub-table.
[0041] The first join sub-table will be inserted into the wide table to determine the update information for the wide table.
[0042] If the update information in the second table is a specified field in the first data table, and table data is deleted, then the update information in the second table is joined with the second data table to obtain the second join sub-table.
[0043] Finally, the second join query sub-table will be deleted from the wide table to determine the update information for the wide table.
[0044] When the first data table is the subordinate table and the second data table is the master table, if the update information of the second table is a specified field of the first data table and the data is inserted into the table, then the update information of the second table is joined with the second data table to obtain the third join query sub-table.
[0045] Then, identify the data in the second data table that can be associated with the update information in the second table.
[0046] Finally, data will be deleted from the wide table, and then inserted into the third join query sub-table to confirm the updated information for the wide table.
[0047] If the update information in the second table is that data in the specified field of the first data table has been deleted, then the update information in the second table is joined with the second data table to obtain the fourth join sub-table.
[0048] Then, identify the data in the second data table that can be associated with the update information in the second table;
[0049] Finally, the fourth join sub-table will be deleted from the wide table, and then data will be inserted to confirm the update information of the wide table.
[0050] In summary, Flink performs joins between the input update information and the relevant tables in the backup library to obtain the incremental update data of the wide table, and supports the association of multiple data tables.
[0051] For example, the reasoning process in set theory is as follows:
[0052] For a join of two single tables A and B, where table A is the master table and table B is the slave table, then define A... TB For data in table A that can be linked with table B, A FB This refers to data in table A that cannot be joined with table B, i.e., A TB ∪A FB =A, A TB ∩A FB =□
[0053] According to the definitions of left join and inner join, we know that:
[0054] A left join B=A inner join B+A FB
[0055] Let A+r be the table after multiple rows of data r are inserted into table A at the same point in time. We can then conclude that:
[0056] (A+r)left join B=(A+r)inner join B+(A+r) FB
[0057] =A inner join B+A FB +r inner join B+r FB
[0058] =A left join B+r left join B
[0059] Similarly, defining B+r as the table after multiple rows of data r are inserted into table B at the same point in time, we can see that:
[0060] A left join(B+r)=A inner join(B+r)+A F()
[0061] Where A F() =A FB -A Tr A Tr For the data in table A that can be associated with data r, we can obtain:
[0062] A left join(B+r)=A inner join(B+r)+A F()
[0063] =A inner join(B+r)+(A FB -A Tr )
[0064] =A inner join B+A inner join r+A FB -A Tr
[0065] =A left join B+A inner join rA Tr
[0066] Similarly, the operation performed when deleting data can be obtained as follows:
[0067] Let Ar be the table after multiple rows of data r are deleted from table A at the same point in time. Then:
[0068] (Ar)left join B=A left join Br left join B
[0069] Similarly, if we define Br as the table after deleting multiple rows of data r from table B at the same point in time, then:
[0070] A left join(Br)=Aleft join BA inner join r+A Tr
[0071] S104: Update the wide table according to the wide table update information.
[0072] That is, the wide table update is synchronized to the wide table database, and the wide table is updated.
[0073] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S104 will be described sequentially, but this does not mean that steps S101 to S104 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S104 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S104 can be appropriately adjusted according to actual needs.
[0074] pass Figure 1This method, based on set theory, supports multi-table joins and can monitor data table updates. It uses join operations to incrementally synchronize data table updates to the wide table in real time, advancing the join operations to the time the data table is updated. During searches, only the results need to be queried directly, improving query efficiency, reducing database pressure, and promptly synchronizing update information to the wide table when data is updated, making data display more real-time and improving the update efficiency of the wide table.
[0075] More intuitively, Figure 2 This is a schematic diagram of the framework of a wide table update system provided in an embodiment of this application.
[0076] exist Figure 2 The system includes the original database, the Flink CDC module, the Flink module, the backup database, and the single-table database (wide-table database).
[0077] This involves using Flink CDC to monitor update information from multiple tables, including update statements that represent field data insertion and deletion. The table update information is then pushed to Flink as input data. Flink splits the update statement into a deletion statement and an insertion statement, and sequentially synchronizes the deleted or inserted data to the corresponding tables in the backup database. Finally, the deleted or inserted data is joined with the corresponding tables to obtain the wide table update information.
[0078] Based on the same idea, some embodiments of this application also provide devices and non-volatile computer storage media corresponding to the above methods.
[0079] Figure 3 A schematic diagram of a wide table update device provided in this application embodiment includes:
[0080] At least one processor; and,
[0081] A memory communicatively connected to the at least one processor; wherein,
[0082] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:
[0083] Monitor the update information of the first table in the database; wherein, the database includes multiple data tables corresponding to the wide table;
[0084] By serializing the update information of the first table, the update information of the first table is converted into update information of the second table in a preset description format;
[0085] Based on the preset wide table update model, the update information of the second table is processed to obtain the wide table update information;
[0086] The wide table is updated based on the wide table update information.
[0087] Some embodiments of this application provide a wide table update non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:
[0088] Monitor the update information of the first table in the database; wherein, the database includes multiple data tables corresponding to the wide table;
[0089] By serializing the update information of the first table, the update information of the first table is converted into update information of the second table in a preset description format;
[0090] Based on the preset wide table update model, the update information of the second table is processed to obtain the wide table update information;
[0091] The wide table is updated based on the wide table update information.
[0092] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.
[0093] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.
[0094] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0095] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0096] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0097] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0098] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0099] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.
[0100] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.
[0101] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.
[0102] The above description is merely an embodiment of this application and is not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the technical principles of this application should fall within the protection scope of this application.
Claims
1. A method of wide table update, the method comprising: The method comprises: listening to first table update information in a database; wherein the database comprises multiple data tables corresponding to a wide table; serializing the first table update information to convert the first table update information into second table update information in a preset description format; processing the second table update information according to a preset wide table update model to obtain wide table update information; updating the wide table according to the wide table update information; the processing of the second table update information according to the preset wide table update model to obtain wide table update information specifically comprises: searching for a first data table corresponding to the second table update information in a backup library constructed in advance; updating the first data table according to the second table update information to obtain a first updated data table; determining a second data table for joint query with the first data table according to the joint query relationship of the wide table; performing a left outer join operation on the first updated data table and the second data table to obtain the wide table update information.
2. The method of claim 1, wherein, the left outer join operation on the first updated data table and the second data table to obtain the wide table update information specifically comprises: determining the first data table as a master table and the second data table as a slave table; if the second table update information is inserting table data in a specified field of the first data table, performing a left outer join operation on the second table update information and the second data table to obtain a first joint query subtable; inserting the first joint query subtable into the wide table to determine the wide table update information.
3. The method of claim 2, wherein, the method further comprises: if the second table update information is deleting table data in a specified field of the first data table, performing a left outer join operation on the second table update information and the second data table to obtain a second joint query subtable; deleting the second joint query subtable from the wide table to determine the wide table update information.
4. The method of claim 1, wherein, the left outer join operation on the first updated data table and the second data table to obtain the wide table update information specifically comprises: determining the first data table as a slave table and the second data table as a master table; if the second table update information is inserting table data in a specified field of the first data table, performing an inner join operation on the second table update information and the second data table to obtain a third joint query subtable; determining data in the second data table that can be associated with the second table update information; deleting the data from the wide table and inserting the third joint query subtable to determine the wide table update information.
5. The method of claim 4, wherein, the method further comprises: if the second table update information is deleting table data in a specified field of the first data table, performing an inner join operation on the second table update information and the second data table to obtain a fourth joint query subtable; determining data in the second data table that can be associated with the second table update information; deleting the fourth joint query subtable from the wide table and inserting the data to determine the wide table update information.
6. The method of claim 1, wherein, Before the first table update information is converted into the second table update information in the preset description format by serializing the first table update information, the method further comprises: obtaining a joint query field of the wide table; comparing a table field of the first table update information with the joint query field of the wide table to determine that the first table update information is valid information.
7. The method of claim 1, wherein, The method further comprises: if the second table update information is multiple pieces of update information at different time points, determining an update time of each piece of second table update information; processing each piece of second table update information according to a preset wide table update model in the order from early to late of the update time to obtain wide table update information.
8. The method of claim 1, wherein, The method further comprises: monitoring the first table update information in the database by Flink CDC and pushing the first table update information to Fink; in the Flink, converting the first table update information into the second table update information in the preset description format by serializing the first table update information; processing the second table update information according to a preset wide table update model to obtain wide table update information.
9. A wide table update device, characterized by, comprise: at least one processor; and a memory in communication connection with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the wide table update method in any one of claims 1-8.
Citation Information
Patent Citations
Index updating method and system
CN113468199A
Data updating method and device
CN113495895A