A method and device for sensing, determining and handling data structure changes

Abstract

The application discloses a method and device for sensing, judging and processing data structure change. The method comprises the following steps: collecting a data structure change event based on a change data capture tool; constructing a data structure change abstract syntax tree through a SQL parser to accurately identify the change operation type and content; obtaining structure snapshots before and after the change; executing compatibility judgment in combination with a compatibility rule library to output the compatibility state and risk level; adopting a hierarchical message notification strategy according to the risk level; and automatically triggering or arranging corresponding processing actions, such as automatically synchronizing fields, generating a compatible view or suspending a high-risk task. The application realizes semantic-level analysis of data structure change, automatic compatibility judgment, refined risk grading and automatic processing, significantly reduces the cost of manual intervention, effectively avoids downstream synchronization interruption and data quality abnormalities caused by structure change, and improves the stability and automatic operation and maintenance level of a medium or large data platform.

Description

Technical Field

[0001] This application relates to the field of data management technology, and in particular to data structure change management in scenarios such as data warehouses, data lakes, and real-time data synchronization and integration. Specifically, it discloses a method and apparatus for sensing, judging and handling data structure changes. Background Technology

[0002] In practical applications, changes to the data structure of the data source (such as table structure, field definitions, etc.) (i.e., DDL operations) are common. Existing CDC (Change Data Capture) technologies can capture DDL events from database logs and output them as change messages. However, existing solutions typically only retain the raw DDL statements or simple event identifiers, lacking the ability to perform structured parsing of DDL semantics, making it difficult to identify specific change actions and field-level changes.

[0003] Meanwhile, existing technologies typically lack a mechanism to link schema snapshots before and after changes, and also lack the ability to analyze schema differences in conjunction with downstream task dependencies, making it impossible to accurately determine whether a DDL change will cause downstream synchronization interruption, missing fields, type conversion failure, or abnormal data quality.

[0004] At the handling level, existing solutions mostly remain at the stage of unified alarms or manual troubleshooting, lacking a tiered notification and automatic adaptation mechanism based on compatibility assessment results. This makes it difficult to meet the real-time, stability, and automated operation and maintenance requirements of medium and large-scale data platforms. The main reasons for the above deficiencies are:

[0005] (1) Existing CDC technology focuses on log capture and transmission rather than DDL semantic understanding, which results in DDL events often existing in the form of raw strings, making it difficult to perform structured analysis that can be judged by machines.

[0006] (2) Existing systems typically lack the ability to manage time-series schema versions and cannot obtain comparable structural snapshots before and after DDL execution;

[0007] (3) The existing solution does not link structural change information with downstream field dependencies, task dependencies and target execution capabilities, so it is difficult to accurately identify compatibility risks;

[0008] (4) The existing system lacks a linkage interface with the task scheduling system, metadata system and target execution engine, which means that DDL change processing still mainly relies on manual intervention.

[0009] In summary, existing technologies lack an intelligent solution for handling data source structure changes. This solution is capable of semantic parsing, compatibility assessment, risk classification, and automatic handling of DDL changes, and therefore cannot meet the needs of medium and large-scale data platforms for efficient and stable data synchronization and change management. Summary of the Invention

[0010] To address the aforementioned problems in existing technologies regarding data source structure change handling, this invention provides a method and apparatus for intelligent sensing, compatibility assessment, and tiered handling of data structure changes. The purpose of this invention is not simply to "notify upon discovering a Data Change Request (DDL)," but rather to automatically identify the DDL type, determine its compatibility with downstream processes, and employ different notification and handling actions based on the risk level, thereby achieving more intelligent, efficient, and reliable data structure change management.

[0011] Terminology Explanation:

[0012] Flink CDC (Change Data Capture): An open-source framework for capturing and processing database change logs, capable of capturing data changes (including DML and DDL operations) from various databases in real time and converting them into streaming data.

[0013] DDL (Data Definition Language): A language used to define and manage the structure of database objects (such as tables, indexes, and views). Common DDL operations include CREATE TABLE, ALTER TABLE, and DROP TABLE.

[0014] AST (Abstract Syntax Tree): An abstract syntax tree is an abstract representation of the source code structure, showing the syntax structure of a programming language in a tree-like form. For DDL statements, AST can parse them into structured objects, making them easier for the program to understand.

[0015] Schema Snapshot: The structural definition of a database or table at a specific point in time, including information such as table name, column name, column type, and constraints.

[0016] Compatibility rule base: A predefined set of rules used to determine the compatibility of DDL operations with downstream data processing systems or data models.

[0017] To achieve the above objectives, the present invention employs the following technical strategies:

[0018] (1) DDL semantic level parsing: Existing technologies only extract DDL record strings from logs. This invention introduces a DDLSQL parser to construct an abstract syntax tree (AST), which can accurately understand every detail of DDL changes (such as operation type, involved table columns, new and old attributes, etc.), which is the basis for realizing intelligent judgment and handling.

[0019] (2) Acquisition and comparison of structural snapshots before and after changes: Not only can changes be captured, but the complete schema structure before and after the DDL operation can also be acquired and compared, providing the necessary data context for accurate compatibility determination.

[0020] (3) Intelligent compatibility assessment: Establish a clear compatibility rule base, which can intelligently assess the compatibility of changes with downstream data synchronization and applications based on the semantics and schema comparison results of DDL, and identify potential incompatibility risks. This is a key leap from "notification" to "understanding the impact".

[0021] (4) Tiered notification strategy: Based on the compatibility assessment results, the risk is divided into four levels: low, medium, high and very high. Different notification channels and notification targets are used in a targeted manner to avoid notification overload and improve response efficiency and priority.

[0022] (5) Automated handling mechanism: For DDL changes with different compatibility status and risk level, the present invention can automatically trigger or arrange adaptive handling actions (such as automatically generating SQL to complete fields, creating compatible views, pausing tasks, etc.), minimizing manual intervention and greatly improving the automation level of data synchronization and system stability.

[0023] (6) Paradigm shift from passive to proactive: This invention transforms data structure change management from "passive perception and manual processing" to a new paradigm of "proactive intelligent perception, automatic evaluation, hierarchical early warning and automated handling", which significantly improves the resilience and operational efficiency of the data platform.

[0024] Specifically, this application provides the following technical solutions:

[0025] The first aspect of this application provides a method for sensing, determining, and handling data structure changes, such as... Figure 1 As shown, it includes the following steps:

[0026] S1. Collect data structure change events: Based on the change data capture tool, obtain the log change records of the monitored data source and identify the data structure change events in them;

[0027] S2. Construct a data structure change syntax tree: Perform semantic parsing on the structured query language statements in the data structure change event to generate an abstract syntax tree to identify the data structure change operation type and specific change content.

[0028] S3. Obtain structural snapshots before and after the change: Based on the abstract syntax tree and the changed data capture context information, obtain the first structural snapshot before the data structure change operation occurs and the second structural snapshot after the change occurs.

[0029] S4. Perform compatibility determination: Based on the first structure snapshot, the second structure snapshot, and the preset compatibility rule base, determine the compatibility of the data structure change with downstream data consumers, and generate a compatibility determination result. The compatibility determination result includes at least the compatibility status and risk level.

[0030] S5. Tiered message notification: Based on the risk level in the compatibility assessment result, different levels of notification strategies are adopted to send data structure change information to the preset recipients.

[0031] S6. Automatic handling: Based on the compatibility status and risk level in the compatibility determination result, automatically trigger or arrange corresponding handling actions.

[0032] Furthermore, in the method of this application, step S2 includes: parsing the data structure change statement using a structured query language parser and converting it into a structured object. The structured object contains the data structure change operation type, the table name involved, and a list of change actions. The list of change actions includes at least one of the following: change action type, affected column name, data type, whether nullable, default value, and old column name.

[0033] Furthermore, in the method of this application, step S3 includes:

[0034] Extract the database or table structure context before the change from the change history of the change data capture tool; and / or

[0035] Query database metadata to obtain the real-time structure after changes; and / or

[0036] Maintain a local data structure version repository, and deduce the structure before and after the change based on the historical records of the version repository and the current data structure changes.

[0037] Furthermore, in the method of this application, step S4 includes:

[0038] Based on the change actions, first structure snapshot, second structure snapshot, and downstream dependency information corresponding to the data structure change object extracted from the abstract syntax tree, a structural difference description result is generated. The structural difference description result includes at least object-level change type, field-level change type, field attribute change item, constraint change item, and the identifier of the affected downstream object.

[0039] The rule engine is invoked to match the structural difference description results according to preset judgment rules, and output the compatibility status, risk level, scope of impact and handling suggestions.

[0040] Furthermore, in the method of this application, the compatibility rule base mentioned in step S4 includes at least the following rules:

[0041] Adding nullable columns or columns with default values ​​is considered low-risk and is generally compatible.

[0042] Deleting a column is considered high-risk; if the column is referenced by a downstream task, it will be incompatible.

[0043] Renaming columns is considered high-risk and is generally incompatible.

[0044] The column type has been broadened and is classified as medium risk; it is generally compatible.

[0045] The column type is narrowed, which is deemed high-risk and incompatible.

[0046] The newly added non-empty constraint is classified as high-risk and is incompatible with the write link.

[0047] The table renaming was deemed extremely high-risk and incompatible.

[0048] Furthermore, in the method of this application, step S5 includes:

[0049] If the risk level is low, the data development group will be notified via WeChat group or email.

[0050] If the risk level is medium, the data development group and the responsible persons involved in the data structure change will be notified via WeChat group or email.

[0051] If the risk level is high, the data development group, the person in charge of the data structure change, and the on-duty personnel will be notified via WeChat group and email, and a work order will be automatically created in the work order system.

[0052] If the risk level is extremely high, in addition to issuing a high-risk notification, the action of automatically suspending the relevant data synchronization task will be triggered.

[0053] Furthermore, in the method of this application, step S6 includes:

[0054] If the data structure is changed to include new columns, the add column statement for the target data warehouse will be automatically generated and executed;

[0055] If the data structure is changed to delete a column, the target synchronization task instance that has a dependency relationship with that field will be automatically paused, subsequent data writing will be blocked, and manual confirmation will be required.

[0056] If the data structure is changed to rename columns, a compatible view is automatically generated, and the old field aliases are maintained in the view;

[0057] If the data structure is changed to modify the column type, a compatibility check of the downstream table structure will be automatically performed, and a corresponding conversion or adjustment script will be generated for approval.

[0058] Furthermore, in the method of this application, before step S6, the following steps are also included:

[0059] The processing module verifies the data structure change support capability of the target data storage system, execution window constraints, and target table status. If the preset execution conditions are met, the processing action is automatically executed; otherwise, a processing task pending approval is generated.

[0060] Furthermore, the method of this application also includes:

[0061] Recording steps: Record the detailed information of this data structure change in the change information table. The detailed information includes the original data structure change statement, abstract syntax tree, snapshot of the structure before and after the change, compatibility judgment result, risk level, notification record, handling action and its result, for subsequent auditing, tracking and analysis.

[0062] The second aspect of this application provides a device for sensing, determining, and handling data structure changes. When the device operates, it implements the steps of the aforementioned method for sensing, determining, and handling data structure changes, such as... Figure 2 As shown, the device includes:

[0063] The acquisition module is used to obtain log change records of the monitored data source based on the change data capture tool and identify data structure change events in them;

[0064] The parsing module is used to perform semantic parsing on the structured query language statements in the data structure change event, generate an abstract syntax tree, and identify the data structure change operation type and specific change content.

[0065] The structure snapshot module is used to obtain a first structure snapshot before the data structure change operation occurs and a second structure snapshot after the data structure change operation occurs, based on the abstract syntax tree and the changed data capture context information.

[0066] The compatibility determination module is used to determine the compatibility of the data structure change with downstream data consumers based on the first structure snapshot, the second structure snapshot and the preset compatibility rule base, and generate a compatibility determination result. The compatibility determination result includes at least the compatibility status and risk level.

[0067] The notification module is used to send data structure change information to a preset recipient by adopting different levels of notification strategies based on the risk level in the compatibility determination result.

[0068] The handling module is used to automatically trigger or schedule corresponding handling actions based on the compatibility status and risk level in the compatibility determination result.

[0069] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0070] Memory: Used to store computer programs;

[0071] Processor: Used to execute the computer program to implement the steps of the aforementioned method for sensing, determining and handling data structure changes.

[0072] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned method for sensing, determining, and handling data structure changes.

[0073] In summary, compared with the prior art, the solution of the present invention has the following advantages:

[0074] (1) DDL semantic level recognition capability: By introducing DDL semantic parsing and AST construction, it is possible to accurately understand the intent and specific content of DDL changes, rather than simply identifying them as "structural changes", laying the foundation for subsequent intelligent judgment and handling.

[0075] (2) Automated compatibility assessment: Based on the structural snapshots before and after the change and the preset rule base, it can automatically assess the compatibility of DDL changes with downstream, identify potential risks from the source, and avoid the subjectivity and inefficiency of manual comparison and experience judgment.

[0076] (3) Refined risk classification management: DDL changes are classified into low, medium, high and very high risk levels according to their impact on downstream industries, making notifications more accurate and enabling recipients to respond quickly according to the risk level, thereby improving the efficiency of change management and the priority of emergency handling.

[0077] (4) Automatic handling capability: For DDL changes with different risk levels and compatibility status, the system can automatically arrange and execute corresponding handling actions, such as automatically filling in fields, generating compatible views, pausing high-risk tasks, etc., which greatly reduces manual intervention, shortens fault recovery time, and ensures the continuity and stability of data synchronization.

[0078] (5) Improve the stability and reliability of the data platform: The model of passive perception and manual intervention has been transformed into a model of active identification, intelligent judgment and automatic handling, which significantly reduces the impact of DDL changes on data synchronization and downstream applications, and improves the stability, reliability and data governance level of the entire data platform.

[0079] (6) Applicable to complex data ecosystems: This solution is applicable to complex data ecosystems such as medium and large data warehouses, data lakes, and real-time synchronization platforms, and can effectively manage structural changes in multi-source heterogeneous data systems.

[0080] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0081] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0082] Figure 1 This is a flowchart illustrating the overall implementation process of the methods for sensing, judging, and handling data structure changes in this application.

[0083] Figure 2 This is a schematic diagram of the functional modules of the data structure change sensing, judgment and handling (notification) device in an embodiment of this application.

[0084] Figure 3 This is a schematic diagram illustrating the operation flow of the data structure change perception, judgment, and handling method in an embodiment of this application.

[0085] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0086] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be noted that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0087] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0088] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0089] This invention provides a method for sensing, determining, and handling data structure changes, including:

[0090] Collect data structure change events: Obtain log change records of the monitored data source based on the Change Data Capture (CDC) tool, and identify data structure change (DDL) events within them;

[0091] Constructing a DDL syntax tree (AST): Semantic parsing of SQL statements in the identified DDL events to generate a standardized, structured DDL syntax tree (AST) to accurately identify the DDL operation type and specific changes.

[0092] Obtain schema snapshots before and after the change: Based on the DDL syntax tree and the context information captured by CDC, obtain data structure snapshots before and after the DDL operation.

[0093] Perform compatibility assessment: Based on the structural snapshots before and after the change and the preset compatibility rule base, determine the compatibility of the data structure change with downstream data consumers and generate a compatibility assessment result, which includes at least the compatibility status and risk level.

[0094] Tiered message notification: Based on the risk level in the compatibility assessment results, different levels of notification strategies are adopted to send data structure change information to preset recipients;

[0095] Automatic handling: Based on the compatibility status and risk level in the compatibility determination results, automatically trigger or schedule corresponding handling actions.

[0096] In one implementation, the step of constructing the DDL syntax tree (AST) includes:

[0097] The DDL statement is parsed using an SQL parser (such as a custom parser based on Calcite or ANTLR) and converted into a structured object. The structured object contains the DDL operation type (such as ALTER TABLE, DROP TABLE, RENAMETABLE, etc.), the table names involved, and a detailed list of change actions (such as ADD COLUMN, DROP COLUMN, MODIFYCOLUMN, etc. and their corresponding parameters).

[0098] In one implementation, the step of obtaining a snapshot of the structure before and after the change includes:

[0099] Extract the database / table structure context before the change from the historyRecord information in the CDC tool; and / or

[0100] Query database metadata (such as information_schema or system views) to obtain the real-time structure after changes; and / or

[0101] Maintain a local data structure version repository and deduce the structure before and after the change based on the repository's history and current DDL changes.

[0102] In one implementation, the step of performing the compatibility determination includes:

[0103] The compatibility assessment module includes a rule engine, which outputs the compatibility status and risk level based on DDL operation type, field attribute changes, structural difference results, downstream dependencies, and target system constraints.

[0104] The rule base includes at least the following example rules:

[0105] Adding nullable columns or columns with default values: deemed low risk, generally compatible;

[0106] Delete column: This is considered high-risk; if the column is referenced by a downstream task, it will be incompatible.

[0107] Column renaming: Deemed high-risk, usually incompatible;

[0108] Column type relaxation (e.g., INT to BIGINT, DECIMAL(10,2) to DECIMAL(18,2)): classified as medium risk, generally compatible;

[0109] Narrowing column types (e.g., converting BIGINT to INT, VARCHAR(200) to VARCHAR(32)): This is considered high-risk and incompatible.

[0110] Added NOT NULL constraint: Determined to be high-risk and incompatible with write links;

[0111] Table renaming: Determined to be of extremely high risk and incompatible.

[0112] Based on the structural snapshots before and after the change, and by referring to the compatibility rule base, generate a compatibility status (such as "compatible" or "incompatible") and a risk level (such as "low", "medium", "high", or "extremely high"), as well as the specific reasons for incompatibility.

[0113] In one implementation, the hierarchical message notification step includes:

[0114] If the risk level is "low", the data development group will be notified via WeChat group or email.

[0115] If the risk level is "medium", the data development group and the responsible persons involved in the DDL will be notified via WeChat group or email.

[0116] If the risk level is "high", then the data development group, the person in charge of the DDL, and the on-duty personnel will be notified through the enterprise WeChat group and email, and a work order will be automatically created in the work order system.

[0117] If the risk level is "extremely high", then in addition to executing the high-risk notification, the action of automatically suspending the relevant data synchronization task will be triggered.

[0118] In one implementation, the automatic processing steps include:

[0119] If the DDL operation involves adding a new column: automatically generate and execute the "ALTER TABLE ... ADD COLUMN" statement for the target data warehouse (such as Hive, Doris, StarRocks, etc.);

[0120] If the DDL operation is to delete a column: automatically pause the target synchronization task instance that has a dependency relationship with this field, block subsequent data writing, and wait for manual confirmation;

[0121] If the DDL operation is column renaming: a compatible view is automatically generated, and the old field alias is maintained in the view for a period of time to reduce the impact on downstream operations;

[0122] If the DDL operation modifies the column type: automatically perform a compatibility check on the downstream table structure and generate the corresponding conversion or adjustment script for approval.

[0123] Secondly, the present invention provides a data structure change sensing, judgment, and handling device, comprising:

[0124] Acquisition module: Used to acquire log change records of the monitored data source based on the CDC tool, and identify data structure change events within them;

[0125] The parsing module is used to perform semantic parsing on the SQL statements in the identified DDL events, and generate a standardized, structured DDL syntax tree (AST) to accurately identify the DDL operation type and specific changes.

[0126] The structure snapshot module is used to obtain data structure snapshots before and after a DDL operation, based on the DDL syntax tree and context information captured by CDC.

[0127] Compatibility determination module: Based on the structural snapshots before and after the change and the preset compatibility rule base, it determines the compatibility of the data structure change with downstream data consumers and generates a compatibility determination result, which includes at least the compatibility status and risk level.

[0128] Notification module: Used to send data structure change information to a preset recipient by adopting different levels of notification strategies based on the risk level in the compatibility determination result;

[0129] The handling module is used to automatically trigger or schedule corresponding handling actions based on the compatibility status and risk level in the compatibility determination results.

[0130] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0131] Figure 3 The figure shows a schematic diagram of the operation flow of the data structure change perception, judgment and handling method according to an embodiment of the present invention. The detailed implementation process of this solution is as follows:

[0132] Step S10: Collect data structure change events.

[0133] This step utilizes or enhances existing CDC technology, leveraging Flink CDC or other log capture tools like Debezium to obtain real-time log change records from the monitored data sources (such as binlog, redo log, WAL, etc. of databases like MySQL, PostgreSQL, and Oracle). Based on this, the system further identifies DDL events within these records that indicate changes to the data structure. For example, when Flink CDC or Debezium captures a DDL statement, its output event will include specific markers (such as keySchema being io.debezium.connector.mysql.SchemaChangeKey) or op type SCHEMACHANGE, while also containing the original DDL statement in the historyRecord field.

[0134] Step S20: Construct the DDL syntax tree (AST).

[0135] Once a DDL event is identified, this invention no longer treats it merely as a string, but performs deep semantic parsing on the original DDL SQL statements it contains.

[0136] In practice, an SQL parser can be integrated or developed, for example, based on tools such as Apache Calcite or ANTLR, to build custom DDL syntax parsing rules for different database dialects (such as MySQL and PostgreSQL).

[0137] The parser transforms DDL SQL statements (e.g., ALTER TABLE order_info ADD COLUMN buyer_levelVARCHAR(16) DEFAULT 'N';) into a standardized Abstract Syntax Tree (AST) or structured object. It then uses the DDLAST to extract structured change actions and combines this with before-and-after structural snapshots and downstream dependency information to perform compatibility checks and tiered handling.

[0138] This structured object will contain the following key information:

[0139] opType: Operation type, such as "ALTER_TABLE", "DROP_TABLE", "RENAME_TABLE", etc.

[0140] tableName: The name of the table that is affected.

[0141] actions: A list describing the specific change actions. Each action contains:

[0142] actionType: Action type, such as "ADD_COLUMN", "DROP_COLUMN", "MODIFY_COLUMN", "RENAME_COLUMN", etc.

[0143] columnName: The name of the column that is affected (if applicable).

[0144] dataType: The new data type (if applicable).

[0145] nullable: Whether it can be nullable (if applicable).

[0146] defaultValue: Default value (if applicable).

[0147] oldColumnName: The old column name (used for RENAME COLUMN).

[0148] Through AST, the system can accurately identify every detail of DDL operations, which is the basis for subsequent compatibility determination.

[0149] Step S30: Obtain a snapshot of the structure before and after the change.

[0150] After parsing the DDL statement and understanding its semantics, the system needs to obtain a complete structural snapshot of the data source (or specific table) before and after the DDL operation to perform comparison and compatibility assessment.

[0151] The acquisition methods may include:

[0152] Recovering from CDC historyRecord: CDC tools such as Debezium typically store database schema context information prior to DDL changes in their historyRecord field. The system can parse this information to obtain a snapshot of the structure before the change.

[0153] Querying database metadata: After a DDL operation occurs (or during real-time querying while processing a DDL event), the system can connect to the database via JDBC and query the information_schema (for MySQL) or the corresponding system view (for PostgreSQL, Oracle, etc.) to obtain the latest (i.e., changed) table structure. By combining this with the reverse operation of the DDL statement, the structure before the change can also be derived.

[0154] Maintaining a local schema version repository (preferred method): The system can maintain a local schema version management service. Whenever a DDL event is identified and parsed, the system stores both the old and new schemas. When DDL compatibility needs to be determined, a snapshot of the structure at a specified point in time or for a specified version is directly retrieved from the version repository.

[0155] Ultimately, this step will produce two structured objects: beforeSchema (structure before the change) and afterSchema (structure after the change), which contain detailed information such as column names, data types, nullability, and constraints.

[0156] Step S35: Obtain downstream dependency information.

[0157] Obtain downstream reference relationships of tables or fields involved in DDL from metadata center, task orchestration system, SQL lineage analysis results or field mapping configuration, and form a dependency graph.

[0158] Step S40: Perform a compatibility check.

[0159] The compatibility assessment module generates a structural difference description result based on the change actions extracted from the DDL AST, the schema snapshot before the change, the schema snapshot after the change, and the downstream dependency information corresponding to the DDL object.

[0160] The structural difference description results include at least: object-level change type, field-level change type, field attribute change item, constraint change item, and the identifier of the affected downstream object.

[0161] Subsequently, the compatibility determination module calls the rule engine to match the structural difference description results according to the preset determination rules, and outputs the compatibility status, risk level, scope of impact and handling suggestions.

[0162] Example rules are as follows:

[0163] Low risk (usually compatible):

[0164] Add a nullable column with no DEFAULT values.

[0165] Add a nullable column with a DEFAULT value.

[0166] Medium risk (generally compatible, but requires attention):

[0167] Column types are relaxed: for example, INT → BIGINT, DECIMAL(10,2) → DECIMAL(18,2). Although generally compatible, this may affect downstream storage space or specific computational logic.

[0168] High risk (may be incompatible, manual intervention required):

[0169] Delete column: Incompatible if the column is being used by downstream tasks or models.

[0170] Column renaming: Causes downstream tasks to be unable to find the old column name.

[0171] Narrowing column types, such as BIGINT → INT, VARCHAR(200) → VARCHAR(32), may result in data truncation or conversion failure.

[0172] Added NOT NULL constraint: This is incompatible with downstream write links (if downstream data is directly synchronized via insert or update).

[0173] Extremely high risk (severe incompatibility, manual intervention or task suspension required):

[0174] Renaming the table caused downstream tasks to be unable to find the table at all.

[0175] Delete the table.

[0176] Destructive modifications to core constraints such as primary keys and unique keys.

[0177] The compatibility assessment module compares the beforeSchema and afterSchema, combines the DDL AST, and matches it against the rule base.

[0178] Example of judgment result output:

[0179] {

[0180] "compatibility": "INCOMPATIBLE", / / Compatibility status: COMPATIBLE / INCOMPATIBLE

[0181] "riskLevel": "HIGH", / / Risk Level: LOW / MEDIUM / HIGH / EXTREME_HIGH

[0182] "reason": [

[0183] "DROP_COLUMN phone",

[0184] "column 'phone' is referenced by downstream job 'ods_order_sync'(detected from metadata)"

[0185] ],

[0186] "ddlStatement": "ALTER TABLE order_info DROP COLUMN phone;"

[0187] }

[0188] Step S50: Hierarchical message notification.

[0189] Based on the risk level in the compatibility assessment results generated by S40, the system will implement different levels of message notification strategies. This avoids a "one-size-fits-all" notification approach and improves the effectiveness of notifications.

[0190] Low risk: Notify the data development team of the non-disruptive structural changes via WeChat groups, internal instant messaging tools, or email groups.

[0191] Medium risk: In addition to the low risk notification, the person responsible for the specific data development of the data source or related data domain will be notified.

[0192] High risk: Based on the medium risk notification, further notify the on-duty personnel and automatically create a work order related to the DDL change through the work order system, forcing the responsible person to handle it.

[0193] Extremely high risk: Based on the high-risk notification, immediately trigger the automatic suspension of the relevant data synchronization tasks to prevent data quality issues or task crashes caused by incompatible changes, and wait for manual confirmation and handling.

[0194] Step S60: Automatic processing.

[0195] This step is another core innovation of the present invention. It can automatically trigger or arrange a series of actionable actions based on the compatibility determination results, so as to reduce the impact of DDL changes or automatically complete the adaptation work.

[0196] Before automatic processing, the processing module first verifies the DDL support capabilities of the target data storage system, the execution window constraints, and the status of the target table. If the preset execution conditions are met, it will be executed automatically; otherwise, a processing task pending approval will be generated.

[0197] Example of automatic handling action:

[0198] (1) Compatibility changes for the newly added column (ADD COLUMN):

[0199] If the target data warehouse is Hive, Doris, StarRocks, etc., the system can automatically generate and execute the corresponding ALTER TABLE <target table> ADD COLUMN <new column> <new type> [DEFAULT <default value>]; SQL statement to achieve automatic synchronization of the target table structure.

[0200] (2) For high-risk incompatible changes to delete columns:

[0201] The system will first trigger a notification and, in extremely high-risk situations, suspend the relevant synchronization tasks. The handling module will then prompt manual confirmation as to whether the column truly no longer needs synchronization, or provide guidance on how to modify the downstream model to avoid referencing the column.

[0202] (3) For high-risk incompatible changes to rename columns:

[0203] The system can automatically create a compatible view for the affected table in the downstream target data warehouse (CREATEVIEW <compatible view> AS SELECT <renamed column name> AS <old column name>, ... FROM <original table>;), maintaining the alias of the old field for a period of time, giving downstream consumers a buffer and modification time.

[0204] (4) For medium / high risk changes involving modification of column type:

[0205] The processing module can automatically perform compatibility checks on downstream table structures, such as checking whether the new type meets the requirements of the downstream storage system, and generate corresponding database migration scripts or data conversion functions, which will be executed after manual approval.

[0206] Recording module:

[0207] After message notification and automatic processing, the present invention may further include a recording module for recording the detailed information of this DDL change (including the original DDL, AST, schema before and after the change, compatibility judgment result, risk level, notification record, processing action and its result, etc.) into a change information table for subsequent auditing, tracking and analysis.

[0208] This invention also provides a device for sensing, determining, and handling (notifying) data structure changes.

[0209] Figure 2 This is a functional module diagram of the device according to an embodiment of the present invention. As shown in the figure, the device includes:

[0210] Acquisition module 10: Used to implement the above S10 steps, acquire and identify DDL events.

[0211] Parsing module 20: Used to implement the above S20 steps and construct the DDL syntax tree.

[0212] Structural snapshot module 30: Used to implement the above S30 step and obtain structural snapshots before and after the change.

[0213] Compatibility determination module 40: Used to implement the above S40 steps and perform compatibility determination.

[0214] Notification module 50: Used to implement the above S50 steps and perform hierarchical message notifications.

[0215] Processing module 60: Used to implement the above S60 steps and perform automatic processing.

[0216] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of apparatus, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0217] like Figure 4 As shown in the illustration, an embodiment of this application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned method for sensing, determining, and handling data structure changes.

[0218] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0219] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0220] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0221] Conventional procedural programming languages, such as "C" or similar programming languages.

[0222] The execution methods of program code include, but are not limited to:

[0223] It runs entirely on the user's computer;

[0224] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0225] Execute as a standalone software package;

[0226] It is executed entirely on a remote computer or server.

[0227] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0228] Furthermore, this application also discloses a computer-readable storage medium, which, when the instructions in the computer-readable storage medium are executed by the processor of an electronic device, enables the electronic device to perform the various steps of the data structure change sensing, determination and handling method disclosed in this application.

[0229] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0230] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0231] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0232] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0233] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0234] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0235] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0236] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0237] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0238] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0239] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0240] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0241] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the data structure change sensing, determination, and handling methods disclosed in this application. When the computer program is executed by a processing device, it can achieve the functions defined in the embodiments of this application.

[0242] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0243] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for sensing, determining, and handling data structure changes, characterized in that, Includes the following steps: S1. Collect data structure change events: Based on the change data capture tool, obtain the log change records of the monitored data source and identify the data structure change events in them; S2. Construct a data structure change syntax tree: Perform semantic parsing on the structured query language statements in the data structure change event to generate an abstract syntax tree to identify the data structure change operation type and specific change content. S3. Obtain structural snapshots before and after the change: Based on the abstract syntax tree and the changed data capture context information, obtain the first structural snapshot before the data structure change operation occurs and the second structural snapshot after the change occurs. S4. Perform compatibility determination: Based on the first structure snapshot, the second structure snapshot, and the preset compatibility rule base, determine the compatibility of the data structure change with downstream data consumers, and generate a compatibility determination result. The compatibility determination result includes at least the compatibility status and risk level. S5. Tiered message notification: Based on the risk level in the compatibility assessment result, different levels of notification strategies are adopted to send data structure change information to the preset recipients. S6. Automatic handling: Based on the compatibility status and risk level in the compatibility determination result, automatically trigger or arrange corresponding handling actions.

2. The method according to claim 1, characterized in that, Step S2 includes: using a structured query language parser to parse the data structure change statement and convert it into a structured object. The structured object contains the data structure change operation type, the table names involved, and a list of change actions. The list of change actions includes at least one of the following: change action type, affected column names, data type, whether nullable, default value, and old column names.

3. The method according to claim 1, characterized in that, Step S3 includes: Extract the database or table structure context before the change from the change history of the change data capture tool; and / or Query database metadata to obtain the real-time structure after changes; and / or Maintain a local data structure version repository, and deduce the structure before and after the change based on the historical records of the version repository and the current data structure changes.

4. The method according to claim 1, characterized in that, Step S4 includes: Based on the change actions, first structure snapshot, second structure snapshot, and downstream dependency information corresponding to the data structure change object extracted from the abstract syntax tree, a structural difference description result is generated. The structural difference description result includes at least object-level change type, field-level change type, field attribute change item, constraint change item, and the identifier of the affected downstream object. The rule engine is invoked to match the structural difference description results according to preset judgment rules, and output the compatibility status, risk level, scope of impact and handling suggestions.

5. The method according to claim 4, characterized in that, The compatibility rule base mentioned in step S4 includes at least the following rules: Adding nullable columns or columns with default values ​​is considered low-risk and is generally compatible. Deleting a column is considered high-risk; if the column is referenced by a downstream task, it will be incompatible. Renaming columns is considered high-risk and is generally incompatible. The column type has been broadened and is classified as medium risk; it is generally compatible. The column type is narrowed, which is deemed high-risk and incompatible. The newly added non-empty constraint is classified as high-risk and is incompatible with the write link. The table renaming was deemed extremely high-risk and incompatible.

6. The method according to claim 1, characterized in that, Step S5 includes: If the risk level is low, the data development group will be notified via WeChat group or email. If the risk level is medium, the data development group and the responsible persons involved in the data structure change will be notified via WeChat group or email. If the risk level is high, the data development group, the person in charge of the data structure change, and the on-duty personnel will be notified via WeChat group and email, and a work order will be automatically created in the work order system. If the risk level is extremely high, in addition to issuing a high-risk notification, the action of automatically suspending the relevant data synchronization task will be triggered.

7. The method according to claim 1, characterized in that, Step S6 includes: If the data structure is changed to include new columns, the add column statement for the target data warehouse will be automatically generated and executed; If the data structure is changed to delete a column, the target synchronization task instance that has a dependency relationship with that field will be automatically paused, subsequent data writing will be blocked, and manual confirmation will be required. If the data structure is changed to rename columns, a compatible view is automatically generated, and the old field aliases are maintained in the view; If the data structure is changed to modify the column type, a compatibility check of the downstream table structure will be automatically performed, and a corresponding conversion or adjustment script will be generated for approval.

8. The method according to claim 1, characterized in that, Before step S6, the following is also included: The processing module verifies the data structure change support capability of the target data storage system, execution window constraints, and target table status. If the preset execution conditions are met, the processing action is automatically executed; otherwise, a processing task pending approval is generated.

9. The method according to claim 1, characterized in that, The method further includes: Recording steps: Record the detailed information of this data structure change in the change information table. The detailed information includes the original data structure change statement, abstract syntax tree, snapshot of the structure before and after the change, compatibility judgment result, risk level, notification record, handling action and its result, for subsequent auditing, tracking and analysis.

10. A device for sensing, determining, and handling data structure changes, characterized in that, The steps of the method for sensing, determining, and handling data structure changes as described in any one of claims 1-9 when the device is in operation include: The acquisition module is used to obtain log change records of the monitored data source based on the change data capture tool and identify data structure change events in them; The parsing module is used to perform semantic parsing on the structured query language statements in the data structure change event, generate an abstract syntax tree, and identify the data structure change operation type and specific change content. The structure snapshot module is used to obtain a first structure snapshot before the data structure change operation occurs and a second structure snapshot after the data structure change operation occurs, based on the abstract syntax tree and the changed data capture context information. The compatibility determination module is used to determine the compatibility of the data structure change with downstream data consumers based on the first structure snapshot, the second structure snapshot and the preset compatibility rule base, and generate a compatibility determination result. The compatibility determination result includes at least the compatibility status and risk level. The notification module is used to send data structure change information to a preset recipient by adopting different levels of notification strategies based on the risk level in the compatibility determination result. The handling module is used to automatically trigger or schedule corresponding handling actions based on the compatibility status and risk level in the compatibility determination result.

Images