Text embedding

Syntax

EMBEDDING('<text>', '<model_name>', <dimension>)

Parameters

Example

Run the following SQL statement to convert the text'hello' into a 1024-dimensional vector using thetext-embedding-v4 model.

SELECT EMBEDDING('hello', 'text-embedding-v4', 1024);
+-----------------------------------------------+
| EMBEDDING('hello', 'text-embedding-v4', 1024)   |
+-----------------------------------------------+
| ;|n=0=    K: ^<)<i    K  ...|
+-----------------------------------------------+

SELECT HEX(EMBEDDING('hello', 'text-embedding-v4', 1024));
+-----------------------------------------------+
| EMBEDDING('hello', 'text-embedding-v4', 1024)   |
+-----------------------------------------------+
| E20BFE3BB7A37CBDE46E953D14308C3DF63D173DE8 ...|
+-----------------------------------------------+

Step 1: Create a table and generate vectors

You can use the Stored Generated Column feature in PolarDB for MySQL with theEMBEDDING() function to automatically generate vectors when data is inserted or updated. You must alsocreate a vector index for the vector column to accelerate similarity search.

-- Create the knowledge base table.
CREATE TABLE knowledge_base (
  id INT AUTO_INCREMENT PRIMARY KEY,
  doc TEXT,
  -- Define a Stored Generated Column (STORED). When the doc column is inserted or updated,
  -- the EMBEDDING() function is automatically called to generate a vector and store it in the vec column.
  vec VECTOR(1024) AS (EMBEDDING(doc, 'text-embedding-v4', 1024)) STORED 
  -- Use the COMMENT syntax to declare an HNSW vector index for the vec column. This is the specific syntax required to create a vector index.
  -- metric=cosine: Specifies that cosine distance is used for distance calculation.
  COMMENT 'imci_vector_index=HNSW(metric=cosine,max_degree=16,ef_construction=300)'
) 
-- COMMENT 'COLUMNAR=1' creates a columnar index for this table, which is a prerequisite for using a vector index.
COMMENT 'COLUMNAR=1';

-- Insert the source text data. Vector generation and index creation are automatically handled by the database.
INSERT INTO knowledge_base (doc) VALUES 
('PolarDB IMCI supports accessing both row and column stores in a single SQL statement by using a Hybrid Plan.'),
('HashMatch is a join operator in PolarDB IMCI.'),
('PolarDB IMCI provides a built-in vector index and text embedding service.');

Step 2: Perform semantic search and construct a prompt

Once the knowledge base is populated, you can simulate a user question by embedding it in real time with the EMBEDDING() function, finding the most relevant documents with the DISTANCE() function, and then using the CONCAT() and GROUP_CONCAT() functions to combine the retrieved knowledge with the original question into a complete prompt.

• Construct a prompt within a single SQL statement:

  -- Embed the user question "What is HashMatch?", find the most similar record in the knowledge_base table,
  -- and then combine its content with the question to create a prompt to send to a large language model.
  
  SELECT 
      CONCAT(
          'Based on the following context: ', 
          GROUP_CONCAT(doc), 
          ', please answer the user''s question in a suitable tone: What is HashMatch?'
      ) AS final_prompt
  FROM (
      SELECT 
          doc 
      FROM 
          knowledge_base 
      ORDER BY 
          -- The DISTANCE() function calculates the distance between two vectors. Here, it calculates the cosine distance between the user question vector and each knowledge vector in the table.
          DISTANCE(vec, EMBEDDING('What is HashMatch?', 'text-embedding-v4', 1024), 'COSINE') 
      LIMIT 1
  ) AS t;

  The following result is returned:

  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | final_prompt                                                                                                                                                      |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Based on the following context: HashMatch is a join operator in PolarDB IMCI, please answer the user's question in a suitable tone: What is HashMatch? |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• Store the embedding result in a session variable to reuse it in subsequent SQL statements.

  SET @value = EMBEDDING("What is HashMatch?", "text-embedding-v4", 1024);
  
  SELECT 
      CONCAT(
          'Based on the following context: ', 
          GROUP_CONCAT(doc), 
          ', please answer the user''s question in a suitable tone: What is HashMatch?'
      ) AS final_prompt
  FROM (
      SELECT 
          doc 
      FROM 
          knowledge_base 
      ORDER BY 
          -- The DISTANCE() function calculates the distance between two vectors. Here, it calculates the cosine distance between the user question vector and each knowledge vector in the table.
          DISTANCE(vec, @value, 'COSINE') 
      LIMIT 1
  ) AS t;