CN113220707A - Method and device for combining multiple service index data in real time and computer equipment - Google Patents
Method and device for combining multiple service index data in real time and computer equipment Download PDFInfo
- Publication number
- CN113220707A CN113220707A CN202110454052.6A CN202110454052A CN113220707A CN 113220707 A CN113220707 A CN 113220707A CN 202110454052 A CN202110454052 A CN 202110454052A CN 113220707 A CN113220707 A CN 113220707A
- Authority
- CN
- China
- Prior art keywords
- data table
- service
- field information
- service index
- key field
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 53
- 238000004364 calculation method Methods 0.000 claims abstract description 30
- 239000002131 composite material Substances 0.000 claims abstract description 30
- 238000004590 computer program Methods 0.000 claims description 11
- 230000009545 invasion Effects 0.000 abstract 1
- 230000008569 process Effects 0.000 description 8
- 238000010586 diagram Methods 0.000 description 6
- 238000005516 engineering process Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 238000004422 calculation algorithm Methods 0.000 description 1
- 238000004140 cleaning Methods 0.000 description 1
- 238000013075 data extraction Methods 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 230000008520 organization Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2425—Iterative querying; Query formulation based on the results of a preceding query
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2358—Change logging, detection, and notification
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/256—Integrating or interfacing systems involving database management systems in federated or virtual databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to a method, a device, computer equipment and a storage medium for merging multiple service index data in real time. The method comprises the following steps: storing the updated service index record in any external data table into a local data table corresponding to the external data table in real time; acquiring incremental service index records in each local data table at regular time, and carrying out merging and duplicate removal processing on service main key field information of all incremental service index records to generate an intermediate incremental data table, wherein the intermediate incremental data table comprises a plurality of pieces of updated service main key field information; and acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information. The method can combine various service index data in real time with low invasion and low delay.
Description
Technical Field
The present application relates to the field of data processing technologies, and in particular, to a method, an apparatus, a computer device, and a storage medium for merging multiple service index data in real time.
Background
In recent years, with the rapid development of internet technology, the amount of data to be processed has also increased greatly. Particularly, in the enterprise operation process, real-time combination calculation is often required to be performed on multi-system and multi-dimensional data. However, in the conventional real-time merging calculation method for multi-system multi-dimensional data, data is extracted into the HDFS periodically, and then the data is associated through Hive SQL, so that data processing, cleaning and merging among multiple systems are realized, and finally, a complete-dimension data is formed to be provided for a report system to query. The method processes data corresponding to T-1 time at T time, so that the data delay is large; in addition, in the conventional operation, index data of each service system is extracted to a local system for combination calculation, a large amount of data of the service system needs to be extracted, and great pressure is applied to a database of the service system for providing the data, so that the response time of the service system for receiving external information query is influenced, and the Hive SQL consumes a large amount of calculation resources.
Disclosure of Invention
In view of the foregoing, there is a need to provide a method, an apparatus, a computer device and a storage medium for merging multiple service index data in real time with low intrusion and low latency.
The first aspect of the invention provides a method for merging multiple service index data in real time, wherein the multiple service index data are respectively stored in a plurality of external data tables, the plurality of external data tables are provided with the same service main key fields, a plurality of local data tables are preset locally, and the plurality of local data tables correspond to the plurality of external data tables one by one; the method comprises the following steps:
storing the updated service index record in any external data table into a local data table corresponding to the external data table in real time, wherein each service index record comprises service main key field information and service index field information;
acquiring incremental service index records in each local data table at regular time, and merging and de-duplicating the service main key field information of all the incremental service index records to generate an intermediate incremental data table, wherein each incremental service index record is a service index record stored in each local data table within the past preset time, and the intermediate incremental data table comprises a plurality of pieces of updated service main key field information;
and acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information.
In one embodiment, the step of storing the service index record updated in any external data table into a preset local data table corresponding to the external data table in real time includes:
collecting a binlog log generated after data updating of any external data table in real time, obtaining an updated service index record in the external data table according to the binlog log, and storing the updated service index record into a preset local data table corresponding to the external data table.
In one embodiment, the service index record further includes timestamp information, and the intermediate incremental data table further includes a plurality of pieces of update timestamp information corresponding to the plurality of pieces of update service primary key field information one to one; merging and de-duplicating the service main key field information of all incremental service index records to generate an intermediate incremental data table, which comprises the following steps:
merging and de-duplicating the service main key field information recorded by all incremental service indexes to obtain a plurality of pieces of updated service main key field information;
acquiring maximum timestamp information corresponding to a plurality of pieces of updating service main key field information in all incremental service index records, wherein the maximum timestamp information is used as a plurality of pieces of updating timestamp information corresponding to the plurality of pieces of updating service main key field information one by one;
and generating an intermediate incremental data table according to the plurality of pieces of updating service main key field information and the plurality of pieces of updating time stamp information which are in one-to-one correspondence with the plurality of pieces of updating service main key field information.
In one embodiment, the local data tables are an on-board data table, an on-board warehousing data table and an on-board ex-warehouse data table respectively, a service index field set in the on-board warehousing data table is an on-board warehousing digital section, and a service index field set in the on-board ex-warehouse data table is an on-board ex-warehouse digital section;
acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, wherein the method comprises the following steps:
according to each piece of updating service main key field information, acquiring the number of in-storage corresponding to the piece of updating service main key field information from an in-storage data table, acquiring the number of in-storage on the way corresponding to the piece of updating service main key field information from an in-storage data table on the way, and acquiring the number of out-storage on the way corresponding to the piece of updating service main key field information from an out-storage data table on the way;
performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service primary key field information, wherein the method comprises the following steps:
summing all in-transit warehousing numbers corresponding to each piece of updating service main key field information to obtain a total in-transit warehousing number corresponding to each piece of updating service main key field information;
summing all the outbound numbers corresponding to the main key field information of each updating service to obtain the total outbound number corresponding to the main key field information of each updating service;
and performing combined calculation on the number of in-store, total in-transit number of in-store and total in-transit number of out-store corresponding to each piece of updating service main key field information to obtain composite service index data corresponding to each piece of updating service main key field information.
In one embodiment, obtaining the updated service index record in the external data table according to the binlog log comprises:
and putting the binlog log into the first message queue, and analyzing the binlog log in the first message queue to obtain the updated service index record in the external data table.
In one embodiment, obtaining the updated service index record in the external data table according to the binlog log comprises: analyzing the binlog log to obtain an updated service index record in the external data table, and putting the updated service index record in the external data table into a first message queue;
storing the updated service index record into a preset local data table corresponding to the external data table, including: and acquiring the updated service index record from the first message queue and storing the updated service index record into a preset local data table corresponding to the external data table.
In one embodiment, the method further comprises:
storing each piece of updating service main key field information, updating timestamp information corresponding to the updating service main key field information and composite service index data into a second message queue, wherein the data in the second message queue is used for a downstream service system to consume;
specifically, the external data table is a data table of a Mysql database; the first message queue and the second message queue are kafka message queues;
specifically, the service primary key field information includes a goods code, a place code, a library code, and a supplier code.
The second aspect of the present invention provides a device for merging multiple service index data in real time, where the multiple service index data are stored in multiple external data tables, the multiple external data tables are provided with the same service primary key fields, multiple local data tables are preset locally, and the multiple local data tables correspond to the multiple external data tables one to one; the device comprises:
the real-time acquisition module is used for storing the updated service index records in any external data table into a local data table corresponding to the external data table in real time, and each service index record comprises service main key field information and service index field information;
the intermediate table generation module is used for acquiring incremental service index records in each local data table at regular time, merging and de-duplicating the service main key field information of all the incremental service index records to generate an intermediate incremental data table, wherein each incremental service index record is a service index record stored in each local data table within the past preset time length, and the intermediate incremental data table comprises a plurality of pieces of updated service main key field information;
and the merging calculation module is used for acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information.
A third aspect of the invention provides a computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the steps of any of the above-described embodiments of the method when executing the computer program.
A fourth aspect of the invention provides a computer-readable storage medium having stored thereon a computer program which, when being executed by a processor, carries out the steps of an embodiment of any of the methods described above.
In the above embodiment, a plurality of local data tables corresponding to the plurality of external data tables one to one are preset locally, the server stores the updated service index record in any external data table into the local data table corresponding to the external data table in real time, and acquires the incremental service index record in each local data table at regular time to process and obtain the intermediate incremental data table, where the intermediate incremental data table includes a plurality of pieces of updated service main key field information, and acquires the corresponding service index field information from each local data table according to each piece of updated service main key field information to perform calculation, so as to obtain the composite service index data corresponding to each piece of updated service main key field information.
The server acquires the updated service index records in each external data table in real time, the data volume extracted from the service system each time is small, and the pressure of data acquisition on a service system database can be reduced, so that the invasiveness to the service system is reduced; meanwhile, the server also acquires the incremental service index records in each local data table at regular time to generate a middle incremental data table, then acquires data from each local data table through the middle incremental data table, and calculates to obtain composite service index data, so that the real-time performance and the accuracy of data combination calculation are ensured.
Drawings
FIG. 1 is a diagram illustrating an exemplary embodiment of a method for merging multiple service indicator data in real time;
FIG. 2 is a flow chart illustrating a method for merging multiple service indicator data in real time according to an embodiment;
FIG. 3 is a flowchart illustrating the intermediate incremental data table generation step in one embodiment;
FIG. 4 is a block diagram of an apparatus for merging multiple service indicator data in real time according to an embodiment;
FIG. 5 is a diagram illustrating an internal structure of a computer device according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
Example one
In this embodiment, the method for merging multiple service index data in real time provided by the present application may be applied to an application environment as shown in fig. 1. Where the local server 102 communicates with the service server 104 over a network, the local server 102 may also communicate with the downstream system 106 and/or the terminal 108 over the network. A plurality of service systems are operated in the service server 104, each service system has one or more service index data, the plurality of service index data are respectively stored in a plurality of external data tables, the plurality of external data tables are provided with the same service primary key field, the plurality of external data tables may exist in the same or different service systems, a plurality of local data tables are preset in the local server 102, and the plurality of local data tables correspond to the plurality of external data tables one to one. The local server 102 may obtain updated service index records from the service server 104 in real time in any external data table, store the updated service index records in the local data table, the local server 102 further obtains incremental service index records in each local data table at regular time to generate an intermediate incremental data table, then obtains data from each local data table through the intermediate incremental data table, and calculates to obtain composite service index data for the downstream system 106 or the terminal 108.
The local server 102, the service server 104 and the downstream system 106 may be implemented by independent servers or a server cluster composed of a plurality of servers, or may be implemented by a distributed system; the terminal 108 may be, but is not limited to, various personal computers, laptops, smartphones, tablets, and portable wearable devices.
The method for merging multiple service index data in real time provided by this embodiment includes the steps shown in fig. 2, and the following description will take the application of this method to the local server 102 in fig. 1 as an example.
And step 100, storing the updated service index record in any external data table into a local data table corresponding to the external data table in real time.
The external data table may be a data table of an associated database, such as a data table of a Mysql database. The known service index data is generally used to reflect the concept and value of the service quantity characteristic, and generally consists of an index name and an index value, so the service index record in step 100 can be regarded as a specific service index data.
In an embodiment, each service index record includes service primary key field information and service index field information, where the service primary key field information may be used to represent a service object, and the service index field information may be an index value corresponding to the service object, and the service object and the index value corresponding to the service object form a service index record together.
For example, when the service index data is data of the number of commodities in the warehouse, a corresponding service index record is usually a record including commodity element information and the number of commodities in the warehouse. The product element information, i.e., the service key field information, is used to indicate a product to be used as a service object, and the number of product in stock, i.e., the service index field information, i.e., a specific numerical value of the number of product in stock corresponding to the product, and the number of product in stock is usually a positive integer value. In some specific implementation scenarios involving the sale, transportation or inventory of goods, for example, when the above method is applied to the inventory field, the goods element information may include a goods code, a location code, a stock level code and a supplier code.
In another embodiment, each service index record includes not only the service primary key field information and the service index field information, but also recording time information, which can be used to record the time of data update, and is generally recorded in the form of a field, such as a timestamp field. At this time, each service index record includes service primary key field information, service index field information and timestamp information, and the fields set by each local data table are the service primary key field, the service index field and the timestamp field.
In order to reduce the amount of data extracted from the business system and reduce the pressure on the business system database caused by data acquisition, the local server 102 acquires the updated business index records in each external data table in real time, rather than all the business index data. Specifically, when the local server 102 obtains data from the external data table in real time, several methods for obtaining the updated data may be adopted, such as: establishing a trigger, extracting the operations of adding and deleting in the external data table, and analyzing to obtain an updated service index record; or, the updated service index record is obtained by analyzing the log information of the external data table.
Taking the method of obtaining update data by parsing log information as an example, in one embodiment, step 100 includes the following steps:
collecting a binlog log generated after data updating of any external data table in real time, obtaining an updated service index record in the external data table according to the binlog log, and storing the updated service index record into a preset local data table corresponding to the external data table.
The binlog log is a binary log for recording all database table structure changes and table data modification, so that the binlog log can be generated as long as an external data table is changed. For example, when a certain commodity on the shopping page is placed for order, the inventory of the commodity changes, the inventory system can reduce the inventory of the commodity by one, and a Binlog log is generated.
When data extraction is performed on a plurality of service systems, the pressure of a database of the service system is often increased, and in the embodiment, a binlog log of an external data table is collected and analyzed, so that the service data in the external data table can be decoupled, low intrusion to the service system is realized, and the influence on the service system is greatly reduced.
In addition, in the log processing step of collecting, analyzing and storing the binlog log into the local data table, in consideration of the fact that the operations may be executed by different servers, the message queue may be used to store intermediate data generated in the log processing process, so that other servers may also use the intermediate data in the message queue.
Therefore, based on the foregoing embodiments, in an embodiment, the step of obtaining the updated service index record in the external data table according to the binlog log may further include: and putting the binlog log into the first message queue, and analyzing the binlog log in the first message queue to obtain the updated service index record in the external data table.
In another embodiment, the step of obtaining the updated service index record in the external data table according to the binlog log may further include: analyzing the binlog log to obtain an updated service index record in the external data table, and putting the updated service index record in the external data table into a first message queue; in this embodiment, the step of storing the updated service index record into the preset local data table corresponding to the external data table includes: and acquiring the updated service index record from the first message queue and storing the updated service index record into a preset local data table corresponding to the external data table.
In the above embodiments, a log collection tool, such as a Flume, logstash, or other log collection tool, may be used to collect and parse binlog logs in real time; the first message queue may employ a common message queue such as Kafka, ActiveMQ, RabbitMQ, or the like.
It should be noted that, besides the listed manners of acquiring the update data, the process may also be implemented in other manners, and the embodiment of the present invention does not limit the specific manner of acquiring the update data.
And 200, acquiring incremental service index records in each local data table at regular time, and performing merging and duplicate removal processing on the service main key field information of all the incremental service index records to generate an intermediate incremental data table.
The incremental service index records are service index records stored in each local data table within the past preset time, the intermediate incremental data table comprises a plurality of pieces of updated service main key field information, the updated service main key field information is service main key field information obtained by combining the same service main key field information in all incremental service index records, each piece of updated service main key field information is service main key field information of which the corresponding service index field information in all external data tables within the past preset time is subjected to data updating, and each piece of updated service main key field information is different service main key field information.
Specifically, the local server 102 acquires incremental service index records in each local data table at regular time with a preset time length as an interval, that is, acquires service index records stored in each local data table within a past preset time length, and performs merging and deduplication processing on service key field information of the service index records acquired each time to generate an intermediate incremental data table.
In an actual application scenario, in order to ensure real-time performance and accuracy of real-time merged data, the preset duration may be set to any time value less than 5 minutes, such as 1 minute, 2 minutes, and the like, and the real-time stream data processing tool is used to periodically obtain an incremental service index record to generate an intermediate incremental data table, for example: the method comprises the steps of obtaining and processing incremental service index records at regular time through Spark Streaming to obtain an intermediate incremental data table, wherein the intermediate incremental data table can be a temporary intermediate table and can be used for storing data associated among local data tables, so that the local data tables are mutually independent to realize decoupling.
The Spark Streaming is an extension of a Spark core API, can implement high-throughput processing of real-time Streaming data with a fault-tolerant mechanism, supports data acquisition from multiple data sources, including Kafka and flash, and can also perform algorithm processing by using a high-level function and store the processing result in a database.
In one embodiment, the service index record further includes timestamp information, the intermediate incremental data table further includes a plurality of pieces of update timestamp information corresponding to the plurality of pieces of update service primary key field information one to one, and fields set in the intermediate incremental data table are a service primary key field and a timestamp field; merging and deduplication processing is performed on the service primary key field information of all incremental service index records to generate an intermediate incremental data table, which includes the steps shown in fig. 3:
and step 210, performing merging and duplicate removal processing on the service primary key field information of all incremental service index records to obtain a plurality of pieces of updated service primary key field information.
In the above embodiment, the maximum timestamp information corresponding to each piece of update service main key field information is selected as the update timestamp information corresponding to the maximum timestamp information, so that the latest data update time recorded in the intermediate incremental data table is ensured, and meanwhile, it is ensured that a subsequent system can acquire the latest composite service index data.
And 300, acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information.
The composite service index data may be a specific numerical value obtained by combining and calculating service index field information acquired from each local data table. For example, when each piece of service index field information is a certain number, the composite service index data may be a number calculated by each number according to a preset rule.
Taking the method for merging the inventory index data in real time as an example, in one implementation mode, the plurality of local data tables are respectively an on-warehouse data table, an on-road warehousing data table and an on-road ex-warehouse data table, a service index field set in the on-road warehousing data table is an on-road warehousing digital section, and a service index field set in the on-road ex-warehouse data table is an on-road ex-warehouse digital section.
Acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, wherein the method comprises the following steps: according to each piece of updating service main key field information, the corresponding in-warehouse number of the piece of updating service main key field information is obtained from the in-warehouse data table, and the corresponding in-warehouse number of the piece of updating service main key field information is obtained from the in-warehouse data table.
The number of in-stock is information of the number of in-stock sections corresponding to the field information of the updated main key, the number of in-stock in-transit sections corresponding to the field information of the updated main key, and the number of out-stock in-transit sections corresponding to the field information of the updated main key.
Performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service primary key field information, wherein the method comprises the following steps:
and summing all in-transit warehousing numbers corresponding to each piece of updating service main key field information to obtain a total in-transit warehousing number corresponding to each piece of updating service main key field information.
And summing all the outbound numbers corresponding to the field information of the main key of each updating service to obtain the total outbound number corresponding to the field information of the main key of each updating service.
And performing combined calculation on the number of in-store, total in-transit number of in-store and total in-transit number of out-store corresponding to each piece of updating service main key field information to obtain composite service index data corresponding to each piece of updating service main key field information.
The storage mode of the database-in-database data table is updating storage, if one commodity stock changes, the changed database-in-database data table is updated to the database-in-database data table according to the field information of the main key of the service; the storage modes of the in-transit warehousing data table and the in-transit ex-warehouse data table are incremental storage, and each normal order of in-transit warehousing or in-transit ex-warehouse has one-time warehousing or ex-warehouse quantity, so that the sum calculation is carried out on all in-transit warehousing quantities or in-transit ex-warehouse quantities corresponding to each piece of updated business main key field information.
In the inventory system, the situations of instantaneity of inventory updating and inaccurate inventory data often exist, so that the total inventory is difficult to be effectively controlled and the inventory of each channel is difficult to be balanced, and the inventory is depreciated and unnecessarily allocated. The method for combining various service index data in real time can be suitable for real-time calculation of various inventory data, so that inventory management is more real-time and more accurate.
In one embodiment, the method further comprises: and storing each piece of updating service main key field information, updating timestamp information corresponding to the updating service main key field information and composite service index data into a second message queue, wherein the data in the second message queue is used for being consumed by a downstream service system.
The second message queue may adopt a common message queue, such as Kafka, ActiveMQ, RabbitMQ, and the like, and in specific implementation, the second message queue may adopt the same format as the first message queue, or may adopt different formats. The downstream service system may be any service system that requires the use of composite service indicator data, and these service systems are usually systems different from the local server.
Specifically, due to the organization and architecture relationship inside the enterprise, when the downstream business system needs to use the composite business index data, the downstream business system often cannot directly access the database of the business system or the database of the local server, so that the local server stores the data in the second message queue for consumption by the downstream business system. When the downstream service system consumes the data in the second message queue in real time, a piece of data with the same updating service main key field information is selected from a plurality of pieces of data with the same updating service main key field information, and the obtained composite service index data is ensured to be the latest.
In the above embodiment, a plurality of local data tables corresponding to the plurality of external data tables one to one are preset locally, the server stores the updated service index record in any external data table into the local data table corresponding to the external data table in real time, and acquires the incremental service index record in each local data table at regular time to process and obtain the intermediate incremental data table, where the intermediate incremental data table includes a plurality of pieces of updated service main key field information, and acquires the corresponding service index field information from each local data table according to each piece of updated service main key field information to perform calculation, so as to obtain the composite service index data corresponding to each piece of updated service main key field information.
The server acquires the updated service index records in each external data table in real time, the data volume extracted from the service system each time is small, and the pressure of data acquisition on a service system database can be reduced, so that the invasiveness to the service system is reduced; meanwhile, the server also acquires the incremental service index records in each local data table at regular time to generate a middle incremental data table, then acquires data from each local data table through the middle incremental data table, and calculates to obtain composite service index data, so that the real-time performance and the accuracy of data combination calculation are ensured.
It should be understood that although the various steps in the flow charts of fig. 2-3 are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps of the flow diagrams in fig. 2-3 may include multiple sub-steps or multiple stages that are not necessarily performed at the same time, but may be performed at different times, and the order of performance of the sub-steps or stages is not necessarily sequential, but may be performed in rotation or alternation with other steps or at least a portion of the sub-steps or stages of other steps.
Example two
The embodiment provides a device for merging multiple service index data in real time, wherein the multiple service index data are respectively stored in multiple external data tables, the multiple external data tables are provided with the same service main key fields, multiple local data tables are preset locally, and the multiple local data tables correspond to the multiple external data tables one by one; as shown in fig. 4, the apparatus includes:
a real-time obtaining module 402, configured to store, in real time, an updated service index record in any external data table into a local data table corresponding to the external data table, where each service index record includes service primary key field information and service index field information.
The intermediate table generating module 404 is configured to obtain incremental service index records in each local data table at regular time, perform merge deduplication processing on the service primary key field information of all the incremental service index records, and generate an intermediate incremental data table, where each incremental service index record is a service index record stored in each local data table within a preset time period in the past, and each intermediate incremental data table includes multiple pieces of updated service primary key field information.
And a merging calculation module 406, configured to obtain corresponding service index field information from each local data table according to each piece of updated service primary key field information, and perform merging calculation on the service index field information obtained from each local data table to obtain composite service index data corresponding to each piece of updated service primary key field information.
In an embodiment, the real-time obtaining module is specifically configured to collect a binlog log generated after data update occurs in any external data table in real time, obtain an updated service index record in the external data table according to the binlog log, and store the updated service index record in a preset local data table corresponding to the external data table.
In one embodiment, the service index record further includes timestamp information, and the intermediate incremental data table further includes a plurality of update timestamp information corresponding to the plurality of update service primary key field information one to one; the intermediate table generating module includes:
and the updating service main key field information acquisition unit is used for carrying out merging and duplicate removal processing on the service main key field information recorded by all the incremental service indexes to obtain a plurality of pieces of updating service main key field information.
And the updating timestamp information acquisition unit is used for acquiring the maximum timestamp information corresponding to the plurality of pieces of updating service main key field information in all the incremental service index records as a plurality of pieces of updating timestamp information corresponding to the plurality of pieces of updating service main key field information one by one.
And the intermediate table generating unit is used for generating an intermediate incremental data table according to the plurality of pieces of updating service main key field information and the plurality of pieces of updating time stamp information which are in one-to-one correspondence with the plurality of pieces of updating service main key field information.
In one embodiment, the plurality of local data tables are respectively an on-road database table, an on-road warehousing data table and an on-road ex-warehouse data table, a service index field set in the on-road warehousing data table is an on-road warehousing digital section, and a service index field set in the on-road ex-warehouse data table is an on-road ex-warehouse digital section; the merging calculation module includes:
the service index field information acquisition unit is used for acquiring the number of in-warehouses corresponding to each piece of updated service main key field information from the in-warehouse data table, acquiring the number of in-warehouses corresponding to the piece of updated service main key field information from the in-warehouse data table and acquiring the number of out-warehouses corresponding to the piece of updated service main key field information from the in-warehouse data table according to each piece of updated service main key field information;
a merging calculation unit, configured to perform merging calculation on the service index field information obtained from each local data table to obtain composite service index data corresponding to each piece of updated service primary key field information, and specifically configured to: summing all in-transit warehousing numbers corresponding to each piece of updating service main key field information to obtain a total in-transit warehousing number corresponding to each piece of updating service main key field information; summing all the outbound numbers corresponding to the main key field information of each updating service to obtain the total outbound number corresponding to the main key field information of each updating service; and performing combined calculation on the number of in-store, total in-transit number of in-store and total in-transit number of out-store corresponding to each piece of updating service main key field information to obtain composite service index data corresponding to each piece of updating service main key field information.
In an embodiment, the real-time obtaining module is further specifically configured to place the binlog log into a first message queue, and analyze the binlog log in the first message queue to obtain an updated service index record in the external data table.
In another embodiment, the real-time obtaining module is specifically configured to parse the binlog log to obtain an updated service index record in the external data table, and place the updated service index record in the external data table into the first message queue; and acquiring the updated service index record from the first message queue and storing the updated service index record into a preset local data table corresponding to the external data table.
In one embodiment, the apparatus further comprises:
the data storage module is used for storing each piece of updating service main key field information, the updating timestamp information corresponding to the updating service main key field information and the composite service index data into a second message queue, and the data in the second message queue is used for being consumed by a downstream service system; optionally, the external data table is a data table of a Mysql database; the first message queue and the second message queue are kafka message queues; optionally, the service primary key field information includes a goods code, a location code, a library code, and a supplier code.
For the specific limitation of the apparatus for combining multiple service index data in real time, reference may be made to the above limitation on the method for combining multiple service index data in real time, and details are not described herein again. All or part of each module in the device for combining various service index data in real time can be realized by software, hardware and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
EXAMPLE III
In this embodiment, a computer device is provided, and the computer device may be a server, and its internal structure diagram may be as shown in fig. 5. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device comprises a nonvolatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement the steps of a method for merging multiple service index data in real time as described in the first and second embodiments.
Those skilled in the art will appreciate that the architecture shown in fig. 5 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
Example four
In this embodiment, a computer-readable storage medium is provided, on which a computer program is stored, which, when being executed by a processor, implements the steps of a method for merging multiple service index data in real time as described in the first and second embodiments above.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory, among others. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. A method for merging multiple service index data in real time is characterized in that the multiple service index data are respectively stored in multiple external data tables, the multiple external data tables are provided with the same service main key fields, multiple local data tables are preset locally, and the multiple local data tables correspond to the multiple external data tables one by one; the method comprises the following steps:
storing the updated service index record in any external data table into a local data table corresponding to the external data table in real time, wherein each service index record comprises service main key field information and service index field information;
acquiring incremental service index records in each local data table at regular time, and carrying out merging and duplicate removal processing on service main key field information of all the incremental service index records to generate an intermediate incremental data table, wherein each incremental service index record is a service index record stored in each local data table within a preset time in the past, and the intermediate incremental data table comprises a plurality of pieces of updated service main key field information;
and acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information.
2. The method according to claim 1, wherein said storing the updated service index record in any one of the external data tables in real time into a preset local data table corresponding to the external data table comprises:
collecting a binlog log generated after data updating of any external data table in real time, obtaining an updated service index record in the external data table according to the binlog log, and storing the updated service index record into a preset local data table corresponding to the external data table.
3. The method of claim 2, wherein the service index record further comprises timestamp information, and wherein the intermediate delta data table further comprises a plurality of pieces of update timestamp information in one-to-one correspondence with the plurality of pieces of update service primary key field information; the merging and deduplication processing is performed on the service primary key field information of all the incremental service index records to generate an intermediate incremental data table, and the method includes:
merging and de-duplicating the service main key field information of all the incremental service index records to obtain a plurality of pieces of updated service main key field information;
acquiring maximum timestamp information corresponding to the plurality of pieces of updated service main key field information in all the incremental service index records, and taking the maximum timestamp information as a plurality of pieces of updated timestamp information corresponding to the plurality of pieces of updated service main key field information one by one;
and generating the intermediate incremental data table according to the plurality of pieces of updating service main key field information and the plurality of pieces of updating time stamp information which are in one-to-one correspondence with the plurality of pieces of updating service main key field information.
4. The method according to claim 3, wherein the plurality of local data tables are an on-board data table, an on-board database table and an on-board database table, respectively, the on-board data table is provided with an on-board numerical field, and the on-board database table is provided with an on-board numerical field;
the acquiring corresponding service index field information from each local data table according to each piece of updated service primary key field information includes:
according to each piece of updating service main key field information, acquiring the number of in-warehouses corresponding to the piece of updating service main key field information from the in-warehouse data table, acquiring the number of in-warehouse warehouses corresponding to the piece of updating service main key field information from the in-warehouse data table on the way, and acquiring the number of out-warehouse warehouses corresponding to the piece of updating service main key field information from the out-warehouse data table on the way;
the merging calculation of the service index field information obtained from each local data table to obtain the composite service index data corresponding to each piece of updated service primary key field information includes:
summing all in-transit warehousing numbers corresponding to each piece of updating service main key field information to obtain a total in-transit warehousing number corresponding to each piece of updating service main key field information;
summing all the outbound numbers corresponding to the main key field information of each updating service to obtain the total outbound number corresponding to the main key field information of each updating service;
and performing combined calculation on the number of in-store, total in-transit number of in-store and total in-transit number of out-store corresponding to each piece of updating service main key field information to obtain composite service index data corresponding to each piece of updating service main key field information.
5. The method according to any of claims 2 to 4, wherein said obtaining the updated service index record in the external data table according to the binlog log comprises:
and putting the binlog log into a first message queue, and analyzing the binlog log in the first message queue to obtain the updated service index record in the external data table.
6. The method according to any of claims 2 to 4, wherein said obtaining the updated service index record in the external data table according to the binlog log comprises: analyzing the binlog log to obtain an updated service index record in the external data table, and putting the updated service index record in the external data table into a first message queue;
the storing the updated service index record into the preset local data table corresponding to the external data table includes: and obtaining the updated service index record from the first message queue and storing the updated service index record into a preset local data table corresponding to the external data table.
7. The method of claim 3, further comprising:
storing each piece of updating service main key field information, updating timestamp information corresponding to the updating service main key field information and composite service index data into a second message queue, wherein the data in the second message queue is used for a downstream service system to consume;
preferably, the external data table is a data table of a Mysql database; the first message queue and the second message queue are kafka message queues;
preferably, the service primary key field information includes a goods code, a place code, a library code and a supplier code.
8. A device for merging multiple service index data in real time is characterized in that the multiple service index data are respectively stored in multiple external data tables, the multiple external data tables are provided with the same service main key fields, multiple local data tables are preset locally, and the multiple local data tables correspond to the multiple external data tables one by one; the device comprises:
the real-time acquisition module is used for storing the updated service index records in any external data table into a local data table corresponding to the external data table in real time, and each service index record comprises service main key field information and service index field information;
the intermediate table generation module is used for acquiring incremental service index records in each local data table at regular time, merging and de-duplicating the service main key field information of all the incremental service index records to generate an intermediate incremental data table, wherein each incremental service index record is a service index record stored in each local data table within the past preset time length, and the intermediate incremental data table comprises a plurality of pieces of updated service main key field information;
and the merging calculation module is used for acquiring corresponding service index field information from each local data table according to each piece of updated service main key field information, and performing merging calculation on the service index field information acquired from each local data table to obtain composite service index data corresponding to each piece of updated service main key field information.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the steps of the method of any of claims 1 to 7 are implemented when the computer program is executed by the processor.
10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 7.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202110454052.6A CN113220707A (en) | 2021-04-26 | 2021-04-26 | Method and device for combining multiple service index data in real time and computer equipment |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202110454052.6A CN113220707A (en) | 2021-04-26 | 2021-04-26 | Method and device for combining multiple service index data in real time and computer equipment |
Publications (1)
Publication Number | Publication Date |
---|---|
CN113220707A true CN113220707A (en) | 2021-08-06 |
Family
ID=77089195
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202110454052.6A Pending CN113220707A (en) | 2021-04-26 | 2021-04-26 | Method and device for combining multiple service index data in real time and computer equipment |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN113220707A (en) |
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN114756564A (en) * | 2022-05-10 | 2022-07-15 | 中国平安财产保险股份有限公司 | Data processing method, device, equipment and medium for streaming computation |
CN114756564B (en) * | 2022-05-10 | 2024-07-02 | 中国平安财产保险股份有限公司 | Data processing method, device, equipment and medium for stream computing |
Citations (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN107544984A (en) * | 2016-06-27 | 2018-01-05 | 北京京东尚科信息技术有限公司 | A kind of method and apparatus of data processing |
CN107783975A (en) * | 2016-08-24 | 2018-03-09 | 北京京东尚科信息技术有限公司 | The method and apparatus of distributed data base synchronization process |
CN108170826A (en) * | 2018-01-08 | 2018-06-15 | 北京国信宏数科技有限责任公司 | A kind of macro economic analysis method and system based on internet big data |
CN109753531A (en) * | 2018-12-26 | 2019-05-14 | 深圳市麦谷科技有限公司 | A kind of big data statistical method, system, computer equipment and storage medium |
CN109800225A (en) * | 2018-12-24 | 2019-05-24 | 北京奇艺世纪科技有限公司 | Acquisition methods, device, server and the computer readable storage medium of operational indicator |
CN110046168A (en) * | 2019-03-28 | 2019-07-23 | 苏宁易购集团股份有限公司 | A kind of incremental data consistency implementation method and device |
CN110175209A (en) * | 2019-04-12 | 2019-08-27 | 中国人民财产保险股份有限公司 | Incremental data synchronization method, system, equipment and storage medium |
CN110765131A (en) * | 2019-10-09 | 2020-02-07 | 苏宁云计算有限公司 | Data compression method and device for goods source data, computer equipment and storage medium |
CN112416975A (en) * | 2020-11-17 | 2021-02-26 | 用友网络科技股份有限公司 | Message configuration method, electronic device and readable storage medium |
-
2021
- 2021-04-26 CN CN202110454052.6A patent/CN113220707A/en active Pending
Patent Citations (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN107544984A (en) * | 2016-06-27 | 2018-01-05 | 北京京东尚科信息技术有限公司 | A kind of method and apparatus of data processing |
CN107783975A (en) * | 2016-08-24 | 2018-03-09 | 北京京东尚科信息技术有限公司 | The method and apparatus of distributed data base synchronization process |
CN108170826A (en) * | 2018-01-08 | 2018-06-15 | 北京国信宏数科技有限责任公司 | A kind of macro economic analysis method and system based on internet big data |
CN109800225A (en) * | 2018-12-24 | 2019-05-24 | 北京奇艺世纪科技有限公司 | Acquisition methods, device, server and the computer readable storage medium of operational indicator |
CN109753531A (en) * | 2018-12-26 | 2019-05-14 | 深圳市麦谷科技有限公司 | A kind of big data statistical method, system, computer equipment and storage medium |
CN110046168A (en) * | 2019-03-28 | 2019-07-23 | 苏宁易购集团股份有限公司 | A kind of incremental data consistency implementation method and device |
CN110175209A (en) * | 2019-04-12 | 2019-08-27 | 中国人民财产保险股份有限公司 | Incremental data synchronization method, system, equipment and storage medium |
CN110765131A (en) * | 2019-10-09 | 2020-02-07 | 苏宁云计算有限公司 | Data compression method and device for goods source data, computer equipment and storage medium |
CN112416975A (en) * | 2020-11-17 | 2021-02-26 | 用友网络科技股份有限公司 | Message configuration method, electronic device and readable storage medium |
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN114756564A (en) * | 2022-05-10 | 2022-07-15 | 中国平安财产保险股份有限公司 | Data processing method, device, equipment and medium for streaming computation |
CN114756564B (en) * | 2022-05-10 | 2024-07-02 | 中国平安财产保险股份有限公司 | Data processing method, device, equipment and medium for stream computing |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CN110489520B (en) | Knowledge graph-based event processing method, device, equipment and storage medium | |
CN110633277B (en) | Time sequence data storage method, device, computer equipment and storage medium | |
CN110377569B (en) | Log monitoring method, device, computer equipment and storage medium | |
CN111339073A (en) | Real-time data processing method and device, electronic equipment and readable storage medium | |
CN110609852B (en) | Streaming data processing method and device, computer equipment and storage medium | |
CN112559475B (en) | Data real-time capturing and transmitting method and system | |
CN112800061B (en) | Data storage method, device, server and storage medium | |
CN113537850A (en) | Storage optimization method and device, computer equipment and storage medium | |
CN110990365A (en) | Data synchronization method, device, server and storage medium | |
CN115544007A (en) | Label preprocessing method and device, computer equipment and storage medium | |
CN112860706A (en) | Service processing method, device, equipment and storage medium | |
CN113837584B (en) | Service processing system and abnormal data processing method based on service processing system | |
CN109740799B (en) | Budget data generation method, device, computer equipment and storage medium | |
CN112948504B (en) | Data acquisition method and device, computer equipment and storage medium | |
CN110837529B (en) | Big data analysis monitoring method and device, server and readable storage medium | |
CN113220707A (en) | Method and device for combining multiple service index data in real time and computer equipment | |
US10956369B1 (en) | Data aggregations in a distributed environment | |
US11308115B2 (en) | Method and system for persisting data | |
CN113254732B (en) | Method and device for determining enterprise relationship, computer equipment and storage medium | |
CN112765170B (en) | Embedded time sequence data management method and device | |
CN114925919A (en) | Service resource processing method and device, computer equipment and storage medium | |
CN113778996A (en) | Large data stream data processing method and device, electronic equipment and storage medium | |
CN111274316B (en) | Method and device for executing multi-level data stream task, electronic equipment and storage medium | |
KR20220115859A (en) | Edge table representation of the process | |
CN115423595B (en) | File information processing method and device, computer equipment and storage medium |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
PB01 | Publication | ||
PB01 | Publication | ||
SE01 | Entry into force of request for substantive examination | ||
SE01 | Entry into force of request for substantive examination |