Community Blog Combine OpenAI to Convert Natural Language Instructions into SQL Queries

Combine OpenAI to Convert Natural Language Instructions into SQL Queries

This short article uses a demo to explain how to use OpenAI to convert natural language instructions into SQL queries.

By digoal


All the approaches to convert natural language to SQL are similar to each other. First, sending schema metadata to OpenAI, then natural language can be converted to SQL through OpenAI's learning. An OpenAI account is required, and metadata needs to be sent to OpenAI.

Databend AI for database reference: https://databend.rs/doc/sql-functions/ai-functions/ai-to-sql


Use the latest Codex model to convert natural language instructions into SQL query code-davinci-002:

Databend provides an effective solution to build SQL queries by combining OLAP and AI. This feature allows you to convert instructions written in natural language into SQL query statements that conform to the table schema. For example, you can provide the function with a sentence (such as get all items that cost no more than $10) as input and generate the corresponding SQL query "SELECT * FROM items WHERE price <= 10" as output.

Note: The generated SQL query statements follow the PostgreSQL standard, so they may need to be manually modified to be consistent with the syntax of Databend.

USE <your-database>;
SELECT * FROM ai_to_sql('<natural-language-instruction>');

Obtain and configure the OpenAI API key:

Please visit this link to generate a new OpenAI API key.

Configure the databend-query.toml file using openai_api_key settings:

... ...  
openai_api_key = "<your-key>"  

In this example, an SQL query statement is generated by the AI_TO_SQL function instruction, and the result statement is executed to obtain the query result.

1.  Prepare data:

USE openai;  
    id INT,  
    name VARCHAR,  
    age INT,  
    country VARCHAR  
CREATE TABLE orders(  
    order_id INT,  
    user_id INT,  
    product_name VARCHAR,  
    price DECIMAL(10,2),  
    order_date DATE  
-- Insert sample data into the users table  
INSERT INTO users VALUES (1, 'Alice', 31, 'USA'),  
                         (2, 'Bob', 32, 'USA'),  
                         (3, 'Charlie', 45, 'USA'),  
                         (4, 'Diana', 29, 'USA'),  
                         (5, 'Eva', 35, 'Canada');  
-- Insert sample data into the orders table  
INSERT INTO orders VALUES (1, 1, 'iPhone', 1000.00, '2022-03-05'),  
                          (2, 1, 'OpenAI Plus', 20.00, '2022-03-06'),  
                          (3, 2, 'OpenAI Plus', 20.00, '2022-03-07'),  
                          (4, 2, 'MacBook Pro', 2000.00, '2022-03-10'),  
                          (5, 3, 'iPad', 500.00, '2022-03-12'),  
                          (6, 3, 'AirPods', 200.00, '2022-03-14');  

2.  Run the AI_TO_SQL function with instructions written in English as input:

SELECT * FROM ai_to_sql(  
    'List the total amount spent by users from the USA who are older than 30 years, grouped by their names, along with the number of orders they made in 2022'); 

3.  The function generates an SQL statement as output:

*************************** 1. row ***************************  
     database: openai  
generated_sql: SELECT name, SUM(price) AS total_spent, COUNT(order_id) AS total_orders  
               FROM users  
                        JOIN orders ON users.id = orders.user_id  
               WHERE country = 'USA' AND age > 30 AND order_date BETWEEN '2022-01-01' AND '2022-12-31'  
               GROUP BY name;  

4.  Run the generated SQL statement to obtain the query result:

| name    | order_count | total_spent |  
| Bob     |           2 |     2020.00 |  
| Alice   |           2 |     1020.00 |  
| Charlie |           2 |      700.00 |  
0 0 0
Share on


275 posts | 24 followers

You may also like