how-to-do-offline-recommendations-with-sqlite-and-gemini

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

How to do Offline Recommendations with SQLite and Gemini

如何使用SQLite和Gemini实现离线推荐

When working with a CMS (like PocketBase) it is common to add some sort of recommendatios for related content. For example you can have a list of blog posts, and show related posts either by random selection or recently viewed.

I first learned about this technique from Aaron Francis on his YouTube channel:

在使用PocketBase等CMS系统时，通常会为相关内容添加某种推荐功能。例如，你可以展示博客文章列表，并通过随机选择或最近浏览的方式显示相关文章。

我最初是在Aaron Francis的YouTube频道上了解到这项技术的：

Text Embeddings

Text embeddings are a way to convert a chunk of text into a an array of numbers. Having a mathematical representation means we can easily store them in a database and run common functions to calculate the distances between vectors that we have stored.

You will need an API Key from AI Studio to generate the descriptions and embeddings.

In order to create the embedding we need to first generate chunk small enough to fit in the embedding window size. For example we can use an LLM like Gemini to generate a description for a blog post and then vectorize the description which we can store in the database.

We only need to generate a new embedding and description when the content changes which limits the billing costs to the frequency of the content changes.

Text embeddings是一种将一段文本转换为数字数组的方法。这种数学表示形式意味着我们可以轻松地将其存储在数据库中，并通过常用函数计算已存储向量之间的距离。

你需要从AI Studio获取API密钥才能生成描述和嵌入向量。

为了创建嵌入向量，我们首先需要生成足够小的文本块以适配嵌入窗口大小。例如，我们可以使用Gemini这样的大语言模型为博客文章生成描述，然后将该描述向量化并存储到数据库中。

我们只需要在内容发生变化时生成新的嵌入向量和描述，这样可以将计费成本控制在内容变更的频率范围内。

Storing the Vectors

存储向量

To store the text embeddings as vectors we can save them in a SQLite database using a runtime loadable extension called sqlite-vec. Here is an example from the readme on how to query the vectors directly in SQLite:

.load ./vec0

create virtual table vec_examples using vec0(
  sample_embedding float[8]
);

-- vectors can be provided as JSON or in a compact binary format
insert into vec_examples(rowid, sample_embedding)
  values
    (1, '[-0.200, 0.250, 0.341, -0.211, 0.645, 0.935, -0.316, -0.924]'),
    (2, '[0.443, -0.501, 0.355, -0.771, 0.707, -0.708, -0.185, 0.362]'),
    (3, '[0.716, -0.927, 0.134, 0.052, -0.669, 0.793, -0.634, -0.162]'),
    (4, '[-0.710, 0.330, 0.656, 0.041, -0.990, 0.726, 0.385, -0.958]');


-- KNN style query
select
  rowid,
  distance
from vec_examples
where sample_embedding match '[0.890, 0.544, 0.825, 0.961, 0.358, 0.0196, 0.521, 0.175]'
order by distance
limit 2;
/*
┌───────┬──────────────────┐
│ rowid │     distance     │
├───────┼──────────────────┤
│ 2     │ 2.38687372207642 │
│ 1     │ 2.38978505134583 │
└───────┴──────────────────┘
*/

Now we can just take the vectors we created earlier and store them in a table which can update as content changes.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  description TEXT.
  embeddings TEXT
);

CREATE VIRTUAL TABLE vec_posts USING vec0(
  id INTEGER PRIMARY KEY,
  embedding float[768]
);

-- Sync vectors
INSERT INTO vec_posts(id, embedding) SELECT id, embeddings FROM posts;

We could also setup triggers to keep them up to date but in PocketBase I am using event hooks to keep the virtual table udpated.

要将文本嵌入存储为向量，我们可以使用名为sqlite-vec的运行时可加载扩展，将它们保存到SQLite数据库中。以下是来自README的示例，展示如何直接在SQLite中查询向量：

.load ./vec0

create virtual table vec_examples using vec0(
  sample_embedding float[8]
);

-- vectors can be provided as JSON or in a compact binary format
insert into vec_examples(rowid, sample_embedding)
  values
    (1, '[-0.200, 0.250, 0.341, -0.211, 0.645, 0.935, -0.316, -0.924]'),
    (2, '[0.443, -0.501, 0.355, -0.771, 0.707, -0.708, -0.185, 0.362]'),
    (3, '[0.716, -0.927, 0.134, 0.052, -0.669, 0.793, -0.634, -0.162]'),
    (4, '[-0.710, 0.330, 0.656, 0.041, -0.990, 0.726, 0.385, -0.958]');


-- KNN style query
select
  rowid,
  distance
from vec_examples
where sample_embedding match '[0.890, 0.544, 0.825, 0.961, 0.358, 0.0196, 0.521, 0.175]'
order by distance
limit 2;
/*
┌───────┬──────────────────┐
│ rowid │     distance     │
├───────┼──────────────────┤
│ 2     │ 2.38687372207642 │
│ 1     │ 2.38978505134583 │
└───────┴──────────────────┘
*/

现在我们只需将之前创建的向量存储到表中，该表可以随着内容变化而更新。

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  description TEXT.
  embeddings TEXT
);

CREATE VIRTUAL TABLE vec_posts USING vec0(
  id INTEGER PRIMARY KEY,
  embedding float[768]
);

-- Sync vectors
INSERT INTO vec_posts(id, embedding) SELECT id, embeddings FROM posts;

我们也可以设置触发器来保持数据更新，但在PocketBase中，我使用事件钩子来更新虚拟表。

Generate the Recommendation

生成推荐

Now to generate the recommendation offline we just need to use one of the blog posts to use as the input query to then use k-nearest neighbor search (kNN) to get N number of related posts.

SELECT
  vec_posts.id as id,
  vec_posts.embedding as embedding,
  posts.title as title,
  posts.description as description,
  posts.slug as slug
FROM vec_posts
INNER JOIN vec_posts.id = posts.id
WHERE embedding match ?
AND k = 6
ORDER BY distance;

We just need to provide the ? argument with the vector of the currently selected blog post, and then after we filter out the current blog post from the list then we have the N closest number of blog posts that are related in a vector database.

现在，要离线生成推荐，我们只需选择一篇博客文章作为输入查询，然后使用k近邻搜索（kNN）获取N篇相关文章。

SELECT
  vec_posts.id as id,
  vec_posts.embedding as embedding,
  posts.title as title,
  posts.description as description,
  posts.slug as slug
FROM vec_posts
INNER JOIN vec_posts.id = posts.id
WHERE embedding match ?
AND k = 6
ORDER BY distance;

我们只需将当前选中博客文章的向量作为参数传入?，然后从结果列表中过滤掉当前文章，就能得到向量数据库中最相关的N篇博客文章。

Conclusion

总结

This makes it so no matter how many times a blog posts is visited no network calls are made for the recommendation which enables this to scale really well.

To see this in action you can click around on the various blog posts I have on my site and see the generated descriptions and related posts at the end of each article.

这样一来，无论博客文章被访问多少次，推荐过程都不会发起网络请求，因此该方案的扩展性非常好。

你可以访问我的网站，点击不同的博客文章，查看每篇文章末尾生成的描述和相关文章，亲身体验这项功能。