With the rapid development of large model technology, how to fully utilize AI while ensuring data privacy has become a hot topic. Open-source local large language models (LLMs) are gradually becoming an important tool to solve this problem.
Today, we will introduce a star-level open-source model—LLaMA2, and see how it seamlessly implements the “text to SQL” magic operation in a local environment!
In many scenarios that require processing sensitive data, such as finance, healthcare, or internal corporate data analysis, data privacy is of utmost importance. At this time, open-source local large models are undoubtedly the best choice. LLaMA2 is not only powerful but also avoids the risk of data transmission to the cloud through local deployment, providing double insurance for privacy protection.
SQL, as the foundational language for data querying, has a very wide range of usage scenarios. However, writing SQL queries can be quite a challenge for users without a technical background. By running the LLaMA2 model locally, we can achieve a quick transformation from natural language to SQL. For example, you only need to input a sentence: “Query the top 10 products by sales in the last 30 days”, and LLaMA2 can generate accurate SQL statements, greatly lowering the barrier to entry.
If you also want to experience this technology, you can refer to the latest “LLaMA2 Text to SQL Practical Guide”. It provides detailed instructions on how to run different versions of LLaMA2 locally and how to combine it with SQL statement generation. Even if you are a technical novice, you can easily deploy and start experimenting.
To use a non-private external API, we can use Replicate.
pip install langchain replicate# Local
from langchain_community.chat_models import ChatOllama
llama2_chat = ChatOllama(model="llama2:13b-chat")
llama2_code = ChatOllama(model="codellama:7b-instruct")
# API
from langchain_community.llms import Replicate
# REPLICATE_API_TOKEN = getpass()
# os.environ["REPLICATE_API_TOKEN"] = REPLICATE_API_TOKEN
replicate_id = "meta/llama-2-13b-chat:f4e2de70d66816a838a89eeeb621910adffb0dd0baba3976c96980970978018d"
llama2_chat_replicate = Replicate(
model=replicate_id, input={"temperature": 0.01, "max_length": 500, "top_p": 1}
)# Simply set the LLM we want to use
llm = llama2_chatTo create this specific database, you can use the following code and follow the steps shown here.
from langchain_community.utilities import SQLDatabase
db = SQLDatabase.from_uri("sqlite:///nba_roster.db", sample_rows_in_table_info=0)
def get_schema(_):
return db.get_table_info()
def run_query(query):
return db.run(query)Query SQL Database
# Prompt
from langchain_core.prompts import ChatPromptTemplate
# Update the template based on the type of SQL Database like MySQL, Microsoft SQL Server and so on
template = """Based on the table schema below, write a SQL query that would answer the user's question:
{schema}Question: {question}SQL Query:"""
prompt = ChatPromptTemplate.from_messages(
[
("system", "Given an input question, convert it to a SQL query. No pre-amble."),
("human", template),
])
# Chain to query
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnablePassthrough
sql_response = (
RunnablePassthrough.assign(schema=get_schema)
| prompt
| llm.bind(stop=["\nSQLResult:"])
| StrOutputParser())
sql_response.invoke({"question": "What team is Klay Thompson on?"})Output:
' SELECT "Team" FROM nba_roster WHERE "NAME" = 'Klay Thompson';'# Chain to answer
template = """Based on the table schema below, question, sql query, and sql response, write a natural language response:
{schema}Question: {question}SQL Query: {query}SQL Response: {response}"""
prompt_response = ChatPromptTemplate.from_messages(
[
(
"system",
"Given an input question and SQL response, convert it to a natural language answer. No pre-amble.",
),
("human", template),
])
full_chain = (
RunnablePassthrough.assign(query=sql_response)
| RunnablePassthrough.assign(
schema=get_schema,
response=lambda x: db.run(x["query"]),
)
| prompt_response
| llm)
full_chain.invoke({"question": "How many unique teams are there?"})Next, we can add memory.
# Prompt
from langchain.memory import ConversationBufferMemory
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
template = """Given an input question, convert it to a SQL query. No pre-amble. Based on the table schema below, write a SQL query that would answer the user's question:
{schema}"""
prompt = ChatPromptTemplate.from_messages(
[
("system", template),
MessagesPlaceholder(variable_name="history"),
("human", "{question}"),
])
memory = ConversationBufferMemory(return_messages=True)
# Chain to query with memory
from langchain_core.runnables import RunnableLambdasql_chain = (
RunnablePassthrough.assign(
schema=get_schema,
history=RunnableLambda(lambda x: memory.load_memory_variables(x)["history"]),
)
| prompt
| llm.bind(stop=["\nSQLResult:"])
| StrOutputParser())
def save(input_output):
output = {"output": input_output.pop("output")}
memory.save_context(input_output, output)
return output["output"]
sql_response_memory = RunnablePassthrough.assign(output=sql_chain) | save
sql_response_memory.invoke({"question": "What team is Klay Thompson on?"})# Chain to answer
template = """Based on the table schema below, question, sql query, and sql response, write a natural language response:
{schema}Question: {question}SQL Query: {query}SQL Response: {response}"""
prompt_response = ChatPromptTemplate.from_messages(
[
(
"system",
"Given an input question and SQL response, convert it to a natural language answer. No pre-amble.",
),
("human", template),
])
full_chain = (
RunnablePassthrough.assign(query=sql_response_memory)
| RunnablePassthrough.assign(
schema=get_schema,
response=lambda x: db.run(x["query"]),
)
| prompt_response
| llm)
full_chain.invoke({"question": "What is his salary?"})In this article, we explored how to use the open-source local large language model LLaMA2 to achieve rapid transformation from text to SQL, and emphasized the importance of local deployment in data privacy protection. From basic principles to practical guides, this technology demonstrates its immense potential in efficient data querying and low technical barrier application scenarios.
Are you also looking for a tool that can protect data privacy while improving work efficiency? The text to SQL feature of LLaMA2 might just be your ideal choice! Whether you are a business analyst or a regular user, you can experience the charm of AI empowerment in just a few simple steps.
👉 What are your thoughts on the role of local LLMs in data privacy protection?👉 In your daily work, in which scenarios would you use the text to SQL feature?👉 If you had the chance to try LLaMA2, what feature would you most want to test?
Feel free to share your thoughts or experiences in the comments section; we look forward to exploring more possibilities with you!
Reference: https://github.com/langchain-ai/langchain/blob/master/cookbook/LLaMA2_sql_chat.ipynb