Using Vector Search

Before you can store embeddings as vectors in InterSystems IRIS, you first need to create them from a source. In general, you can transform a piece of text into an embedding in four steps.

1. Import a package to use to turn your text into a series of embeddings.

2. Pre-process your text to best fit your chosen embedding model’s input specifications.

3. Instantiate the model and convert your text to the embeddings, using your chosen package’s workflow.

With Embedded Python, you can execute Python code that converts text to embeddings (using whichever package you like) alongside ObjectScript code that can insert those embeddings directly into your database. For information on importing a Python package into InterSystems IRIS, see Install and Import Python Packages.

Example: Create Embeddings with Embedded Python

The following example takes an input list of sentences, passed as a built-in Python list, converts them to embeddings using the sentence_transformers package, and returns a list of the embeddings for later insertion into a table. See Performing an INSERT for more information on inserting the embeddings. This example assumes that the input sentences have already been pre-processed to match the embedding model’s specifications.

ClassMethod GetEmbeddingPy(sentences) [ Language = python ]
{
  import json

  # import the package
  import sentence_transformers

  # perform any preprocessing

  # create the model and form the embeddings
  model = sentence_transformers.SentenceTransformer('all-MiniLM-L6-v2')
  embeddings = model.encode(sentences)

  return embeddings
}

Once you have a list of strings that represent embeddings, you can insert them into your table as VECTORs either with an INSERT statement or by creating an object and storing the embedding as a property of the object. The following example demonstrates how to insert data with an INSERT.

Example

For each embedding, execute an INSERT statement that adds the embedding to the desired table. Use TO_VECTOR to convert the string representation of the embedding to a VECTOR.

In the following command, one embedding is inserted into a table called Sample.Description that has two columns: one for the embedding that represents a textual description and the other for a unique identifier that can be used to link the embedding with the text it is derived from (implicitly stored in a separate table with the same unique identifier). (Note that the example uses ?s as placeholders for the embedding and the unique identifier, as these are typically supplied programmatically as parameters to the statement.)

INSERT INTO Sample.Descriptions (DescriptionEmbedding, UID) 
   VALUES (TO_VECTOR(?,double), ?)

The following code samples use this query to insert a single embedding into the table.

ObjectScript Java/JDBC Python/DB-API .NET/ODBC

 ClassMethod InsertEmbeddings(embedding As %String, uid As %Integer)
 {
   set sc=$$$OK
   try {
     set myquery = "INSERT INTO Sample.Descriptions (DescriptionEmbedding, UID)" 
                  _"VALUES (TO_VECTOR(?,double), ?)"
     set tStatement = ##class(%SQL.Statement).%New()
     $$$ThrowOnError(tStatement.%Prepare(query))
 
     set rset = tStatement.%Execute(embedding, uid)
     if (rset.%SQLCODE < 0) {
       throw ##class(%Exception.SQL).CreateFromSQLCODE(rset.%SQLCODE,rset.%Message)
     }
   }
   catch e {
     set sc = e.AsStatus()
     return sc
   }
   return 0
 }

 public void InsertEmbeddings(String embeddings, Integer uid) {
   try {
     // set connection parameters
     IRISDataSource ds = new IRISDataSource();
     ds.setServerName("127.0.0.1");
     ds.setPortNumber(51776);
     ds.setDatabaseName("USER");
     ds.setUser("_SYSTEM");
     ds.setPassword("SYS");
     IRISConnection conn = ds.GetConnection();
 
     String sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " + 
                  "VALUES (TO_VECTOR(?,double), ?)";
     PreparedStatement pstmt = conn.prepareStatement(sql);
 
     pstmt.SetString(embedding);
     pstmt.SetInt(uid);
  
     pstmt.executeUpdate();
  
     pstmt.close();
   } catch (Exception ex) {
     System.out.println("caught exception: "
             + ex.GetClass().getName() + ": " + ex.GetMessage());
   }
 }

 def insertEmbeddings(embeddings, uid):
   // set the connection parameters
   conn_string = "localhost:1972/USER"
   username = "_system"
   password = "SYS"
   connection = iris.connect(conn_string, username, password)
   cursor = connection.cursor()
   
   try:
     sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " +
           "VALUES (TO_VECTOR(?,double), ?)"
     params = [embeddings,uid]
     cursor.execute(sql,params))
     cursor.close()
   except Exception as ex:
     print(ex)
   finally:
     if cursor:
       cursor.close()
     if connection:
       connection.close()
   

 static void InsertEmbeddings(string emb, Integer uid)
 {
    // set the connection parameters
    String host = "127.0.0.1";
    String port = "51776";
    String username = "_SYSTEM";
    String password = "SYS";
    String namespace = "USER";

    IRISConnection conn = new IRISConnection();
    IRISConnect.ConnectionString = "Server = " + host
        + "; Port = " + port + "; Namespace = " + namespace
        + "; Password = " + password + "; User ID = " + username;

    conn.Open();

    String sql = "INSERT INTO Sample.Embeddings (Embedding, UID) " +
                 "VALUES (TO_VECTOR(?,double), ?)";
    IRISCommand cmd = new IRISCommand(sql, conn);
    cmd.ExecuteNonQuery();

    cmd.Dispose();
    conn.Close();
 }

Before you can insert embeddings into a table, you must decide on an embedding model you wish to use to convert text into an embedding. Once you have selected a model, you can insert metadata for that model into the %Embedding.Config table to create an embedding configuration. The embedding configuration stores the information needed to make API calls to your chosen embedding model.

The %Embedding.Config table has five columns:

• Name: The unique and valid identifier used to refer to the model.

• Configuration: A JSON-formatted string that includes particular data for a particular source.

• EmbeddingClass: The name of the ObjectScript class that extends %Embedding.Interface and defines logic for retrieving embeddings from the endpoint. InterSystems IRIS provides two such classes out of the box: %Embedding.OpenAI and %Embedding.SentenceTransformers. To use an embedding model from a different source, you must manually define a new class that extends the %Embedding.Interface class.

• VectorLength: The length of the vector (number of dimensions) the embedding model returns.

• Description: An optional description of this endpoint.

The following example inserts an embedding configuration for an OpenAI embedding modelOpens in a new tab into the %Embedding.Config table.

INSERT INTO %Embedding.Config (Name, Configuration, EmbeddingClass, VectorLength, Description)
  VALUES ('my-opanai-config', 
          '{"apiKey":"<api key>", 
            "sslConfig": "llm_ssl", 
            "modelName": "text-embedding-3-small"}',
          '%Embedding.OpenAI', 
          1536,  
          'a small embedding model provided by OpenAI') 

Once you have stored an embedding configuration in the %Embedding.Config table, you can create a table that uses this configuration to populate EMBEDDING-typed columns.

When defining a column with the embedding type, you must specify two arguments for the model and source parameters. The model parameter is the name of the embedding configuration that converts text into an embedding. The source parameter is a comma separated list of properties in the class that are used to calculate the embedding.

The following example creates a table with four columns, two of which store strings and two of which store embeddings of those strings. Both of the EMBEDDING-typed fields use an embedding configuration called my-openai-config.

CREATE TABLE Embedding.Example (
  Description VARCHAR(200),
  Name VARCHAR(30),
  DescriptionEmbedding EMBEDDING('my-opanai-config','Description'),
  NameEmbedding EMBEDDING('my-opanai-config','Name')
)

The following example demonstrates how to use SQL to issue a query that uses VECTOR_DOT_PRODUCT to find the most semantically similar descriptions to an input sentence. Convert an input search term to an embedding with the EMBEDDING function and use either VECTOR_DOT_PRODUCT or VECTOR_COSINE within an ORDER BY clause to return the most similar pieces of text. In this example, note that you do not need to specify the name of the embedding model in the EMBEDDING function because the function is used on an EMBEDDING-typed field in a table and the system automatically uses the embedding model associated with the field. To select only the most similar results, use a TOP clause. This example shows a SQL statement that selects the five descriptions that are the most similar to the input user query. (Note that the example uses ? as a placeholder for the embedding of the search term, as this value is typically provided as a parameter, not as a literal.)

SELECT TOP 5 Description FROM Embedding.Example
  ORDER BY VECTOR_DOT_PRODUCT(DescriptionEmbedding, 
                              EMBEDDING(?)) DESC

The following demonstrates an ObjectScript method that executes this query in Dynamic SQL, iterating through the result set to append the descriptions in a long string:

 ClassMethod GetSimilarDesc(searchTerm As %String)
 {
   set sc = $$$OK
   try {
       set query = "SELECT TOP 5 Description FROM Embedding.Example
                  _"ORDER BY VECTOR_DOT_PRODUCT(DescriptionEmbedding, 
                  _"EMBEDDING(?,'my-openai-config')) DESC"
       set tStatement = ##class(%SQL.Statement).%New()
       $$$ThrowOnError(tStatement.%Prepare(query))
       
       set rset = tStatement.%Execute(searchTerm)
       if (rset.%SQLCODE < 0) {
         throw ##class(%Exeception.SQL).CreateFromSQLCODE(rset.%SQLCODE,rset.%Message)
       }
       
       // process retrieved descriptions here and return the result
       set retrievedInfo = ""
       while rset.%Next() {
         set retrievedInfo = retrievedInfo_" Description: "_rset.%Get("Description")
       }
       return retrievedInfo
   }
   catch e {
      set sc = e.AsStatus()
      return sc
   }
 }