Business report generation method and device, equipment and storage medium

Abstract

The invention belongs to the technical field of report generation, and discloses a business report generation method and device, equipment and a storage medium. The method comprises the following steps: establishing a connection between a Canal service and a mysql logistics database, and monitoring the change condition of a key business table in the mysql logistics database through the Canal service; analyzing the monitored key business table change condition into data change information, and sending the data change information to a Kafka message queue through a Canal service; performing cleaning processing on corresponding logistics data in a mysql logistics database according to the data change information; and according to business requirements, defining related business indexes and groups in the Elasticsearch, and carrying out aggregation operation on the cleaned logistics data stored in the Elasticsearch and generating a business report. According to the method, two different tools, namely Canal and Elasticsearch, are used for processing the data, and finally, the generated ready-made report form is queried by a user, so that the query efficiency is extremely high, and the risk of database collapse is reduced while the performance is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of report generation, and in particular to a business report generation method, device, equipment and storage medium. Background Art

[0002] The business process of express delivery is long and the amount of data is huge. In order for logistics workers to better understand the current logistics operation and the completion of various tasks, the reporting function is essential. However, the function of using traditional databases to display statistical reports has performance bottlenecks. When dealing with the huge amount of data and user groups in the logistics industry, the query takes a long time. During business peaks, it is even easy to affect the overall performance of the database, thereby affecting other major business functions.

[0003] Therefore, the prior art still needs to be improved and developed. Summary of the invention

[0004] The main purpose of the present invention is to solve the problem that the prior art uses traditional databases to make statistical reports for the logistics industry with huge data volume and user groups, which takes a long time and affects the overall performance of the database.

[0005] A first aspect of the present invention provides a method for generating a business report, comprising:

[0006] Establish a connection between the Canal service and the MySQL logistics database, and monitor the changes in the key business tables in the MySQL logistics database through the Canal service; parse the monitored changes in the key business tables into data change information and send it to the Kafka message queue through the Canal service; clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch; define relevant business indicators and groups in Elasticsearch according to business needs, aggregate the cleaned logistics data stored in Elasticsearch, and generate business reports.

[0007] Optionally, in a first implementation method of the first aspect of the present invention, the step of establishing a connection between the Canal service and the MySQL logistics database, and monitoring whether key business tables in the MySQL logistics database have changed through the Canal includes: building a Canal service, creating a new Canal instance in the Canal service and configuring the connection information of the MySQL logistics database and the filtering rules of the key business tables, ensuring that the Canal service can establish a connection with the MySQL logistics database and obtain binlog access rights; setting the operating parameters of the Canal instance according to business needs; obtaining the binlog file of the MySQL logistics database through the Canal service, and monitoring changes in key business tables in the MySQL logistics database.

[0008] Optionally, in a second implementation method of the first aspect of the present invention, a new Canal instance is created in the Canal service and the connection information of the MySQL logistics database and the filtering rules of the key business table are configured, including the steps of: configuring the connection information of the MySQL logistics database in the Canal instance, the connection information including the address, port, user name, and password; setting a regular expression in the Canal instance to match the key business table that needs to be monitored.

[0009] Optionally, in a third implementation method of the first aspect of the present invention, the monitored changes in key business tables are parsed into data change information and sent to the Kafka message queue through the Canal service, including the steps of: parsing the acquired binlog file to obtain the data operation language events therein, wherein the data operations include insert, update and delete operations; encapsulating the data operation language events and converting them into data change information, wherein the data change information includes the table name of the data change, the data change type and the changed data content; and sending the data change information to the Kafka message queue through the Canal service.

[0010] Optionally, in a fourth implementation method of the first aspect of the present invention, the corresponding logistics data in the MySQL logistics database is cleaned according to the data change information, and the cleaned logistics data is saved in Elasticsearch, including the steps of: designing corresponding data cleaning logic according to business needs, and determining the data fields and cleaning rules that need to be cleaned; obtaining corresponding logistics data from the MySQL logistics database according to the data change information; cleaning the obtained logistics data according to the data cleaning logic, and saving the cleaned logistics data in Elasticsearch.

[0011] Optionally, in a fifth implementation of the first aspect of the present invention, relevant business indicators and groups are defined in Elasticsearch according to business needs, and the post-cleaning logistics data stored in Elasticsearch is aggregated, and a business report is generated, including the steps of: clarifying business needs and defining relevant business indicators and groups in Elasticsearch accordingly; in Elasticsearch, designing an index mapping to store the post-cleaning logistics data and business indicators; using the aggregation function of Elasticsearch to calculate the business indicators of the post-cleaning logistics data according to business needs; exporting the calculated results as business reports and storing them in the mysql logistics database.

[0012] Optionally, in the sixth implementation method of the first aspect of the present invention, after generating the business report, the following steps are also included: after receiving a business report query request, searching for the generated business report data in the MySQL logistics database; returning the queried business report data to the user so that the user can view the required business report data.

[0013] The second aspect of the present invention provides a business report generation device, including: a monitoring module, used to establish a connection between a Canal service and a MySQL logistics database, and monitor changes in key business tables in the MySQL logistics database through the Canal service; a parsing module, used to parse the monitored changes in key business tables into data change information and send it to a Kafka message queue through the Canal service; a cleaning module, used to clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch; a report generation module, used to define relevant business indicators and groups in Elasticsearch according to business needs, and aggregate the cleaned logistics data stored in Elasticsearch, and generate business reports.

[0014] Optionally, in a first implementation method of the second aspect of the present invention, the monitoring module includes: a connection unit, used to build a Canal service, create a new Canal instance in the Canal service and configure the connection information of the MySQL logistics database and the filtering rules of the key business tables, to ensure that the Canal service can establish a connection with the MySQL logistics database and obtain binlog access rights; a parameter setting unit, used to set the operating parameters of the Canal instance according to business requirements; a monitoring unit, used to obtain the binlog file of the MySQL logistics database through the Canal service, and monitor the changes in the key business tables in the MySQL logistics database.

[0015] Optionally, in a second implementation of the second aspect of the present invention, the connection unit includes: a configuration subunit, used to configure the connection information of the MySQL logistics database in the Canal instance, the connection information including the address, port, user name, and password; a setting subunit, used to set a regular expression in the Canal instance for matching key business tables that need to be monitored.

[0016] Optionally, in a third implementation of the second aspect of the present invention, the parsing module includes: a parsing unit, used to parse the acquired binlog file to obtain data operation language events therein, wherein the data operations include insert, update and delete operations; a conversion unit, used to encapsulate the data operation language events and convert them into data change information, wherein the data change information includes the table name of the data change, the data change type and the changed data content; a sending unit, used to send the data change information to the Kafka message queue through the Canal service.

[0017] Optionally, in a fourth implementation of the second aspect of the present invention, the cleaning module includes: a design unit, used to design corresponding data cleaning logic according to business requirements, and determine the data fields and cleaning rules that need to be cleaned; an acquisition unit, used to obtain corresponding logistics data from the MySQL logistics database according to the data change information; a cleaning unit, used to clean the acquired logistics data according to the data cleaning logic, and save the cleaned logistics data into Elasticsearch.

[0018] Optionally, in a fifth implementation of the second aspect of the present invention, the report generation module includes: a definition unit, used to clarify business requirements and define relevant business indicators and groups in Elasticsearch accordingly; an index design unit, used to design index mapping in Elasticsearch to store the post-cleaning logistics data and business indicators; a calculation unit, used to use the aggregation function of Elasticsearch to calculate the business indicators of the post-cleaning logistics data according to business requirements; and a storage unit, used to export the calculation results as business reports and store them in the mysql logistics database.

[0019] Optionally, in the sixth implementation method of the second aspect of the present invention, it also includes a query module: when a business report query request is received, it searches for the generated business report data in the MySQL logistics database; and returns the queried business report data to the user, so that the user can view the required business report data.

[0020] A third aspect of the present invention provides a business report generating device, comprising: a memory and at least one processor, wherein the memory stores computer-readable instructions, and the memory and the at least one processor are interconnected via a line; the at least one processor calls the computer-readable instructions in the memory so that the business report generating device executes each step of the business report generating method as described above.

[0021] A fourth aspect of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-readable instructions, which, when executed on a computer, enable the computer to execute the various steps of the business report generation method described above.

[0022] Beneficial effect: In the technical solution of the present invention, for the business of generating reports with large amounts of data, the Canal service is used to synchronize the data to a separate server for processing, some indicators are pre-calculated and saved on Elasticsearch, and then the report is generated for display through a scheduled task. The business report generation method provided by the present invention avoids the use of traditional relational databases to generate reports, distributes the performance pressure to multiple nodes, uses two different tools, Canal and Elasticsearch, to process the data, and finally provides users with ready-made reports that have been generated, with extremely high query efficiency. While improving performance, it also reduces the risk of database crashes. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 A first flow chart of a method for generating a business report provided by an embodiment of the present invention;

[0024] Figure 2 A second flow chart of the method for generating a business report provided by an embodiment of the present invention;

[0025] Figure 3 A third flow chart of the method for generating a business report provided by an embodiment of the present invention;

[0026] Figure 4 A fourth flow chart of a method for generating a business report provided by an embodiment of the present invention;

[0027] Figure 5 A fifth flow chart of the method for generating a business report provided by an embodiment of the present invention;

[0028] Figure 6 A schematic diagram of the structure of a business report generation device provided by an embodiment of the present invention;

[0029] Figure 7 Another structural schematic diagram of a business report generation device provided by an embodiment of the present invention;

[0030] Figure 8 A schematic diagram of the structure of a business report generating device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0031] The embodiment of the present invention provides a business report generation method, device, equipment and storage medium, which establishes a connection between a Canal service and a MySQL logistics database, and monitors the changes of key business tables in the MySQL logistics database through the Canal service; the monitored key business table changes are parsed into data change information and sent to a Kafka message queue through the Canal service; the corresponding logistics data in the MySQL logistics database is cleaned according to the data change information, and the cleaned logistics data is saved in Elasticsearch; according to business needs, relevant business indicators and groups are defined in Elasticsearch, and the cleaned logistics data stored in Elasticsearch is aggregated, and a business report is generated. A business report generation method provided by the present invention avoids using a traditional relational database to generate a report, distributes performance pressure to multiple nodes, uses two different tools, Canal and Elasticsearch, to process data, and finally provides users with ready-made reports that have been generated, with extremely high query efficiency, while improving performance, also reducing the risk of database crashes.

[0032] The terms "first", "second", "third", "fourth", etc. (if any) in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "including" or "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0033] For ease of understanding, the specific process of the embodiment of the present invention is described below. Figure 1 The first embodiment of the method for generating a business report in the embodiment of the present invention includes:

[0034] S100, establishing a connection between the Canal service and the MySQL logistics database, and monitoring changes in key business tables in the MySQL logistics database through the Canal service.

[0035] Specifically, Canal is an open source middleware based on database incremental log parsing, mainly used for incremental data subscription and consumption of MySQL database. In this embodiment, Canal can disguise itself as a MySQL slave node, monitor the changes of key business tables in the MySQL logistics database, and send the parsed database change events to the Kafka message queue for downstream consumers to use. This embodiment uses the Canal service to monitor the changes in key business tables in the MySQL logistics database, which has the following advantages: Canal can capture the change events of the MySQL logistics database in real time, such as order status updates, logistics information changes, etc., to ensure real-time synchronization and consistency of data; Canal only synchronizes the incremental data in the database, rather than the full data, based on the incremental log parsing of binlog, thereby reducing the overhead and synchronization time of data transmission; Canal has good fault tolerance and recovery capabilities, and can automatically reconnect and restore data synchronization even if there are network fluctuations or short-term database connection interruptions; Canal supports multiple message queues as data output, such as Kafka, RocketMQ, etc., which allows developers to choose the most appropriate message queue for processing according to business needs; This embodiment uses Canal to monitor the MySQL logistics database, which can transfer part of the pressure of data reading and processing to the message queue and downstream consumers, thereby reducing the burden on the database and improving the overall performance of the system. In short, Canal monitoring the MySQL logistics database has the advantages of real-time, incremental synchronization, high availability, flexibility, easy integration, reduced database pressure and rich business scenarios, which is of great significance for improving the data processing capability and business response speed of the logistics system.

[0036] S200: parse the monitored changes in key business tables into data change information and send it to the Kafka message queue through the Canal service.

[0037] Specifically, the Canal service provides a rich data interface, including database connectors, message queues, caches, etc., which allows developers to easily obtain and transmit data, integrate and interact with other systems. As a popular message queue system, Kafka has a huge ecosystem that can be integrated with various systems and tools, such as Spark, Flink and other big data processing tools, and Nginx access logs. In the logistics scenario, this embodiment can monitor the changes of key business tables (such as order tables, logistics information tables, etc.) in real time through Canal and Kafka, and perform corresponding processing and analysis according to business needs. For example, when the order status changes, a notification can be triggered immediately or the relevant cache can be updated. The benefits of this embodiment of parsing the monitored key business table changes into data change information and sending it to the Kafka message queue through the Canal service are mainly reflected in real-time and efficiency, data consistency and reliability, flexibility and scalability, rich data interfaces and integration, easy monitoring and management, and business scenario-oriented aspects.

[0038] S300. Clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch.

[0039] In this embodiment, the Canal service is used to capture the changes in logistics data in MySQL in real time, ensuring the real-time update of data. Since the original data in MySQL may have problems such as different formats and noisy data, the cleaning process can remove duplicate, erroneous or unnecessary data to ensure the accuracy and consistency of the data and improve the data quality. As a search engine, Elasticsearch can provide efficient indexing and query capabilities and can quickly respond to various logistics data query requests. This embodiment saves the cleaned logistics data to Elasticsearch with significant benefits such as real-time, accuracy, flexibility, easy integration, query performance optimization and data security. These benefits enable the system to better support various needs in logistics business and improve the processing efficiency and service quality of logistics business.

[0040] S400. According to business requirements, define relevant business indicators and groups in Elasticsearch, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports.

[0041] In this embodiment, by defining clear business indicators and groups in Elasticsearch, it is possible to ensure that the goals of data aggregation and analysis are clear and specific; Elasticsearch provides a powerful aggregation function, supports multiple aggregation types (such as count, maximum value, minimum value, average value, sum, etc.), and through the nested use of aggregation, it can handle more complex business logic and data relationships, meet different business needs, and can efficiently process large amounts of data and quickly generate analysis results. In this embodiment, the distributed architecture and scalability of Elasticsearch enable the system to easily cope with the challenges of business growth and increased data volume, and can quickly and accurately acquire and analyze logistics data and generate business reports, avoiding the performance bottleneck of relational databases, improving the performance and stability of the system, and also optimizing the user experience.

[0042] See also Figure 2 The second embodiment of the method for generating a business report in the embodiment of the present invention includes:

[0043] S110. Build a Canal service, create a new Canal instance in the Canal service, and configure the connection information of the MySQL logistics database and the filtering rules of the key business tables to ensure that the Canal service can establish a connection with the MySQL logistics database and obtain the access rights to the binlog;

[0044] S120. Setting the operation parameters of the Canal instance according to business requirements;

[0045] S130. Obtain the binlog file of the MySQL logistics database through the Canal service to monitor changes in key business tables in the MySQL logistics database.

[0046] Specifically, first make sure that the MySQL server has binlog enabled and the binlog format is set to ROW mode, because Canal performs data parsing based on MySQL binlog; then create a database for logistics data in MySQL (such as logistics_db) and create key business tables in the database (such as the orders table, which is used to store order information).

[0047] Install and start the Canal service to monitor MySQL binlog logs. Set the basic parameters of the Canal service in the Canal service configuration file (such as canal.properties); create a new Canal instance in canal / conf / example / instance.properties and configure the connection information of the MySQL logistics database, including the address, port, user name, and password of the MySQL logistics database; set the operating parameters of the Canal instance according to business needs, such as the number of concurrent threads, transaction isolation level, etc., to optimize performance and ensure data consistency; then set a regular expression in canal.instance.filter.regex to match the key business tables that need to be monitored. For example, if you have a table called order_logistics, you can set it to .*\\..*order_logistics.*.

[0048] Next, introduce the dependency of the Canal client in the Java project, use the API provided by Canal to create a CanalConnector object, and connect to the Canal service; write a loop in the Canal client to continuously obtain the change data in the MySQL binlog log from the Canal service; use the CanalConnector object to subscribe to the previously configured instance and filter rules, that is, only monitor the changes in the key business table. The exemplary code in this embodiment is as follows:

[0049]

[0050]

[0051] See also Figure 3 A third embodiment of the method for generating a business report in the embodiment of the present invention includes:

[0052] S210, parsing the acquired binlog file to obtain data operation language events therein, where the data operations include insert, update, and delete operations;

[0053] S220, encapsulating the data operation language event and converting it into data change information, wherein the data change information includes the table name of the data change, the data change type, and the changed data content;

[0054] S230. Send the data change information to the Kafka message queue through the Canal service.

[0055] Specifically, the Canal instance needs to be configured to listen to the specific database and business table of MySQL. It will read the data change events of these tables from the binlog. When the Canal instance is started, it will start to read the binlog and parse the data change events therein, thereby obtaining the data operation language events therein, and the data operations include insert, update and delete operations; Canal will parse the events read from the binlog internally, identify the type of data change (INSERT, UPDATE, DELETE) and the related data content; then Canal will encapsulate the parsed data change events into data change information, which usually includes the name of the changed table, the type of change (such as INSERT, UPDATE, DELETE), the data content before and after the change (for UPDATE and DELETE operations), etc.; in the configuration of Canal, it is necessary to specify Kafka's BootstrapServers and the Kafka Topic to which the data change information is to be sent; when Canal parses and encapsulates the data change information, it will send this information to the specified Kafka Topic. The process of this embodiment realizes the real-time capture, parsing, encapsulation and distribution of database changes. The combination of Canal and Kafka ensures that any changes in the database can be delivered to other systems or applications in real time, accurately, and completely. This is very useful for scenarios such as real-time data analysis, data synchronization, and cache updates. At the same time, due to Kafka's high throughput, high scalability, and fault tolerance, this solution also has strong stability and scalability.

[0056] See also Figure 4 The fourth embodiment of the method for generating a business report of an express delivery in the embodiment of the present invention includes:

[0057] S310. Design corresponding data cleaning logic according to business requirements and determine the data fields and cleaning rules that need to be cleaned;

[0058] S320, acquiring corresponding logistics data from the MySQL logistics database according to the data change information;

[0059] S330. Clean the acquired logistics data according to the data cleaning logic, and save the cleaned logistics data into Elasticsearch.

[0060] In this embodiment, we first need to deeply understand the business needs, determine which data fields are critical to the business, and which quality standards these data fields should meet; based on the business needs, identify which data fields may have quality problems and need to be cleaned. These fields may include address fields, date fields, phone number fields, etc.; then design cleaning rules for each field that needs to be cleaned. For example, for address fields, it may be necessary to remove leading and trailing spaces, extra characters, or format the address; for date fields, it may be necessary to unify the date format or correct incorrect dates; for phone number fields, it may be necessary to remove non-numeric characters or perform number verification, etc.; the goals and rules of data cleaning are clarified, laying the foundation for subsequent data cleaning work.

[0061] This embodiment uses Canal to monitor the data change information of the MySQL logistics database. When the data changes, Canal will send the data change information to Kafka or other message queues; consume these data change information, and parse out the key information such as the changed table name and primary key value, and use the parsed primary key value as a condition to query the corresponding logistics data from the MySQL logistics database. This step can obtain the logistics data that has changed in the MySQL logistics database in real time, providing a data source for subsequent data cleaning work.

[0062] The acquired logistics data is cleaned according to the previously designed data cleaning logic, which may include removing useless characters, formatting data, correcting errors, etc. The cleaned data is verified to ensure that the data meets business requirements and quality standards; the cleaned and verified logistics data is saved in Elasticsearch. Elasticsearch is a search and analysis engine based on Lucene, which provides full-text search, structured search, analysis, and a distributed multi-tenant capability that combines these three functions.

[0063] In this embodiment, after cleaning, the quality of logistics data has been significantly improved, meeting business needs and data quality standards; since the entire process is triggered based on real-time data change information, it can ensure that the cleaned logistics data is also real-time; the cleaned logistics data is saved in Elasticsearch, and its powerful search and analysis capabilities can be used to conduct in-depth analysis and mining of logistics data to provide support for business decisions. Elasticsearch is a distributed system with good scalability and flexibility, which can easily cope with the growth of data volume and changes in business needs.

[0064] See also Figure 5 The fifth embodiment of the business report generation in the embodiment of the present invention includes:

[0065] S410. Clarify business requirements and define relevant business indicators and groups in Elasticsearch accordingly;

[0066] S420, in Elasticsearch, designing an index mapping to store the post-cleaning logistics data and business indicators;

[0067] S430, using the aggregation function of Elasticsearch to calculate the business indicators of the cleaned logistics data according to business needs;

[0068] S440. Export the calculated results as a business report and store them in the MySQL logistics database.

[0069] S450. After receiving a business report query request, search for the generated business report data in the MySQL logistics database, and return the queried business report data to the user so that the user can view the required business report data.

[0070] In this embodiment, we first deeply understand the business needs and determine the logistics business indicators that need to be monitored and analyzed, such as transportation time, cost, punctuality, etc.; according to the business needs, define the business indicators that need to be stored and calculated in Elasticsearch; according to the business needs, define the data grouping method, such as grouping by region, by transportation method, by time, etc. This embodiment clarifies the business goals and data needs, and provides a direction for the subsequent design of Elasticsearch index and calculation of business indicators. Next, according to the fields and business indicators of the cleaned logistics data, the structure of the Elasticsearch index is designed; for each field, the data type, whether to segment words, whether to index and other attributes are defined to ensure that the data can be correctly stored and queried, create the corresponding index in Elasticsearch, and apply the defined mapping. This step establishes a data structure that matches the business needs and improves the efficiency of data query and analysis. After that, the aggregation query DSL (Domain Specific Language) of Elasticsearch is used to write an aggregation query that meets the business needs, send the written aggregation query to the Elasticsearch cluster, perform calculations, and obtain the calculation results of the aggregation query from Elasticsearch. This embodiment uses the aggregation function of Elasticsearch to quickly and accurately calculate the business indicators of logistics data, providing data support for business analysis. The aggregation results of Elasticsearch are formatted into the data format required for the business report, the formatted data is saved in the MySQL logistics database, and the corresponding business report table structure is established. Based on this, this embodiment realizes the migration of data from Elasticsearch to MySQL, so that the business report data can be stored and queried for a long time, while reducing the query pressure on Elasticsearch.

[0071] In this embodiment, when a business report query request is received from a user, the corresponding business report data is queried in the MySQL logistics database according to the conditions in the query request, and the queried business report data is returned to the user. This embodiment provides a flexible and efficient business report query service. Users can query historical business report data at any time as needed, providing data support for business decisions.

[0072] The entire process of this embodiment realizes the functions of cleaning, storing, analyzing and generating reports of logistics data through the collaborative work of Elasticsearch and MySQL. The aggregation function of Elasticsearch can quickly calculate business indicators and improve the efficiency of data analysis; the persistent storage capability of MySQL ensures the long-term preservation and query of business report data; at the same time, through the division of labor and cooperation between Elasticsearch and MySQL, the overall load of the system is reduced and the stability and reliability of the system are improved.

[0073] The above describes the method for generating a business report in an embodiment of the present invention. The following describes the device for generating a business report in an embodiment of the present invention. Figure 6 In one embodiment of the present invention, a device for generating a business report includes:

[0074] Monitoring module 10, used to establish a connection between the Canal service and the MySQL logistics database, and monitor the changes of key business tables in the MySQL logistics database through the Canal service;

[0075] The parsing module 20 is used to parse the monitored key business table changes into data change information and send it to the Kafka message queue through the Canal service;

[0076] A cleaning module 30, used to clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch;

[0077] The report generation module 40 is used to define relevant business indicators and groups in Elasticsearch according to business needs, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports.

[0078] In this embodiment, for the business of generating reports with large amounts of data, the Canal service is used to synchronize the data to a separate server for processing, some indicators are pre-calculated and saved on Elasticsearch, and then reports are generated for display through scheduled tasks. A business report generation method provided by the present invention avoids using traditional relational databases to generate reports, distributes performance pressure to multiple nodes, uses two different tools, Canal and Elasticsearch, to process data, and finally provides users with ready-made reports that have been generated. The query efficiency is extremely high, and while improving performance, it also reduces the risk of database crashes.

[0079] See also Figure 7Another embodiment of the business report generation device in the embodiment of the present invention includes:

[0080] Monitoring module 10, used to establish a connection between the Canal service and the MySQL logistics database, and monitor the changes of key business tables in the MySQL logistics database through the Canal service;

[0081] The parsing module 20 is used to parse the monitored key business table changes into data change information and send it to the Kafka message queue through the Canal service;

[0082] A cleaning module 30, used to clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch;

[0083] The report generation module 40 is used to define relevant business indicators and groups in Elasticsearch according to business needs, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports.

[0084] In this embodiment, the monitoring module 10 includes:

[0085] Connection unit 11 is used to build a Canal service, create a new Canal instance in the Canal service, and configure the connection information of the MySQL logistics database and the filtering rules of the key business table to ensure that the Canal service can establish a connection with the MySQL logistics database and obtain the access rights of the binlog;

[0086] A parameter setting unit 12, used to set the operation parameters of the Canal instance according to business requirements;

[0087] The monitoring unit 13 is used to obtain the binlog file of the MySQL logistics database through the Canal service, so as to monitor the changes of the key business tables in the MySQL logistics database.

[0088] In this embodiment, the connecting unit 11 includes:

[0089] The first configuration subunit 111 is used to configure the connection information of the MySQL logistics database in the Canal instance, wherein the connection information includes the address, port, user name, and password;

[0090] The setting subunit 112 is used to set a regular expression in the Canal instance for matching the key business table that needs to be monitored.

[0091] In this embodiment, the parsing module 20 includes:

[0092] A parsing unit 21 is used to parse the acquired binlog file to obtain data operation language events therein, where the data operations include insert, update and delete operations;

[0093] A conversion unit 22, used to encapsulate the data operation language event and convert it into data change information, wherein the data change information includes the table name of the data change, the data change type and the changed data content;

[0094] The sending unit 23 is used to send the data change information to the Kafka message queue through the Canal service.

[0095] In this embodiment, the cleaning module 30 includes:

[0096] A design unit 31 is used to design corresponding data cleaning logic according to business requirements and determine the data fields and cleaning rules that need to be cleaned;

[0097] An acquisition unit 32 is used to acquire corresponding logistics data from a MySQL logistics database according to the data change information;

[0098] The cleaning unit 33 is used to clean the acquired logistics data according to the data cleaning logic, and save the cleaned logistics data into Elasticsearch.

[0099] In this embodiment, the report generation module 40 includes:

[0100] Definition unit 41, used to clarify business requirements and define relevant business indicators and groups in Elasticsearch accordingly;

[0101] An index design unit 42 is used to design an index mapping in Elasticsearch to store the post-cleaning logistics data and business indicators;

[0102] A calculation unit 43 is used to calculate the business index of the post-cleaning logistics data according to business requirements by using the aggregation function of Elasticsearch;

[0103] The storage unit 44 is used to export the calculation results as business reports and store them in the MySQL logistics database.

[0104] In this embodiment, a query module 50 is also included, which is used to search for the generated business report data in the MySQL logistics database after receiving a business report query request; and return the queried business report data to the user so that the user can view the required business report data.

[0105] above Figure 6 and Figure 7 The service report generating device in the embodiment of the present invention is described in detail from the perspective of modular functional entities. The service report generating device in the embodiment of the present invention is described in detail from the perspective of hardware processing.

[0106] Figure 8 1 is a schematic diagram of the structure of a business report generation device provided by an embodiment of the present invention. The business report generation device 100 may have relatively large differences due to different configurations or performances, and may include one or more processors (central processing units, CPU) 11 (for example, one or more processors) and a memory 12, and one or more storage media 13 (for example, one or more mass storage devices) storing application programs 133 or data 132. Among them, the memory 12 and the storage medium 13 can be short-term storage or permanent storage. The program stored in the storage medium 13 may include one or more modules (not shown in the figure), and each module may include a series of instruction operations in the business report generation device 100. Furthermore, the processor 11 can be configured to communicate with the storage medium 13 to execute a series of instruction operations in the storage medium 13 on the business report generation device 100.

[0107] The business report generating device 100 may also include one or more power supplies 14, one or more wired or wireless network interfaces 15, one or more input and output interfaces 16, and / or one or more operating systems 131, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc. It will be appreciated by those skilled in the art that Figure 8 The device structure shown does not constitute a limitation on the business report generating device 100 , and may include more or less components than shown in the figure, or combine certain components, or arrange the components differently.

[0108] The present invention also provides a computer-readable storage medium, which may be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium. Instructions are stored in the computer-readable storage medium. When the instructions are executed on a computer, the computer executes the steps of the business report generation method.

[0109] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the above-described system, device, or unit can refer to the corresponding process in the aforementioned method embodiment and will not be repeated here.

[0110] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), disk or optical disk and other media that can store program codes.

[0111] As described above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for generating a business report, characterized in that: The business report generation method comprises: Establish a connection between the Canal service and the MySQL logistics database, and monitor changes in key business tables in the MySQL logistics database through the Canal service; Analyze the monitored changes in key business tables into data change information and send it to the Kafka message queue through the Canal service; Clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch; According to business needs, define relevant business indicators and groups in Elasticsearch, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports.

2. The method for generating a business report according to claim 1, characterized in that: The steps of establishing a connection between the Canal service and the MySQL logistics database and monitoring whether the key business tables in the MySQL logistics database have changed through the Canal include: Build a Canal service, create a new Canal instance in the Canal service and configure the connection information of the MySQL logistics database and the filtering rules of the key business tables to ensure that the Canal service can establish a connection with the MySQL logistics database and obtain access rights to the binlog; Set the operating parameters of the Canal instance according to business needs; Obtain the binlog file of the MySQL logistics database through the Canal service to monitor changes in key business tables in the MySQL logistics database.

3. The method for generating a business report according to claim 2, characterized in that: Create a new Canal instance in the Canal service and configure the connection information of the MySQL logistics database and the filtering rules of the key business tables, including the following steps: Configure the connection information of the MySQL logistics database in the Canal instance, including the address, port, user name, and password; Set a regular expression in the Canal instance to match the key business tables that need to be monitored.

4. The method for generating a business report according to claim 2, characterized in that: Parse the monitored changes in key business tables into data change information and send it to the Kafka message queue through the Canal service, including the following steps: Parse the acquired binlog file to obtain data operation language events therein, where the data operations include insert, update, and delete operations; Encapsulating the data operation language event and converting it into data change information, wherein the data change information includes the table name of the data change, the data change type, and the changed data content; The data change information is sent to the Kafka message queue through the Canal service.

5. The method for generating a business report according to claim 1, characterized in that: The corresponding logistics data in the MySQL logistics database is cleaned according to the data change information, and the cleaned logistics data is saved in Elasticsearch, including the steps of: Design corresponding data cleaning logic according to business needs, and determine the data fields and cleaning rules that need to be cleaned; According to the data change information, corresponding logistics data is obtained from the MySQL logistics database; The acquired logistics data is cleaned according to the data cleaning logic, and the cleaned logistics data is saved in Elasticsearch.

6. The method for generating a business report according to claim 1, characterized in that: According to business needs, define relevant business indicators and groups in Elasticsearch, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports, including the following steps: Clarify business requirements and define relevant business indicators and groups in Elasticsearch accordingly; In Elasticsearch, design an index mapping to store the post-cleaning logistics data and business indicators; Use the aggregation function of Elasticsearch to calculate the business indicators of the cleaned logistics data according to business needs; The calculated results are exported as business reports and stored in the MySQL logistics database.

7. The method for generating a business report according to claim 1, characterized in that: Generating a business report also includes the following steps: When receiving a business report query request, search for the generated business report data in the MySQL logistics database; The queried business report data is returned to the user so that the user can view the required business report data.

8. A business report generation device, characterized in that: include: A monitoring module is used to establish a connection between the Canal service and the MySQL logistics database, and monitor changes in key business tables in the MySQL logistics database through the Canal service; The parsing module is used to parse the monitored key business table changes into data change information and send it to the Kafka message queue through the Canal service; A cleaning module is used to clean the corresponding logistics data in the MySQL logistics database according to the data change information, and save the cleaned logistics data in Elasticsearch; The report generation module is used to define relevant business indicators and groups in Elasticsearch according to business needs, aggregate the post-cleaning logistics data stored in Elasticsearch, and generate business reports.

9. A business report generation device, characterized in that: comprising a memory and at least one processor, wherein the memory has computer-readable instructions stored therein; The at least one processor calls the computer-readable instructions in the memory to execute each step of the business report generation method according to any one of claims 1 to 7.

10. A computer-readable storage medium having computer-readable instructions stored thereon, characterized in that: When the computer-readable instructions are executed by a processor, the steps of the business report generation method according to any one of claims 1 to 7 are implemented.

Images