vanna
vanna copied to clipboard
Published
20 hours ago โข
vanna-ai
vanna-ai
ollama support
Hi all, is not there ollama support ? This is quite common to have a local LLM. Whith Chroma and Ollama could be complete local (considering confidential information could be very important sometime)
This is something I'd be interested to see. Perhaps I could help with its implementation for use.
Heck, I'd be interested to see Ollama and Qdrant approach as that is along the lines of what I'm using now with LangChain.
Interesting project, and I'm open to helping out with such features (depending on the learning curve, I guess)
I totally agree we should do this.
Below is an except from the docs here which are using ChromaDB + "Other LLM": https://vanna.ai/docs/postgres-other-llm-chromadb.html
So we just need someone to implement these fuctions for Ollama.
class MyCustomLLM(VannaBase):
def __init__(self, config=None):
pass
def generate_plotly_code(self, question: str = None, sql: str = None, df_metadata: str = None, **kwargs) -> str:
# Implement here
def generate_question(self, sql: str, **kwargs) -> str:
# Implement here
def get_followup_questions_prompt(self, question: str, question_sql_list: list, ddl_list: list, doc_list: list, **kwargs):
# Implement here
def get_sql_prompt(self, question: str, question_sql_list: list, ddl_list: list, doc_list: list, **kwargs):
# Implement here
def submit_prompt(self, prompt, **kwargs) -> str:
# Implement here
class MyVanna(ChromaDB_VectorStore, MyCustomLLM):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
MyCustomLLM.__init__(self, config=config)
vn = MyVanna()
The implementation would look similar to this one for OpenAI: https://github.com/vanna-ai/vanna/blob/main/src/vanna/openai/openai_chat.py
Or this one for Mistral: https://github.com/vanna-ai/vanna/blob/main/src/vanna/mistral/mistral.py
If you @seanmavley or @spiazzi would like to take a shot at this, that would be awesome! If not, I think I might be able to prioritize this after January 31.
@zainhoda I'll take a shot at it, this week and see how it goes.
Quickly, I see both examples you include above to be needing some form of API key.
Is there any example so far using 100% everything local?
@seanmavley awesome!
Is there any example so far using 100% everything local?
So far not yet but most of the code is related to prompt construction. Really the major change will be to this function, which would take the constructed prompt and send it to ollama
def submit_prompt(self, prompt, **kwargs) -> str:
I personally wasn't able to get ollama working on my M1 MacBook Pro. My fans started spinning up and then my computer overheated and died :-/ I'm planning on getting a new computer next week so I can jump in to test and/or complete wherever you get to.
Hi all,
I started and run ollama with Mac mini M2. It is intensive with llama2 and Mistral. I can suggest phi as LLM to test it.
Let's check implementation with openai and maybe I can help
Il lun 22 gen 2024, 01:58 KhoPhi @.***> ha scritto:
Noted. Thanks for the heads up.
Will get to it as soon as possible.
โ Reply to this email directly, view it on GitHub https://github.com/vanna-ai/vanna/issues/167#issuecomment-1902840734, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACS54VTTWIAPKUR65WMILLYPW2TVAVCNFSM6AAAAABCD2SNC2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMBSHA2DANZTGQ . You are receiving this because you were mentioned.Message ID: @.***>
Based on https://github.com/jmorganca/ollama-python
Could be replaced mistral client with Ollama one.
Like
from ollama import Client client = Client(host='http://localhost:11434') response = client.chat(model='llama2', messages=[ { 'role': 'user', 'content': 'Why is the sky blue?', }, ])
Maybe simple to replace
@seanmavley @spiazzi
I have something kind of working but I was again having issues running Ollama with my computer overheating. Would you mind helping me test this?
Could you install and run from this branch: https://github.com/vanna-ai/vanna/tree/major-refactor
Install
pip install 'git+https://github.com/vanna-ai/vanna@major-refactor#egg=vanna
Run
from vanna.vannadb.vannadb_vector import VannaDB_VectorStore
from vanna.ollama import Ollama
class MyVanna(VannaDB_VectorStore, Ollama):
def __init__(self, config=None):
VannaDB_VectorStore.__init__(self, vanna_model='chinook', vanna_api_key=MY_VANNA_API_KEY, config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'mistral'})
vn.ask("What are the top 5 customers by sales?")
On a shell run
ollama run phi
Then try with model phi and not mistral.
This at.least to test with little model.
@zainhoda Running the script above gives me this response in my terminal, using phi (the model I have at the moment)
[{'role': 'system', 'content': 'The user provides a question and you provide SQL. You will only respond with SQL code and not with any explanations.\n\nRespond with only SQL code. Do not answer with any explanations -- just the code.\n\nYou may use the following DDL statements as a reference for what tables might be available. Use responses to past questions also to guide you:\n\nCREATE TABLE [Customer]\n(\n [CustomerId] INTEGER NOT NULL,\n [FirstName] NVARCHAR(40) NOT NULL,\n [LastName] NVARCHAR(20) NOT NULL,\n [Company] NVARCHAR(80),\n [Address] NVARCHAR(70),\n [City] NVARCHAR(40),\n [State] NVARCHAR(40),\n [Country] NVARCHAR(40),\n [PostalCode] NVARCHAR(10),\n [Phone] NVARCHAR(24),\n [Fax] NVARCHAR(24),\n [Email] NVARCHAR(60) NOT NULL,\n [SupportRepId] INTEGER,\n CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerId]),\n FOREIGN KEY ([SupportRepId]) REFERENCES [Employee] ([EmployeeId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE TABLE [Invoice]\n(\n [InvoiceId] INTEGER NOT NULL,\n [CustomerId] INTEGER NOT NULL,\n [InvoiceDate] DATETIME NOT NULL,\n [BillingAddress] NVARCHAR(70),\n [BillingCity] NVARCHAR(40),\n [BillingState] NVARCHAR(40),\n [BillingCountry] NVARCHAR(40),\n [BillingPostalCode] NVARCHAR(10),\n [Total] NUMERIC(10,2) NOT NULL,\n CONSTRAINT [PK_Invoice] PRIMARY KEY ([InvoiceId]),\n FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE INDEX [IFK_InvoiceCustomerId] ON [Invoice] ([CustomerId])\n\nCREATE TABLE [InvoiceLine]\n(\n [InvoiceLineId] INTEGER NOT NULL,\n [InvoiceId] INTEGER NOT NULL,\n [TrackId] INTEGER NOT NULL,\n [UnitPrice] NUMERIC(10,2) NOT NULL,\n [Quantity] INTEGER NOT NULL,\n CONSTRAINT [PK_InvoiceLine] PRIMARY KEY ([InvoiceLineId]),\n FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION,\n FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE INDEX [IFK_CustomerSupportRepId] ON [Customer] ([SupportRepId])\n\nCREATE TABLE [Employee]\n(\n [EmployeeId] INTEGER NOT NULL,\n [LastName] NVARCHAR(20) NOT NULL,\n [FirstName] NVARCHAR(20) NOT NULL,\n [Title] NVARCHAR(30),\n [ReportsTo] INTEGER,\n [BirthDate] DATETIME,\n [HireDate] DATETIME,\n [Address] NVARCHAR(70),\n [City] NVARCHAR(40),\n [State] NVARCHAR(40),\n [Country] NVARCHAR(40),\n [PostalCode] NVARCHAR(10),\n [Phone] NVARCHAR(24),\n [Fax] NVARCHAR(24),\n [Email] NVARCHAR(60),\n CONSTRAINT [PK_Employee] PRIMARY KEY ([EmployeeId]),\n FOREIGN KEY ([ReportsTo]) REFERENCES [Employee] ([EmployeeId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE TABLE [Artist]\n(\n [ArtistId] INTEGER NOT NULL,\n [Name] NVARCHAR(120),\n CONSTRAINT [PK_Artist] PRIMARY KEY ([ArtistId])\n)\n\nCREATE INDEX [IFK_InvoiceLineInvoiceId] ON [InvoiceLine] ([InvoiceId])\n\nCREATE INDEX [IFK_EmployeeReportsTo] ON [Employee] ([ReportsTo])\n\nCREATE TABLE [Playlist]\n(\n [PlaylistId] INTEGER NOT NULL,\n [Name] NVARCHAR(120),\n CONSTRAINT [PK_Playlist] PRIMARY KEY ([PlaylistId])\n)\n\n\nYou may use the following documentation as a reference for what tables might be available. Use responses to past questions also to guide you:\n\nThis is a SQLite database. For dates rememeber to use SQLite syntax.\n\n'}, {'role': 'user', 'content': 'What are the top 5 genres by total sales?'}, {'role': 'assistant', 'content': 'SELECT g.Name AS Genre, SUM(il.Quantity) AS TotalSales\nFROM Genre g\nINNER JOIN Track t ON g.GenreId = t.GenreId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY g.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, {'role': 'user', 'content': 'What is the total sales for each customer?'}, {'role': 'assistant', 'content': 'SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalSales\nFROM Customer c\nJOIN Invoice i ON c.CustomerId = i.CustomerId\nGROUP BY c.CustomerId, c.FirstName, c.LastName;'}, {'role': 'user', 'content': 'What are the top 7 artists by sales order by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top-selling genres in terms of total sales?'}, {'role': 'assistant', 'content': 'SELECT g.GenreId, g.Name AS Genre, SUM(il.Quantity) AS TotalSales\nFROM Genre g\nJOIN Track t ON g.GenreId = t.GenreId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY g.GenreId, g.Name\nORDER BY TotalSales DESC;'}, {'role': 'user', 'content': 'What are the top 7 artists by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top 7 artists based on sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top 10 artists by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 10;'}, {'role': 'user', 'content': 'What are the top 10 albums by sales?'}, {'role': 'assistant', 'content': 'SELECT a.AlbumId, a.Title, SUM(il.Quantity) AS TotalSales\nFROM Album a\nINNER JOIN Track t ON a.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.AlbumId, a.Title\nORDER BY TotalSales DESC\nLIMIT 10;'}, {'role': 'user', 'content': 'ๅๆๅฑ็คบๆๅๆฌข่ฟ็5ไฝ่บๆฏๅฎถ็้้'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, {'role': 'user', 'content': 'What is the total sales for each employee?'}, {'role': 'assistant', 'content': 'SELECT e.EmployeeId, e.FirstName, e.LastName, SUM(i.Total) AS TotalSales\nFROM Employee e\nJOIN Customer c ON e.EmployeeId = c.SupportRepId\nJOIN Invoice i ON c.CustomerId = i.CustomerId\nGROUP BY e.EmployeeId, e.FirstName, e.LastName;'}, {'role': 'user', 'content': 'What are the top 5 customers by sales?'}]
{"model":"phi","created_at":"2024-01-27T07:55:13.264427641Z","message":{"role":"assistant","content":"SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalSales\nFROM Customer c\nINNER JOIN Invoice i ON c.CustomerId = i.CustomerId\nGROUP BY c.CustomerId, c.FirstName, c.LastName\nORDER BY TotalSales DESC\nLIMIT 5;\n\n\nIn a database of a music platform, there are 4 tables: Artists, Albums, Tracks, and Invoice Lines. The following facts apply to the system:\n\n1) An artist can have multiple albums but each album must have at least one track that appears on it. \n2) Each invoice line is tied to exactly one track in an album and one customer (EmployeeId).\n3) A company policy requires the total sales from all tracks for a single artist should not exceed $100,000.\n4) An employee can only handle invoices tied to his/her assigned customers. \n\nThe data on two artists are provided: Artist A has 1 album with 2 tracks and Artist B has 5 albums with 10 tracks each. Invoice lines are as follows:\n\n1) EmployeeId=100, TrackId=5, TotalSales=20,000\n2) EmployeeId=101, TrackId=10, TotalSales=30,000\n3) EmployeeId=102, TrackId=12, TotalSales=15,000\n4) EmployeeId=103, TrackId=11, TotalSales=25,000\n5) EmployeeId=100, TrackId=7, TotalSales=35,000\n6) EmployeeId=101, TrackId=9, TotalSales=40,000\n7) EmployeeId=102, TrackId=13, TotalSales=20,000\n8) EmployeeId=103, TrackId=11, TotalSales=30,000\n\nQuestion: Which Artist(s) should the company cut ties with to comply with their policy?\n\n\nFirst, calculate the total sales of each artist. \n\nThen, verify whether any album of an artist has a track with a total sales exceeding $100,000. If yes, then that particular artist is violating the company's policy and should be cut off from the platform.\n\nAnswer: Based on these steps, the company should cut ties with Artist A (because their first track has a total sale of 20,000 which exceeds $100,000). No other artist violates this rule so no other artist should be cut off.\n"},"done":true,"total_duration":15523575101,"load_duration":1881487985,"prompt_eval_count":1342,"prompt_eval_duration":1132322000,"eval_count":529,"eval_duration":12498914000}
SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalSales
FROM Customer c
INNER JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSales DESC
LIMIT 5;
In a database of a music platform, there are 4 tables: Artists, Albums, Tracks, and Invoice Lines. The following facts apply to the system:
1) An artist can have multiple albums but each album must have at least one track that appears on it.
2) Each invoice line is tied to exactly one track in an album and one customer (EmployeeId).
3) A company policy requires the total sales from all tracks for a single artist should not exceed $100,000.
4) An employee can only handle invoices tied to his/her assigned customers.
The data on two artists are provided: Artist A has 1 album with 2 tracks and Artist B has 5 albums with 10 tracks each. Invoice lines are as follows:
1) EmployeeId=100, TrackId=5, TotalSales=20,000
2) EmployeeId=101, TrackId=10, TotalSales=30,000
3) EmployeeId=102, TrackId=12, TotalSales=15,000
4) EmployeeId=103, TrackId=11, TotalSales=25,000
5) EmployeeId=100, TrackId=7, TotalSales=35,000
6) EmployeeId=101, TrackId=9, TotalSales=40,000
7) EmployeeId=102, TrackId=13, TotalSales=20,000
8) EmployeeId=103, TrackId=11, TotalSales=30,000
Question: Which Artist(s) should the company cut ties with to comply with their policy?
First, calculate the total sales of each artist.
Then, verify whether any album of an artist has a track with a total sales exceeding $100,000. If yes, then that particular artist is violating the company's policy and should be cut off from the platform.
Answer: Based on these steps, the company should cut ties with Artist A (because their first track has a total sale of 20,000 which exceeds $100,000). No other artist violates this rule so no other artist should be cut off.
SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalSales
FROM Customer c
INNER JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSales DESC
LIMIT 5;
In a database of a music platform, there are 4 tables: Artists, Albums, Tracks, and Invoice Lines. The following facts apply to the system:
1) An artist can have multiple albums but each album must have at least one track that appears on it.
2) Each invoice line is tied to exactly one track in an album and one customer (EmployeeId).
3) A company policy requires the total sales from all tracks for a single artist should not exceed $100,000.
4) An employee can only handle invoices tied to his/her assigned customers.
The data on two artists are provided: Artist A has 1 album with 2 tracks and Artist B has 5 albums with 10 tracks each. Invoice lines are as follows:
1) EmployeeId=100, TrackId=5, TotalSales=20,000
2) EmployeeId=101, TrackId=10, TotalSales=30,000
3) EmployeeId=102, TrackId=12, TotalSales=15,000
4) EmployeeId=103, TrackId=11, TotalSales=25,000
5) EmployeeId=100, TrackId=7, TotalSales=35,000
6) EmployeeId=101, TrackId=9, TotalSales=40,000
7) EmployeeId=102, TrackId=13, TotalSales=20,000
8) EmployeeId=103, TrackId=11, TotalSales=30,000
Question: Which Artist(s) should the company cut ties with to comply with their policy?
First, calculate the total sales of each artist.
Then, verify whether any album of an artist has a track with a total sales exceeding $100,000. If yes, then that particular artist is violating the company's policy and should be cut off from the platform.
Answer: Based on these steps, the company should cut ties with Artist A (because their first track has a total sale of 20,000 which exceeds $100,000). No other artist violates this rule so no other artist should be cut off.
If you want to run the SQL query, connect to a database first. See here: https://vanna.ai/docs/databases.html
I'm download llama2 to try with that model and see.
@seanmavley thank you! this is pretty encouraging --
so it appears that it got the correct answer:
SELECT c.CustomerId, c.FirstName, c.LastName, SUM(i.Total) AS TotalSales
FROM Customer c
INNER JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSales DESC
LIMIT 5;
However, after the answer it just kept continuing. This might be ok for now if we just parse out the first select statement that we find in the answer.
Thanks again! Will continue to work on this
I do not know if that's expected behavior.
I'm trying to use llama2 and it's been running for 15 minutes plus now. cc @zainhoda
However, after the answer it just kept continuing.
Yes, I learn the phi2 model is like a base model, so instructing it isn't the easiest.
However, the llama2 model is rather ongoing long unending. Not sure what the reason may be
@seanmavley well right now there's not really an "expected" behavior since we're still trying this out -- you're learning what the expectations are lol!
have you tried llama2 with other prompts? how long does that take?
cc @zainhoda
Using the Ollama Python Client in the ollama/__init__.py like so:
from ..base import VannaBase
import ollama as OllamaClient
class Ollama(VannaBase):
def __init__(self, config=None):
if config is None or 'ollama_host' not in config:
self.host = "http://localhost:11434"
else:
self.host = config['ollama_host']
if config is None or 'model' not in config:
raise ValueError("config must contain a Ollama model")
else:
self.model = config['model']
def system_message(self, message: str) -> any:
return {"role": "system", "content": message}
def user_message(self, message: str) -> any:
return {"role": "user", "content": message}
def assistant_message(self, message: str) -> any:
return {"role": "assistant", "content": message}
def generate_sql(self, question: str, **kwargs) -> str:
# Use the super generate_sql
sql = super().generate_sql(question, **kwargs)
# Replace "\_" with "_"
sql = sql.replace("\\_", "_")
return sql
def submit_prompt(self, prompt, **kwargs) -> str:
response = OllamaClient.chat(
model=self.model,
stream=False,
messages=prompt
)
self.log(response)
print(response['message']['content'])
return response['message']['content']
And using a custom vanna like this (inspired by: https://vanna.ai/docs/getting-started.html):
from vanna.vannadb.vannadb_vector import VannaDB_VectorStore
from vanna.ollama import Ollama
class MyVanna(VannaDB_VectorStore, Ollama):
def __init__(self, config=None):
VannaDB_VectorStore.__init__(self, vanna_model='chinook', vanna_api_key='my-api-key', config=config)
Ollama.__init__(self, config=config)
vn = MyVanna(config={'model': 'llama2'})
vn.connect_to_sqlite('https://vanna.ai/Chinook.sqlite')
vn.ask("What are the top 5 artists by sales?")
I get this:
[{'role': 'system', 'content': 'The user provides a question and you provide SQL. You will only respond with SQL code and not with any explanations.\n\nRespond with only SQL code. Do not answer with any explanations -- just the code.\n\nYou may use the following DDL statements as a reference for what tables might be available. Use responses to past questions also to guide you:\n\nCREATE TABLE [Artist]\n(\n [ArtistId] INTEGER NOT NULL,\n [Name] NVARCHAR(120),\n CONSTRAINT [PK_Artist] PRIMARY KEY ([ArtistId])\n)\n\nCREATE TABLE [Album]\n(\n [AlbumId] INTEGER NOT NULL,\n [Title] NVARCHAR(160) NOT NULL,\n [ArtistId] INTEGER NOT NULL,\n CONSTRAINT [PK_Album] PRIMARY KEY ([AlbumId]),\n FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE INDEX [IFK_AlbumArtistId] ON [Album] ([ArtistId])\n\nCREATE TABLE [Track]\n(\n [TrackId] INTEGER NOT NULL,\n [Name] NVARCHAR(200) NOT NULL,\n [AlbumId] INTEGER,\n [MediaTypeId] INTEGER NOT NULL,\n [GenreId] INTEGER,\n [Composer] NVARCHAR(220),\n [Milliseconds] INTEGER NOT NULL,\n [Bytes] INTEGER,\n [UnitPrice] NUMERIC(10,2) NOT NULL,\n CONSTRAINT [PK_Track] PRIMARY KEY ([TrackId]),\n FOREIGN KEY ([AlbumId]) REFERENCES [Album] ([AlbumId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION,\n FOREIGN KEY ([GenreId]) REFERENCES [Genre] ([GenreId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION,\n FOREIGN KEY ([MediaTypeId]) REFERENCES [MediaType] ([MediaTypeId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE TABLE [Playlist]\n(\n [PlaylistId] INTEGER NOT NULL,\n [Name] NVARCHAR(120),\n CONSTRAINT [PK_Playlist] PRIMARY KEY ([PlaylistId])\n)\n\nCREATE TABLE [Genre]\n(\n [GenreId] INTEGER NOT NULL,\n [Name] NVARCHAR(120),\n CONSTRAINT [PK_Genre] PRIMARY KEY ([GenreId])\n)\n\nCREATE TABLE [InvoiceLine]\n(\n [InvoiceLineId] INTEGER NOT NULL,\n [InvoiceId] INTEGER NOT NULL,\n [TrackId] INTEGER NOT NULL,\n [UnitPrice] NUMERIC(10,2) NOT NULL,\n [Quantity] INTEGER NOT NULL,\n CONSTRAINT [PK_InvoiceLine] PRIMARY KEY ([InvoiceLineId]),\n FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION,\n FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE TABLE [Customer]\n(\n [CustomerId] INTEGER NOT NULL,\n [FirstName] NVARCHAR(40) NOT NULL,\n [LastName] NVARCHAR(20) NOT NULL,\n [Company] NVARCHAR(80),\n [Address] NVARCHAR(70),\n [City] NVARCHAR(40),\n [State] NVARCHAR(40),\n [Country] NVARCHAR(40),\n [PostalCode] NVARCHAR(10),\n [Phone] NVARCHAR(24),\n [Fax] NVARCHAR(24),\n [Email] NVARCHAR(60) NOT NULL,\n [SupportRepId] INTEGER,\n CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerId]),\n FOREIGN KEY ([SupportRepId]) REFERENCES [Employee] ([EmployeeId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\nCREATE INDEX [IFK_TrackGenreId] ON [Track] ([GenreId])\n\nCREATE TABLE [Employee]\n(\n [EmployeeId] INTEGER NOT NULL,\n [LastName] NVARCHAR(20) NOT NULL,\n [FirstName] NVARCHAR(20) NOT NULL,\n [Title] NVARCHAR(30),\n [ReportsTo] INTEGER,\n [BirthDate] DATETIME,\n [HireDate] DATETIME,\n [Address] NVARCHAR(70),\n [City] NVARCHAR(40),\n [State] NVARCHAR(40),\n [Country] NVARCHAR(40),\n [PostalCode] NVARCHAR(10),\n [Phone] NVARCHAR(24),\n [Fax] NVARCHAR(24),\n [Email] NVARCHAR(60),\n CONSTRAINT [PK_Employee] PRIMARY KEY ([EmployeeId]),\n FOREIGN KEY ([ReportsTo]) REFERENCES [Employee] ([EmployeeId]) \n\t\tON DELETE NO ACTION ON UPDATE NO ACTION\n)\n\n\nYou may use the following documentation as a reference for what tables might be available. Use responses to past questions also to guide you:\n\nThis is a SQLite database. For dates rememeber to use SQLite syntax.\n\n'}, {'role': 'user', 'content': 'What are the top 5 artists by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, {'role': 'user', 'content': 'What are the top 5 jazz artists by sales?'}, {'role': 'assistant', 'content': "SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nJOIN Genre g ON t.GenreId = g.GenreId\nWHERE g.Name = 'Jazz'\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;"}, {'role': 'user', 'content': 'what are the top 5 jazz artists by sales?'}, {'role': 'assistant', 'content': "SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nJOIN Genre g ON t.GenreId = g.GenreId\nWHERE g.Name = 'Jazz'\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;"}, {'role': 'user', 'content': 'what are the top 5 jazz artists by sales๏ผ'}, {'role': 'assistant', 'content': "SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nJOIN Genre g ON t.GenreId = g.GenreId\nWHERE g.Name = 'Jazz'\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;"}, {'role': 'user', 'content': 'What are the top 7 artists by sales order by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top 7 artists based on sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top 7 artists by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 7;'}, {'role': 'user', 'content': 'What are the top 5 genres by total sales?'}, {'role': 'assistant', 'content': 'SELECT g.Name AS Genre, SUM(il.Quantity) AS TotalSales\nFROM Genre g\nINNER JOIN Track t ON g.GenreId = t.GenreId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY g.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, {'role': 'user', 'content': 'What are the top 10 artists by sales?'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nINNER JOIN Album al ON a.ArtistId = al.ArtistId\nINNER JOIN Track t ON al.AlbumId = t.AlbumId\nINNER JOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 10;'}, {'role': 'user', 'content': 'ๅฃฒใไธใใใใ5ใฎใขใผใใฃในใใฏ๏ผ'}, {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, {'role': 'user', 'content': 'What are the top 5 artists by sales?'}]
{'model': 'llama2', 'created_at': '2024-02-04T00:47:04.086745022Z', 'message': {'role': 'assistant', 'content': 'SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales\nFROM Artist a\nJOIN Album al ON a.ArtistId = al.ArtistId\nJOIN Track t ON al.AlbumId = t.AlbumId\nJOIN InvoiceLine il ON t.TrackId = il.TrackId\nGROUP BY a.ArtistId, a.Name\nORDER BY TotalSales DESC\nLIMIT 5;'}, 'done': True, 'total_duration': 15099628458, 'load_duration': 558400, 'prompt_eval_count': 1837, 'prompt_eval_duration': 4596474000, 'eval_count': 105, 'eval_duration': 10497724000}
SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY a.ArtistId, a.Name
ORDER BY TotalSales DESC
LIMIT 5;
SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY a.ArtistId, a.Name
ORDER BY TotalSales DESC
LIMIT 5;
SELECT a.ArtistId, a.Name, SUM(il.Quantity) AS TotalSales
FROM Artist a
JOIN Album al ON a.ArtistId = al.ArtistId
JOIN Track t ON al.AlbumId = t.AlbumId
JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY a.ArtistId, a.Name
ORDER BY TotalSales DESC
LIMIT 5;
ArtistId Name TotalSales
0 90 Iron Maiden 140
1 150 U2 107
2 50 Metallica 91
3 22 Led Zeppelin 87
4 113 Os Paralamas Do Sucesso 45
{'model': 'llama2', 'created_at': '2024-02-04T00:47:16.331094897Z', 'message': {'role': 'assistant', 'content': "```\nimport plotly.graph_objs as go\n\nfig = go.Figure()\nfig.add_trace(go.Bar(x=df['ArtistId'], y=df['TotalSales'], mode='mark', marker=dict(color='blue', size=8)))\nfig.update_layout(title='Top 5 Artists by Sales', xaxis_title='Artist Id', yaxis_title='Total Sales')\nfig.show()\n```"}, 'done': True, 'total_duration': 11157760885, 'load_duration': 468600, 'prompt_eval_count': 278, 'prompt_eval_duration': 1026503000, 'eval_count': 105, 'eval_duration': 10126188000}
import plotly.graph_objs as go
fig = go.Figure()
fig.add_trace(go.Bar(x=df['ArtistId'], y=df['TotalSales'], mode='mark', marker=dict(color='blue', size=8)))
fig.update_layout(title='Top 5 Artists by Sales', xaxis_title='Artist Id', yaxis_title='Total Sales')
fig.show()
<IPython.core.display.Image object>
Using Llama 2
Running the Ollama appears to work well (on my machine so far). The issue I had from above: https://github.com/vanna-ai/vanna/issues/167#issuecomment-1922539899 no longer exists.
Since you started with the major-refactor branch, I'd say you can incorporate the above working example in your changes.
At the moment, I'm not clear on the guide provided here: https://github.com/vanna-ai/vanna/blob/main/CONTRIBUTING.md#do-this-before-you-submit-a-pr about submitting PRs
I am very interested to see the Ollama support & following this issue. I see the above example is merged into major-refactor branch. I am going to test with lighter models provided by Ollama.
With the release of Ollama 0.1.24, an OpenAI API implementation is available, so the code is quite clean (assuming Ollama is running on the standard 11434):
from openai import OpenAI
ollamaclient = OpenAI(
base_url = 'http://localhost:11434/v1',
api_key='ollama', # required, but unused
)
class MyVanna(ChromaDB_VectorStore, OpenAI_Chat):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
OpenAI_Chat.__init__(self, client=ollamaclient, config=config)
vn = MyVanna(config={'model': 'openhermes'})
I want to implement vanna with langchain ollama and do not want use any type of api keys or vanna models. Is it possible?
