6 ms·
OpenAI-to-SQLite
- articsputnik 4y agoI'm always intrigued by Simon, and I enjoy his open-minded way of working. Thanks, Simon!
- swyx 4y agoSimon is singlehandedly pushing up the average package-per-developer ratio of all of us. 1000x developer.
- mritchie712 4y agoSame with HN. Every time I'm about to comment on HN, simonw has already been there with something more thoughtful. 1000x HNer.
- mirekrusin 4y agoIf OpenAI can scrape what humans did then can you create public p2p database of request-responses?
- Cthulhu_ 4y agoI mean that sounds like a natural evolution; if generating a new response costs money, it makes sense to save them in a cheap database for future lookup / reuse. But that's quite possibly OpenAI's model as well - cache results and reduce costs while keeping pricing.
- mirekrusin 4y agoAlso known as "mainly poem database".
- flangola7 4y agoThat would really only work on intrusive prompts, before existing state had been established. It also would not create the non deterministic responses users have come to expect.
- capableweb 4y ago> 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
- mirekrusin 4y agoGood point, randomly rephrase slightly is probably much easier task resource wise.
- vineyardmike 4y ago> before existing state had been established. 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"
- olalonde 4y agoI was thinking about that when working on https://eli5.gg https://eli5.gg. I implemented a caching system internally but it would have been simpler to connect to an OpenAI proxy that handled this automagically.
- nbevans 4y agoThey can put it in the T&Cs saying you can't do that though. Like what most geocoding APIs do - e.g. Google says you cannot cache/store the geocoding results.
- BLO716 4y agoWhat you do with the results is a legal argument after the fact. It's like saying we give you food, but you can't store the poop. Honestly - there is a house of cards just waiting to be challenged. Then again .. this is Google ($$$)
- mirekrusin 4y agoDo they follow license requirements of data they're scraping for training? You just need to cache it with AI and you should be good to go.
- lhnz 4y agoThis is interesting but less interesting than I assumed. I though they'd provide ChatGPT with a schema and ask it to formulate its responses as `INSERT` statements, etc.
- DoctorOW 4y agoI was able to feed it the following schema (actually I coached it through writing it for me). 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) Then responded with: Please copy all my questions into SQLite compatible INSERT statements, similarly create a new INSERT for your answer. What color is the sky? What I got was: 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?".
- viach 4y agoCan't wait for the battle of the OpenAI JavaScript frameworks
- uh_uh 4y agoWhat I really want to see is an HNSW index type for SQLite so finding similar vectors would be fast. I'm wondering if this can be done via an extension.
- CGamesPlay 4y agoYou could do it using an interface similar to the FTS tables in SQLite, where the “index” is actually a table that needs to be kept up to date with the underlying data (you can use triggers to keep them in sync). Querying is a little awkward compared to a normal index, but for these complex, inexact indexes you often want the extra flexibility (like computing the match score).
- uh_uh 4y agoThat's promising, is there a way to integrate this with other filters in the SQL query? Scenario: let's say I have documents and each one has a timestamp and an embedding vector. I should be able to write a query like this: 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.
- giraffe_lady 4y agoI don't think so. You can have unindexed columns on fts tables but I don't think you can directly combine non-fts query filters in that way. 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.
- tomd 4y ago> I'm unsure whether SQLite's extension interface is flexible enough to support this. 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 = value
- TekMol 4y agoThe "embedding" for a text is an array of floating point numbers? How many? 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...)" ?
- jstx1 4y ago> The "embedding" for a text is an array of floating point numbers? 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 https://en.wikipedia.org/wiki/Cosine_similarity (there are other distances too obviously)
- TekMol 4y agoThanks! 1536 That's quite a lot. Is there a fast way to find the most similar one to a given one in SQLite? cosine similarity Why not euclidean distance?
- moconnor 4y agoVery high-dimensional space is unintuitive. It can help to imagine a cube as N increases - 2^N corners and vanishingly small volume to surface area. 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.
- TekMol 4y agoDo I understand you correctly, that you say cosine distance is a better measurement of similarity than euclidian distance when there are many dimensions?
- txtai 4y agotxtai is an alternative approach to this. It builds a FAISS (also supports HNSW) index alongside a SQLite database. It works with sentence-transformers models. For example, this model https://huggingface.co/sentence-transformers/all-MiniLM-L6-v2 https://huggingface.co/sentence-transformers/all-MiniLM-L6-v... is 384 dimensions and works great for semantic search. https://github.com/neuml/txtai https://github.com/neuml/txtai https://neuml.github.io/txtai/embeddings/query/ https://neuml.github.io/txtai/embeddings/query/
- aicharades 4y agoIs there a standard library for extracting data in a consistent format from OpenAI queries yet? And testing the results? Currently using LangChain
- xrd 4y agoI'm trying to wrap my head around embeddings and am not sure I understand how "realtime" embeddings will be in a real world application. 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?
- cpa 4y agoYou need 3 things : a query, a corpus of text to be searched against and a language model that can map text to vectors ("compute the embedding"). 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.
- xrd 4y agoThank you. So, this does imply that you need to know the target of your embeddings in advance, right? 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-thesaurus-data 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.
- BLO716 4y agoTotal newb on the outside looking in, any primers on this stuff?
- simonw 4y agoI wrote a detailed explanation of embeddings as part of this post here: https://simonwillison.net/2023/Jan/13/semantic-search-answers/ https://simonwillison.net/2023/Jan/13/semantic-search-answer...
- simonw 4y agoSince building this I figured out a way to calculate embeddings using a model that runs directly on my own hardware - notes here: https://til.simonwillison.net/python/gtr-t5-large https://til.simonwillison.net/python/gtr-t5-large
- bufo 4y agoThere’s a good leaderboard for embedding focused models here: https://huggingface.co/spaces/mteb/leaderboard https://huggingface.co/spaces/mteb/leaderboard. Some of the sentence-transformers models are even smaller than gr-t5 while still having very close performance for some use cases. 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.
- gokulkrishh09 4y ago[dead]
- anigbrowl 4y agoI look forward to other LLMs besides not-very-OpenAI's becoming available. This is a very clever tool, but what we all actually want is to be able to say 'can you read the paper/article at url:// and help me with XYZ' or highlight text/figures/tables/lists in an editing environment and say 'help parse/graph/translate/implement this', or conversely to say 'here is a buffer/array/heap for you to use, let's build some sort of user interface to make the feedback process more efficient.' This would be particularly useful for code-related tasks. 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.
- canadiantim 4y agoHey Simon, amazing work as usual! 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