Using Vector Search

Translating Text to Embeddings

Before you can store embeddings as vectors in InterSystems IRIS, you first need to create them from a text 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.

4. Convert each individual embedding to a string. This step is necessary to convert an embedding to a VECTOR at a later point.

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 Inserting VECTOR-typed Data 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

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

  # convert the embeddings to a string
  embeddings_list = [str(embedding.tolist()) for embedding in embeddings]
  return embeddings_list
}

Inserting VECTOR-typed Data

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.

In this example, the embedding is stored in a table alongside a unique identifier. When the embedding vectors are inserted in these examples, the assumption is that the source text is stored in another table with the same unique identifier. The shared value of the unique identifiers allows you to perform a JOIN, linking an embedding vector with the text it represents. If you choose to store the embeddings and text in the same table, the unique identifier is not necessary.

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.Embeddings that has two columns: one for the embedding itself 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.Embeddings (Embedding, 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.Embeddings (Embedding, 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();
 }

Example

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. After converting an input search term to an embedding, use either VECTOR_DOT_PRODUCT or VECTOR_COSINE within an ORDER BY clause to return the most similar pieces of text. Additionally, use a TOP clause to select only the most similar results.

The query selects the five most relevant descriptions based on an input query. The example assumes that the embeddings and the descriptions are stored in separate tables (Sample.Embeddings and Sample.Descriptions, respectively), with a unique ID field (called UID) identifying which embeddings correspond with which descriptions. A JOIN on this field links the embeddings with the descriptions. If you choose to store embeddings alongside the text they represent, you do not need to perform a JOIN.

The following 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 descrip.Description FROM Sample.Embeddings emb
  JOIN Sample.Description descrip ON emb.UID = descrip.UID
  ORDER BY VECTOR_DOT_PRODUCT(emb.Embeddings, 
                            TO_VECTOR(?,double)) 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(searchTerms As %String)
 {
   set sc = $$$OK
   try {
       set query = "SELECT TOP 5 descrip.Description FROM Sample.Embeddings emb"
                  _"JOIN Sample.Description descrip ON emb.UID = descrip.UID"
                  _"ORDER BY VECTOR_DOT_PRODUCT(emb.Embeddings,"
                  _"TO_VECTOR(?,double)) DESC"
       set tStatement = ##class(%SQL.Statement).%New()
       $$$ThrowOnError(tStatement.%Prepare(query))
  
       // convert input string to an embedding
       set searchTermsEmbedding = ..GetEmbeddings(searchTerms)
       
       set rset = tStatement.%Execute(searchTermsEmbedding)
       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
   }
 }