OpenAI-to-SQLite
datasette.io
datasette.io
For example, if you use embeddings, do you need to know "a priori" what you want to search for? In other words, if you don't know your search queries up front, you have to generate the embeddings, store them inside the database, and then use them. The first step requires an API call to a commercial company (OpenAI here), running against a private model, via a service (which could have downtime, etc). (I imagine there are other embedding technologies but then I would need to manage the hardware costs of training and all the things that come with running ML models on my own.)
Compare that to a regular LIKE search inside my database: I can do that with just a term that a user provides without preparing my database beforehand; the database has native support for finding that term in whatever column I choose. Embedding seem much more powerful in that you can search for something in a much fuzzier way using the cosine distance of the embedding vector, but requires me to generate and store those embeddings first.
Am I wrong about my assumptions here?
Morally, when you get a query, you compute its embedding (through an API call) and return parts of your corpus whose embedding are cosine-close to the query.
In order to do that efficiently, you indeed have to pre-compute all the embeddings of your corpus beforehand and store them in your database.
If I want to know if my text has something like "dancing" and it does have "tango" inside it, why wouldn't I just generate a list of synonyms and then for-each run those queries on my text and then aggregate them myself?
I can download a few synonyms databases here:
https://stackoverflow.com/questions/5618304/looking-for-thes...
Is the value here that OpenAI can get me a better list of synonyms than I could do on my own?
If OpenAI were better at generating this list of synonyms, especially with more current data (I need to search for a concept like "fuzzy-text" and want text with "embeddings" to be a positive match!) that would be valuable.
It feels like OpenAI will probably be faster to update their model with current data from the Internet than those synonym lists linked above. Having said that, one of the criticisms of ChatGPT is that it does not have great knowledge of more recent events, right? Don't ask it about the Ukraine war unless you want a completely fabricated result.
And if you really need to, you can also fine-tune your LLM or do few shot learning to further customise your embeddings to your dataset.
1. Create embeddings of your db entries by running through a nn in inference mode, save in a database in vector format.
2. Convert your query to an embedding by running it through a neural network in inference mode
3. Perform a nearest neighbor search of your query embedding with your db embeddings. There are also databases optimized for this, for example FAISS by meta/fb [1].
So if your network is already trained or you use something like OpenAI for embeddings, it can still be done in near real time just think of getting your embedding vector as part of the indexing process.
You can do more things too, like cluster your embeddings db to find similar entries.
[1] https://engineering.fb.com/2017/03/29/data-infrastructure/fa...
No. Your embedding is something that represents the data. You initially calculate the embedding for each datapoint with the API (or model) and store them in an index.
When a user makes a query, it is first embedded by a call to the API. Then you can measure similarity score with a very simple multiply and add operation (dot product) between these embeddings.
More concretely, an embedding is an array of floats, usually between 300 and 10,000 long. To compare two embeddings a and b you do sum_i(a_i * b_i), the larger this score is, the more similar a and b are. If you sort by similarity you have ranked your results.
The fun part is when you compare embeddings of text with embeddings of images. They can exist in the same space, and that's how generative AI relates text to images.
I’m working on making them 2-4x smaller with SmoothQuant int8 quantization and hoping to release standalone C++ a Rust implementation optimized for CPU inference.
select * from documents where timestamp=today() order by similarity(embedding, query_vector)
In this case the query planner should be smart enough such that it first filters by timestamp and within those records remaining it does the HNSW search. I'm unsure whether SQLite's extension interface is flexible enough to support this.
You can do it, but you need to manually filter from the main table then subquery the fts index, or use a join, etc. It's powerful but the interface isn't as friendly as it could be in that way.
The sqlite extension system is powerful enough that an extension could unify the behaviors as you describe. It's just not how the fts5 extension interface is exposed, and afaik no one has written an extension on top of it to make it easier to use.
I think it is, if I've understood your requirements correctly. e.g. from the datasette-faiss docs:
with related as (
select value from json_each(
faiss_search(
'intranet',
'embeddings',
(select embedding from embeddings where id = :id),
5
)
)
)
select id, title from articles, related
where id = valueMore info: https://www.sqlite.org/vtab.html#the_xbestindex_method
This is not going to be my primary DB. I would update this maybe once a day and the update doesn't have to be super fast.
https://opensearch.org/docs/latest/search-plugins/knn/index/
And how do you calculate the distance between two texts then?
Just request the embeddings array "a" for text one and "b" for text two and then calculate "sqrt((a[0]-b[0])^2+(a[1]-b[1])^2+(a[2]-b[2])^2...)" ?
Yes, that's what an embedding is.
> How many?
It varies depending on the model. In this case 1536, it's in the original post.
> And how do you calculate the distance between two texts then?
You take their embedding vectors and calculate the distance between the two vectors using something like cosine similarity - https://en.wikipedia.org/wiki/Cosine_similarity (there are other distances too obviously)
1536
cosine similarity
This gives useful intuitions e.g. cosine distance ignores length and only looks at angles, but this matters less with higher dims; by contrast the euclidian length becomes less useful.
Euclidean distance when used is squared, because even that sqrt is too expensive.
But that's quite possibly OpenAI's model as well - cache results and reduce costs while keeping pricing.
It also would not create the non deterministic responses users have come to expect.
Probably slightly modifying text is less expensive than coming up with brand new text. So the text can be cached, and when retrieved, slightly altered before served. A "cached-but-not-really" cache
As I understand it, "state" is actually just feeding in the history into the prompt as "context", so realistically this could help build history and context via lookups
eg.
Answer this as an assistant, blah blah blah.
You already said ${QUERY 1}, ${QUERY 2}. Refer to ${QUERY 3}, ${QUERY 4} for ideal formatting.
Prompt: "This is what the user asks chat gpt this round"
You just need to cache it with AI and you should be good to go.
Table: Questions
- id (Primary Key, Auto-Incremented)
- title (VARCHAR)
- description (TEXT)
Table: Answers
- id (Primary Key, Auto-Incremented)
- question_id (Foreign Key referencing the Questions table)
- answer (TEXT)
Please copy all my questions into SQLite compatible INSERT statements, similarly create a new INSERT for your answer.
What color is the sky?
Here is the SQLite compatible INSERT statement for your question:
INSERT INTO Questions (title, description)
VALUES ("What color is the sky?", "");
And here is the SQLite compatible INSERT statement for my answer:
INSERT INTO Answers (question_id, answer)
VALUES (1, "The sky is typically blue during the day and black at night.");
Note: The question_id in the Answers INSERT statement is referencing the id of the question in the Questions table. In this example, the assumption is that the question with id of 1 is "What color is the sky?".Right now it's like dealing with a very eager assistant that has staggering powers of information retrieval and really wants to help, but being forced to conduct all your conversations in a dark room which you and the AI take turns entering and leaving to examine what you've received. You can talk it into performing array and dictionary operations and maintaining text buffers in practice, but you're mutually forbidden from sharing/viewing any kind of semantic workspace directly.
Do you think you'll ever replace the search on your website with Semantic Search like this? I'm not sure how well Semantic Search gels with the ability to facet though, so that could be the hiccoup.
Otherwise it seems like you can get Semantic Search off SQLite that rivals e.g. the Typesenses and ElasticSearch's of the world
Currently using LangChain