An intelligent and accurate synchronous data collection method based on change data

Abstract

The present invention discloses an intelligent and precise synchronous data collection method based on changed data, which belongs to the technical field of big data processing. The present invention comprises step 1: determining the scope of key original data to be monitored; step 2: deploying data monitoring triggers; step 3: setting a data synchronization plan; step 4: executing a data synchronization engine. The present invention provides a data collection method for synchronizing changed data records to an analysis platform intelligently, continuously and accurately, wherein the changed data refers to modified and deleted records in a database; the method adopted by the present invention is to utilize the trigger function of the database to generate corresponding operation log records for modified and deleted records, and automatically add this record to the change record log table, and then use the data synchronization tool to perform cross-domain, cross-system and cross-table distributed retrieval and execution, thereby realizing accurate data collection after the change.

Description

Technical Field

[0001] The present invention belongs to the technical field of big data processing, and in particular relates to an intelligent and precise synchronous data collection method based on changed data. Background Art

[0002] With the rapid development of big data analysis technology, how to accurately and continuously synchronize the changed data to the data analysis platform has become a very important and critical issue for data changes in the data source. The traditional way is to assume that as long as the uploaded data is correct and will not be modified, and only pull data from the source data regularly in different time periods. In reality, the uploaded data will be modified. In this case, it is often roughly cleared and asked to re-upload.

[0003] Therefore, it is necessary to establish a set of logs that can monitor the status changes of key data records after a large amount of data is uploaded in the initial stage, so as to facilitate the subsequent intelligent and continuous limited data collection plan. Summary of the invention

[0004] In response to the problem of how to accurately and continuously synchronize the changed data to the data analysis platform in response to the data changes in the data source mentioned in the background technology, the present invention proposes an intelligent and precise synchronization data collection method based on the changed data, and provides a data collection implementation method for intelligent data synchronization that can continuously and limitedly accurately obtain the changed data.

[0005] Technical solution: In order to solve the above technical problems, the technical solution adopted by the present invention is as follows:

[0006] An intelligent and accurate synchronous data collection method based on change data includes the following steps:

[0007] Step 1: Determine the scope of key raw data to be monitored;

[0008] Step 2: Deploy data monitoring triggers;

[0009] Step 3: Set up a data synchronization plan;

[0010] Step 4: Execute the data synchronization engine.

[0011] Preferably, in step 1, the key raw data range to be monitored is determined, and the specific content is:

[0012] Step 101: Setting the monitoring range according to data analysis needs;

[0013] Step 102: Output data monitoring directory list and description.

[0014] Preferably, in step 2, the specific content of deploying the data monitoring trigger is:

[0015] Step 201: Create an operation log table in the corresponding business system library;

[0016] Step 202: Write update and delete trigger scripts based on the directory list in step 102;

[0017] Step 203: deploy the trigger script to the database of the corresponding system;

[0018] Step 204: Monitor update and delete operations in the database, add the operation type and related data content to the data change log table, save in JSON format, and the synchronization time and synchronization status of the current log record are both empty strings, indicating a state to be synchronized.

[0019] Preferably, in step 3, the specific contents of setting the data synchronization plan are:

[0020] Step 301: Configure a data synchronization plan;

[0021] Set the start time, end time and execution frequency of the data synchronization plan;

[0022] Step 302: configuring a task list in the data synchronization plan;

[0023] Set up the business system, business database and data change log table corresponding to each task;

[0024] Step 303: Submit the data synchronization plan.

[0025] Preferably, in step 4, the specific content of executing the data synchronization engine is:

[0026] Step 401: the data synchronization engine is started;

[0027] Step 402: Regularly check the effective time of the plan execution. If it is within the execution setting time, proceed to step 403. Otherwise, continue to wait until the interval time is reached before proceeding to step 402.

[0028] Step 403: Switch the plan state machine to being executed, so that the timing check ignores the check, ensuring that the task list execution program is not interrupted;

[0029] Step 404: Retrieving each task from the task list;

[0030] Step 405: Create a data synchronization thread and state machine for each task in round-robin fashion according to each task configuration information;

[0031] Step 406: Start the data synchronization threads corresponding to all tasks in the plan;

[0032] Step 407: The data synchronization program retrieves a list of unexecuted data change records in the log table of the system corresponding to the task configuration information corresponding to the thread;

[0033] Step 408: Determine whether there is a record in the data change record list. If there is no record, jump to step 407 after waiting for an interval. If there is a record, change the state machine to executing.

[0034] Step 409: polling the data change record list;

[0035] Step 410: Read a change record;

[0036] Step 411: Parse the content in the change record;

[0037] Step 412: Perform data synchronization operation according to the parsed result;

[0038] Step 413: modify the record execution status and execution time according to the data synchronization operation feedback;

[0039] Step 414: determine whether the data change record list is executed, if not, jump to step 410, if it is executed, switch the state machine state to execution completed;

[0040] Step 415: Determine whether the planned execution end time has been reached. If so, stop the thread and switch the state machine to the thread stop state; otherwise, wait until the interval time has expired and jump to step 408;

[0041] Step 416: Periodically determine whether the plan has reached the end time, and check whether all task threads are in thread stop state. If the plan execution is completed, otherwise wait until the interval time and jump to step 404.

[0042] Beneficial effects: Compared with the prior art, the present invention has the following advantages:

[0043] (1) The present invention provides a data collection method for synchronizing changed data records to an analysis platform in an intelligent, continuous and accurate manner. The changed data refers to the records modified and deleted in the database. The method adopted by the present invention is to use the trigger function of the database to generate corresponding operation log records for the modified and deleted records, and automatically add the records to the change record log table, and then use the data synchronization tool to perform cross-domain, cross-system and cross-table distributed retrieval and execution, thereby realizing accurate changed data collection. The advantage of the present invention is that it provides accurate and limited changed data synchronization, solving the problem that traditional data collection can only be batch collected and cannot be accurately modified after collection.

[0044] (2) The present invention can be applied to various business systems and has cross-domain, cross-system and cross-table distributed computing and processing capabilities, and has broad application prospects. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Figure 1 It is a flow chart of the intelligent data synchronization acquisition method based on changed data of the present invention;

[0046] Figure 2 A schematic diagram of a process for determining the range of key raw data to be monitored according to the present invention;

[0047] Figure 3 A schematic diagram of the process of deploying data monitoring triggers of the present invention;

[0048] Figure 4 A schematic diagram of a process for setting a data synchronization plan of the present invention;

[0049] Figure 5 It is a flowchart of executing the data synchronization engine of the present invention. DETAILED DESCRIPTION

[0050] The present invention is further illustrated below in conjunction with specific examples. The examples are implemented based on the technical solutions of the present invention. It should be understood that these examples are only used to illustrate the present invention and are not used to limit the scope of the present invention.

[0051] The present embodiment provides a data collection method for intelligent data synchronization that continuously and accurately obtains changed data records. The data collection method for intelligent data synchronization has two cores: data monitoring and data synchronization tools; data monitoring is composed of a group of update and deletion triggers of key data tables; data synchronization tools are composed of data change log rotation and data synchronization engine.

[0052] In this embodiment, the change data refers to the change data to be monitored determined according to the needs in step 1, which can be a column in a table or multiple columns. This method is applicable to all industries and all business areas. The change operation refers to adding, modifying and deleting. These operations generate a change operation log in an agreed format, and after saving this log in the change operation log table, the synchronization engine will execute it in batches after round-robin.

[0053] For example, the medical record homepage in the hospital information system (HIS) and electronic medical record (EMR) of a hospital is a large table with nearly 900 fields. Assuming that only the diagnosis and diagnosis code, surgery and surgery code need to be collected in a certain data cloud platform, these fields are set as monitoring fields, and the corresponding trigger scripts and log tables are implanted in the HIS or EMR to be collected. Each change operation on this table will generate a corresponding operation log and record it in the log table to be changed. After the engine polls, it will trigger the relevant tables of the data cloud platform to perform update operations.

[0054] like Figure 1-5As shown, the intelligent and accurate synchronous data collection method based on change data provided in this embodiment includes the following steps:

[0055] Step 1: Determine the scope of key raw data to be monitored, specifically:

[0056] Step 101: Setting the monitoring range according to data analysis needs;

[0057] For example, in a certain data cloud platform, the diagnosis and diagnosis code, surgery and surgery code in the medical record homepage need to be set as monitoring fields in the HIS medical record homepage table of the tuberculosis hospital, and the change-triggered script and log table need to be implanted.

[0058] Step 102: Output data monitoring directory list and description.

[0059] For example, there are two records in the monitoring directory list, as follows:

[0060] 1) Monitored business table: the information table on the first page of the medical record in the EMR.

[0061] Monitored fields: unique medical record ID number, medical institution number, medical institution name, hospitalization number, medical record number, patient medical card type, patient medical card number, gender code, date of birth, age (years), admission route code, admission date and time, admission department code, admission department name, discharge date and time, discharge department number, discharge department name, number of days in hospital, outpatient and emergency western medicine diagnosis disease name, outpatient and emergency western medicine diagnosis disease code, discharge main diagnosis disease code, discharge main diagnosis disease name, discharge main western medicine diagnosis disease name, discharge main western medicine diagnosis disease code, admission condition (corresponding to the main diagnosis), treatment outcome (corresponding to the main diagnosis), drug allergy, ABO blood type code, discharge method code, surgical treatment fee, anesthesia fee, surgical fee, whether it is a critical case, whether it is a difficult case, surgery and operation code, surgery and operation name, surgery and operation date.

[0062] Monitored events: Add, modify and delete.

[0063] Synchronization target table: EMR_FIRSTPAGE_MEDICAL_RECORD.

[0064] 2) Monitored business table: outpatient, emergency and hospitalization fee table in HIS.

[0065] Monitored fields: unique ID number of charging item, patient ID type, patient ID number, gender code, date of birth, outpatient / emergency / hospitalization / physical examination identification, outpatient / emergency / hospitalization / physical examination number, settlement sequence number, charging item number, charging item name, charging / refund mark, item unit price (yuan), item quantity, item total price (yuan), item self-paid amount (yuan), department code, department name, charging / refund date and time, medical institution code, medical institution name.

[0066] Monitored events: Add, modify and delete.

[0067] Synchronization target table: EMR_CHARGE_DETAIL_RECORD.

[0068] Step 2: Deploy data monitoring triggers, specifically:

[0069] Step 201: Create an operation log table in the corresponding business system library;

[0070] The operation log table contains the creation time and execution status;

[0071] Step 202: Write update and delete trigger scripts based on the directory list in step 102;

[0072] Step 203: deploy the trigger script to the database of the corresponding system;

[0073] Step 204: Monitor update and delete operations in the database, add the operation type and related data content to the data change log table; save in the agreed JSON format, and the synchronization time and synchronization status of the current log record are both empty strings, indicating a state to be synchronized.

[0074] Step 3: Set up a data synchronization plan (the initial state is to deploy the trigger script to the database of the corresponding system), specifically:

[0075] Step 301: Configure a data synchronization plan;

[0076] Set the start time, end time and execution frequency of the plan;

[0077] Step 302: configure the task list in the plan;

[0078] Set up the business system, business database and data change log table corresponding to each task;

[0079] Step 303: Submit the data synchronization plan.

[0080] Step 4: Execute the data synchronization engine (the initial state is that the data synchronization plan has been configured), specifically:

[0081] Step 401: the data synchronization engine is started;

[0082] Step 402: Regularly check the effective time of the plan execution. If it is within the execution setting time, proceed to step 403. Otherwise, continue to wait until the interval time is reached before proceeding to step 402.

[0083] Step 403: Switch the plan state machine to being executed, so that the timing check ignores the check, ensuring that the task list execution program is not interrupted;

[0084] Step 404: Retrieving each task from the task list;

[0085] Step 405: Create a data synchronization thread and state machine for each task in round-robin fashion according to each task configuration information;

[0086] Step 406: Start the data synchronization threads corresponding to all tasks in the plan;

[0087] Step 407: The data synchronization program retrieves a list of unexecuted data change records in the log table of the system corresponding to the task configuration information corresponding to the thread;

[0088] Step 408: Determine whether there is a record in the data change record list. If there is no record, jump to step 407 after waiting for an interval. If there is a record, change the state machine to executing.

[0089] Step 409: polling the data change record list;

[0090] Step 410: Read a change record;

[0091] Step 411: Parse the content (JSON) in the change record;

[0092] If the main diagnosis and code in the medical record homepage table in HIS are modified, the modification trigger will be triggered and the following change log will be generated. The specific JSON is as follows:

[0093] {

[0094] "optype":"update",

[0095] "op_data":{

[0096] "key_feild":"pno",

[0097] "key_value":"20240101001",

[0098] "values":[

[0099] {

[0100] "op_feild":"zyzd",

[0101] "old_value":"gonococcal infection",

[0102] "new_value":"gonococcal infection of the anus and rectum",

[0103] },

[0104] {

[0105] "op_feild":"zyzdbm",

[0106] "old_value":"A54.9",

[0107] "new_value":"A54.6",

[0108] } ]

[0110] "src_table":"EMP_Patient_Base",

[0111] "target_table":"SZYC_Patient_Base",

[0112] },

[0113] "op_time":"2024-01-01 11:24:24",

[0114] "monitor_server":"192.168.1.24",

[0115] "monitor_db":"emr",

[0116] };

[0117] In the above JSON, optype: monitored event type (add, update, del); op_data: monitored field information, where key_feild: the condition field for data synchronization can be understood as the primary key, key_value: the value of the condition field for data synchronization, values: a list of monitored fields, src_table: the monitored table, target_table: the target table for synchronized data; op_time: event trigger time; monitor_server: the server where the monitored table is located; monitor_db: the monitored database.

[0118] Step 412: Perform data synchronization operation according to the parsed result;

[0119] Parse the pulled JSON of the record to be changed, and perform corresponding operations according to the parsed content to achieve synchronization of the target data.

[0120] Step 413: modify the record execution status and execution time according to the data synchronization operation feedback; if the execution is successful, the execution status of the current record is updated to synchronization success; if there is an error or an abnormality, the abnormal status is marked and the next round of verification execution is pending;

[0121] Step 414: according to the execution status identifier in the above feedback, determine whether the data change record list is executed. If not, jump to step 410. If it is executed, switch the state machine state to execution completion.

[0122] Step 415: Determine whether the planned execution end time has been reached according to the index position of the current record in the record set. If so, stop the thread and switch the state machine to the thread stop state; otherwise, wait until the interval time has expired and jump to step 408;

[0123] Step 416: Periodically determine whether the plan has reached the end time, and check whether all task threads are in thread stop state. If the plan execution is completed, otherwise wait until the interval time and jump to step 404.

[0124] The present embodiment provides a data collection method and system for intelligently, continuously and accurately synchronizing changed data records to an analysis platform. The changed data refers to the modified and deleted records in the database. The method adopted in the present embodiment is to use the trigger function of the database to generate corresponding operation log records for the modified and deleted records, and automatically add this record to the change record log table, and then use the data synchronization tool to perform cross-domain, cross-system and cross-table distributed retrieval and execution, thereby achieving accurate data collection after the change. The advantage of the present invention is that it provides accurate and limited synchronization of changed data, which solves the problem that traditional data collection can only be collected in batches and cannot be accurately modified after collection.

[0125] The present invention can also be applied to various other business systems, has cross-domain, cross-system and cross-table distributed computing processing capabilities, and has broad application prospects.

[0126] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principle of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. An intelligent and accurate synchronous data collection method based on change data, characterized in that: The following steps are involved: Step 1: Determine the scope of key raw data to be monitored; Step 101: Setting the monitoring range according to data analysis needs; Step 102: Output data monitoring directory list and description; Step 2: Deploy data monitoring triggers; Step 201: Create an operation log table in the corresponding business system library; Step 202: Write update and delete trigger scripts based on the directory list in step 102; Step 203: deploy the trigger script to the database of the corresponding system; Step 204: monitor the update and delete operations in the database, add the operation type and related data content to the data change log table, store them in JSON format, and the synchronization time and synchronization status of the current log record are both empty strings, indicating a state to be synchronized; Step 3: Set up a data synchronization plan; Step 301: Configure a data synchronization plan; Set the start time, end time and execution frequency of the data synchronization plan; Step 302: configuring a task list in the data synchronization plan; Set up the business system, business database and data change log table corresponding to each task; Step 303: Submit a data synchronization plan; Step 4: Execute the data synchronization engine; Step 401: the data synchronization engine is started; Step 402: Regularly check the effective time of the plan execution. If it is within the execution setting time, proceed to step 403. Otherwise, continue to wait until the interval time is reached before proceeding to step 402. Step 403: Switch the plan state machine to being executed, so that the scheduled check ignores the check, ensuring that the task list execution program is not interrupted; Step 404: Retrieving each task from the task list; Step 405: Create a data synchronization thread and state machine for each task in round-robin fashion according to each task configuration information; Step 406: Start the data synchronization threads corresponding to all tasks in the plan; Step 407: The data synchronization program retrieves a list of unexecuted data change records in the log table of the system corresponding to the task configuration information corresponding to the thread; Step 408: Determine whether there is a record in the data change record list. If there is no record, jump to step 407 after waiting for an interval. If there is a record, change the state machine to executing. Step 409: polling the data change record list; Step 410: Read a change record; Step 411: Parse the content in the change record; Step 412: Perform data synchronization operation according to the parsed result; Step 413: modify the record execution status and execution time according to the data synchronization operation feedback; Step 414: determine whether the data change record list is executed, if not, jump to step 410, if it is executed, switch the state machine state to execution completed; Step 415: Determine whether the planned execution end time has been reached. If so, stop the thread and switch the state machine to the thread stop state; otherwise, wait until the interval time has expired and jump to step 408; Step 416: Periodically determine whether the plan has reached the end time, and check whether all task threads are in thread stop state. If the plan execution is completed, otherwise wait until the interval time and jump to step 404.

Images