28:null 29:["$","title",null,{"children":"Introducing sqlite-vss: A SQLite Extension for Vector Search | Alex Garcia | Observable"}] 2e:T499,### Importing the JSON dataset First we'll import the 200k news article headlines + descriptions into a single SQLite table. The data source is a 84MB newline-delimited JSON file, which we can easily import into a table with [`sqlite-lines`](https://github.com/asg017/sqlite-lines) and SQLite's builtin [JSON support](https://www.sqlite.org/json1.html): ```sql .load ./lines0 create table articles( headline text, headline_embedding blob, description text, description_embedding blob, link text, category text, authors text, date ); insert into articles(headline, description, link, category, authors, date) select line ->> '$.headline' as headline, line ->> '$.short_description' as description, line ->> '$.link' as link, line ->> '$.category' as category, line ->> '$.authors' as authors, line ->> '$.date' as date from lines_read('News_Category_Dataset_v3.json'); ``` Note that we're leaving `headline_embedding` and `description_embedding` as `NULL`, that's coming next! Now we have a nice `articles` table with ~200k rows of our raw data: 2f:T51a, ### [Pinecone](https://www.pinecone.io/)/[Milvus](https://milvus.io)/[Weaviate](https://weaviate.io/)/[Qdrant](https://qdrant.tech/) These databases can store millions or billions of vectors, while `sqlite-vss` has a limit of ~1GB per column (*[though hopefully not for long](https://github.com/asg017/sqlite-vss/issues/1)*). They also scale across multiple machines with proper distributed systems, while `sqlite-vss` only works on one machine. On the other hand, `sqlite-vss` is completely open source and can be selfhosted, while some of these databases can not. `sqlite-vss` is also fairly simple to setup - it only requires SQLite (which is already [the most deployed database out there](https://www.sqlite.org/mostdeployed.html)), a few `apt-get` installable packages, and the [`sqlite-vss` loadable extension](https://github.com/asg017/sqlite-vss#Installing). These other solutions may require several Docker containers, entire dedicated clusters, or worse - a credit card. Most of these databases also offer additional filtering on top of similarity search, described in Pinecone's [*The Missing WHERE Clause in Vector Search*](https://www.pinecone.io/learn/vector-search-filtering/). `sqlite-vss` doesn't support this, [but will in the future](https://github.com/asg017/sqlite-vss/issues/2)! 30:[{"id":"31f88a3b4e16bee1ce15b59b5d244146999e8595198b48cbab64359f4c70718680e56c2dd58cb63c6df086c72971d3122b059c72d092d2af0366bd70f5f8b715","href":"https://static.observableusercontent.com/files/31f88a3b4e16bee1ce15b59b5d244146999e8595198b48cbab64359f4c70718680e56c2dd58cb63c6df086c72971d3122b059c72d092d2af0366bd70f5f8b715","name":"1.jpeg","type":"image/jpeg","lastModified":1674849104046.973,"size":46120},{"id":"13f28998c72e714d29d09d32f824e614cbe136c0e348d0275ab29f8fca802bdfae9e24830126e768943a181588c4cba35cb8193adb4ffe81cd16cb10555cf7d6","href":"https://static.observableusercontent.com/files/13f28998c72e714d29d09d32f824e614cbe136c0e348d0275ab29f8fca802bdfae9e24830126e768943a181588c4cba35cb8193adb4ffe81cd16cb10555cf7d6","name":"3.jpeg","type":"image/jpeg","lastModified":1674849104080.136,"size":35201},{"id":"bb866e8232dc8e4afb4357114e4366a7538ed63e2e1aa4c098a4680c32d06bc4b088805f15098655b6323385767dbe2bfbbe1b763a456318d3e4581805fc6780","href":"https://static.observableusercontent.com/files/bb866e8232dc8e4afb4357114e4366a7538ed63e2e1aa4c098a4680c32d06bc4b088805f15098655b6323385767dbe2bfbbe1b763a456318d3e4581805fc6780","name":"2.jpeg","type":"image/jpeg","lastModified":1674849104077.95,"size":39986}] 2c:{"id":"3cd03297f0f854aa","slug":"introducing-sqlite-vss","title":"Introducing sqlite-vss: A SQLite Extension for Vector Search","thumbnail":"f8766c29f1472725dea325b6665a855e9a1fec72bf2919dd2ccbfdabbc23140c","access_level":"public","create_time":"$D2023-01-06T20:53:00.717Z","update_time":"$D2023-06-07T20:53:20.824Z","view_time":null,"first_public_time":"$D2023-02-10T16:41:51.950Z","has_unpublished_edits":null,"fork":false,"stars":21,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024"},"version":453,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null}],"members":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":10,"collection":{"id":"ec34b19c7b7564c5","slug":"sqlite-blog","title":"SQLite Blog","owner":{"id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia"}},"type":"notebook","body":{"title":"Introducing sqlite-vss: A SQLite Extension for Vector Search","cells":[{"id":14,"value":"# Introducing sqlite-vss: A SQLite Extension for Vector Search\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":6,"value":"

\n ${(await Promise.all([\n FileAttachment(\"1.jpeg\").url(),\n FileAttachment(\"3.jpeg\").url(),\n FileAttachment(\"2.jpeg\").url(),\n \n])).map(d=>htl.html`

`)}\n

DALL·E attempts for this post's thumbnail

\n ","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":62,"value":"

${md`[\\`sqlite-vss\\`](https://github.com/asg017/sqlite-vss) is a new SQLite extension that adds vector search capabilities to SQLite, based on [Faiss](https://faiss.ai/). It's several steps above storing embeddings as JSON or pickle, but doesn't scale as well as heavier vector databases like Pinecone/Qdrant/Weaviate. Great for local-first applications and no-nonsense deployments!`} \n