Data development system and method based on template configuration

Abstract

The invention discloses a data development system and method based on WPS template configuration, and the method comprises the steps: S1, carrying out demand analysis and data modeling; S1.1, carrying out the communication with a business department: firstly, carrying out the communication with the business department, and understanding the change of business demands; for complex requirements, the most critical output index and data processing flow can be determined through a guiding problem. Through combination of WPS template configuration and SQL script arrangement, an efficient and flexible solution is provided. A user completes data development through a graphical configuration interface, dependence on programming capability is reduced, and the threshold of data development is lowered. Meanwhile, through a template engine and a script arrangement technology, the system can automatically generate a complex ETL script according to configuration, the development process is optimized, the development period is shortened, and the development efficiency is improved. In addition, with the support of functions of monitoring, optimization, expansion and the like, the service requirements of rapid change can be better met, and a stable and reliable operation environment is provided.

Description

Technical Field

[0001] The present invention relates to the technical field of WPS template configuration, and particularly to a data development system and method based on template configuration. Background Art

[0002] In the rapidly developing field of information technology today, data development has become one of the core requirements of enterprises. Various data development systems (web reporting tools) are common in the market, such as the FineReport platform developed by Finereport Company and the JimuReport platform developed by the JeecgBoot team, an open-source alternative. In addition, the mainstream data development methods mainly include three types: the first is the hard-coding method, which completes development by developing the front-end page and writing SQL scripts; the second is the online drag-and-drop method, which automatically generates code by online drag-and-drop through a graphical tool to complete development; the third is the method that combines tools and SQL to complete development.

[0003] With the explosion of data volume and the continuous change of business requirements, the first two data development systems and development methods face many challenges. The first hard-coding method is mainly applicable to developers in the technical department, who are required to have some data development capabilities, but the technical level and business understanding ability of data development vary. The hard-coding method has relatively high requirements for programming ability, requiring an understanding of SQL processing logic and the association relationship between data tables; the development cycle is long and it is difficult to adapt to rapidly changing business requirements. The second online drag-and-drop method is mainly applicable to junior developers or some business personnel. This method is relatively intuitive, lacking flexibility and scalability, etc., and it is difficult to meet the needs of agile development. To address the above problems, the present invention introduces the concept of WPS template configuration and provides a method that can improve data development quality and reduce data through the combination of tools and SQL.

[0004] Therefore, we propose a data development system and method based on WPS template configuration. Summary of the Invention

[0005] To achieve the above object, the present invention provides the following technical solution: A data development system and method based on WPS template configuration, including the following steps:

[0006] S1: Requirement analysis and data modeling

[0007] S1.1: Communicate with the business department: First, it is necessary to communicate with the business department to understand the changes in business requirements. For complex requirements, the most critical output indicators and data processing processes can be determined through guiding questions;

[0008] S1.2: Data Model Design: Based on the requirements analysis, design the data model. Determine the data sources, data tables, data fields, and relationships involved in the business requirements (e.g., how the tables in the data source are associated through foreign keys and the dependency relationships between fields);

[0009] S2: Data Source Sorting and ETL Process Design

[0010] S2.1: ETL Process Analysis: Analyze the processes of data extraction (Extract), transformation (Transform), and loading (Load) to ensure that each step of the ETL meets the business requirements;

[0011] S2.2: Template Configuration: Use the WPS template to configure the data fields to ensure that developers can conveniently configure the mapping relationships between the data source fields and the target fields through the template;

[0012] S3: WPS Template Configuration

[0013] S3.1: WPS Template Structure Design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationships of the data source. The template contains field information for each data table, transformation logic (such as data type conversion, field concatenation, calculation, etc.), conditional filtering, and other information;

[0014] S3.2: Dynamic Template: Design a dynamic template engine to generate different templates according to the business requirements. These templates will automatically generate adapted ETL scripts, providing a basis for subsequent development;

[0015] S4: Template Field Configuration

[0016] S4.1: Field Mapping Configuration: Through the configuration page provided in the WPS template, users can select different data source fields and target fields for mapping. This mapping process no longer requires writing cumbersome SQL but is completed through a graphical interface;

[0017] S4.2: Calculated Field Configuration: In the WPS template, developers can configure some fields that need to be calculated or transformed through formulas or functions, such as calculating the sum, average, etc. of a certain metric based on existing fields;

[0018] S5: Transformation Logic Configuration

[0019] S5.1: SQL Script Embedding: In the template, SQL scripts can be embedded through specific tags, and users can configure the transformation logic through a graphical interface. Complex data transformation operations can be directly written in SQL scripts and associated with the fields configured in the template;

[0020] S5.2: Dynamic Script Generation: According to the configurations in the template, the system will dynamically generate SQL scripts, which include functions such as field conversion, data filtering, and conditional judgment.

[0021] S6: System Engine and Script Orchestration

[0022] S6.1: WPS Template Parsing: The system has a built-in WPS template engine that can parse the configurations in the WPS template. Contents such as field mapping relationships, conversion rules, and SQL script embedding in the template are automatically converted into executable ETL scripts by the template engine;

[0023] S6.2: Dynamic Script Generation: According to different business requirements, the system automatically generates different ETL scripts, supporting complex operations between multiple SQL scripts (such as SELECT, INSERT, UPDATE, DELETE, etc.) and data sources.

[0024] S7: Automatic SQL Script Orchestration

[0025] S7.1: Script Execution Scheduling: Through the script scheduling engine, the ETL scripts can be executed regularly according to requirements. The scheduling engine can manage the execution order, dependency relationships, and execution times of the scripts;

[0026] S7.2: Automatic Processing: The system will automatically generate and execute SQL scripts according to preset rules to complete data extraction, transformation, and loading operations. Business personnel do not need to write SQL and only need to configure through the WPS template.

[0027] S8: Real-time Data Monitoring and Tuning

[0028] S8.1: Real-time Data Monitoring Panel: The system provides a real-time data monitoring panel to monitor the execution status of each ETL task. The monitoring contents include execution status, execution time, success and failure records, etc.;

[0029] S8.2: Exception Alarm Mechanism: When an error occurs during data processing, the system can send alarm messages to relevant personnel in a timely manner to ensure that the data development tasks are not affected;

[0030] S8.3: Automatic Detection: During the data development process, the system can automatically perform data quality detection to check whether the data meets the predetermined rules (such as field types, value ranges, field integrity, etc.);

[0031] S8.4: Real-time Data Verification: When loading data, the system verifies the correctness and consistency of the data in real time to ensure that no errors occur in data processing.

[0032] S9: Script Optimization and Performance Tuning

[0033] S9.1: SQL Optimization: The system automatically identifies SQL scripts that may have performance issues and optimizes them (e.g., index optimization, join condition optimization, subquery optimization, etc.);

[0034] S9.2: Parallel Execution: Supports multi-threaded or distributed computing methods to improve the execution efficiency of ETL scripts. Especially in a big data environment, it can significantly enhance the data processing speed.

[0035] S10: System Extension and Integration

[0036] S10.1: Data Source Adapter: The system provides a rich set of data source adapters that support seamless connection with different types of data sources (such as MySQL, PostgreSQL, Oracle, Hadoop, Spark, etc.). Through these adapters, users can easily migrate data between different data platforms;

[0037] S10.2: API Interface: Provides RESTful API interfaces that allow third-party systems or applications to call the data development system for data processing or querying, supporting integration with external data platforms;

[0038] S10.3: Custom Script Module: Developers can write custom SQL scripts or Python scripts according to business requirements for complex ETL logic processing;

[0039] S10.4: Plugin Mechanism: The system supports plugin-based extension and can quickly integrate new data sources, transformation rules, or functional modules according to user needs.

[0040] S11: Deployment and Operation and Maintenance

[0041] S11.1: Automatic Script Deployment: The system can automatically deploy the generated SQL scripts to the target environment and regularly update or adjust the scripts to adapt to new business requirements;

[0042] S11.2: Containerized Deployment: Packages the system into a container and supports deployment on containerized platforms such as Docker, enhancing the scalability and operation and maintenance convenience of the system;

[0043] S11.3: Permission Management: The system provides a fine-grained permission management mechanism to ensure that users with different roles can only access the data and functions they have permissions to;

[0044] S11.4: System Log and Audit: Records all operation logs of the system, facilitating problem tracking, auditing, and maintenance by operation and maintenance personnel.

[0045] S12: WPS Template Configuration

[0046] S12.1: WPS Template Structure Design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationships of the data source. The template contains field information for each data table, conversion logic (such as data type conversion, field concatenation, calculation, etc.), conditional filtering, and other information;

[0047] S12.2: Dynamic Template: Design a dynamic template engine to generate different templates according to business requirements. These templates will automatically generate adapted ETL scripts, providing a basis for subsequent development;

[0048] S12.3: Field Mapping Configuration: Through the configuration page provided in the WPS template, users can select different data source fields to map with target fields. This mapping process no longer requires writing cumbersome SQL, but is completed through a graphical interface. Calculated Field Configuration: In the WPS template, developers can configure some fields that need to be calculated or converted through formulas or functions, such as calculating the sum, average, etc. of a certain metric based on existing fields. Conversion Logic Configuration;

[0049] S12.4: SQL Script Embedding: In the template, SQL scripts can be embedded through specific tags, and users configure the conversion logic through a graphical interface. Complex data conversion operations can be directly written as SQL scripts and associated with the fields configured in the template;

[0050] S12.5: Dynamic Script Generation: According to the configuration in the template, the system will dynamically generate SQL scripts, including functions such as field conversion, data filtering, and conditional judgment.

[0051] Preferably, before communicating with the business department in step S1, prepare a basic requirements analysis document covering the current system status, known problems, and possible solutions. Through one-on-one interviews, focus group discussions, etc., understand the requirements of the business department. Leading questions can help uncover hidden requirements. For example: "What are the most commonly used metrics in your data analysis?" and "What additional insights do you hope the system can provide?" etc. Organize the requirements through documents or online tools (such as Jira, Confluence) and confirm with the business department to ensure the requirements are clear and definite. For complex requirements, use prototype design or data flow diagrams to help clarify. Based on the requirements analysis, identify the main data sources involved, which may include CRM systems, ERP systems, third-party data providers, etc. Design the data table structure, clarify the fields, data types, and their relationships of each data table. For example, order tables, user tables, product tables, etc. Show the relationships between data tables through ER diagrams (Entity Relationship Diagrams) or UML class diagrams. Design foreign key constraints, indexes, etc. to ensure data consistency and integrity.

[0052] Preferably, in step S2, determine from which data sources to extract data, formulate a data extraction plan (full extraction or incremental extraction), select a suitable extraction tool or SQL query script, and perform processing such as cleaning, merging, deduplication, and standardization on the extracted data. This step usually requires writing conversion rules, which may include data type conversion, data cleaning (such as removing null values or outliers), etc., and determining the loading method for the target database or data warehouse, whether it is full loading or incremental loading. Design a suitable loading strategy according to the data volume and the performance requirements of the target table (for example, batch insertion, batch-by-batch loading, etc.).

[0053] Preferably, in step S2, use a WPS template (or a similar tool) to design the template format and determine the field mapping rules. The WPS template should include the mapping relationship between the source table and the target table, data cleaning rules, conversion logic, etc. Develop a graphical interface for users to select the mapping relationship between the source data fields and the target fields, ensuring that the field names, data types, and conversion rules can be directly configured through the interface.

[0054] Preferably, in step S3, design the overall framework of the template, including information such as field names, data types, conversion rules, and target fields. List the specific definitions of the source table, target table, and fields in detail in the template. For example: field name, type, optionality (required / optional), default value, etc. Clearly define the way of conversion rules, including field type conversion, calculation rules, data cleaning logic, etc. Design a dynamic template engine that can automatically generate different templates according to business requirements. This can be achieved through a template configuration file combined with a rule engine. The template engine automatically generates the corresponding ETL script according to the business requirements input by the user, supporting customized generation of different scripts (such as data extraction scripts, conversion scripts, etc.).

[0055] Preferably, in step S4, develop a graphical interface that allows users to complete the mapping of source fields and target fields by dragging or selecting. This tool should support various field types (such as numbers, texts, dates, etc.), provide a real-time preview function, enabling users to see the actual effect after field mapping, ensuring that the configuration is correct, and allowing developers to design calculated fields in the WPS template through formulas or functions. For example, calculate the sum, average value, growth rate, etc. of fields in a data table, and support users to customize functions or SQL scripts for complex calculation or conversion operations.

[0056] Preferably, in step S5, embed an SQL script in the template, through specific tags (such as <sql>)Define custom SQL statements in the template. In this way, users can configure SQL logic through a graphical interface, such as data filtering, transformation, etc. Provide an SQL script template, and users can quickly generate or modify SQL scripts according to business requirements. According to the field mapping and transformation rules in the template, the system automatically generates SQL scripts. For example, according to the mapping relationship, SQL statements such as INSERT and UPDATE are automatically generated, and the generated SQL scripts are automatically merged to avoid redundant operations and optimized to improve execution efficiency.

[0057] Preferably, in step S6, design a WPS template parsing engine that can read the field mapping, transformation rules, SQL scripts, etc. in the WPS template and convert these configurations into specific ETL execution steps. The engine should support dynamically generating adapted ETL scripts according to different requirements, automatically adjusting the fields, table structures, etc. According to the changes in business requirements, the system should be able to dynamically generate different types of ETL scripts, support complex operations between different data sources and data tables. When generating scripts, the system automatically optimizes the SQL statements to ensure execution efficiency.

[0058] Preferably, in step S7, develop a scheduling system that supports the regular execution of ETL scripts. Users can set the execution period (such as daily, weekly, etc.) and priority. The scheduling engine will automatically execute tasks, manage the dependencies between scripts, and ensure the sequence in the data extraction, transformation, and loading processes. The system can automatically generate and execute ETL scripts according to preset rules. Through the user-defined template, the system automatically obtains data, executes transformations, and loads data, reducing manual intervention.

[0059] Preferably, in step S8, develop a real-time monitoring panel to display the execution status of each ETL task, including execution status (success / failure), execution time, task logs, etc. Intuitively display the execution results of tasks through charts, pie charts, etc. to help business personnel quickly identify problems. Set thresholds. When data processing errors occur (such as data quality problems, task execution failures, etc.), the system will automatically trigger an alarm notification. Design automation rules to detect the quality of data regularly or in real time, such as field types, value ranges, data integrity, etc., and generate a data quality report for data developers to further check and repair. During the data loading process, the system performs real-time verification to ensure that the data conforms to the expected format, range, and business rules. If the data verification fails, the system will automatically repair or send an alarm.

[0060] Compared with the prior art, the present invention provides a data development system and method based on WPS template configuration, having the following beneficial effects:

[0061] The data development system and method based on WPS template configuration provides an efficient and flexible solution by combining WPS template configuration with SQL script orchestration. Users can complete data development through a graphical configuration interface, reducing the dependence on programming skills and lowering the threshold of data development. At the same time, through the template engine and script orchestration technology, the system can automatically generate complex ETL scripts according to the configuration, optimize the development process, shorten the development cycle, and improve development efficiency. Coupled with the support of functions such as monitoring, optimization, and expansion, it can better meet the rapidly changing business needs and provide a stable and reliable operating environment. Detailed implementation manners

[0062] The technical solutions in the embodiments of the present invention will be clearly and completely described below. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0063] Embodiment

[0064] An embodiment of a data development system and method based on WPS template configuration

[0065] A data development system and method based on WPS template configuration includes the following steps:

[0066] S1: Requirement analysis and data modeling

[0067] S1.1: Communicate with the business department: First, it is necessary to communicate with the business department to understand the changes in business requirements. For complex requirements, the most critical output indicators and data processing processes can be determined through guiding questions;

[0068] S1.2: Data model design: Based on requirement analysis, design a data model. Determine the data sources, data tables, data fields, and relationships involved in business requirements (for example, how the tables in the data source are associated through foreign keys and the dependency relationships between fields);

[0069] S2: Data source sorting and ETL process design

[0070] S2.1: ETL process analysis: Analyze the processes of data extraction (Extract), transformation (Transform), and loading (Load) to ensure that each step of ETL meets business requirements;

[0071] S2.2: Template configuration: Use the WPS template to configure data fields to ensure that developers can conveniently configure the mapping relationship between data source fields and target fields through the template;

[0072] S3: WPS Template Configuration

[0073] S3.1: WPS Template Structure Design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationship of the data source. The template contains field information for each data table, conversion logic (such as data type conversion, field concatenation, calculation, etc.), conditional filtering, and other information;

[0074] S3.2: Dynamic Template: Design a dynamic template engine to generate different templates according to business requirements. These templates will automatically generate adapted ETL scripts, providing a basis for subsequent development;

[0075] S4: Template Field Configuration

[0076] S4.1: Field Mapping Configuration: Through the configuration page provided in the WPS template, users can select different data source fields to map with target fields. This mapping process no longer requires writing cumbersome SQL, but is completed through a graphical interface;

[0077] S4.2: Calculated Field Configuration: In the WPS template, developers can configure some fields that need to be calculated or converted through formulas or functions, such as calculating the sum, average, etc. of a certain metric based on existing fields;

[0078] S5: Conversion Logic Configuration

[0079] S5.1: SQL Script Embedding: In the template, SQL scripts can be embedded through specific tags, and users configure the conversion logic through a graphical interface. Complex data conversion operations can be directly written in SQL scripts and associated with the fields configured in the template;

[0080] S5.2: Dynamic Script Generation: According to the configuration in the template, the system will dynamically generate SQL scripts, including functions such as field conversion, data filtering, and conditional judgment.

[0081] S6: System Engine and Script Orchestration

[0082] S6.1: WPS Template Parsing: The system has a built-in WPS template engine that can parse the configuration in the WPS template. Contents such as field mapping relationships, conversion rules, and SQL script embedding in the template are automatically converted into executable ETL scripts through the template engine;

[0083] S6.2: Dynamic Script Generation: According to different business requirements, the system automatically generates different ETL scripts, supporting complex operations between multiple SQL scripts (such as SELECT, INSERT, UPDATE, DELETE, etc.) and data sources.

[0084] S7: Automatic Orchestration of SQL Scripts

[0085] S7.1: Script Execution Scheduling: Through the script scheduling engine, ETL scripts can be executed regularly according to requirements. The scheduling engine can manage the execution order, dependencies, and execution time of scripts;

[0086] S7.2: Automatic Processing: The system will automatically generate and execute SQL scripts according to preset rules to complete data extraction, transformation, and loading operations. Business personnel do not need to write SQL and only need to configure through WPS templates.

[0087] S8: Real-time Data Monitoring and Tuning

[0088] S8.1: Real-time Data Monitoring Panel: The system provides a real-time data monitoring panel to monitor the execution status of each ETL task. The monitoring content includes execution status, execution time, success and failure records, etc.;

[0089] S8.2: Exception Alarm Mechanism: The system can send alarm messages to relevant personnel in a timely manner when errors occur during data processing to ensure that data development tasks are not affected;

[0090] S8.3: Automatic Detection: During the data development process, the system can automatically perform data quality detection to check whether the data meets the predetermined rules (such as field types, value ranges, field integrity, etc.);

[0091] S8.4: Real-time Data Verification: When loading data, the system verifies the correctness and consistency of the data in real time to ensure that no errors occur in data processing.

[0092] S9: Script Optimization and Performance Tuning

[0093] S9.1: SQL Optimization: The system automatically identifies SQL scripts that may have performance issues and optimizes them (for example, index optimization, join condition optimization, subquery optimization, etc.);

[0094] S9.2: Parallel Execution: Supports multi-threaded or distributed computing methods to improve the execution efficiency of ETL scripts. Especially in a big data environment, it can significantly improve the data processing speed.

[0095] S10: System Expansion and Integration

[0096] S10.1: Data Source Adapter: The system provides a rich set of data source adapters to support seamless connection with different types of data sources (such as MySQL, PostgreSQL, Oracle, Hadoop, Spark, etc.). Through these adapters, users can easily migrate data between different data platforms;

[0097] S10.2: API Interface: Provide RESTful API interfaces that allow third-party systems or applications to call the data development system for data processing or querying, and support integration with external data platforms;

[0098] S10.3: Custom Script Module: Developers can write custom SQL scripts or Python scripts according to business requirements for complex ETL logic processing;

[0099] S10.4: Plugin Mechanism: The system supports plugin-based expansion and can quickly integrate new data sources, transformation rules, or functional modules according to user needs.

[0100] S11: Deployment and Operation and Maintenance

[0101] S11.1: Script Automatic Deployment: The system can automatically deploy the generated SQL scripts to the target environment and regularly update or adjust the scripts to adapt to new business requirements;

[0102] S11.2: Containerized Deployment: Package the system into a container and support deployment on containerized platforms such as Docker to improve the scalability and operation and maintenance convenience of the system;

[0103] S11.3: Permission Management: The system provides a fine-grained permission management mechanism to ensure that users of different roles can only access the data and functions to which they have permissions;

[0104] S11.4: System Log and Audit: Record all operation logs of the system to facilitate problem tracking, auditing, and maintenance by operation and maintenance personnel.

[0105] S12: WPS Template Configuration

[0106] S12.1: WPS Template Structure Design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationships of the data source. The template contains field information, transformation logic (such as data type conversion, field concatenation, calculation, etc.), conditional filtering, and other information for each data table;

[0107] S12.2: Dynamic Template: Design a dynamic template engine to generate different templates according to business requirements. These templates will automatically generate adapted ETL scripts to provide a basis for subsequent development;

[0108] S12.3: Field Mapping Configuration: Through the configuration page provided in the WPS template, users can select different data source fields to map with target fields. This mapping process no longer requires writing cumbersome SQL, but is completed through a graphical interface. Calculated Field Configuration: In the WPS template, developers can configure some fields that need to be calculated or transformed through formulas or functions, such as calculating the sum, average, etc. of a certain metric based on existing fields. Transformation Logic Configuration;

[0109] S12.4: SQL Script Embedding: In the template, SQL scripts can be embedded through specific tags, and users configure the transformation logic through a graphical interface. SQL scripts can be directly written to perform complex data transformation operations and at the same time associate them with the fields configured in the template;

[0110] S12.5: Dynamic Script Generation: According to the configuration in the template, the system will dynamically generate SQL scripts, including functions such as field transformation, data filtering, and conditional judgment.

[0111] Specifically, before communicating with the business department in step S1, prepare a basic requirements analysis document covering the current system status, known problems, and possible solutions. Through one-on-one interviews, focus group discussions, etc., understand the needs of the business department. Leading questions can help uncover hidden requirements. For example: "What are the most commonly used metrics in your data analysis?" and "What additional insights do you hope the system can provide?" etc. Organize the requirements through documents or online tools (such as Jira, Confluence) and confirm with the business department to ensure that the requirements are clear and definite. For complex requirements, use prototyping or data flow diagrams to help clarify. Based on the requirements analysis, identify the main data sources involved, which may include CRM systems, ERP systems, third-party data providers, etc. Design the data table structure, clarify the fields, data types, and their relationships of each data table. For example, order tables, user tables, product tables, etc. Show the relationships between data tables through ER diagrams (Entity Relationship Diagrams) or UML class diagrams. Design foreign key constraints, indexes, etc. to ensure data consistency and integrity.

[0112] Specifically, in step S2, confirm from which data sources to extract data, formulate a data extraction plan (full extraction or incremental extraction), select a suitable extraction tool or SQL query script, and perform processing such as cleaning, merging, deduplication, and standardization on the extracted data. This step usually requires writing transformation rules, which may include data type conversion, data cleaning (such as removing null values or outliers), etc. Determine the loading method of the target database or data warehouse, whether it is full loading or incremental loading. Design a suitable loading strategy according to the data volume and the performance requirements of the target table (such as batch insertion, batch-by-batch loading, etc.).

[0113] Specifically, in step S2, use a WPS template (or similar tool) to design the template format and determine the field mapping rules. The WPS template should include the mapping relationship between the source table and the target table, data cleaning rules, conversion logic, etc. Develop a graphical interface for users to select the mapping relationship between the data source field and the target field, ensuring that the field name, data type, and conversion rules can be directly configured through the interface.

[0114] Specifically, in step S3, design the overall framework of the template, including information such as field name, data type, conversion rule, target field, etc. List the specific definitions of the source table, target table, and fields in detail in the template. For example: field name, type, optionality (required / optional), default value, etc. Define the way of conversion rules clearly, including field type conversion, calculation rules, data cleaning logic, etc. Design a dynamic template engine that can automatically generate different templates according to business requirements. This can be achieved through a template configuration file combined with a rule engine. The template engine automatically generates the corresponding ETL script according to the business requirements input by the user, supporting customized generation of different scripts (such as data extraction scripts, conversion scripts, etc.).

[0115] Specifically, in step S4, develop a graphical interface that allows users to complete the mapping between the source field and the target field by dragging or selecting. This tool should support various field types (such as numbers, text, dates, etc.), provide a real-time preview function, enabling users to see the actual effect after field mapping, ensuring that the configuration is correct, and allowing developers to design calculated fields in the WPS template through formulas or functions. For example, calculate the sum, average value, growth rate, etc. of fields in a data table, and support users to customize functions or SQL scripts for complex calculation or conversion operations.

[0116] Specifically, in step S5, embed SQL scripts in the template, through specific tags (such as <sql>)Define custom SQL statements in the template. In this way, users can configure SQL logic through a graphical interface, such as data filtering, transformation, etc. Provide an SQL script template, and users can quickly generate or modify SQL scripts according to business requirements. According to the field mapping and transformation rules in the template, the system automatically generates SQL scripts. For example, according to the mapping relationship, SQL statements such as INSERT and UPDATE are automatically generated, and the generated SQL scripts are automatically merged to avoid redundant operations and optimized to improve execution efficiency.

[0117] Specifically, in step S6, design a WPS template parsing engine that can read contents such as field mapping, transformation rules, and SQL scripts in the WPS template, and convert these configurations into specific ETL execution steps. The engine should support dynamically generating adapted ETL scripts according to different requirements, automatically adjusting contents such as fields and table structures. According to changes in business requirements, the system should be able to dynamically generate different types of ETL scripts, support complex operations between different data sources and data tables. When generating scripts, the system automatically optimizes SQL statements to ensure execution efficiency.

[0118] Specifically, in step S7, develop a scheduling system that supports regular execution of ETL scripts. Users can set the execution period (such as daily, weekly, etc.) and priority. The scheduling engine will automatically execute tasks, manage the dependency relationships between scripts, and ensure the sequence in the data extraction, transformation, and loading processes. The system can automatically generate and execute ETL scripts according to preset rules. Through user-defined templates, the system automatically obtains data, executes transformations, and loads data, reducing manual intervention.

[0119] Specifically, in step S8, develop a real-time monitoring panel to display the execution status of each ETL task, including execution status (success / failure), execution time, task logs, etc. Intuitively display the execution results of tasks through charts, pie charts, etc., to help business personnel quickly identify problems. Set thresholds, and when data processing errors occur (such as data quality problems, task execution failures, etc.), the system will automatically trigger alarm notifications. Design automation rules to regularly or real-time detect data quality, such as field types, value ranges, data integrity, etc., and generate data quality reports for data developers to further check and repair. During the data loading process, the system performs real-time verification to ensure that the data conforms to the expected format, range, and business rules. If the data verification fails, the system will automatically repair or send an alarm.

[0120] Through the above technical solution, in the present invention, by combining WPS template configuration and SQL script orchestration, an efficient and flexible solution is provided. Users complete data development through a graphical configuration interface, reducing the dependence on programming capabilities and lowering the threshold of data development. At the same time, through the template engine and script orchestration technology, the system can automatically generate complex ETL scripts according to the configuration, optimize the development process, shorten the development cycle, and improve the development efficiency. Coupled with the support of functions such as monitoring, optimization, and extension, it can better meet the rapidly changing business needs and provide a stable and reliable operating environment. Although the embodiments of the present invention have been shown and described, for those of ordinary skill in the art, it can be understood that various changes, modifications, substitutions, and variations can be made to these embodiments without departing from the principles and spirit of the present invention, and the scope of the present invention is defined by the appended claims and their equivalents.< / sql> < / sql>

Claims

1. A data development system and method based on WPS template configuration, characterized in that: The following steps are involved: S1: Requirements Analysis and Data Modeling S1.1: Communicate with the business department: First, you need to communicate with the business department to understand the changes in business needs. For complex needs, you can use guiding questions to determine the most critical output indicators and data processing procedures; S1.2: Data model design: Design the data model based on the requirements analysis. Determine the data sources, data tables, data fields, and relationships involved in the business requirements (for example, how the tables in the data source are related through foreign keys and the dependencies between fields); S2: Data source sorting and ETL process design S2.1: ETL process analysis: Analyze the data extraction (Extract), transformation (Transform) and loading (Load) processes to ensure that each step of ETL meets business requirements; S2.2: Template configuration: Use WPS templates to configure data fields, ensuring that developers can easily configure the mapping relationship between data source fields and target fields through templates; S3: WPS template configuration S3.1: WPS template structure design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationship of the data source. The template contains the field information of each data table, conversion logic (such as data type conversion, field splicing, calculation, etc.), conditional filtering and other information; S3.2: Dynamic templates: Design a dynamic template engine to generate different templates according to business needs. These templates will automatically generate adaptive ETL scripts to provide a basis for subsequent development; S4: Template field configuration S4.1: Field mapping configuration: Through the configuration page provided in the WPS template, users can select different data source fields and target fields for mapping. This mapping process no longer requires writing tedious SQL, but is completed through a graphical interface; S4.2: Calculation field configuration: In the WPS template, developers can configure some fields that need to be calculated or converted through formulas or functions, such as calculating the sum or average of a certain indicator based on existing fields; S5: Conversion logic configuration S5.1: SQL script embedding: In the template, SQL scripts can be embedded through specific tags, and users can configure the transformation logic through a graphical interface. SQL scripts can be directly written to perform complex data transformation operations and associated with the fields configured in the template; S5.2: Dynamic script generation: Based on the configuration in the template, the system will dynamically generate SQL scripts, including field conversion, data filtering, conditional judgment and other functions. S6: System Engine and Scripting S6.1: WPS template parsing: The system has a built-in WPS template engine that can parse the configuration in the WPS template. The field mapping relationship, conversion rules, SQL script embedding and other contents in the template are automatically converted into executable ETL scripts through the template engine; S6.2: Dynamic script generation: According to different business needs, the system automatically generates different ETL scripts, supporting complex operations between multiple SQL scripts (such as SELECT, INSERT, UPDATE, DELETE, etc.) and data sources. S7: SQL script automatic arrangement S7.1: Script execution scheduling: The script scheduling engine can be used to regularly execute ETL scripts based on demand. The scheduling engine can manage the execution order, dependencies, and execution time of scripts; S7.2: Automated processing: The system will automatically generate and execute SQL scripts according to preset rules to complete data extraction, conversion and loading operations. Business personnel do not need to write SQL, but only need to configure through WPS templates. S8: Real-time data monitoring and tuning S8.1: Real-time data monitoring panel: The system provides a real-time data monitoring panel to monitor the execution of each ETL task. The monitoring content includes execution status, execution time, success and failure records, etc. S8.2: Abnormal alarm mechanism: When an error occurs during data processing, the system can send alarm information to relevant personnel in a timely manner to ensure that data development tasks are not affected; S8.3: Automated testing: During the data development process, the system can automatically perform data quality testing to check whether the data conforms to the predetermined rules (such as field type, value range, field integrity, etc.); S8.4: Real-time data verification: When loading data, the system verifies the correctness and consistency of the data in real time to ensure that there are no errors in data processing. S9: Script optimization and performance tuning S9.1: SQL optimization: The system automatically identifies SQL scripts that may have performance issues and optimizes them (for example, index optimization, join condition optimization, subquery optimization, etc.); S9.2: Parallel execution: Supports multi-threaded or distributed computing methods to improve the execution efficiency of ETL scripts, especially in a big data environment, which can significantly improve data processing speed. S10: System expansion and integration S10.1: Data source adapter: The system provides a variety of data source adapters to support seamless connection with different types of data sources (such as MySQL, PostgreSQL, Oracle, Hadoop, Spark, etc.). Through these adapters, users can easily migrate data between different data platforms; S10.2: API interface: Provide RESTful API interface, allowing third-party systems or applications to call the data development system for data processing or query, and support integration with external data platforms; S10.3: Custom script module: Developers can write custom SQL scripts or Python scripts based on business needs to perform complex ETL logic processing; S10.4: Plug-in mechanism: The system supports plug-in extensions and can quickly integrate new data sources, conversion rules or functional modules according to user needs. S11: Deployment and Operation S11.1: Automatic script deployment: The system can automatically deploy the generated SQL scripts to the target environment and regularly update or adjust the scripts to adapt to new business needs; S11.2: Containerized deployment: Package the system into containers and support deployment on containerized platforms such as Docker to improve system scalability and operation and maintenance convenience; S11.3: Permission management: The system provides a sophisticated permission management mechanism to ensure that users of different roles can only access data and functions for which they have permission; S11.4: System logs and audits: Record all system operation logs to facilitate problem tracking, auditing and maintenance by operation and maintenance personnel. S12: WPS template configuration S12.1: WPS template structure design: Design a WPS template file that meets the requirements. The template file is used to configure the table structure and field mapping relationship of the data source. The template contains the field information of each data table, conversion logic (such as data type conversion, field splicing, calculation, etc.), conditional filtering and other information; S12.2: Dynamic templates: Design a dynamic template engine to generate different templates according to business needs. These templates will automatically generate adaptive ETL scripts to provide a basis for subsequent development; S12.3: Field mapping configuration: Through the configuration page provided in the WPS template, users can select different data source fields and target fields for mapping. This mapping process no longer requires writing tedious SQL, but is completed through a graphical interface. Calculation field configuration: In the WPS template, developers can configure some fields that need to be calculated or converted through formulas or functions, such as calculating the sum or average of a certain indicator based on existing fields. Conversion logic configuration; S12.4: SQL script embedding: In the template, SQL scripts can be embedded through specific tags, and users can configure the transformation logic through a graphical interface. SQL scripts can be directly written to perform complex data transformation operations and associated with the fields configured in the template; S12.5: Dynamic script generation: Based on the configuration in the template, the system will dynamically generate SQL scripts, including field conversion, data filtering, conditional judgment and other functions.

2. According to claim 1, a data development system and method based on WPS template configuration, characterized in that: In step S1, before communicating with the business department, prepare a basic demand analysis document covering the current system status, known problems and possible solutions, and understand the needs of the business department through one-on-one interviews, focus group discussions, etc. Guiding questions can help uncover hidden needs. For example: "What are the most commonly used indicators in data analysis?", "What additional insights do you hope the system can provide?", etc., organize the requirements through documents or online tools (such as Jira, Confluence), and confirm with the business department to ensure that the requirements are clear. For complex requirements, prototype design or data flow diagrams are used to help clarify. Based on the demand analysis, identify the main data sources involved, which may include CRM systems, ERP systems, third-party data providers, etc., design the data table structure, and clarify the fields, data types and their relationships of each data table. For example, order tables, user tables, product tables, etc., use ER diagrams (entity relationship diagrams) or UML class diagrams to show the relationship between data tables. Design foreign key constraints, indexes, etc. to ensure data consistency and integrity.

3. According to claim 1, a data development system and method based on WPS template configuration, characterized in that: In step S2, it is determined which data sources are to be used for data extraction, a data extraction plan is developed (full extraction or incremental extraction), an appropriate extraction tool or SQL query script is selected, and the extracted data is cleaned, merged, deduplicated, and standardized. This step usually requires writing conversion rules, which may include data type conversion, data cleaning (such as removing null values ​​or outliers), etc., and determining the loading method of the target database or data warehouse, whether it is full loading or incremental loading. Design an appropriate loading strategy (such as batch insert, batch loading, etc.) based on the data volume and performance requirements of the target table.

4. According to claim 1, a data development system and method based on WPS template configuration, characterized in that: In step S2, a WPS template (or similar tool) is used to design a template format and determine field mapping rules. The WPS template should include the mapping relationship between the source table and the target table, data cleaning rules, conversion logic, etc. A graphical interface is developed for users to select the mapping relationship between the data source field and the target field, ensuring that the field name, data type, and conversion rule can be directly configured through the interface.

5. According to claim 1, a data development system and method based on WPS template configuration is characterized in that: The overall framework of the template designed in step S3 includes information such as field name, data type, conversion rules, target field, etc. The specific definitions of the source table, target table, and field are listed in detail in the template. For example: field name, type, optionality (required / optional), default value, etc., clarify the definition method of conversion rules, including field type conversion, calculation rules, data cleaning logic, etc., and design a dynamic template engine that can automatically generate different templates according to business needs. This can be done by combining the template configuration file with the rule engine. The template engine automatically generates the corresponding ETL script according to the business needs entered by the user, and supports customized generation of different scripts (such as data extraction scripts, conversion scripts, etc.).

6. A data development system and method based on WPS template configuration according to claim 1, characterized in that: In step S4, a graphical interface is developed to allow users to complete the mapping of source fields and target fields by dragging or selecting. This tool should support various field types (such as numbers, text, dates, etc.), provide a real-time preview function, so that users can see the actual effect after field mapping, ensure that the configuration is correct, and allow developers to design calculated fields through formulas or functions in the WPS template. For example, calculate the sum, average, growth rate, etc. of fields in a data table, and support user-defined functions or SQL scripts to perform complex calculations or conversion operations.

7. A data development system and method based on WPS template configuration according to claim 1, characterized in that: In step S5, the SQL script is embedded in the template through a specific tag (such as <sql> ) Define custom SQL statements in the template. In this way, users can configure SQL logic, such as data filtering, conversion, etc. through a graphical interface. SQL script templates are provided, and users can quickly generate or modify SQL scripts according to business needs. According to the field mapping and conversion rules in the template, the system automatically generates SQL scripts. For example, based on the mapping relationship, SQL statements such as INSERT and UPDATE are automatically generated, and the generated SQL scripts are automatically merged to avoid redundant operations and optimized to improve execution efficiency.< / sql> 8. A data development system and method based on WPS template configuration according to claim 1, characterized in that: In step S6, a WPS template parsing engine is designed, which can read the field mapping, conversion rules, SQL scripts and other contents in the WPS template, and convert these configurations into specific ETL execution steps. The engine should support dynamic generation of adaptive ETL scripts according to different needs, and automatically adjust fields, table structures and other contents. According to changes in business needs, the system should be able to dynamically generate different types of ETL scripts and support complex operations between different data sources and data tables. When generating scripts, the system automatically optimizes SQL statements to ensure execution efficiency.

9. A data development system and method based on WPS template configuration according to claim 1, characterized in that: In step S7, a scheduling system is developed to support the regular execution of ETL scripts. Users can set the execution cycle (such as daily, weekly, etc.) and priority. The scheduling engine will automatically execute tasks, manage the dependencies between scripts, and ensure the order of data extraction, conversion, and loading. The system can automatically generate and execute ETL scripts according to preset rules. Through user-defined templates, the system automatically obtains data, performs conversions, and loads data, reducing manual intervention.

10. A data development system and method based on WPS template configuration according to claim 1, characterized in that: In the step S8, a real-time monitoring panel is developed to display the execution status of each ETL task, including execution status (success / failure), execution time, task log and other information. The execution results of the task are intuitively displayed through charts, pie charts and other methods to help business personnel quickly identify problems and set thresholds. When errors occur in data processing (such as data quality problems, task execution failure, etc.), the system will automatically trigger alarm notifications, design automation rules, regularly or in real time detect data quality, such as field type, value range, data integrity, etc., and generate data quality reports for data developers to further check and repair. During the data loading process, the system performs real-time verification to ensure that the data conforms to the expected format, range and business rules. If the data verification fails, the system will automatically repair it or send an alarm.