{"id":3624,"date":"2024-09-26T08:25:21","date_gmt":"2024-09-26T15:25:21","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=3624"},"modified":"2024-11-08T09:22:52","modified_gmt":"2024-11-08T17:22:52","slug":"build-a-chatbot-on-your-own-data-in-1-hour-with-azure-sql-langchain-and-chainlit","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/build-a-chatbot-on-your-own-data-in-1-hour-with-azure-sql-langchain-and-chainlit\/","title":{"rendered":"Build a chatbot on your own data in 1 hour with Azure SQL, Langchain and Chainlit"},"content":{"rendered":"<p>Chatbots are the hot topic lately, and now you can create them easily by downloading solutions like <a href=\"https:\/\/openwebui.com\/\">OpenWebUI<\/a>, connect it to <a href=\"https:\/\/ollama.com\/\">Ollama<\/a> or any OpenAI compatible API, choose your favorite language model, and then run it. It just takes a few minutes and it&#8217;s done.<\/p>\n<p>But building chatbots is not enough, you most likely want to build a chatbot <em>on your own data<\/em>. Luckly, the software ecosystem around AI and chatbot is growing every day, and today creating a chatbot that allow your users to chat with data stored in your database is very easy, thanks to libraries like <a href=\"https:\/\/python.langchain.com\/docs\/introduction\/\">LangChain<\/a>, <a href=\"https:\/\/docs.chainlit.io\/get-started\/overview\">ChainLit<\/a> and, of course, Azure SQL.<\/p>\n<p>To get started, in fact, these three things are the only thing you need:<\/p>\n<ul>\n<li>Langchain: aframework for developing applications powered by large language models (LLMs).<\/li>\n<li>Chainlit: an open-source async Python framework which allows developers to build scalable Conversational AI or agentic applications.<\/li>\n<li>Azure SQL: it added vector support as a core engine feature (check the <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/announcing-eap-native-vector-support-in-azure-sql-database\/\">Early Adopter Preview<\/a> post)<\/li>\n<\/ul>\n<p>You can optionally use Azure Functions to have the data added or updated to the database automatically processed for usage with AI models, if you like a change-feed kind of approach. The final architecture, simple and elegant, will look like the following:<\/p>\n<p style=\"text-align: center;\"><a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/09\/architecture.png\"><img decoding=\"async\" class=\"alignnone size-full wp-image-3625\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/09\/architecture.png\" alt=\"Image architecture\" width=\"986\" height=\"584\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/09\/architecture.png 986w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/09\/architecture-300x178.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2024\/09\/architecture-768x455.png 768w\" sizes=\"(max-width: 986px) 100vw, 986px\" \/><\/a><\/p>\n<h2>It only takes an hour (or less)<\/h2>\n<h3>Start from the database<\/h3>\n<p>That is right, it only takes less than one hour to start to have a fully working chatbot on your own data. Since we&#8217;re focusing on the data side of things, let&#8217;s start from the feature that make this capability possible: the newly introduced <code>vector<\/code> type and the related <code>vector_distance<\/code> function:<\/p>\n<pre class=\"prettyprint language-sql\"><code class=\"language-sql\">declare @qv vector(1536)\r\nexec web.get_embedding 'RAG on your own data', @qv output\r\n\r\nselect top(5)\r\n    se.id as session_id, \r\n    vector_distance('cosine', se.[embeddings], @qv) as distance\r\nfrom \r\n    web.sessions se\r\norder by\r\n    distance <\/code><\/pre>\n<p>As you can see, the code is really simple. It turns the provided text into an embedding by calling OpenAI endpoint, via the stored procedure <code><a href=\"https:\/\/github.com\/Azure-Samples\/azure-sql-db-rag-langchain-chainlit\/blob\/main\/database\/sql\/060-get_embedding.sql\">get_embedding<\/a><\/code> and then use the returned vector to find all the similar vectors in the sample table. In this case I&#8217;m using a table with data related to conference sessions.<\/p>\n<h3>Integrate with LangChain<\/h3>\n<div class=\"content user-select-text\" tabindex=\"0\" aria-description=\"\" aria-label=\"Sent by Copilot: Sure! Here's the sentence in active voice: &quot;The next step is to figure out how to plug this code into LangChain, which orchestrates the RAG pattern. Thanks to LCEL - Langchain Expression Language - it's pretty easy to define the prompt to be sent to the LLM along with additional context information extracted from the database.&quot; Is there anything else you need help with?\">\n<div class=\"ac-container ac-adaptiveCard\">\n<div class=\"ac-textBlock\">\n<p>The next step is to figure out how to plug this code into LangChain, which orchestrates the RAG pattern. Thanks to LCEL &#8211; <a href=\"https:\/\/python.langchain.com\/v0.1\/docs\/expression_language\/\">Langchain Expression Language<\/a> &#8211; it&#8217;s pretty easy to define the prompt and send it to the LLM along with additional context information extracted from the database:<\/p>\n<\/div>\n<\/div>\n<\/div>\n<pre class=\"prettyprint language-py\"><code class=\"language-py\">prompt = ChatPromptTemplate.from_messages([\r\n(\r\n    \"ai\", \r\n    \"\"\" \r\n    You are a system assistant who helps users find the right session to watch from the conference, \r\n    based off the sessions that are provided to you. Sessions will be provided in an assistant message \r\n    in the format of `title|abstract|speakers|start-time|end-time`. You can use only the provided session \r\n    list to help you answer the user's question. If the user ask a question that is not related to the \r\n    provided sessions, you can respond with a message that you can't help with that question. \r\n    Your aswer must have the session title, a very short summary of the abstract, the speakers, \r\n    the start time, and the end time. \r\n    \"\"\"\r\n), \r\n( \r\n    \"human\", \r\n    \"\"\" \r\n    The sessions available at the conference are the following: \r\n    {sessions} \r\n    \"\"\" \r\n), \r\n( \r\n    \"human\", \r\n    \"{question}\" \r\n) \r\n])\r\n\r\n# Use an agent retriever to get similar sessions\r\nretriever = RunnableLambda(get_similar_sessions, name=\"GetSimilarSessions\").bind() \r\n\r\nrunnable = {\"sessions\": retriever, \"question\": RunnablePassthrough()} | prompt | openai | StrOutputParser()<\/code><\/pre>\n<p>the line where <code>runnable<\/code> is defined is where you can see LCEL in action. It makes sure that placeholders like <code>{sessions}<\/code> are replaced with actual data before being sent to the LLM (OpenAI in the sample). Actual data that is retrieved using the defined <code>retriever<\/code>, that in turn calls the <code>get_similar_session<\/code> function which is nothing more than a wrapper around the SQL code described before.<\/p>\n<p>Pretty amazing! In a few lines of code, we have defined the prompt, the tool used by LangChain to provide additional context data to the LLM, and the way to inject into the prompt that additional context data. The code of <code>get_similar_session<\/code> is <a href=\"https:\/\/github.com\/Azure-Samples\/azure-sql-db-rag-langchain-chainlit\/blob\/184bd74b5988c3616a8edb846938409c8a1a132a\/chainlit\/utilities.py#L28\">here<\/a>, if you want to take a look. I have used a stored procedure instead of ad-hoc SQL since it makes the solution cleaner, more secure and easier to maintain.<\/p>\n<h3>Add ChainLit<\/h3>\n<p>Last missing piece is integrating the work done with Chainlit, which has native support for LangChain, via the <code>LangchainCallbackHandler<\/code> function. The integration is well explained here: <a href=\"https:\/\/docs.chainlit.io\/integrations\/langchain\">Chainlit- Integrations<\/a>. Basically, it boils down to two points:<\/p>\n<ul>\n<li>Initialize Langchain chain and make it available to use before the chat starts<\/li>\n<li>When users send messages, get their content and pass it to Langchain chain for processing, and return the result<\/li>\n<\/ul>\n<p>That means that integration happens in two methods only:\u00a0 <code>on_chat_start<\/code> and <code>on_message<\/code>. Super easy and straightforward.<\/p>\n<p>Once that is done,&#8230;well, the project is done! Run the Python script and you&#8217;ll be good to go! Of course, this is only a starting point. You surely want to build a completer and more complex LCEL chains to handle the most diverse requests and situations, and the limits now is just your imagination!<\/p>\n<h2>See it in action and try it yourself!<\/h2>\n<p>I had to chance to talk about that in the one of the recent conferences, the <a href=\"https:\/\/github.com\/microsoft\/RAG_Hack\">RAGHack<\/a> conference, where presented the full end-to-end solution, that you can find on GitHub here:<\/p>\n<p><a href=\"https:\/\/github.com\/Azure-Samples\/azure-sql-db-rag-langchain-chainlit\">https:\/\/github.com\/Azure-Samples\/azure-sql-db-rag-langchain-chainlit<\/a><\/p>\n<p>Aside from the code repo, you can also watch the recording on YouTube:<\/p>\n<p><iframe title=\"RAG on Azure SQL Server\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/B47QNc7xRbg?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>Enjoy and start trying the repo on your own data today!<\/p>\n<p><div  class=\"d-flex justify-content-center\"><a class=\"cta_button_link btn-primary mb-24\" href=\"https:\/\/github.com\/Azure-Samples\/azure-sql-db-rag-langchain-chainlit\" target=\"_blank\">Go to GitHub Repo<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Chatbots are the hot topic lately, and now you can create them easily by downloading solutions like OpenWebUI, connect it to Ollama or any OpenAI compatible API, choose your favorite language model, and then run it. It just takes a few minutes and it&#8217;s done. But building chatbots is not enough, you most likely want [&hellip;]<\/p>\n","protected":false},"author":24720,"featured_media":3625,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[601,599,1,605,615],"tags":[630,629,579,569],"class_list":["post-3624","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai","category-azure-openai","category-azure-sql","category-langchain","category-vectors","tag-chainlit","tag-chatbot","tag-langchain","tag-vector"],"acf":[],"blog_post_summary":"<p>Chatbots are the hot topic lately, and now you can create them easily by downloading solutions like OpenWebUI, connect it to Ollama or any OpenAI compatible API, choose your favorite language model, and then run it. It just takes a few minutes and it&#8217;s done. But building chatbots is not enough, you most likely want [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/3624","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=3624"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/3624\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/3625"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=3624"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=3624"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=3624"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}