OpenSearch:Configure the NL2SQL service

Examples

Configuration description

The NL2SQL service for query analysis transforms natural language queries into SQL statements. To use the NL2SQL service, you must configure the following information:

• Basic table information: Configure the business data tables on which the NL2SQL service depends. This includes field names, field types, field enumeration values, field descriptions, field mappings, and table joins.

• Learning samples: Configure frequently used or complex queries and their corresponding SQL statements to improve the accuracy of the NL2SQL service. If the service generates an unexpected SQL statement, you can also add the query and the correct SQL statement here.

• Custom rules (business terms and concepts): User queries often contain business-specific terms and concepts. To help the model understand this information, you can configure the terms, concepts, and their corresponding explanations here.

  For example:

  • How has waterlogging administration been in the past week? Related information: Waterlogging administration includes rainwater manhole covers and road flooding events.

  • Exemplary students are students with an ID less than or equal to 10. Student athletes are students with an ID greater than 11.

Procedure

1. On the AI Search Open Platform, choose Model Service > Service Configuration, and then click Create.

   Keep the default settings for Service Category and Configuration Type. Enter a Configuration Name, such as student_info_analysis, which represents student information data analytics.

2. Click Save and Next. Configure the data tables required by the NL2SQL service in the following format.

   • Basic table configuration

     [
       {
         "table":"schools",
         "columns": [
           {
             "column": "class",
             "description": "Class",
             "type": "string",
             "example": ["Class 3, Grade 1", "Accelerated Class"],
             "value_mapping": []
           }, 
           {
             "column": "school",
             "description": "School",
             "type": "string",
             "example": ["High School B in City A", "AA No. 5 High School"],
             "value_mapping": []
           }
         ]
       }, 
       {
         "table":"students",
         "columns": [
           {
             "column": "id",
             "description": "Student ID",
             "type": "int",
             "example": [1, 2],
             "value_mapping": [
               [1,"Zhang San"],
               [2,"Li Si"]
             ]
           },
           {
             "column": "class",
             "description": "Class",
             "type": "string",
             "example": ["Class 3, Grade 1", "Accelerated Class"],
             "value_mapping": []
           }
         ]
       }
     ]

     The table name for table must start with a lowercase letter and can contain only lowercase letters, numbers, and underscores (_). The name cannot exceed 30 characters.

     The field name for column must start with a lowercase letter and can contain only lowercase letters, numbers, and underscores (_). The name cannot exceed 30 characters.

     The field type for type can be text, string, int8, uint8, int16, uint16, int32, int, uint32, int64, uint64, float, double, location, date, time, or timestamp.

   • Table Asociated Configuration

     ["students.class=schools.class"]

3. Click Next. Configure custom rules and learning samples to improve the accuracy of the NL2SQL service.

   • Learning samples: Configure frequently used or complex queries and their corresponding SQL statements to improve the accuracy of the NL2SQL service. If the service generates an unexpected SQL statement, you can also add the query and the correct SQL statement here.

     [
       {
         "query":"Which class is Zhang San in?",
         "sql" : "SELECT class FROM students WHERE name = 'Zhang San'"
       }
     ]

   • Custom Rule Mapping (business terms and concepts): User queries often contain business-specific terms and concepts. To help the model understand this information, you can configure the terms, concepts, and their corresponding explanations here.

     For example: Exemplary students are students with an ID (id) <= 10. Student athletes are students with an ID (id) > 11.

     [
       {
         "key":"Exemplary student",
         "value":"students.id <= 10"
       },
       {
         "key":"Student athlete",
         "value":"students.id > 11"
       }
     ]

4. Click OK, and then click Activate Now. You are redirected to the service configuration list. The activation status changes to Activating while the system validates the format of your configuration.

   When the activation status changes to Activated, the service configuration is ready to use. You can test the NL2SQL service in the Experience Center.

What to do next

To integrate the NL2SQL service into your business code, see the NL2SQL API.