AnalyticDB for PostgreSQL:Use SQL to import vector data

Prerequisites

• Vector search engine optimization is enabled by using one of the following methods:

  • Enable vector search engine optimization when you create an AnalyticDB for PostgreSQL instance. For more information, see Create an instance.

  • Enable vector search engine optimization after an AnalyticDB for PostgreSQL instance is created. For more information, see Enable or disable vector search engine optimization.

• An AnalyticDB for PostgreSQL database is installed with the vector search extension FastANN. You can run the \dx fastann command to check whether the FastANN extension is installed. If relevant information about the extension is returned, the extension is installed. If no information is returned,

  submit a ticket to install the extension.

Test data

To facilitate your test, AnalyticDB for PostgreSQL provides a test data file named vector_sample_data.csv.

The following table describes the schema of the file.

In the Linux system, you can run a command to download the test data. Sample command:

wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230606/uzkx/vector_sample_data.csv

Import data

In this example, the COPY statement is used to import on-premises data.

1. Connect to a database.

2. Create and switch to another database.

   -- Create a database named testdb.
   CREATE DATABASE testdb;
   
   -- Switch to the testdb database.
   \c testdb

3. Create a table that has the same schema as the test data file. The table must contain a vector column.

   CREATE SCHEMA IF NOT EXISTS vector_test;
   CREATE TABLE IF NOT EXISTS vector_test.car_info
   (
     id bigint NOT NULL,
     market_time timestamp,
     color varchar(10),
     price int,
     feature float4[],
     PRIMARY KEY(id)
   ) DISTRIBUTED BY(id);
   

4. Create indexes.

   In this example, structured indexes are created for the launch time, color, and price fields, and a vector index is created for the feature vector field.

   -- Change the storage format of the vector column to PLAIN.
   ALTER TABLE vector_test.car_info ALTER COLUMN feature SET STORAGE PLAIN;
   
   -- Create structured indexes.
   CREATE INDEX ON vector_test.car_info(market_time);
   CREATE INDEX ON vector_test.car_info(color);
   CREATE INDEX ON vector_test.car_info(price);
   
   -- Create a vector index.
   CREATE INDEX ON vector_test.car_info USING ann(feature) WITH (dim='10', pq_enable='0');

5. Import the test data to the table.

   \COPY vector_test.car_info FROM '/DATA_PATH/vector_sample_data.csv';

   Set /DATA_PATH/ to the directory of the test data. For example, if the test data is downloaded to the /home directory, replace /DATA_PATH/vector_sample_data.cs with /home/vector_sample_data.csv.

   If COPY 10000 is returned, the data import is successful.

References

• Use the \copy command to import data from your computer to AnalyticDB for PostgreSQL

• Java

• Perform vector search

What to do next

Perform vector analysis