Wide table synchronization method and apparatus

Abstract

The present disclosure provides a wide table synchronization method and device, the wide table is synthesized by multiple data tables, and the data streams corresponding to the multiple data tables are respectively consumed in different stream processing tasks. The method comprises the following steps: consuming the data stream of a current data table, extracting the associated field of an associated data table associated with the current data table from the data stream of the current data table; querying the data of the associated data table from a preset database according to the associated field; in the case that the data of the associated data table and the data of the wide table primary key are queried, synchronizing the data of the current data table, the data of the associated data table and the data of the wide table primary key to the wide table; when the data of the associated data table is not queried, the associated field is added to a retry queue created in advance to be re-queried according to the queue. The present method saves a large amount of storage space and achieves the effect that each server node can fully utilize the CPU to process data.

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and in particular to a wide table synchronization method and device. Background Art

[0002] Existing business reporting scenarios require queries against large amounts of data. Many of these scenarios involve data stored in multiple tables. This means that retrieving the data required for a report requires performing queries across multiple tables. To facilitate queries, data from multiple tables is often consolidated into a single wide table. Subsequent changes to the tables are then synchronized to the wide table.

[0003] Currently, a stream processing framework is used to achieve wide table synchronization. Operations on data tables will generate changed data, which form data streams. Multiple data streams are consumed in the same stream task. The data stream that arrives first needs to wait for the data stream that arrives later. Therefore, storage space needs to be set up to store the data of the stream that arrives first. However, in actual scenarios, it is impossible to determine the arrival time of the previous and next streams, so a large amount of storage space is required to store the data of the first-arrived stream. Summary of the Invention

[0004] The present disclosure provides a wide table synchronization method and apparatus to solve the problem in the prior art that wide table synchronization requires a large amount of storage space.

[0005] In a first aspect, the present disclosure provides a wide table synchronization method, wherein the wide table is composed of multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The method includes:

[0006] Consuming the data stream of the current data table, and extracting the associated fields of the associated data table associated with the current data table from the data stream of the current data table;

[0007] Querying the data of the associated data table from a preset database according to the associated fields;

[0008] When the data of the associated data table and the data of the wide table primary key are queried, the data of the current data table, the data of the associated data table and the data of the wide table primary key are synchronized to the wide table, wherein the wide table primary key is determined when the wide table is created;

[0009] If no data in the associated data table is found, the associated field is added to a pre-created retry queue, and the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue is continued.

[0010] According to the wide table synchronization method provided by the present disclosure, when the data of the associated data table and the data of the wide table primary key are queried, synchronizing the data of the current data table, the data of the associated data table, and the data of the wide table primary key to the wide table includes:

[0011] When the data of the wide table primary key is found in the query, the data of the wide table primary key, the data of the current data table, and the data of the associated data table are obtained;

[0012] A data row corresponding to the wide table is generated according to the data of the wide table primary key, the data of the current data table, and the data of the associated data table, and the data row is synchronized to the wide table.

[0013] According to the wide table synchronization method provided in the present disclosure, before consuming the data stream of the current data table, the method further includes:

[0014] determining a data table from the plurality of data tables as a reference table;

[0015] Integrate the data columns of the remaining data tables into the data columns of the base table to form the wide table;

[0016] Determine the primary key of the wide table.

[0017] According to the wide table synchronization method provided by the present disclosure, before a new data stream is generated and consumed, the step of querying the data of the associated data table from a preset database according to the associated field in the retry queue is triggered.

[0018] According to the wide table synchronization method provided by the present disclosure, the step of adding the associated field to a pre-created retry queue includes:

[0019] When the associated field enters the queue for the first time, the number of rechecks of the associated field is set to 0, and the associated field is added to the end of the retry queue;

[0020] If the associated field does not enter the queue for the first time, the number of rechecks of the associated field is increased by 1, and it is determined whether the number of rechecks of the associated field reaches a preset threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

[0021] According to the wide table synchronization method provided by the present disclosure, the step of adding the associated field to a pre-created retry queue includes:

[0022] When the associated field enters the queue for the first time, the recheck duration of the associated field is set to 0, the first entry time is recorded, and the associated field is added to the end of the retry queue;

[0023] In the case that this is not the first time that the associated field enters the queue, the recheck duration is set to the time difference between the current time and the first time of entering the queue, and it is determined whether the recheck duration of the associated field exceeds the preset duration threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

[0024] According to the wide table synchronization method provided by the present disclosure, before eliminating the associated field, the method also includes: querying the current queue length of the retry queue, if the current queue length is less than a preset length threshold, adding the associated field to the end of the retry queue, otherwise, eliminating the associated field.

[0025] In a second aspect, the present disclosure provides a wide table synchronization device, wherein the wide table is composed of multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different stream processing tasks respectively. The device includes:

[0026] A data stream consumption module, configured to consume the data stream of the current data table and extract associated fields of an associated data table associated with the current data table from the data stream of the current data table;

[0027] A data query module, configured to query the data in the associated data table from a preset database according to the associated fields;

[0028] a data synchronization module, configured to synchronize the data of the current data table, the data of the associated data table, and the data of the wide table primary key to the wide table when the data of the associated data table and the data of the wide table primary key are queried, wherein the wide table primary key is determined when the wide table is created;

[0029] The data retry module is used to add the associated field to a pre-created retry queue when no data in the associated data table is found, and continue to execute the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue.

[0030] In a third aspect, the present disclosure provides an electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of any one of the above-described wide table synchronization methods when executing the program.

[0031] In a fourth aspect, the present disclosure provides a non-transitory computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of any of the above-mentioned wide table synchronization methods.

[0032] The present disclosure provides a wide table synchronization method and device. The wide table is synthesized from multiple data tables. The data streams corresponding to the multiple data tables are consumed in different stream processing tasks. In this way, the data streams corresponding to the data tables are processed in separate stream processing tasks without the need to store the previously arrived streams or wait for subsequent streams to arrive before processing. Moreover, after consuming the data stream of the current data table and extracting the associated fields of the associated data table associated with the current data table, if the data of the associated data table cannot be queried from the database based on the associated fields, only the associated fields corresponding to the associated data table are added to the retry queue. The entire previously arrived data stream is not stored, that is, the entire data stream of the current data table is not stored, saving a large amount of storage space. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions in the present disclosure or the prior art, a brief introduction will be given below to the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0034] Figure 1 This is one of the flowcharts of the wide table synchronization method provided by the embodiment of the present disclosure;

[0035] Figure 2 This is the second flowchart of the wide table synchronization method provided by the embodiment of the present disclosure;

[0036] Figure 3 This is the third flowchart of the wide table synchronization method provided by the embodiment of the present disclosure;

[0037] Figure 4 is a structural diagram of a wide table synchronization device provided by an embodiment of the present disclosure;

[0038] Figure 5 It is a structural diagram of the electronic device provided by the present disclosure. DETAILED DESCRIPTION

[0039] To make the objectives, technical solutions, and advantages of this disclosure more clear, the technical solutions of this disclosure will be clearly and completely described below in conjunction with the accompanying drawings. Obviously, the embodiments described are only part of the embodiments of this disclosure, not all of them. All other embodiments obtained by persons of ordinary skill in the art based on the embodiments of this disclosure without creative effort shall fall within the scope of protection of this disclosure.

[0040] First, the terms involved in this embodiment are explained.

[0041] Wide table: refers to multiple data tables combined into a complete data table with the same data dimension through associated fields. The wide table contains the fields of all associated tables.

[0042] Data stream: refers to the data generated by business operations and stored in the MySQL database. Operations such as inserting and updating data tables in the database will generate changing data. These changing data continuously form stream-like data.

[0043] Flink multi-stream join: refers to the Flink stream processing framework, which consumes multiple data streams and merges them into a single data stream for further processing by correlating fields.

[0044] Retry queue: The data that failed to be processed or was not queried is placed in a storage mechanism in an orderly manner, ready for consumption or query next time.

[0045] Existing business reporting scenarios require large data volumes to be queried, and a significant portion of the data involved is stored in multiple database tables. This means that to retrieve the data required for a report, multiple database tables must be joined together for querying. Existing MySQL databases cannot perform multi-table join queries on large amounts of data, and the Elasticsearch database, with its excellent features, has been chosen as a database for storing and querying large, even massive, amounts of data. However, Elasticsearch cannot perform join queries, so data processing is required. This involves preprocessing data from multiple tables into a single wide table. Subsequent changes to each table must then be synchronized to the wide table. Currently, the Flink stream processing framework is used for wide table synchronization.

[0046] Flink stream processing technology processes wide tables using a multi-stream join approach. This involves consuming multiple real-time data streams in a single Flink task, linking the data from these streams through associated fields to form a complete data entry, which is then written to the Elasticsearch database.

[0047] In the Flink stream processing framework's multi-stream join scheme, multiple data streams may not arrive simultaneously. When these streams arrive at different times, the first-arriving stream must wait for the next. Because Flink is a real-time processing framework, it cannot pause processing. Furthermore, in real-world scenarios, data stream processing cannot be blocked by a single stream. Therefore, storage media is required to store the data of the first-arriving stream. However, in real-world scenarios, the arrival time of the first and second streams is uncertain, requiring a large amount of storage media. On some big data platforms, this storage media is the Flink server's hard drive, which has a maximum capacity of 80GB. Therefore, when a large amount of storage media is required, a large number of server nodes must be clustered to ensure sufficient storage. However, this large number of server nodes results in significant waste of CPU resources.

[0048] To solve the problem of needing a large amount of storage media, e.g. Figure 1 As shown in FIG. 1 , a wide table synchronization method provided by an embodiment of the present disclosure is provided. In this method, a wide table is composed of multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different Flink tasks. The method includes:

[0049] Step S101: Consume the data stream of the current data table, and extract associated fields of an associated data table associated with the current data table from the data stream of the current data table.

[0050] In this embodiment, the data generated by business operations is stored in a corresponding data table in a pre-set database (e.g., MySQL). Operations such as inserts and updates on the data table generate corresponding change data. These change data continuously form a stream-like data stream, namely, the data stream corresponding to the data table. Obviously, the data stream corresponding to a data table includes all data from a single operation on the data table. Due to the association between data tables, the data stream also includes the associated fields of the associated data tables associated with the data table.

[0051] The order of operations on each data table is different. The data table operated first forms the corresponding data stream first. The Flink framework distributes the data streams corresponding to different data tables to their respective Flink tasks for consumption in order.

[0052] Step S102: Query the data of the associated data table from a preset database based on the associated fields. Because the data streams corresponding to the multiple data tables in the composite wide table are consumed by different Flink tasks, the data stream of each current data table does not include data from other data tables except for the associated fields of its associated data table. Therefore, it is necessary to query the database for data of the associated data table. The multiple data tables in the composite wide table have a certain association relationship, and an associated query using the associated fields can obtain a complete data row of the wide table.

[0053] Step S103: When data in the associated data table and the wide table primary key are found, the data in the current data table, the data in the associated data table, and the data in the wide table primary key are synchronized to the wide table. The wide table primary key is determined when the wide table is created. The primary key in a data table uniquely identifies a row of data. When creating a wide table, the wide table primary key can be set based on the relationships between multiple data tables or actual needs. Synchronization of the wide table is performed based on the primary key.

[0054] Step S104: If the data of the associated data table is not found, the associated field is added to the pre-created retry queue, and step S102 is continued according to the associated field in the retry queue. The retry queue is created when the Flink framework is running. After creation, the retry queue is empty. In the subsequent synchronization process of the wide table, when the data of the associated data table cannot be queried due to the data flow of the associated data table being generated later, the associated field is added to the retry queue. The retry queue can be created in the memory of the Flink server node or in an external storage medium. When re-querying, the associated field is taken out from the head of the retry queue, and the query is performed according to step S102. If the query is successful, step S103 is executed, and the associated field is no longer added to the retry queue. Otherwise, the associated field is re-added to the end of the retry queue.

[0055] The wide table synchronization method provided by the present disclosure consumes the data streams corresponding to the multiple data tables that form the wide table in different Flink tasks during the wide table synchronization process. In this way, the data streams corresponding to the data tables are processed in separate Flink tasks without the need to store the previously arrived streams or wait for the subsequent streams to arrive before processing. Moreover, after consuming the data stream of the current data table and extracting the associated fields of the associated data table associated with the current data table, if the data of the associated data table cannot be queried from the database based on the associated fields, only the associated fields corresponding to the associated data table are added to the retry queue, and the entire previously arrived data stream is not stored, that is, the entire data stream of the current data table is not stored, thereby saving a large amount of storage space and achieving the effect that each Flink server node can fully utilize the CPU to process data.

[0056] In this embodiment, step S103 includes:

[0057] Step 1. When the data of the wide table primary key is found, obtain the data of the wide table primary key, the data of the current data table, and the data of the associated data table. If the data of the wide table primary key is not found, it means that part of the fields of the wide table primary key are fields of other data tables. Subsequently, when the data streams of other data tables are consumed, the complete data of the wide table primary key will be obtained by executing the above steps S101 to S04. The primary key uniquely identifies a row of data, so before wide table synchronization, it is necessary to determine whether the primary key is obtained. Since the data stream of the current data table has been consumed, and the data of the associated data table associated with the current data table can be queried, the preset database has recorded the data in the data stream of the previously consumed data table. From the data of each associated data table recorded in the preset database, it can be determined whether the data of the wide table primary key is obtained.

[0058] Step 2: Generate data rows corresponding to the wide table based on the data of the wide table primary key, the data of the current data table, and the data of the associated data table, and synchronize the data rows to the wide table.

[0059] The following describes the situation where three data tables A, B, and C are integrated into a wide table D and synchronized with the wide table:

[0060] Tables A, B, and C each have their primary keys PkeyA, PkeyB, and PkeyC, respectively. After being consolidated into wide table D, the primary key of wide table D is the combination of PkeyA, PkeyB, and PkeyC. The field that links Table A to Table B is keyB1, the field that links Table B to Table A is keyA, the field that links Table B to Table C is keyC, and the field that links Table C to Table B is keyB2. The data flows consuming Table A, Table B, and Table C are executed in Flink tasks 1, 2, and 3, respectively.

[0061] Flink Task 1: Consumes the data stream of Table A. This data stream contains PkeyA, all the data in Table A, and the keyB1 field associated with Table B. However, this data is missing data from Tables B and C from wide Table D. The required field data from Table B is queried using the keyB1 field. If no data from Table B is found, the keyB1 field is placed in the retry queue and the query is retried. After querying Table B's data, the query is then performed to determine whether the primary key data for Wide Table D is available. At this point, the primary key data for Wide Table D already contains data from PkeyA and PkeyB, but data from PkeyC is missing. Subsequently, when Table C's data is obtained by consuming the data stream, the data from Tables A, B, and C are written to the corresponding rows in Wide Table D based on PkeyA, PkeyB, and PkeyC.

[0062] Flink Task 2: Consumes the data stream from Table B. This data stream contains PkeyB, all data from Table B, the keyA field associated with Table A, and the keyC field associated with Table C. However, this data differs from the data in Tables A and C in wide Table D. The associated fields keyA and keyC are used to query the required field data from Tables A and C, respectively. If data from Tables A and C cannot be found, the associated fields keyA and keyC are placed in the retry queue and the query is retried. If data from Tables A and C is found, the primary key data for Table D, PkeyA, PkeyB, and PkeyC, is also found in wide Table D. This creates a row of data from Tables A, B, and C, which is then written to wide Table D using the wide table primary key. If data from one of tables A and C is found again, for example, data from table A is found again but data from table C is not, and subsequently the data from table C is obtained based on the data flow that consumes table C, the data from tables A, B, and C are written to the corresponding data rows in wide table D based on PkeyA, PkeyB, and PkeyC.

[0063] Flink Task 3: Consumes the data stream from Table C. This data stream contains PkeyC, all the data from Table A, and the keyB2 field associated with Table B. However, this data differs from the data in Tables A and B, which are missing from the data in Wide Table D. KeyB2 is used to query the required field data in Table B. If no data from Table B is found, keyB2 is placed in the retry queue and the query is retried. After querying Table B's data, the query is then performed to determine whether the primary key data for Wide Table D is available. At this point, PkeyB and PkeyC are already available for Wide Table D's primary key, but PkeyA is missing. Subsequently, when Table A's data is obtained by consuming Table A's data stream, the data from Tables A, B, and C are written to the corresponding rows in Wide Table D based on PkeyA, PkeyB, and PkeyC.

[0064] It should be noted that in this embodiment, due to front-end business requirements, multiple data tables of the synthesized wide table are all in the MySQL database, and the wide table is created in the Elasticsearch database based on the requirements of large or even massive data association queries. However, the wide table synchronization method of this embodiment is not limited to MySQL databases and Elasticsearch databases. Figure 2 As shown in the figure, a wide table is created before consuming the data stream of the current data table. The creation steps include:

[0065] Step S201: Determine a data table from the multiple data tables as a base table. The base table can be determined based on the actual business report requirements. For example, if a report dimension is a product detail dimension, each product represents a row of data, and an order corresponds to one product, the product detail table can be determined as the base table. Partial order information needs to be appended to each product detail to form a wide table.

[0066] Step S202: Integrate the data columns of the remaining data tables into the data columns of the base table. This means that the remaining data tables are spliced ​​onto the back of the base table to form the wide table, which includes all fields from each data table. Wide tables are created when there's a need for massive data-related queries. Prior to the creation of the wide table, each data table already had data. When the wide table is created, the existing data from each data table is also inserted into the wide table. Changes to the data generated by subsequent business operations on the data tables are synchronized to the wide table using steps S101 to S104.

[0067] Step S203: Determine the wide table primary key. The wide table primary key can be a composite field or the primary key of a table in a single table. The specific determination should be based on the actual business logic, as long as the wide table primary key can uniquely identify a row of data in the wide table. For example, in the case where A, B, and C are integrated into wide table D, the primary key of wide table D can be a combination of PkeyA, PkeyB, and PkeyC, or a combination of any two of them, or just one of them, such as PkeyA. Of course, the associated field between data table A and data table B is not necessarily the primary key PkeyB of data table B, but can also be other fields of data table B.

[0068] The retry queue is characterized by first-in, first-out (FIFO). The associated fields at the head of the queue are the first data to be entered, and are also the data with the longest history. To ensure that the associated fields corresponding to the data stream of the first generated data table are queried first, in this embodiment, before the data stream of the new data table is generated and consumed, the step of querying the data of the associated data table from the preset database based on the associated fields in the retry queue is triggered, i.e., step S102 is continued. Specifically, before the data stream of the new data table is generated and consumed, the associated fields are first sequentially retrieved from the head of the retry queue and queried. If the query is unsuccessful, they are then queued again, and the associated fields of the data stream of the new data table are then added to the end of the queue.

[0069] During the re-check process, if the query fails due to some exceptions, such as manually deleting the data that has just been updated in a data table, this embodiment proposes a retry elimination mechanism to avoid the waste of resources caused by endless re-checking.

[0070] Specifically, in step S104, the step of adding the associated field to a pre-created retry queue includes:

[0071] When the associated field enters the queue for the first time, the number of rechecks of the associated field is set to 0, and the associated field is added to the end of the retry queue;

[0072] If the associated field does not enter the queue for the first time, the number of rechecks of the associated field is increased by 1, and it is determined whether the number of rechecks of the associated field reaches a preset threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

[0073] By setting an elimination mechanism based on the number of rechecks, related fields in the retry queue are eliminated to avoid multiple queries. At the same time, a certain size of the retry queue is maintained to avoid wasting storage space.

[0074] The retry queue elimination mechanism in step S104 may also be the following: the step of adding the associated field to a pre-created retry queue includes:

[0075] When the associated field enters the queue for the first time, the recheck duration of the associated field is set to 0, the first entry time is recorded, and the associated field is added to the end of the retry queue;

[0076] In the case that this is not the first time that the associated field enters the queue, the recheck duration is set to the time difference between the current time and the first time of entering the queue, and it is determined whether the recheck duration of the associated field exceeds the preset duration threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

[0077] By setting an elimination mechanism based on the re-query timeout, related fields in the retry queue are eliminated to avoid timeout queries. At the same time, the retry queue is maintained at a certain size to avoid wasting storage space.

[0078] In the above two retry queue elimination mechanisms, since the associated fields are eliminated, the rows of the data corresponding to the eliminated associated fields in the wide table are empty. When the associated fields are eliminated, a warning message can also be generated to prompt the user.

[0079] The two aforementioned retry queue elimination mechanisms can be activated or deactivated based on actual circumstances. Specifically, before eliminating the associated field, the two aforementioned retry queue elimination mechanisms also include: querying the current queue length of the retry queue; if the current queue length is less than a preset length threshold, adding the associated field to the end of the retry queue; otherwise, eliminating the associated field. When the length of the retry queue exceeds the preset length threshold, the elimination mechanism is activated to prevent the retry queue from occupying too much storage space.

[0080] In the above two retry queue elimination mechanisms, the preset number threshold and the preset time threshold can also be determined according to the length of the retry queue. After the length of the retry queue exceeds the above preset length threshold, the longer the length, the smaller the values ​​of the preset number threshold and the preset time threshold. Conversely, the shorter the length, the larger the values ​​of the preset number threshold and the preset time threshold.

[0081] An embodiment of the present disclosure also provides a wide table synchronization method, such as Figure 3 Shown, including:

[0082] Step S301: Consume the data stream of the current data table, and extract the associated fields of the associated data table associated with the current data table from the data stream of the current data table.

[0083] Step S302: query the data of the associated data table from a preset database according to the associated fields.

[0084] Step S303: Determine whether the data of the associated data table and the data of the wide table primary key are found in the query. If so, execute step S304; otherwise, execute step S306.

[0085] Step S304: Acquire the data of the wide table primary key, the data of the current data table, and the data of the associated data table.

[0086] Step S305: Synchronize the data of the current data table, the data of the associated data table, and the data of the wide table primary key to the wide table.

[0087] Step S306: Add the associated field to a preset retry queue to re-query according to the queue.

[0088] Step S307: Determine whether the associated field in the retry queue can be eliminated according to the elimination mechanism. If so, execute step S308; otherwise, execute step S302.

[0089] Step S308: Eliminate the associated field from the retry queue, that is, the key field is no longer added to the retry queue.

[0090] The wide table synchronization device provided by the present disclosure is described below. The wide table synchronization device described below and the wide table synchronization method described above can be referenced to each other.

[0091] like Figure 4 The figure shows a schematic diagram of the structure of a wide table synchronization device provided by an embodiment of the present disclosure. In the device, a wide table is synthesized from multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The device includes:

[0092] Data stream consumption module 401 is used to consume the data stream of the current data table and extract the associated fields of the associated data table associated with the current data table from the data stream of the current data table. In this embodiment, the data streams corresponding to the multiple data tables in the composite wide table are consumed in different Flink tasks. Data stream consumption module 401 consumes one Flink task at a time.

[0093] The data query module 402 is configured to query the data in the associated data table from a preset database according to the associated fields.

[0094] The data synchronization module 403 is used to synchronize the data of the current data table, the data of the associated data table and the data of the wide table primary key to the wide table when the data of the associated data table and the data of the wide table primary key are queried, wherein the wide table primary key is determined when the wide table is created.

[0095] The data retry module 404 is used to add the associated field to a pre-created retry queue when no data in the associated data table is found, and continue to execute the step of querying the data of the associated data table from a preset database according to the associated field in the retry queue.

[0096] The data synchronization module 403 includes:

[0097] The data acquisition module is used to acquire the data of the wide table primary key, the data of the current data table and the data of the associated data table when the data of the wide table primary key is queried.

[0098] A wide table synchronization module is used to generate data rows corresponding to the wide table according to the data of the wide table primary key, the data of the current data table and the data of the associated data table, and synchronize the data rows to the wide table.

[0099] Optionally, the wide table synchronization device further includes:

[0100] The reference table determining module is configured to determine a data table from the plurality of data tables as a reference table.

[0101] The wide table generating module is used to integrate the data columns of the remaining data tables into the data columns of the reference table to form the wide table.

[0102] The primary key determination module is used to determine the primary key of the wide table.

[0103] Optionally, the wide table synchronization device also includes: a retry query trigger module, which is used to trigger the step of querying the data of the associated data table from the preset database according to the associated field in the retry queue before the data flow of the new data table is generated and consumed.

[0104] Optionally, the data retry module 404 is specifically used to set the number of rechecks of the associated field to 0 when the associated field enters the queue for the first time, and add the associated field to the end of the retry queue; when the associated field does not enter the queue for the first time, add 1 to the number of rechecks of the associated field, and determine whether the number of rechecks of the associated field reaches a preset number threshold. If so, eliminate the associated field; otherwise, add the associated field to the end of the retry queue.

[0105] Optionally, the data retry module 404 is specifically used to set the recheck time of the associated field to 0 when the associated field enters the queue for the first time, record the first entry time, and add the associated field to the end of the retry queue; when the associated field does not enter the queue for the first time, set the recheck time to the time difference between the current time and the first entry time, and determine whether the recheck time of the associated field exceeds a preset time threshold. If so, eliminate the associated field; otherwise, add the associated field to the end of the retry queue.

[0106] Optionally, the data retry module 404 is also specifically used to, before eliminating the associated field, also include: querying the current queue length of the retry queue; if the current queue length is less than a preset length threshold, adding the associated field to the end of the retry queue; otherwise, eliminating the associated field.

[0107] The wide table synchronization device provided by the present disclosure consumes the data streams corresponding to the multiple data tables that form the wide table in different Flink tasks during the wide table synchronization process. In this way, the data streams corresponding to the data tables are processed in separate Flink tasks without the need to store the previously arrived streams or wait for the subsequent streams to arrive before processing. Moreover, after consuming the data stream of the current data table and extracting the associated fields of the associated data table associated with the current data table, if the data of the associated data table cannot be queried from the database based on the associated fields, only the associated fields corresponding to the associated data table are added to the retry queue, and the entire previously arrived data stream is not stored, that is, the entire data stream of the current data table is not stored, thereby saving a large amount of storage space and achieving the effect that each Flink server node can fully utilize the CPU to process data.

[0108] Figure 5 The following is a schematic diagram of the physical structure of an electronic device, which may include: a processor 501, a communication interface 502, a memory 503, and a communication bus 504. The processor 501, the communication interface 502, and the memory 503 communicate with each other via the communication bus 504. The processor 501 may call the logic instructions in the memory 503 to execute a wide table synchronization method. In this method, the wide table is composed of multiple data tables, and the data streams corresponding to each of the multiple data tables are consumed in different stream processing tasks. The method includes:

[0109] The data stream of the current data table is consumed, and associated fields of an associated data table associated with the current data table are extracted from the data stream of the current data table.

[0110] The data in the associated data table is queried from a preset database according to the associated fields.

[0111] When the data of the associated data table and the data of the wide table primary key are queried, the data of the current data table, the data of the associated data table and the data of the wide table primary key are synchronized to the wide table, wherein the wide table primary key is determined when the wide table is created.

[0112] If no data in the associated data table is found, the associated field is added to a pre-created retry queue, and the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue is continued.

[0113] In addition, the logic instructions in the above-mentioned memory 503 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or the part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0114] On the other hand, the present disclosure further provides a computer program product, comprising a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the wide table synchronization method provided by the above methods. In this method, the wide table is composed of multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The method includes:

[0115] The data stream of the current data table is consumed, and associated fields of an associated data table associated with the current data table are extracted from the data stream of the current data table.

[0116] The data in the associated data table is queried from a preset database according to the associated fields.

[0117] When the data of the associated data table and the data of the wide table primary key are queried, the data of the current data table, the data of the associated data table and the data of the wide table primary key are synchronized to the wide table, wherein the wide table primary key is determined when the wide table is created.

[0118] If no data in the associated data table is found, the associated field is added to a pre-created retry queue, and the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue is continued.

[0119] In another aspect, the present disclosure further provides a non-transitory computer-readable storage medium having a computer program stored thereon. When executed by a processor, the computer program is implemented to perform the wide table synchronization method provided by the above methods. In this method, the wide table is composed of multiple data tables, and the data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The method includes:

[0120] The data stream of the current data table is consumed, and associated fields of an associated data table associated with the current data table are extracted from the data stream of the current data table.

[0121] The data in the associated data table is queried from a preset database according to the associated fields.

[0122] When the data of the associated data table and the data of the wide table primary key are queried, the data of the current data table, the data of the associated data table and the data of the wide table primary key are synchronized to the wide table, wherein the wide table primary key is determined when the wide table is created.

[0123] If no data in the associated data table is found, the associated field is added to a pre-created retry queue, and the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue is continued.

[0124] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, i.e., they may be located in one location or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of the present embodiment. Persons of ordinary skill in the art will be able to understand and implement the present invention without inventive effort.

[0125] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, or of course, by hardware. Based on this understanding, the essence of the above technical solution or the part that contributes to the existing technology can be embodied in the form of a software product. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or certain parts of the embodiments.

[0126] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present disclosure, rather than to limit them. Although the present disclosure has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the various embodiments of the present disclosure.

Claims

1. A wide table synchronization method, characterized in that: A wide table is composed of multiple data tables. The data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The method includes: Consuming the data stream of the current data table, and extracting the associated fields of the associated data table associated with the current data table from the data stream of the current data table; Querying the data of the associated data table from a preset database according to the associated fields; When the data of the associated data table and the data of the wide table primary key are queried, the data of the current data table, the data of the associated data table and the data of the wide table primary key are synchronized to the wide table, wherein the wide table primary key is determined when the wide table is created; If no data in the associated data table is found, the associated field is added to a pre-created retry queue, and the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue is continued.

2. The wide table synchronization method according to claim 1, characterized in that: When the data of the associated data table and the data of the wide table primary key are queried, synchronizing the data of the current data table, the data of the associated data table, and the data of the wide table primary key to the wide table includes: When the data of the wide table primary key is found in the query, the data of the wide table primary key, the data of the current data table, and the data of the associated data table are obtained; A data row corresponding to the wide table is generated according to the data of the wide table primary key, the data of the current data table, and the data of the associated data table, and the data row is synchronized to the wide table.

3. The wide table synchronization method according to claim 1, characterized in that: Before consuming the data stream of the current data table, the method further includes: determining a data table from the plurality of data tables as a reference table; Integrate the data columns of the remaining data tables into the data columns of the base table to form the wide table; Determine the primary key of the wide table.

4. The wide table synchronization method according to claim 1, characterized in that: Before the data flow of the new data table is generated and consumed, the step of querying the data of the associated data table from the preset database according to the associated field in the retry queue is triggered.

5. The wide table synchronization method according to claim 1, characterized in that: The step of adding the associated field to a pre-created retry queue includes: When the associated field enters the queue for the first time, the number of rechecks of the associated field is set to 0, and the associated field is added to the end of the retry queue; If the associated field does not enter the queue for the first time, the number of rechecks of the associated field is increased by 1, and it is determined whether the number of rechecks of the associated field reaches a preset threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

6. The wide table synchronization method according to claim 1, characterized in that: The step of adding the associated field to a pre-created retry queue includes: When the associated field enters the queue for the first time, the recheck duration of the associated field is set to 0, the first entry time is recorded, and the associated field is added to the end of the retry queue; In the case that this is not the first time that the associated field enters the queue, the recheck duration is set to the time difference between the current time and the first time of entering the queue, and it is determined whether the recheck duration of the associated field exceeds the preset duration threshold. If so, the associated field is eliminated; otherwise, the associated field is added to the end of the retry queue.

7. The wide table synchronization method according to claim 5 or 6, characterized in that: Before eliminating the associated field, the method further includes: The current queue length of the retry queue is queried. If the current queue length is less than a preset length threshold, the associated field is added to the end of the retry queue; otherwise, the associated field is eliminated.

8. A wide meter synchronization device, characterized in that: A wide table is composed of multiple data tables. The data streams corresponding to the multiple data tables are consumed in different stream processing tasks. The device includes: A data stream consumption module, configured to consume the data stream of the current data table and extract associated fields of an associated data table associated with the current data table from the data stream of the current data table; A data query module, configured to query the data in the associated data table from a preset database according to the associated fields; a data synchronization module, configured to synchronize the data of the current data table, the data of the associated data table, and the data of the wide table primary key to the wide table when the data of the associated data table and the data of the wide table primary key are queried, wherein the wide table primary key is determined when the wide table is created; The data retry module is used to add the associated field to a pre-created retry queue when no data in the associated data table is found, and continue to execute the step of querying the data in the associated data table from a preset database according to the associated field in the retry queue.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the steps of the wide table synchronization method according to any one of claims 1 to 7 are implemented.

10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the wide table synchronization method according to any one of claims 1 to 7 are implemented.

Images