How to access AnalyticDB for PostgreSQL vector databases - AnalyticDB

After you enable vector engine optimization, you can access the AnalyticDB for PostgreSQL vector database using SQL or API operations. This topic describes how to use these two access methods and provides code examples.

Access vector databases using SQL

Java

The AnalyticDB for PostgreSQL vector database supports connections that use the Java Database Connectivity (JDBC) drivers for PostgreSQL or Greenplum. For more information about how to connect to a database using JDBC, see JDBC.

If you have a PostgreSQL JDBC driver and use Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
</dependency>

The following code shows an example of how to access a vector database using Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class GreenplumSample {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://yourhost:yourport/yourdbname";
        String user = "yourusername";
        String password = "yourpassword";

        try {
            // Load the driver.
            Class.forName("org.postgresql.Driver");
            // Create a connection.
            Connection con = DriverManager.getConnection(url, user, password);
            // Create a statement.
            Statement st = con.createStatement();
            // Execute the query.
            String query = "SELECT * FROM yourtable LIMIT 10";
            ResultSet rs = st.executeQuery(query);
            // Process the results.
            while(rs.next()) {
                // Assume that the result set has at least one column of the string type.
                String resultColumn = rs.getString(1);
                System.out.println(resultColumn);
            }
            // Close the resources.
            rs.close();
            st.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Python

You can use the psycopg2 tool to connect to the AnalyticDB for PostgreSQL vector database and use Python code to import and query vector data. For more information about how to connect to a database using psycopg2, see Python.

The following code shows an example of how to access a vector database using Python:

import psycopg2
from psycopg2 import pool

# Create a connection pool.
connection_pool = psycopg2.pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    user='your_username',
    password='your_password',
    host='your_host',
    port='your_port',
    database='your_database'
)

# Check whether the connection is active.
def is_connection_alive(conn):
    try:
        conn.cursor().execute("SELECT 1")
    except (psycopg2.OperationalError, psycopg2.InterfaceError):
        return False
    return True

# Obtain a connection object from the connection pool.
def get_connection():
    conn = connection_pool.getconn()
    while not is_connection_alive(conn):
        conn = connection_pool.getconn()
    return conn

# Return the connection object to the connection pool.
def release_connection(conn):
    connection_pool.putconn(conn)

# Use the connection object to execute a query.
def execute_query(query):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(query)
        if query.startswith("SELECT"):
            result = cursor.fetchall()
        else:
            conn.commit()
            result = None
    except (psycopg2.DatabaseError, psycopg2.InterfaceError) as e:
        print(f"Error executing query: {e}")
        conn.rollback()
        result = None
    cursor.close()
    release_connection(conn)
    return result

# Sample queries.
# Query.
select_query = "SELECT * FROM your_table"
result = execute_query(select_query)
if result:
    print(result)

# Insert.
insert_query = "INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2')"
execute_query(insert_query)

# Update.
update_query = "UPDATE your_table SET column1 = 'new_value' WHERE column2 = 'value2'"
execute_query(update_query)

# Delete.
delete_query = "DELETE FROM your_table WHERE column1 = 'value1'"
execute_query(delete_query)

C

You can use the libpq library to connect to the AnalyticDB for PostgreSQL vector database and use C code to import and query vector data.

The following code shows an example of how to access a vector database using C:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main() {
    const char *conninfo;
    PGconn *conn;
    PGresult *res;
    int nFields;
    int i, j;

    // Set the connection string.
    conninfo = "dbname=yourdbname user=yourusername host=yourhostname port=yourport password=yourpassword";

    // Create a connection.
    conn = PQconnectdb(conninfo);

    // Check the status.
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        exit(1);
    }

    // Execute a query.
    res = PQexec(conn, "SELECT * FROM yourtablename LIMIT 10");

    // Check whether a result set is returned.
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "The SELECT command did not return data: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        exit(1);
    }

    // Obtain the number of fields.
    nFields = PQnfields(res);

    // Print each row.
    for (i = 0; i < PQntuples(res); i++) {
        for (j = 0; j < nFields; j++) {
            printf("%s = %s", PQfname(res, j), PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clean up.
    PQclear(res);

    // Close the connection.
    PQfinish(conn);

    return 0;
}

Access vector databases using an API

OpenAPI encapsulates the DDL and DML for vector operations in AnalyticDB for PostgreSQL. This lets you manage vector data using OpenAPI.

Prerequisites

An instance in elastic storage mode is created.
Vector engine optimization is enabled.
An initial account is created.
If you use a Resource Access Management (RAM) user, you must grant permissions to the RAM user. For more information, see Python SDK call example.

Procedure

Install the SDK.
Initialize the client.
Initialize the vector database.
Create a namespace.
Create a collection.
Upload vector data.
Retrieve vector data.

Install the SDK

For SDK download links, see SDK Reference.

Java

If you use Maven, add the following dependencies to your pom.xml file:

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>gpdb20160503</artifactId>
    <version>3.12.0</version>
</dependency>

<!-- The dependency version must be the same as or later than the following version. -->
<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>tea-openapi</artifactId>
    <version>0.3.1</version>
</dependency>
<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>tea</artifactId>
    <version>1.2.8</version>
</dependency>
<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>openapiutil</artifactId>
    <version>0.2.1</version>
</dependency>
<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>credentials-java</artifactId>
    <version>0.3.0</version>
</dependency>
<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>tea-util</artifactId>
    <version>0.2.21</version>
</dependency>

Go

Run the following commands to install the Go SDK:

go get github.com/alibabacloud-go/gpdb-20160503/v4/client
go get github.com/alibabacloud-go/darabonba-openapi/v2/client
go get github.com/alibabacloud-go/tea-utils/v2/service
go get github.com/alibabacloud-go/tea

Python 3

If you do not specify an SDK version, the latest version is automatically installed. The code is as follows:

pip install alibabacloud_gpdb20160503
pip install alibabacloud_tea_openapi

To install a specific version of the SDK, run the following commands. This topic uses alibabacloud_gpdb20160503 version 3.5.0 and alibabacloud_tea_openapi version 0.3.8 as examples.

pip install alibabacloud_gpdb20160503==3.5.0
pip install alibabacloud_tea_openapi==0.3.8

Initialize the client

Initialize the client to access OpenAPI. The following code provides an example. The environment variables are described as follows:

ALIBABA_CLOUD_ACCESS_KEY_ID: The AccessKey ID used to access OpenAPI.
ALIBABA_CLOUD_ACCESS_KEY_SECRET: The AccessKey secret used to access OpenAPI.

Java

import com.aliyun.gpdb20160503.Client;
import com.aliyun.teaopenapi.models.Config;

public static Client getClient() throws Exception {
    Config config = new Config();
    config.setAccessKeyId(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID"));
    config.setAccessKeySecret(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET"));
    config.setRegionId("cn-beijing");    // The region where the instance resides. Set this parameter based on the destination instance.
    config.setEndpoint("gpdb.****uncs.com");   // You do not need to configure this parameter if you access the instance over the Internet. Otherwise, configure this parameter based on the information at https://api.aliyun.com/product/gpdb.
    config.setMaxIdleConns(200);  // The maximum number of connections. Set this parameter based on the maximum number of concurrent connections for the client.
    return new Client(config);
}

Go

package main

import (
	"os"
	
	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	"github.com/alibabacloud-go/tea/tea"
)

func CreateClient() (_result *gpdb20160503.Client, _err error) {
	config := &openapi.Config{
		AccessKeyId:     tea.String(os.Getenv("ALIBABA_CLOUD_ACCESS_KEY_ID")),
		AccessKeySecret: tea.String(os.Getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET")),
	}
	// For more information about endpoints, see https://api.aliyun.com/product/gpdb.
	config.Endpoint = tea.String("gpdb.aliyuncs.com")
	_result = &gpdb20160503.Client{}
	_result, _err = gpdb20160503.NewClient(config)
	return _result, _err
}

Python 3

from alibabacloud_tea_openapi import models as open_api_models
from alibabacloud_gpdb20160503.client import Client
import os

ALIBABA_CLOUD_ACCESS_KEY_ID = os.environ['ALIBABA_CLOUD_ACCESS_KEY_ID']
ALIBABA_CLOUD_ACCESS_KEY_SECRET = os.environ['ALIBABA_CLOUD_ACCESS_KEY_SECRET']

def get_client():
    config = open_api_models.Config(
        access_key_id=ALIBABA_CLOUD_ACCESS_KEY_ID,
        access_key_secret=ALIBABA_CLOUD_ACCESS_KEY_SECRET
    )
    config.region_id = "cn-beijing"  # The region where the instance resides.
    return Client(config)

Initialize the vector database

Before you use vector search, initialize the knowledgebase database and related full-text index features.

Call The following code shows an example of how to call the operation. For more information about the parameters, see InitVectorDatabase.

Java

import com.aliyun.gpdb20160503.models.*;
import com.aliyun.gpdb20160503.Client;
import com.google.gson.Gson;

public static void initVectorDatabase() throws Exception {
    String region = "cn-beijing";  // The region where the instance resides.
    String instanceId = "gp-bp1c62r3l489****";  // The instance ID.
    String managerAccount = "myaccount";  // The initial account of the instance.
    String managerAccountPassword = "myaccount_password";  // The password of the initial account.

    InitVectorDatabaseRequest request = new InitVectorDatabaseRequest();
    request.setRegionId(region);
    request.setDBInstanceId(instanceId);
    request.setManagerAccount(managerAccount);
    request.setManagerAccountPassword(managerAccountPassword);
    Client client = getClient();
    InitVectorDatabaseResponse response = client.initVectorDatabase(request);
    System.out.println(response.getStatusCode());
    System.out.println(new Gson().toJson(response.getBody()));
}


public static void main(String[ ] args) throws Exception {
    initVectorDatabase();
}

Go

package main

import (
	"fmt"

	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	util "github.com/alibabacloud-go/tea-utils/v2/service"
	"github.com/alibabacloud-go/tea/tea"
)

func initVectorDatabase() {
	client, _err := CreateClient()
	if _err != nil {
		panic(_err)
	}

	initVectorDatabaseRequest := &gpdb20160503.InitVectorDatabaseRequest{
		RegionId:               tea.String("cn-beijing"),
		DBInstanceId:           tea.String("gp-bp1c62r3l489****"),
		ManagerAccount:         tea.String("myaccount"),
		ManagerAccountPassword: tea.String("myaccount_password"),
	}
	runtime := &util.RuntimeOptions{}
	response, _err := client.InitVectorDatabaseWithOptions(initVectorDatabaseRequest, runtime)
	if _err != nil {
		panic(_err)
	}
	fmt.Printf("response is %#v\n", response.Body)
}

func main() {
	initVectorDatabase()
}

Python 3

from alibabacloud_gpdb20160503 import models as gpdb_20160503_models

def init_vector_database():
    region_id = "cn-beijing"  # The region where the instance resides.
    dbinstance_id = "gp-bp1c62r3l489****"  # The instance ID.
    manager_account = "myaccount"  # The initial account of the instance.
    manager_account_password = "myaccount_password"  # The password of the initial account.

    request = gpdb_20160503_models.InitVectorDatabaseRequest(
        region_id=region_id,
        dbinstance_id=dbinstance_id,
        manager_account=manager_account,
        manager_account_password=manager_account_password
    )
    response = get_client().init_vector_database(request)
    print(f"init_vector_database response code: {response.status_code}, body:{response.body}")

if __name__ == '__main__':
    init_vector_database()

# output: body:
# {
#    "Message":"success",
#    "RequestId":"FC1E0318-E785-1F21-A33C-FE4B0301B608",
#    "Status":"success"
# }

Create a namespace

Namespaces are used for schema fencing. Before you use vectors, create at least one namespace or use the public namespace.

Call The following code shows an example of how to call the operation. For more information about the parameters, see CreateNamespace.

Java

import com.aliyun.gpdb20160503.models.*;
import com.aliyun.gpdb20160503.Client;
import com.google.gson.Gson;

public static void createNamespace() throws Exception {
    String region = "cn-beijing";  // The region where the instance resides.
    String instanceId = "gp-bp1c62r3l489****";  // The instance ID.
    String managerAccount = "myaccount";  // The initial account of the instance.
    String managerAccountPassword = "myaccount_password";  // The password of the initial account.

    String namespace = "vector_test";  // The namespace to be created.
    String namespacePassword = "vector_test_password";  // The password that corresponds to the namespace.

    CreateNamespaceRequest request = new CreateNamespaceRequest();
    request.setRegionId(region);
    request.setDBInstanceId(instanceId);
    request.setManagerAccount(managerAccount);
    request.setManagerAccountPassword(managerAccountPassword);
    request.setNamespace(namespace);
    request.setNamespacePassword(namespacePassword);
    Client client = getClient();
    CreateNamespaceResponse response = client.createNamespace(request);
    System.out.println(response.getStatusCode());
    System.out.println(new Gson().toJson(response.getBody()));
}


public static void main(String[ ] args) throws Exception {
    createNamespace();
}

Go

package main

import (
	"fmt"

	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	util "github.com/alibabacloud-go/tea-utils/v2/service"
	"github.com/alibabacloud-go/tea/tea"
)

func createNamespace() {
	client, _err := CreateClient()
	if _err != nil {
		panic(_err)
	}

	createNamespaceRequest := &gpdb20160503.CreateNamespaceRequest{
		RegionId:               tea.String("cn-beijing"),
		DBInstanceId:           tea.String("gp-bp1c62r3l489****"),
		ManagerAccount:         tea.String("myaccount"),
		ManagerAccountPassword: tea.String("myaccount_password"),
		Namespace:              tea.String("vector_test"),
		NamespacePassword:      tea.String("vector_test_password"),
	}
	runtime := &util.RuntimeOptions{}
	response, _err := client.CreateNamespaceWithOptions(createNamespaceRequest, runtime)
	if _err != nil {
		panic(_err)
	}
	fmt.Printf("response is %#v\n", response.Body)
}

func main() {
	createNamespace()
}

Python 3

def create_namespace():
    region_id = "cn-beijing"  # The region where the instance resides.
    dbinstance_id = "gp-bp1c62r3l489****"  # The instance ID.
    manager_account = "myaccount"  # The initial account of the instance.
    manager_account_password = "myaccount_password"  # The password of the initial account.
    namespace = "vector_test"  # The namespace to be created.
    namespace_password = "vector_test_password"  # The password that corresponds to the namespace.

    request = gpdb_20160503_models.CreateNamespaceRequest(
        region_id=region_id,
        dbinstance_id=dbinstance_id,
        manager_account=manager_account,
        manager_account_password=manager_account_password,
        namespace=namespace,
        namespace_password=namespace_password
    )
    response = get_client().create_namespace(request)
    print(f"create_namespace response code: {response.status_code}, body:{response.body}")

if __name__ == '__main__':
    create_namespace()

# output: body:
# {
#    "Message":"success",
#    "RequestId":"78356FC9-1920-1E09-BB7B-CCB6BD267124",
#    "Status":"success"
# }

After the namespace is created, you can view the corresponding schema in the knowledgebase database of the instance.

SELECT schema_name FROM information_schema.schemata;

Create a collection

Collections are used to store vector data and are fenced by namespaces.

Call The following code shows an example of how to call the operation. For more information about the parameters, see Create a vector collection.

Java

import com.aliyun.gpdb20160503.models.*;
import com.aliyun.gpdb20160503.Client;
import com.google.gson.Gson;

import java.util.HashMap;
import java.util.Map;

public static void createCollection() throws Exception {
    String region = "cn-beijing";  // The region where the instance resides.
    String instanceId = "gp-bp1c62r3l489****";  // The instance ID.
    String managerAccount = "myaccount";  // The initial account of the instance.
    String managerAccountPassword = "myaccount_password";  // The password of the initial account.
    String namespace = "vector_test";  // The created namespace.

    String collection = "document";  // The collection to be created.
    Map<String, String> metadata = new HashMap<>();
    metadata.put("title", "text");
    metadata.put("link", "text");
    metadata.put("content", "text");
    metadata.put("pv", "int");
    String fullTextRetrievalFields = "title,content";  // The fields for full-text index.
    Long dimension = 10L;  // The vector dimensions.

    CreateCollectionRequest request = new CreateCollectionRequest();
    request.setRegionId(region);
    request.setDBInstanceId(instanceId);
    request.setManagerAccount(managerAccount);
    request.setManagerAccountPassword(managerAccountPassword);
    request.setNamespace(namespace);
    request.setCollection(collection);
    request.setMetadata(new Gson().toJson(metadata));
    request.setFullTextRetrievalFields(fullTextRetrievalFields);
    request.setDimension(dimension);
    request.setParser("zh_cn");
    Client client = getClient();
    CreateCollectionResponse response = client.createCollection(request);
    System.out.println(response.getStatusCode());
    System.out.println(new Gson().toJson(response.getBody()));
}


public static void main(String[ ] args) throws Exception {
    createCollection();
}

Go

package main

import (
	"fmt"

	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	util "github.com/alibabacloud-go/tea-utils/v2/service"
	"github.com/alibabacloud-go/tea/tea"
)

func createCollection() {
	client, _err := CreateClient()
	if _err != nil {
		panic(_err)
	}

	createCollectionRequest := &gpdb20160503.CreateCollectionRequest{
		RegionId:                tea.String("cn-beijing"),
		DBInstanceId:            tea.String("gp-bp1c62r3l489****"),
		ManagerAccount:          tea.String("myaccount"),
		ManagerAccountPassword:  tea.String("myaccount_password"),
		Namespace:               tea.String("vector_test"),
		Collection:              tea.String("document"),
		Dimension:               tea.Int64(3),
		Parser:                  tea.String("zh_cn"),
		FullTextRetrievalFields: tea.String("title,content"),
		Metadata:                tea.String("{\"title\":\"text\",\"content\":\"text\",\"response\":\"int\"}"),
	}
	runtime := &util.RuntimeOptions{}
	response, _err := client.CreateCollectionWithOptions(createCollectionRequest, runtime)
	if _err != nil {
		panic(_err)
	}
	fmt.Printf("response is %#v\n", response.Body)
}

func main() {
	createCollection()
}

Python 3

def create_collection():
    region_id = "cn-beijing"  # The region where the instance resides.
    dbinstance_id = "gp-bp1c62r3l489****"  # The instance ID.
    manager_account = "myaccount"  # The initial account of the instance.
    manager_account_password = "myaccount_password"  # The password of the initial account.
    namespace = "vector_test"  # The created namespace.
    collection = "document"  # The collection to be created.
    metadata = '{"title":"text", "content": "text", "page":"int"}'
    full_text_retrieval_fields = "title,content"  # The fields for full-text index.
    dimension = 8  # The vector dimensions.

    request = gpdb_20160503_models.CreateCollectionRequest(
        region_id=region_id,
        dbinstance_id=dbinstance_id,
        manager_account=manager_account,
        manager_account_password=manager_account_password,
        namespace=namespace,
        collection=collection,
        metadata=metadata,
        full_text_retrieval_fields=full_text_retrieval_fields,
        dimension=dimension
    )
    response = get_client().create_collection(request)
    print(f"create_collection response code: {response.status_code}, body:{response.body}")

if __name__ == '__main__':
    create_collection()

# output: body:
# {
#    "Message":"success",
#    "RequestId":"7BC35B66-5F49-1E79-A153-8D26576C4A3E",
#    "Status":"success"
# }

After the collection is created, you can view the corresponding table in the knowledgebase database of the instance.

SELECT tablename FROM pg_tables WHERE schemaname='vector_test';

Upload vector data

Upload the prepared embedding vector data to the corresponding collection.

Call The following code shows an example of how to call the operation. For more information about the parameters, see UpsertCollectionData.

Java

import com.aliyun.gpdb20160503.models.*;
import com.aliyun.gpdb20160503.Client;
import com.google.gson.Gson;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public static void upsertCollectionData() throws Exception {
    String region = "cn-beijing";  // The region where the instance is located.
    String instanceId = "gp-bp1c62r3l489****";  // The instance ID.
    String namespace = "vector_test";  // The created namespace.
    String namespacePassword = "vector_test_password";  // The namespace password.
    String collection = "document";  // The created collection.

    UpsertCollectionDataRequest request = new UpsertCollectionDataRequest();
    request.setRegionId(region);
    request.setDBInstanceId(instanceId);
    request.setNamespace(namespace);
    request.setNamespacePassword(namespacePassword);
    request.setCollection(collection);
    request.setRows(getRows());
    Client client = getClient();
    UpsertCollectionDataResponse response = client.upsertCollectionData(request);
    System.out.println(response.getStatusCode());
    System.out.println(new Gson().toJson(response.getBody()));
}

public static List<UpsertCollectionDataRequest.UpsertCollectionDataRequestRows> getRows() {
    List<UpsertCollectionDataRequest.UpsertCollectionDataRequestRows> rows = new ArrayList<>();
    UpsertCollectionDataRequest.UpsertCollectionDataRequestRows row = new UpsertCollectionDataRequest.UpsertCollectionDataRequestRows();
    Map<String, String> metadata = new HashMap<>();
    metadata.put("title", "test document");
    metadata.put("content", "test content");
    metadata.put("link", "http://127.0.0.1/document1");
    metadata.put("pv", "1000");
    row.setMetadata(metadata);
    row.setVector(Arrays.asList(0.2894745251078251,0.5364747050266715,0.1276845661831275,0.22528871956822372,0.7009319238651552,0.40267406135256123,0.8873626696379067,0.1248525955774931,0.9115507046412368,0.2450859133174706));
    rows.add(row);
    return rows;
}


public static void main(String[ ] args) throws Exception {
    upsertCollectionData();
}

Go

package main

import (
	"fmt"

	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	util "github.com/alibabacloud-go/tea-utils/v2/service"
	"github.com/alibabacloud-go/tea/tea"
)

func upsertCollectionData() {
	client, _err := CreateClient()
	if _err != nil {
		panic(_err)
	}

	rows0Metadata := map[string]*string{
		"title":    tea.String("test_document"),
		"content":  tea.String("test_content"),
		"response": tea.String("1"),
	}
	rows0 := &gpdb20160503.UpsertCollectionDataRequestRows{
		Metadata: rows0Metadata,
		Id:       tea.String("0CB55798-ECF5-4064-B81E-FE35B19E01A6"),

		Vector:   [ ]*float64{tea.Float64(0.2894745251078251), tea.Float64(0.5364747050266715), tea.Float64(0.1276845661831275)},

	}
	upsertCollectionDataRequest := &gpdb20160503.UpsertCollectionDataRequest{
		RegionId:          tea.String("cn-beijing"),

		Rows:              [ ]*gpdb20160503.UpsertCollectionDataRequestRows{rows0},

		DBInstanceId:      tea.String("gp-bp1c62r3l489****"),
		Collection:        tea.String("document"),
		Namespace:         tea.String("vector_test"),
		NamespacePassword: tea.String("vector_test_password"),
	}
	runtime := &util.RuntimeOptions{}
	response, _err := client.UpsertCollectionDataWithOptions(upsertCollectionDataRequest, runtime)
	if _err != nil {
		panic(_err)
	}
	fmt.Printf("response is %#v\n", response.Body)
}

func main() {
	upsertCollectionData()
}

Python 3

def upsert_collection_data():
    region_id = "cn-beijing"  # The region where the instance resides.
    dbinstance_id = "gp-bp1c62r3l489****"  # The instance ID.
    namespace = "vector_test"  # The created namespace.
    namespace_password = "vector_test_password"  # The password of the namespace.
    collection = "document"  # The created collection.


    rows = [ ]

    rows.append(gpdb_20160503_models.UpsertCollectionDataRequestRows(
        id="0CB55798-ECF5-4064-B81E-FE35B19E01A6",
        metadata={
            "page": 1,
            "content": "test_content",
            "title": "test_document"
        },
        vector=[0.2894745251078251, 0.5364747050266715, 0.14858841010401188, 0.42140750105351877,
                0.5780346820809248, 0.1145475372279496, 0.04329004329004329, 0.43246796493549741]
    ))

    request = gpdb_20160503_models.UpsertCollectionDataRequest(
        region_id=region_id,
        dbinstance_id=dbinstance_id,
        namespace=namespace,
        namespace_password=namespace_password,
        collection=collection,
        rows=rows,
    )
    response = get_client().upsert_collection_data(request)
    print(f"upsert_collection_data response code: {response.status_code}, body:{response.body}")

if __name__ == '__main__':
    upsert_collection_data()

# output: body:
# {
#    "Message":"success",
#    "RequestId":"8FEE5D1E-ECE8-1F2F-A17F-48039125CDC3",
#    "Status":"success"
# }

After the upload is complete, you can view the data in the knowledgebase database of the instance.

SELECT * FROM vector_test.document;

Retrieve vector data

Prepare the query vectors or full-text index fields for retrieval, and then call the query operation.

Call The following code shows an example of how to call the operation. For more information about the parameters, see QueryCollectionData.

Java

import com.aliyun.gpdb20160503.models.QueryCollectionDataRequest;
import com.aliyun.gpdb20160503.models.QueryCollectionDataResponse;
import com.aliyun.gpdb20160503.Client;
import com.google.gson.Gson;

import java.util.Arrays;

public static void queryCollectionData() throws Exception {
    QueryCollectionDataRequest request = new QueryCollectionDataRequest();
    request.setDBInstanceId("gp-bp1c62r3l489****");
    request.setCollection("document");
    request.setNamespace("vector_test");
    request.setNamespacePassword("vector_test_password");
    request.setContent("test");
    request.setFilter("pv > 10");
    request.setTopK(10L);
    request.setVector(Arrays.asList(0.7152607422256894,0.5524872066437732,0.1168505269851303,0.704130971473022,0.4118874999967596,0.2451574619214022,0.18193414783144812,0.3050522957905741,0.24846180714868163,0.0549715380856951));
    request.setRegionId("cn-beijing");

    Client client = getClient();
    QueryCollectionDataResponse response = client.queryCollectionData(request);
    System.out.println(response.getStatusCode());
    System.out.println(new Gson().toJson(response.getBody()));
}


public static void main(String[ ] args) throws Exception {
    queryCollectionData();
}

The following result is returned:

{
  "Matches": {
    "match": [
      {
        "Id": "0CB55798-ECF5-4064-B81E-FE35B19E01A6",
        "Metadata": {
          "title":"test_document",
          "content":"test_content",
          "link":"http://127.0.0.1/document1",
          "pv":"1000"
        },
        "Values": [
           0.2894745251078251,
           0.5364747050266715,
           0.1276845661831275,
           0.22528871956822372,
           0.7009319238651552,
           0.40267406135256123,
           0.8873626696379067,
           0.1248525955774931,
           0.9115507046412368,
           0.2450859133174706
        ]
      }
    ]
  },
  "RequestId": "ABB39CC3-4488-4857-905D-2E4A051D0521",
  "Status": "success"
}

Go

package main

import (
	"fmt"

	openapi "github.com/alibabacloud-go/darabonba-openapi/v2/client"
	gpdb20160503 "github.com/alibabacloud-go/gpdb-20160503/v4/client"
	util "github.com/alibabacloud-go/tea-utils/v2/service"
	"github.com/alibabacloud-go/tea/tea"
)

func queryCollectionData() {
	client, _err := CreateClient()
	if _err != nil {
		panic(_err)
	}

	queryCollectionDataRequest := &gpdb20160503.QueryCollectionDataRequest{
		RegionId:          tea.String("cn-beijing"),
		DBInstanceId:      tea.String("gp-bp1c62r3l489****"),
		Collection:        tea.String("document"),
		Namespace:         tea.String("vector_test"),
		NamespacePassword: tea.String("vector_test_password"),
		Content:           tea.String("test"),
		Filter:            tea.String("response > 0"),
		TopK:              tea.Int64(10),

		Vector:            [ ]*float64{tea.Float64(0.7152607422256894), tea.Float64(0.5524872066437732), tea.Float64(0.1168505269851303)},

	}
	runtime := &util.RuntimeOptions{}
	response, _err := client.QueryCollectionDataWithOptions(queryCollectionDataRequest, runtime)
	if _err != nil {
		panic(_err)
	}
	fmt.Printf("response is %#v\n", response.Body)
}

func main() {
	queryCollectionData()
}

The following result is returned:

{
   "Matches": {
      "match": [
         {
            "Id": "0CB55798-ECF5-4064-B81E-FE35B19E01A6",
            "Metadata": {
               "content": "test_content",
               "response": "1",
               "source": "3",
               "title": "test_document"
            },
            "MetadataV2": {
               "content": "test_content",
               "response": 1,
               "source": 3,
               "title": "test_document"
            },
            "Score": 0.9132830731723668,
            "Values": {
               "value": [
                  0.28947452,
                  0.5364747,
                  0.12768456
               ]
            }
         }
      ]
   },
   "RequestId": "707D2202-61A6-53DF-AAD2-E8DE276CE292",
   "Status": "success"
}

Python 3

def query_collection_data():
    region_id = "cn-beijing"  # Region where the instance is located
    dbinstance_id = "gp-bp1c62r3l489****"  # Instance ID
    namespace = "vector_test"  # Created namespace
    namespace_password = "vector_test_password"  # Namespace password
    collection = "document"  # Created collection

    content = "test query"
    vector = [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0]

    request = gpdb_20160503_models.QueryCollectionDataRequest(
        region_id=region_id,
        dbinstance_id=dbinstance_id,
        namespace=namespace,
        namespace_password=namespace_password,
        collection=collection,
        top_k=5,
        content=content,
        vector=vector,
    )
    response = get_client().query_collection_data(request)
    print(f"query_collection_data response code: {response.status_code}, body:{response.body}")

if __name__ == '__main__':
    query_collection_data()

# output:
# query_collection_data response code: 200, body:{'Matches': {'match': [{'Id': '0CB55798-ECF5-4064-B81E-FE35B19E01A6', 'Metadata': {'source': 1, 'page': '1', 'title': 'Test document', 'content': 'Test content'}, 'Score': 0.7208109110736349, 'Values': {'value': [0.28947452, 0.5364747, 0.1485884, 0.4214075, 0.5780347, 0.114547536, 0.043290045, 0.7]}}]}, 'RequestId': '709E2C82-FE25-1722-9DBB-00AD0F85ABBB', 'Status': 'success'}

References

For more information about how to use clients in other languages, see the pgvector Compatibility Mode Guide.