## Module 4: Auto Embedding and Semantic Searches

![demo_203-tidb-x-auto-embedding.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/82b09527459f4fc0b9f238aa5ff7ba06ca9ceed6.jpg)

> [!NOTE]
> This module introduces a feature that is currently in preview.

With the influence of Generative AI, more and more vector data is entering databases. TiDB Cloud Starter provides Auto Embedding capabilities, making it much easier for you to store and query vector data.

### Step 1. Create a table for auto embedding and semantic searches

The Auto Embedding feature enables vector searches using plain text. You can insert text data and perform semantic searches with text queries, while TiDB Cloud Starter automatically converts the text to vectors in the background. For more details about auto embedding, see [this document](https://docs.pingcap.com/tidbcloud/vector-search-auto-embedding-overview/?plan=starter).

- In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB if you are not already connected.

  ```bash
  mysql \
   --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
  ```

- Create a new `books_embedding` table with auto embedding enabled. This step uses the `Amazon Titan Embed Text v2` embedding model. The column `title_vector` will store the embedded vector for the `title` automatically as defined by the `GENERATED ALWAYS AS` clause. For a complete list of available text embedding models, see [this documentation](https://docs.pingcap.com/tidbcloud/vector-search-auto-embedding-overview/?plan=starter#available-text-embedding-models).

  ```sql
  USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME};
  DROP TABLE IF EXISTS `books_embedding`;
  CREATE TABLE `books_embedding` (
      `id` int AUTO_INCREMENT,
      `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
      `type` enum(
          'Magazine',
          'Novel',
          'Life',
          'Arts',
          'Comics',
          'Education & Reference',
          'Humanities & Social Sciences',
          'Science & Technology',
          'Kids',
          'Sports'
      ) COLLATE utf8mb4_unicode_ci NOT NULL,
      `published_at` datetime NOT NULL,
      `stock` int DEFAULT '0',
      `price` decimal(15, 2) DEFAULT '0.0',
      `title_vector` VECTOR(1024) GENERATED ALWAYS AS (
          EMBED_TEXT("tidbcloud_free/amazon/titan-embed-text-v2", title)
      ) STORED,
      PRIMARY KEY (`id`)
  );
  ```

  ```output
  Query OK, 0 rows affected (0.12 sec)
  ```

- Copy 100 books from table `books` to table `books_embedding`, vector column `title_vector` will be assigned by Auto Embedding automatically.

  ```sql
  INSERT INTO books_embedding (title, type, published_at, stock, price)
  SELECT title, type, published_at, stock, price FROM books WHERE id BETWEEN 1 AND 100;
  ```

  ```output
  Query OK, 100 rows affected (0.60 sec)
  Records: 100  Duplicates: 0  Warnings: 0
  ```

- Select two rows from books, choose book type `type`, book title `title`, book title vector `title_vector` only. To conserve space, we show only the first 40 characters of each title vector after converting it to text.

  ```sql
  SELECT id, type, title, LEFT(VEC_AS_TEXT(title_vector),40) as title_vec_head
  FROM books_embedding
  WHERE id IN (1,2)
  ORDER BY id\G
  ```

- The output displays the book titles `Greatest Love of All` and `The Power of Love`.

  ```output
  *************************** 1. row ***************************
              id: 1
            type: Education & Reference
           title: Greatest Love of All
  title_vec_head: [-0.019431183,0.045707557,-0.03952369,-0
  *************************** 2. row ***************************
              id: 2
            type: Novel
           title: The Power of Love
  title_vec_head: [-0.035356652,0.025503386,-0.03312202,-0
  2 rows in set (0.01 sec)
  ```

### Step 2. Execute semantic searches

- Since the book titles are stored as vectors in the `title_vector` column, you can now perform semantic searches by using the `VEC_EMBED_COSINE_DISTANCE` function together with the `ORDER BY` clause to find the book titles that are most semantically similar to your search keywords.

- Let's search for the top 3 book titles that are semantically close to `boys and girls`.

  ```sql
  SELECT id, title FROM books_embedding
  ORDER BY VEC_EMBED_COSINE_DISTANCE(
      title_vector,
      "boys and girls"
  )
  LIMIT 3\G
  ```

- In the example output, the top book titles are `Body & Soul`, `Cars`, and `ABC`. Do these results make sense to you? If you use an embedding model other than `Amazon Titan Embed Text v2`, your results may differ slightly.

  ```output
  *************************** 1. row ***************************
     id: 51
  title: Body & Soul
  *************************** 2. row ***************************
     id: 99
  title: Cars
  *************************** 3. row ***************************
     id: 60
  title: ABC
  3 rows in set (0.02 sec)
  ```

- Now, let's search for the top 3 book titles that are semantically related to `sadness and happiness`. In the example output, you'll notice titles such as `Soul`, `Mood`, and `Melody` returned as results.

  ```sql
  SELECT id, title FROM books_embedding
  ORDER BY VEC_EMBED_COSINE_DISTANCE(
      title_vector,
      "sadness and happiness"
  )
  LIMIT 3\G
  ```

  ```output
  *************************** 1. row ***************************
     id: 51
  title: Body & Soul
  *************************** 2. row ***************************
     id: 44
  title: Mood Indigo
  *************************** 3. row ***************************
     id: 3
  title: Unchained Melody
  3 rows in set (1.10 sec)
  ```

- You can explore a deeper dive into how to build a generative AI vector search application in hands-on lab [Build Vector Search Apps Using Jupyter Notebook](/labs/demo_408). For now, keep your database connection open and proceed to the next module.