A DataAgent implementation approach of NL-OSI-SQL

Abstract

This invention relates to the fields of big data and artificial intelligence, and more particularly to a DataAgent implementation method for NL-OSI-SQL. This invention discloses an NLOSISQL DataAgent implementation method, belonging to the field of big data and artificial intelligence. This invention introduces the OSI Open Semantic Exchange standard as an intermediate layer, adopting a layered architecture of natural language → OSI semantics → SQL. It first converts natural language into standardized OSI semantic files, and then translates them into SQL executable by the target computing engine. This solves the problems of low accuracy, high development cost, poor cross-platform compatibility, weak interpretability, and untraceable lineage inherent in traditional NLSQL, NLDSLSQL, and NLMDLSQL. This invention has advantages such as high accuracy, low development cost, strong cross-platform compatibility, good scalability, and interpretability and traceability, and is suitable for natural language intelligent querying and indicator calculation scenarios in enterprise-level multi-platform data warehouses, lake warehouses, and multi-cloud environments.

Description

Technical Field

[0001] This invention relates to the fields of big data and artificial intelligence technology, specifically to a DataAgent implementation method for NL-OSI-SQL. Background Technology

[0002] With the rapid popularization of large-scale modeling technology, DataAgent intelligent data querying has become a core scenario for enterprise data applications. Business users can initiate data queries using natural language, and the system automatically generates executable SQL and returns the results. However, current mainstream implementations have significant shortcomings: 1. NL SQL: SQL is generated directly from natural language, which relies heavily on the model's understanding of table structure and business semantics. It has low accuracy, serious illusions, poor interpretability, and weak cross-platform capabilities. 2. NL DSL SQL: First convert it into a custom domain query language DSL, and then parse it into SQL. This requires building the syntax and parser from scratch, which is extremely costly and complicated to adapt to cross engines. 3. NL MDL SQL: Based on the indicator query language MDL, it has limited scalability, difficulty in supporting API / UDF, and insufficient cross-platform compatibility. Therefore, to address the above problems, a DataAgent implementation method based on NL-OSI-SQL is proposed. Summary of the Invention

[0003] The purpose of this invention is to provide a DataAgent implementation method for NL-OSI-SQL to solve the problems mentioned in the background art.

[0004] To achieve the above objectives, the present invention provides the following technical solution: As an optional implementation of the NL-OSI-SQL DataAgent method described in this invention, it includes the following steps: S1: Construct a unified semantic model according to the OSI Open Semantic Exchange standard, and solidify the semantic information of dataset, fields, relationships, basic indicators, dimensions and ai_context; S2: Receives natural language queries, performs intent recognition and semantic retrieval by a large model, extracts indicators, dimensions, and filtering conditions, and generates OSI format semantic files; S3: Dynamically generate derived and composite indicators based on OSI semantic files, fully inheriting the caliber constraints of basic indicators; S4: Through OSI The SQL translation tool converts OSI semantic files into executable SQL for the target computing engine, executes it, and returns business-specific results.

[0005] As an optional implementation of the NL-OSI-SQL DataAgent method described in this invention, the OSI semantic model includes dataset definition, field mapping, primary key, metric, dimension, relation, synonym, and ai_context caliber constraint.

[0006] As an optional implementation of the NL-OSI-SQL DataAgent described in this invention, the conversion from natural language to OSI includes intent recognition, indicator retrieval, dimension extraction, condition parsing, and dynamic indicator generation, with the output being an OSIYAML file.

[0007] As an optional implementation of the NL-OSI-SQL DataAgent described in this invention, the dynamic indicators include derived indicators and composite indicators, which are generated by formulas depending on the basic indicators, and support weight calculation and level determination.

[0008] As an optional implementation of the NL-OSI-SQL DataAgent described in this invention, the OSI to SQL translation supports multiple dialects including ANSISQL, Snowflake, MySQL, FabricIQ, and PalantirFoundry.

[0009] As an optional implementation of the NL-OSI-SQL DataAgent described in this invention, it supports calling external APIs and UDFs, and OSI 2.0 compatible indicator update and deletion operations.

[0010] As an optional implementation of the NL-OSI-SQL DataAgent method described in this invention, the generated SQL has traceable lineage, verifiable scope, and interpretable results.

[0011] As an optional implementation of the NL-OSI-SQL DataAgent described in this invention, it includes an OSI semantic governance module, a natural language understanding module, a dynamic indicator calculation module, and an SQL translation and execution module. The OSI semantic governance module is used to build and manage a standardized indicator library; The Natural Language Understanding module is used for intent recognition and OSI generation. The dynamic indicator calculation module is used for generating derived / composite indicators; The SQL translation and execution module is used for OSI-to-SQL conversion and query execution. The system performs claim 1 7. Any of the methods described in the above.

[0012] Compared with the prior art, the beneficial effects of the present invention are: High accuracy: The semantics and caliber are strictly constrained by OSI, which significantly reduces hallucinations and the results are reproducible.

[0013] Low cost: Directly reuses OSI standards and translation tools, eliminating the need for custom DSL / MDL.

[0014] Strong cross-platform compatibility: One semantic model is compatible with Snowflake, MySQL, Fabric IQ, Palantir, etc. Highly scalable: Supports external APIs and UDFs, and is compatible with OSI 2.0 indicator updates / deletions; Explainable and traceable: logic first, then syntax; clear lineage; verifiable interpretation. Model-friendly: Standardized structure reduces the cost of understanding and fine-tuning large models. Attached Figure Description

[0015] Figure 1 This is an architecture diagram of a DataAgent implementation method for NL-OSI-SQL; Figure 2 This method dynamically creates composite indicator relationships for the business semantics of a DataAgent implementation of NL-OSI-SQL. Detailed Implementation

[0016] Example 1: Please refer to Figure 1 and Figure 2 The present invention provides a technical solution: A method for implementing a DataAgent in NL-OSI-SQL includes the following steps: S1: Construct a unified semantic model according to the OSI Open Semantic Exchange standard, and solidify the semantic information of dataset, fields, relationships, basic indicators, dimensions and ai_context; S2: Receives natural language queries, performs intent recognition and semantic retrieval by a large model, extracts indicators, dimensions, and filtering conditions, and generates OSI format semantic files; S3: Dynamically generate derived and composite indicators based on OSI semantic files, fully inheriting the caliber constraints of basic indicators; S4: Through OSI The SQL translation tool converts OSI semantic files into executable SQL for the target computing engine, executes it, and returns business-specific results.

[0017] The OSI semantic model includes dataset definition, field mapping, primary key, metric, dimension, relation, synonym, and ai_context caliber constraint.

[0018] The conversion from natural language to OSI includes intent recognition, indicator retrieval, dimension extraction, condition parsing, and dynamic indicator generation, with the output being an OSIYAML file.

[0019] Dynamic indicators include derived indicators and composite indicators, which are generated by formulas based on basic indicators and support weight calculation and level determination.

[0020] OSI to SQL supports translation of multiple dialects including ANSI SQL, Snowflake, MySQL, FabricIQ, and Palantir Foundry.

[0021] It supports calling external APIs and UDFs, and updating and deleting OSI 2.0 compatible indicators.

[0022] The generated SQL is traceable in origin, verifiable in scope, and interpretable in results.

[0023] It includes an OSI semantic governance module, a natural language understanding module, a dynamic index calculation module, and an SQL translation and execution module; The OSI semantic governance module is used to build and manage a standardized indicator library; The Natural Language Understanding module is used for intent recognition and OSI generation. The dynamic indicator calculation module is used for generating derived / composite indicators; The SQL translation and execution module is used for OSI-to-SQL conversion and query execution. The system performs claim 1 7. Any of the methods described in the above.

[0024] With the rapid popularization of large-scale modeling technology, DataAgent intelligent data querying has become a core scenario for enterprise data applications. Business users can initiate data queries using natural language, and the system automatically generates executable SQL and returns the results. However, current mainstream implementations have significant shortcomings: NL... SQL: SQL is generated directly from natural language, heavily reliant on the model's understanding of table structure and business semantics, resulting in low accuracy, significant illusions, poor interpretability, and weak cross-platform capabilities; NL DSL SQL: First, it needs to be converted into a custom domain query language (DSL), and then parsed into SQL. This requires building the syntax and parser from scratch, resulting in extremely high development costs and complex cross-engine adaptation; NL MDL SQL: Based on the indicator query language MDL, it has limited scalability, difficulty in supporting APIs / UDFs, and insufficient cross-platform compatibility. This approach offers the following advantages: High accuracy: Semantics and definitions are strictly constrained by OSI, significantly reducing illusions, and results are reproducible; Low cost: Direct reuse of OSI standards and translation tools, eliminating the need for custom DSL / MDL; Strong cross-platform compatibility: One semantic model adapts to Snowflake, MySQL, Fabric IQ, Palantir, etc.; Good scalability: Supports external APIs and UDFs, compatible with OSI 2.0 indicator updates / deletions; Explainable and traceable: Logic first, then syntax, clear lineage, and verifiable definitions; Model-friendly: Standardized structure reduces the cost of understanding and fine-tuning large models.

[0025] The specific steps are as follows: The terminology is defined as follows: OSI: Open Semantic Interchange, is an open semantic exchange standard used to unify the semantic representation of entities, attributes, relationships, metrics, and dimensions.

[0026] Semantic Metrics Platform: Composed of a metrics platform and a semantic layer, it encapsulates technical fields into business metrics and dimensions.

[0027] Basic metric: The smallest indivisible atomic unit of measurement.

[0028] Derivative indicators: Indicators calculated using formulas based on basic indicators.

[0029] Composite index: A comprehensive score or index that is weighted and synthesized from multiple indicators.

[0030] NL OSI SQL: A hierarchical transformation architecture from natural language to OSI standard semantics to the target SQL dialect.

[0031] Overall architecture: OSI Semantic Governance Layer: Constructs a RAG indicator library by predefining datasets, fields, indicators, dimensions, relationships, ai_context synonyms, and caliber constraints according to the OSI specification.

[0032] Natural Language Understanding Layer: The large model performs intent recognition, indicator retrieval, dimension extraction, and condition parsing on the input to generate OSIYAML.

[0033] Dynamic indicator calculation layer: Dynamically generates derived / composite indicators based on basic indicators, and fully inherits the caliber constraints.

[0034] SQL Translation and Execution Layer: Translates OSI into executable SQL for the target engine and executes it.

[0035] Implementation steps: S1: Construct a unified semantic model based on the OSI standard, and solidify the dataset, basic indicators, dimensions, relationships and ai_context.

[0036] S2: Receives natural language queries, the large model retrieves matching features from the RAG library, and generates OSI semantic files.

[0037] S3: Based on OSI, dynamically generate derived and composite indicators to maintain consistent and traceable definitions.

[0038] S4: Through OSI SQL translation tools can generate ANSI SQL or target dialect SQL.

[0039] S5: Executes SQL and returns business-specific results.

[0040] Detailed explanation based on e-commerce business health scoring scenarios: Construct an OSI semantic model: Define a dataset business_db.sales.orders, and solidify basic metrics: GMV, number of users placing orders, and refund amount; define dimensions: order month and region; configure ai_context and caliber constraints.

[0041] Input natural language: "Calculate the monthly business health score for South China in March 2026. Algorithm: ((GMV)" (Refund amount) / GMV)*0.7 + (Number of users who placed orders / 10000)*0.3, with a maximum score of 100. ≥80 is excellent, 60 80 is good, <60 needs optimization. Generate OSIYAML: Dynamically generate: monthly net GMV, original health score, and health level, fully inheriting the basic indicator definitions.

[0042] OSI to SQL: Generates a multi-layered CTE structure SQL, and calculates the basic indicators → derived indicators → scores → levels in sequence.

[0043] Output results: Month, Region, GMV, Net GMV, Number of Users, Health Score, Health Level.

[0044] The specific process is as follows: The generated SQL follows these steps: WITH --1. Basic Indicator Layer: Atomic Indicator Aggregation base_metrics AS ( SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month, region SUM(payment_amount) AS gmv, COUNT(DISTINCT user_id) AS unique_order_users, SUM(CASE WHEN status = 'refunded' THEN payment_amount ELSE 0 END)AS refund_amount FROM business_db.sales.orders WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-03' AND region = 'South China' GROUP BY order_month, region), --2. Derivative Indicator Layer: Monthly Net GMV derived_metrics AS ( SELECT *, gmv - refund_amount AS monthly_net_gmv FROM base_metrics), -- 3. Composite Indicator Layer: Raw Score of Business Health score_calculation AS ( SELECT *, ((monthly_net_gmv / gmv) * 0.7 + (unique_order_users / 10000) *0.3) * 100 AS monthly_health_raw_score FROM derived_metrics) -- 4. Output Layer: Score + Grade SELECT order_month, region gmv, refund_amount, monthly_net_gmv, unique_order_users, ROUND(monthly_health_raw_score, 2) AS monthly_health_raw_score, CASE WHEN monthly_health_raw_score >= 80 THEN 'Excellent' WHEN monthly_health_raw_score >= 60 THEN 'Good' ELSE 'To be optimized' END AS monthly_health_levelFROM score_calculation; This article uses specific examples to illustrate the principles and implementation methods of the present invention. The above examples are only for the purpose of helping to understand the method and core ideas of the present invention. The above descriptions are only preferred embodiments of the present invention. It should be noted that due to the limitations of textual expression, while there are objectively infinite specific structures, those skilled in the art can make several improvements, modifications, or changes without departing from the principles of the present invention, and can also combine the above technical features in an appropriate manner. These improvements, modifications, changes, or combinations, or the direct application of the inventive concept and technical solution to other situations without modification, should all be considered within the scope of protection of the present invention.

Claims

1. A DataAgent implementation method for NL-OSI-SQL, characterized in that: Includes the following steps: S1: Construct a unified semantic model according to the OSI Open Semantic Exchange standard, and solidify the semantic information of dataset, fields, relationships, basic indicators, dimensions and ai_context; S2: Receives natural language queries, performs intent recognition and semantic retrieval by a large model, extracts indicators, dimensions, and filtering conditions, and generates OSI format semantic files; S3: Dynamically generate derived and composite indicators based on OSI semantic files, fully inheriting the caliber constraints of basic indicators; S4: Through OSI The SQL translation tool converts OSI semantic files into executable SQL for the target computing engine, executes it, and returns business-specific results.

2. The method according to claim 1, characterized in that, The OSI semantic model includes dataset definition, field mapping, primary key, metric, dimension, relation, synonym, and ai_context caliber constraint.

3. The method according to claim 1, characterized in that, The conversion from natural language to OSI includes intent recognition, indicator retrieval, dimension extraction, condition parsing, and dynamic indicator generation, with the output being an OSIYAML file.

4. The method according to claim 1, characterized in that, Dynamic indicators include derived indicators and composite indicators, which are generated by formulas based on basic indicators and support weight calculation and level determination.

5. The method according to claim 1, characterized in that, OSI to SQL supports translation of multiple dialects including ANSI SQL, Snowflake, MySQL, FabricIQ, and Palantir Foundry.

6. The method according to claim 1, characterized in that, It supports calling external APIs and UDFs, and updating and deleting OSI 2.0 compatible indicators.

7. The method according to claim 1, characterized in that, The generated SQL is traceable in origin, verifiable in scope, and interpretable in results.

8. A type of NL OSI The SQLDataAgent system is characterized by, It includes an OSI semantic governance module, a natural language understanding module, a dynamic index calculation module, and an SQL translation and execution module; The OSI semantic governance module is used to build and manage a standardized indicator library; The Natural Language Understanding module is used for intent recognition and OSI generation. The dynamic indicator calculation module is used for generating derived / composite indicators; The SQL translation and execution module is used for OSI-to-SQL conversion and query execution. The system performs claim 1 7. Any of the methods described in the above.

Images