In today’s world, data is being generated at an unprecedented rate, and extracting meaningful insights from this data has become a crucial task for businesses and organizations. Traditional analytics tools can often be complex, requiring technical expertise to understand and use effectively. But what if we could simplify this process, making it as easy as asking a question?
Imagine a tool that combines the power of natural language processing with the precision of structured data analytics. That’s exactly what we can achieve by building an LLM-powered analytics dashboard. By leveraging large language models (LLMs) like OpenAI’s GPT and integrating them with database querying capabilities, we can empower users to get valuable insights simply by asking questions in plain language.
In this blog, we’ll walk you through the process of building an LLM-powered analytics dashboard using Langchain, OpenAI’s GPT models, and a simple SQLite database. Whether you’re new to LLMs or just looking to enhance your existing data tools, this guide will help you create a powerful, intuitive interface for querying and analyzing data. The github link to the project is at: https://github.com/cloudxlab/text-to-sql.
What is LLM?
An LLM, or Large Language Model, is a type of artificial intelligence (AI) model designed to understand, generate, and process human-like text based on vast amounts of data it has been trained on. These models, such as OpenAI’s GPT (Generative Pre-trained Transformer), use advanced machine learning techniques, particularly deep learning, to predict and generate text based on given prompts.
Key Features of LLMs:
- Natural Language Understanding: LLMs can interpret and respond to natural language inputs, making them ideal for conversational interfaces, summarization, and more.
- Context Awareness: They maintain contextual coherence in conversations, enabling meaningful and contextually relevant responses.
- Versatility: LLMs can perform a range of tasks, including answering questions, generating text, code, and creative writing, and assisting in research.
Application in Analytics Dashboards:
When integrated with analytics tools, LLMs enhance user experience by:
- Allowing users to query data in plain language without requiring SQL or coding expertise.
- Providing intuitive and human-like interactions.
- Simplifying the process of extracting insights from structured and unstructured data.
For example, an LLM-powered analytics dashboard can translate a user’s plain-language question like “What were the top-selling products last quarter?” into a database query, retrieve the data, and present it in an easy-to-understand format. This integration democratizes data analytics, making it accessible to non-technical users.
What is an LLM-Powered Analytics Dashboard?
An LLM-Powered Analytics Dashboard is a smart solution that lets users ask questions in natural language, and in return, the system automatically generates and executes the corresponding SQL queries to retrieve the desired data. This creates a seamless, user-friendly interface that does not require any knowledge of SQL or database schema.
For example, instead of manually crafting SQL queries like:
sql Code:
SELECT COUNT("EmployeeId") FROM employees;
Users can simply ask:
- “How many employees are there currently?”
- “Where does Andrew Adams live?”
The system will handle the rest, generating SQL queries, executing them, and providing an easy-to-understand response.
What is Query Generation with LLMs?
Query generation with LLMs involves using AI models like GPT to transform natural language questions into database queries, such as SQL. This capability allows users to interact with databases using plain language, eliminating the need for advanced SQL knowledge.
1. System Prompt:
The system prompt instructs the Large Language Model (LLM) on how to handle the user’s query. It provides the context that the LLM is a MySQL expert. The system prompt template is:
css Code:
"You are a MySQL expert. Given the database schema {database_schema} and below user's query, generate a SQL query. {user_question}"
This means that the LLM will:
- Understand the structure of the database from the Database Schema.
- Use this knowledge to interpret the User’s Query.
- Generate a valid and accurate SQL Query.
2. Database Schema:
The Database Schema defines the structure of the database. In this example, the schema describes a table named Orders:
sql Code: CREATE TABLE Orders ( OrderID INTEGER NOT NULL AUTO_INCREMENT, UserID INTEGER, OrderDate DATETIME NOT NULL, TotalAmount DECIMAL(10, 2) NOT NULL, PRIMARY KEY (OrderID), CONSTRAINT orders_ibfk_1 FOREIGN KEY(UserID) REFERENCES User(UserID) );
3. User’s Question
The User’s Question specifies the information they are looking for. In this case:
plaintext Code: "How many smartphones of model 10 were ordered in February?"
This question implies:
- Smartphone Model: Focus is on “model 10.”
- Month: The month is February.
- Count: The result should return the number of such orders.
4. Final Prompt
The Final Prompt is a combination of the System Prompt, Database Schema, and User’s Question. It provides all necessary context for the LLM to generate the SQL query.
The Final Prompt combines:
- System Prompt: The LLM’s role as a MySQL expert.
- Database Schema: The structure of the database.
- User’s Question: A natural language query, such as “How many smartphones with Model 10 were sold after February this year?”
The final prompt given to the LLM is:
sql Code: System Prompt + Database Schema + User's question
Final Prompt:
plaintext Code: "You are a MySQL expert. Given the database schema: CREATE TABLE `Orders` ( OrderID INTEGER NOT NULL AUTO_INCREMENT, UserID INTEGER, OrderDate DATETIME NOT NULL, TotalAmount DECIMAL(10, 2) NOT NULL, PRIMARY KEY (OrderID), CONSTRAINT orders_ibfk_1 FOREIGN KEY(UserID) REFERENCES User(UserID) ); and below user's query, generate a SQL query. User's question: 'How many smartphones of model 10 were ordered in February?'"
5. LLM
The LLM (Language Learning Model) processes the Final Prompt to generate an appropriate SQL query based on:
- Its understanding of the database schema.
- The user’s question.
6. SQL Query
The SQL Query is the output generated by the LLM:
sql Code: SELECT COUNT(*) AS NumberOfSmartphones FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID JOIN Products p ON od.ProductID = p.ProductID WHERE p.ProductModel = 10 AND MONTH(o.OrderDate) = 2 AND YEAR(o.OrderDate) = YEAR(CURRENT_DATE);
Setting Up the Environment
Before diving into the code, let’s first set up the necessary environment. You’ll need to install Langchain and OpenAI’s integration. You can do this easily using the following command:
bash Code: !pip install --upgrade --quiet langchain langchain-community langchain-openai
After installation, set up your environment by importing the required libraries and configuring the connection to your OpenAI API key.
python Code: import openai_config import os os.environ["OPENAI_API_KEY"] = openai_config.OPENAI_API_KEY
Make sure you replace openai_config.OPENAI_API_KEY with your actual OpenAI API key.
Connecting to the Database
For this demonstration, we’ll be using an SQLite database (chinook.db), which contains a variety of data, such as artists, albums, customers, employees, invoices, and more. Langchain’s SQLDatabase utility helps connect to the database and manage queries.
python Code:
from langchain_community.utilities import SQLDatabase
db = SQLDatabase.from_uri("sqlite:///chinook.db")
print(db.dialect)
print(db.get_usable_table_names())
db.run("SELECT * FROM Artists LIMIT 10;")
Here, we list available tables and fetch some sample data from the “Artists” table to serve as our data source.
Building the Query Chain with Langchain
Next, we need to create a query chain that translates user input into SQL queries. This is done using Langchain’s create_sql_query_chain function, which integrates an LLM-based model (e.g., OpenAI’s GPT) to help translate natural language questions into SQL queries.
python Code: from langchain.chains import create_sql_query_chain from langchain_openai import ChatOpenAI from langchain_community.llms import OpenAI llm = ChatOpenAI(model="gpt-3.5-turbo", temperature=0, verbose=True) chain = create_sql_query_chain(llm, db)
The create_sql_query_chain function takes care of both generating the query and interpreting the results, ensuring a seamless interaction.
Understanding the Generated Prompts
Langchain provides useful prompts that guide the LLM in generating the right SQL queries. Here’s an example of a prompt that helps the model understand how to interact with the database.
python Code: chain.get_prompts()[0].pretty_print()
This prompt essentially tells the LLM to create syntactically correct SQLite queries and use the results to generate human-readable responses:
css Code: You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Answering User Questions with SQL
Once the chain is in place, it can respond to user queries. For instance, when a user asks, “How many employees are there currently?”, the system generates the SQL query:
sql Code:
SELECT COUNT("EmployeeId") FROM employees;
The query is executed on the database, and the result, such as 8, is processed by the LLM, which then returns a response like: “There are currently 8 employees.”
Here’s how the interaction works:
python Code:
invoke_chain("How many employees are there currently?")
This would output:
sql Code:
SQL query: SELECT COUNT("EmployeeId") AS "TotalEmployees" FROM employees;
Result: [(8,)]
Response: There are currently 8 employees.
Handling More Complex Questions
The LLM can also handle more complex queries. For example, if a user asks, “Where does Andrew Adams live?”, the system generates a query that filters the employees table based on the name and fetches the relevant fields.
python Code:
invoke_chain("Where does Andrew Adams live?")
This would result in:
sql Code:
SQL query: SELECT "Address", "City", "State", "Country" FROM employees WHERE "FirstName" = 'Andrew' AND "LastName" = 'Adams' LIMIT 1;
Result: [('11120 Jasper Ave NW', 'Edmonton', 'AB', 'Canada')]
Response: Andrew Adams lives at 11120 Jasper Ave NW, Edmonton, AB, Canada.
Full Code for Invoking the Chain
Here’s the complete code for generating SQL queries and returning user-friendly answers:
python Code:
from langchain.prompts import PromptTemplate
from langchain.chains import LLMChain
def invoke_chain(user_question):
# Generate SQL query from the user's question
sql_query = chain.invoke({"question": user_question})
print("SQL query:", sql_query, end="\n\n")
# Execute the SQL query against the database and get the result
result = db.run(sql_query)
print("Result:", result, end="\n\n")
# Create a prompt template to generate a response from the SQL result
answer_prompt = PromptTemplate.from_template(
"""Given the following user question, corresponding SQL query, and SQL result, generate a proper reply to give to user
Question: {question}
SQL Query: {query}
SQL Result: {result}
Answer: """
)
# Generate the answer using LLMChain
llm = LLMChain(llm=OpenAI(), prompt=answer_prompt)
ans = llm(inputs={"question": user_question, "query": sql_query, "result": result})
# Print the final response
print("Response:", ans['text'])
Conclusion
Building an LLM-Powered Analytics Dashboard combines the best of AI and traditional database querying. By integrating Langchain and OpenAI’s GPT models, you can create a powerful tool that allows users to interact with data using natural language, eliminating the need to understand SQL syntax or database structures. This solution offers a streamlined approach to business analysis, customer support, or general data exploration, making data-driven decisions more accessible and efficient for all.
By enabling faster and simpler access to valuable insights, this dashboard makes interacting with complex data systems easier and more intuitive for non-technical users.
