AI queries (generate query based on AI request and db schema)

[Tsovak]

@delfrrr is it related to #114 or #113? if yes, should we take 114,113 first?

[delfrrr]

@Tsovak it's unrelated. It's more like Copilot Chat.

[Tsovak]

https://vanna.ai/docs/ train a RAG "model" on your data, and then ask questions which will return SQL queries that can be set up to automatically run on your database.

[Tsovak]

Azure provides a Complition "Natural language to SQL". One of approach is to use Azure AI service.

Input:

### Postgres SQL tables, with their properties:
#
# Employee(id, name, department_id)
# Department(id, name, address)
# Salary_Payments(id, employee_id, amount, date)
#
### A query to list the names of the departments which employed more than 10 employees in the last 3 months

SELECT

the result is

SELECT Department.name
FROM Department
WHERE Department.id IN (
    SELECT Employee.department_id
    FROM Employee
    WHERE Employee.id IN (
        SELECT Salary_Payments.employee_id
        FROM Salary_Payments
        WHERE Salary_Payments.date >= NOW() - INTERVAL '3 months'
        GROUP BY Salary_Payments.employee_id
        HAVING COUNT(*) > 10
    )
)

#Note: The openai-python library support for Azure OpenAI is in preview.
import os
import openai
openai.api_type = "azure"
openai.api_base = "https://<your-env>.openai.azure.com/"
openai.api_version = "2023-09-15-preview"
openai.api_key = os.getenv("OPENAI_API_KEY")

response = openai.Completion.create(
  engine="gpt-35-turbo",
  prompt="### Postgres SQL tables, with their properties:\n#\n# Employee(id, name, department_id)\n# Department(id, name, address)\n# Salary_Payments(id, employee_id, amount, date)\n#\n### A query to list the names of the departments which employed more than 10 employees in the last 3 months\n\nSELECT Department.name\nFROM Department\nWHERE Department.id IN (\n    SELECT Employee.department_id\n    FROM Employee\n    WHERE Employee.id IN (\n        SELECT Salary_Payments.employee_id\n        FROM Salary_Payments\n        WHERE Salary_Payments.date >= NOW() - INTERVAL '3 months'\n        GROUP BY Salary_Payments.employee_id\n        HAVING COUNT(*) > 10\n    )\n)",
  temperature=0,
  max_tokens=150,
  top_p=1,
  frequency_penalty=0,
  presence_penalty=0,
  stop=["#",";"])