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:
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.-
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 loadairportdb
in MySQL HeatWave Cluster, see Section 3.2, “Quickstart: AirportDB Analytics”.
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.
Learn more about the
NL_SQL
routine.