Distributed database SQL translation adaptive optimization method and system

Abstract

The invention relates to the technical field of database migration, and discloses a distributed database SQL translation adaptive optimization method and system. The method is used for solving the problems that in a traditional method, target library metadata and distributed cost perception are lacked, and semantic deviation, rule conflicts and serious performance degradation are likely to be caused. The method comprises the steps that firstly, a target library is connected, a table structure, a fragmentation or partition key, an index main key and statistical information are collected, and a versioned metadata snapshot is generated; normalizing the source library SQL, analyzing the source library SQL into a unified grammar structure, normalizing dialect differences and marking special elements; equivalence translation and recursive structure rearrangement are completed according to a target library rule, original texts of non-equivalence fragments are reserved, and manual entrances are registered; and based on metadata snapshots, assessing risks of cross-fragmentation, push-down and the like, triggering layered rewriting, conflict resolution and version trace leaving, outputting a target SQL and supporting audit backtracking.

Description

Technical Field

[0001] This invention relates to the field of database migration technology, specifically to a distributed database SQL translation adaptive optimization method and system. Background Technology

[0002] Driven by national demands for information technology innovation and independent control, core business systems in sectors such as finance and telecommunications are gradually migrating from foreign centralized commercial databases to domestically developed distributed databases. During the migration process, existing SQL statements and stored procedure code on the application side need to be adapted to the target database's syntax and execution mechanism. SQL adaptation and modification typically become one of the key aspects of the migration cycle and migration risks. In existing technologies, SQL adaptation typically employs the following methods: First, manual translation and optimization, where developers and database administrators meticulously understand the source database's SQL and its business semantics, then rewrite it according to the syntax and performance characteristics of the target distributed database. Second, automated scripts based on text replacement, using regular expressions or keyword mapping tables for batch replacement to achieve preliminary conversion of some syntactic elements. Third, basic syntax-level translation tools, which parse the basic syntactic structure of SQL to achieve a certain degree of automated translation, capable of covering some simple DML and DDL dialect conversion needs. However, the above methods still have significant shortcomings when facing large-scale, high-concurrency, and performance-constrained distributed database migration scenarios. First, existing tools mostly adopt an offline translation approach, lacking the ability to perceive the target database's metadata. They struggle to perform constraint checks based on the target database's table structure, sharding or partitioning information, index primary keys, and data distribution, easily leading to issues such as missed shard key filtering, triggering full table scans, or excessive overhead from cross-shard data interaction. This results in translations that are executable but with significantly degraded performance. Second, there is insufficient coverage of complex syntax and a lack of controllable rollback mechanisms. For advanced features such as hierarchical queries, recursive queries, and complex functions, direct errors, translation errors, or semantic inconsistencies often occur, making it difficult to establish a stable and usable batch processing pipeline in engineered migrations. Third, the execution costs and risks in a distributed environment are more complex. Existing methods generally lack distributed cost assessment capabilities, making it difficult to promptly identify performance risks such as large table broadcasting, data skew, cross-shard joins, and aggregation sorting. This often leads to problems surfacing after deployment, resulting in high costs for investigation and rework. Furthermore, when multiple rules, such as syntax transformation and performance optimization, are introduced simultaneously, governance issues such as rule conflicts, unclear rewriting order, and cyclic triggering can easily arise, thus affecting the consistency and controllability of batch translation results. Finally, in the process of batch migration and automatic rewriting, existing solutions are relatively weak in sensitive information processing, rewriting process logging, rule hit recording, and quantitative evaluation of effects, making it difficult to meet the security, compliance, and traceability requirements of the migration process. Therefore, the industry urgently needs a SQL adaptation technology that can balance semantic correctness, performance controllability, and engineering traceability in heterogeneous database migration, in order to reduce the cost of manual rewriting, reduce migration blockage caused by failure of complex syntax processing, and reduce performance regression and risk spillover in distributed execution environments. Summary of the Invention

[0003] To address the shortcomings of existing technologies, this invention provides a distributed database SQL translation adaptive optimization method and system, which solves the problems of lack of target database metadata and distributed cost awareness in traditional methods, which easily lead to semantic bias, rule conflicts and serious performance degradation.

[0004] To ensure semantic consistency and reduce performance risks such as cross-sharding issues, this invention provides the following technical solution: An adaptive optimization method for distributed database SQL translation includes: S1: Establish a connection with the target distributed database, obtain and register table structure information, shard key or partition key information, index and primary key information, statistical information and node information, and generate metadata snapshot; S2: Parse the source database SQL to generate a unified grammatical structure representation, normalize grammatical elements according to dialect differences, and set extended tags for proprietary grammatical elements that cannot be normalized. S3: Based on the preset translation rules, perform equivalent translation on the unified grammatical structure representation, perform structural reconstruction on the grammatical structure involving recursive queries, and retain the corresponding statement fragment and mark the manual processing entry when the equivalence constraint is not met. S4: Based on metadata snapshots, conduct distributed execution risk assessments on the translated syntax structure representation to determine shard key filtering, cross-shard association, aggregation pushdown, and table size risk factors. S5: Trigger adaptive rewriting rules based on risk assessment conclusions, execute rule priority decisions and rewriting order control, complete rewriting processing in rounds, and end processing when termination conditions are met.

[0005] In a preferred embodiment, a connection is established with the target distributed database, table structure information, shard key or partition key information, index and primary key information, statistical information, and node information are obtained and registered, and a metadata snapshot is generated, including: Read the connection and execution timeout according to the configuration, perform connection authentication and initialize session parameters, and generate a connection context; Collect node information and schema permission boundaries, register table fields, sharding partition keys, index primary keys and statistical information, and generate object mapping and version snapshots; Incremental updates are triggered based on DDL audit logs or timed differences, and snapshots are available and written to the context during abnormal switching.

[0006] In a preferred embodiment, the source database SQL is parsed to generate a unified grammatical structure representation. Grammatical elements are normalized based on dialect differences, and extended tags are set for proprietary grammatical elements that cannot be normalized, including: Receive source database SQL and type, and read target database type and snapshot version; Normalize the SQL and segment the statement units, and establish a unified mapping and positioning of constant placeholders and parameters; Load and parse configurations based on source library type to generate a unified syntax structure and register alias ranges; The process involves dialect unification, setting extended tags for proprietary elements and performing incompatibility checks, and then writing the data into storage and context.

[0007] In a preferred embodiment, based on preset translation rules, a unified grammatical structure representation is equivalently translated; for grammatical structures involving recursive queries, structural reconstruction is performed; and when equivalence constraints are not met, the corresponding statement fragment is retained and marked with a manual processing entry point, including: Read the unified syntax structure, extended tags, and positioning information, and associate them with snapshot versions; Load the target library's translation rules, naming rules, and compatibility list; Translate and validate object references and alias ranges according to statement units based on priority, and rearrange the recursive structure; If there is a discrepancy, retain the original fragment, register the manual entry and context binding, and write the intermediate result.

[0008] In a preferred embodiment, a distributed execution risk assessment is performed on the translated syntax structure representation based on metadata snapshots to determine risk factors such as shard key filtering, cross-shard joins, aggregation pushdown, and table size, including: Read the snapshot version, translation structure, and entry point location; Verify schema table fields and nullable primary key constraints by object mapping and alias range, and correct or register caliber risks; Risks are registered based on the segment key overwrite, connection clipping alignment, pushdown constraints, and row number cardinality hierarchical registration. Entry fragments are registered and awaiting processing; risk records are written and stored in the context.

[0009] In a preferred embodiment, an adaptive rewriting rule is triggered based on the risk assessment conclusion, rule priority adjudication and rewriting order control are executed, the rewriting process is completed in rounds, and the process ends when the termination condition is met, including: Read risk factors and locations to be addressed, and load the rule base, capability list, and control configuration version; Rewrite statements in rounds according to their categories and priority, verifying preconditions and checking for support prompts; Generate version track and perform consistency checks; skip or roll back in case of conflicts, and transfer to pending handling if necessary. The process terminates when the stability condition is met, there are no available rules, or there are critical nodes to be addressed, and the data is written to storage and context.

[0010] On the other hand, a distributed database SQL translation adaptive optimization system includes: Connection and Session Management Module: Receives the target library connection configuration, completes connection authentication and session initialization, maintains reusable connection contexts, and provides a unified access interface for metadata collection and verification; Metadata management module: Collects and stores target database cluster, schema permissions, table fields, sharding partition keys, index primary keys and statistical information, generates versioned metadata snapshots, supports scheduled and change updates, and switches to a usable snapshot in case of an anomaly; SQL preprocessing and parsing normalization module: normalizes source database SQL and parses it into a unified syntax structure, normalizes mappable elements, and generates extended tags and location information for proprietary elements; Translation and Restructuring Module: Loads target library rules and naming conventions, translates and rearranges the syntax structure, and restructures recursive structures; non-equivalent fragments retain the original text and register manual entries; Distributed execution risk assessment module: Based on metadata snapshot verification of object consistency, it identifies risks such as shard filtering, cross-shard association, pushdown limitation and table size, and generates risk elements and elements to be handled for rewrite triggering; Adaptive rewriting and conflict resolution module: Rewrite rules according to risk factors, record rounds and verify consistency, skip, roll back or transfer to manual entry if there is a conflict, and solidify the structure and trajectory after termination. Target SQL generation and dialect adaptation module: Renders the final syntax structure into target library dialect SQL, adapts the prompts and execution strategies according to the capability list, and outputs executable SQL, metadata and rule version information; Security audit and evaluation module: De-identifies and tracks constants and sensitive information, records SQL version chains and rule logs, summarizes failure samples and manual entry points, and supports evaluation backtracking.

[0011] Compared with existing technologies, this invention provides a distributed database SQL translation adaptive optimization method and system, which has the following beneficial effects: 1. This invention unifies object reference standards through a target database metadata snapshot and refresh mechanism, parses source database SQL into a unified syntax structure to eliminate dialect differences, and sets extended tags for proprietary elements that cannot be unified; then, it combines translation rules and structural reconstruction to handle complex syntaxes such as recursion, retains the original text of non-equivalent fragments and registers manual entry points; subsequently, it performs distributed execution risk assessment based on metadata, triggers layered rewriting and conflict resolution, and records version trajectory and audit information for backtracking analysis, ultimately achieving adaptive optimization of SQL translation for distributed database migration.

[0012] 2. This invention organizes parsing, translation, risk assessment, and rewriting into statement-level units, and maintains location information, extended tags, and manual processing entry points at each stage. This ensures that proprietary syntax, missing objects, or incompatible syntax can be accurately attributed to specific segments and their context nodes, preventing script interruptions due to partial incompatibility. Simultaneously, it adjudicates according to rule categories and priority, combining precondition verification, consistency checks, and conflict resolution to control overlay conflicts and cyclic triggering when multiple rules are superimposed. Furthermore, it retains the location and contextual association of the elements to be processed when canceling a decision, facilitating subsequent centralized revisions and backfilling. Ultimately, this supports the operability, maintainability, and continuous iteration of the migration process. Attached Figure Description

[0013] Figure 1 This is a schematic diagram of the process of a distributed database SQL translation adaptive optimization method according to the present invention; Figure 2 This is a schematic diagram of the distributed database SQL translation adaptive optimization system structure of the present invention. Detailed Implementation

[0014] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0015] Example 1: Figure 1 An adaptive optimization method for distributed database SQL translation is presented, including: S1: Establish a connection with the target distributed database, obtain and register table structure information, shard key or partition key information, index and primary key information, statistical information and node information, and generate metadata snapshot; S2: Parse the source database SQL to generate a unified grammatical structure representation, normalize grammatical elements according to dialect differences, and set extended tags for proprietary grammatical elements that cannot be normalized. S3: Based on the preset translation rules, perform equivalent translation on the unified grammatical structure representation, perform structural reconstruction on the grammatical structure involving recursive queries, and retain the corresponding statement fragment and mark the manual processing entry when the equivalence constraint is not met. S4: Based on metadata snapshots, conduct distributed execution risk assessments on the translated syntax structure representation to determine shard key filtering, cross-shard association, aggregation pushdown, and table size risk factors. S5: Trigger adaptive rewriting rules based on risk assessment conclusions, execute rule priority decisions and rewriting order control, complete rewriting processing in rounds, and end processing when termination conditions are met.

[0016] S1: Establish a connection with the target distributed database, obtain and register table structure information, shard key or partition key information, index and primary key information, statistical information, and node information, and generate a metadata snapshot. The specific implementation is as follows: The system receives connection configuration parameters from the target distributed database. These parameters specify the database type, cluster entry address, authentication credentials, target database identifier, connection timeout constraints, and statement execution timeout constraints. The timeout constraints are configured and saved by the operations and maintenance personnel in the configuration management module according to the target database type. The system reads the corresponding configuration as the session control standard before initiating the connection. The system first performs a connectivity probe. After successful connection, a database session is established and authentication is completed. After successful authentication, session-level parameters are loaded, and session attributes such as character set, time zone, transaction isolation level, and statement execution timeout are initialized. A connection context identifier is generated for reuse of the same session in the subsequent metadata reading phase. Subsequently, the system performs metadata collection on the target database based on the pre-set object list, forming a metadata snapshot; the system reads cluster-level information, recording node identifiers, node roles, node availability status, and access entry information, and writes the collection time identifier; the system reads database-level information, obtaining the schema list and permission scope, and generating object access boundary identifiers; the system reads table-level information, recording table name, field name, field type, nullable constraint, default value, and foreign key constraint information for each table; for tables with sharding or partitioning configurations, it records the sharding key or partition key definition and corresponding rule scope; for indexes and primary keys, it records the index name, index column order, uniqueness identifier, index type, and primary key column set; for statistical information, it records the table row count scope, field cardinality scope, statistical source identifier, and statistical update time scope; the system constructs fully qualified names using the database identifier, schema, table name, and field name, and generates internal identifiers accordingly, establishing a mapping relationship between object names and internal identifiers, and associating this mapping relationship with object access boundary identifiers and metadata entries for subsequent referencing and positioning according to a unified object standard; The system writes the collected metadata entries into the metadata snapshot storage area, generates a version number and generation time identifier for the snapshot, and records the target database type identifier. The system is configured with a snapshot update mechanism that supports change-triggered updates and scheduled updates. When a change-triggered update occurs, the system obtains object definition change information through the database change record interface or DDL audit log, generates a change event, and extracts a list of affected objects. The system performs incremental collection based on the list of affected objects, updates the corresponding entries in the snapshot, and synchronously updates the snapshot version number. When a scheduled update occurs, the system performs a comparison check on the statistical update time caliber and statistical source identifier recorded in the snapshot, generates a difference list, and updates the statistical information entries of the corresponding objects accordingly, while also updating the snapshot version number. When a connection failure or metadata reading failure occurs, the system records the reason for the failure and switches to the most recent available snapshot version to continue providing metadata reference caliber to subsequent processes. After snapshot generation or switching is completed, the system writes the snapshot version number, object access boundary identifier, mapping relationship between object name and internal identifier, and metadata related to sharding or partitioning into the process context.

[0017] S2: Parse the source database SQL to generate a unified grammatical structure representation. Normalize grammatical elements based on dialect differences, and set extension tags for proprietary grammatical elements that cannot be normalized. Specifically, the implementation is as follows: The system receives the source database SQL text and source database type identifier from the task to be migrated, and reads the target database type identifier and metadata snapshot version number from the process context as the parsing criteria for this stage and the basis for subsequent translation alignment. The system preprocesses and normalizes the source database SQL text, sequentially completing whitespace merging, comment removal, keyword case unification, string and numeric constant placeholder replacement, and unified merging of named parameters and positional parameters. The system establishes a correspondence table between placeholders and original constants, and associates and saves this correspondence table with the original position index of the SQL text. When the source database SQL text contains multiple statements, the system segments it according to the statement terminator and statement block boundaries to obtain a set of statement-level units, and generates a sequence number and original position index for each statement-level unit. The system loads and parses configurations from the configuration management module based on the source library type identifier. The parsed configurations are then used to create configuration entries based on the source library type and managed in a versioned manner. Each configuration entry defines the identification rules for keywords, functions and operators, join syntax, pagination syntax, and hierarchical query syntax. The system performs syntax parsing on each statement-level unit, generating a unified syntax structure representation. This representation records the statement type, output column list, data source, join relationships, filtering conditions, grouping, sorting, and subquery hierarchy relationships. When alias references occur, the system establishes a binding relationship between the alias and the entity object and records the binding scope for field attribution recovery and object reference verification. The system loads a normalization rule set based on the source library type and performs mapping transformations on normalizable grammatical elements at the unified grammatical structure level. The system converts implicit join expressions into explicit join structures and separates join conditions from filter conditions. The system converts equivalent function forms and equivalent expression forms into unified node forms while maintaining consistent parameter order and scope. The system expands abbreviated syntax in the source library and converts it into standard node combinations that can be expressed by the unified grammatical structure. For proprietary grammatical elements that cannot be normalized, the system sets extension tags in the grammatical structure representation. Extension tags are associated with statement-level unit numbers, original position indices, scope ranges, dependent objects and field sets, and references to adjacent structural nodes. The system performs incompatibility checks based on the target library scope in the process context and the object access boundaries in the metadata snapshot: if the object or field that the extension tag depends on is not within the object access boundary, or if the object cannot be determined through object mapping relationships, an object scope incompatibility type identifier is registered; if the grammatical element corresponding to the extension tag is not within the target library's grammatical compatibility list, a grammatical constraint incompatibility type identifier is registered. After completing the syntax parsing and normalization process, the system writes the unified syntax structure representation, extended tag list, placeholder correspondence table, and parsing configuration version information into the task intermediate result storage, and registers the output identifier of this stage in the process context.

[0018] S3: Based on preset translation rules, perform equivalent translation on the unified grammatical structure representation, perform structural reconstruction on the grammatical structure involving recursive queries, and retain the corresponding statement fragment and mark the manual processing entry point when the equivalence constraint is not met. The specific implementation is as follows: The system reads the unified syntax structure representation, extended tag list, and location information from the intermediate result storage of the task, and reads the target library type identifier and metadata snapshot version number from the process context as the basis for translation standards and object constraints. In the configuration management module, the system loads the translation rule set, naming normalization rules, and syntax compatibility list according to the target library type, and records the corresponding configuration version number for traceability. The translation rule set includes dialect mapping rules and structural rearrangement rules, and each rule sets the applicable conditions, priority, and scope of effectiveness. The naming normalization rules explicitly retain keyword avoidance methods, identifier case rules, schema prefix rules, and field type compatibility standards. The system processes data at the statement level, performing equivalent translation on unified syntactic structures. It applies translation rules according to applicable conditions and priorities, performing node replacement, attribute adjustment, and reordering on mappable nodes to ensure they meet the target library's syntactic constraints. The system performs object alignment checks on nodes involving object references. Object access boundaries are defined by the accessible schema list, object mapping relationships, and permission scope in the metadata snapshot. The system checks whether table names, field names, and schema names are resolvable based on object access boundaries and standardizes identifier representation according to naming conventions. After restoring field attribution by combining alias binding ranges, the system performs object existence checks. If an object's reference is uncertain, a field is missing, there are identical names, a corresponding mapping rule does not exist in the rule set, or the translated node fails the target library's syntactic check, the system registers pending processing information and triggers a rollback registration process. The pending processing information is saved in association with the location information. The system performs structural reconstruction on the recursive query-related syntax structure; the system locates the recursive anchor point, recursive item, and recursive termination condition in the unified syntax structure, and records the reference relationship and scope boundary between the three; the system rearranges the recursive structure into a recursive expression structure supported by the target library, and simultaneously adjusts the connection relationship nodes and filter condition nodes in the recursive item to make them consistent with the scope boundary after rearrangement, while maintaining the consistency of the column order, column alias, and hierarchical reference relationship in the output column list; when the recursive structure or its associated nodes do not meet the syntax compatibility list constraints, or when the object references involved in the recursive structure cannot be resolved, the system registers pending processing information and triggers the rollback registration process; When the system executes the rollback registration process, it extracts the corresponding original statement fragments based on the location information, attaches these fragments to placeholder nodes with a unified syntax structure, and establishes an association between the fragments and context structure nodes. The system registers manual processing entry information, which includes location information, associated object identifiers, trigger reason categories, dependent extension tag numbers, and context binding information. Trigger reason categories cover object scope issues, target library syntax incompatibility, and incompatibility with recursive structures. The system establishes context binding relationships between the manual processing entry points and adjacent data source nodes or connection relationship nodes, enabling context-based judgment and attribution during the next stage of distributed execution risk assessment. After processing, the system generates a translated syntax structure representation for each statement-level unit and writes a version identifier. The version identifier is associated with the target library type identifier, the metadata snapshot version number, and the translation rule set version number. The system compiles these into a manual processing entry list and writes the translated syntax structure representation, the manual processing entry list, and their location association information into the task intermediate result storage. At the same time, it registers the output identifier of this stage in the process context for the next stage to conduct distributed execution risk assessment and risk factor collection based on the metadata snapshot.

[0019] S4: Based on metadata snapshots, perform distributed execution risk assessment on the translated syntax structure representation to determine risk factors such as shard key filtering, cross-shard joins, aggregation pushdown, and table size. The specific implementation is as follows: The system reads metadata snapshots and their version numbers from the intermediate results storage of the task, and simultaneously reads the translated syntax structure representation, statement-level unit sequence numbers, and manual processing entry lists. It then establishes a processing identifier for this stage within the workflow context as a unified processing context. The system performs object alignment checks, verifying the existence of the schemas, tables, and fields referenced in the syntax structure based on the object mapping relationships in the metadata snapshot. When alias-bound field references are found, the system first restores the field's ownership according to the binding scope before performing object verification. The system performs consistency verification on structural constraints such as field types, nullable constraints, and primary key constraints according to the entries registered in the metadata snapshot. If inconsistencies are found, the system prioritizes correcting the references based on the object mapping relationships and synchronously updates the object identifiers in the syntax structure. If the object mapping relationship does not exist, or the mapped object is not within the object access boundary, the system registers object-related risk items and associates and saves these risk items with the statement-level unit sequence number and node location information. After object alignment is completed, the system conducts structured risk assessment around key elements of distributed execution, and forms risk elements by combining the sharding or partitioning criteria, table row count criteria, and field cardinality criteria in the metadata snapshot; the system categorizes filtering conditions according to the data source node and subquery level they affect, extracts the limiting conditions for the sharding key or partitioning key from the filtering conditions, determines the pruning range based on the limiting conditions, and binds and saves the pruning range with the corresponding data source node; when the filtering conditions do not cover the sharding key or partitioning key criteria, the system registers the insufficient sharding key filtering elements; the system identifies the table objects and join key column sets referenced on both sides of the join relationship, and determines whether the join may lead to cross-shard data interaction by combining the sharding key or partitioning key criteria of the two tables and the pruning range; if the join key does not cover the sharding key or partitioning key, or if the two tables do not cover the sharding key or partitioning key, or if the two tables do not cover the sharding key or partitioning key, the system registers the insufficient sharding key filtering elements. When the trimming range cannot be aligned, the system registers cross-shard association risk items; the system verifies the attribution relationship between aggregation grouping fields, sorting fields and data source nodes, and loads the target library capability list from the configuration management module according to the target library type, and determines whether the aggregation or sorting meets the pushdown conditions based on the pushdown constraint caliber in the target library capability list; when the aggregation or sorting depends on cross-shard interaction, depends on multiple data sources, or does not meet the pushdown constraints, the system registers pushdown restricted risk items; the system loads the scale level classification rules from the configuration management module, and the scale level classification rules establish configuration entries according to the target library type or business scenario; the system maps the table objects involved in the syntax structure to scale levels based on the table row count caliber and field cardinality caliber in the metadata snapshot; when large tables participate in joins, aggregations or sorting, the system registers table scale risk items; The internal structure of the statement fragments corresponding to the entry list is not expanded when manually processing them; the system registers the elements to be processed according to the associated objects and location information in the entry information, and establishes a reference relationship between the elements to be processed and the adjacent syntactic structure nodes, so that the subsequent rewriting stage can be processed according to the same location relationship; The system summarizes the judgment results into a risk element record set, and writes a statement-level unit number, risk type identifier, associated object identifier, node location information, metadata snapshot version number, and trigger condition summary for each record. When multiple types of risks are hit in the same statement-level unit, the system generates records separately and establishes intra-statement association identifiers. The system writes the risk element record set and the list of elements to be dealt with into the task intermediate result storage, and registers the output identifier of this stage in the process context for the next stage to perform adaptive rewriting rule selection, rule priority adjudication, and rewriting order control.

[0020] S5: Based on the risk assessment conclusion, trigger the adaptive rewriting rule, execute rule priority adjudication and rewriting order control, complete the rewriting process in rounds, and end the process when the termination condition is met. The specific implementation is as follows: The system reads the risk element record set, the list of elements to be handled, and their location information from the intermediate result storage of the task. It also reads the target library type identifier, metadata snapshot version number, and preceding syntax structure version identifier from the process context to establish a rewrite processing context. The system loads the rewrite rule library, target library capability list, and rewrite control caliber configuration from the configuration management module according to the target library type, and records the currently effective version number. The rewrite control caliber configuration is maintained with versioned configuration entries. Each configuration entry defines the rule category and processing order, priority caliber of the same category, stability judgment parameters, conflict resolution strategies, and stop rewrite trigger conditions. Configuration entries are preset and saved by operations or administrators according to the target library type or business scenario. The system executes the control for this stage according to the currently effective configuration. The system filters available rewrite rules according to the risk element type and trigger conditions, categorizes the rules into syntax constraint type, structure adjustment type, and execution path adjustment type, and binds the scope of application, precondition description, and conflict relationship identifier to each rule for subsequent adjudication and conflict handling. The system executes rule adjudication and sequence control at the statement level. When multiple rules are matched within the same statement level, they are processed sequentially according to rule category: syntax constraint rules are applied first, followed by structure adjustment rules, and finally execution path adjustment rules. Within the same category, rules are applied in descending order of priority from the rule base. Before each rule application, the system checks whether the preconditions are met. Preconditions cover the existence of the target node, node type consistency, object reference resolvability, consistency between risk element records and node location information, and whether the element to be processed covers the target node. If the preconditions are not met, the system skips the rule and continues to adjudicate the next rule. Before applying execution path adjustment rules, the system checks whether the target library supports the corresponding prompt node or execution strategy node based on the target library capability list. If not, the rule is skipped and the reason for skipping is recorded. After the rule application is completed, the system synchronously updates the attachment relationship of the modified node and its adjacent nodes to ensure that the reference relationship between the filtering conditions, join conditions, and subquery levels remains consistent, and that the object reference and alias binding relationship remains resolvable. After each round of rewriting, the system generates a new syntax structure version identifier, which is associated with the statement-level unit number, the rewriting rule base version number, the target library type identifier, and the metadata snapshot version number. The system registers the rule identifiers hit in this round, the location information of the rewritten nodes, and a summary of the differences before and after the rewriting in the version track. The system performs consistency checks on the rewritten nodes, covering the consistency of object references and metadata snapshots, the validity of alias binding relationships, whether there are duplicate attachments or mismatches in filter conditions and join conditions, whether subquery level references are continuous, and whether the rewriting introduces new elements to be disposed of. When rules are detected to overlap or to trigger in a loop, the system performs processing according to the conflict resolution strategy. Conflict resolution supports skipping low-priority rules, reverting to the previous stable version and re-evaluating, and registering the relevant nodes as elements to be disposed of and adding them to the list of elements to be disposed of. The system performs cancellation judgments based on the rewrite control caliber configuration and terminates processing when termination conditions are met. Termination conditions include risk element records entering a stable state, the current statement-level unit no longer hitting available rules, and the element to be disposed of still being associated with key nodes. Key nodes include data source nodes, connection relationship nodes, filter condition nodes, aggregation nodes, and sorting nodes. A risk element record is determined to enter a stable state when it remains unchanged within the stability judgment range defined by the stability judgment parameters, or when the risk element record no longer triggers any available rules. When the element to be disposed of is still associated with key nodes, the system stops automatically rewriting the corresponding segment, retains only the location information, and maintains the segment as the element to be disposed of. After the termination conditions are met, the system freezes the final syntax structure representation and version trajectory, and writes the final syntax structure representation, version trajectory, and list of elements to be disposed of into the task intermediate result storage. At the same time, it registers the output identifier of this stage in the process context, providing subsequent target SQL generation and auditing trace stages with rendering according to the target library dialect, and hitting the log and rewrite link according to the version trajectory archiving rules.

[0021] In this embodiment, the system accesses the target distributed database and completes session initialization. It collects cluster, schema, table fields, shard or partition keys, indexes and primary keys, and statistical information to generate a versioned metadata snapshot. After receiving the source database SQL, it first performs normalization processing, then parses it into a unified syntax structure according to the source database dialect, normalizes the mappable syntax elements, and sets extended tags for proprietary elements. Based on the target database translation rules and naming conventions, it performs equivalent translation of the unified syntax structure and rearranges the recursive query structure. When encountering fragments that cannot be processed equivalently, it retains the original fragments and registers the manual processing entry. Based on the metadata snapshot, it performs shard filtering, cross-shard association, pushdown conditions, and scale risk assessment on the translated structure to form risk elements and elements to be dealt with. Based on the risk elements, it triggers rewriting rules and performs hierarchical adjudication and version tracking. When the termination conditions such as risk convergence or no longer hitting available rules are met, the final structure is solidified for subsequent SQL generation and audit calls.

[0022] Example 2: Figure 2 A distributed database SQL translation adaptive optimization system is presented, including: Connection and Session Management Module: Receives the target library connection configuration, completes connection authentication and session initialization, maintains reusable connection contexts, and provides a unified access interface for metadata collection and verification; Metadata management module: Collects and stores target database cluster, schema permissions, table fields, sharding partition keys, index primary keys and statistical information, generates versioned metadata snapshots, supports scheduled and change updates, and switches to a usable snapshot in case of an anomaly; SQL preprocessing and parsing normalization module: normalizes source database SQL and parses it into a unified syntax structure, normalizes mappable elements, and generates extended tags and location information for proprietary elements; Translation and Restructuring Module: Loads target library rules and naming conventions, translates and rearranges the syntax structure, and restructures recursive structures; non-equivalent fragments retain the original text and register manual entries; Distributed execution risk assessment module: Based on metadata snapshot verification of object consistency, it identifies risks such as shard filtering, cross-shard association, pushdown limitation and table size, and generates risk elements and elements to be handled for rewrite triggering; Adaptive rewriting and conflict resolution module: Rewrite rules according to risk factors, record rounds and verify consistency, skip, roll back or transfer to manual entry if there is a conflict, and solidify the structure and trajectory after termination. Target SQL generation and dialect adaptation module: Renders the final syntax structure into target library dialect SQL, adapts the prompts and execution strategies according to the capability list, and outputs executable SQL, metadata and rule version information; Security audit and evaluation module: De-identifies and tracks constants and sensitive information, records SQL version chains and rule logs, summarizes failure samples and manual entry points, and supports evaluation backtracking.

[0023] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. An adaptive optimization method for SQL translation in a distributed database, characterized in that, include: S1: Establish a connection with the target distributed database, obtain and register table structure information, shard key or partition key information, index and primary key information, statistical information and node information, and generate metadata snapshot; S2: Parse the source database SQL to generate a unified grammatical structure representation, normalize grammatical elements according to dialect differences, and set extended tags for proprietary grammatical elements that cannot be normalized. S3: Based on the preset translation rules, perform equivalent translation on the unified grammatical structure representation, perform structural reconstruction on the grammatical structure involving recursive queries, and retain the corresponding statement fragment and mark the manual processing entry when the equivalence constraint is not met. S4: Based on metadata snapshots, conduct distributed execution risk assessments on the translated syntax structure representation to determine shard key filtering, cross-shard association, aggregation pushdown, and table size risk factors. S5: Trigger adaptive rewriting rules based on risk assessment conclusions, execute rule priority decisions and rewriting order control, complete rewriting processing in rounds, and end processing when termination conditions are met.

2. The distributed database SQL translation adaptive optimization method according to claim 1, characterized in that, Establish a connection to the target distributed database, obtain and register table structure information, shard key or partition key information, index and primary key information, statistical information, and node information, and generate a metadata snapshot, including: Read the connection and execution timeout according to the configuration, perform connection authentication and initialize session parameters, and generate a connection context; Collect node information and schema permission boundaries, register table fields, sharding partition keys, index primary keys and statistical information, and generate object mapping and version snapshots; Incremental updates are triggered based on DDL audit logs or timed differences, and snapshots are available and written to the context during abnormal switching.

3. The distributed database SQL translation adaptive optimization method according to claim 1, characterized in that, The source database SQL is parsed to generate a unified grammatical structure representation. Grammatical elements are normalized based on dialect differences, and extended tags are set for proprietary grammatical elements that cannot be normalized, including: Receive source database SQL and type, and read target database type and snapshot version; Normalize the SQL and segment the statement units, and establish a unified mapping and positioning of constant placeholders and parameters; Load and parse configurations based on source library type to generate a unified syntax structure and register alias ranges; The process involves dialect unification, setting extended tags for proprietary elements and determining incompatibility, and then writing the data into storage and context.

4. The distributed database SQL translation adaptive optimization method according to claim 1, characterized in that, Based on preset translation rules, equivalent translation is performed on the unified grammatical structure representation. For grammatical structures involving recursive queries, structural reconstruction is performed. When equivalence constraints are not met, the corresponding statement fragments are retained and marked with manual processing entry points, including: Read the unified syntax structure, extended tags, and positioning information, and associate them with snapshot versions; Load the target library's translation rules, naming rules, and compatibility list; Translate and validate object references and alias ranges according to statement units based on priority, and rearrange the recursive structure; If there is a discrepancy, the original fragment is retained, and the manual entry point and context binding are registered and written into the intermediate result.

5. The distributed database SQL translation adaptive optimization method according to claim 1, characterized in that, Based on metadata snapshots, a distributed execution risk assessment is performed on the translated syntax structure representation to identify risk factors related to shard key filtering, cross-shard joins, aggregation pushdown, and table size, including: Read the snapshot version, translation structure, and entry point location; Verify schema table fields and nullable primary key constraints by object mapping and alias range, and correct or register caliber risks; Risks are registered based on the segment key overwrite, connection clipping alignment, pushdown constraints, and row number cardinality hierarchical registration. Entry fragments are registered and awaiting processing; risk records are written and stored in the context.

6. The distributed database SQL translation adaptive optimization method according to claim 1, characterized in that, Based on the risk assessment conclusions, adaptive rewriting rules are triggered, rule priority decisions and rewriting order control are executed, rewriting processing is completed in rounds, and processing ends when termination conditions are met, including: Read risk factors and locations to be addressed, and load the rule base, capability list, and control configuration version; Rewrite statements in rounds according to their categories and priority, verifying preconditions and checking for support prompts; Generate version track and perform consistency checks; skip or roll back in case of conflicts, and transfer to pending handling if necessary. The process terminates when the stability condition is met, there are no available rules, or there are critical nodes awaiting resolution, and the data is written to storage and context.

7. A distributed database SQL translation adaptive optimization system, used to implement the distributed database SQL translation adaptive optimization method according to any one of claims 1-6, characterized in that, include: Connection and Session Management Module: Receives the target library connection configuration, completes connection authentication and session initialization, maintains reusable connection contexts, and provides a unified access interface for metadata collection and verification; Metadata management module: Collects and stores target database cluster, schema permissions, table fields, sharding partition keys, index primary keys and statistical information, generates versioned metadata snapshots, supports scheduled and change updates, and switches to a usable snapshot in case of an anomaly; SQL preprocessing and parsing normalization module: normalizes source database SQL and parses it into a unified syntax structure, normalizes mappable elements, and generates extended tags and location information for proprietary elements; Translation and Restructuring Module: Loads target library rules and naming conventions, translates and rearranges the syntax structure, and restructures recursive structures; non-equivalent fragments retain the original text and register manual entries; Distributed execution risk assessment module: Based on metadata snapshot verification of object consistency, it identifies risks such as shard filtering, cross-shard association, pushdown limitation and table size, and generates risk elements and elements to be handled for rewrite triggering; Adaptive rewriting and conflict resolution module: Rewrite rules according to risk factors, record rounds and verify consistency, skip, roll back or transfer to manual entry if there is a conflict, and solidify the structure and trajectory after termination. Target SQL generation and dialect adaptation module: Renders the final syntax structure into target library dialect SQL, adapts prompts and execution strategies according to the capability list, and outputs executable SQL, metadata and rule version information; Security audit and evaluation module: De-identifies and tracks constants and sensitive information, records SQL version chains and rule logs, summarizes failure samples and manual entry points, and supports evaluation backtracking.

Images