Data development environment management method and system

Abstract

The invention relates to a data development environment management method and system. Configuration information of SIT, UAT and PROD three-state environments is managed in a centralized mode through the unified configuration center module. The method comprises the following steps: analyzing an SQL script submitted by a developer through an automatic assembly line engine module, decomposing the SQL script into structured components, storing the structured components in a configuration table, reading information from the configuration table, and reversely splicing the information into an executable SQL; resource isolation and security control of the three-state environment are realized on the unified cluster through the dynamic isolation sandbox module, and the test environment is authorized to read production environment data in real time; and an offset detection and interception module is used for automatically detecting and intercepting environmental deviations at the process key points based on predefined SQL rules. According to the method, consistency management, one-button automatic deployment, strict security isolation and active risk prevention and control of the data development environment are realized, and the problems of inconsistency of multiple environments, low management efficiency, high release risk and the like are effectively solved.

Description

Technical Field

[0001] This invention relates to the field of big data technology and software development and operations (DevOps), specifically, to a method and system for achieving consistency management, automated deployment, security isolation, and risk interception of data development tasks in multiple environments (SIT, UAT, PROD). Background Technology

[0002] With the accelerating penetration of the global digital economy, digital transformation has become a core engine for enterprises to restructure their competitiveness and achieve high-quality development. In the financial industry, typical data development environments typically include three types: System Integration Testing (SIT), User Acceptance Testing (UAT), and Production (PROD). However, in actual operation, only the production environment often receives effective and systematic maintenance and management, while SIT and UAT environments generally lack standardized and continuous management support. This situation leads to significant differences among the three types of environments in multiple dimensions, including software versions, system configurations, program code, hardware infrastructure, account permissions, and data quality, thereby triggering various problems and potential risks throughout the data development process. Specifically, the main problems and risks include:

[0003] 1. Inconsistent environments can lead to operational anomalies. Due to inconsistencies between the SIT or UAT environment and the PROD environment in terms of software version, system configuration, etc., code that passes verification in the test environment may experience functional abnormalities or compatibility errors after deployment in the production environment, potentially triggering emergency rollbacks or even business interruptions.

[0004] 2. Insufficient representativeness of test data. The data used in the SIT or UAT environment is often outdated or has not been properly anonymized, failing to cover the real business scenarios of the PROD environment. This results in some logical errors not being discovered during the testing phase, ultimately causing production incidents after deployment.

[0005] 3. Database structure inconsistency. Differences exist between the database table structure in the test environment and the production environment. For example, missing new fields or inconsistent field types can cause batch processing scripts to fail to execute or generate incorrect data in the production environment.

[0006] 4. Mismatch between data scale and performance pressure. SIT or UAT environments often use small-scale sample data, which cannot effectively simulate the data processing pressure of the production environment. This leads to problems such as timeouts and memory overflows after the job goes live, which in turn causes a chain of task blocking.

[0007] 5. Complex and error-prone configuration management. SIT, UAT, and production environments require independent maintenance of various configuration items. Relying on manual management can easily lead to configuration errors, such as misusing test configurations in the production environment, which can seriously affect system stability.

[0008] 6. Lack of security control measures. Test environments often have lax access control and do not strictly implement production-level security policies. This can lead to data loss due to developer errors or allow malicious attackers to exploit vulnerabilities for penetration.

[0009] 7. Low collaboration efficiency. Development, testing, and operations personnel communicate frequently due to the differences between the three environments. The problem localization process is time-consuming and labor-intensive, significantly increasing team collaboration costs, which can easily lead to project delays and waste resources on unnecessary troubleshooting.

[0010] 8. Deployment process relies on manual processes and is prone to errors. Script deployment in production environments typically relies on manual packaging and deployment, which can easily lead to problems such as version packaging errors or missing scripts.

[0011] Furthermore, even among some leading financial institutions that have implemented a certain level of management for the SIT and UAT environments, their maintenance work still mainly relies on manual methods, resulting in significant bottlenecks such as high management costs, low operational efficiency, and difficulty in ensuring consistency and reliability. Summary of the Invention

[0012] The present invention aims to overcome the above-mentioned defects of the prior art and provide a method and system for data development environment management, so as to achieve unified management of environment configuration, automation of deployment process, security isolation between environments, and proactive prevention and control of release risks.

[0013] The technical solution for achieving the objective of this invention is as follows:

[0014] This invention proposes a data development environment management method, comprising the following steps:

[0015] Step S1: Receive and store data development task configuration information related to the System Integration Test Environment (SIT), User Acceptance Test Environment (UAT), and Production Environment (PROD) through the unified configuration center module;

[0016] Step S2: The automated pipeline engine module parses the SQL script code submitted by the data developers and stores the parsed structured information in the unified configuration center module.

[0017] Step S3: Using the dynamic isolation sandbox module, on a unified big data platform infrastructure, based on resource isolation technology, create mutually isolated tenants for the SIT environment, UAT environment, and PROD environment, and grant SIT tenants and UAT tenants the permission to read data in the PROD environment.

[0018] Step S4: Before the key nodes of the task flow driven by the automated pipeline engine module, the offset detection and interception module detects the deviation between the environment configuration or data and the expected state based on predefined rules, and intercepts when the deviation is detected.

[0019] Step S5: The automated pipeline engine module reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task, so as to complete the entire process from SIT testing, UAT testing to production deployment.

[0020] Furthermore, the parsing of the SQL script code in step S2 specifically includes:

[0021] The SQL script code is preprocessed and parsed, and the parsed job information, job association information, and field mapping information are stored in the job information table, job association information table, and job field mapping information table, respectively; among them...

[0022] The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code;

[0023] The job association information table stores the association table names and association conditions from the SQL script code;

[0024] The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

[0025] Furthermore, the creation of tenants for the three environments based on resource isolation technology described in step S3 is implemented as follows:

[0026] Using Kubernetes as the container orchestration platform, each tenant is assigned a Kubernetes Namespace, and isolation is achieved at the compute, storage, and network levels by setting resource quotas and network policies;

[0027] The specific implementation of granting SIT tenants and UAT tenants the permission to read PROD environment data in step S3 includes:

[0028] The database names of the SIT, UAT, and PROD environments are standardized and managed. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], which enables SIT tenants and UAT tenants to access the data in the PROD environment in real time as needed during testing.

[0029] Furthermore, the specific process of the offset detection and interception module in step S4 is as follows:

[0030] All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code; the detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification;

[0031] When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table.

[0032] The task-related context information is used as variables, dynamically replaced in the pre-defined detection rule SQL script code, and then executed; the execution result of the detection rule SQL script code is used to determine whether the corresponding detection rule is hit;

[0033] If no deviation is detected by any of the triggered detection rules, the task will be automatically approved to proceed to the next stage; if any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details.

[0034] The key nodes mentioned in step S4 include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

[0035] Furthermore, the process of parsing the SQL script code and storing the decomposed structured information in step S2 is as follows:

[0036] Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these).

[0037] Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships;

[0038] Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table.

[0039] Furthermore, the method for reading configuration information from the unified configuration center module and assembling it into a complete executable SQL script code in step S5 is as follows:

[0040] The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes:

[0041] Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively.

[0042] According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

[0043] This invention also proposes a data development environment management system, comprising:

[0044] The unified configuration center module is used to centrally store and manage the configuration information of the system integration test environment (SIT), user acceptance test environment (UAT), and production environment (PROD).

[0045] The automated pipeline engine module parses the SQL script code submitted by data developers and stores the parsed structured information in the unified configuration center module. It reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task to complete the entire process from SIT testing and UAT testing to production deployment.

[0046] The dynamic isolation sandbox module is used to create mutually isolated tenants for the SIT environment, UAT environment and PROD environment on a unified big data platform infrastructure based on resource isolation technology, and to grant SIT tenants and UAT tenants the permission to read data in the PROD environment.

[0047] The offset detection and interception module detects deviations between the environment configuration or data and the expected state based on predefined rules before the key nodes of the task flow driven by the automated pipeline engine module, and intercepts the deviation when it is detected.

[0048] Furthermore, the unified configuration center module stores and manages SQL script code in a structured manner by maintaining a job information table, a job association information table, and a job field mapping information table;

[0049] The automated pipeline engine module preprocesses and parses the SQL script code, and stores the parsed job information, job association information, and field mapping information in the job information table, job association information table, and job field mapping information table, respectively; wherein...

[0050] The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code;

[0051] The job association information table stores the association table names and association conditions from the SQL script code;

[0052] The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

[0053] Furthermore, the dynamic isolation sandbox module uses Kubernetes as a container orchestration platform, assigning each tenant a Kubernetes Namespace, and achieving isolation at the computing, storage, and network levels by setting resource quotas and network policies;

[0054] Granting SIT and UAT tenants access to read PROD environment data includes: standardizing the database names of the SIT, UAT, and PROD environments. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], enabling SIT and UAT tenants to access PROD environment data in real time as needed during testing.

[0055] Furthermore, the offset detection and interception module has a built-in offset detection rule table. All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code. The detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification.

[0056] When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table.

[0057] The task-related context information is used as variables, dynamically replaced in the pre-defined detection rule SQL script code, and then executed; the execution result of the detection rule SQL script code is used to determine whether the corresponding detection rule is hit;

[0058] If no deviation is detected by any of the triggered detection rules, the task will be automatically approved to proceed to the next stage; if any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details.

[0059] The key nodes include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

[0060] Furthermore, the automated production line engine module is further configured as follows:

[0061] The process of parsing the SQL script code and storing the decomposed structured information is as follows:

[0062] Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these).

[0063] Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships;

[0064] Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table.

[0065] The configuration information is read from the unified configuration center module and assembled into a complete executable SQL script. The specific process is as follows:

[0066] The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes:

[0067] Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively.

[0068] According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

[0069] Compared with the prior art, the significant advantages and beneficial effects of this invention are as follows:

[0070] 1. Improved consistency: Through a unified configuration center, a high degree of consistency in infrastructure, code, and configuration is ensured across all environments from development to production, eliminating deployment failures caused by environmental differences.

[0071] 2. Increased efficiency: The automated pipeline enables one-click operation of the entire process from code submission to testing and production deployment, greatly reducing manual intervention and improving release efficiency.

[0072] 3. Enhanced security: By using dynamic isolation sandbox technology, strict security isolation and fine-grained access control are achieved on the shared cluster, effectively reducing the risk of data leakage.

[0073] 4. Controllable risks: By using an offset detection mechanism to automatically intercept at key points in the process, potential risks can be detected and blocked early, ensuring the reliability of production releases.

[0074] 5. Cost reduction: It solved the problem of repeated communication and investigation caused by environmental issues, reduced team collaboration costs, and improved resource utilization.

[0075] Other features and advantages of the invention will be set forth in the following description, and will be apparent in part from the description, or may be learned by practicing the invention. The objects and other advantages of the invention may be realized and obtained by means of the structures particularly pointed out in the written description, claims, and drawings. Attached Figure Description

[0076] The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Throughout the drawings, the same reference numerals denote the same parts.

[0077] Figure 1 This is a schematic diagram of the data development environment management system of the present invention.

[0078] Figure 2 This is a schematic diagram of how the dynamic isolation sandbox module of this invention creates mutually isolated tenants for the SIT environment, UAT environment and PROD environment based on resource isolation technology on a unified big data platform infrastructure.

[0079] Figure 3 This is a schematic diagram of a data development task implementation process based on the present invention. Detailed Implementation

[0080] It is readily understood that, based on the technical solution of this invention, various embodiments of the invention can be conceived by those skilled in the art without altering the essential spirit of the invention. Therefore, the following detailed embodiments and accompanying drawings are merely illustrative examples of the technical solution of this invention and should not be considered as the entirety of the invention or as limitations or restrictions on the technical solution of this invention. Rather, these embodiments are provided to enable those skilled in the art to gain a more thorough understanding of the invention. Preferred embodiments of the invention are described below in conjunction with the accompanying drawings, which form part of this application and, together with the embodiments of the invention, serve to illustrate the innovative concept of the invention.

[0081] like Figure 1 As shown, the data development environment management system of this invention consists of four core modules: a unified configuration center module, an automated pipeline engine module, a dynamic isolation sandbox module, and an offset detection and interception module. The functions and implementation mechanisms of each module are described in detail below, and the complete operation process of the system is illustrated using a data development task workflow as an example.

[0082] Unified Configuration Center Module:

[0083] The Unified Configuration Center module is the core hub of the data development environment management system. It is responsible for centrally storing and managing all code and configuration information related to data development tasks in the system integration test environment (SIT), user acceptance test environment (UAT), and production environment (PROD), and is responsible for the following specific tasks:

[0084] Unified configuration item management: A unified configuration management approach is adopted to achieve systematic control over code versions and ensure consistency between code and versions.

[0085] Differentiated environment configuration: Set corresponding environment configuration values ​​for different environments (SIT, UAT and PROD). For example, the SIT environment uses the SIT database and the UAT environment uses the UAT database. While meeting the specific needs of each environment, ensure that it is consistent with the underlying infrastructure.

[0086] Version control and auditing: Version control is implemented for all configuration item changes, and detailed records are kept of the operators, timestamps and reasons for each modification, providing complete audit trail capabilities for easy post-event query and analysis.

[0087] Configuration distribution: Automatically and securely distribute approved configuration information to the corresponding target environments, including SIT, UAT, and PROD.

[0088] Automated production line engine module:

[0089] The automated pipeline engine module automates the entire online process from SQL code submission to SIT and UAT testing, and is responsible for the following specific tasks:

[0090] Standardized build and deployment: By building SQL scripts, standardized operation steps such as SQL code parsing, compilation, packaging, and data execution are automatically executed.

[0091] Test integration: It can automatically trigger a series of test activities such as SIT test and UAT test.

[0092] Approval process integration: Integrate manual approval or automated approval mechanisms based on predefined policies at key process stages (such as moving from the SIT environment to the UAT environment, or releasing from the UAT environment to the production environment).

[0093] Dynamic isolation sandbox module:

[0094] The dynamic isolation sandbox module ensures strict physical or logical isolation between the SIT, UAT, and PROD environments, preventing mutual interference and data leakage. It is a core guarantee of system security and is responsible for the following specific tasks:

[0095] Automatic resource partitioning and isolation: Based on predefined strategies, automatically create, allocate, and isolate the necessary computing and storage resources for each environment.

[0096] Role-based access control (RBAC): Strictly define the specific operation permissions (including read, write, execute, deploy, configuration modification, etc.) of different roles (such as developers, testers, operations personnel, and auditors) in different environments, and follow the principle of least privilege to ensure that users only have the minimum permissions necessary to complete their work.

[0097] Database and table lifecycle management: Enables automatic control over the entire lifecycle of databases and tables, including creation, opening, recycling, and destruction.

[0098] Offset detection and interception module:

[0099] The offset detection and interception module is responsible for promptly detecting deviations between configuration or data (such as differences in software version, system configuration, program code, data quality, etc.) and the expected state (baseline or standard), and proactively intercepting them at key process nodes (such as before SIT testing and before production deployment). It is responsible for the following specific tasks:

[0100] Offset detection: By writing SQL rules, the differences between the SIT environment, UAT environment and baseline environment (usually the production environment) in terms of configuration, infrastructure settings, code version and other aspects are systematically compared.

[0101] Continuous monitoring: Regularly scan and compare the differences between each environment and the baseline environment.

[0102] Proactive Interception: During the execution of the automated pipeline, especially before deployment to the SIT and production environments, once an offset is detected, the system will automatically pause the development and testing process and trigger an alarm mechanism.

[0103] The following sections will describe in detail several important mechanisms in this invention.

[0104] First, the dynamic isolation sandbox module provides a mechanism for creating, managing, and accessing data in real time for three types of environments: SIT, UAT, and PROD.

[0105] The dynamic isolation sandbox module is based on a unified big data platform infrastructure (i.e., a physical cluster). By creating SIT tenants, UAT tenants, and PROD tenants, and managing the basic attributes of each tenant (such as the resource pool to which they belong, the default storage location, resource quota limits, access permissions, etc.), it achieves the logical division of three independent environments within the same cluster through resource isolation technology.

[0106] One implementation method of logically dividing three independent environments within the same cluster using resource isolation technology in this invention is as follows:

[0107] Kubernetes (K8s) serves as the foundational platform for container orchestration and resource scheduling. Each tenant corresponds to a Kubernetes Namespace, which acts as a logical boundary for resource isolation. Quota limits for resources such as CPU, memory, and storage are set within this Namespace, and default resource requests and limits are defined for each compute unit and application instance. Inbound and outbound rules for compute units within each tenant's Namespace are defined through Network Policy. The Kubernetes scheduler allocates compute units to nodes that meet resource requirements and quota limits. Through this mechanism, isolation is achieved at the compute, storage, and network levels. Simultaneously, strict tenant access control ensures that users can only access data they are authorized to access; even if data is physically stored within the same cluster, unauthorized access across tenants is impossible.

[0108] Since the SIT, UAT, and PROD environments are based on the same cluster and implemented through isolation technology, their basic environment configurations (such as middleware version, Python version, environment variables, etc.) are naturally consistent, fundamentally solving the problem of environment inconsistency.

[0109] This invention also addresses the issue of outdated data in the SIT or UAT environments, which fails to cover real-world production scenarios and leads to undetected logical errors causing production incidents after deployment. Traditionally, to resolve the data staleness problem in the SIT or UAT environments, production data is fully synchronized to the SIT or UAT environments periodically (e.g., monthly or quarterly). This approach cannot guarantee data real-time performance. If business changes occur in the production data during the synchronization interval, the test environment still uses outdated data, resulting in distorted test results and deviations after deployment. This invention leverages the isolation characteristics within the same cluster, implementing access control for SIT, UAT, and PROD tenants to grant them access to read production environment data. Combined with strict database name management, this allows the testing phase to read the latest production environment data in real-time as needed. The specific implementation method is as follows:

[0110] Permission Granting: Grant read access to PROD tenant data to both SIT and UAT tenants. Leveraging the underlying advantage of the same cluster, production environment data can be accessed in real time whenever testing needs arise.

[0111] Database name standardization management: Strictly standardize database names for different environments. Taking the model layer as an example:

[0112] PROD environment: The library name is fixed as FDM.

[0113] SIT environment: The library name format is FDM_Development Task Number_SIT (the system assigns a unique number to each newly created development task).

[0114] UAT environment: The library name format is FDM_Development Task Number_UAT.

[0115] The following example, using development task FDM_T10_AC_PR_LOAN_LEDG_0001 (task number: 2025081100019) entering the SIT testing phase, illustrates the process of reading the latest data from the production environment in real time as needed during the testing phase:

[0116] (1) When the task enters the SIT test, the dynamic isolation sandbox module dynamically creates a dedicated database FDM_2025081100019_SIT in the SIT environment through the SIT tenant.

[0117] (2) The dynamic isolation sandbox module reads field information from the job field mapping information table (map_mapping_col_info) in the unified configuration center and creates the corresponding table in the new database.

[0118] FDM_T10_AC_PR_LOAN_LEDG.

[0119] (3) The automated pipeline engine concatenates and generates complete SQL code and executes it under the SIT tenant (i.e., SIT environment). Since the SIT tenant has the right to read production data, it can directly use real-time production data to generate SIT test results.

[0120] Second, a unified configuration center module implements a mechanism for managing SQL script code in three environments: SIT, UAT, and PROD.

[0121] Building upon the foundational management of SIT, UAT, and PROD environments, this invention revolutionizes the management of SQL script code for data development. It abandons the traditional "one SQL code, one script" model, adopting a "configurable table" management approach. This model structurally breaks down the SQL code into three parts, each stored in one of three configurable tables:

[0122] The job information table (map_job_info) stores information such as the target table name, ETL algorithm, and WHERE conditions in the SQL query.

[0123] The job association information table (map_table_related) stores table association information in SQL, including the associated table name and association conditions.

[0124] The job field mapping information table (map_mapping_col_info) stores the mapping relationship between the fields in the SELECT part of the SQL and the fields in the INSERT part.

[0125] The key table structure information of the job information table (map_job_info) is as follows:

[0126]

[0127]

[0128] The key table structure information of the job association information table (map_table_related) is as follows:

[0129] field name Data types Notes DATABASE_ENV VARCHAR library name JOB_NAME VARCHAR Assignment Name VERSION DECIMAL Version number SYS_CODE VARCHAR Belonging to the three-state environment IS_LATEST INT Is it the latest version identifier? JOIN_TABLE VARCHAR Related table name TABLE_ALIAS VARCHAR Table aliases of related tables JOIN_SEQUENCE VARCHAR Association order JOIN_TYPE VARCHAR Association type JOIN_CONDITION VARCHAR Association conditions

[0130] The key table structure information for the job field mapping information table (map_mapping_col_info) is as follows:

[0131] field name Data types Notes DATABASE_ENV VARCHAR library name JOB_NAME VARCHAR Assignment Name VERSION DECIMAL Version number SYS_CODE VARCHAR Belonging to the three-state environment IS_LATEST INT Is it the latest version identifier? COL_NO INT Field Number COL_NAME VARCHAR field name COL_COMMENT VARCHAR Field comments COL_MAPPING VARCHAR Field mapping relationship

[0132] The SIT, UAT, and PROD environments all utilize a unified configuration center module for centralized management and maintenance of these three configuration tables. This ultimately achieves unified, efficient, and reliable management of SQL script code across the three environments. As a preferred approach, the SIT and UAT environments share the same set of configuration tables, while the PROD environment uses a separate set. This further enhances environment isolation and ensures standardized security auditing.

[0133] Third, the implementation mechanism of offset detection and interception.

[0134] In a data development workflow, the system automatically triggers offset detection at two key stages: before SIT testing and before production deployment. Offset detection relies on a pre-configured and flexibly definable set of rules, which are stored in the offset detection rule table (offset_detect_rule) shown below. Key fields of this table include:

[0135] field name Data types Notes ENABLE_STAGE VARCHAR Activation phase IS_ENABLE INT Enable or disable? RULE_NO VARCHAR Rule Number RULE_TYPE VARCHAR Rule Type RULE_DEFI VARCHAR Rule definition RULE_COMMENT VARCHAR Rule notes

[0136] In the table,

[0137] ENABLE_STAGE: Indicates at which stage of the task flow this rule is invoked (currently mainly before SIT testing and before production deployment).

[0138] IS_ENABLE (Enabled): Indicates whether this rule is effective, with a value of 0 (not enabled) or 1 (enabled).

[0139] RULE_NO (Rule Number): A unique string used for technical management identification. A prefix of 'COM' indicates a general rule; a prefix with a specific DATABASE_ENV value indicates that the rule applies only to a specific database.

[0140] RULE_TYPE (Rule Type): Divided into "General" (COM) and "Specific". General rules apply to all tasks; specific rules only apply if the library name of the relevant task matches the rule.

[0141] RULE_DEFI (Rule Definition): This core field stores the specific SQL rules used for detection. Since the SQL for development tasks is already broken down and stored, complex detection logic (such as data quality checks, code style verification, etc.) can be flexibly defined by writing SQL queries. Once a rule violation is detected, the system will issue an interception alert and automatically return the task to the development phase.

[0142] For example, a model layer development task FDM_T10_AC_PR_LOAN_LEDG_0001 will be tested according to the predefined detection rules in the offset detection rule table (offset_detect_rule) before production deployment, after UAT testing. Below are some examples of common predefined rules:

[0143] (1) Primary key duplicate detection. The predefined rules are as follows:

[0144] SELECT'TRUE'WHERE(SELECT COUNT(*)FROM

[0145] (SELECT'{PK_COLUMN}',COUNT(*)FROM'{TABLE_NAME}'GROUP BY

[0146] '{PK_COLUMN}'HAVING COUNT(*)>1))>0

[0147] Here, '{TABLE_NAME}' is a variable whose value is the table name information covered in the development task, namely FDM_T10_AC_PR_LOAN_LEDG

[0148] '{PK_COLUMN}' is a variable whose value is passed by the following SQL:

[0149] SELECT PK_COLUMN FROM MAP_JOB_INFO

[0150] WHERE JOB_NAME='{TABLE_NAME}'

[0151] (2) Field fill rate less than 2% is detected. The predefined rules are as follows:

[0152] SELECT 'TRUE' WHERE (SELECT COUNT(*) FROM '{TABLE_NAME}' WHERE '{COLUMN_NAME}' <>”) / (SELECT COUNT(*) FROM '{TABLE_NAME}' < 0.02) where '{TABLE_NAME}' is a variable whose value is the table name information covered in the development task, namely FDM_T10_AC_PR_LOAN_LEDG

[0153] '{COLUMN_NAME}' is a variable whose value is passed by the following SQL:

[0154] SELECT COL_NAME FROM MAP_MAPPING_COL_INFO WHERE JOB_NAME='{TABLE_NAME}'

[0155] (3) SQL statement standardization check, such as checking whether the field processing logic covers window functions (ROW_NUMBER, RANK, DENSE_RANK). The predefined rules are as follows:

[0156] SELECT'TRUE'WHERE(SELECT COUNT(*)FROM MAP_MAPPING_COL_INFO WHERE JOB_NAME='{TABLE_NAME}'AND(COL_MAPPING LIKE'%ROW_NUMBER()%'OR COL_MAPPINGLIKE'%RANK()%'OR COL_MAPPING LIKE'%DENSE_RANK()%'))>0

[0157] If the detection rule is hit, the offset detection and interception module will block it and prevent it from being deployed to production. At this time, the developers need to adjust the SQL according to the specification requirements before submitting the development task.

[0158] Through the offset detection and interception module, this invention can efficiently detect SQL code and table field values, greatly improving the standardization of SQL and the accuracy of data.

[0159] The offset detection and interception mechanism of this invention automatically and accurately identifies deviations between environment configuration, code style, or data quality and the expected baseline at critical nodes in the data development task flow, and proactively interrupts the process to intercept risks, thereby preventing production accidents caused by inconsistencies in the environment, code defects, or data problems. It has the following effects:

[0160] The flexible "configuration as code" rule: The detection logic is implemented as SQL rules, which allows business experts (such as DBAs and data architects) to easily define and extend the detection rules without developing additional functions, greatly improving the system's adaptability and maintainability.

[0161] Seamless integration and proactive prevention: By deeply embedding detection points into automated production lines, passive manual inspections are transformed into proactive, programmed interception, achieving a true "quality access control" and ensuring the rigidity and unbypassability of the process.

[0162] Risk mitigation and efficiency improvement: Potential production environment issues can be identified early in the development phase, preventing problems from escalating later, significantly reducing repair costs, minimizing communication losses caused by environmental issues, and improving overall development efficiency.

[0163] Ensuring release reliability: By conducting final verification before production deployment, an additional solid line of defense is added to the stability of the production environment and the accuracy of the data, which is a key guarantee for achieving safe, reliable and automated release.

[0164] Fourth, the implementation mechanism of SQL parsing and processing in the automated pipeline engine module.

[0165] The automated pipeline engine module plays three core roles in SQL parsing and processing: first, it parses the SQL scripts submitted by developers and decomposes them into structured components; second, it reads information from three configurable tables and assembles them into a complete executable SQL; and third, it connects and drives the entire data development and testing process.

[0166] The process of parsing SQL scripts and decomposing them into structured components as described in this invention is as follows:

[0167] (1) Preprocessing: Standardize the original SQL text, including:

[0168] Remove all comments (--single-line comments or / *multi-line comments* / );

[0169] Remove extra spaces and newline characters to compress the SQL into a single line.

[0170] Unify the case of all keywords (e.g., convert them to uppercase, such as converting select to SELECT and from to FROM);

[0171] Handle special characters that may exist in aliases (such as removing quotes or keeping them as standard identifiers, for example, removing quotes from "table-1" or keeping it as an identifier).

[0172] (2) Syntax parsing: Using Python's sqlparse library, SQL is broken down into recognizable syntactic units. This process includes:

[0173] Lexical analysis: Decomposes the SQL string into the smallest units (tokens), including keywords (such as SELECT, FROM, JOIN, WHERE, INSERT, INTO), identifiers (table names, field names, aliases), operators (=, >, AND, OR), constants (strings, numbers, NULL), etc.

[0174] Syntax analysis: Based on SQL syntax rules, the token sequence is combined into an abstract syntax tree (AST) to identify core clause structures such as the target table (existing in INSERT INTO or CREATE TABLE AS statements), source table and relationships (existing in FROM...JOIN...ON... clauses), field mapping relationships (existing in SELECT...AS... clauses), and filtering conditions (WHERE clauses).

[0175] (3) Information extraction: Extract corresponding information from the tables. Based on the structure of the Abstract Syntax Tree (AST), the system will extract information from the parsed SQL and populate it into the three corresponding configuration tables.

[0176] Extract the information and populate it into the job information table (map_job_info):

[0177] The core information to be extracted mainly includes the target table name, WHERE conditions, GROUP BY operations, HAVING operations, etc.

[0178] Target table name: If the SQL structure is INSERT INTO target table SELECT..., then the target table name (e.g., target_table) is directly extracted from after the INSERT INTO clause; if the structure is CREATE TABLE target table AS SELECT..., then the target table name is extracted from after the CREATE TABLE clause.

[0179] WHERE condition: Extract the complete conditional expression from the WHERE clause (e.g., user.age>18ANDuser.status='active') and store it directly as a string, or parse it into an expression tree structure for later execution.

[0180] The GROUP BY and HAVING operations extract logic similar to the WHERE condition, retrieving and storing the complete expression from the corresponding clause.

[0181] Extract the information and populate it into the job association information table (map_table_related):

[0182] The core information to be extracted includes the source table name, table alias, association method, association conditions, and association order.

[0183] Source table identification: Extract all involved source table names (e.g., t_user, t_order) and their aliases (e.g., T1, T2) from the FROM clause and all JOIN clauses.

[0184] Association method: Identify and record the specific type of JOIN (e.g., INNER JOIN, LEFT JOIN, FULL JOIN, etc.).

[0185] Associated conditions: Extract the associated condition expression from the ON clause (e.g., u.id = o.user_id).

[0186] Join order: Marked according to the order in which JOIN appears in the SQL statement (e.g., marked as the 1st join, the 2nd join, etc.).

[0187] Extract the information and populate it into the job field mapping information table (map_mapping_col_info):

[0188] The core content to be extracted includes the source field name (which may include table aliases), the target field name, and the transformation rules on the field (such as max, min, and other function operations).

[0189] Target field: If the target field is explicitly specified in the SQL statement (e.g., INSERT INTO t_target(id,order_no)SELECT...), it will be extracted from the field list after the INSERT clause; if not explicitly specified, it will correspond one-to-one with the field order after the SELECT clause by default.

[0190] Source fields: Extract all field expressions from the SELECT clause (e.g., u.id, o.order_no), including fields processed by functions (e.g., DATE_FORMAT(o.create_time,'%Y-%m-%d')).

[0191] Mapping relationship: A one-to-one mapping relationship is established between the fields in the SELECT clause and the target fields according to their order of appearance. If the fields are specified with aliases using AS (e.g., u.id AS target_id), then the mapping relationship is: source field = "u.id" → target field = "target_id".

[0192] Conversion rule: If the source field contains a function or expression (e.g., SUM(amount)), then the entire function or expression is recorded and used as the conversion rule for that field.

[0193] After parsing the SQL and storing the information in three configurable tables, the automated pipeline engine module needs to have the ability to read information from these tables and reassemble it into a complete, executable SQL statement. The process of reading information from the three configurable tables and reassembling it into a complete, executable SQL statement in this invention is as follows:

[0194] 1. Read information from the configuration table

[0195] Using Python scripts and ODBC database connections, task-related information is read from three configurable tables and stored in corresponding list structures to prepare for subsequent SQL concatenation.

[0196] (1) Read job information: Read the target table name, database environment, filter conditions (WHERE, GROUP BY, HAVING, ORDER BY, etc.), primary key fields, etc. from the job information table (map_job_info) and store them in the job_info list. Example query statement is as follows:

[0197] SELECT

[0198] JOB_NAME,

[0199] DATABASE_ENV,

[0200] TRIM(CONCAT_WS(”,WHERE_INFO,GROUP_INFO,HAVING_INFO,ORDER_INFO))ASWHERE_INFO,

[0201] TAB_CODE,

[0202] ALGORITHMCODE,

[0203] CASE WHEN IFNULL(PKCOLUMN,”)<>”THEN PKCOLUMN ELSE”END AS PKCOLUMN

[0204] FROM MAP_JOB_INFO

[0205] WHERE JOB_NAME='FDM_T10_AC_PR_LOAN_LEDG_0001'

[0206] AND SYS_CODE = 'SIT'

[0207] AND IS_LATEST = 1;

[0208] (2) Read job field mapping information: Read field number, field name, field mapping relationship, and other information from the job field mapping information table (map_mapping_col_info), sort by field number, and store it in the map_info list. Example query statement is as follows:

[0209] SELECT

[0210] JOB_NAME,

[0211] DATABASE_ENV,

[0212] JOB_NAME,

[0213] COL_NO,

[0214] COL_NAME,

[0215] COL_MAPPING

[0216] FROM MAP_MAPPING_COL_INFO

[0217] WHERE JOB_NAME='FDM_T10_AC_PR_LOAN_LEDG_0001'

[0218] AND SYS_CODE = 'SIT'

[0219] AND IS_LATEST = 1

[0220] ORDER BY COL_NO;

[0221] (3) Read job association information: Retrieves information such as the associated table name, table alias, association type, and association conditions from the job association information table (map_table_related), sorts them according to the association order, and stores them in the rel_info list. Example query statement is as follows:

[0222] SELECT

[0223] JOB_NAME,

[0224] DATABASE_ENV,

[0225] JOIN_TABLE,

[0226] TABLE_ALIAS,

[0227] IFNULL(JOIN_TYPE,”)AS JOIN_TYPE,

[0228] IFNULL(JOIN_CONDITION,”)AS JOIN_CONDITION

[0229] FROM MAP_TABLE_RELATED

[0230] WHERE JOB_NAME='FDM_T10_AC_PR_LOAN_LEDG_0001'

[0231] ORDER BY SUBSTR(TABLE_ALIAS,2);

[0232] 2. SQL statement concatenation

[0233] Based on the structured information stored in the three lists above, and combined with the fixed rules of SQL syntax, the complete SQL statement is reconstructed through text concatenation. The core Python code is shown below:

[0234]

[0235]

[0236]

[0237] This function iterates through the configuration information, dynamically concatenates the SELECT field list, FROM clause, JOIN join conditions, and WHERE filtering conditions, and finally generates a complete SQL statement that conforms to the syntax rules for subsequent execution in the corresponding environment.

[0238] like Figure 3 As shown, the method for implementing environment management in data development is as follows:

[0239] (1) Task creation and submission: Data developers create new data development tasks and submit SQL code to the automation pipeline engine module.

[0240] (2) SQL Parsing and Storage: The automated pipeline engine module receives SQL code, parses it, and stores the decomposed structured information in three configurable tables managed by the unified configuration center module. The system assigns a version number to this submission (the initial version for new jobs is 1.0, and for optimized jobs, the version number is incremented by 0.1), and marks it as a SIT test status and a latest version identifier.

[0241] (3) Pre-SIT Offset Detection: The offset detection and interception module automatically calls predefined detection rules to verify this submission. Only after passing all relevant checks can the task proceed to the next stage.

[0242] (4) Create SIT environment resources: After the test is passed, the dynamic isolation sandbox module dynamically creates a unique database for the task in the SIT environment through the SIT tenant (database name format: SIT_data development task number).

[0243] (4) SIT test data generation: The automated pipeline engine module reads the information of the SIT state from the three configurable tables, splices them into a complete executable SQL script, and runs the script in the SIT database created in the previous step to generate test result data.

[0244] (5) SIT Test Execution: Data developers test and verify the result data in the SIT environment. After the test is passed, the unified configuration center module updates the status of the task in the three configuration tables to the UAT test status.

[0245] (6) Create UAT environment resources: The dynamic isolation sandbox module dynamically creates a unique database for the task in the UAT environment through the UAT tenant (database name format: UAT_data development task number).

[0246] (7) UAT test data generation: The automated pipeline engine module reads the configuration information in the UAT state, concatenates the complete SQL statement and executes it in the UAT database to generate UAT test data.

[0247] (8) UAT test execution: UAT testers conduct acceptance testing on the test results. After acceptance, the task enters the pre-production offset detection stage.

[0248] (9) Pre-production offset detection: The offset detection and interception module restarts and performs pre-production rule checks. After passing the checks, the unified configuration center module marks the task status as "pending release".

[0249] (10) Production Release: During the specified production release window, the operation and maintenance personnel use the unified configuration center module to securely synchronize the task information in the "pending production" state in the three configuration tables to the corresponding configuration table in the production environment through a reliable database synchronization tool, and finally update the task status to "production operation status" to complete the entire release process.

[0250] Based on the detailed description of the above modules and mechanisms, this invention proposes a data development environment management method, including the following steps:

[0251] Step S1: Receive and store data development task configuration information related to the System Integration Test Environment (SIT), User Acceptance Test Environment (UAT), and Production Environment (PROD) through the unified configuration center module;

[0252] Step S2: The automated pipeline engine module parses the SQL script code submitted by the data developers and stores the parsed structured information in the unified configuration center module.

[0253] Step S3: Using the dynamic isolation sandbox module, on a unified big data platform infrastructure, based on resource isolation technology, create mutually isolated tenants for the SIT environment, UAT environment, and PROD environment, and grant SIT tenants and UAT tenants the permission to read data in the PROD environment.

[0254] Step S4: Before the key nodes of the task flow driven by the automated pipeline engine module, the offset detection and interception module detects the deviation between the environment configuration or data and the expected state based on predefined rules, and intercepts when the deviation is detected.

[0255] Step S5: The automated pipeline engine module reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task, so as to complete the entire process from SIT testing, UAT testing to production deployment.

[0256] Furthermore, the parsing of the SQL script code in step S2 specifically includes:

[0257] The SQL script code is preprocessed and parsed, and the parsed job information, job association information, and field mapping information are stored in the job information table, job association information table, and job field mapping information table, respectively; among them...

[0258] The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code;

[0259] The job association information table stores the association table names and association conditions from the SQL script code;

[0260] The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

[0261] Furthermore, the creation of tenants for the three environments based on resource isolation technology described in step S3 is implemented as follows:

[0262] Using Kubernetes as the container orchestration platform, each tenant is assigned a Kubernetes Namespace, and isolation is achieved at the compute, storage, and network levels by setting resource quotas and network policies;

[0263] The specific implementation of granting SIT tenants and UAT tenants the permission to read PROD environment data in step S3 includes:

[0264] The database names of the SIT, UAT, and PROD environments are standardized and managed. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], which enables SIT tenants and UAT tenants to access the data in the PROD environment in real time as needed during testing.

[0265] Furthermore, the specific process of the offset detection and interception module in step S4 is as follows:

[0266] All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code; the detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification;

[0267] When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table.

[0268] The task-related context information is used as variables, dynamically replaced in the pre-defined detection rule SQL script code, and then executed; the execution result of the detection rule SQL script code is used to determine whether the corresponding detection rule is hit;

[0269] If no deviation is detected by any of the triggered detection rules, the task will be automatically approved to proceed to the next stage; if any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details.

[0270] The key nodes mentioned in step S4 include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

[0271] Furthermore, the process of parsing the SQL script code and storing the decomposed structured information in step S2 is as follows:

[0272] Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these).

[0273] Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships;

[0274] Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table.

[0275] Furthermore, the method for reading configuration information from the unified configuration center module and assembling it into a complete executable SQL script code in step S5 is as follows:

[0276] The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes:

[0277] Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively.

[0278] According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

[0279] This invention also proposes a data development environment management system, comprising:

[0280] The unified configuration center module is used to centrally store and manage the configuration information of the system integration test environment (SIT), user acceptance test environment (UAT), and production environment (PROD).

[0281] The automated pipeline engine module parses the SQL script code submitted by data developers and stores the parsed structured information in the unified configuration center module. It reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task to complete the entire process from SIT testing and UAT testing to production deployment.

[0282] The dynamic isolation sandbox module is used to create mutually isolated tenants for the SIT environment, UAT environment and PROD environment on a unified big data platform infrastructure based on resource isolation technology, and to grant SIT tenants and UAT tenants the permission to read data in the PROD environment.

[0283] The offset detection and interception module detects deviations between the environment configuration or data and the expected state based on predefined rules before the key nodes of the task flow driven by the automated pipeline engine module, and intercepts the deviation when it is detected.

[0284] Furthermore, the unified configuration center module stores and manages SQL script code in a structured manner by maintaining a job information table, a job association information table, and a job field mapping information table;

[0285] The automated pipeline engine module preprocesses and parses the SQL script code, and stores the parsed job information, job association information, and field mapping information in the job information table, job association information table, and job field mapping information table, respectively; wherein...

[0286] The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code;

[0287] The job association information table stores the association table names and association conditions from the SQL script code;

[0288] The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

[0289] Furthermore, the dynamic isolation sandbox module uses Kubernetes as a container orchestration platform, assigning each tenant a Kubernetes Namespace, and achieving isolation at the computing, storage, and network levels by setting resource quotas and network policies;

[0290] Granting SIT and UAT tenants access to read PROD environment data includes: standardizing the database names of the SIT, UAT, and PROD environments. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], enabling SIT and UAT tenants to access PROD environment data in real time as needed during testing.

[0291] Furthermore, the offset detection and interception module has a built-in offset detection rule table. All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code. The detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification.

[0292] When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table.

[0293] The task-related context information is used as variables, dynamically replaced in the pre-defined detection rule SQL script code, and then executed; the execution result of the detection rule SQL script code is used to determine whether the corresponding detection rule is hit;

[0294] If no deviation is detected by any of the triggered detection rules, the task will be automatically approved to proceed to the next stage; if any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details.

[0295] The key nodes include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

[0296] Furthermore, the automated production line engine module is further configured as follows:

[0297] The process of parsing the SQL script code and storing the decomposed structured information is as follows:

[0298] Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these).

[0299] Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships;

[0300] Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table.

[0301] The configuration information is read from the unified configuration center module and assembled into a complete executable SQL script. The specific process is as follows:

[0302] The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes:

[0303] Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively.

[0304] According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

[0305] The above description is only a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in the present invention should be included within the scope of protection of the present invention.

[0306] It should be understood that, in order to simplify the present invention and help those skilled in the art understand its various aspects, in the above description of exemplary embodiments of the present invention, various features of the present invention are sometimes described in a single embodiment or with reference to a single figure. However, the present invention should not be construed as including all features in the exemplary embodiments as essential technical features of the claims of this patent.

[0307] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program instructing related hardware, and the program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0308] It should be understood that the modules, units, components, etc., included in the device of one embodiment of the present invention can be adaptively changed to be placed in a device different from that embodiment. Different modules, units, or components included in the device of the embodiment can be combined into a single module, unit, or component, or they can be divided into multiple sub-modules, sub-units, or sub-components.

[0309] The modules, units, or components in the embodiments of the present invention can be implemented in hardware, in software running on one or more processors, or in a combination thereof. Those skilled in the art will understand that microprocessors or digital signal processors (DSPs) can be used in practice to implement the embodiments of the present invention. The present invention can also be implemented on a computer program product or computer-readable medium for performing some or all of the methods described herein.

Claims

1. A data development environment management method, characterized in that, Includes the following steps: Step S1: Receive and store data development task configuration information related to the System Integration Test Environment (SIT), User Acceptance Test Environment (UAT), and Production Environment (PROD) through the unified configuration center module; Step S2: The automated pipeline engine module parses the SQL script code submitted by the data developers and stores the parsed structured information in the unified configuration center module. Step S3: Using the dynamic isolation sandbox module, on a unified big data platform infrastructure, based on resource isolation technology, create mutually isolated tenants for the SIT environment, UAT environment, and PROD environment, and grant SIT tenants and UAT tenants the permission to read data in the PROD environment. Step S4: Before the key nodes of the task flow driven by the automated pipeline engine module, the offset detection and interception module detects the deviation between the environment configuration or data and the expected state based on predefined rules, and intercepts when the deviation is detected. Step S5: The automated pipeline engine module reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task, so as to complete the entire process from SIT testing, UAT testing to production deployment.

2. The data development environment management method according to claim 1, characterized in that, The specific steps in step S2 involving parsing the SQL script code include: The SQL script code is preprocessed and parsed, and the parsed job information, job association information, and field mapping information are stored in the job information table, job association information table, and job field mapping information table, respectively; among them... The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code; The job association information table stores the association table names and association conditions from the SQL script code; The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

3. The data development environment management method according to claim 1, characterized in that, The creation of tenants for the three environments based on resource isolation technology in step S3 is implemented as follows: Using Kubernetes as the container orchestration platform, each tenant is assigned a Kubernetes Namespace, and isolation is achieved at the compute, storage, and network levels by setting resource quotas and network policies; The specific implementation of granting SIT tenants and UAT tenants the permission to read PROD environment data in step S3 includes: The database names of the SIT, UAT, and PROD environments are standardized and managed. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], which enables SIT tenants and UAT tenants to access the data in the PROD environment in real time as needed during testing.

4. The data development environment management method according to claim 1, characterized in that, The specific process of the offset detection and interception module in step S4 is as follows: All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code; the detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification; When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table. The task-related context information is used as a variable, dynamically replaced in the preset detection rule SQL script code, and then executed. The execution result of the SQL script code according to the detection rules determines whether the corresponding detection rules are matched. If no deviation is detected by all triggered detection rules, the task will be automatically approved to proceed to the next stage; If any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details. The key nodes mentioned in step S4 include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

5. The data development environment management method according to claim 1, characterized in that, The process of parsing the SQL script code and storing the decomposed structured information in step S2 is as follows: Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these). Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships; Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table. The method for reading configuration information from the unified configuration center module and assembling it into a complete executable SQL script in step S5 is as follows: The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes: Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively. According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

6. A data development environment management system, characterized in that, include: The unified configuration center module is used to centrally store and manage the configuration information of the system integration test environment (SIT), user acceptance test environment (UAT), and production environment (PROD). The automated pipeline engine module parses the SQL script code submitted by data developers and stores the parsed structured information in the unified configuration center module. It reads configuration information from the unified configuration center module, assembles it into executable SQL script code, and executes it in the target environment database dynamically created by the dynamic isolation sandbox module for the corresponding task to complete the entire process from SIT testing and UAT testing to production deployment. The dynamic isolation sandbox module is used to create mutually isolated tenants for the SIT environment, UAT environment and PROD environment on a unified big data platform infrastructure based on resource isolation technology, and to grant SIT tenants and UAT tenants the permission to read data in the PROD environment. The offset detection and interception module detects deviations between the environment configuration or data and the expected state based on predefined rules before the key nodes of the task flow driven by the automated pipeline engine module, and intercepts the deviation when it is detected.

7. The data development environment management system according to claim 6, characterized in that, The unified configuration center module stores and manages SQL script code in a structured manner by maintaining a job information table, a job association information table, and a job field mapping information table. The automated pipeline engine module preprocesses and parses the SQL script code, and stores the parsed job information, job association information, and field mapping information in the job information table, job association information table, and job field mapping information table, respectively; wherein... The job information table stores the target table name, ETL algorithm, and WHERE conditions from the SQL script code; The job association information table stores the association table names and association conditions from the SQL script code; The job field mapping information table stores the mapping relationship between the fields in the SELECT part and the fields in the INSERT part of the SQL script code.

8. The data development environment management system according to claim 6, characterized in that, The dynamic isolation sandbox module uses Kubernetes as a container orchestration platform, assigns each tenant a Kubernetes namespace, and achieves isolation at the compute, storage, and network levels by setting resource quotas and network policies. Granting SIT and UAT tenants access to read PROD environment data includes: standardizing the database names of the SIT, UAT, and PROD environments. The database name format for the SIT and UAT environments is [Base Database Name]_[Development Task Number]_[Environment Identifier], enabling SIT and UAT tenants to access PROD environment data in real time as needed during testing.

9. The data development environment management system according to claim 6, characterized in that, The offset detection and interception module has a built-in offset detection rule table. All detection rules are predefined and stored in the offset detection rule table in the form of executable SQL script code. The detection rules include primary key uniqueness check, key field fill rate check, code style verification, and environment consistency verification. When the data development task flows through the automated pipeline to the preset key node, the offset detection and interception module will be automatically triggered. Based on the current stage of the task, it will automatically load all the rules that have been enabled at that stage from the detection rule table. The task-related context information is used as a variable, dynamically replaced in the preset detection rule SQL script code, and then executed. The execution result of the SQL script code according to the detection rules determines whether the corresponding detection rules are matched. If no deviation is detected by all triggered detection rules, the task will be automatically approved to proceed to the next stage; If any detection rule detects a deviation, the execution of the automated pipeline will be immediately paused, the deployment process will be interrupted, and an alarm message will be issued, indicating the triggered detection rule and the specific deviation details. The key nodes include at least the period before the task enters the SIT test and the period before the task is deployed to the PROD environment.

10. The data development environment management system according to claim 6, characterized in that, The automated production line engine module is further configured to: The process of parsing the SQL script code and storing the decomposed structured information is as follows: Standardize SQL script code, including removing comments, compressing spaces and line breaks, and unifying keyword case (one or more of these). Using a syntax parsing library, SQL script code strings are converted into abstract syntax trees, which identify the target table, source table, join conditions, and core clauses of field mapping relationships; Based on the abstract syntax tree structure, extract the target table name, WHERE condition, GROUP BY operation, associated table name, association type, source field name, target field name and transformation rule from the corresponding clauses, and populate them into the job information table, job association information table and job field mapping information table. The configuration information is read from the unified configuration center module and assembled into a complete executable SQL script. The specific process is as follows: The program reads the configuration information of the specified data development task and target environment from the job information table, job association information table, and job field mapping information table, and dynamically concatenates them into a complete executable statement conforming to SQL syntax specifications using a program script; the concatenation process includes: Through the database connection interface, the task's filter conditions, field list, and table association information are read from the job information table, job field mapping information table, and job association information table, respectively. According to the SQL syntax rules, the read SELECT field list, FROM and JOIN clauses, WHERE conditions and other components are concatenated in order to generate complete SQL script code.

Images