ApsaraDB RDS:Use the rds_embedding extension to generate vectors

Background information

Embedding is a technique that translates high-dimensional data into a low-dimensional representation. In machine learning and natural language processing (NLP), embedding is a common method that is used to represent sparse symbols or objects as points within a vector space.

During embedding generation, the vectors are obtained based on the model that is referenced. ApsaraDB RDS for PostgreSQL allows you to use the rds_embedding extension to convert text in your RDS instance into vectors based on an external model that is referenced. ApsaraDB RDS for PostgreSQL also allows you to use a vector similarity operator to calculate the similarity between the text in the RDS instance and the specified text in the referenced model. This helps meet your business requirements in various scenarios.

Prerequisites

• The RDS instance runs PostgreSQL 14 or later.

• The minor engine version of the RDS instance is updated if the major engine version of the RDS instance meets the requirements but the extension is still not supported. For example, if your RDS instance runs PostgreSQL 17, the minor engine version of the RDS instance must be 20241030 or later. For more information, see Update the minor engine version.

• In this topic, the text embedding model provided by Alibaba Cloud Model Studio is used. You must activate Alibaba Cloud Model Studio and obtain the required API key. For more information, see Obtain an API key.

  Note

  In addition to the text embedding model, you can use the functions supported by the rds_embedding extension to add other models. For more information, see Functions supported by the rds_embedding extension.

• The RDS instance is connected over the Internet. By default, you cannot connect to the RDS instance over the Internet. You must create a NAT gateway for the virtual private cloud (VPC) in which the RDS instance resides. This way, you can connect to the RDS instance over the Internet and the RDS instance can access external models. For more information about NAT gateways, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

  Create a NAT gateway

  1. Create a NAT gateway.

     1. Log on to the NAT Gateway console.

     2. On the Internet NAT Gateway page, click Create Internet NAT Gateway.

     3. Optional. In the Create Service-Linked Role section of the Internet NAT Gateway page, click Create Service-Linked Role to create a service-linked role. If this is the first time you create an Internet NAT gateway, this step is required. After the service-linked role is created, you can create Internet NAT gateways.

     4. On the buy page, configure the following parameters and click Buy Now.

        Note

        The following table describes only key parameters. For more information about all parameters, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

        Parameter	Description
        Region	Select the region in which you want to create the Internet NAT gateway. The region must be the same as the region of your RDS instance.
        VPC	Select the VPC to which the Internet NAT gateway belongs. The VPC must be the same as the VPC of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the VPC of your RDS instance.
        Associate vSwitch	Select the vSwitch to which the Internet NAT gateway belongs. The vSwitch must be the same as the vSwitch of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the vSwitch of your RDS instance.
        Access Mode	In this example, Configure Later is selected.

     5. On the Confirm page, confirm the information, select the Terms of Service check box, and then click Confirm.

        You can find the Internet NAT gateway on the Internet NAT Gateway page.

  2. Associate an EIP with the Internet NAT gateway.

     1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.

     2. On the Associated Elastic IP Address tab, click Bind Elastic IP Address.

     3. In the Associate EIP dialog box, select Purchase and Associate EIP.

     4. Click OK.

        After you associate an EIP with the Internet NAT gateway, the EIP is displayed on the Associated Elastic IP Address tab.

  3. Create SNAT Entry

     1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.

     2. On the SNAT Management tab, click Create SNAT Entry.

     3. On the Create SNAT Entry page, configure the following parameters and click OK.

        Parameter	Description
        SNAT Entry	Specify whether you want to create an SNAT entry for a VPC, a vSwitch, an ECS instance, or a custom CIDR block. In this example, Specify vSwitch is selected. This option specifies that only RDS instances that are attached to a specified vSwitch can access the Internet by using a specified public IP address.
        Select vSwitch	Select the vSwitch of your RDS instance from the drop-down list.
        Select EIP	Select one or more EIPs to access the Internet. In this example, a single EIP is selected from the drop-down list.

        After the SNAT entry is created, you can view the SNAT entry in the SNAT Entry List section.

Enable or disable the extensions

• Enable the extensions.

  Before you enable the rds_embedding extension, you must enable the vector extension. The vector extension supports the required vector data types and basic vector operations, such as calculations of the distance and similarity between vectors. The rds_embedding extension only translates high-dimensional text into vectors.

  CREATE EXTENSION vector;
  CREATE EXTENSION rds_embedding;

• Disable the extensions.

  DROP EXTENSION rds_embedding;
  DROP EXTENSION vector;

Examples

In this example, the text-embedding-v3 model provided by Alibaba Cloud Model Studio is used. For more information about text embedding models, see Model overview.

1. Create a test table named test.

   CREATE TABLE test(info text, vec vector(1024) NOT NULL);

2. Add a model.

   SELECT rds_embedding.add_model(
       'text-embedding-v3',
       'https://dashscope-intl.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding',
       'Authorization: Bearer sk-****',
       '{"input":{"texts":["%s"]},"model":"text-embedding-v3","parameters":{"text_type":"query"}}',
       '->''output''->''embeddings''->0->>''embedding'''
   );

   Note

   For more information about rds_embedding.add_model(), see rds_embedding.add_model().

3. Insert text and the required vector data.

   INSERT INTO test SELECT '风急天高猿啸哀', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', '风急天高猿啸哀')::real[];
   INSERT INTO test SELECT '渚清沙白鸟飞回', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', '渚清沙白鸟飞回')::real[];
   INSERT INTO test SELECT '无边落木萧萧下', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', '无边落木萧萧下')::real[];
   INSERT INTO test SELECT '不尽长江滚滚来', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', '不尽长江滚滚来')::real[];

   Note

   For more information about rds_embedding.get_embedding_by_model(), see rds_embedding.get_embedding_by_model().

4. Calculate the similarities between the text 不尽长江滚滚来 and the vectors of each piece of text in the test table.

   SELECT 
       info, 
       vec <=> rds_embedding.get_embedding_by_model(
           'text-embedding-v3', 
           'sk-****', 
           '不尽长江滚滚来'
       )::real[]::vector AS distance 
   FROM 
       test 
   ORDER BY 
       vec <=> rds_embedding.get_embedding_by_model(
           'text-embedding-v3', 
           'sk-****', 
           '不尽长江滚滚来'
       )::real[]::vector;

   Sample output:

         info      |      distance
   ----------------+--------------------
    不尽长江滚滚来 |                  0
    无边落木萧萧下 | 0.42740682200152647
    风急天高猿啸哀 | 0.5247695147991147
    渚清沙白鸟飞回 | 0.5161883811726116
   (4 rows)

References

curl --location 'https://dashscope-intl.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding' \
--header 'Authorization: Bearer <API-KEY>' \
--header 'Content-Type: application/json' \
--data '{
    "model": "text-embedding-v3",
    "input": {
        "texts": [
        "风急天高猿啸哀",
        "渚清沙白鸟飞回", 
        "无边落木萧萧下", 
        "不尽长江滚滚来"
        ]
    },
    "parameters": {
    		"text_type": "query"
    }
}'

             Objects in extension "rds_embedding"
                      Object description
---------------------------------------------------------------
 function rds_embedding.add_model(text,text,text,text,text)
 function rds_embedding.del_model(text)
 function rds_embedding.get_embedding_by_model(text,text,text)
 function rds_embedding.get_response_by_model(text,text,text)
 function rds_embedding.show_models()
 function rds_embedding.update_model(text,text,text,text,text)
 schema rds_embedding
 table rds_embedding.models
(8 rows)