Why Choose Text-to-SQL?

Consider the scenario of needing specific information from a vast database. Traditionally, crafting complex SQL queries requires a deep understanding of SQL syntax and the database's structure, which can be daunting for non-technical users. Text-to-SQL addresses this challenge by enabling users to express their requests in plain English, which the system then translates into precise SQL queries. This approach allows users to concentrate on their information needs rather than on the intricacies of query writing.

How LlamaIndex Facilitates Text-to-SQL

LlamaIndex enhances Text-to-SQL by:

• Interpreting Table Schema: It reads the schema of your database tables, gaining insights into the columns, data types, and inter-table relationships. This contextual understanding ensures that the generated SQL aligns with your database structure.
• Creating SQL Queries: Based on user input and the schema, LlamaIndex formulates a SQL query that accurately retrieves the desired data, conforming to the specified columns and data types to guarantee both correctness and efficiency.

Step-by-Step Guide for Utilizing LlamaIndex's Text-to-SQL

Table Overview for Demonstration

In this tutorial, we will utilize a table named loan to analyze student loan stability, structured as follows:

• id: Unique identifier for each loan entry.
• risk_grade: An integer from 1 to 10 indicating the loan's risk level, with 10 denoting significant financial burden.
• probability: The likelihood associated with the risk grade.
• gender: The gender of the loan applicant.
• name: The name of the loan applicant.
• create_date: The date the loan entry was created.

Step 1: Install Necessary Libraries

pip install llama-index

Step 2: Configure Environment Variables and Define LLM

You must configure your environment with the requisite API keys. Substitute the placeholder with your actual OpenAI API key.

import os

from llama_index.llms.openai import OpenAI

os.environ["OPENAI_API_KEY"] = "your_openai_api_key"

# Create an LLM instance using OpenAI function

llm = OpenAI(model="gpt-4o", temperature=0.1)

Step 3: Connect to Your Database

Employ SQLAlchemy to establish a connection to your database, replacing the database URL with your actual connection string.

from sqlalchemy import create_engine, text

# Substitute with your actual database URL

DATABASE_URL = "postgresql://username:password@hostname:port/database_name"

engine = create_engine(DATABASE_URL)

Step 4: Verify Database Connection

Before moving forward, it’s crucial to test the database connection to confirm it’s functioning correctly. The output will display all schemas along with their corresponding table names.

# Test the connection

with engine.connect() as connection:

result = connection.execute(text("""

SELECT table_schema, table_name

FROM information_schema.tables

WHERE table_type = 'BASE TABLE'

ORDER BY table_schema, table_name

"""))

for row in result:

print(row)

Step 5: Define SQL Database

Initialize the SQL database using llama_index.core.SQLDatabase.

from llama_index.core import SQLDatabase

tables = ['loan']

# Initialize the SQLDatabase

sql_database = SQLDatabase(engine, schema="public", include_tables=tables, sample_rows_in_table_info=1)

print("SQLDatabase initialized successfully.")

Explanation:

• SQLDatabase Initialization: This step involves creating an instance of SQLDatabase from the llama_index.core module.
• Parameters:
  • engine: The SQLAlchemy engine object created earlier.
  • schema: The schema in your database where the specified tables are located (set to "public").
  • include_tables: A list of table names to include; here, we include the loan table.
  • sample_rows_in_table_info: Number of sample rows to include for LlamaIndex to comprehend the structure and data types.

Step 6: Example Usage

You can now apply the defined functions to convert a natural language query into SQL and execute it against your database.

from llama_index.core.query_engine import NLSQLTableQueryEngine

query_engine = NLSQLTableQueryEngine(

sql_database=sql_database, tables=["loan"], llm=llm

)

query_str = "Show me all loans with a risk grade greater than 5 from loan table."

response = query_engine.query(query_str)

display(Markdown(f"{response}"))

Additional Explanation:

Occasionally, LlamaIndex will directly provide the SQL query based on your natural language input. You can then copy and paste this SQL query into your PostgreSQL client for execution. For instance, if you wish to view the distribution of risk_grade from the loan table, LlamaIndex might generate the following SQL query:

query_str = "Show me the distribution of risk_grade from loan table."

response = query_engine.query(query_str)

Conclusion

LlamaIndex's Text-to-SQL features make database interactions significantly easier, enabling non-technical users to retrieve data through natural language queries. By following this guide, you can effortlessly set up LlamaIndex, connect it to your database, and produce precise SQL queries from everyday language. This not only saves valuable time but also helps bridge the gap between complex SQL syntax and user-friendly data access. Whether you are a data analyst, business user, or anyone seeking quick insights from your database, LlamaIndex allows you to focus on your data needs without the steep learning curve of SQL. Explore LlamaIndex today and enjoy the simplicity of natural language querying for effective and efficient data retrieval.

This video demonstrates the integration of LlamaIndex with DuckDB for Text-to-SQL functionality, showcasing how to streamline queries effortlessly.

This tutorial explains how to utilize Ollama and Vanna for local Text-to-SQL applications with any database, making querying accessible to everyone.