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 space. In machine learning and natural language processing (NLP), embedding is a common method that is used to represent sparse symbols or objects as continuous vectors.

During embedding, 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 similarities 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

• Your RDS instance runs PostgreSQL 14.0 or a later version.

  Note

  This extension is not support by ApsaraDB RDS for PostgreSQL instances that run PostgreSQL 17.

• 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, you can update the minor engine version. For more information, see Update the minor engine version.

• The API key for the model that is used in this topic is obtained, and the region in which the RDS instance resides supports access to OpenAI. In this topic, the embedding model of OpenAI and the Singapore region are used. For more information, see Embeddings.

• 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 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 extension

• Enable the extension.

  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 data operations, such as calculations of the distance and similarities between vectors. The rds_embedding extension only translates high-dimensional text into vectors.

  CREATE EXTENSION vector;
  CREATE EXTENSION rds_embedding;

• Disable the extension.

  DROP EXTENSION rds_embedding;
  DROP EXTENSION vector;

Example

1. Create a test table named test.

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

2. Add a model.

   SELECT rds_embedding.add_model('text-embedding-ada-002','https://api.openai.com/v1/embeddings','Authorization: Bearer sk-****P','{"input":{"texts":["%s"]},"model":"text-embedding-v1"}','->"data"->0->>"embedding"');

   Note

   • The model that is used in this topic is an OpenAI embedding model. The API key for the model that is used in this topic is obtained, and the region in which the RDS instance resides supports access to OpenAI. In this topic, the embedding model of OpenAI and the Singapore region are used. For more information, see Embeddings.

   • For more information about the function that is used in this step, 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-ada-002', 'sk-****P', '风急天高猿啸哀')::real[];
   INSERT INTO test SELECT '渚清沙白鸟飞回', rds_embedding.get_embedding_by_model('text-embedding-ada-002', 'sk-****P', '渚清沙白鸟飞回')::real[];
   INSERT INTO test SELECT '无边落木萧萧下', rds_embedding.get_embedding_by_model('text-embedding-ada-002', 'sk-****P', '无边落木萧萧下')::real[];
   INSERT INTO test SELECT '不尽长江滚滚来', rds_embedding.get_embedding_by_model('text-embedding-ada-002', 'sk-****P', '不尽长江滚滚来')::real[];

   Note

   For more information about the function that is used in this step, 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-ada-002', 'sk-****P', '不尽长江滚滚来')::real[]::vector AS distance FROM test ORDER BY vec <=> rds_embedding.get_embedding_by_model('text-embedding-ada-002', 'sk-****P', '不尽长江滚滚来')::real[]::vector;

   Sample output:

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

References

curl https://api.openai.com/v1/embeddings \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $OPENAI_API_KEY" \
  -d '{
    "input": "Your text string goes here",
    "model": "text-embedding-ada-002"
  }'

             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)