Data filling method and device, electronic equipment and computer readable medium

By obtaining data change messages and querying the target primary key in a wide table for incremental filling, combined with the half-finish search algorithm for full filling, the problems of low data filling efficiency and easy failure in the existing technology are solved, and efficient and stable data is achieved filling.

CN119005147APending Publication Date: 2024-11-22BEIJING JINGDONG YUANSHENG TECH CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411161776.1
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-22
Publication Date
2024-11-22

AI Technical Summary

Technical Problem

In the prior art, data filling efficiency is low and easy to fill failure, especially when the data volume is large, dynamic wide table creation and incremental data filling are difficult to achieve.

Method used

By obtaining data change messages, query the target primary key in the wide table according to the table name and primary key in the message, delete the old data and perform incremental filling, and use the half-finish search algorithm to fill it fully to improve efficiency.

Benefits of technology

It improves data filling efficiency, ensures the stability of data filling, and avoids filling failure. It is suitable for analytical MPP databases such as ClickHouse and Doris.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119005147A_ABST
    Figure CN119005147A_ABST
Patent Text Reader

Abstract

The invention discloses a data filling method and device, electronic equipment and a computer readable medium, and relates to the technical field of databases. The method comprises the following steps: splicing a basic service table and at least one associated service table into a wide table according to a table structure of the basic service table and a table structure of the at least one associated service table associated with the basic service table; fully filling the business data in the basic business table and the business data in the at least one associated business table into a wide table; obtaining each data change message, wherein the data change message carries the table name of the service table subjected to the data change and the primary key subjected to the data change; and querying a target wide table main key in the wide table according to each data change message, deleting wide table data corresponding to the target wide table main key, and filling the changed data increment into the wide table according to the data change message. According to the embodiment, the technical problems of low data filling efficiency and easy filling failure can be solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data filling method, device, electronic device and computer-readable medium. Background Art

[0002] Currently, most static wide tables are populated with data through stream processing frameworks, but static wide tables cannot be dynamically created according to business needs, and their application scope is narrow. If wide tables can be created dynamically, the process of calculating incremental data by the stream processing framework will be more troublesome.

[0003] Dynamic wide tables are basically filled with full data through the computing engine. When the data volume is large, data pulling is prone to timeout, resulting in data filling failure. In addition, it is currently impossible to fill incremental data. Summary of the invention

[0004] In view of this, embodiments of the present invention provide a data filling method, device, electronic device and computer-readable medium to solve the technical problems of low data filling efficiency and easy filling failure.

[0005] To achieve the above object, according to one aspect of an embodiment of the present invention, a data filling method is provided, comprising:

[0006] According to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and the at least one associated business table are spliced ​​into a wide table; wherein the basic business table is associated with the at least one associated business table through an associated field;

[0007] Filling all the business data in the basic business table and the business data in the at least one associated business table into the wide table;

[0008] Obtain each data change message, where the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred;

[0009] The target wide table primary key is queried in the wide table according to each data change message, the target wide table primary key and its corresponding wide table data are deleted, and the changed data is incrementally filled into the wide table according to the data change message.

[0010] Optionally, querying the target wide table primary key in the wide table according to each data change message includes:

[0011] According to each data change message, query the basic business table for a first basic business primary key in which data has been changed;

[0012] According to each data change message, query the wide table for a second basic business primary key in which data has been changed;

[0013] Merge the first basic business primary key and the second basic business primary key to remove duplication, thereby obtaining a target basic business primary key;

[0014] A target wide table primary key is queried in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key.

[0015] Optionally, searching the basic business table for a first basic business primary key having data changed according to each data change message includes:

[0016] If the business table with data changes carried in the data change message is the basic business table, querying the basic business table for a first basic business primary key with data changes according to the primary key with data changes;

[0017] If the business table with data changes carried in the data change message is the associated business table, the first basic business primary key with data changes is queried in the basic business table according to the associated fields between the associated business table and the basic business table and the primary key with data changes.

[0018] Optionally, searching the wide table for a second basic business primary key in which data has been changed according to each data change message includes:

[0019] The wide table primary key with data changes is queried in the wide table according to the primary key with data changes, and then the second basic business primary key with data changes is queried according to the wide table data corresponding to the wide table primary key with data changes; wherein the primary key with data changes is the basic business primary key and / or the associated business primary key.

[0020] Optionally, obtain various data change messages, including:

[0021] Pull each data change message from the message middleware at regular intervals.

[0022] Optionally, filling the changed data increment into the wide table according to the data change message includes:

[0023] The target business table is queried out according to the table name of the business table with data changes, the target business data is queried out in the target business table according to the primary key with data changes, and the target business data is filled into the wide table; wherein the target business table is a basic business table and / or an associated business table.

[0024] Optionally, filling the business data in the basic business table and the business data in the at least one associated business table into the wide table in full includes:

[0025] A binary search algorithm is used to control the value range of the primary key of the basic business table;

[0026] According to the value range of the primary key of the basic business table, the business data in the basic business table and the business data in the at least one associated business table are fully filled into the wide table in batches.

[0027] In addition, according to another aspect of an embodiment of the present invention, a data filling device is provided, comprising:

[0028] a splicing module, configured to splice the basic business table and the at least one associated business table associated with the basic business table into a wide table according to the table structure of the basic business table and the table structure of the at least one associated business table associated with the basic business table; wherein the basic business table is associated with the at least one associated business table through an associated field;

[0029] A full filling module, used to fill the business data in the basic business table and the business data in the at least one associated business table into the wide table;

[0030] An acquisition module, used to acquire each data change message, wherein the data change message carries the table name of the business table where the data change occurs and the primary key where the data change occurs;

[0031] The incremental filling module is used to query the target wide table primary key in the wide table according to the various data change messages, delete the target wide table primary key and its corresponding wide table data, and incrementally fill the changed data into the wide table according to the data change message.

[0032] Optionally, the incremental filling module is further used for:

[0033] According to each data change message, query the basic business table for a first basic business primary key in which data has been changed;

[0034] According to each data change message, query the wide table for a second basic business primary key in which data has been changed;

[0035] Merge the first basic business primary key and the second basic business primary key to remove duplication, thereby obtaining a target basic business primary key;

[0036] A target wide table primary key is queried in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key.

[0037] Optionally, the incremental filling module is further used for:

[0038] If the business table with data changes carried in the data change message is the basic business table, querying the basic business table for a first basic business primary key with data changes according to the primary key with data changes;

[0039] If the business table with data changes carried in the data change message is the associated business table, the first basic business primary key with data changes is queried in the basic business table according to the associated fields between the associated business table and the basic business table and the primary key with data changes.

[0040] Optionally, the incremental filling module is further used for:

[0041] The wide table primary key with data changes is queried in the wide table according to the primary key with data changes, and then the second basic business primary key with data changes is queried according to the wide table data corresponding to the wide table primary key with data changes; wherein the primary key with data changes is the basic business primary key and / or the associated business primary key.

[0042] Optionally, the acquisition module is further used for:

[0043] Pull each data change message from the message middleware at regular intervals.

[0044] Optionally, the incremental filling module is further used for:

[0045] The target business table is queried out according to the table name of the business table with data changes, the target business data is queried out in the target business table according to the primary key with data changes, and the target business data is filled into the wide table; wherein the target business table is a basic business table and / or an associated business table.

[0046] Optionally, the full filling module is further used for:

[0047] A binary search algorithm is used to control the value range of the primary key of the basic business table;

[0048] According to the value range of the primary key of the basic business table, the business data in the basic business table and the business data in the at least one associated business table are fully filled into the wide table in batches.

[0049] According to another aspect of an embodiment of the present invention, there is further provided an electronic device, including:

[0050] one or more processors;

[0051] a storage device for storing one or more programs,

[0052] When the one or more programs are executed by the one or more processors, the one or more processors implement the method described in any one of the above embodiments.

[0053] According to another aspect of an embodiment of the present invention, a computer-readable medium is provided, on which a computer program is stored. When the program is executed by a processor, the method described in any one of the above embodiments is implemented.

[0054] According to another aspect of an embodiment of the present invention, a computer program product is provided, including a computer program, and when the computer program is executed by a processor, the method described in any one of the above embodiments is implemented.

[0055] An embodiment of the above invention has the following advantages or beneficial effects: because each data change message is obtained, the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred, the target wide table primary key is queried in the wide table according to each data change message, the wide table data corresponding to the target wide table primary key is deleted, and the changed data is incrementally filled into the wide table according to the data change message. Therefore, the technical problems of low data filling efficiency and easy filling failure in the prior art are overcome. The embodiment of the present invention does not need to pay attention to how the data changes specifically, but first finds the data impact range in the wide table through the data change message, then deletes the affected wide table data, and finally performs data incremental filling. This not only improves the data filling efficiency, but also ensures smooth data filling and avoids data filling failure.

[0056] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art descriptions. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work. Among them:

[0058] Figure 1 is a flow chart of a data filling method according to an embodiment of the present invention;

[0059] Figure 2 is a flow chart of a data filling method according to a reference embodiment of the present invention;

[0060] Figure 3is a flow chart of a data filling method according to another reference embodiment of the present invention;

[0061] Figure 4 is a flow chart of a data filling method according to yet another reference embodiment of the present invention;

[0062] Figure 5 is a schematic diagram of a data filling device according to an embodiment of the present invention;

[0063] Figure 6 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0064] Figure 7 It is a schematic diagram of the structure of a computer system of a terminal device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0065] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0066] It should be noted that the collection, collection, updating, analysis, processing, use, transmission, storage and other aspects of user personal information involved in the technical solution of this disclosure are in compliance with the provisions of relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. Necessary measures are taken for user personal information to prevent illegal access to user personal information data and maintain the security of user personal information, network security and national security.

[0067] Figure 1 is a flow chart of a data filling method according to an embodiment of the present invention. As an embodiment of the present invention, Figure 1 As shown, the data filling method may include:

[0068] Step 101, according to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and the at least one associated business table are spliced ​​into a wide table; wherein the basic business table is associated with the at least one associated business table through an associated field.

[0069] First, according to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and at least one associated business table are spliced ​​into a wide table. It should be noted that the basic business table and the associated business table are associated through associated fields, and in the wide table, the basic business primary key of the basic business table will not be empty, while the associated business primary key of the associated business table may be empty.

[0070] For example, there is a sales order table (basic business table) and a product table and a shipping warehouse table (two related business tables) associated with the sales order table. The table structure of the sales order table is as follows:

[0071]

[0072] The table structure of the products table is as follows:

[0073]

[0074] The table structure of the shipping warehouse table is as follows:

[0075]

[0076] The sales order table is associated with the product table through product codes, and the sales order table is associated with the shipping warehouse table through warehouse codes. Therefore, the sales order table and the product table and shipping warehouse table associated with the sales order table can be spliced ​​into a wide table. The table structure of the wide table is as follows:

[0077]

[0078] Wide table: Multiple business tables are associated through business table fields, and some business table fields are selected to form a new large table including multiple business table fields. This is called a wide table.

[0079] Business table: A wide table is composed of multiple business tables through associated fields, and some fields are selected from each business table. Each business object table is called a business table. Business tables include basic business tables and associated business tables. The basic business tables and associated business tables are associated through associated fields.

[0080] In the embodiment of the present invention, when splicing wide tables, the primary keys of the various business tables are also spliced ​​into the wide table, which facilitates the calculation of the data range affected by the changed data.

[0081] Step 102: Fill all the business data in the basic business table and the business data in the at least one associated business table into the wide table.

[0082] After the basic business table and at least one associated business table are joined into a wide table through associated fields, the business data in the basic business table and the business data in each associated business table are fully filled into the wide table.

[0083] Optionally, the business data in the basic business table and the business data in the at least one associated business table are filled into the wide table in full, including: using a binary search algorithm to control the value range of the primary key of the basic business table; according to the value range of the primary key of the basic business table, the business data in the basic business table and the business data in the at least one associated business table are filled into the wide table in batches. Since in the wide table, the basic business primary key of the basic business table will not be empty, and the associated business primary key of the associated business table may be empty, the binary search algorithm is first used to control the value range of the primary key of the basic business table, and then the value range of the primary key of each batch is used to fill the business data in the basic business table and the business data in each associated business table into the wide table in full. The use of a binary search algorithm can control the amount of data written to the wide table in a single batch, thereby efficiently writing data in batches to the wide table. Therefore, the embodiment of the present invention can control the total amount of data written to the wide table each time, avoid deep paging performance problems, and prevent data skew, thereby ensuring smooth data filling in large data volume scenarios.

[0084] In response to flexible business needs, the embodiments of the present invention can dynamically create wide tables based on the basic business table and the associated business tables associated with the basic business table, and efficiently and stably fill the full amount of data. The embodiments of the present invention can support the filling of large amounts of data of more than 100 million, and are particularly suitable for analytical MPP databases such as ClickHouse and Doris.

[0085] Step 103: Acquire each data change message, wherein the data change message carries the table name of the business table where the data change occurs and the primary key where the data change occurs.

[0086] Optionally, obtaining each data change message includes: pulling each data change message from the message middleware at regular intervals. After the business data in each business table (basic business table and associated business table) changes, the database (producer) generates a data change message according to the table name of the business table where the data has changed and the primary key where the data has changed, and stores the data change message in the message middleware (MQ). The consumer pulls each data change message within this period from the message middleware at regular intervals.

[0087] Step 104: query the target wide table primary key in the wide table according to the data change messages, delete the target wide table primary key and its corresponding wide table data, and fill the changed data increment into the wide table according to the data change messages.

[0088] By parsing each data change message, the table name of the business table where the data has changed and the primary key where the data has changed can be obtained, and then the target wide table primary key (such as the id in the wide table) is queried in the wide table based on the table name of the business table where the data has changed and the primary key where the data has changed, and then the wide table data corresponding to the target wide table primary key (that is, the row data in the wide table) is deleted, and finally the wide table is incrementally filled based on the table name of the business table where the data has changed and the primary key where the data has changed. It should be noted that the embodiment of the present invention does not need to pay attention to how the data is changed specifically, but first finds the data impact range in the wide table through the data change message, then deletes the affected wide table data, and finally performs data incremental filling.

[0089] Optionally, a target wide table primary key is queried in the wide table according to the various data change messages, including: querying a first basic business primary key whose data has been changed in the basic business table according to the various data change messages; querying a second basic business primary key whose data has been changed in the wide table according to the various data change messages; merging and removing duplicates of the first basic business primary key and the second basic business primary key to obtain a target basic business primary key; querying a target wide table primary key in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key. There are many situations in which data is changed, such as addition, deletion, and change. If the data is deleted, the data can no longer be found in the business table. If the data is added, the data may not be found in the wide table. In order to accurately query the target wide table primary key, it is necessary to query the first basic business primary key with data change in the basic business table and the second basic business primary key with data change in the wide table, and then merge and deduplicate the queried basic business primary keys. The result is the final target basic business primary key (that is, the id in the basic business table). Finally, the target wide table primary key (that is, the id in the wide table) is queried in the wide table based on the target basic business primary key.

[0090] Optionally, the wide table is filled with the changed data increment according to the data change message, including: querying the target business table according to the table name of the business table with data change, querying the target business data in the target business table according to the primary key with data change, and filling the target business data into the wide table; wherein the target business table is a basic business table and / or an associated business table. Based on the table name of the business table with data change and the primary key with data change carried in the data change message, it is possible to query which business table and which primary key corresponding to which business data has changed, and then fill the business data with data change and the business data in other business tables associated through associated fields into the wide table.

[0091] According to the various embodiments described above, it can be seen that the embodiments of the present invention obtain various data change messages, which carry the table name of the business table where the data change occurred and the primary key where the data change occurred, query the target wide table primary key in the wide table according to each data change message, delete the wide table data corresponding to the target wide table primary key, and fill the changed data incrementally into the wide table according to the data change message. This technical means solves the technical problems of low data filling efficiency and easy filling failure in the prior art. The embodiments of the present invention do not need to pay attention to how the data changes specifically, but first find out the data impact range in the wide table through the data change message, then delete the affected wide table data, and finally perform data incremental filling. This not only improves the data filling efficiency, but also ensures smooth data filling and avoids data filling failure.

[0092] Figure 2 is a flow chart of a data filling method according to a reference embodiment of the present invention. As another embodiment of the present invention, Figure 2 As shown, the data filling method may include:

[0093] Step 201 : According to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and the at least one associated business table are spliced ​​into a wide table.

[0094] The basic business table is associated with the at least one associated business table through an associated field.

[0095] Step 202: Use a binary search algorithm to fill all the business data in the basic business table and the business data in the at least one associated business table into the wide table.

[0096] For example, the business data in the basic business table (sales order table) is as follows:

[0097]

[0098] The business data in the associated business table (product table) is as follows:

[0099]

[0100] The business data in the associated business table (shipping warehouse table) is as follows:

[0101]

[0102] When creating data in a wide table for the first time, you need to fill all the data in each business table. You can use the binary search algorithm to fill all the business data in the sales order table, product table, and shipping warehouse table into the wide table. The data in the wide table after full filling is as follows:

[0103]

[0104] In this step, a binary search algorithm is used to fill the business data in the basic business table and the business data in the associated business table into the wide table. For example, by halving the id range of the basic business table, the total amount of data that meets the conditions is controlled, such as setting the data volume threshold to 1 million, 2 million, 2.5 million or 3 million, etc. If the amount of data that meets the conditions still exceeds the data volume threshold, continue to halve on the basis of the last time, and judge again whether the total amount of data that meets the conditions exceeds the data volume threshold, until the total amount of data that meets the conditions is less than the data volume threshold, and then start to write data in batches in a cycle, and control the range of the id value of the basic business table to control the total amount of data written in each batch. Using a binary search algorithm to write business data in batches, even in the case of data volume scenarios, the full amount of data can be smoothly filled into the wide table.

[0105] If there are subsequent data changes in the basic business table or related business table, the data in the wide table needs to be updated synchronously.

[0106] Step 203: Pull each data change message from the message middleware at regular intervals. The data change message carries the table name of the business table where the data has been changed and the primary key where the data has been changed.

[0107] For example, a record (id=5) is added to the basic business table (sales order table). The business data in the table is as follows:

[0108]

[0109] For example, a record (id=2) is deleted from the associated business table (product table)

[0110]

[0111] The business data in the associated business table (shipping warehouse table) has not changed, as follows:

[0112]

[0113] Step 204: query the basic business table for a first basic business primary key in which data has been changed according to each data change message.

[0114] Optionally, step 204 may include: if the business table with data changes carried in the data change message is the basic business table, then based on the primary key with data changes, querying the first basic business primary key with data changes in the basic business table; if the business table with data changes carried in the data change message is the associated business table, then based on the associated fields between the associated business table and the basic business table and the primary key with data changes, querying the first basic business primary key with data changes in the basic business table.

[0115] If the business table with data changes carried in the data change message is a basic business table, the basic business primary key with data changes is directly queried in the basic business table based on the primary key with data changes carried in the data change message, such as id=5 in the sales order table. If the business table with data changes carried in the data change message is an associated business table, the basic business primary key with data changes is queried in the basic business table based on the associated fields between the associated business table and the basic business table and the primary key with data changes, such as id=3 and id=4 in the sales order table. Finally, three first basic business primary keys with data changes are queried in the basic business table, namely id=3, id=4, and id=5.

[0116] Step 205: According to each data change message, the wide table is queried to find the second basic business primary key whose data has been changed.

[0117] Optionally, step 206 may include: querying the wide table primary key that has changed data in the wide table according to the primary key that has changed data, thereby querying the second basic business primary key that has changed data according to the wide table data corresponding to the wide table primary key that has changed data; wherein the primary key that has changed data is the basic business primary key and / or the associated business primary key. In this step, the wide table primary key that has changed data is queried in the wide table according to the primary key that has changed data (such as the basic business primary key, the associated business primary key, etc.), and then the basic business primary key that has changed data is queried according to the wide table data corresponding to the wide table primary key. For example, if the primary key that has changed data is id=5 in the sales order table and id=2 in the product table, then the wide table primary keys id=3 and id=4 that have changed data can be queried in the wide table, and then the basic business primary keys order_id =3 and order_id =4 that have changed data can be queried according to id=3 and id=4.

[0118] Step 206: merge the first basic business primary key and the second basic business primary key to remove duplication, so as to obtain a target basic business primary key.

[0119] The basic business primary keys queried in step 204 and the basic business primary keys queried in step 205 are merged and deduplicated, and finally the target basic business primary keys order_id =3, order_id =4, order_id =5 are obtained.

[0120] Step 207: query the target wide table primary key in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key.

[0121] The data impact range is queried in the wide table according to the target basic business primary key determined in step 206, that is, the target wide table primary key and its corresponding wide table data. Since there is no order_id = 5 in the wide table, the target wide table primary keys queried in the wide table are id = 3 and id = 4. Then, the data impact range of this data change is the wide table data corresponding to id = 3 and id = 4.

[0122] It should be noted that the reason for executing step 205 is that if the data in the business table is deleted, it may not be found through the associated statement, but it still exists in the wide table, so it is necessary to query once in the wide table, and the union of the two data is the affected range of all data.

[0123] Step 208: Delete the target wide table primary key and its corresponding wide table data.

[0124] After the target wide table primary key is found, the target wide table primary key and its corresponding wide table data are deleted, that is, the data in the wide table affected by the change of the business data in the business table is deleted.

[0125] It should be noted that, since there are only id=3 and id=4 in the wide table, order_id=5 is the newly written change data in the basic business table, in order to improve data filling efficiency, the embodiment of the present invention does not accurately determine which data already exists and which data does not exist, and deletes all existing data.

[0126] Step 209: fill the wide table with the changed data increment according to the data change message.

[0127] Optionally, step 209 may include: querying the target business table according to the table name of the business table with data changes, querying the target business data in the target business table according to the primary key with data changes, and filling the target business data into the wide table; wherein the target business table is a basic business table and / or an associated business table. The table name of the business table with data changes and the primary key with data changes are parsed from the data change message, so that the target business data with data changes can be queried in the target business table (such as the basic business table, the associated business table, etc.), and then the target business data with data changes and the business data in other business tables associated through associated fields are filled into the wide table.

[0128] After incremental filling, the data in the wide table is as follows:

[0129]

[0130] In addition, the specific implementation content of the data filling method in a reference embodiment of the present invention has been described in detail in the data filling method described above, so the repeated content will not be described here.

[0131] Figure 3 is a flow chart of a data filling method according to another reference embodiment of the present invention. As another embodiment of the present invention, Figure 3 As shown, step 103 and step 104 may include:

[0132] Step 301: Pull each data change message from the message middleware at regular intervals. The data change message carries the table name of the business table where the data has been changed and the primary key where the data has been changed.

[0133] Step 302: Determine each target wide table affected by each data change message according to the association relationship between each wide table and each business table.

[0134] Each target wide table is looped, and steps 303 to 305 are executed for each target wide table.

[0135] Step 303: Query the target wide table primary key in the target wide table according to each data change message.

[0136] Step 304: Delete the target wide table primary key and its corresponding wide table data in batches.

[0137] Step 305 , query the changed data in batches according to each data change message, and fill the changed data into the target wide table in batches.

[0138] In an embodiment of the present invention, for incremental data, it is not necessary to accurately determine which cell data in the wide table has changed, but to determine which row data has changed, delete the changed row data, and then write the latest data after the change into the wide table.

[0139] In addition, the specific implementation content of the data filling method in another reference embodiment of the present invention has been described in detail in the data filling method described above, so the repeated content will not be described here.

[0140] Figure 4 is a flow chart of a data filling method according to another reference embodiment of the present invention. As another embodiment of the present invention, Figure 4 As shown, the process of using a binary search algorithm to fill the business data in the business table into the wide table may include:

[0141] Step 401 : According to the association relationship between the basic business table and at least one associated business table associated with the basic business table, the total data volume (K1) of the basic business table and the at least one associated business table is queried.

[0142] Step 402 , determining whether the total data volume (K1) is less than a data volume threshold; if so, executing step 403 ; if not, executing step 404 .

[0143] Step 403: Write all the business data of the basic business table and at least one associated business table into the wide table.

[0144] Step 404: halve the primary key of the basic business table, that is, id=max(id) / 2.

[0145] Step 405, query the total data volume (K2) of the basic business table and at least one associated business table after the primary key is halved.

[0146] Step 406 , determining whether the total data volume (K2) after halving is less than the data volume threshold; if so, executing step 407 ; if not, executing step 408 .

[0147] Step 407: Fill the wide table with all the business data in the basic business table and at least one associated business table in two batches.

[0148] Specifically, first fill all the business data in the basic business table and at least one associated business table into the wide table when id≤max(id) / 2, and then fill all the business data in the basic business table and at least one associated business table into the wide table when max(id) / 2<id≤max(id).

[0149] Step 408, halve the primary key again based on the last halving.

[0150] Step 409, query the total data volume (K3) of the basic business table and at least one associated business table after the primary key is halved again.

[0151] Step 410 , determining whether the total data volume (K3) after being halved again is less than the data volume threshold; if so, executing step 411 ; if not, executing step 408 .

[0152] Step 411: Fill the wide table with all the business data in the basic business table and at least one associated business table in batches.

[0153] Specifically, when the previous batch primary key < id < previous batch primary key * 2, all business data in the basic business table and at least one related business table are filled into the wide table, and then the id value is increased in an upward loop until all data is filled.

[0154] The embodiment of the present invention controls the total amount of data that meets the conditions by halving the ID range of the basic business table. If the amount of data that meets the conditions still exceeds the data volume threshold, it continues to halve based on the previous time, and again determines whether the total amount of data that meets the conditions exceeds the data volume threshold, until the total amount of data that meets the conditions is less than the data volume threshold, and then starts to write data in batches in a cycle. The binary search algorithm is used to write business data in batches, and even in the case of a large amount of data, the full amount of data can be smoothly filled into the wide table.

[0155] In addition, the specific implementation content of the data filling method in another reference embodiment of the present invention has been described in detail in the data filling method described above, so the repeated content will not be described here.

[0156] Figure 5 is a schematic diagram of a data filling device according to an embodiment of the present invention. Figure 5As shown, the data filling device 500 includes a splicing module 501, a full filling module 502, an acquisition module 503 and an incremental filling module 504; wherein the splicing module 501 is used to splice the basic business table and the at least one associated business table associated with the basic business table into a wide table according to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table; wherein the basic business table is associated with the at least one associated business table through an associated field; the full filling module 502 is used to fill the business data in the basic business table and the business data in the at least one associated business table into the wide table in full; the acquisition module 503 is used to obtain each data change message, the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred; the incremental filling module 504 is used to query the target wide table primary key in the wide table according to each data change message, delete the target wide table primary key and its corresponding wide table data, and fill the changed data incrementally into the wide table according to the data change message.

[0157] Optionally, the incremental filling module 504 is further configured to:

[0158] According to each data change message, query the basic business table for a first basic business primary key in which data has been changed;

[0159] According to each data change message, query the wide table for a second basic business primary key in which data has been changed;

[0160] Merge the first basic business primary key and the second basic business primary key to remove duplication, thereby obtaining a target basic business primary key;

[0161] A target wide table primary key is queried in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key.

[0162] Optionally, the incremental filling module 504 is further configured to:

[0163] If the business table with data changes carried in the data change message is the basic business table, querying the basic business table for a first basic business primary key with data changes according to the primary key with data changes;

[0164] If the business table with data changes carried in the data change message is the associated business table, the first basic business primary key with data changes is queried in the basic business table according to the associated fields between the associated business table and the basic business table and the primary key with data changes.

[0165] Optionally, the incremental filling module 504 is further configured to:

[0166] The wide table primary key with data changes is queried in the wide table according to the primary key with data changes, and then the second basic business primary key with data changes is queried according to the wide table data corresponding to the wide table primary key with data changes; wherein the primary key with data changes is the basic business primary key and / or the associated business primary key.

[0167] Optionally, the acquisition module 503 is further used for:

[0168] Pull each data change message from the message middleware at regular intervals.

[0169] Optionally, the incremental filling module 504 is further configured to:

[0170] The target business table is queried out according to the table name of the business table with data changes, the target business data is queried out in the target business table according to the primary key with data changes, and the target business data is filled into the wide table; wherein the target business table is a basic business table and / or an associated business table.

[0171] Optionally, the full filling module 502 is further used for:

[0172] A binary search algorithm is used to control the value range of the primary key of the basic business table;

[0173] According to the value range of the primary key of the basic business table, the business data in the basic business table and the business data in the at least one associated business table are fully filled into the wide table in batches.

[0174] It should be noted that the specific implementation content of the data filling device of the present invention has been described in detail in the data filling method described above, so the repeated content will not be described again here.

[0175] Figure 6 An exemplary system architecture 600 is shown to which the data filling method or data filling device according to the embodiment of the present invention can be applied.

[0176] like Figure 6 As shown, system architecture 600 may include terminal devices 601, 602, 603, network 604 and server 605. Network 604 is used to provide a medium for communication links between terminal devices 601, 602, 603 and server 605. Network 604 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0177] The user can use the terminal devices 601, 602, 603 to interact with the server 605 through the network 604 to receive or send messages, etc. Various communication client applications can be installed on the terminal devices 601, 602, 603, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only for example).

[0178] The terminal devices 601 , 602 , and 603 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, and desktop computers, etc.

[0179] The server 605 may be a server that provides various services, such as a backend management server (for example only) that provides support for shopping websites browsed by users using the terminal devices 601, 602, and 603. The backend management server may analyze and process the received data such as the item information query request, and feed back the processing results to the terminal device.

[0180] It should be noted that the data filling method provided in the embodiment of the present invention is generally executed by the server 605 , and accordingly, the data filling device is generally arranged in the server 605 .

[0181] It should be understood that Figure 6 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0182] Reference below Figure 7 , which shows a schematic diagram of the structure of a computer system 700 of a terminal device suitable for implementing an embodiment of the present invention. Figure 7 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0183] like Figure 7 As shown, the computer system 700 includes a central processing unit (CPU) 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage part 708 to a random access memory (RAM) 703. In the RAM 703, various programs and data required for the operation of the system 700 are also stored. The CPU 701, the ROM 702, and the RAM 703 are connected to each other through a bus 704. An input / output (I / O) interface 705 is also connected to the bus 704.

[0184] The following components are connected to the I / O interface 705: an input section 706 including a keyboard, a mouse, etc.; an output section 707 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN card, a modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 710 as needed, so that a computer program read therefrom is installed into the storage section 708 as needed.

[0185] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program, which includes a computer program carried on a computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 709, and / or installed from the removable medium 711. When the computer program is executed by the central processing unit (CPU) 701, the above-mentioned functions defined in the system of the present invention are executed.

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

[0187] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, functions and operations of the system, method and computer program according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0188] The modules involved in the embodiments of the present invention may be implemented by software or hardware. The modules described may also be set in a processor, for example, it may be described as: a processor includes a splicing module, a full filling module, an acquisition module and an incremental filling module, wherein the names of these modules do not constitute a limitation on the modules themselves in some cases.

[0189] As another aspect, the present invention also provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently and not be assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device implements the following method: according to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and the at least one associated business table are spliced ​​into a wide table; wherein the basic business table is associated with the at least one associated business table through an associated field; the business data in the basic business table and the business data in the at least one associated business table are filled into the wide table in full; each data change message is obtained, and the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred; according to each data change message, the target wide table primary key is queried in the wide table, the target wide table primary key and its corresponding wide table data are deleted, and the changed data increment is filled into the wide table according to the data change message.

[0190] As another aspect, an embodiment of the present invention further provides a computer program product, including a computer program, and when the computer program is executed by a processor, the method described in any of the above embodiments is implemented.

[0191] According to the technical solution of the embodiment of the present invention, because each data change message is obtained, the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred, the target wide table primary key is queried in the wide table according to each data change message, the wide table data corresponding to the target wide table primary key is deleted, and the changed data is incrementally filled into the wide table according to the data change message. Therefore, the technical problems of low data filling efficiency and easy filling failure in the prior art are overcome. The embodiment of the present invention does not need to pay attention to how the data changes specifically, but first finds the data impact range in the wide table through the data change message, then deletes the affected wide table data, and finally performs data incremental filling. This not only improves the data filling efficiency, but also ensures smooth data filling and avoids data filling failure.

[0192] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A data filling method, characterized in that: include: According to the table structure of the basic business table and the table structure of at least one associated business table associated with the basic business table, the basic business table and the at least one associated business table are spliced ​​into a wide table; wherein the basic business table is associated with the at least one associated business table through an associated field; Filling all the business data in the basic business table and the business data in the at least one associated business table into the wide table; Obtain each data change message, where the data change message carries the table name of the business table where the data change has occurred and the primary key where the data change has occurred; The target wide table primary key is queried in the wide table according to each data change message, the target wide table primary key and its corresponding wide table data are deleted, and the changed data is incrementally filled into the wide table according to the data change message.

2. The method according to claim 1, characterized in that Querying the target wide table primary key in the wide table according to each data change message includes: According to each data change message, query the basic business table for a first basic business primary key in which data has been changed; According to each data change message, query the wide table for a second basic business primary key in which data has been changed; Merge the first basic business primary key and the second basic business primary key to remove duplication, thereby obtaining a target basic business primary key; A target wide table primary key is queried in the wide table according to the target basic business primary key, and the wide table data corresponding to the target wide table primary key includes the target basic business primary key.

3. The method according to claim 2, characterized in that Querying the first basic business primary key in which data has been changed in the basic business table according to each data change message includes: If the business table with data changes carried in the data change message is the basic business table, querying the basic business table for a first basic business primary key with data changes according to the primary key with data changes; If the business table with data changes carried in the data change message is the associated business table, the first basic business primary key with data changes is queried in the basic business table according to the associated fields between the associated business table and the basic business table and the primary key with data changes.

4. The method according to claim 2, characterized in that: Querying the wide table for a second basic business primary key in which data has been changed according to each data change message includes: The wide table primary key with data changes is queried in the wide table according to the primary key with data changes, and then the second basic business primary key with data changes is queried according to the wide table data corresponding to the wide table primary key with data changes; wherein the primary key with data changes is the basic business primary key and / or the associated business primary key.

5. The method according to claim 1, characterized in that Get various data change messages, including: Pull each data change message from the message middleware at regular intervals.

6. The method according to claim 1, characterized in that Filling the changed data increment into the wide table according to the data change message includes: The target business table is queried out according to the table name of the business table with data changes, the target business data is queried out in the target business table according to the primary key with data changes, and the target business data is filled into the wide table; wherein the target business table is a basic business table and / or an associated business table.

7. The method according to claim 1, characterized in that Filling the business data in the basic business table and the business data in the at least one associated business table into the wide table in full, including: A binary search algorithm is used to control the value range of the primary key of the basic business table; According to the value range of the primary key of the basic business table, the business data in the basic business table and the business data in the at least one associated business table are fully filled into the wide table in batches.

8. A data filling device, characterized in that: include: a splicing module, configured to splice the basic business table and the at least one associated business table associated with the basic business table into a wide table according to the table structure of the basic business table and the table structure of the at least one associated business table associated with the basic business table; wherein the basic business table is associated with the at least one associated business table through an associated field; A full filling module, used to fill the business data in the basic business table and the business data in the at least one associated business table into the wide table; An acquisition module, used to acquire each data change message, wherein the data change message carries the table name of the business table where the data change occurs and the primary key where the data change occurs; The incremental filling module is used to query the target wide table primary key in the wide table according to the various data change messages, delete the target wide table primary key and its corresponding wide table data, and incrementally fill the changed data into the wide table according to the data change message.

9. An electronic device, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 7.

10. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

11. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.