An algorithm data model dynamic mapping and structured query language conversion method and system

Abstract

The application discloses a kind of algorithm data model dynamic mapping and structured query language conversion method and system, belong to data processing technical field.The method includes: defining logical table structure according to the data requirement of algorithm service;Establish the mapping relationship between the logical table structure and the physical table structure in business database;First structured query language statement is generated based on the logical table structure by algorithm service, the first structured query language statement is parsed to identify logical table and logical field therein, according to the mapping relationship, the logical table and the logical field are replaced by corresponding physical table and physical field respectively, second structured query language statement is generated and submitted to the business database for execution.The application defines the dynamic mapping between logical data model and physical database by domain-specific language, realizes the complete decoupling of algorithm and database structure, avoids customized development and reduces implementation cost.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a method and system for dynamic mapping of algorithm data models and conversion of structured query language, which is particularly suitable for data interaction scenarios between algorithm services such as operations research optimization, scheduling algorithms, and path planning and heterogeneous business databases. Background Technology

[0002] As enterprises continue their digital transformation, more and more IT systems are introducing optimization algorithms (such as operations research, scheduling algorithms, and path planning) to improve business decision-making capabilities. Algorithms are typically deployed as independent services. Running an algorithm requires input business data and outputs optimization results. However, for different users, the business data provided to the algorithm service is stored in different database tables. The table structures and relationships between these data are uncertain, and different users have different design and implementation methods. Therefore, algorithm service products require customized development and modification during implementation by different users, resulting in a large amount of development and testing work, significantly increasing costs and uncertainty.

[0003] In real-world enterprise applications, different customers' business systems exhibit the following characteristics: different data storage methods, such as structured query language databases like MySQL, Oracle, and PostgreSQL; significant differences in table structure design, including differences in field naming conventions, partitioning methods, and normalization designs; and inconsistent relationships between tables, including differences in primary and foreign key relationships and association methods.

[0004] The existing technical solutions mainly adopt the following methods.

[0005] Customized Structured Query Language (SCL) Development Model: This model involves developing separate SCLs for both input and output data for each client, with the algorithm service directly dependent on the specific database table structure. This model requires writing customized SCL statements for each client, resulting in a significant development workload.

[0006] Data middleware layer mode: Business data is extracted into a unified middleware table, and the algorithm runs based on the unified table structure. Although this mode achieves decoupling between the algorithm and the business database, it introduces latency in data extraction and causes data redundancy.

[0007] Object-Relational Mapping (ORM) or Data Access Object Abstraction Pattern: This pattern uses an ORM framework (such as Hibernate or MyBatis) to abstract data access, adapting code to different database structures. However, this pattern still requires writing adaptation code for different database structures and cannot achieve complete configurability.

[0008] The aforementioned existing technical solutions all have the following drawbacks: high development costs, each customer needs to customize a structured query language or code, resulting in serious duplication of development; complex maintenance, changes in table structure require synchronous code modification, making unified management difficult; poor scalability, new algorithm integration requires re-adaptation of data structure; and lack of standardized modeling capabilities, the algorithm cannot abstract a unified data logic model. Summary of the Invention

[0009] This invention aims to solve the following technical problems: how to decouple algorithm data structures from business data structures; how to avoid developing customized structured query languages; how to achieve unified algorithm service capabilities across clients; how to provide a flexible and configurable data mapping mechanism; and how to achieve dynamic structured query language conversion while ensuring performance.

[0010] To achieve the above-mentioned objectives, the present invention provides the following technical solutions.

[0011] A method for dynamic mapping of algorithmic data models and conversion to structured query language includes logical table structure definition steps, mapping configuration steps, structured query language conversion steps, and execution steps.

[0012] Logical table structure definition steps: Define the logical table structure according to the data requirements of the algorithm service. The logical table structure includes the logical table name, logical field names, and field types. This logical table structure is independent of the physical table structure of the business database. The algorithm service can define the logical table structure and table relationships according to the data format and requirements of the algorithm. The logical table structure is defined using a domain-specific language configuration file. The structure of the logical table structure definition file (algo_schema.yaml) is as follows:

[0013] logical_tables:

[0014] name: order

[0015] Description: Order Form

[0016] primary_key:

[0017] order_id

[0018] fields:

[0019] name: order_id

[0020] type: string

[0021] nullable: false

[0022] name: demand

[0023] type: double

[0024] name: vehicle_id

[0025] type: string

[0026] name: vehicle

[0027] Description: Vehicle Table

[0028] primary_key:

[0029] vehicle_id

[0030] fields:

[0031] name: vehicle_id

[0032] type: string

[0033] name: capacity

[0034] type: double

[0035] The meanings of the fields in this logical table structure file are as follows: `logical_tables` defines a list of logical tables; `name` represents the name of the logical table; `description` represents the description of the logical table; `primary_key` represents the primary key of the logical table; `fields` represents a list of fields in the logical table; `name` in `fields` represents the field name; `type` in `fields` represents the field type; and `nullable` in `fields` indicates whether the field can be nullable. This logical table structure is independent of a specific database and only describes the minimum data structure required by the algorithm. The logical table structure is defined based on the input and output data required by the algorithm to describe the minimum dataset on which the algorithm depends for execution.

[0036] Mapping configuration steps: Establish the mapping relationship between the logical table structure and the physical table structure in the business database. The mapping relationship includes the correspondence between logical tables and physical tables, and the correspondence between logical fields and physical fields. Define the mapping relationship through the configuration interface or configuration file. The structure of the mapping configuration file (algo_mapping.yaml) is as follows:

[0037] datasource:

[0038] name: business_db

[0039] type: mysql

[0040] table_mapping:

[0041] logic_table: order

[0042] description: order table

[0043] physical_table: t_order

[0044] fields:

[0045] logic_name: order_id

[0046] physical_column: id

[0047] physical_type: string

[0048] logic_name: demand

[0049] physical_column: qty

[0050] physical_type: double

[0051] logic_name: vehicle_id

[0052] physical_column: vehicle_id

[0053] physical_type: string

[0054] logic_table: vehicle

[0055] description: vehicle table

[0056] physical_table: t_vehicle

[0057] fields:

[0058] logic_name: vehicle_id

[0059] physical_column: id

[0060] physical_type: string

[0061] logic_name: capacity

[0062] physical_column: max_capacity

[0063] physical_type: double

[0064] The meanings of the fields in this mapping configuration file are as follows: `datasource` represents the data source information; `datasource_name` represents the data source name; `datasource_type` represents the database type; `table_mapping` represents the list of table mapping relationships; `logic_table` represents the logical table name; `description` represents the description; `physical_table` represents the physical table name; `fields` represents the list of field mappings; `logic_name` represents the logical field name; `physical_column` represents the physical field name; and `physical_type` represents the physical field type. This mapping configuration supports both field mapping and table mapping. Through this configuration, the algorithm's logical table structure can be mapped to the table structure of the user's business database. The mapping configuration steps support differentiated mapping of heterogeneous table structures between different business databases, including different database types, different table naming conventions, different field naming conventions, and different table relationship designs.

[0065] The structured query language (SCL) conversion steps are as follows: First, obtain the first SCL statement generated by the algorithm service based on the logical table structure. Parse the first SCL statement to identify the logical tables and logical fields it contains. Based on the mapping relationship, replace the logical tables and logical fields in the first SCL statement with their corresponding physical tables and physical fields, respectively, generating a second SCL statement that can be executed in the business database. This method automatically converts the SCL used by the algorithm to operate on the logical table structure into a SCL that queries the table structure of the user's business database, based on the mapping relationship. The algorithm's execution of SCL operations on the logical table structure will be converted into SCL operations on the business database to obtain or update data.

[0066] For input data read operations, taking a SELECT query in Structured Query Language (SCL) as an example, the algorithm needs to join the order table and the vehicle table to query the order identifier, required quantity, vehicle identifier, and vehicle capacity information. The corresponding logical table structure in SCL is as follows:

[0067] SELECT o.order_id, o.demand, v.vehicle_number, v.capacity FROM ordero, vehicle v WHERE o.vehicle_id = v.vehicle_id AND o.demand > 10

[0068] In this logical structured query language: SELECT is a key term indicating that a query result should be returned; o.order_id represents the order identifier field from the order table; o.demand represents the quantity demanded field from the order table; v.vehicle_number represents the license plate number field from the vehicle table; v.capacity represents the capacity field from the vehicle table; FROM is a key term indicating the tables to be joined; order o represents querying the order table, where o is an alias for the order table; vehicle v represents querying the vehicle table, where v is an alias for the vehicle table; WHERE is a key term indicating the query conditions; o.vehicle_id = v.vehicle_id means the vehicle identifier in the order table is equal to the vehicle identifier in the vehicle table; AND o.demand > 10 means the quantity demanded in the order table is greater than 10. The parsing process includes constructing a logical relational algebra tree (RelNode) and identifying logical tables and logical fields.

[0069] Based on the mapping relationship, the logical structured query language is converted into a business structured query language. The physical table structure is as follows: the order table t_order contains id, qty, and vehicle_id fields; the vehicle table t_vehicle contains id, number, and capacity fields. The converted structured query language is as follows:

[0070] SELECT o.id, o.qty, v.number, v.capacity FROM t_order o, t_vehicle vWHERE o.vehicle_id = v.id AND o.qty > 10

[0071] In the converted structured query language: SELECT is a structured query language keyword; o.id represents retrieving the identification field of the order physical table; o.qty represents retrieving the required quantity field of the order physical table; v.number represents retrieving the license plate number field of the vehicle physical table; v.capacity represents retrieving the capacity field of the vehicle physical table; FROM is a structured query language keyword; t_order o represents querying the order physical table, with o as the alias; t_vehicle v represents querying the vehicle physical table, with v as the alias; WHERE is a structured query language keyword; o.vehicle_id = v.id represents that the vehicle identifier of the order physical table is equal to the identifier of the vehicle physical table; AND o.qty > 10 represents that the required quantity of the order physical table is greater than 10.

[0072] For the output data write operation, taking the INSERT statement in structured query language as an example, the algorithm generates trip information through calculation. The INSERT structured query language corresponding to the logical table structure is as follows:

[0073] INSERT INTO load(vehicle_number, start_location, end_location, start_time, end_time, total_distance, total_qty, load_rate) VALUES('Jing PXXXXX', 'Beijing Chaoyang Logistics Center', 'Beijing Haidian Shopping Center', '2025-01-09 08:12', '2025-01-09 11:50', 38500, 25, 0.9)

[0074] In this logical structured query language: vehicle_number represents the license plate number; start_location represents the departure location; end_location represents the destination; start_time represents the departure time; end_time represents the arrival time; total_distance represents the total distance; total_qty represents the total quantity; load_rate represents the loading rate. The physical table structure is Load_info, which includes the fields of vehicle_no, pick_loc, drop_loc, pick_time, drop_time, total_distance, total_weight, and full_rate. The converted structured query language is as follows:

[0075] INSERT INTO load_info(vehicle_no, pick_loc, drop_loc, pick_time,drop_time, total_distance, total_weight, full_rate) VALUES('Jing PXXXXX', 'Beijing Chaoyang Logistics Center', 'Beijing Haidian Shopping Center', '2025-01-09 08:12', '2025-01-09 11:50',38500, 25, 0.9)

[0076] In the converted Structured Query Language: vehicle_no represents the license plate number; pick_loc represents the pickup location, i.e., the departure place; drop_loc represents the unloading location, i.e., the destination; pick_time represents the pickup time, i.e., the departure time; drop_time represents the unloading time, i.e., the arrival time; total_distance represents the total travel distance; total_weight represents the total weight; full_rate represents the loading rate. Key technologies include field replacement rules, connection path derivation, and conditional expression conversion. The above conversion can be implemented based on a Structured Query Language parsing and optimization framework (such as Apache Calcite).

[0077] Execution step: submitting the second Structured Query Language statement to the service database for execution. The system submits the converted physical Structured Query Language statement to the customer database engine for execution. The execution result (query result set or number of rows affected by the update) is returned to the algorithm service, completing a full data interaction.

[0078] Compared with the prior art, the present invention has the following beneficial effects:

[0079] (1) Significantly reducing the development cost: the traditional customized Structured Query Language development mode is replaced by the configuration method of a domain-specific language, and there is no need to write customized Structured Query Language for each customer, thus avoiding repeated development;

[0080] (2) Improving system scalability: when a new customer accesses, only the mapping relationship needs to be configured, no algorithm code modification is required, and differentiated mapping of heterogeneous table structures between different service databases is supported;

[0081] (3) Enhancing system stability: the automatic Structured Query Language conversion based on mapping rules reduces manual development errors;

[0082] (4) Increasing flexibility: supporting complex mapping and dynamic structures, and being able to flexibly cope with various complex table structure relationships;

[0083] (5) Improves real-time performance, eliminating the need for an extraction-transformation-loading intermediate layer, thus eliminating data latency and data redundancy;

[0084] (6) Achieve algorithm standardization, completely decouple the algorithm from the data, and abstract a unified data logic model from the algorithm to achieve unified algorithm service capabilities across customers. Attached Figure Description

[0085] Figure 1 This is a flowchart illustrating the algorithm data model dynamic mapping and structured query language conversion method of the present invention.

[0086] Figure 2 This is a structural block diagram of the system for dynamic mapping of data models and conversion of structured query language according to the algorithm of this invention. Detailed Implementation

[0087] The present invention will be further described in detail below with reference to embodiments, but the implementation of the present invention is not limited thereto.

[0088] Overview of Detailed Implementation Methods

[0089] like Figure 1 As shown, the algorithm data model dynamic mapping and structured query language conversion method proposed in this invention is executed in sequence according to the following four steps.

[0090] Step S101: Logical Table Structure Definition. Define the logical table structure based on the data requirements of the algorithm service. The logical table structure includes the logical table name, logical field names, and field types. This logical table structure is independent of the physical table structure of the business database. The algorithm service writes Structured Query Language (SCL) statements based on the defined logical table structure, without needing to concern itself with the table names, field names, and inter-table relationships of the physical tables in the actual business database. The output of this step is the logical table structure definition file.

[0091] Step S102: Mapping Configuration. Establish the mapping relationship between the logical table structure and the physical table structure in the business database. The mapping relationship includes the correspondence between logical tables and physical tables, and the correspondence between logical fields and physical fields. Implementers define the above mapping relationship for the current customer's business database through a domain-specific language configuration file or by inputting information through a configuration interface. The output of this step is the mapping configuration file.

[0092] Step S103: Structured Query Language (SCL) Conversion. Obtain the first SCL statement generated by the algorithm service based on the logical table structure. Parse the first SCL statement to identify the logical tables and logical fields it contains. Based on the mapping relationship, replace the logical tables and logical fields in the first SCL statement with their corresponding physical tables and physical fields, generating a second SCL statement that can be executed in the business database. The core of this step lies in achieving automatic conversion from logical SQL to physical SQL through the mapping relationship, allowing the algorithm to operate without concern for the specific implementation of the physical database.

[0093] Step S104: Execution. Submit the second structured query language statement to the business database for execution. The execution result is returned to the algorithm service, completing a full data interaction.

[0094] The four steps described above are sequentially linked: Step S101 provides the logical table structure definition; Step S102 establishes the mapping relationship between the logical table and the physical table; Step S103 automatically converts the SQL statement based on the mapping relationship; and Step S104 submits the converted SQL statement for execution. These four steps are interconnected, together achieving complete decoupling between the algorithm and the database structure. The following detailed examples illustrate each step.

[0095] Example 1: Detailed Implementation of the Logical Table Structure Definition Steps

[0096] In this embodiment, taking a logistics scheduling algorithm as an example, the specific implementation process of the logical table structure definition step is explained.

[0097] Logistics scheduling algorithms require order and vehicle information for scheduling optimization calculations. The data required by the algorithm includes: the unique identifier of the order, the weight (or volume) of the order, the identifier of the vehicle carrying the order; and the unique identifier of the vehicle, the vehicle's license plate number, and the vehicle's capacity (i.e., the maximum weight or volume the vehicle can carry).

[0098] Based on the data requirements of the above algorithm, the logical table structure is defined as follows.

[0099] The logical table `order` contains the following logical fields: `order_id` (order identifier), a string that cannot be null, used as the primary key; `demand` (demand quantity), a double-precision floating-point number representing the weight or volume of the order; and `vehicle_id` (vehicle identifier), a string representing the vehicle carrying the order.

[0100] The logical table `vehicle` contains the following logical fields: `vehicle_id` (vehicle identifier), a string that cannot be null, which serves as the primary key; `vehicle_number` (license plate number), a string; and `capacity`, a double-precision floating-point number representing the vehicle's loading capacity.

[0101] The above logical table structure is defined in the form of a domain-specific language configuration file (algo_schema.yaml) as follows:

[0102] logical_tables:

[0103] name: order

[0104] Description: Order Form

[0105] primary_key:

[0106] order_id

[0107] fields:

[0108] name: order_id

[0109] type: string

[0110] nullable: false

[0111] name: demand

[0112] type: double

[0113] name: vehicle_id

[0114] type: string

[0115] name: vehicle

[0116] Description: Vehicle Table

[0117] primary_key:

[0118] vehicle_id

[0119] fields:

[0120] name: vehicle_id

[0121] type: string

[0122] name: vehicle_number

[0123] type: string

[0124] name: capacity

[0125] type: double

[0126] This logical table structure is independent of the specific database and only describes the minimum data structure required by the algorithm. The algorithm service writes a structured query language based on this logical table structure without needing to know the specific names of the tables, fields, or the specific relationships between tables in the actual business database.

[0127] In this implementation, the logical table structure is defined based on the input data (order data, vehicle data) and output data (scheduling scheme) required by the algorithm. This brings the following benefits: the algorithm code is completely decoupled from the physical database structure, allowing the algorithm to be seamlessly migrated between different customers' business systems; when algorithm requirements change, only the logical table structure definition needs to be adjusted without affecting the configured mapping relationships.

[0128] Example 2: Detailed Implementation of Mapping Configuration Steps

[0129] In this embodiment, the logistics scheduling algorithm will continue to be used as an example to illustrate the specific implementation process of the mapping configuration step.

[0130] Assume that customer A's business database is a MySQL database, where the physical table storing order data is named t_order and the physical table storing vehicle data is named t_vehicle.

[0131] The physical table t_order (order table) contains the following fields: id (order identifier, type string); qty (order weight, type double-precision floating-point number); vehicle_id (vehicle identifier, type string).

[0132] The physical table t_vehicle contains the following fields: id (vehicle identifier, type string); number (license plate number, type string); max_capacity (vehicle capacity, type double-precision floating-point number).

[0133] Establish a mapping relationship between the logical table structure and the physical table structure described above. The mapping relationship between logical fields and physical fields is shown in Table 1 below:

[0134] Table 1 Mapping Relationship

[0135]

[0136] The above mapping relationship is defined in the form of a domain-specific language configuration file (algo_mapping.yaml) as follows:

[0137] datasource:

[0138] name: business_db

[0139] type: mysql

[0140] table_mapping:

[0141] logic_table: order

[0142] Description: Order Form

[0143] physical_table: t_order

[0144] fields:

[0145] logic_name: order_id

[0146] physical_column: id

[0147] physical_type: string

[0148] logic_name: demand

[0149] physical_column: qty

[0150] physical_type: double

[0151] logic_name: vehicle_id

[0152] physical_column: vehicle_id

[0153] physical_type: string

[0154] logic_table: vehicle

[0155] Description: Vehicle Table

[0156] physical_table: t_vehicle

[0157] fields:

[0158] logic_name: vehicle_id

[0159] physical_column: id

[0160] physical_type: string

[0161] logic_name: vehicle_number

[0162] physical_column: number

[0163] physical_type: string

[0164] logic_name: capacity

[0165] physical_column: max_capacity

[0166] physical_type: double

[0167] In this implementation, the logical table name `order` differs from the physical table name `t_order`, the logical field name `order_id` differs from the physical field name `id`, and the logical field name `demand` differs from the physical field name `qty`. All these differences are established through a mapping configuration. This brings the following benefits: the algorithm does not need to concern itself with the specific naming conventions of the physical tables; it only needs to write a structured query language based on the logical table structure. When the physical table structure changes (such as field renaming), only the mapping configuration needs to be updated, without modifying the algorithm code.

[0168] Example 3: Detailed Implementation of the Structured Query Language Transformation Steps

[0169] 1. Transformation of SELECT query statements;

[0170] In this embodiment, taking the data query scenario of a logistics scheduling algorithm as an example, we will illustrate the specific implementation method of Structured Query Language (SCL) conversion.

[0171] The algorithm needs to query the order identifier, required quantity, vehicle license plate number, and vehicle capacity information, and needs to link the order table and the vehicle table. The query condition is that the required quantity of the order is greater than 10.

[0172] Based on the logical table structure, the algorithm is written using the following logical structured query language statement:

[0173] SELECT o.order_id, o.demand, v.vehicle_number, v.capacity FROM ordero, vehicle v WHERE o.vehicle_id = v.vehicle_id AND o.demand > 10

[0174] The parsing process of this Logical Structured Query Language statement is as follows.

[0175] First, the statement is lexical and syntactic analyzed using a structured query language parser (such as the Apache Calcite structured query language parser) to construct a logical relational algebra tree (RelNode). Within the logical relational algebra tree, logical table nodes (order and vehicle) and logical field nodes (order_id, demand, vehicle_number, capacity, vehicle_id) are identified.

[0176] Then, the logical relational algebra tree is traversed, and according to the mapping relationship defined in the mapping configuration file, the logical table name and logical field name in each node are replaced with the physical table name and the physical field name respectively: the logical table order in the FROM clause is replaced with the physical table t_order, and the logical table vehicle is replaced with the physical table t_vehicle; the logical field order_id in the SELECT clause is replaced with the physical field id, the logical field demand is replaced with the physical field qty, the logical field vehicle_number is replaced with the physical field number, and the logical field capacity is replaced with the physical field max_capacity; the logical field vehicle_id in the WHERE clause (in the order table) is replaced with the physical field vehicle_id, the logical field vehicle_id (in the vehicle table) is replaced with the physical field id, and the logical field demand is replaced with the physical field qty; the JOIN relationship between logical tables is derived, that is, the logical condition o.vehicle_id = v.vehicle_id is converted into the physical condition o.vehicle_id = v.id.

[0177] The Physical Structured Query Language statement regenerated based on the replaced nodes is as follows:

[0178] SELECT o.id, o.qty, v.number, v.capacity FROM t_order o, t_vehicle v WHERE o.vehicle_id = v.id AND o.qty > 10

[0179] In the above conversion process, each key step constructs a logical relational algebra tree and completes mapping and replacement on the tree nodes, which ensures that the structure of the Structured Query Language statement (such as the hierarchical relationship of the SELECT clause, FROM clause, and WHERE clause) remains consistent before and after conversion, only the table names and field names are replaced, without changing the logical semantics and syntactic structure of the Structured Query Language statement; the association conditions between physical tables are automatically derived through the mapping relationship, so that even if the association fields between logical tables have different field names in the physical tables, the conversion engine can correctly establish the JOIN relationship between the physical tables.

[0180] 2. Conversion of INSERT statements;

[0181] In this embodiment, the data output scenario of the logistics scheduling algorithm is taken as an example to describe the specific implementation of Structured Query Language conversion.

[0182] After the algorithm performs scheduling optimization calculation, it generates vehicle trip information, including: license plate number, departure location, destination, departure time, arrival time, total travel distance, total loaded weight, and loading rate.

[0183] Based on the logical table structure, the logical Structured Query Language INSERT statement written by the algorithm is as follows:

[0184] INSERT INTO load(vehicle_number, start_location, end_location, start_time, end_time, total_distance, total_qty, load_rate) VALUES('Jing PXXXXX', 'Chaoyang Logistics Center, Beijing', 'Haidian Shopping Center, Beijing', '2025-01-09 08:12', '2025-01-09 11:50', 38500, 25, 0.9)

[0185] The parsing process of this logical Structured Query Language INSERT statement is as follows.

[0186] First, the statement is parsed by a Structured Query Language parser to construct a logical relational algebra tree, and the logical table load and each logical field are identified in the logical relational algebra tree.

[0187] Then, conversion is performed according to the mapping relationship defined in the mapping configuration file: replace the logical table name load in the INSERT INTO clause with the physical table name load_info; sequentially replace the logical fields in the field list with the corresponding physical fields, where the mapping relationships are: vehicle_number maps to vehicle_no, start_location maps to pick_loc, end_location maps to drop_loc, start_time maps to pick_time, end_time maps to drop_time, total_distance maps to total_distance, total_qty maps to total_weight, load_rate maps to full_rate; the values in the VALUES clause are arranged correspondingly according to the order of the mapped physical fields, keeping consistent with the order of the field list.

[0188] The converted physical structured query language INSERT statement is as follows:

[0189] INSERT INTO load_info(vehicle_no, pick_loc, drop_loc, pick_time,drop_time, total_distance, total_weight, full_rate) VALUES('Jing PXXXXX', 'Beijing Chaoyang Logistics Center', 'Beijing Haidian Shopping Center', '2025-01-09 08:12', '2025-01-09 11:50',38500, 25, 0.9)

[0190] In the above conversion process, the key step is to replace the logical table name and logical field names in the INSERT statement with the physical table name and physical field names one by one according to the mapping relationship, while keeping the order of the values in the VALUES clause strictly corresponding to the order of the field list, so as to ensure the correctness of the insertion operation; by processing both read and write operations through unified mapping rules, a bidirectional unified conversion mechanism for input data read operations and output data write operations is implemented, so that the algorithm service does not need to care about the structural details of the physical database in both the data reading and writing directions.

[0191] 3. Implementation manner of the conversion engine based on Apache Calcite;

[0192] In this implementation manner, the specific implementation manner of implementing the structured query language conversion engine based on the Apache Calcite framework is described.

[0193] Apache Calcite is an open-source framework for parsing and optimizing structured query language, providing complete capabilities for structured query language parsing, relational algebra construction, and query optimization.

[0194] The specific implementation is as follows: The Apache Calcite Structured Query Language parser (SqlParser) is used to perform lexical and syntactic analysis on the logical structured query language statement to generate an abstract syntax tree; the Apache Calcite relational algebra builder (SqlToRelConverter) is used to convert the abstract syntax tree into a logical relational algebra tree (RelNode); logical table and logical field nodes are identified in the logical relational algebra tree; the logical relational algebra tree is traversed, and according to the mapping relationship provided by the domain-specific language parsing engine, the logical table names in each node are replaced with physical table names, and the logical field names are replaced with physical field names; the Apache Calcite relational algebra to structured query language converter (RelToSqlConverter) is used to regenerate the replaced relational algebra tree into a physical structured query language statement.

[0195] Through the above implementation methods, this invention fully utilizes the mature structured query language processing capabilities of the Apache Calcite framework, ensuring the accuracy and efficiency of structured query language conversion. Simultaneously, through customized conversion rules and mapping relationships, it achieves automatic conversion from logical structured query language to physical structured query language.

[0196] Example 4: Detailed Implementation of the Execution Steps

[0197] In this embodiment, the specific implementation method of the execution steps is described.

[0198] After completing the Structured Query Language (SCL) conversion, the execution engine submits the second SCL statement (i.e., the physical SCL statement) to the business database for execution.

[0199] Specifically, the execution engine obtains a connection to the business database through the database connection pool, constructs a preprocessed statement object, and sends the converted Physical Structured Query Language (PSM) statement to the database engine. After the database engine executes the statement, it returns the execution result: for SELECT queries, it returns a ResultSet, which the execution engine converts into a data format recognizable by the algorithm service (such as a list or mapping structure) and returns to the algorithm service; for INSERT, UPDATE, DELETE, and other update statements, it returns the number of affected rows, which the execution engine returns to the algorithm service.

[0200] Through this execution step, the algorithm service can obtain the required input data or write the algorithm calculation results into the business database, realizing bidirectional data flow between the algorithm and the business database. As the final executor of the transformed structured query language statement, the execution engine completes the entire closed-loop process from logical data manipulation to physical data manipulation, ensuring the algorithm service's complete data manipulation capabilities.

[0201] Example 5: Overall System Implementation

[0202] In this embodiment, a complete system is used as an example to illustrate the overall implementation of the system technical solution of the present invention.

[0203] like Figure 2 As shown, an algorithm data model dynamic mapping and structured query language conversion system includes the following modules:

[0204] Logical Table Structure Definition Module 1: This module defines the logical table structure based on the data requirements of the algorithm service. The logical table structure includes the logical table name, logical field names, and field types. This module defines the logical table structure using a domain-specific language configuration file.

[0205] Mapping Configuration Module 2: This module establishes the mapping relationship between the logical table structure and the physical table structure in the business database. The mapping relationship includes the correspondence between logical tables and physical tables, and the correspondence between logical fields and physical fields. This module supports defining the mapping relationship using domain-specific language configuration files or by inputting through a configuration interface.

[0206] Domain-specific language parsing engine 3: used to parse the mapping relationships defined in the mapping configuration module and provide the parsed mapping relationships to the structured query language conversion engine.

[0207] Structured Query Language Transformation Engine 4: Used to obtain the first structured query language statement generated by the algorithm service based on the logical table structure, parse the first structured query language statement to identify the logical tables and logical fields contained therein, and replace the logical tables and logical fields in the first structured query language statement with the corresponding physical tables and physical fields respectively according to the mapping relationship provided by the domain-specific language parsing engine, thereby generating a second structured query language statement that can be executed in the business database.

[0208] Execution Engine 5: Used to submit the second Structured Query Language statement to the business database for execution.

[0209] The workflow of this system embodiment is as follows.

[0210] First, the algorithm service defines the logical table structure using the logical table structure definition module, based on the data format and requirements of the algorithm. The logical table structure is defined using a domain-specific language configuration file, describing the minimum data structure required by the algorithm. The algorithm service then uses this logical table structure to write a structured query language to obtain input data and write output data.

[0211] Secondly, the implementation team uses the mapping configuration module to establish a mapping relationship between the logical table structure and the physical table structure for the current customer's business database. This mapping relationship is defined using a domain-specific language configuration file or by inputting it through a configuration interface.

[0212] Then, when the algorithm service performs an operation that requires accessing data, the Structured Query Language (SCL) conversion engine obtains the first SCL statement generated by the algorithm service based on the logical table structure. The domain-specific language parsing engine parses the mapping relationships defined in the mapping configuration module and provides the parsed mapping relationships to the SCL conversion engine.

[0213] Next, the Structured Query Language (SCL) conversion engine parses the first SCL statement, identifying the logical tables and logical fields it contains. Based on the mapping relationship provided by the domain-specific language parsing engine, the logical tables and logical fields in the first SCL statement are replaced with their corresponding physical tables and physical fields, generating a second SCL statement that can be executed in the business database.

[0214] Finally, the execution engine submits the second structured query language statement to the business database for execution. The execution result (query result set or number of rows affected by the update) is returned to the algorithm service, completing a full data interaction.

[0215] In this implementation, all data interactions between the algorithm service and the business database are completed through the process described above. The algorithm service always operates on data based on the logical table structure and does not need to concern itself with the specific implementation of the physical database. When a new customer is added, only a new mapping relationship needs to be configured; the algorithm service requires no modifications.

[0216] This system implementation achieves complete decoupling between the algorithm and the database structure; it enables unified algorithm services across clients; it replaces customized development with a configurable approach, significantly reducing implementation costs; and the overall system workflow is stable and efficient, ensuring real-time data interaction.

[0217] Industrial applicability

[0218] This invention can be widely applied to various scenarios requiring algorithm services to interact with heterogeneous business databases, especially suitable for applications such as operations research algorithms, scheduling algorithms, and path planning algorithms that need to read input data from different clients' business databases and write output data. This invention replaces the traditional customized development model with a domain-specific language configuration method, significantly reducing the implementation cost of algorithm services, improving the scalability and maintainability of algorithm services, and possessing good industrial practical value.

[0219] The above description is merely a preferred embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent structural or procedural transformations made based on the content of the present invention specification, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of the present invention.

Claims

1. A method for dynamic mapping of algorithmic data models and conversion to structured query language, characterized in that, include: Logical table structure definition steps: Define the logical table structure according to the data requirements of the algorithm service. The logical table structure includes the logical table name, logical field name and field type. The logical table structure is independent of the physical table structure of the business database. Mapping configuration steps: Establish a mapping relationship between the logical table structure and the physical table structure in the business database. The mapping relationship includes the correspondence between logical tables and physical tables, and the correspondence between logical fields and physical fields. Structured Query Language (SCL) conversion steps: Obtain the first SCL statement generated by the algorithm service based on the logical table structure; parse the first SCL statement to identify the logical tables and logical fields contained therein; replace the logical tables and logical fields in the first SCL statement with the corresponding physical tables and physical fields according to the mapping relationship; and generate a second SCL statement that can be executed in the business database. Execution steps: Submit the second structured query language statement to the business database for execution.

2. The algorithm data model dynamic mapping and structured query language conversion method according to claim 1, characterized in that, In the mapping configuration step, the mapping relationship is defined by a domain-specific language configuration file or by inputting a configuration interface. The domain-specific language configuration file includes data source configuration information and table mapping configuration information. The data source configuration information includes the database type, and the table mapping configuration information includes the correspondence between logical table names and physical table names, as well as the correspondence between each logical field and physical field.

3. The algorithm data model dynamic mapping and structured query language conversion method according to claim 1 or 2, characterized in that, In the structured query language conversion step, the first structured query language statement is parsed by constructing a logical relational algebra tree. Logical table and logical field nodes are identified in the logical relational algebra tree. The logical relational algebra tree is traversed, and the logical table name in each node is replaced with the physical table name and the logical field name is replaced with the physical field name according to the mapping relationship. The second structured query language statement is then regenerated based on the replaced nodes.

4. The algorithm data model dynamic mapping and structured query language conversion method according to claim 1 or 2, characterized in that, The Structured Query Language (SCL) conversion step supports the conversion of SELECT query statements, including: replacing logical fields in the SELECT clause with physical fields, replacing logical tables in the FROM clause with physical tables, replacing logical fields in the WHERE clause and other conditional clauses with physical fields, and deriving the association relationships between physical tables corresponding to the JOIN relationships between logical tables.

5. The algorithm data model dynamic mapping and structured query language conversion method according to claim 1 or 2, characterized in that, The Structured Query Language (SCL) conversion step supports the conversion of INSERT statements, including: replacing the logical table name in the INSERT INTO clause with the physical table name, replacing the logical fields in the field list with the physical fields, and arranging the values ​​in the VALUES clause according to the mapped order of the physical fields.

6. The algorithm data model dynamic mapping and structured query language conversion method according to claim 1, characterized in that, In the logical table structure definition step, the logical table structure is defined based on the input and output data required by the algorithm to describe the minimum dataset on which the algorithm depends for operation; the mapping configuration step supports differentiated mapping of heterogeneous table structures between different business databases, and the heterogeneous table structures include different database types, different table naming methods, different field naming methods, and different table relationship designs.

7. The algorithm data model dynamic mapping and structured query language conversion method according to claim 3, characterized in that, The method is based on the Apache Calcite framework to implement structured query language parsing and conversion. It uses the Apache Calcite structured query language parser to construct the logical relational algebra tree, and achieves automatic conversion from logical structured query language statements to physical structured query language statements through custom conversion rules and the mapping relationship.

8. A system for dynamic mapping of algorithmic data models and conversion of structured query language, used to execute the method for dynamic mapping of algorithmic data models and conversion of structured query language according to any one of claims 1-7, characterized in that, include: The logical table structure definition module is used to define the logical table structure according to the data requirements of the algorithm service. The logical table structure includes the logical table name, logical field name, and field type. The mapping configuration module is used to establish the mapping relationship between the logical table structure and the physical table structure in the business database. The mapping relationship includes the correspondence between logical tables and physical tables, and the correspondence between logical fields and physical fields. A domain-specific language parsing engine is used to parse the mapping relationships defined in the mapping configuration module and provide the parsed mapping relationships to the structured query language conversion engine; A structured query language conversion engine is used to obtain a first structured query language statement generated by the algorithm service based on the logical table structure, parse the first structured query language statement to identify the logical tables and logical fields contained therein, and replace the logical tables and logical fields in the first structured query language statement with the corresponding physical tables and physical fields respectively according to the mapping relationship provided by the domain-specific language parsing engine, thereby generating a second structured query language statement that can be executed in the business database. An execution engine is used to submit the second structured query language statement to the business database for execution.

9. The algorithm data model dynamic mapping and structured query language conversion system according to claim 8, characterized in that, The mapping configuration module supports defining the mapping relationship using a domain-specific language configuration file or a configuration interface. The domain-specific language configuration file includes data source configuration information and table mapping configuration information. The data source configuration information includes the database type, and the table mapping configuration information includes the correspondence between logical table names and physical table names, as well as the correspondence between each logical field and physical field. The logical table structure definition module defines the logical table structure using a domain-specific language configuration file.

Images