Data simulation method and system based on database function and timed task

Abstract

The invention discloses a data simulation method based on a database function and a timed task. The method comprises the following steps: S1, acquiring source data based on a multi-modal lightweight intelligent acquisition strategy; s2, performing data cleaning and standardization so as to ensure engineering processing of simulation data quality and consistency; s3, creating a user-defined function in the database to package intelligent logic of data simulation; according to a time condition and a data mode in the timed task, intelligently performing simulation trigger judgment and simulation data generation through the user-defined function; s4, performing efficient data injection based on a buffer and batch optimization mechanism of the intermediate table; and S5, performing timed task period regulation and control. The invention further discloses a corresponding system, electronic equipment and a computer readable storage medium.

Description

Technical Field

[0001] This invention relates to the fields of operation and maintenance and data simulation technology, and in particular to a data simulation method and system based on database functions and scheduled tasks. Background Technology

[0002] In today's era of rapid information technology development, large enterprises and institutions typically build centralized operations and maintenance (O&M) management platforms to achieve unified monitoring, management, and maintenance of multiple branch sites (such as data centers, regional branches, and production workshops) distributed across the country and even globally. These O&M platforms constitute the nerve center of the enterprise's IT architecture, and their stability and forward-looking capabilities directly affect the continuity of overall business. However, a prevalent and increasingly serious challenge is that the core services of the O&M platform are usually deployed on one or a few central nodes (hereinafter referred to as "local platforms"), while the various site environments it manages are highly heterogeneous and unique. Each site continuously generates a large amount of unique operational, configuration, and status data based on factors such as its geographical location, business type, hardware model, network topology, software configuration version, and user habits.

[0003] This "center-edge" deployment model, while facilitating centralized management, inherently creates data fragmentation. Local platforms struggle to acquire and synchronize all field-specific data in real-time and completely. This data is not ordinary performance metrics, but rather critical information deeply embedded in specific environmental contexts. Examples include specific alarm codes for a particular switch model, non-standard log formats from older middleware versions, edge computing node policy adjustment records triggered by network fluctuations in specific areas, or correlation data between customized fan speed control policies and temperature thresholds based on on-site hardware. The "incompleteness" or "generalization" of local platform datasets leads to a series of deep-seated problems affecting operational efficiency and R&D effectiveness, specifically manifested in the following aspects:

[0004] First, there is a lack of proactive fault prevention and early warning capabilities. Modern operation and maintenance concepts emphasize a shift from "passive firefighting" to "proactive prevention," the core of which lies in utilizing big data analytics and machine learning models to uncover fault symptoms from historical data. However, when the dataset used by the local platform to train the model lacks specific data for each site, the resulting early warning model will inevitably be "general" but "sluggish." It cannot identify subtle, leading anomaly patterns that only appear under certain specific combinations or configurations of equipment. For example, a specific SCSI error log appearing on a specific model of disk array at site A, indicating impending bad sectors, if never synchronized to the local platform, will prevent the platform from providing targeted early warnings for other sites B or C that have deployed the same model of array. This leaves the platform essentially in a "blind spot" when facing potential risks caused by environment specificity, missing the best opportunity for prevention.

[0005] Second, the coverage and realism of the testing and verification process are severely insufficient. Before developing, upgrading, or releasing new features on an operations and maintenance platform, thorough verification in a test environment is essential to ensure its stability and compatibility. However, since test environments are typically built locally, the test data used is often artificially constructed or comes from only a few typical scenarios, failing to cover all the unique and ever-changing data specific to each scenario. This kind of testing is "idealized," potentially leading to the following consequences: First, certain code branches or logical vulnerabilities that only appear under specific data triggers may go undetected, bringing potential problems to the production environment; second, compatibility testing for new features is incomplete. For example, a feature designed to parse new types of logs may fail to parse after deployment because the test data lacks a log format specific to a particular scenario; finally, performance stress test results are distorted because the simulated load and data volume cannot accurately reflect the actual pressure scenarios of a large-scale data scenario. This significantly increases the quality risks of software releases.

[0006] Third, online problem localization and reproduction are exceptionally difficult and inefficient. When a specific site experiences a failure or abnormal behavior, local R&D personnel need to quickly intervene and analyze it. However, since they lack the specific data context of that site locally, they must rely on remote connections (such as VPNs or jump servers) to access the site environment to view real-time logs, query the site database, and analyze the operational status. This process faces numerous challenges: network latency and jitter can lead to slow operational responses, or even disconnections, significantly impacting troubleshooting efficiency; the site environment is usually a stressed production system, and direct debugging and log capture pose operational risks that may affect business operations; more importantly, problems can be transient, and by the time R&D personnel connect remotely, the site status may have already changed, making it impossible to capture a complete snapshot of the site at the moment the problem occurred. This "remote diagnostics" approach is not only time-consuming and labor-intensive but also heavily reliant on the assistance capabilities of on-site personnel and network stability, significantly extending the mean time to resolve (MTTR).

[0007] Fourth, the physical data copying solution is impractical in practice. A seemingly straightforward solution is to periodically copy all on-site data to a local platform. However, in the era of big data, this approach is virtually unrealistic. First, the amount of data generated on-site is massive, potentially reaching terabytes per day. Completing a full data transfer would take hours or even days, which is excessively time-consuming. Second, the data transfer process consumes enormous network bandwidth, potentially impacting network quality for normal business operations. Third, data value is highly time-sensitive; by the time massive amounts of data arrive locally after a long transmission period, their value for real-time alerts and immediate problem analysis has been significantly diminished—the data has become "expired." Finally, this also involves a series of derivative issues such as data security, compliance, and storage costs. Therefore, although data copying is theoretically a method of information synchronization, its feasibility, timeliness, and cost-effectiveness are all very low when dealing with the need for synchronizing large amounts of operational data across multiple sites.

[0008] In summary, under current technology, centralized operation and maintenance platforms cannot effectively acquire and utilize the unique environmental data scattered across various sites, resulting in significant shortcomings in key areas such as fault prevention, testing and verification, and problem diagnosis. This not only reduces the agility and accuracy of operation and maintenance responses but also limits the improvement of the platform's intelligence level. Therefore, there is an urgent need in this field for an innovative technical solution that can efficiently, flexibly, and on-demand "simulate" or "reproduce" the unique data environment of each site on a local platform without relying on massive physical data transmission. This would provide highly realistic data support for R&D, testing, and operation and maintenance analysis, fundamentally solving the aforementioned dilemma. Summary of the Invention

[0009] To address the problems existing in the prior art, this invention provides a data simulation method and system based on database functions and scheduled tasks, including innovations in three stages: in the initial stage of system deployment, a one-time comprehensive copy of the field data is performed to ensure the integrity of the initial dataset; subsequent data is dynamically generated using database functions and scheduled tasks to avoid repeated copying and improve data update efficiency; and the accuracy and consistency of the simulated data are ensured through periodic data cleaning and organization.

[0010] This invention provides a data simulation method based on database functions and scheduled tasks, comprising:

[0011] S1, acquires source data based on a multimodal lightweight intelligent acquisition strategy;

[0012] S2 performs data cleaning and standardization, thereby ensuring the quality and consistency of the simulated data through engineered processing.

[0013] S3, by creating a custom function within the database to encapsulate the intelligent logic of data simulation; and based on the time conditions and data patterns in the scheduled task, intelligently performing simulation trigger judgment and simulation data generation through the custom function;

[0014] S4, based on the intermediate table's buffering and batch optimization mechanism, performs efficient data injection;

[0015] S5 is used for timed task cycle adjustment.

[0016] Preferably, the source data in S1 includes:

[0017] The basic entity data used to characterize static features is collected following the principle of unique identifier + key attribute.

[0018] The key to simulating dynamic behavior is to use time-series and periodic feature data to characterize dynamic behavior, thereby enabling the simulation data to have realism in the time dimension.

[0019] Relationship and rule data used to characterize contextual logic, thereby describing information about the relationships between data entities and business rules;

[0020] Data source metadata is used to characterize technical metadata, thereby describing the technical information from which the data originates and ensuring the traceability of the simulation process.

[0021] Preferably, the multimodal lightweight intelligent acquisition strategy of S1 includes using mode A, mode B, and mode C individually, or using a combination of the three modes, wherein:

[0022] Method A is an automated agent script copying method, which includes: deploying a lightweight agent program for each site, the agent containing predefined data extraction logic, which can automatically query and export the source data from the site's database, configuration file, API interface or log system according to the configuration, package it into one or more structured data files, and then incrementally transmit it to the designated receiving directory of the central platform through a secure channel; or, containerized agent; or, differential acquisition; or, compression and encryption.

[0023] Method B is manual input and configuration, including: providing a human-computer interaction interface for data that cannot be automatically obtained or requires expert knowledge to refine and confirm, thereby allowing maintenance personnel or field engineers to manually enter, select or configure this information; or, the interface provides template import / export functions, data validity verification and linkage with the configuration management database, thereby automatically pulling some basic entity data from the configuration management database;

[0024] Method C is a message queue-based streaming acquisition method for scenarios with high real-time requirements. It includes: on the field side, publishing change events of source data to the message queue in real time; and on the central platform, deploying a consumer service to subscribe to messages from the queue and perform parsing and storage.

[0025] Preferably, S2 includes:

[0026] S21, perform format standardization processing;

[0027] S22 performs data integrity verification and repair to ensure data robustness;

[0028] S23, perform structural normalization and data enrichment.

[0029] Preferably, S3 includes:

[0030] S31, create a simulated scheduling function within the database to simulate trigger judgment logic; where:

[0031] The basic decision logic of the simulated scheduling function includes: reading metadata and iterating through all tasks in the ACTIVE state, performing the following calculations for each task:

[0032] current_time:=CURRENT_TIMESTAMP;

[0033] expected_next_time:=last_simulation_time+simulation_interval; and determine the trigger condition: if current_time>=expected_next_time, then the task is determined to have expired and needs to be executed. After triggering, the simulation scheduling function records a simulation start log, and then calls the data generation function fn_generate_[data_type]_data(), passing the task ID and last_simulation_time as parameters;

[0034] The characteristics of the simulated scheduling function include: concurrency control, fault tolerance and retry mechanism, and time compensation mechanism;

[0035] S32, Create data generation functions within the database based on a diversified generation strategy library to simulate data generation logic; wherein, the strategies of the diversified generation strategy library include:

[0036] Strategy 1: Based on the random copying and perturbation of historical samples, the data generation function randomly selects a record as a template from the historical samples of the collected source data.

[0037] Strategy 2: For data with clear patterns, synthesize simulated data based on statistical models;

[0038] Strategy 3: For the source data with state transitions, a unique auto-incrementing ID is generated using the database sequence based on the sequence and state machine; for those using Strategy 3, a state machine is maintained within the function to drive state changes based on the average time taken for state transitions in historical data.

[0039] Strategy 4: Generate the simulated data functionally based on built-in database functions;

[0040] S33, based on the last acquisition time recorded in the cleaned data and the preset timed task cycle, determine whether a data simulation operation needs to be performed; perform the data simulation based on the determination that a data simulation operation needs to be performed.

[0041] Preferably, S4 includes:

[0042] S41, an intermediate table designed for data buffering and for maximizing write speed; wherein the structure of the intermediate table is completely identical to that of the target table and serves as a temporary workspace;

[0043] S42, Establish a batch optimization mechanism to optimize the batch insertion process; wherein, the batch insertion process includes:

[0044] (1) Batch insertion into intermediate tables, including: the data generation function constructs a value list of multiple generated records in memory and completes it through an INSERT statement; or, it is completed by batch writing or LOAD DATA INFILE.

[0045] (2) Transfer the transaction from the intermediate table to the target table, or, if the target table exceeds the threshold before insertion, temporarily disable the non-unique index and foreign key constraints, and re-enable and rebuild the non-unique index and foreign key constraints after the insertion is completed; or, delay index maintenance and postpone index updates until the transaction is committed in batches.

[0046] S43 performs exception handling and data consistency processing, including: if the transfer fails, the transaction is rolled back, the data in the intermediate table will not be lost, and it can be retried; the clear operation is only executed after the target table is confirmed to have been successfully inserted; and a lightweight data quality check is performed on the intermediate table before the transfer.

[0047] Preferably, S5 includes:

[0048] S51, Establish a refined mathematical model for periodic setting; wherein, the core variables of the refined model include:

[0049] t_sim: The period defined by the simulation data, obtained from step S1;

[0050] t_exec_avg: The average execution time of the simulation function obtained from step S3 and the injection process obtained from step S4 is calculated in real time by system monitoring;

[0051] t_exec_max: The maximum execution time in history;

[0052] t_overhead: The overhead of the scheduler itself and the system safety margin;

[0053] The period calculation formula for the refined model includes:

[0054] Periodic calculation based on the most conservative strategy: T = min(t_sim, t_exec_max + t_overhead);

[0055] Cycle calculation based on cost optimization strategy: T=min(t_sim,k*t_exec_avg+t_overhead); where k is the safety factor;

[0056] The calculation based on the adaptive strategy includes: the system continuously monitors t_exec_avg and t_exec_max, and periodically recalculates and adjusts the period according to the formula;

[0057] S52 implements scheduled tasks and is adapted for cross-platform compatibility;

[0058] S53, System monitoring and resource protection are performed based on a monitoring system; the monitoring indicators of the monitoring system include:

[0059] Performance metrics: t_exec_avg, t_exec_max, task queue length, database CPU utilization, IOPS; and business metrics: simulated data volume, simulation latency, and task success rate for each task.

[0060] The system monitoring based on the monitoring system includes: marking key steps in the scheduling function and data generation function, recording the start and end times, and writing them into a "simulated execution log table"; and monitoring for long transactions or lock waits through the database system view.

[0061] Resource protection based on monitoring systems includes:

[0062] Automatic degradation: When the overall database load exceeds the threshold, the scheduler automatically pauses some non-core simulation tasks until the load decreases;

[0063] Alerts: Notify operations and maintenance personnel when a task fails, simulation delay exceeds a threshold, or system resources are continuously in short supply.

[0064] A second aspect of the present invention is to provide a data simulation system based on database functions and scheduled tasks, comprising:

[0065] Source data collector (101) is used to acquire source data based on a multimodal lightweight intelligent acquisition strategy;

[0066] A data cleaning engine (102) is used to perform data cleaning and standardization, thereby ensuring the engineered processing of simulation data quality and consistency;

[0067] The simulation logic core (103) is used to encapsulate the intelligent logic of data simulation by creating a custom function inside the database; and to intelligently perform simulation trigger judgment and simulation data generation through the custom function according to the time conditions and data patterns in the scheduled task.

[0068] The high-efficiency injection module (104) is used for high-efficiency data injection based on the buffer and batch optimization mechanism of the intermediate table;

[0069] The task scheduler (105) is used to adjust the cycle of timed tasks.

[0070] A third aspect of the present invention provides an electronic device including a processor and a memory, the memory storing a plurality of instructions, the processor being configured to read the instructions and execute the method as described in the first aspect.

[0071] A fourth aspect of the present invention provides a computer-readable storage medium storing a plurality of instructions which can be read by a processor and executed as described in the first aspect.

[0072] The method, system, and electronic device provided by this invention have the following beneficial effects:

[0073] This invention constructs a highly automated, resource-efficient, elastically scalable, and industrial-grade robust data simulation system. It not only solves the problem of missing data specific to multiple sites in its basic functions, but also ensures stable and reliable operation in complex and ever-changing real-world production environments through a series of in-depth technical optimizations and anomaly handling mechanisms. This provides a near-limitless and highly realistic data source for the development and operation and maintenance activities of the operation and maintenance platform, fundamentally improving the efficiency and quality of enterprise IT operations. Specifically, this is manifested in:

[0074] (1) Real-time simulation data: This invention can simulate data from various sites in real time, ensuring that engineers can obtain a data environment consistent with the site environment locally. When a bug occurs on site, engineers can quickly reproduce the problem locally, shortening the problem-solving cycle. Real-time simulation data also supports developers and testers in verifying system performance in different scenarios, improving product quality.

[0075] (2) Improve work efficiency: Through automated data simulation and scheduled tasks, the time cost of manual operation and data copying is reduced.

[0076] (3) Enhance system stability: The accuracy of simulation data improves the effectiveness of testing, helps to discover and fix potential problems, and enhances system stability.

[0077] (4) Flexible and scalable: The technical solution of the present invention is easy to expand and can adapt to changes in different site and data requirements, and has good flexibility and scalability. Attached Figure Description

[0078] Figure 1 This is a flowchart of the data simulation method based on database functions and scheduled tasks described in this invention;

[0079] Figure 2 This is a schematic diagram of the data simulation method based on database functions and scheduled tasks described in this invention.

[0080] Figure 3 This is a schematic diagram of the data simulation system architecture based on database functions and timed tasks as described in this invention.

[0081] Figure 4 This is a schematic diagram of the electronic device structure described in this invention. Detailed Implementation

[0082] To better understand the above technical solutions, the following will provide a detailed explanation of the technical solutions in conjunction with the accompanying drawings and specific implementation methods.

[0083] To make the objectives, technical solutions, and advantages of this invention clearer, the embodiments of this invention will be described in great detail and comprehensively below with reference to the accompanying drawings. This invention not only provides the core method flow but also explores various implementation variations, exception handling mechanisms, resource optimization strategies, and adaptation schemes for different database platforms to ensure the robustness, versatility, and industrial applicability of the technical solution.

[0084] This invention provides a data simulation method and system based on database functions and scheduled tasks. Its core concept lies in accurately simulating the unique data environments distributed across various heterogeneous sites on a central operations and maintenance platform (i.e., the "local platform") through a lightweight, highly automated, and resource-controllable technical approach. This method avoids the timeliness, bandwidth, and storage bottlenecks caused by massive physical data transmission. By intelligently extracting the "meta-features" and "behavioral patterns" of the source data and deeply utilizing the native functions and task scheduling capabilities of the database kernel, it generates highly valuable and timely simulated data locally. This provides high-quality, high-coverage data support for software testing, problem reproduction, and the training of early warning models.

[0085] refer to Figure 1 and Figure 2 Each step is explained, covering basic implementation, advanced variations, exception handling, and cross-platform adaptation.

[0086] S1, acquires source data based on a multimodal lightweight intelligent acquisition strategy;

[0087] Step S1 serves as the cornerstone of data simulation, collecting the "Minimum Viable Dataset" necessary to drive the data simulation from a dispersed, heterogeneous field environment. Compared to traditional full copying, the embodiments of the present invention collect data with "representativeness," "lightweighting," and "meta-information."

[0088] In a preferred embodiment, the source data in S1 is a multi-level information collection, including the following four categories:

[0089] (1) Basic entity data used to characterize static features, thereby forming the basic unit of the field data environment skeleton. The collection of the basic entity data follows the principle of "unique identifier + key attribute".

[0090] Example of basic entity data:

[0091] Device management scenario: Collect the device's unique identifier (such as SN code, MAC address), model, management IP address, firmware version, key configuration parameters (such as CPU / memory alarm threshold, fan speed control strategy), and information about the rack / data center to which it belongs.

[0092] Business monitoring scenarios: Collect business instance ID, service name, version number, list of dependent services (ServiceDependencies), specific business configuration items (such as timeout, number of retries), and hash value of database connection string.

[0093] Network monitoring scenario: Collect network device ID, interface index number, interface description, bandwidth value, VLAN, OSPF area number, etc.

[0094] Advanced variant: For configuration parameters, not only their current value is collected, but also their "value range" (such as minimum / maximum value, enumerated value list) and "variability" (such as whether the parameter is statically configured or dynamically changing). This provides a foundation for more complex simulations later on.

[0095] (2) The key to realizing the "simulation" of dynamic behavior is the use of time-series and periodic feature data to characterize dynamic behavior, so that the simulated data has the authenticity in the time dimension.

[0096] Examples of time-series and periodic feature data:

[0097] Data generation cycle: Precisely records the regular intervals at which specific types of data are generated. For example, a server agent reports performance metrics every 15 seconds, an application records aggregate logs every 5 minutes, and a sensor reports environmental data every 1 second.

[0098] Historical data pattern sample: Instead of copying all historical data, a "sampling" and "aggregation" strategy is used.

[0099] Time window sampling: Extracting data from a representative time window (e.g., a complete business cycle: 24 hours, a week) to analyze its patterns.

[0100] Data distribution analysis: Calculate the statistical characteristics of data from a large amount of historical data, such as (minimum, maximum, average, standard deviation, distribution histogram) for numerical fields, and (each value and its frequency) for enumeration fields.

[0101] Association mining: Analyzing the correlations between different data fields. For example, analyzing the Pearson correlation coefficient between CPU utilization and memory usage, or discovering time-series association rules such as "when event A occurs, the probability of event B occurring within 5 minutes is greater than 80%".

[0102] Last Acquisition / Update Timestamp: Accurately records the final time point at which the source data for this batch was extracted on-site, serving as the benchmark for calculating time increments in subsequent simulation tasks. UTC time is recommended to avoid time zone confusion.

[0103] (3) Relationship and rule data used to characterize context logic, thereby describing the relationship between data entities and business rules, which is the key to achieving high-level simulation.

[0104] Example of relational and rule data:

[0105] Topology: such as "Device A is connected to port GigabitEthernet1 / 0 / 1 of switch B".

[0106] Causal / association rules: such as "When the CPU load of server A exceeds 85% for 3 consecutive minutes, there is a 90% probability that it will trigger a 'Session Limit Near' alarm on the connected firewall B".

[0107] Status transition rules: such as the flow conditions and average time for a work order's status to change from "Open" to "Processing" to "Resolved".

[0108] (4) Data source metadata used to characterize technical metadata, thereby describing the technical information of the data source and ensuring the traceability of the simulation process.

[0109] Example of data source metadata:

[0110] The source database type and version, the table name, field name, field data type, and the hash value of the SQL statement or script used for collection.

[0111] As a preferred embodiment, the multimodal lightweight intelligent acquisition strategy in S1 includes: multiple complementary methods supported for achieving lightweight, automated acquisition that adapts to complex network environments, which can be used in combination, specifically including:

[0112] Method A: Automated proxy script copying

[0113] The first embodiment of automated agent script copying involves deploying a lightweight agent (e.g., written in Python or Go) to each site. This agent contains predefined data extraction logic that, based on configuration, automatically queries and exports source data from site databases, configuration files, API interfaces, or log systems, and packages it into one or more structured data files (e.g., CSV, JSON, Avro formats). Subsequently, it incrementally transfers the data to a designated receiving directory on the central platform via a secure channel (e.g., SFTP / SCP, HTTPS with Token Authentication).

[0114] The second embodiment of automated agent script copying: containerized agent. The agent program is packaged into a Docker image and deployed and managed on-site using a unified container runtime (such as Docker Engine), achieving environment isolation and one-click deployment.

[0115] The third embodiment of automated agent script copying: differential acquisition. The agent program maintains a snapshot of the state from the last acquisition locally. During each acquisition, only changed data (added, deleted, modified) is collected, and data consistency is ensured through a transaction mechanism, greatly reducing the amount of data transmitted. For example, only device configurations added or modified since the last acquisition are collected.

[0116] The fourth embodiment of automated agent script copying: compression and encryption. Before transmission, data packets are compressed (e.g., gzip) to save bandwidth and encrypted (e.g., using the AES algorithm) to ensure data security.

[0117] Method B: Manual Input and Configuration

[0118] The first implementation of manual input and configuration: For data that cannot be automatically obtained or requires expert knowledge for refinement and confirmation (such as complex business rules or characteristics of non-standard equipment), a human-computer interaction interface (web page or client tool) is provided. This interface provides controls such as forms, drop-down selections, and rule configurators, allowing maintenance personnel or field engineers to manually enter, select, or configure this information.

[0119] The second embodiment of manual input and configuration: The interface provides template import / export functions, data validity verification (such as rule logic verification, numerical range verification), and linkage with CMDB (Configuration Management Database), which can automatically pull some basic entity data from CMDB to reduce the amount of manual input.

[0120] Method C: For scenarios with high real-time requirements, implement streaming acquisition based on message queues;

[0121] Specific implementation methods of message queue-based streaming acquisition include:

[0122] On the field side, change events of the source data (such as CDC - Change Data Capture in the database) are published to a message queue (such as Kafka, RabbitMQ) in real time. On the central platform, a consumer service is deployed to subscribe to these messages from the queue and parse and store them. This approach can achieve near real-time source data synchronization, but the architecture is highly complex.

[0123] Regardless of the method used, the collected source data will be uniformly stored in a dedicated "simulated source data warehouse" after entering the central platform. It is recommended that this warehouse be designed using a star schema or snowflake schema. For example, with fact_data_source as the core fact table, it can be associated with dimension tables such as dim_site (site dimension), dim_data_type (data type dimension), and dim_collection_time (time dimension) to facilitate subsequent querying, management, and version control.

[0124] S2 performs data cleaning and standardization, thereby ensuring the quality and consistency of the simulated data through engineered processing.

[0125] Because the source data comes from diverse and heterogeneous environments, its format, encoding, units, and even semantics may be inconsistent, and directly using it for simulation will lead to distorted results. Step S2 utilizes the data processing capabilities of the database, combined with external scripts, to perform engineered cleaning and standardization of the collected raw data.

[0126] UPDATE zbx_items d set d.delays=86400 where d.state=2 and d.delay='86400;50 / 1-7,00:00-24:00';

[0127] UPDATE zbx_items d set d.delays=LEFT(delay,LENGTH(delay)-1) whered.state=2 and RIGHT(delay,1)='s';

[0128] UPDATE zbx_items d set d.delays=LEFT(delay,LENGTH(delay)-1)*60 whered.state=2 and RIGHT(delay,1)='m';

[0129] UPDATE zbx_items d set d.delays=LEFT(delay,LENGTH(delay)-1)*3600where d.state=2 and RIGHT(delay,1)='h';

[0130] UPDATE zbx_items d set d.delays=LEFT(delay,LENGTH(delay)-1)*3600*24where d.state=2 and RIGHT(delay,1)='d';

[0131] UPDATE zbx_items d set d.delays=d.delay where d.state=2 and d.delays=0 and d.delay REGEXP '(^[0-9]+.[0-9]+$)|(^[0-9]$)'.

[0132] In a preferred embodiment, S2 includes:

[0133] S21, perform format standardization processing;

[0134] In this embodiment, step S21 includes:

[0135] (1) Date and time formatting:

[0136] A. Basic SQL: Use the database's date conversion functions, such as:

[0137] MySQL's STR_TO_DATE(raw_date,'%Y-%m-%d %H:%i:%s');

[0138] Oracle's TO_DATE(raw_date,'YYYY-MM-DD HH24:MI:SS');

[0139] PostgreSQL's TO_TIMESTAMP(raw_date,'YYYY-MM-DD HH24:MI:SS').

[0140] B. Handling complex situations:

[0141] Time zone handling: If the source data contains time zone information, it needs to be uniformly converted to UTC time for storage. For example, in PostgreSQL, use AT TIME ZONE 'UTC'.

[0142] Non-standard formats: For unusual date formats (such as 27-Oct-2023), more complex regular expressions or multi-step CASE WHEN logic are required for parsing.

[0143] Timestamp precision: Standardize precision to milliseconds, microseconds, etc., to avoid errors in subsequent comparisons.

[0144] (2) Character encoding and string processing:

[0145] A. Encoding Conversion: Use CONVERT(raw_string USING utf8mb4) (MySQL) or database management tools to ensure that all text data uses a unified UTF-8 character set, thus completely resolving issues such as garbled Chinese characters.

[0146] B. Removing extra spaces: Use the TRIM() function to remove leading and trailing spaces from a string.

[0147] C. Case Consistency: Based on business rules, use the UPPER() or LOWER() function to unify case and ensure consistency of enumerated data.

[0148] (3) Unit standardization:

[0149] A. Through basic SQL: This can be achieved using CASE WHEN statements or arithmetic operations. For example:

[0150] SQL

[0151] --Consolidate memory usage to GB

[0152] UPDATE raw_table SET memory_gb =

[0153] CASE

[0154] WHEN memory_unit = 'MB' THEN memory_value / 1024.0

[0155] WHEN memory_unit = 'KB' THEN memory_value / (1024.0 * 1024.0)

[0156] ELSE memory_value -- Assuming the original unit is GB

[0157] END;

[0158] B. By creating a "unit conversion dimension table" (dim_unit_conversion) to store the conversion relationships of various units, dynamic conversion can be achieved through JOIN operations, improving maintainability.

[0159] S22 performs data integrity verification and repair to ensure data robustness;

[0160] In this embodiment, S22 includes:

[0161] (1) Null value handling strategy:

[0162] To directly fill in the default value: `UPDATE cleaned_table SET some_field = COALESCE(some_field, 'N / A');`

[0163] Logical deduction: For example, for missing device models, they can be filled in using a dedicated deduction function or query based on their IP address range, OID information, or information from other devices collected in the same batch.

[0164] Marking method: For important null values ​​that cannot be handled, instead of filling them directly, they are marked as IS_DIRTY and handled specially in subsequent simulation logic (such as skipping the simulation of the record or using a more complex interpolation algorithm).

[0165] (2) Outlier detection and smoothing:

[0166] Statistical method: The moving average and standard deviation are calculated using window functions, and values ​​that exceed the range of the mean ± 3 times the standard deviation are considered outliers.

[0167] SQL

[0168] WITH stats AS (

[0169] SELECT AVG(cpu_usage) as avg_usage, STDDEV(cpu_usage) as std_usage

[0170] FROM raw_table WHERE ... )

[0172] UPDATE cleaned_table

[0173] SET cpu_usage = (SELECT avg_usage FROM stats)

[0174] WHERE cpu_usage > (SELECT avg_usage + 3 * std_usage FROM stats);

[0175] Business rule method: Define hard rules based on business knowledge. For example, CPU utilization cannot exceed 100%, and network card traffic cannot be negative; such data can be directly corrected or set to null.

[0176] (3) Uniqueness constraints and duplicate data handling:

[0177] Use window functions such as ROW_NUMBER() OVER(PARTITION BY unique_key ORDER BY update_timeDESC) to rank duplicate data, and then keep only the top-ranked (i.e., the most recent) record.

[0178] S23, perform structure normalization and data enrichment, including:

[0179] (1) Parsing unstructured data: For semi-structured or unstructured data (such as JSON logs, free text alarm information), use the database's native JSON functions (such as JSON_EXTRACT, JSONB_TO_RECORD) or regular expressions (REGEXP_SUBSTR, REGEXP_REPLACE) to parse it into structured fields.

[0180] (2) Data enrichment: Add additional contextual information to the data by joining other dimension tables or lookup tables. For example, enrich the geographical region and department information of the device records by using the IP address range table.

[0181] The cleaned data is stored in the "cleaned source data table", which has a clear table structure, high data quality, and metadata about the cleaning process (such as cleaning time and cleaning batch ID), and is the only reliable input for subsequent data simulation processes.

[0182] S3, by creating a custom function within the database to encapsulate the intelligent logic of data simulation; and based on the time conditions and data patterns in the scheduled task, intelligently performing simulation trigger judgment and simulation data generation through the custom function;

[0183] S31, Create a simulated scheduling function inside the database to simulate the trigger judgment logic;

[0184] In this embodiment, a core simulation scheduling function, such as fn_simulation_scheduler(), is created in the database. This function serves as the entry point for direct calls by scheduled tasks and is responsible for coordinating the entire simulation process.

[0185] The basic judgment logic of the simulated scheduling function includes:

[0186] (1) Reading metadata: The function first reads all the simulation tasks that need to be monitored from the "cleaned source data table" or a dedicated "simulation task configuration table". Each task includes: task ID, corresponding site ID, data type, last_simulation_time (the time of the last successful simulation), simulation_interval (the simulation period, obtained from S1), and task status (such as ACTIVE, PAUSED). Loop judgment

[0187] (2) The simulation scheduling function iterates through all tasks in the ACTIVE state and performs the following calculations for each task:

[0188] current_time:=CURRENT_TIMESTAMP;

[0189] expected_next_time:=last_simulation_time+simulation_interval;

[0190] (3) Determine the triggering condition: If current_time >= expected_next_time, the task is deemed to have expired and needs to be executed. After triggering, the function will record a "simulation started" log, and then call the specific data generation function fn_generate_[data_type]_data(), passing the task ID, last_simulation_time, etc. as parameters.

[0191] In this embodiment, the characteristics of the simulated scheduling function include:

[0192] (1) Concurrency control: To prevent multiple long-running simulation tasks from consuming too much database resources at the same time, the scheduling function can maintain a "list of running tasks". Before calling the data generation function, it checks whether the number of currently running tasks is less than the set concurrency threshold (e.g., 5). If it exceeds the threshold, the task is temporarily suspended and placed in the waiting queue.

[0193] (2) Fault tolerance and retry:

[0194] Failure detection: The data generation function should return a status code (SUCCESS / FAILURE). If FAILURE is returned, the scheduling function will not update last_simulation_time.

[0195] Retry mechanism: Configure a retry counter (e.g., maximum 3 times) and retry interval (e.g., 5 minutes) for each task. When a task fails, the scheduling function sets its status to RETRYING and plans to execute it again after the retry interval. After the maximum number of retries is exceeded, the task status is set to FAILED and an alarm is sent.

[0196] (3) Time Compensation Mechanism (Catch-up): If the scheduler has not run for a long time due to system maintenance or other reasons, resulting in a backlog of simulation tasks for multiple cycles, simply executing them one by one may cause excessive instantaneous pressure on the system. The advanced scheduler can identify this situation and adopt an "aggregated simulation" strategy. For example, if it finds that 3 cycles (30 minutes) have been missed, it can call the data generation function once, but instruct it to generate all the data from the past 30 minutes at once (e.g., 6 records, one every 5 minutes), instead of triggering 3 function calls. This requires the data generation function to support time range parameters.

[0197] S32, based on a diverse generation strategy library, creates data generation functions within the database to simulate data generation logic;

[0198] The data generation function is the core of the simulation. This embodiment is not limited to a single generation method, but provides a pluggable "strategy library" that selects the most suitable strategy to generate or simulate data based on the data characteristics.

[0199] Strategy 1: Based on the random copying and perturbation of historical samples, the data generation function randomly selects a record as a template from the historical samples of the collected source data.

[0200] For example:

[0201] For numeric fields: Randomly perturb the values ​​in the template. For example: `new_value = template_value * (1 + (RAND() - 0.5) * 0.1)` achieves a fluctuation of ±5%. A more natural fluctuation can be generated using the `NORMAL_RAND()` function (if the database supports it).

[0202] For text fields: For device names, descriptions, etc., a random combination can be made from a predefined thesaurus, or a random suffix can be added to the original value.

[0203] For the timestamp field: Replace the template's timestamp with the logical time of the current period. For example, if the template time is 10:00:00 and the current simulation period is 11:00:00, then the timestamp of the new record will be 11:00:00.

[0204] Strategy 2: For data with clear patterns, synthesize simulated data based on statistical models; including:

[0205] (1) Prediction by time series: For performance indicators, the statistical model is constructed based on the statistical characteristics (mean, trend, seasonality) calculated from the source data, and the simulated data is synthesized based on the statistical model.

[0206] For example, CPU usage may have peak times in the morning and evening. The function can fluctuate around a baseline value based on different times of the day. `new_cpu_usage=base_usage_hourly[HOUR(current_time)]*(1+(RAND()-0.5)*0.1)`

[0207] (2) Through association generation, the statistical model is constructed based on the association rules mined from the source data, and the simulated data is synthesized based on the statistical model.

[0208] For example, after generating a high CPU alert, the function will insert a related alert record into another "firewall alert table" based on the probability defined in the rule (such as 90%).

[0209] Strategy 3: For data such as work orders and serial numbers, use database sequences (SEQUENCE) based on sequences and state machines to generate unique auto-incrementing IDs.

[0210] In a preferred implementation, for the source data with existing state transitions using strategy 3, a state machine is maintained within the function. State changes are driven by the average time taken for state transitions in historical data.

[0211] For example, for stateful data (such as work order status or fault ticket lifecycle), a state machine can be maintained within the function. The state changes are driven by the average time taken for state transitions in historical data. For instance, if a work order stays in the "processing" state for an average of 30 minutes, the function can automatically update its status to "resolved" after 30 minutes.

[0212] Strategy 4: Generate the simulated data functionally based on built-in database functions;

[0213] Example:

[0214] UUID(): Generates a unique identifier.

[0215] MD5(RAND()): Generates a random string.

[0216] FLOOR(RAND()*100): Generates a random integer between 0 and 99.

[0217] For step S3, the cross-platform SQL example code is as follows:

[0218] MySQL:

[0219] DELIMITER $$

[0220] CREATE FUNCTION fn_generate_server_metrics(p_task_id INT, p_base_timeDATETIME) RETURNS INT

[0221] BEGIN

[0222] DECLARE v_template_metrics JSON;

[0223] -- 1. Obtain a random template

[0224] SELECT JSON_OBJECT('cpu', cpu_usage, 'memory', mem_usage) INTO v_template_metrics

[0225] FROM cleaned_sample_data WHERE data_type = 'server_metrics' ORDERBY RAND() LIMIT 1;

[0226] -- 2. Generate new data and insert it

[0227] INSERT INTO target_metrics (server_id, cpu_usage, memory_usage,collect_time)

[0228] VALUES (

[0229] (SELECT server_id FROM task_table WHERE id = p_task_id),

[0230] JSON_UNQUOTE(JSON_EXTRACT(v_template_metrics, '$.cpu')) *(0.95 + RAND() * 0.1), -- Perturbation

[0231] JSON_UNQUOTE(JSON_EXTRACT(v_template_metrics, '$.memory')) *(0.95 + RAND() * 0.1),

[0232] p_base_time );

[0234] RETURN 0; -- SUCCESS

[0235] END$$

[0236] DELIMITER ;

[0237] ·PostgreSQL:

[0238] sql

[0239] CREATE OR REPLACE FUNCTION fn_generate_server_metrics(p_task_id INT,p_base_time TIMESTAMPTZ)

[0240] RETURNS INT AS $$

[0241] DECLARE

[0242] v_cpu_usage NUMERIC;

[0243] v_memory_usage NUMERIC;

[0244] BEGIN

[0245] -- 1. Get random template values

[0246] SELECT cpu_usage, memory_usage INTO v_cpu_usage, v_memory_usage

[0247] FROM cleaned_sample_data WHERE data_type ='server_metrics' ORDER BY RANDOM() LIMIT 1;

[0248] -- 2. Insert new data

[0249] INSERT INTO target_metrics (server_id, cpu_usage, memory_usage,collect_time)

[0250] VALUES (

[0251] (SELECT server_id FROM task_table WHERE id = p_task_id),

[0252] v_cpu_usage * (0.95 + RANDOM() * 0.1),

[0253] v_memory_usage * (0.95 + RANDOM() * 0.1),

[0254] p_base_time );

[0256] RETURN 0; -- SUCCESS

[0257] END;

[0258] $$ LANGUAGE plpgsql;

[0259] S33, based on the last acquisition time recorded in the cleaned data and the preset timed task cycle, determine whether a data simulation operation needs to be performed; perform the data simulation based on the determination that a data simulation operation needs to be performed.

[0260] Step S33 ensures that data simulation is performed only when necessary, avoiding unnecessary resource consumption and reducing the time required for data simulation.

[0261] S4, based on the intermediate table's buffering and batch optimization mechanism, performs efficient data injection;

[0262] Directly inserting data generated from functions row by row or in small batches into a large target business table results in frequent transaction commits and index updates, leading to significant performance bottlenecks, especially in high-frequency simulation scenarios. To address this, this invention introduces an "intermediate table" as a high-performance buffer and performs engineering optimizations on the entire insertion process.

[0263] The role of the intermediate table for data migration

[0264] (1) Data storage format: File storage. With the disk I / O rate remaining constant, the smaller the file, the faster the search.

[0265] (2) Retrieve only the operation data: Extract the data we want to simulate from the large table based on the conditions.

[0266] (3) Lifecycle of intermediate tables: Intermediate tables are temporary tables that are created at the beginning of the algorithm and destroyed when the algorithm ends.

[0267] Simulated data processing

[0268] (1) Modify the status of the monitored item

[0269] The state of the monitored item is changed from the simulated state to the stateless state, and then changed back to the simulated state by time conditions during the next algorithm call;

[0270] (2) Destroy intermediate tables

[0271] Destroy intermediate tables to save storage space on the database server.

[0272] S41, an intermediate table is designed for data buffering; wherein the structure of the intermediate table is completely identical to that of the target table, thereby simplifying the transfer operation from the intermediate table to the target table. The intermediate table serves as a temporary working area to maximize write speed. Therefore:

[0273] (1) Indexes: No secondary indexes are usually created. This is because the overhead of maintaining an index far outweighs the query benefits when inserting in batches.

[0274] (2) Constraints: No foreign key constraints, unique constraints, etc. are set. All constraint checks are postponed until the final insertion into the target table.

[0275] (3) Storage Engine: Prioritize engines with high write performance. In MySQL, use MyISAM (if transactions are not required) or ARCHIVE engines, or use InnoDB directly but set innodb_flush_log_at_trx_commit=0 (sacrificing some durability for speed). In databases that support in-memory tables (such as MySQL's MEMORY engine), in-memory tables are the best implementation if the data volume is small.

[0276] (4) Lifecycle management: The intermediate table is created during system initialization. In each simulation task, TRUNCATETABLE is first cleared before a new round of batch insertion is performed. TRUNCATE is a DDL operation, which is faster than DELETE and does not generate undo logs.

[0277] S42, Establish a batch optimization mechanism to optimize the batch insertion process; wherein, the batch insertion process includes:

[0278] (1) Batch insertion into intermediate tables, including: the data generation function constructs a value list in memory from the generated multiple records (such as 1000 records), and completes it through an INSERT statement.

[0279] SQL

[0280] INSERT INTO intermediate_table (field1, field2, ...)

[0281] VALUES

[0282] (value1_1, value1_2, ...),

[0283] (value2_1, value2_2, ...), ...

[0285] (valueN_1, valueN_2, ...);

[0286] As a preferred implementation, the batch insertion into the intermediate table can also be achieved using either batch writing or the LOAD DATAINFILE method. Batch writing includes: if the amount of data generated in a single operation is extremely large (e.g., 100,000 rows), it can be submitted in batches (e.g., 1,000 rows at a time) to prevent single SQL statements from becoming too large or exceeding the database's package size limit. Using LOAD DATAINFILE includes: for MySQL, the data generation function can first write the data to a CSV file, and then use the LOADDATA INFILE 'file_name' INTO TABLE intermediate_table command. This is the fastest batch data loading method in MySQL.

[0287] (2) Second step: Transfer the transaction from the intermediate table to the target table.

[0288] SQL

[0289] START TRANSACTION;

[0290] INSERT INTO target_table (field1, field2, ...)

[0291] SELECT field1, field2, ... FROM intermediate_table;

[0292] TRUNCATE TABLE intermediate_table; -- Clear the table for later use

[0293] COMMIT;

[0294] As a more preferred implementation, the transactional transfer from the intermediate table to the target table can also be achieved in the following way:

[0295] Disabling indexes / constraints: Before inserting data into a large target table, you can temporarily disable non-unique indexes (ALTERTABLE ... DISABLE KEYS in MySQL) and foreign key constraints (SET FOREIGN_KEY_CHECKS=0). After inserting, re-enable and rebuild them. This can significantly improve speed.

[0296] Deferred index maintenance: Some databases (such as Google Cloud Spanner) support deferred index maintenance, which allows index updates to be postponed until after a transaction is committed and performed in batches.

[0297] S43, perform exception handling and data consistency processing;

[0298] The exception handling process includes ensuring the entire "transfer from intermediate table -> clear intermediate table" process is performed within a single database transaction. If the transfer fails, the transaction is rolled back, the data in the intermediate table is not lost, and retry is possible. The clear operation is only performed after successful insertion into the target table is confirmed.

[0299] Data consistency processing includes performing a lightweight data quality check on the intermediate table before the transfer (such as checking whether required fields are NULL) to prevent dirty data from polluting the target table.

[0300] S5 enables timed task cycle adjustment, thereby achieving precise, efficient and adaptive scheduling;

[0301] Scheduled tasks are the "pulse" of the entire simulation process, and setting their cycle is an art of trade-offs, requiring the finding of the optimal balance between the simulation's "real-time performance" and "system load".

[0302] In a preferred embodiment, S5 includes:

[0303] S51, Establish a refined mathematical model for periodic settings;

[0304] In this embodiment, the period (T) of the timed task needs to be a dynamic and calculable value.

[0305] The core variables of this refined model include:

[0306] t_sim: The period defined by the simulation data (business requirements), obtained from step S1;

[0307] t_exec_avg: The average execution time of the simulation function (obtained from step S3) and the injection process (obtained from step S4), which needs to be calculated in real time through system monitoring.

[0308] t_exec_max: The historical maximum execution time of the above process (used to handle worst-case scenarios).

[0309] t_overhead: The overhead of the scheduler itself (traversing tasks, making judgments, etc.) and the system safety margin.

[0310] The calculation formula for this refined model includes:

[0311] (1) Most conservative strategy (ensuring no accumulation): T = min(t_sim, t_exec_max + t_overhead);

[0312] This strategy ensures that even if a particular execution takes the longest, it will be completed before the next cycle begins.

[0313] (2) Cost optimization strategy (balancing efficiency and stability):

[0314] T=min(t_sim,k*t_exec_avg+t_overhead);

[0315] k is the safety factor, typically ranging from 1.5 to 3. It allows for normal fluctuations in execution time and utilizes resources more aggressively than a conservative strategy.

[0316] (3) Adaptive strategy: The system continuously monitors t_exec_avg and t_exec_max, and periodically (e.g., every 24 hours) recalculates and adjusts T according to the formula. This enables the system to adapt to the growth of data volume or changes in hardware performance.

[0317] S52 implements scheduled tasks and is adapted for cross-platform compatibility;

[0318] The MySQL code in step S52 uses the CREATE EVENT statement, as shown below:

[0319] SQL

[0320] CREATE EVENT event_simulation_scheduler

[0321] ON SCHEDULE EVERY 1 MINUTE -- This 1 minute is the initial value and can be dynamically adjusted according to the S5.1 formula (DROP must be performed before CREATE).

[0322] STARTS CURRENT_TIMESTAMP

[0323] DO

[0324] CALL fn_simulation_scheduler(); -- Assuming the scheduling logic is encapsulated in a stored procedure

[0325] • PostgreSQL (using the pg_cron extension):

[0326] SQL

[0327] -- Configure shared_preload_libraries = 'pg_cron' in postgresql.conf

[0328] SELECT cron.schedule('simulation-scheduler', '* * * * *', 'CALL fn_simulation_scheduler()'); -- Execute every minute

[0329] The Oracle code in step S52 uses the DBMS_SCHEDULER statement, as shown below:

[0330] SQL

[0331] BEGIN

[0332] DBMS_SCHEDULER.CREATE_JOB (

[0333] job_name => 'SIMULATION_SCHEDULER_JOB',

[0334] job_type => 'PLSQL_BLOCK',

[0335] job_action => 'BEGIN fn_simulation_scheduler; END;',

[0336] start_date => SYSTIMESTAMP,

[0337] repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',

[0338] enabled => TRUE );

[0340] END;

[0341] S53 is based on a monitoring system for system monitoring and resource protection.

[0342] To achieve adaptive control and system stability of S51, this embodiment establishes a comprehensive monitoring system.

[0343] The monitoring indicators of the monitoring system include:

[0344] Performance metrics: t_exec_avg, t_exec_max, task queue length, database CPU utilization, IOPS.

[0345] Business metrics: Simulated data volume for each task, simulation latency (current time - last simulation time), and task success rate.

[0346] In this embodiment, as a preferred implementation, the system monitoring based on the monitoring system includes: marking key steps in the scheduling function and data generation function, recording the start and end times, and writing them into a "simulated execution log table"; and monitoring for long transactions or lock waits through database system views (such as information_schema.PROCESSLIST).

[0347] In this embodiment, as a preferred implementation, resource protection based on a monitoring system includes:

[0348] Automatic degradation: When the overall database load exceeds a certain threshold (e.g., CPU > 80%), the scheduler automatically pauses some non-core simulation tasks until the load decreases.

[0349] Alerts: When a task fails, simulation delay exceeds a threshold, or system resources are continuously in short supply, the system will notify maintenance personnel via email, SMS, DingTalk, or other means.

[0350] Example 2

[0351] like Figure 3 As shown, this embodiment provides a data simulation system based on database functions and scheduled tasks, including:

[0352] Source data collector (101) is used to acquire source data based on a multimodal lightweight intelligent acquisition strategy;

[0353] A data cleaning engine (102) is used to perform data cleaning and standardization, thereby ensuring the engineered processing of simulation data quality and consistency;

[0354] The simulation logic core (103) is used to encapsulate the intelligent logic of data simulation by creating a custom function inside the database; and to intelligently perform simulation trigger judgment and simulation data generation through the custom function according to the time conditions and data patterns in the scheduled task.

[0355] The high-efficiency injection module (104) is used for high-efficiency data injection based on the buffer and batch optimization mechanism of the intermediate table;

[0356] The task scheduler (105) is used to adjust the cycle of timed tasks.

[0357] In this embodiment, the source data collector is deployed as a lightweight agent or interface on various field sites or the central platform, responsible for executing the tasks in phase S1. The data cleaning engine, built into the central database or as a standalone service, is primarily responsible for the standardized processing in phase S2. The simulation logic core, composed of a series of carefully designed database functions (Stored Functions / Procedures), is the brain of phase S3. The high-efficiency injection module, through intermediate tables and batch operation techniques in phase S4, achieves high-speed, low-impact data writing to the target table. The task scheduler, utilizing the database's own job scheduling components (such as MySQL Event Scheduler, Oracle DBMS_SCHEDULER, PostgreSQL pg_cron) or an external scheduling framework, is responsible for controlling the pace of phase S5.

[0358] The system in this embodiment will set up a unified monitoring and management console, through which users can configure data acquisition tasks, monitor simulation status, view logs, and adjust system parameters via an optional visual interface.

[0359] Establish an algorithm for automated execution of database jobs

[0360] CREATE EVENT `prc_zbx_moni_job` ON SCHEDULE EVERY 60 MINUTE STARTSNOW() ON COMPLETION NOT PRESERVE ENABLE DO call prc_zbx_moni(0);

[0361] Job name: prc_zbx_moni_job

[0362] Execution interval: 60 minutes

[0363] Start time: NOW()

[0364] Call the algorithm: call prc_zbx_moni(0).

[0365] Related code (analysis of floating-point type class)

[0366] -- Reading the minimum timestamp

[0367] SELECT MIN(clock) into @minclock from history where itemid in (SELECTitemid from zbx_items where value_type=0 and state=1);

[0368] -- Calculate the maximum timestamp

[0369] SELECT MAX(delays) into @max_delays from zbx_items where value_type=0and state=1;

[0370] set @maxclock=@minclock+@max_delays;

[0371] set @absclock=@nowclock-@maxclock;

[0372] -- Start moving data and creating an intermediate table

[0373] DROP TABLE IF EXISTS history_moni;

[0374] CREATE TABLE history_moni LIKE history;

[0375] INSERT into history_moni(itemid,clock,`value`,ns) SELECT itemid,clock+@absclock,`value`,ns from history where itemid in (SELECT itemid from zbx_items where value_type=0 and state=1) and clock BETWEEN @minclock and @maxclock;

[0376] INSERT into history SELECT * from history_moni;

[0377] UPDATE zbx_items set state=0 where value_type=0 and itemid in (SELECTitemid from history_moni);

[0378] DROP TABLE IF EXISTS history_moni;

[0379] The present invention also provides a memory that stores multiple instructions for implementing the method as described in Embodiment 1.

[0380] like Figure 4 As shown, the present invention also provides an electronic device, including a processor 301 and a memory 302 connected to the processor 301. The memory 302 stores a plurality of instructions, which can be loaded and executed by the processor to enable the processor to perform the method as described in Embodiment 1.

[0381] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention. Clearly, those skilled in the art can make various alterations and modifications to the invention without departing from its spirit and scope. Thus, if these modifications and modifications of the invention fall within the scope of the claims and their equivalents, the invention is also intended to include these modifications and modifications.

Claims

1. A data simulation method based on database functions and scheduled tasks, characterized in that, include: S1, acquires source data based on a multimodal lightweight intelligent acquisition strategy; S2 performs data cleaning and standardization, thereby ensuring the quality and consistency of the simulated data through engineered processing. S3, by creating a custom function within the database to encapsulate the intelligent logic of data simulation; and based on the time conditions and data patterns in the scheduled task, intelligently performing simulation trigger judgment and simulation data generation through the custom function; S4, based on the intermediate table's buffering and batch optimization mechanism, performs efficient data injection; S5 is used for timed task cycle adjustment.

2. The data simulation method based on database functions and scheduled tasks according to claim 1, characterized in that, The source data in S1 includes: The basic entity data used to characterize static features is collected following the principle of unique identifier + key attribute. The key to simulating dynamic behavior is to use time-series and periodic feature data to characterize dynamic behavior, thereby enabling the simulation data to have realism in the time dimension. Relationship and rule data used to characterize contextual logic, thereby describing information about the relationships between data entities and business rules; Data source metadata is used to characterize technical metadata, thereby describing the technical information from which the data originates and ensuring the traceability of the simulation process.

3. The data simulation method based on database functions and scheduled tasks according to claim 2, characterized in that, The multimodal lightweight intelligent acquisition strategy of S1 includes using method A, method B, and method C individually, or using a combination of the three methods, wherein: Method A is an automated agent script copying method, which includes: deploying a lightweight agent program for each site, the agent containing predefined data extraction logic, which can automatically query and export the source data from the site's database, configuration file, API interface or log system according to the configuration, package it into one or more structured data files, and then incrementally transmit it to the designated receiving directory of the central platform through a secure channel; or, containerized agent; or, differential acquisition; or, compression and encryption. Method B is manual input and configuration, including: providing a human-computer interaction interface for data that cannot be automatically obtained or requires expert knowledge to refine and confirm, thereby allowing maintenance personnel or field engineers to manually enter, select or configure this information; or, the interface provides template import / export functions, data validity verification and linkage with the configuration management database, thereby automatically pulling some basic entity data from the configuration management database; Method C is a message queue-based streaming acquisition method for scenarios with high real-time requirements. It includes: on the field side, publishing change events of source data to the message queue in real time; and on the central platform, deploying a consumer service to subscribe to messages from the queue and perform parsing and storage.

4. The data simulation method based on database functions and scheduled tasks according to claim 3, characterized in that, S2 includes: S21, perform format standardization processing; S22 performs data integrity verification and repair to ensure data robustness; S23, perform structural normalization and data enrichment.

5. The data simulation method based on database functions and scheduled tasks according to claim 4, characterized in that, S3 includes: S31, create a simulated scheduling function within the database to simulate trigger judgment logic; where: The basic decision logic of the simulated scheduling function includes: reading metadata and iterating through all tasks in the ACTIVE state, performing the following calculations for each task: current_time:=CURRENT_TIMESTAMP; expected_next_time:=last_simulation_time+simulation_interval; and determine the trigger condition: if current_time>=expected_next_time, then the task is determined to have expired and needs to be executed. After triggering, the simulation scheduling function records a simulation start log, and then calls the data generation function fn_generate_[data_type]_data(), passing the task ID and last_simulation_time as parameters; The characteristics of the simulated scheduling function include: concurrency control, fault tolerance and retry mechanism, and time compensation mechanism; S32, Create data generation functions within the database based on a diversified generation strategy library to simulate data generation logic; wherein, the strategies of the diversified generation strategy library include: Strategy 1: Based on the random copying and perturbation of historical samples, the data generation function randomly selects a record as a template from the historical samples of the collected source data; Strategy 2: For data with clear patterns, synthesize simulated data based on statistical models; Strategy 3: For the source data with state transitions, a unique auto-incrementing ID is generated using the database sequence based on the sequence and state machine; for those using Strategy 3, a state machine is maintained within the function to drive state changes based on the average time taken for state transitions in historical data. Strategy 4: Generate the simulated data functionally based on built-in database functions; S33, based on the last acquisition time recorded in the cleaned data and the preset timed task cycle, determine whether a data simulation operation needs to be performed; perform the data simulation based on the determination that a data simulation operation needs to be performed.

6. The data simulation method based on database functions and scheduled tasks according to claim 5, characterized in that, S4 includes: S41, an intermediate table designed for data buffering and for maximizing write speed; wherein the structure of the intermediate table is completely identical to that of the target table and serves as a temporary workspace; S42, Establish a batch optimization mechanism to optimize the batch insertion process; wherein, the batch insertion process includes: (1) Batch insertion into intermediate tables, including: the data generation function constructs a value list of multiple generated records in memory and completes it through an INSERT statement; or, it is completed by batch writing or LOAD DATA INFILE. (2) Transfer the transaction from the intermediate table to the target table, or, if the target table exceeds the threshold before insertion, temporarily disable the non-unique index and foreign key constraints, and re-enable and rebuild the non-unique index and foreign key constraints after the insertion is completed; or, delay index maintenance and postpone index updates until the transaction is committed in batches. S43 performs exception handling and data consistency processing, including: if the transfer fails, the transaction is rolled back, the data in the intermediate table will not be lost, and it can be retried; the clear operation is only executed after the target table is confirmed to have been successfully inserted; and a lightweight data quality check is performed on the intermediate table before the transfer.

7. The data simulation method based on database functions and scheduled tasks according to claim 6, characterized in that, S5 includes: S51, Establish a refined mathematical model for periodic setting; wherein, the core variables of the refined model include: t_sim: The period defined by the simulation data, obtained from step S1; t_exec_avg: The average execution time of the simulation function obtained from step S3 and the injection process obtained from step S4 is calculated in real time by system monitoring; t_exec_max: The maximum execution time in history; t_overhead: The overhead of the scheduler itself and the system safety margin; The period calculation formula for the refined model includes: Periodic calculation based on the most conservative strategy: T = min(t_sim, t_exec_max + t_overhead); Cycle calculation based on cost optimization strategy: T=min(t_sim,k*t_exec_avg+t_overhead); where k is the safety factor; The calculation based on the adaptive strategy includes: the system continuously monitors t_exec_avg and t_exec_max, and periodically recalculates and adjusts the period according to the formula; S52 implements scheduled tasks and is adapted for cross-platform compatibility; S53, System monitoring and resource protection are performed based on a monitoring system; the monitoring indicators of the monitoring system include: Performance metrics: t_exec_avg, t_exec_max, task queue length, database CPU utilization, IOPS; and business metrics: simulated data volume, simulation latency, and task success rate for each task. The system monitoring based on the monitoring system includes: marking key steps in the scheduling function and data generation function, recording the start and end times, and writing them into a "simulated execution log table"; and monitoring for long transactions or lock waits through the database system view. Resource protection based on monitoring systems includes: Automatic degradation: When the overall database load exceeds the threshold, the scheduler automatically pauses some non-core simulation tasks until the load decreases; Alerts: Notify operations and maintenance personnel when a task fails, simulation delay exceeds a threshold, or system resources are continuously in short supply.

8. A data simulation system based on database functions and scheduled tasks, used to implement the method according to any one of claims 1-7, characterized in that, include: Source data collector (101) is used to acquire source data based on a multimodal lightweight intelligent acquisition strategy; A data cleaning engine (102) is used to perform data cleaning and standardization, thereby ensuring the engineered processing of simulation data quality and consistency; The simulation logic core (103) is used to encapsulate the intelligent logic of data simulation by creating a custom function inside the database; and to intelligently perform simulation trigger judgment and simulation data generation through the custom function according to the time conditions and data patterns in the scheduled task. The high-efficiency injection module (104) is used for high-efficiency data injection based on the buffer and batch optimization mechanism of the intermediate table; The task scheduler (105) is used to adjust the cycle of timed tasks.

9. An electronic device, characterized in that, It includes a processor and a memory, the memory storing multiple instructions, and the processor being used to read the instructions and execute the method as described in any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a plurality of instructions, which can be read by a processor and executed as described in any one of claims 1-7.

Images