abean3/da_chatbot

1 2import gradio as gr3from huggingface_hub import InferenceClient4from langchain_community.embeddings import OpenAIEmbeddings5from qdrant_client import QdrantClient6from langchain_community.vectorstores import Qdrant7from langchain_core.prompts import ChatPromptTemplate8from langchain.chains import RetrievalQA#building Retrieval chain9from langchain import PromptTemplate10from langchain.chat_models import ChatOpenAI11 12import os13api_key=os.getenv('aml_api')14 15os.environ["OPENAI_API_KEY"] = api_key16 17qdrant_api_key=os.getenv('qdrant_api_key')18qdrant_url = "https://3f923bd6-7702-4a76-a885-ef9fd6042ae6.europe-west3-0.gcp.cloud.qdrant.io"19 20llm = ChatOpenAI(model_name="gpt-4o-mini", temperature=0)21 22 23# a custom prompt help us to assist our agent with better answer and make sure to not make up answers24custom_prompt_template = """25你是一位資料分析師,請回覆資料處理流程，可以參考以下方式回覆26 27明確說明目標： 清楚說明您想從資料中得到什麼結果，例如計算總和、平均值、篩選特定條件的資料等。28使用的資料表和欄位： 明確指出要處理的資料表和欄位名稱。29篩選條件： 若有需要，可以使用 SQL 語法設定篩選條件，例如 WHERE TXD BETWEEN '20230101' AND '20231231' 用於篩選 2023 年的資料。30計算邏輯： 說明要進行的計算方式，例如使用 COUNT(*) 計算筆數，SUM(AMT) 計算總和等。31資料庫系統及SQL程式： 根據實際使用的資料庫系統撰寫 SQL 語法。32最後是警語：請注意，以上僅根據您提供的資料進行分析，實際操作中可能需要根據資料庫的具体情况进行调整。33 34範例：35 36提供一個串接「顧客基本資料」與「活期性存款帳戶主檔」資料表的範例，並說明資料處理步驟：37步驟一：理解需求38目標是將「顧客基本資料」與「活期性存款帳戶主檔」進行串接，以獲取更完整的顧客資訊。39步驟二：選擇相關資料表40顧客基本資料(tb_cip_bat_custinfo): 包含顧客識別流水號、姓名、身分證號等。41活期性存款帳戶主檔(tb_edls_dept_acc): 包含活期性存款帳號、開戶日期、帳戶狀態等，並可透過顧客統編 (cust_id) 與顧客基本資料串接。42步驟三：決定串接條件43使用顧客統編 (cust_id) 作為串接條件，將兩個資料表關聯起來。44步驟四：選擇所需欄位45根據需求選取所需欄位，例如：46顧客基本資料：顧客統編、顧客姓名、生日。47活期性存款帳戶主檔：活期性存款帳號、開戶日期、帳戶狀態。48步驟五：編寫 SQL 程式碼49SELECT50    cust.cust_id,51    cust.a016 AS cust_name,52    cust.a019 AS birth_dt,53    acc.demand_dept_acc,54    acc.open_acc_date,55    acc.acc_status56FROM57    tb_cip_bat_custinfo AS cust58INNER JOIN59    tb_edls_dept_acc AS acc ON cust.cust_id = acc.cust_id;60程式碼說明:611.使用 SELECT 選擇需要的欄位。622.FROM 指定要查詢的資料表，並使用 AS 為資料表設定別名，方便後續引用。633.INNER JOIN 指定串接方式為內部結合，並使用 ON 指定串接條件。644.cust.cust_id = acc.cust_id 表示以顧客統編作為串接條件，將兩張資料表關聯起來。65步驟六：執行程式碼並驗證結果66執行上述 SQL 程式碼後，即可得到串接後的結果，其中包含了顧客基本資料和活期性存款帳戶的資訊。67 68 69Answer the following questions as best you can. You have access to the following tools: SAS PROC SQL and postgresql70If you don’t know the answer, just say that you don’t know so sorry, don’t try to make up an answer.71 72Context: {context}73Question: {question}74 75Only return the helpful answer below and nothing else.76Helpful and Caring answer:77"""78 79prompt = PromptTemplate(template=custom_prompt_template,80                            input_variables=['context', 'question'])81 82 83 84 85"""86For more information on `huggingface_hub` Inference API support, please check the docs: https://huggingface.co/docs/huggingface_hub/v0.22.2/en/guides/inference87"""88#client = InferenceClient("HuggingFaceH4/zephyr-7b-beta")89 90 91def qa_bot_qdrant_response(context):92    embeddings = OpenAIEmbeddings(model="text-embedding-3-large")93 94    # Connect to the vector database95    client = QdrantClient(url=qdrant_url, api_key=qdrant_api_key)96 97    # Define the collections you want to search across98    #collection_names  = ["schema_01","schema_02","CM_ACCOUNT","CM_CUSTOMER","MDSJR","MYSJR","dept_acc_event","custinfo","custrisk","dept_tx","td_acc","cust_aum","dept_acc","sas_pdf", "sas_html","QA"]99    collection_names  = ["schema_01","schema_02","CM_ACCOUNT","CM_CUSTOMER","MDSJR","MYSJR","sas_pdf", "sas_html","QA"]100 101    all_documents = []102 103    # Iterate over each collection and perform retrieval104    for collection_name in collection_names:105        doc_store = Qdrant(106            client=client,107            collection_name=collection_name,108            embeddings=embeddings109        )110 111        retriever = doc_store.as_retriever(search_kwargs={'k': 15})112 113        # Perform the retrieval for the current collection114        qa = RetrievalQA.from_chain_type(115            llm=llm,116            chain_type='stuff',117            retriever=retriever,118            return_source_documents=True,119            chain_type_kwargs={'prompt': prompt}120        )121 122        response = qa({'query': context})123        all_documents.extend(response["source_documents"])124 125    # Combine and sort documents by score (if needed)126    sorted_documents = sorted(all_documents, key=lambda x: x.metadata.get('score', 0), reverse=True)127 128    # Generate the final response by combining the sorted documents129    final_response_text = " ".join([doc.page_content for doc in sorted_documents])130 131    return  response["result"]132"""133For information on how to customize the ChatInterface, peruse the gradio docs: https://www.gradio.app/docs/chatinterface134"""135demo =gr.Interface(136    #respond,137   title = "數據分析小幫手",138   fn = qa_bot_qdrant_response,139   inputs = gr.Textbox(label="請輸入你的問題:"),140   outputs = gr.Textbox(label="小幫手的回答:")141)142 143 144if __name__ == "__main__":145    demo.launch(auth=(os.getenv('username'),os.getenv('password')))