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

• What's Next

Before You Begin

• Review the MySQL HeatWave GenAI requirements and privileges.

• If you want to use the meta.llama-3.3-70b-instruct OCI Generative AI Service LLM, complete the steps to authenticate OCI Generative AI Service.

• Connect to your MySQL HeatWave DB System.

• Load structured data from the DB System or Object Storage.

  The examples in this topic uses a sample database, airport. To learn how to download and then load airportdb in MySQL HeatWave Cluster, see Section 3.2, “Quickstart: AirportDB Analytics”.

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"
  ],
  "license": "Your use of this Llama model is subject to the Llama 3.1 Community License Agreement available at https://docs.oracle.com/cd/E17952_01/heatwave-9.4-license-com-en/",
  "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.