Real-time wide table construction method and device based on relational database

Abstract

The invention provides a real-time wide table construction method and device based on a relational database, and the method comprises the steps: obtaining multi-dimensional order information data streams of a target order based on an order information collection system of the target order, carrying out the data stream merging of the order information data streams, obtaining the merged multi-stream data, and storing the merged multi-stream data in a database; the method comprises the following steps: determining a primary key of multi-stream data, grouping the multi-stream data based on the primary key, opening a sliding window and aggregating data in the sliding window to obtain corresponding aggregated data, and determining a master-slave database or a master-slave database cluster of a relational database pre-established based on the relational database, and updating corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data, acquiring a corresponding order information log from the master-slave database or the master-slave database cluster based on a preset log acquisition tool, and performing data processing on the order information log to obtain a corresponding real-time target wide table.

Description

Technical Field

[0001] This application relates to the technical field of data processing. Specifically, it relates to a method and device for constructing a real-time wide table based on a relational database. Background Art

[0002] A stream computing engine is a technology specifically used to process continuously flowing data streams. It can process data in real time or near real time and extract valuable information from it. The currently popular stream computing engine is Flink, but Flink native operators such as cogroup, join, and interval join have limitations in multi-stream processing and processing.

[0003] Currently, Flink DataStream can be used to solve the limitations of Flink native operators in multi-stream processing and processing. This is because Flink DataStream exposes a state management interface externally, enabling developers to perform necessary state storage development according to needs. Moreover, compared with Flink SQL, it has a lightweight state, strong operability, and low resource consumption. Therefore, many important scenarios are mostly implemented based on DataStream.

[0004] However, this implementation method based on DataStream has a relatively high implementation cost and great development difficulty. Summary of the Invention

[0005] In view of this, the purpose of this application is to provide a method and device for constructing a real-time wide table based on a relational database. By using the primary key to group the multi-stream data merged from the multi-dimensional order information data streams of the target order, open a sliding window, and aggregate the data within the sliding window, update the corresponding fields in the master-slave database or master-slave database cluster through the aggregated data, and collect the order information logs from the master-slave database or master-slave database cluster through a log collection tool for data processing to obtain a real-time target wide table. Combining the characteristics that the relational database table can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, which is easier to implement, thereby reducing the development difficulty and saving the development cost.

[0006] In a first aspect, an embodiment of this application provides a method for constructing a real-time wide table based on a relational database, and the method includes:

[0007] Based on a collection system for order information of a target order, obtain multi-dimensional order information data streams of the target order, and perform data stream merging on the order information data streams to obtain merged multi-stream data; wherein, the multi-dimensional order information data stream represents an order status table of the target order, and the order status table at least includes an order basic information data stream, an order extended information data stream, and an order logistics information data stream;

[0008] Determine the primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and aggregate the data within the sliding window to obtain corresponding aggregated data;

[0009] Determine the master-slave database or the master-slave database cluster of the relational database pre-built based on the relational database, and update the corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data;

[0010] Obtain the corresponding order information log from the master-slave database or the master-slave database cluster based on a preset log collection tool, and process the order information log to obtain a corresponding real-time target wide table.

[0011] In a possible implementation manner, the grouping, opening of a sliding window, and aggregation of data within the sliding window of the multi-stream data based on the primary key to obtain corresponding aggregated data include:

[0012] Group the multi-stream data based on the primary key so that the multi-stream data is assigned to different partitions, and obtain data for multiple partitions; wherein, each partition represents all order information of an order; the data within each partition has the same primary key;

[0013] Create multiple sliding windows on the partition, and aggregate the order information in the partition within the sliding window to obtain corresponding aggregated data; wherein, each partition has multiple sliding windows, and the sliding windows correspond to the aggregated data one by one.

[0014] In a possible implementation manner, the master-slave database at least includes order basic information, order extended information, and order logistics information; the master-slave database cluster includes multiple shards; each shard at least includes order basic information, order extended information, and order logistics information;

[0015] The master-slave database includes a master database and a slave database, and the master-slave database includes a master cluster and a slave cluster; the master cluster includes multiple master shards, the slave cluster includes multiple slave shards, the number of master shards and slave shards is equal; the master shards and the slave shards correspond to each other one by one.

[0016] In a possible implementation manner, the creating of multiple sliding windows on the partition includes:

[0017] Determine the window size and sliding step of the sliding window; wherein, the window size represents the time range covered by the window, and the sliding step represents the frequency of window movement;

[0018] Construct a corresponding sliding window based on the size and sliding step of the sliding window.

[0019] In a possible implementation manner, the updating the corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data includes:

[0020] For the master-slave database, update the corresponding fields in the order status table of the master database of the master-slave database based on the aggregated data, and perform master-slave synchronization between the updated master database and the slave database;

[0021] Or,

[0022] For the master-slave database cluster, based on the aggregated data, update the corresponding fields in the order status table of the master shard of the master cluster of the master-slave database cluster through a preset database middleware, and perform data synchronization between the updated master shard and the corresponding slave shard.

[0023] In a possible implementation manner, the data processing of the order information log to obtain a corresponding real-time target wide table includes:

[0024] Send the order information log to a preset distributed stream processing platform;

[0025] Based on the preset distributed stream processing platform, perform data processing on the order information log to obtain a corresponding target wide table.

[0026] In a possible implementation manner, the method further includes:

[0027] Determine the validity period of the target order;

[0028] Based on the validity period of the target order, determine the cold status data that no longer needs to be maintained in the order status table of the slave database or the slave database cluster, and perform scheduled cleaning.

[0029] In a second aspect, an embodiment of the present application further provides a real-time wide table construction device based on a relational database. The device includes:

[0030] A first processing module, configured to obtain a multi-dimensional order information data stream of the target order based on a collection system of order information of the target order, and perform data stream merging on the order information data stream to obtain merged multi-stream data; wherein, the multi-dimensional order information data stream represents an order status table of the target order, and the order status table at least includes an order basic information data stream, an order extended information data stream, and an order logistics information data stream;

[0031] A second processing module, configured to determine a primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and perform data aggregation within the sliding window to obtain corresponding aggregated data;

[0032] An update module, configured to determine a master-slave database or a master-slave database cluster of the relational database pre-built based on a relational database, and update corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data;

[0033] An acquisition module, configured to obtain corresponding order information logs from the master-slave database or the master-slave database cluster based on a preset log collection tool, and perform data processing on the order information logs to obtain corresponding real-time target wide tables.

[0034] In a possible implementation manner, the second processing module is specifically configured to:

[0035] Group the multi-stream data based on the primary key, so that the multi-stream data is assigned to different partitions, and obtain data of multiple partitions; wherein, each partition represents all order information of an order; data within each partition has the same primary key;

[0036] Create multiple sliding windows on the partition, and perform aggregation on the order information in the partition within the sliding window to obtain corresponding aggregated data; wherein, each partition has multiple sliding windows, and the sliding windows correspond to the aggregated data one by one.

[0037] In a possible implementation manner, the master-slave database at least includes order basic information, order extended information, and order logistics information; the master-slave database cluster includes multiple shards; each shard at least includes order basic information, order extended information, and order logistics information;

[0038] The master-slave database includes a master database and a slave database, and the master-slave database includes a master cluster and a slave cluster; the master cluster includes multiple master shards, the slave cluster includes multiple slave shards, and the number of the master shards is equal to the number of the slave shards; the master shards and the slave shards correspond to each other one by one.

[0039] In a possible implementation manner, the second processing module is specifically configured to:

[0040] Determine the window size and sliding step of the sliding window; wherein, the window size represents the time range covered by the window, and the sliding step represents the frequency of window movement;

[0041] Construct a corresponding sliding window based on the size and sliding step of the sliding window.

[0042] In a possible implementation manner, the update module is specifically configured to:

[0043] For the master-slave database, based on the aggregated data, update the corresponding fields in the order status table of the master database of the master-slave database, and perform master-slave synchronization between the updated master database and the slave database;

[0044] Or,

[0045] For the master-slave database cluster, based on the aggregated data, update the corresponding fields in the order status table of the master shard of the master cluster of the master-slave database cluster through a preset database middleware, and synchronize the data between the updated master shard and the corresponding slave shard.

[0046] In a possible implementation manner, the acquisition module is specifically configured to:

[0047] Send the order information log to a preset distributed stream processing platform;

[0048] Based on the preset distributed stream processing platform, perform data processing on the order information log to obtain a corresponding target wide table.

[0049] In a possible implementation manner, the real-time wide table construction device based on a relational database of the present application further includes:

[0050] A determination module, configured to determine the validity period of the target order;

[0051] A cleaning module, configured to determine the cold status data that no longer needs to be maintained in the order status table of the slave database or the slave database cluster based on the validity period of the target order, and perform scheduled cleaning.

[0052] In a third aspect, an embodiment of the present application provides an electronic device, including: a processor, a storage medium, and a bus. The storage medium stores machine-readable instructions executable by the processor. When the electronic device runs, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the real-time wide table construction method based on a relational database according to any one of the first aspects.

[0053] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, on which a computer program is stored. When the computer program is run by a processor, it executes the steps of the real-time wide table construction method based on a relational database according to any one of the first aspects.

[0054] A real-time wide table construction method and device based on a relational database provided by an embodiment of the present application, a data acquisition system for order information of a target order, obtains multi-dimensional order information data streams of the target order, merges the order information data streams to obtain merged multi-stream data, determines the primary key of the multi-stream data, groups the multi-stream data based on the primary key, opens a sliding window, and aggregates the data within the sliding window to obtain corresponding aggregated data, determines the master-slave database or master-slave database cluster of the relational database pre-established based on the relational database, updates the corresponding fields in the master-slave database or master-slave database cluster based on the aggregated data, obtains the corresponding order information log from the master-slave database or master-slave database cluster based on a preset log acquisition tool, and processes the order information log to obtain the corresponding real-time target wide table. In the present application, the multi-stream data merged from multi-dimensional order information data streams is grouped, a sliding window is opened, and the data within the sliding window is aggregated through the primary key, the corresponding fields in the master-slave database or master-slave database cluster are updated through the aggregated data, and the order information log is collected from the master-slave database or master-slave database cluster through the log acquisition tool for data processing to obtain the real-time target wide table. Combining the characteristic that the table of the relational database can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, the operation is easier to implement, thereby reducing the development difficulty and saving the development cost.

[0055] To make the above objects, features, and advantages of the present application more obvious and understandable, the following specifically gives preferred embodiments and cooperates with the attached drawings for detailed description as follows. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] To more clearly illustrate the technical solutions of the embodiments of the present application, the following will briefly introduce the drawings required to be used in the embodiments. It should be understood that the following drawings only show some embodiments of the present application, and therefore should not be regarded as limiting the scope. For those of ordinary skill in the art, without creative efforts, other related drawings can also be obtained based on these drawings.

[0057] Figure 1 is a flowchart of a real-time wide table construction method based on a relational database provided by an embodiment of the present application;

[0058] Figure 2 is a schematic flowchart of real-time wide table construction based on a master-slave database of a relational database;

[0059] FIG. 3(a) is a partial flowchart of real-time wide table construction based on a master-slave database cluster of a relational database Figure 1 ;

[0060] FIG. 3(b) is a partial flowchart of real-time wide table construction based on a master-slave database cluster of a relational databaseFigure 2 ;

[0061] Figure 4 is a schematic structural diagram of a real-time wide table construction device based on a relational database provided by an embodiment of the present application;

[0062] Figure 5 is a schematic structural diagram of an electronic device provided by an embodiment of the present application. Detailed implementation manners

[0063] To make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application. It should be understood that the accompanying drawings in the present application are only for the purposes of illustration and description, and are not used to limit the protection scope of the present application. In addition, it should be understood that the schematic drawings are not drawn to scale. The flowcharts used in the present application illustrate operations implemented according to some embodiments of the present application. It should be understood that the operations in the flowcharts may not be implemented in sequence, and steps without logical context may be reversed or implemented simultaneously. In addition, those skilled in the art may add one or more other operations to the flowchart or remove one or more operations from the flowchart under the guidance of the content of the present application.

[0064] In addition, the described embodiments are only some embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application usually described and illustrated in the accompanying drawings here may be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present application provided in the accompanying drawings is not intended to limit the scope of the present application to be protected, but only represents selected embodiments of the present application. All other embodiments obtained by those skilled in the art based on the embodiments of the present application without creative efforts fall within the protection scope of the present application.

[0065] It should be noted that the term "including" will be used in the embodiments of the present application to indicate the existence of the subsequently stated features, but does not exclude the addition of other features.

[0066] Considering that the stream computing engine is a technology specifically used to process continuously flowing data streams, it can process data in real time or near real time and extract valuable information from it. The currently popular stream computing engine is Flink, but Flink native operators such as cogroup, join, and interval join have limitations in multi-stream processing and processing.

[0067] Currently, the limitations of native Flink operators in multi-stream processing can be addressed through Flink DataStream. This is because Flink DataStream exposes a state management interface, enabling developers to perform necessary state storage development as needed. Moreover, compared with Flink SQL, it has a lightweight state, strong operability, and low resource consumption. Therefore, many important scenarios are mostly implemented based on DataStream. However, this implementation method based on DataStream has a relatively high implementation cost and great development difficulty.

[0068] To address this problem, the present application provides a method and apparatus for constructing a real-time wide table based on a relational database. The multi-stream data merged from the data streams of order information with multiple dimensions is grouped, a sliding window is opened, and data aggregation within the sliding window is performed through the primary key. The corresponding fields in the master-slave database or master-slave database cluster are updated through the aggregated data, and the order information logs are collected from the master-slave database or master-slave database cluster through a log collection tool for data processing to obtain a real-time target wide table. Combining the characteristics that the table of the relational database can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, which is easier to implement and thus reduces the development difficulty and saves the development cost.

[0069] Figure 1 is a flowchart of the method for constructing a real-time wide table based on a relational database according to an embodiment of the present application. As Figure 1 shown, the method for constructing a real-time wide table based on a relational database according to an embodiment of the present application may specifically include:

[0070] S101. Based on the acquisition system of the order information of the target order, obtain the multi-dimensional order information data stream of the target order, and perform data stream merging on the order information data stream to obtain the merged multi-stream data.

[0071] S102. Determine the primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and perform data aggregation within the sliding window to obtain the corresponding aggregated data.

[0072] S103. Determine the master-slave database or master-slave database cluster of the relational database pre-established based on the relational database, and update the corresponding fields in the master-slave database or master-slave database cluster based on the aggregated data.

[0073] S104. Based on a preset log collection tool, obtain the corresponding order information logs from the master-slave database or master-slave database cluster, and perform data processing on the order information logs to obtain the corresponding real-time target wide table.

[0074] In the above real-time wide table construction method based on a relational database, the multi-stream data after merging the data streams of multi-dimensional order information is grouped, a sliding window is opened, and data aggregation is performed within the sliding window through the primary key. The corresponding fields in the master-slave database or the master-slave database cluster are updated through the aggregated data, and the order information logs are collected from the master-slave database or the master-slave database cluster through a log collection tool for data processing to obtain a real-time target wide table. Combining the characteristics that the relational database table can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, which is easier to implement, thereby reducing the development difficulty and saving the development cost.

[0075] The above exemplary steps of the embodiments of the present application will be described below with reference to specific examples:

[0076] S101. Based on the order information collection system of the target order, obtain the multi-dimensional order information data stream of the target order, and perform data stream merging on the order information data stream to obtain the merged multi-stream data.

[0077] In the embodiments of the present application, the target order is the order for which a wide table (data warehouse wide table) needs to be constructed. For example, a marketing order. The target order is at least one order, and the collection system is the system from which the target order comes. For example, an order system. The order information data stream is the data of the order information of the target order. The multi-dimensional order information data stream represents the order status table of the target order, and the order status table at least includes the order basic information data stream, the order extended information data stream, and the order logistics information data stream. That is, the order information data stream at least includes the order basic information data stream, the order extended information data stream, and the order logistics information data stream. The order information collection system based on the target order obtains the multi-dimensional order information data stream of the target order, and performs data stream merging on the order information data stream to obtain the merged multi-stream data for subsequent processing. For example, as Figure 2 shown in FIGS. 3(a) and 3(b), where the process in FIG. 3(a) is immediately followed by the process in FIG. 3(b), obtain the order basic information data stream, the order extended information data stream, and the order logistics information data stream, and perform data stream merging (Union).

[0078] It can be understood that data stream merging can also be understood as data stream alignment. Simply put, it is to put the multi-dimensional order information data stream into a pipeline.

[0079] S102. Determine the primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and perform data aggregation within the sliding window to obtain the corresponding aggregated data.

[0080] Optionally, the primary key of the multi-stream data is determined based on the application scenario of the target order. For example, if the application scenario of the target order is a logistics order, the order number of the target order can be used as the primary key of the multi-stream data.

[0081] In the embodiment of the present application, for the multi-stream data obtained in step S101, the primary key of the multi-stream data is determined, for example, the order number is used as the primary key; the multi-stream data is grouped according to the determined primary key, a sliding window is opened, and the data in the sliding window is aggregated to obtain corresponding aggregated data for subsequent processing. Figure 2 As shown in Figures 3(a) and 3(b). As a possible implementation, when grouping multi-stream data based on primary keys, opening sliding windows, and aggregating data within sliding windows to obtain corresponding aggregated data, multi-stream data is grouped based on primary keys so that multi-stream data is assigned to different partitions to obtain data from multiple partitions; multiple sliding windows are created on the partitions, and order information in the partitions is aggregated within the sliding windows to obtain corresponding aggregated data. Each partition represents all order information of an order; the data within each partition has the same primary key; each partition has multiple sliding windows, and the sliding windows correspond to the aggregated data one by one.

[0082] For example, Figure 2 As shown in Figure 3(a) and Figure 3(b), after the order information data stream is merged, keyBy grouping is performed so that the merged multi-stream data is assigned to different partitions to obtain data from multiple partitions. That is, all information of the same order is placed in one partition, so that multiple orders are placed in multiple partitions respectively. Then, a sliding window is opened on the grouped partitions, that is, the partitioned data is segmented. For example, the processing time of the order determined by the order information is from 11:00 to 12:00, that is, 1 hour. If the sliding window is 1 minute, it means that a sliding window is segmented every minute. The first sliding window is from 11:00 to 11:01, and the other sliding windows are analogous. In this 1 hour, 60 windows can be segmented in sequence. Each time a sliding window is created, data is aggregated in the sliding window to obtain the aggregated data of the sliding window (or every minute), thereby realizing data aggregation of each sliding window in sequence.

[0083] Optionally, when multiple sliding windows are created on the partition, the window size and sliding step of the sliding window are determined; and the corresponding sliding window is constructed based on the sliding window size and sliding step. The window size represents the time range covered by the window, and the sliding step represents the frequency of the window movement.

[0084] S103. Determine the master-slave database or master-slave database cluster of the relational database pre-built based on the relational database, and update the corresponding fields in the master-slave database or master-slave database cluster based on the aggregated data.

[0085] In the embodiment of the present application, a master-slave database or a master-slave database cluster of a relational database is pre-built based on the relational database. Among them, the relational database can be Oracle, MySQL, PostgreSQL, etc. Update the corresponding fields in the master-slave database or master-slave database cluster according to the aggregated data obtained in step S102 for subsequent processing.

[0086] Among them, the master-slave database and the master-slave database cluster represent the relational database. The master-slave database is used in scenarios with a small amount of data, and the master-slave database cluster is used in scenarios with a large amount of data, and is specifically selected according to the application scenario. For example, Figure 2 represents the wide table construction process under the master-slave database, and FIGS. 3(a) and 3(b) represent the wide table construction process under the master-slave database cluster. The master-slave database at least includes order basic information, order extended information, and order logistics information; the master-slave database cluster includes multiple shards; each shard at least includes order basic information, order extended information, and order logistics information; the master-slave database includes a master database and a slave database, and the master-slave database includes a master cluster and a slave cluster; the master cluster includes multiple master shards, the slave cluster includes multiple slave shards, and the number of master shards and slave shards is equal; the master shards and slave shards correspond one by one. For example, as Figure 2 shown in FIGS. 3(a) and 3(b), the master cluster and the slave cluster each include 5 corresponding shards.

[0087] Optionally, when updating the corresponding fields in the master-slave database or master-slave database cluster based on the aggregated data, for the master-slave database, update the corresponding fields in the order status table of the master database of the master-slave database based on the aggregated data, and perform master-slave synchronization between the updated master database and the slave database; for example, as Figure 2 shown. Or, for the master-slave database cluster, based on the aggregated data, update the corresponding fields in the order status table of the master shards of the master cluster of the master-slave database cluster through a preset database middleware, and synchronize the data between the updated master shards and the corresponding slave shards; for example, as shown in FIGS. 3(a) and 3(b).

[0088] S104. Obtain the corresponding order information log from the master-slave database or master-slave database cluster based on a preset log collection tool, and perform data processing on the order information log to obtain the corresponding real-time target wide table.

[0089] In the embodiments of the present application, the order information log represents the log of the order whose information has changed, that is, the log of the order that has been modified and updated. The log collection tool is a tool for collecting the logs of a pre-set collection database or database cluster. For example, OGG, Canal, Flink CDC, etc. The corresponding order information log is collected from the built master-slave database or master-slave database cluster through the log collection tool, and the corresponding real-time target wide table is obtained by processing the data of the order information log, thereby completing the construction of the real-time wide table based on the relational database.

[0090] Optionally, when processing the data of the order information log to obtain the corresponding real-time target wide table, the order information log is sent to a pre-set distributed stream processing platform; the data of the order information log is processed based on the pre-set distributed stream processing platform to obtain the corresponding target wide table. For example, as Figure 2 shown in FIGS. 3(a) and 3(b), the order information log, such as the Binlog log, is sent to the distributed stream processing platform Kafka to process the data of the Binlog log according to the distributed stream processing platform Kafka to obtain the corresponding order detail data stream, that is, the data warehouse wide table.

[0091] The method for constructing a real-time wide table based on a relational database provided by the embodiments of the present application is based on a collection system of order information of a target order, obtains a multi-dimensional order information data stream of the target order, merges the data streams of the order information, obtains the merged multi-stream data, determines the primary key of the multi-stream data, groups the multi-stream data based on the primary key, opens a sliding window and aggregates the data within the sliding window to obtain the corresponding aggregated data, determines the master-slave database or master-slave database cluster of the relational database pre-built based on the relational database, updates the corresponding fields in the master-slave database or master-slave database cluster based on the aggregated data, obtains the corresponding order information log from the master-slave database or master-slave database cluster based on the pre-set log collection tool, and processes the data of the order information log to obtain the corresponding real-time target wide table. The method for constructing a real-time wide table based on a relational database of the present application groups, opens a sliding window and aggregates the data within the sliding window for the multi-stream data merged from the multi-dimensional order information data stream through the primary key, updates the corresponding fields in the master-slave database or master-slave database cluster through the aggregated data, and collects the order information log from the master-slave database or master-slave database cluster through the log collection tool to process the data to obtain the real-time target wide table. Combining the characteristics that the table of the relational database can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, the operation is easier to implement, thereby reducing the development difficulty and saving the development cost.

[0092] Further, determine the validity period of the target order; based on the validity period of the target order, determine the cold status data that no longer needs to be maintained in the order status table of the database or database cluster, and perform scheduled cleaning. Here, the validity period is the deadline for the end of the target order. For example, if the deadline of an order is 5 days, it means that the order must be completed within 5 days. Then, data older than 5 days is no longer needed because, at this time, the data of the order cannot change anymore and no longer needs to be updated.

[0093] Optionally, the validity period of the target order can be determined according to the application scenario of the target order.

[0094] Specifically, if the time span of the multi-stream data is relatively narrow, the cold status data that no longer needs to be maintained in the order status table of the database or database cluster can be cleaned regularly. For example, if the validity period of the order is 5 days, the data updated 5 days ago in the slave database or slave cluster can be cleaned regularly. If the validity period of the order is 30 days, the data updated 30 days ago can be cleaned regularly.

[0095] Thus, by regularly cleaning the cold status data in the database or database cluster, the pressure on the database can be reduced.

[0096] In summary, this application realizes the multi-stream primary key association by virtue of the characteristics of easy collection of relational database logs and partial updatability of tables. The latest status of the wide table is maintained in the relational database. After different data streams converge, according to the built master-slave database architecture / master-slave database cluster, the corresponding different fields in the order status table of the master database / master cluster are updated in batches as needed to maintain the latest status of the wide table, and a mature log collection tool is used to send the Binlog logs of the slave database or slave cluster to Kafka to realize the merging and widening of multi-stream data by primary key. Among them, Figure 1 Realizing the real-time wide table processing of data by virtue of the master-slave architecture of the relational database is more suitable for scenarios with a small amount of data; Figure 2 Realizing the real-time wide table processing of data by virtue of the master-slave cluster architecture of the relational database is suitable for scenarios with a relatively large amount of data.

[0097] It should be noted that a method for constructing a real-time wide table based on a relational database in this application is also a method for constructing a real-time data warehouse wide table based on a relational database.

[0098] Figure 4 It is a structural schematic diagram of a device for constructing a real-time wide table based on a relational database provided by an embodiment of this application. As Figure 4 shown, the device 400 for constructing a real-time wide table based on a relational database in the embodiment of this application may specifically include:

[0099] The first processing module 401 is used to obtain the multi-dimensional order information data stream of the target order based on the acquisition system of the order information of the target order, and merge the data streams of the order information to obtain the merged multi-stream data; wherein, the multi-dimensional order information data stream represents the order status table of the target order, and the order status table at least includes the order basic information data stream, the order extended information data stream, and the order logistics information data stream.

[0100] The second processing module 402 is used to determine the primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and aggregate the data within the sliding window to obtain the corresponding aggregated data.

[0101] The update module 403 is used to determine the master-slave database or the master-slave database cluster of the relational database pre-built based on the relational database, and update the corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data.

[0102] The acquisition module 404 is used to obtain the corresponding order information log from the master-slave database or the master-slave database cluster based on the preset log acquisition tool, and process the order information log to obtain the corresponding real-time target wide table.

[0103] In a possible implementation manner, the second processing module is specifically used for:

[0104] Group the multi-stream data based on the primary key, so that the multi-stream data is assigned to different partitions to obtain the data of multiple partitions; wherein, each partition represents all the order information of an order; the data within each partition has the same primary key;

[0105] Create multiple sliding windows on the partitions, and aggregate the order information in the partitions within the sliding windows to obtain the corresponding aggregated data; wherein, each partition has multiple sliding windows, and the sliding windows correspond to the aggregated data one by one.

[0106] In a possible implementation manner, the master-slave database at least includes order basic information, order extended information, and order logistics information; the master-slave database cluster includes multiple shards; each shard at least includes order basic information, order extended information, and order logistics information;

[0107] The master-slave database includes a master database and a slave database, and the master-slave database includes a master cluster and a slave cluster; the master cluster includes multiple master shards, the slave cluster includes multiple slave shards, and the number of master shards and slave shards is equal; the master shards and the slave shards correspond to each other one by one.

[0108] In a possible implementation manner, the second processing module is specifically used for:

[0109] Determine the window size and sliding step of the sliding window; wherein, the window size represents the time range covered by the window, and the sliding step represents the frequency of window movement;

[0110] Construct a corresponding sliding window based on the size and sliding step of the sliding window.

[0111] In a possible implementation manner, the update module is specifically configured to:

[0112] For the master-slave database, update the corresponding fields in the order status table of the master database of the master-slave database based on the aggregated data, and perform master-slave synchronization on the updated master database and slave database;

[0113] Or,

[0114] For the master-slave database cluster, based on the aggregated data, update the corresponding fields in the order status table of the master shard of the master cluster of the master-slave database cluster through a preset database middleware, and synchronize the data between the updated master shard and the corresponding slave shard.

[0115] In a possible implementation manner, the acquisition module is specifically configured to:

[0116] Send the order information log to a preset distributed stream processing platform;

[0117] Perform data processing on the order information log based on the preset distributed stream processing platform to obtain a corresponding target wide table.

[0118] In a possible implementation manner, the real-time wide table construction device based on a relational database of the present application further includes:

[0119] A determination module, configured to determine the validity period of the target order;

[0120] A cleaning module, configured to determine the cold status data that no longer needs to be maintained in the order status table of the slave database or the slave database cluster based on the validity period of the target order, and perform scheduled cleaning.

[0121] The real-time wide table construction device based on a relational database provided by an embodiment of the present application, through a data acquisition system for order information of a target order, obtains a multi-dimensional order information data stream of the target order, merges the data streams of the order information, obtains merged multi-stream data, determines the primary key of the multi-stream data, groups the multi-stream data based on the primary key, opens a sliding window, and aggregates the data within the sliding window to obtain corresponding aggregated data, determines a master-slave database or a master-slave database cluster of a relational database pre-established based on the relational database, updates corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data, obtains corresponding order information logs from the master-slave database or the master-slave database cluster based on a preset log acquisition tool, and processes the order information logs to obtain a corresponding real-time target wide table. The real-time wide table construction device based on a relational database of the present application groups, opens a sliding window, and aggregates the data within the sliding window for the multi-stream data merged from the multi-dimensional order information data stream through the primary key, updates the corresponding fields in the master-slave database or the master-slave database cluster through the aggregated data, and collects the order information logs from the master-slave database or the master-slave database cluster through the log acquisition tool for data processing to obtain a real-time target wide table. Combining the feature that the table of the relational database can be partially updated as needed, the real-time construction of the data warehouse wide table is carried out through the relational database, and the operation is easier to implement, thereby reducing the development difficulty and saving the development cost.

[0122] As Figure 5 shown, an electronic device 500 provided by an embodiment of the present application includes: a processor 501, a memory 502, and a bus. The memory 502 stores machine-readable instructions executable by the processor 501. When the electronic device runs, the processor 501 communicates with the memory 502 through the bus. The processor 501 executes the machine-readable instructions to perform the steps of the real-time wide table construction method based on a relational database as described above.

[0123] Specifically, the above-mentioned memory 502 and processor 501 can be general memories and processors, which are not specifically limited here. When the processor 501 runs the computer program stored in the memory 502, it can execute the real-time wide table construction method based on a relational database as described above.

[0124] Corresponding to the above real-time wide table construction method based on a relational database, an embodiment of the present application further provides a computer-readable storage medium. A computer program is stored on the computer-readable storage medium. When the computer program is run by a processor, it executes the steps of the real-time wide table construction method based on a relational database as described above.

[0125] Those skilled in the art can clearly understand that for the convenience and conciseness of description, the specific working processes of the systems and devices described above can refer to the corresponding processes in the method embodiments, and will not be elaborated herein. In the several embodiments provided in the present application, it should be understood that the disclosed systems, devices, and methods can be implemented in other ways. The device embodiments described above are merely illustrative. For example, the division of the modules is only a logical function division, and there can be other division methods in actual implementation. For another example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the couplings, direct couplings, or communication connections shown or discussed among each other can be through some communication interfaces. The indirect couplings or communication connections of the devices or modules can be in electrical, mechanical, or other forms.

[0126] The modules described as separate components may or may not be physically separated. The components shown as modules may or may not be physical units, that is, they can be located in one place or distributed to multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0127] In addition, in each embodiment of the present application, the functional units can be integrated in a processing unit, or each unit can exist physically alone, or two or more units can be integrated in one unit.

[0128] If the functions are implemented in the form of software function units and sold or used as independent products, they can be stored in a non-volatile computer-readable storage medium executable by a processor. Based on such an understanding, the technical solution of the present application, in essence, or the part that contributes to the prior art or a part of this technical solution can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the deployment methods described in each embodiment of the present application. The aforementioned storage medium includes: various media such as USB flash drives, mobile hard disks, ROM, RAM, magnetic disks, or optical discs that can store program codes.

[0129] The above is only the specific implementation manner of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art can easily think of changes or substitutions within the technical scope disclosed in the present application, and all should be covered by the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A real-time wide table construction method based on a relational database, characterized in that: The method comprises: Based on the order information collection system of the target order, a multi-dimensional order information data stream of the target order is obtained, and the order information data stream is merged to obtain the merged multi-stream data; wherein the multi-dimensional order information data stream represents an order status table of the target order, and the order status table at least includes an order basic information data stream, an order extension information data stream and an order logistics information data stream; Determine a primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and aggregate the data in the sliding window to obtain corresponding aggregated data; Determine a master-slave database or a master-slave database cluster of the relational database pre-built based on the relational database, and update corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data; Based on a preset log collection tool, the corresponding order information log is obtained from the master-slave database or the master-slave database cluster, and data processing is performed on the order information log to obtain a corresponding real-time target wide table.

2. The method according to claim 1, characterized in that The grouping of the multi-stream data based on the primary key, opening a sliding window, and aggregating the data in the sliding window to obtain corresponding aggregated data includes: The multi-stream data is grouped based on the primary key so that the multi-stream data is allocated to different partitions to obtain data of multiple partitions; wherein each partition represents all order information of an order; and the data in each partition has the same primary key; A plurality of sliding windows are created on the partitions, and the order information in the partitions is aggregated within the sliding windows to obtain corresponding aggregated data; wherein each partition has a plurality of sliding windows, and the sliding windows correspond to the aggregated data one by one.

3. The method according to claim 2, characterized in that The master-slave database includes at least basic order information, order extension information, and order logistics information; the master-slave database cluster includes multiple shards; each shard includes at least basic order information, order extension information, and order logistics information; The master-slave database includes a master database and a slave database, and the master-slave database includes a master cluster and a slave cluster; the master cluster includes a plurality of master shards, and the slave cluster includes a plurality of slave shards, and the number of the master shards and the number of the slave shards are equal; The master shards correspond to the slave shards one by one.

4. The method according to claim 3, characterized in that The step of creating a plurality of sliding windows on the partitions includes: Determine the window size and sliding step length of the sliding window; wherein the window size represents the time range covered by the window, and the sliding step length represents the frequency of window movement; A corresponding sliding window is constructed based on the size of the sliding window and the sliding step size.

5. The method according to claim 4, characterized in that The updating of the corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data includes: For the master-slave database, the corresponding fields in the order status table of the master database of the master-slave database are updated based on the aggregated data, and the updated master database and the slave database are synchronized; or, For the master-slave database cluster, based on the aggregated data, the corresponding fields in the order status table of the master shard of the master cluster of the master-slave database cluster are updated through the preset database middleware, and the updated master shard is synchronized with the corresponding slave shard.

6. The method according to claim 5, characterized in that The data processing of the order information log to obtain a corresponding real-time target wide table includes: Sending the order information log to a preset distributed stream processing platform; The order information log is processed based on a preset distributed stream processing platform to obtain a corresponding target wide table.

7. The method according to claim 6, characterized in that The method further comprises: Determining the validity period of the target order; Based on the validity period of the target order, the cold state data that no longer needs to be maintained in the order state table of the slave database or the slave database cluster is determined, and regular cleaning is performed.

8. A real-time wide table construction device based on a relational database, characterized in that: The device comprises: A first processing module is used to obtain a multi-dimensional order information data stream of the target order based on an order information collection system of the target order, and merge the order information data streams to obtain merged multi-stream data; wherein the multi-dimensional order information data stream represents an order status table of the target order, and the order status table at least includes an order basic information data stream, an order extension information data stream, and an order logistics information data stream; A second processing module, configured to determine a primary key of the multi-stream data, group the multi-stream data based on the primary key, open a sliding window, and aggregate data within the sliding window to obtain corresponding aggregated data; An updating module, used for determining a master-slave database or a master-slave database cluster of the relational database pre-built based on the relational database, and updating corresponding fields in the master-slave database or the master-slave database cluster based on the aggregated data; The acquisition module is used to acquire the corresponding order information log from the master-slave database or the master-slave database cluster based on a preset log collection tool, and perform data processing on the order information log to obtain the corresponding real-time target wide table.

9. An electronic device, characterized in that: include: A processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor and the memory communicate via the bus, and when the machine-readable instructions are executed by the processor, the steps of the real-time wide table construction method based on a relational database are performed.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which, when executed by a processor, executes the steps of the real-time wide table construction method based on a relational database as described in any one of claims 1 to 7.

Images