31: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: 32: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)! 33:[{"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}] 2f:{"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"},{"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"},{"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