Combine OpenAI to Convert Natural Language Instructions into SQL Queries

By digoal

Background

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

Demo

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:

[query]  
... ...  
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:

CREATE DATABASE IF NOT EXISTS openai;  
USE openai;  
  
CREATE TABLE users(  
    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 |  
+---------+-------------+-------------+

Community

Combine OpenAI to Convert Natural Language Instructions into SQL Queries

Background

Demo

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

Intelligent Robot

PolarDB for Xscale