Mastering Natural Language to SQL with LangChain and LangSmith | NL2SQL | With Code 👇

[@raumwerk09]

Excellent! I've already watched some 10+ other tutorials on LangChain SQL chats, but none of these really got beyond the basic demo for simplistic databases, completely ignoring the problems of token limits and providing custom table info to the LLM for more complex databases...so this one really stands out. Kind Thanks for the insights, and Greetings from Germany :-)

[@FutureSmartAI]

Glad it was helpful!

[@BangaloreYoutube]

Exactly😅

[@JohnBoen]

I have been a database engineer for decades. I dream SQL... You made the *perfect* tutorial for me :)

[@Zuhair-p2u]

GREAT session. You are legend, smart, and helpful. thank you.

[@sakalyamitra9935]

Went through the video and it is greatly explained in detail. The chaining explanation along with parallel visualization in Langsmith made it super easy to understand how the things are getting executed. Great video to master NL2SQL using Langchain.

[@FutureSmartAI]

You're very welcome!

[@shaktidharreddy6822]

@@FutureSmartAI pls need streamlit code, gitrepo doesnt have

[@diptimanraichaudhuri6477]

Very well explained Pradip! Keep up the good work !

[@igotmail9]

Excellent explanation in detail. God bless you

[@shreyasbs2861]

Excellent text to sql project..thanks a lot

[@chandanha9532]

Nice Explanation Sir...

[@mtsoul9834]

This is what i looking for. Really appreciated

[@carloseduardogabrielsantos9939]

That's amazing, congratulations and thank you very much for making your time available to share you knowledge.

[@farhankhan-ey2bf]

Thank you for this great Video. Wating for next video!!!!!!

[@aguntuk10]

very good expalnation Pradip , really helpful . Thanks a ton

[@SarbaniMaiti]

This is awesome Pradip, you just built an application ready to go in production :)..very useful.

[@FutureSmartAI]

Glad it was helpful!

[@kenchang3456]

Hi Pradit, thank you very much for a detailed video. It was valuable and insightful for my POC.

[@FutureSmartAI]

Glad it was helpful!

[@AlphaTutorial-r6h]

really amzing content sir , i learn so much from this vdeo thank sir :) love from hyderabad

[@FutureSmartAI]

So nice of you

[@alexramos587]

Really helpul. Thanks.

[@FutureSmartAI]

Glad it was helpful!

[@dhruvparthasarathy2050]

Hats off

[@bhaskarrai7127]

detailed explanation bro. Thanks for this vedio.

[@FutureSmartAI]

Glad you liked it

[@SanjayRoy-vz5ih]

Absolutely fantastic tutorial...only question that you on "top-k"... remember it is used only in case of vector embedding for neighborhood similarity search, or as it is called cosine similarity... otherwise it is of no use...if you are not embedding

[@kallolbecs]

Great video, can u make one for nl 2 mongodb as well?

[@RibsCribs]

thank you for this video. learn a lot from it.

[@lucaslira5]

great video! what is necessary to start it in production env?

[@FutureSmartAI]

Hi Can you ellabrate more on what you want to know?

[@lucaslira5]

@@FutureSmartAI for example, if I want to create this project in enterprise enviromente usng production enviroment and release to user ask to our db for example in azure cloud. What is necessary to deploy it in production?

[@tanzeelmohammed9157]

Hi Pradip.. I have been trying to do the same thing. The problem I do not have credit in OpenAI and i wanted to know if there is any other way using opensource models to achieve the same result..specially in LangChain

[@FutureSmartAI]

Yes you can use open source llm instead of Open AI

[@santoshkiranm]

Awesome tutorial, thanks a ton! Follow up question - how would you compare this solution with a RAG based approach where table and column metadata is embedded within a vector database and an agent can then use it for semantic search and get dynamic tables and columns to form the query? Also, how do we add column descriptions to this, to increase the chances of the LLM using right columns for the query

[@FutureSmartAI]

A pure NL2SQL-based approach generally works better than a RAG-based approach for structured queries, as it ensures precision and follows a deterministic process. However, for production use cases, a hybrid approach combining SQL and RAG can be beneficial. The key advantage of incorporating RAG is that you can index unique values of specific columns in a vector database. This allows the system to retrieve relevant values even when users make spelling mistakes or use semantically different terms. For example, consider a column called **skills** with a record where the skill is listed as **Programming**. If a user queries for "coding" using a traditional NL2SQL approach, the query might fail because it doesn’t match the column values exactly. By integrating RAG, you can first perform semantic search to find the closest match for "coding" (which would return **Programming**). Then, you can substitute the user’s input with the retrieved value before forming the final SQL query, ensuring a more accurate response. Regarding column descriptions, you can include them in the prompt to guide the LLM towards selecting the most relevant columns. Another approach is to embed column descriptions in a vector database and retrieve the most relevant metadata dynamically before query generation.

[@siddhesh201182]

Thanks ...

[@vijayanandganesan7660]

Great tutorial looks like it’s still hallucinating may be llm fine tuning is another option. When u get time can u drop a video

[@sanketkurle2251]

❤❤

[@AndersonMesquita1972]

Nice project. How do I obtain the T-SQL query that was generated in this chatbot coding?

[@frag_it]

Can you make a video on vllm and kubernetes to be used with lang graph

[@litttlemooncream5049]

learnt about the helpful langsmith, and also your thought of generating sql query. but when dealing with database column recognizing it's not so precise with this method...looking for knowledge graph based ways. but thanks a lot any way! :)

[@mohitmourya9997]

Great video! I have one question, which one is better this approach or SQL Database Agents one? I'm developing a generic solution that takes in a SQL DB and generate insights based on user query

[@FutureSmartAI]

I found this approach more flexible to make changes. But you should start with sql agent and see if it works out of the box for you

[@KumR]

Hey Pradip . Can we connect this to Oracle Fusion ERP

[@user-ps9mk3pi7o]

Looking for memory management thanks for sharing

[@FutureSmartAI]

check my latest tutorial here memory managment discussed kzbin.info/www/bejne/aWnEfoeOjpeoqbc

[@RAJARAMRamamoorthi]

Have you used any GraphQL chain , if so, pls do a detailed view on that , very helpful session thanks

[@shahnawazfaridi]

excellent video, Thank for ur valuable time give this video. I have 1 question, I want to using local databse oracle 12c

[@YatanveerSingh]

Hi Pradip, Thanks for your blog and this video. This helped me to understand how to use NLP to SQL in a very easy way. I am able to use this on my database, however sometime I am getting list index out of range. This is happening specially if there are lot of rows in my tables. So I am not sure if I use this in Production. Can you advice. Thanks Yatan

[@Pratik345-b1y]

How do we deploy this on AWS ? What should be the ideal process which you can suggest ?

[@FutureSmartAI]

I have other videos on aws deployment

[@Shiva-yn4dl]

It's a great video. Could you please give me the link for the notebook? I want to get the csv file

[@FutureSmartAI]

github.com/PradipNichite/KZbin-Tutorials/blob/main/Langchain_NL2SQL_2024.ipynb

[@Zuhair-p2u]

thank you very much, what do you think about chatgroq ? can we use it for the same purpose?

[@FutureSmartAI]

Yes, you can

[@TharunManogaran]

Hi , Thanks for the video, I am trying to query a multi-tenant db and I want only admin to access all the data while others to see only their relevant data, how can I establish this feature on the top of the base model

[@soumyaparida9231]

Great session.But can you tell how to implement it using gemini pro?

[@chihebnouri5541]

i think building sql llm with gemini is better no?

[@tink417]

Hi, struggling with the create_extraction_chain_pydantic and streamlit launch

[@Random-h6g]

It's an excellent tutorial. I think the DB connection can identify the table schema to send in the prompt based on the user request. But I'm wondering what would happen if the table is so huge that the attributes for one table exceed the context length?

[@FutureSmartAI]

You can have one more level of selection , given current context lenth we can have huge number of columns

[@RajeevKumar-ob7wg]

Hello Pradip, thanks for your great session. I am learner in AI, I am using sqlcoder 7b-2 model along with langchain and huggingface, here I am stuck with create_extraction_chain_pydantic function, not getting what we can use in place of this function, if u can guide me it will be great help. Thanks in advance.

[@aishasartaj2985]

Can we do this via Azure Open AI?

[@shaktidharreddy6822]

please post streamlit code also

[@FutureSmartAI]

Everything is there , check link in the description

[@polly28-9]

Is this create_extraction_chain_pydantic deprecated? What to use instead? Any alternatives? I got error: ValueError: A pending deprecation cannot have a scheduled removal. Please, help me!

[@FutureSmartAI]

You can actully use simple prompt that can ouptut name of table

[@Omer23007]

How this model work with big query?

[@aryanmishra4594]

I also want it to answer questions other that database outside of this database context how to do that

[@hamzamarwat4337]

Can I use Llama 3.1 instead of openai, and follow the same pipeline. By the way nice video, everything is explained very well.

[@FutureSmartAI]

Yes, since we are using langchain we can easily switch to other models

[@hamzabinumar910]

@@FutureSmartAI I tried this approach, unfortunately I only have access to the Meta-llama 3.1 8b. And that LLM can only understand the basics, it hallucinates if for queries in which two or more conditions are involved. I have used the same approach as yours for my project. Could you guide me on the approach for this. If you are comfortable with sharing your email for contact, that would be best, but if you could just guide me here, that would also do. TIA

[@divakarrex9546]

I'm working on building a SQL Retrieval-Augmented Generation (RAG) system where, instead of directly connecting to the database, I use a .sql file containing table definitions to form the knowledge base, as I cannot expose the actual data. However, the language model is struggling to provide accurate answers based on these table definitions. Could you suggest ways to improve the model's performance?

[@kalyan6328]

Will it be able to generate charts like if we ask to draw bar chart or line chart?

[@FutureSmartAI]

Yes, you can have one more prompt that can take db results and ask it to convert in format suitable for plotting chart. Then any plotting librt can plot it

[@adatalearner]

Is it possible to use zero-shot for text-2-sql ?

[@FutureSmartAI]

Yes its possible if your tables and column names are self explanatory

[@sg7571]

this is really great. Can you share your VS code for this tutorial?

[@FutureSmartAI]

link in the description

[@proxy5061]

Hi sir, am java developer some time i do frontend but to use llms, and all these studf do we need knowledge of ml, ds, ai? Python

[@TheEverydayAnalyst]

Can MS Teams be used instead of Streamlit as the user interface? If yes, then how are multiple user sessions handled in there? Can you please consider this topic to make another video?

[@FutureSmartAI]

Yes making video that will explain how to handle sessions for mulitple users. not sure about MS Teams. You can expose your langchain app as api and then you can integrate with any UI

[@TheEverydayAnalyst]

@@FutureSmartAI Another thing which I think would really help people following your playlist - "How to enable data access control based on the user?" I think it's the next thing one would think after creating concurrency. Let's say we are dealing with a HR database of an MNC and we have 2 HRs who are interacting with our bot. X HR works for India office and Y HR works for the US office. Now if X HR asks how many active employees do we have? The answer should be based on the context of their role/dept/team/tower etc.,

[@hse13166]

how to display the column names.. the output records are coming , but not the column names

[@nicolassuarez2933]

Outstanding! Can you give us the requirements.txt with versions. Thanks

[@FutureSmartAI]

Yes i should have mentioend versios, I am updating notebook with latest version

[@nicolassuarez2933]

@FutureSmartAI Thanks!

[@dvm509]

Does it also work, if i have multiple tables and i want to use join operations on some of the columns to get answer ?

[@FutureSmartAI]

yes it works

[@19saikalyan84]

is this possible to do without an api key, by using the downloaded llama model through hugging face?

[@FutureSmartAI]

Yes

[@naveennoelj]

Hi Pradip, very detailed explanation on NL2SQL, most comprehensive I have seen. Thank You. One quick Qs: Is it possible to take the output/results of the SQL query and display a chart (bar/pie/line) only if the results can be put on a chart or if the user asks for it. what would it take to do this?

[@FutureSmartAI]

Yes. First you should access intermediate steps that gives db results and then ypu can visualise it using normal python libraries

[@pavancharan4236]

Hi Pradip. Thanks for the explanation. So you say, we crease a csv file for choosing correct tables for handling larger datasets. Correct me if I am wrong. My doubt is How big the csv file can be? How many tables can we use? Does it work good even for 100s of rows in csv file?

[@FutureSmartAI]

lets say you have 200 tables and each table has 100 columns, so bsically 20000 lines of information or schema which is not feasible to put inprompt sometime and even if its possbile it will increase cost. As we know most of the queries cabe answered max using 10 tables . So you can create table selection prompt with 2-3 lines for each table description . Once you select table you need to only add schema of those tables in final prompt

[@m.aakaashkumar6125]

This is a really nice tutorial and a set of code. Especially the implementation of memory to make it super efficient. I am trying to make some minor changes. I am making use of Snowflake Databases. In this case, the url for connection with sqlalchemy has the database name and schema name as compulsory. This doesn't allow me to access data across multiple schemas for the Chatbot. If I go for multiple db objects, each for one schema, Is it possible to combine them in a single chain for creating the generate_query object??

[@rsivci]

HiPradip, Excellent explanation, I have PrestoDB in our organisation, I some how dont see the connection part between Langchain and this prestoDB, any references you can help me with?

[@FutureSmartAI]

you should look for PrestoDB SQL Alchemy URI . check this www.twilio.com/en-us/blog/access-presto-database-python-sqlalchemy

[@wealth_developer_researcher]

Thanks for nice video. I have one question. What if I am treating this .py file as API Endpoint. And want to use same chain per user. Here I can see you have used Streamlit cache resource so it comes from cache on same server. But my requirement is to use this as endpoint so that i can call it from any server

[@tirupathiraopatnala8976]

Why do we want to maintain a csv file with table name & table desc? get_table_info will have the table names, can't we make sure that model picks right tables from there? Is this not killing model ability to see sample records and create table statements?

[@FutureSmartAI]

Most of the times production table names are not self explanatory , same with columns. Giving table descriptions helps model to select relevant tables

[@MuhammadMuzamilRashid-d8c]

What if someone ask questions unrelated to data ? Guide please

[@FutureSmartAI]

In Prompt you can mentioned that if question cant be answered with retried data or context simply say I dont know. You can also use router prompt that will decide whether question is relevant or not before doing Nl2SQL

[@ShivamGupta-ek4rd]

How Can I use this with IBM DB2 Database?

[@prashanthganna2545]

Hi Pradip, Im using Azure OpenAI. How can i generate dynamic few shot examples?

[@rubberuk]

It all has to go in the system prompt so if you’ve got a large schema plus many examples it adds up to a lot of tokens on every request.

[@dataacademy369]

Do you provide any online training on NLP, if someone aspire to become a freelancer?

[@FutureSmartAI]

Hi No, Did you check my freelancing playlsit? I shared my experiences and tips there

[@Rasmiya_M]

Sir can I get a data science internship at your company?

[@FutureSmartAI]

I post on linkedin when we have openings

[@deepshikhaagarwal4125]

Thanks for amazing video! followed your code bas and all looks good.only problem is create_extraction_chain_pyndantic is not able to extract table name

[@FutureSmartAI]

You can even create seperate prompt and instruct in plain english that output table names as comma seperated values

[@arunsnmimtimt]

Hi Pradip,can we do it without api? I cant expose data

[@FutureSmartAI]

you mean open ai api? then you will require local llm

[@kamaleshnew]

How do you map column values so user need not mention column names for every query

[@FutureSmartAI]

Hi can you example of it?

[@kamaleshnew]

@@FutureSmartAI you have a word 'india' in let say 10 coulmns ....which column to route

[@kamaleshnew]

@@FutureSmartAI also not all texts has been easily identified and showing data ....

[@umeshkumar-nv7ee]

Hi I wanted work on the establishing an SQL agent with chatgpt, but I wanted make a project in such a way where if I ask question related to database then I get response based on data I have in DB, and if I prompt something generic it shud give a generic answer... Is that possible?

[@Thangarajtest]

Hello Sir, Could you pls share the video on how to deploy the this application using langserve?

[@FutureSmartAI]

I have already one video on langserve

[@YashNanaware-fe5jz]

Hi, I have a problem I have data at cloud datamart I want to load it into the db but I am not able to do it as I get connected to the datamart by using UI where in I pass my ID and auntention is done through azure credentials token. Is there any way I can do it as I don't have password for it.

[@aarzoojhamb5581]

How can we do this task without using openai model and rather open source LLMs ?

[@FutureSmartAI]

Yes you can use open source llm also from langchain_community.llms import Ollama llm = Ollama(model="llama2")

[@polly28-9]

Can you tell me with what LLM model learn ( try to know ) the structure of our database? How the LLM model knows the structure of our database? Only with connection and it learn it? I am confused

[@FutureSmartAI]

You can see in video when we connect db . langchain can extract scehma sample records from table , this details gets added in prompt for llm

[@Animesh_SGIS]

Sir, Open Api is not free as of today so what to do?

[@FutureSmartAI]

you can use open source llm, but in my experience many of my clients prefer to use open ai in production

[@ShreepatiHere]

Hi Pradip, Thanks for the video. I am getting this exception , Could you please help me to fix? "langchain_core.exceptions.OutputParserException: This output parser can only be used with a chat generation." Instead of OpenAI, I am using Google Palm LLM and Huggingface Embeddings

[@FutureSmartAI]

you need to use llm that support chat messages

[@n3cr0ph4g1st]

The query after you do invoke is "SQLQuery: " so it never executes anything. you need to include all your package versions as well. Thanks for the replies

[@FutureSmartAI]

Hi we dont need any cutom code for langchain . you only need below code snippet which is already in notebook os.environ["LANGCHAIN_TRACING_V2"] = "true" os.environ["LANGCHAIN_API_KEY"] = "" I agree I should include package version. I will update notebook with langchain and other relevant package version

[@n3cr0ph4g1st]

@@FutureSmartAI I tried implementing and the AI output always includes "sqlquery:" before the query so the query can never be executed in the lcel chain... Are you seeing this behavior? Could it be related to me using gpt4 instead of 3.5?

[@FutureSmartAI]

​@@n3cr0ph4g1st Yes we have seen this behavoir with GPT3.5. basically we are getting something extra with sql query and it resulting in failure while executing. you can use GPT-4. Also you can add post processing fucntion after query generation and before query execution using python.langchain.com/docs/expression_language/how_to/functions

[@FutureSmartAI]

you can even modify prompt to explicitly say to dont write anything other than sq query itself

[@rameshh3821]

Hi Pradip. I'm not able to add memory to my chatbot. I'm getting this error- "The SQL result is an error message indicating that the SQL query was not executed successfully. Therefore, the user question cannot be answered based on the given information." Please help.

[@rameshh3821]

Anyone getting the same error? Please respond.

[@SanaAttar-c1n]

Thanks for video.can please answer my below querie .how can we create dynamic examples,bcz what if we don't know the db domain

[@FutureSmartAI]

We can use chatGPT to comeup with few shot examples . Eg we can give tables schema and sample records and ask chatGPT to comeup with pairs of natural and sql queries. But few shot examples needs to be correct and better we curate them or verify them and use. Other approach we can use feedback mechanism, everytime we show user result we can ask for user feedback and later use positive feedback as few shot examples

[@SanaAttar-c1n]

@@FutureSmartAI Thanks for your response.i have seen that you made 3 videos of converting text to SQL ..1.using langchain agent 2)using langchain chain(current video) 3) using llamaaindex....so which one is better

[@FutureSmartAI]

@@SanaAttar-c1n Since those libraries keep changing syntax you should always refer latest video

[@debarunkumer2019]

Hi, it can be observed in the "table_details.py" file written in VS code, that there is no mention of Message placeholder to save the chat history while defining the prompt for selected tables. That may cause the chain() module to train on the entire list of tables in the database when the input is "List the names." Will it not? If someone may kindly advise me on this issue. Thank you so much for such an informative session. Cheers!!

[@FutureSmartAI]

No sure exactly what are you asking, you want to limit tables that is being used? db = SQLDatabase.from_uri(f"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}",sample_rows_in_table_info=1,include_tables=['customers','orders'],custom_table_info={'customers':"customer"})

[@manikandanr1242]

can we create a chatbot so it can answer friendly question 'hi'

[@FutureSmartAI]

Yes

[@prathamgupta3984]

Can anyone help me it shows error of that open AI key credit exhausted I tried every possible way to get some free credit but always failed

[@AmruthSrinivas]

but these tables do not understand the relationships between the 2 tables like the foreign keys and all , they are just going to answer the direct question on a particular table

[@FutureSmartAI]

It works will with table relationships since we are giving it schema and it has all required info

[@ansumansatpathy3923]

I see two audio messages overlap around 56:47, very distracting. Also pls plan your content ahead to avoid revisiting after breaking existing flow.

[@FutureSmartAI]

Thanks for pointing. Actually my recorder stopped workign after some and re recorded some part that resulted in some overlap when i combined them

[@Mostafa_Sharaf_4_9]

Can we make this code as api using fastapi and deploy it on AWS . If yes, please make more vadios about api ❤

[@FutureSmartAI]

Yes it can be integarted in fastapi, Its just normal python code. I have some other videos where I inetgarted open ai and fastapi deployed on aws vm

[@jamelec-h3q]

please i need the souce code zip file

[@FutureSmartAI]

Link in the description

[@rubberuk]

Be very careful before putting this in production environment, almost impossible to constrain.

[@FutureSmartAI]

One basic thing we can do use to use db user that has only read or limited permissions

[@rubberuk]

If you’re using sql server you could use a security policy and a predicate but ultimately you’ll end up with something that you can’t 100% secure especially in a multi tenant environment. You can also wrap the sql statement in a read only transaction to stop modifications.

[@ajaym4257]

Hello, I want to "Identifying Relevant Database Tables Dynamically". When i run the same code as you as show below i basically get this [ ] square bracket as output. Can you help? table_details_prompt = f"""Return the names of ALL the SQL tables that MIGHT be relevant to the user question. \ The tables are: {table_details} Remember to include ALL POTENTIALLY RELEVANT tables, even if you're not sure that they're needed.""" table_chain = create_extraction_chain_pydantic(Table, llm, system_message=table_details_prompt) tables = table_chain.invoke({"input": "what is relative utilization is coming from which table?"}) tables

[@rameshh3821]

Hi. I'm also getting the same error.

[@FutureSmartAI]

What natural query are you asking ? also you can ignore dynamic table selection since it will not work with follow up question. we will need to add hstory to table selection prompt to make it working

[@RaushanKumar-ut2ke]

When i have used semanticsimilarityexampleselector using AzureOpenAIEmbedding i got ->ValueError: Expected EmbeddingFunction.__call__ to have the following signature: odict_keys(['self', 'input']), got odict_keys(['args', 'kwargs']), Can you explain why i am getting this error.

[@shahnawazfaridi]

excellent video, Thank for ur valuable time give this video. I have 1 question, I want to using local databse oracle 12c