How to Build LLM Apps that can See Hear Speak
Notebook
Note
This tutorial is meant for Standard & Premium Workspaces. You can't run this with a Free Starter Workspace due to restrictions on Storage. Create a Workspace using +group in the left nav & select Standard for this notebook. Gallery notebooks tagged with "Starter" are suitable to run on a Free Starter Workspace
Demo Architecture
Create and use the database llm_webinar
In [1]:
%%sqlDROP DATABASE IF EXISTS llm_webinar;CREATE DATABASE llm_webinar;
Out [1]:
Action Required
Make sure to select a database from the drop-down menu at the top of this notebook. It updates the connection_url to connect to that database.
Create tables
In [2]:
%%sqlCREATE TABLE `stockTable` (`ticker` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`created_at` datetime DEFAULT NULL,`open` float DEFAULT NULL,`high` float DEFAULT NULL,`low` float DEFAULT NULL,`close` float DEFAULT NULL,`volume` int(11) DEFAULT NULL,SORT KEY (ticker, created_at desc),SHARD KEY (ticker));CREATE TABLE newsSentiment (title TEXT CHARACTER SET utf8mb4,url TEXT,time_published DATETIME,authors TEXT,summary TEXT CHARACTER SET utf8mb4,banner_image TEXT,source TEXT,category_within_source TEXT,source_domain TEXT,topic TEXT,topic_relevance_score TEXT,overall_sentiment_score REAL,overall_sentiment_label TEXT,`ticker` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,ticker_relevance_score DECIMAL(10, 6),ticker_sentiment_score DECIMAL(10, 6),ticker_sentiment_label TEXT,SORT KEY (`ticker`,`time_published` DESC),SHARD KEY `__SHARDKEY` (`ticker`,`time_published` DESC),KEY(ticker) USING HASH,KEY(authors) USING HASH,KEY(source) USING HASH,KEY(overall_sentiment_label) USING HASH,KEY(ticker_sentiment_label) USING HASH);CREATE ROWSTORE REFERENCE TABLE companyInfo (ticker VARCHAR(10) PRIMARY KEY,AssetType VARCHAR(50),Name VARCHAR(100),Description TEXT,CIK VARCHAR(10),Exchange VARCHAR(10),Currency VARCHAR(10),Country VARCHAR(50),Sector VARCHAR(50),Industry VARCHAR(250),Address VARCHAR(100),FiscalYearEnd VARCHAR(20),LatestQuarter DATE,MarketCapitalization BIGINT,EBITDA BIGINT,PERatio DECIMAL(10, 2),PEGRatio DECIMAL(10, 3),BookValue DECIMAL(10, 2),DividendPerShare DECIMAL(10, 2),DividendYield DECIMAL(10, 4),EPS DECIMAL(10, 2),RevenuePerShareTTM DECIMAL(10, 2),ProfitMargin DECIMAL(10, 4),OperatingMarginTTM DECIMAL(10, 4),ReturnOnAssetsTTM DECIMAL(10, 4),ReturnOnEquityTTM DECIMAL(10, 4),RevenueTTM BIGINT,GrossProfitTTM BIGINT,DilutedEPSTTM DECIMAL(10, 2),QuarterlyEarningsGrowthYOY DECIMAL(10, 3),QuarterlyRevenueGrowthYOY DECIMAL(10, 3),AnalystTargetPrice DECIMAL(10, 2),TrailingPE DECIMAL(10, 2),ForwardPE DECIMAL(10, 2),PriceToSalesRatioTTM DECIMAL(10, 3),PriceToBookRatio DECIMAL(10, 2),EVToRevenue DECIMAL(10, 3),EVToEBITDA DECIMAL(10, 2),Beta DECIMAL(10, 3),52WeekHigh DECIMAL(10, 2),52WeekLow DECIMAL(10, 2),50DayMovingAverage DECIMAL(10, 2),200DayMovingAverage DECIMAL(10, 2),SharesOutstanding BIGINT,DividendDate DATE,ExDividendDate DATE);CREATE TABLE `embeddings` (`id` bigint(11) NOT NULL AUTO_INCREMENT,`category` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`question` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,`question_embedding` longblob,`answer` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,`answer_embedding` longblob,`created_at` datetime DEFAULT NULL,UNIQUE KEY `PRIMARY` (`id`) USING HASH,SHARD KEY `__SHARDKEY` (`id`),KEY `category` (`category`) USING HASH,SORT KEY `__UNORDERED` (`created_at` DESC));
Out [2]:
In [3]:
%%sqlSHOW TABLES;
Out [3]:
Tables_in_llm_webinar |
---|
companyInfo |
embeddings |
newsSentiment |
stockTable |
In [4]:
%pip install --quiet elevenlabs==0.2.27 openai==1.32.0 matplotlib scipy scikit-learn langchain==0.2.12 langchain-openai==0.1.20 langchain-community==0.2.11
In [5]:
import datetimeimport getpassimport numpy as npimport openaiimport requestsimport singlestoredb as s2import timefrom datetime import datetimefrom datetime import timedeltafrom dateutil.relativedelta import relativedeltafrom langchain.sql_database import SQLDatabasefrom langchain_openai import OpenAI as LangchainOpenAIfrom langchain.agents.agent_toolkits import SQLDatabaseToolkitfrom langchain.agents import create_sql_agent
Set API keys
In [6]:
alpha_vantage_apikey = getpass.getpass("enter alphavantage apikey here")openai_apikey = getpass.getpass("enter openai apikey here")elevenlabs_apikey = getpass.getpass("enter elevenlabs apikey here")
In [7]:
from openai import OpenAIclient = OpenAI(api_key=openai_apikey)def get_embeddings(inputs: list[str], model: str = 'text-embedding-ada-002') -> list[str]:"""Return list of embeddings."""return [x.embedding for x in client.embeddings.create(input=inputs, model=model).data]
Bring past two months of stock data
In [8]:
# set up connection to SingleStore and the ticker lists2_conn = s2.connect(connection_url)ticker_list = ['TSLA', 'AMZN', 'PLTR']
In [9]:
from datetime import datetimedef get_past_months(num_months):today = datetime.today()months = []for months_ago in range(0, num_months):target_date = today - relativedelta(months=months_ago)months.append(target_date.strftime('%Y-%m'))return monthsnum_months = 2 # Number of monthsyear_month_list = get_past_months(num_months)print(year_month_list)# pull intraday data for each stock and write to SingleStorefor ticker in ticker_list:print(ticker)data_list = []for year_month in year_month_list:print(year_month)intraday_price_url = "https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol={}&interval=5min&month={}&outputsize=full&apikey={}".format(ticker, year_month, alpha_vantage_apikey)r = requests.get(intraday_price_url)try:data = r.json()['Time Series (5min)']except:time.sleep(1) # required to not hit API limitscontinuefor key in data:document = data[key]document['datetime'] = keydocument['ticker'] = tickerdocument['open'] = document['1. open']document['high'] = document['2. high']document['low'] = document['3. low']document['close'] = document['4. close']document['volume'] = document['5. volume']document['open'] = float(document['open'])document['high'] = float(document['high'])document['low'] = float(document['low'])document['close'] = float(document['close'])document['volume'] = int(document['volume'])del document['1. open']del document['2. high']del document['3. low']del document['4. close']del document['5. volume']data_list += [document]# Inside your loop, create the params dictionary with the correct valuesparams = {'datetime': document['datetime'],'ticker': ticker,'open': document['open'],'high': document['high'],'low': document['low'],'close': document['close'],'volume': document['volume']}# Construct and execute the SQL statementtable_name = 'stockTable'stmt = f"INSERT INTO {table_name} (created_at, ticker, open, high, low, close, volume) VALUES (%(datetime)s, %(ticker)s, %(open)s, %(high)s, %(low)s, %(close)s, %(volume)s)"with s2_conn.cursor() as cur:cur.execute(stmt, params)# time.sleep(1) # required to not hit API limits
In [10]:
%%sqlselect count(*) from stockTable
Out [10]:
count(*) |
---|
20629 |
Bring in Company data
In [11]:
def float_or_none(x):if x is None or x == 'None':return Nonereturn float(x)# pull intraday data for each stock and write to SingleStorefor ticker in ticker_list:print(ticker)data_list = []# for year_month in year_month_list:company_overview = "https://www.alphavantage.co/query?function=OVERVIEW&symbol={}&outputsize=full&apikey={}".format(ticker, alpha_vantage_apikey)r = requests.get(company_overview)try:data = r.json()except:time.sleep(3) # required to not hit API limitscontinueif 'CIK' not in data:raise RuntimeError(str(data))data['CIK'] = int(data['CIK'])data['MarketCapitalization']= float_or_none(data['MarketCapitalization'])# Assuming data['EBITDA'] is a string containing 'None'ebitda_str = data['EBITDA']if ebitda_str.lower() == 'none':# Handle the case where EBITDA is 'None', for example, you can set it to 0data['EBITDA'] = 0.0else:# Convert the EBITDA string to a floatdata['EBITDA'] = float_or_none(ebitda_str)PERatio_flt = data['PERatio']if PERatio_flt.lower() == 'none':# Handle the case where EVToRevenue is '-'data['PERatio'] = 0.0 # You can use any default value that makes senseelse:# Convert the EVToRevenue string to a floatdata['PERatio'] = float_or_none(PERatio_flt)data['PEGRatio']= float_or_none(data['PEGRatio'])data['BookValue']= float_or_none(data['BookValue'])data['DividendPerShare']= float_or_none(data['DividendPerShare'])data['DividendYield']= float_or_none(data['DividendYield'])data['EPS']= float_or_none(data['EPS'])data['RevenuePerShareTTM']= float_or_none(data['RevenuePerShareTTM'])data['ProfitMargin']= float_or_none(data['ProfitMargin'])data['OperatingMarginTTM']= float_or_none(data['OperatingMarginTTM'])data['ReturnOnAssetsTTM']= float_or_none(data['ReturnOnAssetsTTM'])data['ReturnOnEquityTTM']= float_or_none(data['ReturnOnEquityTTM'])data['RevenueTTM']= int(data['RevenueTTM'])data['GrossProfitTTM']= int(data['GrossProfitTTM'])data['DilutedEPSTTM']= float_or_none(data['DilutedEPSTTM'])data['QuarterlyEarningsGrowthYOY']= float_or_none(data['QuarterlyEarningsGrowthYOY'])data['QuarterlyRevenueGrowthYOY']= float_or_none(data['QuarterlyRevenueGrowthYOY'])data['AnalystTargetPrice']= float_or_none(data['AnalystTargetPrice'])# Assuming data['TrailingPE'] is a string containing '-'trailing_pe_str = data['TrailingPE']if trailing_pe_str == '-':# Handle the case where TrailingPE is '-'data['TrailingPE'] = 0.0 # You can use any default value that makes senseelse:try:# Attempt to convert the TrailingPE string to a floatdata['TrailingPE'] = float_or_none(trailing_pe_str)except ValueError:# Handle the case where the conversion fails (e.g., if it contains invalid characters)data['TrailingPE'] = 0.0 # Set to a default value or handle as neededdata['ForwardPE']= float_or_none(data['ForwardPE'])data['PriceToSalesRatioTTM']= float_or_none(data['PriceToSalesRatioTTM'])# Assuming data['EVToRevenue'] is a string containing '-'PriceToBookRatio_flt = data['PriceToBookRatio']if PriceToBookRatio_flt == '-':# Handle the case where EVToRevenue is '-'data['PriceToBookRatio'] = 0.0 # You can use any default value that makes senseelse:# Convert the EVToRevenue string to a floatdata['PriceToBookRatio'] = float_or_none(PriceToBookRatio_flt)# Assuming data['EVToRevenue'] is a string containing '-'ev_to_revenue_str = data['EVToRevenue']if ev_to_revenue_str == '-':# Handle the case where EVToRevenue is '-'data['EVToRevenue'] = 0.0 # You can use any default value that makes senseelse:# Convert the EVToRevenue string to a floatdata['EVToRevenue'] = float_or_none(ev_to_revenue_str)# data['EVToEBITDA']= float(data['EVToEBITDA'])# Assuming data['EVToRevenue'] is a string containing '-'ev_to_EBITDA_str = data['EVToEBITDA']if ev_to_revenue_str == '-':# Handle the case where EVToRevenue is '-'data['EVToEBITDA'] = 0.0 # You can use any default value that makes senseelse:# Convert the EVToRevenue string to a floatdata['EVToEBITDA'] = float_or_none(ev_to_EBITDA_str)data['Beta']= float_or_none(data['Beta'])data['52WeekHigh']= float_or_none(data['52WeekHigh'])data['52WeekLow']= float_or_none(data['52WeekLow'])data['50DayMovingAverage']= float_or_none(data['50DayMovingAverage'])data['200DayMovingAverage']= float_or_none(data['200DayMovingAverage'])data['SharesOutstanding']= int(data['SharesOutstanding'])# description_embedding = [np.array(x, '<f4') for x in get_embeddings(data["Description"], model=model)]dividend_date_str = data['DividendDate']if dividend_date_str.lower() == 'none':# Handle the case where EBITDA is 'None', for example, you can set it to 0data['DividendDate'] = '9999-12-31'else:# Convert the EBITDA string to a floatdata['DividendDate'] = str(dividend_date_str)exdividend_date_str = data['ExDividendDate']if exdividend_date_str.lower() == 'none':# Handle the case where EBITDA is 'None', for example, you can set it to 0data['ExDividendDate'] = '9999-12-31'else:# Convert the EBITDA string to a floatdata['ExDividendDate'] = str(exdividend_date_str)data_list += [data]# Inside your loop, create the params dictionary with the correct valuesparams = {"Symbol": data["Symbol"],"AssetType": data["AssetType"],"Name": data["Name"],"Description": data["Description"],"CIK": data["CIK"],"Exchange": data["Exchange"],"Currency": data["Currency"],"Country": data["Country"],"Sector": data["Sector"],"Industry": data["Industry"],"Address": data["Address"],"FiscalYearEnd": data["FiscalYearEnd"],"LatestQuarter": data["LatestQuarter"],"MarketCapitalization": data["MarketCapitalization"],"EBITDA": data["EBITDA"],"PERatio": data["PERatio"],"PEGRatio": data["PEGRatio"],"BookValue": data["BookValue"],"DividendPerShare": data["DividendPerShare"],"DividendYield": data["DividendYield"],"EPS": data["EPS"],"RevenuePerShareTTM": data["RevenuePerShareTTM"],"ProfitMargin": data["ProfitMargin"],"OperatingMarginTTM": data["OperatingMarginTTM"],"ReturnOnAssetsTTM": data["ReturnOnAssetsTTM"],"ReturnOnEquityTTM": data["ReturnOnEquityTTM"],"RevenueTTM": data["RevenueTTM"],"GrossProfitTTM": data["GrossProfitTTM"],"DilutedEPSTTM": data["DilutedEPSTTM"],"QuarterlyEarningsGrowthYOY": data["QuarterlyEarningsGrowthYOY"],"QuarterlyRevenueGrowthYOY": data["QuarterlyRevenueGrowthYOY"],"AnalystTargetPrice": data["AnalystTargetPrice"],"TrailingPE": data["TrailingPE"],"ForwardPE": data["ForwardPE"],"PriceToSalesRatioTTM": data["PriceToSalesRatioTTM"],"PriceToBookRatio": data["PriceToBookRatio"],"EVToRevenue": data["EVToRevenue"],"EVToEBITDA": data["EVToEBITDA"],"Beta": data["Beta"],"52WeekHigh": data["52WeekHigh"],"52WeekLow": data["52WeekLow"],"50DayMovingAverage": data["50DayMovingAverage"],"200DayMovingAverage": data["200DayMovingAverage"],"SharesOutstanding": data["SharesOutstanding"],"DividendDate": data["DividendDate"],"ExDividendDate": data["ExDividendDate"]}# Construct and execute the SQL statementtable_name = 'companyInfo'stmt = f"INSERT INTO {table_name} (ticker, AssetType, Name, Description, CIK, Exchange, Currency, Country, Sector, Industry, Address, FiscalYearEnd, LatestQuarter, MarketCapitalization, EBITDA, PERatio, PEGRatio, BookValue, DividendPerShare, DividendYield, EPS, RevenuePerShareTTM, ProfitMargin, OperatingMarginTTM, ReturnOnAssetsTTM, ReturnOnEquityTTM, RevenueTTM, GrossProfitTTM, DilutedEPSTTM, QuarterlyEarningsGrowthYOY, QuarterlyRevenueGrowthYOY, AnalystTargetPrice, TrailingPE, ForwardPE, PriceToSalesRatioTTM, PriceToBookRatio, EVToRevenue, EVToEBITDA, Beta, 52WeekHigh, 52WeekLow, 50DayMovingAverage, 200DayMovingAverage, SharesOutstanding, DividendDate, ExDividendDate) VALUES (%(Symbol)s, %(AssetType)s, %(Name)s, %(Description)s, %(CIK)s, %(Exchange)s, %(Currency)s, %(Country)s, %(Sector)s, %(Industry)s, %(Address)s, %(FiscalYearEnd)s, %(LatestQuarter)s, %(MarketCapitalization)s, %(EBITDA)s, %(PERatio)s, %(PEGRatio)s, %(BookValue)s, %(DividendPerShare)s, %(DividendYield)s, %(EPS)s, %(RevenuePerShareTTM)s, %(ProfitMargin)s, %(OperatingMarginTTM)s, %(ReturnOnAssetsTTM)s, %(ReturnOnEquityTTM)s, %(RevenueTTM)s, %(GrossProfitTTM)s, %(DilutedEPSTTM)s, %(QuarterlyEarningsGrowthYOY)s, %(QuarterlyRevenueGrowthYOY)s, %(AnalystTargetPrice)s, %(TrailingPE)s, %(ForwardPE)s, %(PriceToSalesRatioTTM)s, %(PriceToBookRatio)s, %(EVToRevenue)s, %(EVToEBITDA)s, %(Beta)s, %(52WeekHigh)s, %(52WeekLow)s, %(50DayMovingAverage)s, %(200DayMovingAverage)s, %(SharesOutstanding)s, %(DividendDate)s, %(ExDividendDate)s)"# Replace table_name with the actual table name you're using.with s2_conn.cursor() as cur:cur.execute(stmt, params)
In [12]:
%%sqlselect * from companyInfo limit 1
Out [12]:
ticker | AssetType | Name | Description | CIK | Exchange | Currency | Country | Sector | Industry | Address | FiscalYearEnd | LatestQuarter | MarketCapitalization | EBITDA | PERatio | PEGRatio | BookValue | DividendPerShare | DividendYield | EPS | RevenuePerShareTTM | ProfitMargin | OperatingMarginTTM | ReturnOnAssetsTTM | ReturnOnEquityTTM | RevenueTTM | GrossProfitTTM | DilutedEPSTTM | QuarterlyEarningsGrowthYOY | QuarterlyRevenueGrowthYOY | AnalystTargetPrice | TrailingPE | ForwardPE | PriceToSalesRatioTTM | PriceToBookRatio | EVToRevenue | EVToEBITDA | Beta | 52WeekHigh | 52WeekLow | 50DayMovingAverage | 200DayMovingAverage | SharesOutstanding | DividendDate | ExDividendDate |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
AMZN | Common Stock | Amazon.com Inc | Amazon.com, Inc. is an American multinational technology company which focuses on e-commerce, cloud computing, digital streaming, and artificial intelligence. It is one of the Big Five companies in the U.S. information technology industry, along with Google, Apple, Microsoft, and Facebook. The company has been referred to as one of the most influential economic and cultural forces in the world, as well as the world's most valuable brand. | 1018724 | NASDAQ | USD | USA | TRADE & SERVICES | RETAIL-CATALOG & MAIL-ORDER HOUSES | 410 TERRY AVENUE NORTH, SEATTLE, WA, US | December | 2024-06-30 | 1877452915000 | 104049000000 | 42.69 | 2.161 | 22.54 | None | None | 4.19 | 58.22 | 0.0735 | 0.0992 | 0.0658 | 0.2190 | 604333998000 | 225152000000 | 4.19 | 0.938 | 0.101 | 197.21 | 42.69 | 38.46 | 3.107 | 7.91 | 3.168 | 17.97 | 1.155 | 201.20 | 118.35 | 184.08 | 171.02 | 10495600000 | 9999-12-31 | 9999-12-31 |
Bring in news sentiment
In [13]:
import datetime# pull intraday data for each stock and write to Mongofor ticker in ticker_list:print(ticker)data_list = []for i in year_month_list:date_object = datetime.datetime.strptime(i, '%Y-%m')print(date_object)output_date = date_object.strftime('%Y%m%d') + "T0000"# Get the next month from the 'date_object'previous_month_date = date_object + relativedelta(months=-1)previous_month_date = previous_month_date.strftime('%Y%m%d') + "T0000"# Update 'date_object' for the next iterationdate_object = previous_month_date# replace the "demo" apikey below with your own key from https://www.alphavantage.co/support/#api-keynews_and_sentiment = 'https://www.alphavantage.co/query?function=NEWS_SENTIMENT&tickers={}&time_from={}&time_to={}&limit=1000&outputsize=full&apikey={}'.format(ticker, previous_month_date, output_date, alpha_vantage_apikey)r = requests.get(news_and_sentiment)try:data = r.json()data = data["feed"]except:time.sleep(2) # required to not hit API limitscontinuefor item in data:item['title'] = str(item['title'])item['url'] = str(item['url'])item['time_published'] = datetime.datetime.strptime(str(item['time_published']), "%Y%m%dT%H%M%S").strftime("%Y-%m-%d %H:%M:%S")if item['authors']:# Check if the 'authors' list is not emptyauthors_str = str(item['authors'][0])else:# Handle the case where 'authors' is emptyauthors_str = "No authors available"item['authors'] = authors_stritem['summary'] = str(item['summary'])item['banner_image'] = str(item['banner_image'])item['source'] = str(item['source'])item['category_within_source'] = str(item['category_within_source'])item['source_domain'] = str(item['source_domain'])item['topic'] = str(item['topics'][0]["topic"])item['topic_relevance_score'] = float(item['topics'][0]['relevance_score'])item['overall_sentiment_score'] = float(item['overall_sentiment_score'])item['overall_sentiment_label'] = str(item['overall_sentiment_label'])item['ticker'] = str(item['ticker_sentiment'][0]['ticker'])item['ticker_relevance_score'] = float(item['ticker_sentiment'][0]['relevance_score'])item['ticker_sentiment_score'] = float(item['ticker_sentiment'][0]['ticker_sentiment_score'])item['ticker_sentiment_label'] = str(item['ticker_sentiment'][0]['ticker_sentiment_label'])params= {"title": item["title"],"url": item["url"],"time_published": item["time_published"],"authors": item["authors"],"summary": item["summary"],"banner_image": item["banner_image"],"source": item["source"],"category_within_source": item["category_within_source"],"source_domain": item["source_domain"],"topic": item["topic"],"topic_relevance_score": item['topic_relevance_score'],'overall_sentiment_score': item['overall_sentiment_score'],'overall_sentiment_label': item['overall_sentiment_label'],'ticker': item['ticker'],'ticker_relevance_score': item['ticker_relevance_score'],'ticker_sentiment_score': item['ticker_sentiment_score'],'ticker_sentiment_label': item['ticker_sentiment_label']}#print(params)# Construct and execute the SQL statementtable_name = 'newsSentiment'stmt = f"INSERT INTO {table_name} (title, url, time_published, authors, summary, banner_image, source, category_within_source, source_domain, topic, topic_relevance_score, overall_sentiment_score, overall_sentiment_label, ticker, ticker_relevance_score, ticker_sentiment_score, ticker_sentiment_label) VALUES (%(title)s, %(url)s, %(time_published)s, %(authors)s, %(summary)s, %(banner_image)s, %(source)s, %(category_within_source)s, %(source_domain)s, %(topic)s, %(topic_relevance_score)s, %(overall_sentiment_score)s, %(overall_sentiment_label)s, %(ticker)s, %(ticker_relevance_score)s, %(ticker_sentiment_score)s, %(ticker_sentiment_label)s)"# Replace table_name with the actual table name you're using.with s2_conn.cursor() as cur:cur.execute(stmt, params)
In [14]:
%%sqlSELECT count(*) Rows_in_newsSentiment FROM newsSentiment
Out [14]:
Rows_in_newsSentiment |
---|
2359 |
In [15]:
os.environ["OPENAI_API_KEY"] = openai_apikeyembedding_model = 'text-embedding-ada-002'gpt_model = 'gpt-3.5-turbo-16k'# Create the agent executordb = SQLDatabase.from_uri(connection_url, include_tables=['embeddings', 'companyInfo', 'newsSentiment', 'stockTable'], sample_rows_in_table_info=2)llm = LangchainOpenAI(openai_api_key=os.environ["OPENAI_API_KEY"], temperature=0, verbose=True)toolkit = SQLDatabaseToolkit(db=db, llm=llm)agent_executor = create_sql_agent(llm=LangchainOpenAI(temperature=0),toolkit=toolkit,verbose=True,prefix= '''You are an agent designed to interact with a SQL database called SingleStore. This sometimes has Shard and Sort keys in the table schemas, which you can ignore.\nGiven an input question, create a syntactically correct MySQL query to run, then look at the results of the query and return the answer.\n If you are asked about similarity questions, you should use the DOT_PRODUCT function.\nHere are a few examples of how to use the DOT_PRODUCT function:\nExample 1:Q: how similar are the questions and answers?A: The query used to find this is:select question, answer, dot_product(question_embedding, answer_embedding) as similarity from embeddings;\nExample 2:Q: What are the most similar questions in the embeddings table, not including itself?A: The query used to find this answer is:SELECT q1.question as question1, q2.question as question2, DOT_PRODUCT(q1.question_embedding, q2.question_embedding) :> float as scoreFROM embeddings q1, embeddings q2WHERE question1 != question2ORDER BY score DESC LIMIT 5;\nExample 3:Q: In the embeddings table, which rows are from the chatbot?A: The query used to find this answer is:SELECT category, question, answer FROM embeddingsWHERE category = 'chatbot';\nIf you are asked to describe the database, you should run the query SHOW TABLES\nUnless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.\n The question embeddings and answer embeddings are very long, so do not show them unless specifically asked to.\nYou can order the results by a relevant column to return the most interesting examples in the database.\nNever query for all the columns from a specific table, only ask for the relevant columns given the question.\nYou have access to tools for interacting with the database.\nOnly use the below tools.Only use the information returned by the below tools to construct your final answer.\nYou MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again up to 3 times.\n\nDO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n\nIf the question does not seem related to the database, just return "I don\'t know" as the answer.\n,''',format_instructions='''Use the following format:\n\nQuestion: the input question you must answer\nThought: you should always think about what to do\nAction: the action to take, should be one of [{tool_names}]\nAction Input: the input to the action\nObservation: the result of the action\nThought: I now know the final answer\nFinal Answer: the final answer to the original input question\nSQL Query used to get the Answer: the final sql query used for the final answer'''',top_k=3,max_iterations=5)
Create function that processes user question with a check in Semantic Cache Layer
In [16]:
table_name = 'embeddings'similarity_threshold = .97def process_user_question(question):print(f'\nQuestion asked: {question}')category = 'chatbot'# Get vector embedding from the original question and calculate the elapsed timestart_time = time.time()question_embedding= [np.array(x, '<f4') for x in get_embeddings([question], model=embedding_model)]elapsed_time = (time.time() - start_time) * 1000print(f"Execution time for getting the question embedding: {elapsed_time:.2f} milliseconds")params = {'question_embedding': question_embedding,}# Check if embedding is similar to existing questions# If semantic score < similarity_threshold, then run the agent executor# Calculate elapsed time for this stepstmt = f'select question, answer, dot_product( %(question_embedding)s, question_embedding) :> float as score from embeddings where category="chatbot" order by score desc limit 1;'with s2_conn.cursor() as cur:start_time = time.time()cur.execute(stmt, params)row = cur.fetchone()elapsed_time = (time.time() - start_time) * 1000print(f"Execution time for checking existing questions: {elapsed_time:.2f} milliseconds")try:question2, answer, score = rowprint(f"\nClosest Matching row:\nQuestion: {question2}\nAnswer: {answer}\nSimilarity Score: {score}")if score > similarity_threshold:print('Action to take: Using existing answer')return answerelse:print('Action to take: Running agent_executor')start_time = time.time()answer2 = agent_executor.run(question)elapsed_time = (time.time() - start_time) * 1000print(f"agent_executor execution time: {elapsed_time:.2f} milliseconds")# Get current timecreated_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")# Get the answer embedding and calculate the elapsed timestart_time = time.time()answer_embedding = [np.array(x, '<f4') for x in get_embeddings([answer2], model=embedding_model)]elapsed_time = (time.time() - start_time) * 1000print(f"Answer embeddings execution time: {elapsed_time:.2f} milliseconds")params = {'category': category, 'question': question,'question_embedding': question_embedding,'answer': answer2, 'answer_embedding': answer_embedding,'created_at': created_at}# Send params details as a row into the SingleStoreDB embeddings table and calculate the elapsed timestmt = f"INSERT INTO {table_name} (category, question, question_embedding, answer, answer_embedding, created_at) VALUES (%(category)s, \n%(question)s, \n%(question_embedding)s, \n%(answer)s, \n%(answer_embedding)s, \n%(created_at)s)"start_time = time.time()with s2_conn.cursor() as cur:cur.execute(stmt, params)elapsed_time = (time.time() - start_time) * 1000print(f"Insert to SingleStore execution time: {elapsed_time:.2f} milliseconds")return answer2# Handle known exceptions then run as normalexcept:print('No existing rows. Running agent_executor')start_time = time.time()answer2 = agent_executor.run(question)elapsed_time = (time.time() - start_time) * 1000print(f"agent_executor execution time: {elapsed_time:.2f} milliseconds")created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")# Record the start timestart_time = time.time()answer_embedding = [np.array(x, '<f4') for x in get_embeddings([answer2], model=embedding_model)]# Calculate the elapsed timeelapsed_time = (time.time() - start_time) * 1000print(f"Answer embeddings execution time: {elapsed_time:.2f} milliseconds")params = {'category': category, 'question': question,'question_embedding': question_embedding,'answer': answer2, 'answer_embedding': answer_embedding,'created_at': created_at}# Send to SingleStoreDBstmt = f"INSERT INTO {table_name} (category, question, question_embedding, answer, answer_embedding, created_at) VALUES (%(category)s, \n%(question)s, \n%(question_embedding)s, \n%(answer)s, \n%(answer_embedding)s, \n%(created_at)s)"# Record the start timestart_time = time.time()with s2_conn.cursor() as cur:cur.execute(stmt, params)# Calculate the elapsed timeelapsed_time = (time.time() - start_time) * 1000print(f"Insert to SingleStore execution time: {elapsed_time:.2f} milliseconds")return answer2
Test on two similar questions
In [17]:
from datetime import datetime# Two similar questionsquestion_1 = "describe the database"question_2 = "describe database"
In [18]:
# Question: describe the databaseanswer = process_user_question(question_1)print(f'The answer is: {answer}')
In [19]:
%%sqlselect id, category, question, answer from embeddings limit 1
Out [19]:
id | category | question | answer |
---|---|---|---|
1125899906842625 | chatbot | describe the database | The database contains information on various companies, including their ticker, asset type, name, description, CIK, exchange, currency, country, sector, industry, address, fiscal year end, latest quarter, market capitalization, EBITDA, P/E ratio, PEG ratio, book value, dividend per share, dividend yield, EPS, revenue per share, profit margin, operating margin, return on assets, return on equity, revenue, gross profit, diluted EPS, quarterly earnings growth, quarterly revenue growth, analyst target price, trailing P/E, forward P/E, price to sales ratio, price to book ratio, EV to revenue, EV to EBITDA, beta, 52-week high, 52-week low, 50-day moving average, 200-day moving average, shares outstanding, dividend date, and ex-dividend date. SQL Query used to get the Answer: SELECT * FROM companyInfo; |
In [20]:
# Question: describe databaseanswer = process_user_question(question_2)print(f'The answer is: {answer}')
Select a voice
In [21]:
from elevenlabs import generate, stream, voicesfrom elevenlabs import set_api_keyfrom IPython.display import Audiofrom IPython.display import displayimport requests
In [22]:
voices = voices()voices[0]
Out [22]:
Voice(voice_id='EXAVITQu4vr4xnSDxMaL', name='Sarah', category='premade', description=None, labels={'description': 'soft', 'accent': 'american', 'age': 'young', 'gender': 'female', 'use_case': 'news'}, samples=None, design=None, preview_url='https://storage.googleapis.com/eleven-public-prod/premade/voices/EXAVITQu4vr4xnSDxMaL/01a3e33c-6e99-4ee7-8543-ff2216a32186.mp3', settings=None)
In [23]:
CHUNK_SIZE = 1024url = "https://api.elevenlabs.io/v1/text-to-speech/21m00Tcm4TlvDq8ikWAM/stream"headers = {"Accept": "audio/mpeg","Content-Type": "application/json","xi-api-key": elevenlabs_apikey}data = {"text": answer,"model_id": "eleven_monolingual_v1","voice_settings": {"stability": 0.5,"similarity_boost": 0.5}}response = requests.post(url, json=data, headers=headers, stream=True)# create an audio filewith open('output.mp3', 'wb') as f:for chunk in response.iter_content(chunk_size=CHUNK_SIZE):if chunk:f.write(chunk)
In [24]:
!ls
In [25]:
audio_file = 'output.mp3'audio = Audio(filename=audio_file, autoplay =True)display(audio)
Out [25]: