Skip to main content

Generate SQL with Text2SQL

Text2SQL helps you turn a natural-language request into a SQL statement in the NineData SQL Console. Use it when you want to explore data, draft a query, or give less SQL-focused users a faster way to retrieve information.

What Text2SQL Can Do​

  • Generate SQL from plain English: Describe the result you need, and NineData drafts the corresponding SQL statement.
  • Support common SQL operations: Generate query, create, update, and delete statements for the operation you describe.
  • Use database structure as context: NineData identifies the selected database, tables, and fields to reduce manual input.
  • Improve generated SQL: Use SQL Optimize after generation to review and optimize SQL before execution.

When to Use Text2SQL​

  • Business analysis: Help business, marketing, support, or operations users draft SQL without writing every clause manually.
  • Large or unfamiliar schemas: Generate a first draft when a query spans many tables or when table relationships are hard to remember.
  • Complex query patterns: Use natural language to describe joins, filters, grouping, or aggregations, then review the generated SQL before running it.

Prerequisites​

  • The target data source type must be one of these types: MySQL, PostgreSQL, Oracle, Sybase, SQL Server, Redis, Vastbase, MongoDB, OceanBase, Greenplum, Kingbase, Dameng, GaussDB, VastBase, PolarDB Oracle, Ymatrix, TiDB, Doris, ClickHouse.

  • In the commercial versions (DevOps Pro, DevOps Enterprise), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOps service. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

    check_quota

  • To improve SQL generation accuracy, generate enhanced ER diagrams for the target data source. For more information, see Enhanced E-R Diagram.

Procedure​

  1. Log in to the NineData Console.

  2. Go to DevOps > SQL Console.

  3. Open the target data source in SQL Console:

    • If a previous data source session is still open, NineData opens that data source automatically. To open another data source, select + next to the SQL Console tab, select the target data source on the New Query page, and click OK.
    • If no data source session is open, open the dropdown under SQL Console, select the target data source, and click Query.

  4. In the SQL editor, press / to open the Text2SQL input box.

  5. Enter the request you want to query, then press Enter or click send. NineData analyzes the request and generates SQL in the editor.

  6. Review the generated SQL before execution, especially for statements that change data or database objects.

Add Business Context for Enhanced AI​

Add AI comments to target tables and fields to give Text2SQL more business context. Comments can describe field meanings, business rules, foreign-key relationships, or valid values.

Note: AI comments improve generation accuracy only. They do not change the actual database.

  1. Log in to the NineData Console.

  2. Go to DevOps > SQL Console.

  3. Open the target data source in SQL Console:

    • If a previous data source session is still open, NineData opens that data source automatically. To open another data source, select + next to the SQL Console tab, select the target data source on the New Query page, and click OK.
    • If no data source session is open, open the dropdown under SQL Console, select the target data source, and click Query.

  4. In the navigation tree, right-click the target database name and select Add Table Notes For AI. In Table List, use the Actions column next to the target table and select Add AI Notes.

    tip

    Another option is to right-click a target table or column in the navigation tree and select Add Table Notes For AI.

  5. In the Add AI Notes window, add Table Notes For AI. Use the Actions column next to the target column name and select Edit to add AI Comment and configure Foreign key relationships for that column.

  6. Click OK to save your changes.

Result​

NineData saves the AI comments and uses them as additional context the next time you generate SQL with Text2SQL for the same tables or fields.

Next Steps​

  • Run and validate the generated SQL in SQL Console.
  • Use AI SQL Optimization to review SQL performance before running expensive queries.