BI system automatic synchronization method and device, electronic equipment and storage medium
By combining the scheduling platform and CDC monitoring service, the BI system is automatically triggered to synchronize data, which solves the problems of real-time performance and high development costs in traditional methods, and achieves efficient data synchronization and improved system performance.
Patent Information
- Application Number
- CN202511853337.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-10
- Publication Date
- 2026-03-06
AI Technical Summary
Traditional data synchronization methods cannot achieve real-time triggering and suffer from high development costs and high scheduling task complexity.
Data is exported to the database on a scheduled basis through the scheduling platform. Change data is captured by the CDC monitoring service, converted into interface data and pushed to the webhook service. Automatic synchronization is achieved by combining shell scripts and BI system interfaces.
It achieves near real-time data updates, improves the timeliness of data analysis and display, reduces resource waste, is highly maintainable, and enhances system performance and efficiency.
Smart Images
Figure CN121614536A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data synchronization technology, and more specifically to a method, apparatus, electronic device, and storage medium for automatic synchronization of a BI system. Technical Background
[0002] In the data processing workflow of modern enterprises, data synchronization is a crucial link. Enterprises usually need to import data from the source database into the target database, and then the BI system synchronizes the data from the target database to its own backend database for analysis and display. Traditional data synchronization methods include: (1) manually setting a timer to perform data synchronization tasks according to the timer. This method does not require additional configuration and can maintain the existing configuration, but it has the problems of not being able to synchronize immediately after the upstream task is completed and the difficulty of ensuring the coupling of the timing frequency of the upstream and downstream. (2) setting a new task to trigger synchronization after each upstream export task is completed on the scheduling platform. Although this method can meet the requirement of real-time triggering and does not require the introduction of other technical frameworks, it has the problem of too many export tasks, which leads to the need to add and configure a large number of trigger tasks, increasing development costs on the one hand and increasing the complexity of scheduling tasks on the other.
[0003] Technical content
[0004] The present invention aims to solve at least one of the above-mentioned technical problems.
[0005] To address the aforementioned problems, this invention provides a method, apparatus, electronic device, and storage medium for automatic synchronization of a BI system.
[0006] In a first aspect, the present invention provides a method for automatic synchronization of a BI system, comprising:
[0007] The scheduling platform periodically exports data to the database;
[0008] The CDC listening service is started for the log information of the database, the changed data of the scheduling task instance table is captured and the successfully executed task information parameters are exported, converted into interface data and pushed to the webhook service;
[0009] After receiving the interface data, the webhook service passes it to the shell script for judgment and processing. Based on the project name and table name configured in the BI system, the curl command is concatenated and pushed to the BI system interface.
[0010] The BI system triggers a data synchronization task upon receiving the data.
[0011] Optionally, the step of capturing the changed data of the scheduled task instance table and exporting the task information parameters of successfully executed tasks includes:
[0012] The source component obtains all the original stream data of the changes to the scheduled task instance table;
[0013] The transform component filters, processes, and transforms the original stream data according to the rules configured in the query, and outputs the status data of the successful execution of the update task to the sink component.
[0014] Optionally, the step of converting the data into interface data and pushing it to the webhook service includes: after the sink component obtains the data, it pushes it to the webhook service via an HTTP interface through a configured URL in the form of interface parameters.
[0015] Optionally, the task information parameters include the updated task name, the updated data table name, the task flow name to which the task belongs, the task execution success status, the execution success time, and the executor.
[0016] Optionally, the scheduling platform periodically exports data to the database, and also includes a log monitoring service that monitors the binlog log information of all tables in the database in the background.
[0017] Optionally, the command content includes the interface type POST, the interface address, and the data packet; the data packet content includes the token key, the BI project data source ID, the data table name, and the trigger type.
[0018] Optionally, the BI system triggers a data synchronization task after receiving the data, including: polling and triggering a synchronization task based on the BI project ID, data source ID, and data table name to complete the data synchronization.
[0019] Secondly, the present invention provides an apparatus for automatic synchronization of a BI system, comprising:
[0020] The export unit is used to schedule the platform to export data to the database at regular intervals.
[0021] The listening unit is used to start the CDC listening service for the log information of the database, capture the changed data of the scheduling task instance table, export the successfully executed task information parameters, convert them into interface data, and push them to the webhook service.
[0022] The execution unit is used to pass the interface data received by the webhook service to the shell script for judgment and processing, and to concatenate the curl command according to the project name and table name configured in the BI system to push it to the BI system interface;
[0023] The synchronization unit is used to trigger the BI system to perform a data synchronization task after receiving the data.
[0024] Thirdly, the present invention provides an electronic device, including a memory and a processor;
[0025] The memory is used to store computer programs;
[0026] The processor is configured to implement, when executing the computer program, a method for automatic synchronization of a BI system as described in the first aspect.
[0027] Fourthly, the present invention provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements a method for automatic synchronization of a BI system as described in the first aspect.
[0028] The technical solution of this invention, based on CDC technology, can promptly trigger data synchronization in the BI system by capturing changes in the database in real time, achieving near real-time data updates and improving the timeliness of data analysis and display; 2. No need to modify or add scheduling tasks, while avoiding unnecessary resource waste; 3. The basic functions are built on the existing technical framework, which has high maintainability and helps to improve the overall performance and efficiency of the system.
[0029] Instruction manual illustrations
[0030] Figure 1 This is a flowchart illustrating the automatic synchronization method of the BI system according to an embodiment of the present invention;
[0031] Figure 2 This is a system architecture diagram of the BI system automatic synchronization device according to an embodiment of the present invention;
[0032] Figure 3 This is a system architecture diagram of an electronic device according to an embodiment of the present invention. Detailed Implementation
[0033] To make the above-mentioned objects, features, and advantages of the present invention more apparent and understandable, specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings. Although some embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided to provide a more thorough and complete understanding of the present invention. It should be understood that the accompanying drawings and embodiments of the present invention are for illustrative purposes only and are not intended to limit the scope of protection of the present invention.
[0034] It should be understood that the various steps described in the method embodiments of the present invention may be performed in different orders and / or in parallel. Furthermore, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present invention is not limited in this respect.
[0035] The term "comprising" and its variations as used herein are open-ended, meaning "including but not limited to"; the term "based on" means "at least partially based on"; the term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments"; and the term "optionally" means "optional embodiments". Definitions of other terms will be given in the following description. It should be noted that the concepts of "first," "second," etc., mentioned in this invention are used only to distinguish different devices, modules, or units, and are not intended to limit the order of functions performed by these devices, modules, or units or their interdependencies.
[0036] It should be noted that the terms "a" and "a plurality of" used in this invention are illustrative rather than restrictive. Those skilled in the art should understand that, unless otherwise expressly indicated in the context, they should be understood as "one or more".
[0037] The names of the messages or information exchanged between the multiple devices in the embodiments of the present invention are for illustrative purposes only and are not intended to limit the scope of these messages or information.
[0038] like Figure 1 As shown in the figure, an embodiment of the present invention provides a method for automatic synchronization of a BI system, the specific method of which is as follows:
[0039] S1. The scheduling platform exports data to the database periodically.
[0040] The scheduling platform executes tasks that update data to the database. In this embodiment, the tasks are divided into two categories: one that updates hourly and one that updates daily. However, due to differences in platform resources and task concurrency, the time interval between task completions varies. The task update time can be set according to actual needs. Specifically, the log monitoring service listens to the binlog information of all tables in the scheduling platform's MySQL database in the background to obtain data change information for specific tables as needed.
[0041] S2. Start the CDC listening service for the database log information, capture the changed data of the scheduled task instance table, export the successfully executed task information parameters, convert them into interface data, and push them to the webhook service.
[0042] Specifically, the source component retrieves all raw stream data related to changes in the scheduled task instance table. Here, table-names specifies the table name of the scheduled task instance table, indicating that change information for that table should be retrieved, ignoring other tables.
[0043] The transform component uses queries to configure specific filtering and transformation rules to process, filter, and transform data streams, obtaining information such as the exported task name, the exported data table name, the task stream name to which the task belongs, the task execution success status, the execution success time, and the executor.
[0044] When the sink component receives the data processed by the transform component, it converts information such as the update task name, update table name, task flow name, task execution success status, execution success time, and executor into interface parameters, and pushes them to the webhook service via the configured URL using an HTTP interface.
[0045] S3. After receiving the interface data, the webhook service passes it to the shell script for judgment and processing. Based on the project name and table name configured in the BI system, the curl command is concatenated and pushed to the BI system interface.
[0046] Specifically, the webhook service receives the interface parameters sent by the CDC sink component. These parameters include the export task name, the export table name, the task flow name to which the task belongs, the task execution success status, the execution success time, and the executor. The webhook service then passes these parameters to the shell script for processing. Based on the project name and table name configured in the BI system, the script concatenates a curl command. The command includes the interface type (POST), the interface address, and a data packet. The data packet contains the token key, the BI project data source ID, the table name, and the trigger type. The curl command is then executed, pushing the trigger information to the BI system's interface.
[0047] In this solution, the shell script checks whether a synchronization task has been configured in the BI system based on the table name. The script also includes looping through an array. For example, if table A needs to be synchronized by the BI system, and each project (proj1-proj6) has a synchronization task for this table, then each project will append a curl command to perform data synchronization. In this case, table A would need to be synchronized 6 times.
[0048] S4: Upon receiving the data, the BI system triggers a data synchronization task. Based on the data packet content obtained from the interface, the BI system, according to the BI project ID, data source ID, and data table name, selectively triggers the synchronization task for that data table, thus completing the data synchronization process.
[0049] To better understand the principles of this technical solution, practical applications are illustrated below:
[0050] Every hour, the scheduling platform executes a task to update the order record table (order_record), writing the latest data to the GP database. Simultaneously, a data synchronization task runs in the BI system, responsible for synchronizing the table data from the GP database to the BI system, so that business personnel and management can see the latest order record table data in their dashboard reports.
[0051] The scheduling platform maintains a reasonable timer for executing update tasks on the order record table, and writes the data to the GP database upon completion.
[0052] Run the CDC monitoring service to ensure that all changes to the scheduling platform's database tables can be monitored;
[0053] The program processes the content monitored by CDC. The source component is configured to process the scheduled task instance table (task_instance), and the database connection information is configured. At this time, the source component will only retrieve the changed data of the task_instance table in the monitoring service; information from other tables will be ignored. The source component will output all the changed data of the task_instance table to the transform component, which will filter and transform it according to the query configuration rules. When the task_instance table contains status data indicating that the order record table has been updated and the task has been successfully executed, the transform component will transform this success status data and output it to the sink component. After obtaining the success status data, the sink component will update the task name, update the table name, the task flow name to which the task belongs, the task execution success status, the execution success time, the executor, and other information, and send them to the webhook interface as parameters.
[0054] After the webhook service receives the data from the data interface, it concatenates the curl command and executes it. For example: `curl -sH "Content-Type:application / json" -X POST https: / / xxxxxx / api / dash / xxxxxx / pushTable --data'{"token":"xxxxxx","dataConnectionId":"$current_conn","database":"$data_base","tableName":"$table_name","relyEvents":["tableExtract"]}'`.
[0055] The BI system receives the interface trigger information and initiates the synchronization task of the order record table. After synchronization is complete, the dashboard and reports will display the latest data.
[0056] This invention also provides a device 400 for automatic synchronization of a BI system, comprising:
[0057] Export unit 410 is used to schedule the platform to export data to the database periodically.
[0058] The listening unit 420 is used to start the CDC listening service for the log information of the database, capture the changed data of the scheduling task instance table and export the successfully executed task information parameters, convert them into interface data and push them to the webhook service.
[0059] The execution unit 430 is used to pass the interface data received by the webhook service to the shell script for judgment and processing, and to concatenate the curl command according to the project name and table name configured in the BI system to push it to the BI system interface;
[0060] Synchronization unit 440 is used to trigger the BI system to perform data synchronization tasks after receiving the data.
[0061] An electronic device 500 provided in this embodiment of the invention includes a memory 510 and a processor 520; the memory 510 is used to store a computer program; the processor 520 is used to implement a method for automatic synchronization of a BI system as described above when the computer program is executed.
[0062] This invention provides a computer-readable storage medium storing a computer program. When the computer program is executed by a processor, it implements a method for automatic synchronization of a BI system as described above.
[0063] Electronic device 500, which can serve as a server or client of the present invention, is described below as an example of a hardware device applicable to various aspects of the present invention. Electronic device 500 is intended to represent various forms of digital electronic computer devices, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. Electronic device 500 can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.
[0064] Electronic device 500 includes a computing unit that can perform various appropriate actions and processes based on a computer program stored in read-only memory (ROM) or a computer program loaded from a storage unit into random access memory (RAM). The RAM may also store various programs and data required for device operation. The computing unit, ROM, and RAM are interconnected via a bus. Input / output (I / O) interfaces are also connected to the bus.
[0065] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), or random access memory (RAM), etc. In this application, the units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of the embodiments of the present invention according to actual needs. Furthermore, the functional units in the various embodiments of the present invention can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated units can be implemented in hardware or as software functional units.
[0066] While the present invention has been disclosed above, its scope of protection is not limited thereto. Those skilled in the art can make various changes and modifications without departing from the spirit and scope of the present invention, and all such changes and modifications will fall within the scope of protection of the present invention.
Claims
1. A method for automatic synchronization of BI systems, characterized in that The method comprises the following steps: The scheduling platform exports data to a database at a timing; A CDC monitoring service is started for log information of the database, change data of a scheduling task instance table is captured, and task information parameters of successful execution are exported, and after being converted into interface data, the task information parameters are pushed to a webhook service; The webhook service receives the interface data and transmits the interface data to a shell script for judgment and processing, a curl command is spliced according to a project name and a table name configured by a BI system, and the BI system interface is pushed to the BI system interface; The BI system receives the command and triggers a data synchronization task.
2. The method for automatic synchronization of a BI system of claim 1, wherein The method comprises the following steps: A source component acquires all original stream data of the scheduling task instance table; A transform component filters, screens and processes the original stream data stream according to a rule configured by a query, converts state data of successful execution of an update task, and outputs the state data to a sink component.
3. The method for automatic synchronization of a BI system of claim 2, wherein The sink component acquires the data in the form of interface parameters, pushes the data to the webhook service in the form of an http interface through a configured url, and the data is converted into interface data and pushed to the webhook service.
4. The method for automatic synchronization of a BI system according to any one of claims 1 to 3, characterized in that The task information parameters comprise an update task name, an update data table name, a task flow name to which the task belongs, a task execution success state, an execution success time, and an executor.
5. The method for automatic synchronization of a BI system of claim 1, wherein The scheduling platform exports data to a database at a timing, and the method further comprises the following step:
6. The method for automatic synchronization of a BI system of claim 1, wherein A log monitoring service monitors binlog log information of all tables of the database in the background.
7. The method for automatic synchronization of a BI system of claim 1 or 6, wherein The content of the command comprises an interface type POST, an interface address and a data packet; and the content of the data packet comprises a token key, a BI project data source id, a data table name and a trigger type.
8. An apparatus for automatic synchronization of BI systems, characterized in that After receiving the command, the BI system triggers a data synchronization task, and completes data synchronization. The method comprises the following steps: An export unit is configured to export data to a database at a timing of a scheduling platform; A monitoring unit is configured to start a CDC monitoring service for log information of the database, capture change data of a scheduling task instance table, and export task information parameters of successful execution, and after being converted into interface data, the task information parameters are pushed to a webhook service; An execution unit is configured to transmit the interface data received by the webhook service to a shell script for judgment and processing, splice a curl command according to a project name and a table name configured by a BI system, and push the BI system interface to the BI system interface; 9. An electronic device, comprising: A synchronization unit is configured to trigger a data synchronization task after the BI system receives the command. The method comprises the following steps: The memory is configured to store a computer program; 10. A computer-readable storage medium, characterized in that, The processor is configured to execute the computer program, and implement the method for automatic synchronization of the BI system according to any one of claims 1 to 7. The storage medium stores a computer program, and when the computer program is executed by the processor, the method for automatic synchronization of the BI system according to any one of claims 1 to 7 is implemented.