[MUSIC]
Welcome to this video, where you will learn about implementing LangChain's AI-powered
SQL agent. After watching this video, you'll be able to explain the setup process for using
an AI-powered LangChain SQL agent and describe how to run a natural language query using
the LangChain SQL agent.
Let's explore the setup process.
First, create the Python virtual environment using the command virtualenv my_env to manage
project dependencies.
Install the required libraries including the ibm-watsonx-ai, LangChain, and mysql-connector-python
to facilitate AI integration and database connectivity.
Now it's time to launch your SQL server in the development environment.
In this instance, open the MySQL window and select Create.
Next, launch the MySQL server within the development environment. Allow approximately 15 seconds
for the database to become active and ready for use.
Select the MySQL CLI button so that you can interact with the server in a terminal window.
A new terminal tab opens to the MySQL monitor. You will see MySQL as the root location.
This implementation uses the Chinook database, a sample dataset representing a digital media store.
It includes various tables that we'll query using our AI-powered system.
The graph shows the Entity Relationship Diagram (or ERD) that illustrates how the different
components such as the album name, artist information, track information, and media
information in the Chinook database connect and interact with each other.
To load the database, begin by selecting wget and the URL of the Chinook MySQL SQL
file to fetch the SQL script.
Next, in the MySQL terminal window, _SOURCE chinook-mysql.sql to load the contents
of the SQL script into your database.
Then run the command _SHOW DATABASES; to verify the creation of the database.
Now run an SQL query so that you can verify that you can read the data. For example, suppose
you want to know how many albums the Chinook database contains. To find this information,
you could run the following SQL commands:
USE Chinook;
SELECT COUNT(*) FROM Album
Now it's time to load the IBM watsonx.ai Granite large language model.
To load the Granite large language model using IBM watsonx.ai, follow these steps.
First create a dictionary to store credential information.
Then set up the model credentials.
Now initialize the model parameters.
These parameters control how the model generates text during processing.
First set the MAX_NEW_TOKENS parameters to define the maximum number
of tokens that the model can generate in a single run.
Configure the TEMPERATURE parameter to adjust the randomness of the generated text.
Lower values make the output more predictable and deterministic, while higher values introduce
greater creativity and variability.
Next, specify the project and space IDs.
The space ID identifies the project namespace.
In this example, the project ID is "skills-network".
Set the space ID to "None" if no specific space ID is required.
If you are working in a local or unsecured environment, you might need to disable SSL
verification by setting verify to false.
Next, load the large language model using IBM watsonx.
To begin, import the necessary classes.
Specify the model from the ibm_watsonx_ai.foundation_models to initialize the watsonx.ai model.
Then specify the watsonxLLM from langchain_ibm to integrate the model with LangChain.
Initialize the watsonx.ai model by providing the model_id, which is the identifier for the specific model.
Credentials, which is a dictionary containing authentication details, such as the API key.
Params, such as generation parameters, such as max_new_tokens and temperature,
and the project_id, which is your IBM watsonx project ID,
and optionally the space ID if one exists.
To use the watsonx.ai model within the LangChain framework, wrap the model using
the watsonxLLM class which provides seamless integration with LangChain's features including
chains and tools including chain-of-thought processing, text generation, and reasoning steps.
This means you can build interactive chatbots, connect it to SQL agents for database querying,
and integrate the model with vector stores for semantic search.
Complete the following five steps to connect to the MySQL database.
First, specify the MySQL connection parameters.
To establish a connection, first define the necessary parameters including:
mysql_username: your MySQL username
mysql_password: your MySQL password
mysql_host: the IP address or hostname for your MySQL server
mysql_port: the port number for MySQL (default is 3306)
and the database_name: the name of the database you created, in this case, is Chinook.
Next, build the URI (Uniform Resource Identifier), which is a connection string that combines
all the parameters into a single string for easy connection setup.
Use LangChain's SQLDatabase.from_URI method for seamless integration to the MySQL server.
By combining an AI model with the database connection, you create an SQL agent using
LangChain's create_sql_agent function.
This agent will handle the translation of natural language queries into SQL.
First, set up the SQL agent from LangChain.
Import create_sql_agent from LangChain.
You are creating an SQL agent, which connects the LLM to a database so the agent can answer
database-related questions using natural language.
Next, pass in the LLM and the database shown as db.
Set verbose to True if you want to see more details while the code runs.
And finally, set the agent type to a zero-shot agent that performs a reasoning step before acting.
Test your setup with a simple query: "How many Albums are listed in the database?"
The system sends a natural language query to the agent.
Then the LLM translates or invokes the query to SQL.
The query runs against the database.
The SQL agent responds with the correct count, demonstrating successful integration.
By setting verbose equal to True in the code, you get to see not only the final answer but
also the complete thought process including all actions taken by the LLM and the actual
SQL queries it generates to arrive to that answer.
When running correctly, the agent will confirm that there are 347 albums in the database,
matching exactly what a direct SQL query would return.
And the chain finishes.
Let's recap what you've learned.
Begin the implementation process by creating a Python virtual environment.
This is done by running the virtualenv command.
Install the necessary libraries, which include ibm-watsonx-ai, LangChain, and mysql-connector-python.
These libraries enable the AI model's integration, LangChain functionality, and database connectivity.
After the virtual environment is set up, you need to launch the MySQL server to access
the MySQL CLI (Command Line Interface).
To allow LangChain to interact with the database, you must build a database connector.
LangChain requires an agent to interact with the database using natural language.
Import the create_sql_agent function from LangChain's community toolkit.
Initialize the agent with the LLM instance, the database connection, and the agent type.
Enabling the verbose parameter to view detailed logs of the query translation and execution,
and to run a query, you use a natural language query along with the SQL agent.
[MUSIC]