HeatWave User Guide  /  Perform AI-Powered Search and Content Generation  /  Generate SQL Queries From Natural-Language Statements

7.11 Generate SQL Queries From Natural-Language Statements

As of MySQL 9.4.1, MySQL HeatWave GenAI lets you generate SQL queries from natural-language statements, making it easier for you to interact with your databases. This feature collects information on the schemas, tables, and columns that you have access to, and then uses a Large Language Model (LLM) to generate an SQL query for the question pertaining to your data. It also lets you run the generated query and view the result set.

This topic describes how to use the NL_SQL routine to generate and run SQL queries from natural-language statements. It contains the following sections:

Before You Begin

Generate and Run an SQL Query

Perform the following step:

mysql>CALL sys.NL_SQL("NaturalLanguageStatement",@output, JSON_OBJECT('schemas',JSON_ARRAY('DBName'),'model_id','ModelID'));

Replace the following:

  • NaturalLanguageStatement: natural-language statement. It can be a question, statement, or query pertaining to your data available in MySQL HeatWave.

  • DBName: database to consider for generating and running the SQL query.

  • ModelID: LLM to use.

For example:

mysql>CALL sys.NL_SQL("How many flights are there in total?",@output, JSON_OBJECT('schemas',JSON_ARRAY('airportdb'),'model_id','llama3.1-8b-instruct-v1'));

The output is similar to the following:

+-----------------------------------------------------+
| Executing generated SQL statement...                |
+-----------------------------------------------------+
| SELECT COUNT(`flight_id`) FROM `airportdb`.`flight` |
+-----------------------------------------------------+
1 row in set (1 min 39.4060 sec)

+--------------------+
| COUNT(`flight_id`) |
+--------------------+
|             462553 |
+--------------------+

View the value stored in the variable @output:

mysql>SELECT JSON_PRETTY(@output);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(@output)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "tables": [
    "airportdb.airline",
    "airportdb.airplane",
    "airportdb.airplane_type",
    "airportdb.airport",
    "airportdb.airport_geo",
    "airportdb.airport_reachable",
    "airportdb.booking",
    "airportdb.employee",
    "airportdb.flight",
    "airportdb.flight_log",
    "airportdb.flightschedule",
    "airportdb.passenger",
    "airportdb.passengerdetails",
    "airportdb.weatherdata"
  ],
  "schemas": [
    "airportdb"
  ],
  "model_id": "llama3.1-8b-instruct-v1",
  "sql_query": "SELECT COUNT(`flight_id`) FROM `airportdb`.`flight`",
  "is_sql_valid": 1
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The output includes the following details:

  • List of tables the routine considered for generating and running the SQL query.

  • List of databases the routine considered for generating and running the SQL query.

  • Model ID of the LLM used to generate the SQL query.

  • The generated SQL query.

  • Whether the query is valid.

What's Next

Learn more about the NL_SQL routine.