This page describes how to generate and store vector embeddings. For anoverview, seeVector embedding storage.

Before you begin

You need to have a Cloud SQL instance with thevector database flags enabled.

Generate vector embeddings based on row data

You can generate a vector embedding for a given row's data by using a textembedding API such asVertex AIorOpenAI.You can use any text embedding API with Cloud SQL vectorembeddings. However, you must use the same text embedding API for query stringvector generation. You can't combine different APIs for source data and queryvectorization.

For example, you cangenerate a vector embedding from Vertex AI:

fromvertexai.language_modelsimportTextEmbeddingModeldeftext_embedding()->list:"""Text embedding with a Large Language Model."""model=TextEmbeddingModel.from_pretrained("text-embedding-004")embeddings=model.get_embeddings(["What is life?"])forembeddinginembeddings:vector=embedding.valuesprint(f"Length of Embedding Vector:{len(vector)}")returnvectorif__name__=="__main__":text_embedding()

Store vector embeddings

This section provides example statements for storing vector embeddings inCloud SQL.

Create a new table with a vector embedding column

Use theCREATE TABLE statement with a column that uses theVECTOR data type.

Use the following syntax to create the table:

CREATETABLETABLE_NAME(idINTEGERPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(60),EMBEDDING_COLUMN_NAMEVECTOR(VECTOR_DIMENSIONS)USINGVARBINARY);

Replace the following parameters:

• TABLE_NAME: the name of the table you where you want to store theembeddings.
• EMBEDDING_COLUMN_NAME: the name of column that stores the embedding.
• VECTOR_DIMENSIONS: the number of dimensions to use for the embedding.

In the following example, the embedding column has a vector with threedimensions. The data stored in this column has theVARBINARY data type.

CREATETABLEbooks(idINTEGERPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(60),embeddingVECTOR(3)USINGVARBINARY);

Add a vector embedding column to an existing table

Use theALTER TABLE statement to add a vector embedding column to an existingtable. The column must use theVECTOR data type to hold the embedding.

In the following example, an embedding column that has a vector with threedimensions is inserted into the table. The data stored in this column has theVARBINARY data type.

ALTERTABLEbooksADDCOLUMNembeddingVECTOR(3)USINGVARBINARY;

Insert a vector embedding

UseINSERT with thestring_to_vector function to insert a vectorembedding values into a table.

In the following example, a vector with three dimensions is inserted into theembedding column.

INSERTINTObooks(title,embedding)VALUES(('book title',string_to_vector('[1,2,3]')));

Insert multiple vector embeddings

UseINSERT with thestring_to_vector functionto insert a comma-separated list of vector embeddings.

In the following statement, two embeddings, each containing a vector with threedimensions and is inserted into the embedding column.

INSERTINTObooks(title,embedding)VALUES(('book title',string_to_vector('[1,2,3]')),('book title',string_to_vector('[4,5,6]')));

Upsert a vector embedding

Use anINSERT orUPDATE operation on a table with thestring_to_vector functionto add a vector embedding column, using the following syntax.

In the following statement, an upsert is used to insert or update the embeddingcolumn with an embedding that contains a vector with three dimensions.

INSERTINTObooks(id,title,embedding)VALUES((1,'book title',string_to_vector('[1,2,3]')))ONDUPLICATEKEYUPDATEembedding=string_to_vector('[1,2,3]');

Update a vector embedding

UseUPDATE with thestring_to_vector functionto update a vector embedding.

In the following statement,UPDATE is used to update the embedding column witha vector with three dimensions.

UPDATEbooksSETembedding=string_to_vector('[7,8,9]')WHEREid=1;

Retrieve vector embeddings

To retrieve vector embeddings, use the Cloud SQLvector_to_string functionalong with the name of the embedding.

In the following statement, the embedding column is retrieved to view.

SELECTvector_to_string(embedding)FROMbooksWHEREid=1;

Delete a vector embedding

UseDELETE with thestring_to_vector functionto remove a vector embedding from a table. If there's a vector index, you mustfirst delete it. For more information, seeDrop a vector index.

In the following statement,DELETE is used to delete the value in the embeddingcolumn.

DELETEFROMbooksWHEREembedding=string_to_vector('[1,2,3]');

What's next

• Read theoverview about vector search on Cloud SQL.
• Learn how toenable and disable vector embeddings on your instance.
• Learn how tocreate vector indexes.
• Learn how toperform searches on vector embeddings.