Sap hana cloud vector engine integration

SAP HANA Cloud Vector Engine is a vector store fully integrated into the SAP HANA Cloud database.

Setup

Install the langchain-hana external integration package, as well as the other packages used throughout this notebook.

pip install langchain-hana

Credentials

Ensure your SAP HANA instance is running. Load your credentials from environment variables and create a connection:

import os

from dotenv import load_dotenv
from hdbcli import dbapi

load_dotenv()
# Use connection settings from the environment
connection = dbapi.connect(
    address=os.environ.get("HANA_DB_ADDRESS"),
    port=os.environ.get("HANA_DB_PORT"),
    user=os.environ.get("HANA_DB_USER"),
    password=os.environ.get("HANA_DB_PASSWORD")
)

Learn more about SAP HANA in What is SAP HANA?.

Initialization

To initialize a HanaDB vector store, you need a database connection and an embedding instance. SAP HANA Cloud Vector Engine supports both external and internal embeddings.

Using external embeddings

# | output: false
# | echo: false
from langchain_openai import OpenAIEmbeddings

embeddings = OpenAIEmbeddings(model="text-embedding-3-large")

Using internal embeddings

Alternatively, you can compute embeddings directly in SAP HANA using its native VECTOR_EMBEDDING() function. To enable this, create an instance of HanaInternalEmbeddings with your internal model ID and pass it to HanaDB. Note that the HanaInternalEmbeddings instance is specifically designed for use with HanaDB and is not intended for use with other vector store implementations. For more information about internal embedding, see the SAP HANA VECTOR_EMBEDDING Function.

Caution: Ensure NLP is enabled in your SAP HANA Cloud instance.

from langchain_hana import HanaInternalEmbeddings

embeddings = HanaInternalEmbeddings(internal_embedding_model_id="SAP_NEB.20240715")

# optionally, you can specify a remote source to use models from your deployed SAP AI CORE instance

# embeddings = HanaInternalEmbeddings(
#     internal_embedding_model_id="your-embedding-model-id",
#     remote_source_schema="your-remote-source-schema-name",
#     remote_source="your-remote-source-name",
# )

Once you have your connection and embedding instance, create the vector store by passing them to HanaDB along with a table name for storing vectors:

from langchain_hana import HanaDB

db = HanaDB(
    embedding=embeddings, connection=connection, table_name="MY_TABLE"
)

Manage vector store

Once you have created your vector store, we can interact with it by adding and deleting different items.

Add items to vector store

We can add items to our vector store by using the add_documents function.

from langchain_core.documents import Document

docs = [Document(page_content="Some text"), Document(page_content="Other docs")]
db.add_documents(docs)

Add documents with metadata.

docs = [
    Document(
        page_content="foo",
        metadata={"start": 100, "end": 150, "doc_name": "foo.txt", "quality": "bad"},
    ),
    Document(
        page_content="bar",
        metadata={"start": 200, "end": 250, "doc_name": "bar.txt", "quality": "good"},
    ),
]
db.add_documents(docs)

Add documents with map merge insertion

Note use_map_merge is only accepted as a keyword argument and is only allowed for HanaInternalEmbeddings instances.

# a use_map_merge flag can be supplied for faster insertion
db.add_documents(docs, use_map_merge=True)

Delete items from vector store

db.delete(filter={"quality": "bad"})

Query vector store

Query directly

Similarity search

Performing a simple similarity search with filtering on metadata can be done as follows:

docs = db.similarity_search("foobar", k=2, filter={"quality": "bad"})
# With filtering on "quality"=="bad", only one document should be returned
for doc in docs:
    print("-" * 80)
    print(doc.page_content)
    print(doc.metadata)

--------------------------------------------------------------------------------
foo
{'start': 100, 'end': 150, 'doc_name': 'foo.txt', 'quality': 'bad'}

MMR search

Performing a Maximal Marginal Relevance (MMR) with filtering on metadata search can be done as follows:

docs = db.max_marginal_relevance_search("foobar", k=2, fetch_k=5, filter={"quality": "bad"})
for doc in docs:
    print("-" * 80)
    print(doc.page_content)
    print(doc.metadata)

--------------------------------------------------------------------------------
foo
{'start': 100, 'end': 150, 'doc_name': 'foo.txt', 'quality': 'bad'}

Query by turning into retriever

You can also transform the vector store into a retriever for easier usage in your chains.

retriever = db.as_retriever()
docs = retriever.invoke("foobar", filter={"quality": "good"})
for doc in docs:
    print("-" * 80)
    print(doc.page_content)
    print(doc.metadata)

--------------------------------------------------------------------------------
bar
{'start': 200, 'end': 250, 'doc_name': 'bar.txt', 'quality': 'good'}

Distance similarity algorithm

HanaDB supports the following distance similarity algorithms:

Cosine Similarity (default)
Euclidean Distance (L2)

You can specify the distance strategy when initializing the HanaDB instance by using the distance_strategy parameter.

from langchain_hana.utils import DistanceStrategy
db = HanaDB(
    embedding=embeddings,
    connection=connection,
    distance_strategy=DistanceStrategy.EUCLIDEAN_DISTANCE,
    # distance_strategy=DistanceStrategy.COSINE_SIMILARITY,  # (default)
    table_name="MY_TABLE",
)

Creating a HNSW index

A vector index can significantly speed up top-k nearest neighbor queries for vectors. Users can create a Hierarchical Navigable Small World (HNSW) vector index using the create_hnsw_index function.

For more information about creating an index at the database level, please refer to the official documentation.

db = HanaDB(
    embedding=embeddings, connection=connection, table_name="MY_TABLE"
)
db.create_hnsw_index(
    index_name="MY_TABLE_index",
    m=100,  # Max number of neighbors per graph node (valid range: 4 to 1000)
    ef_construction=200,  # Max number of candidates during graph construction (valid range: 1 to 100000)
    ef_search=500,  # Min number of candidates during the search (valid range: 1 to 100000)
)

If no other parameters are specified, the default values will be used

Default values: m=64, ef_construction=128, ef_search=200

The default index name will be: "<TABLE_NAME>_idx"

Advanced filtering

In addition to the basic value-based filtering capabilities, it is possible to use more advanced filtering. The table below shows the available filter operators.

Operator	Semantic	Supported data types for Operands
`$eq`	Equality (==)	int, float, str, bool, date object, None
`$ne`	Inequality (!=)	int, float, str, bool, date object, None
`$lt`	Less than (<)	int, float, str, date object
`$lte`	Less than or equal (<=)	int, float, str, date object
`$gt`	Greater than (>)	int, float, str, date object
`$gte`	Greater than or equal (>=)	int, float, str, date object
`$in`	Contained in a set of given values (in)	list of same values of type (int, float, str, bool, date object)
`$nin`	Not contained in a set of given values (not in)	list of same values of type (int, float, str, bool, date object)
`$between`	Between the range of two boundary values	list of 2 values of (int, float, str, date object)
`$like`	Text equality based on the "LIKE" semantics in SQL (using "%" as wildcard)	str
`$contains`	Filters documents containing a specific keyword	non-empty str
`$and`	Logical "and", supporting two or more operands	list(filters)
`$or`	Logical "or", supporting two or more operands	list(filters)

# Prepare some test documents
docs = [
    Document(
        page_content="First",
        metadata={"name": "Adam Smith", "is_active": True, "id": 1, "height": 10.0},
    ),
    Document(
        page_content="Second",
        metadata={"name": "Bob Johnson", "is_active": False, "id": 2, "height": 5.7},
    ),
    Document(
        page_content="Third",
        metadata={"name": "Jane Doe", "is_active": True, "id": 3, "height": 2.4},
    ),
]

db = HanaDB(
    connection=connection,
    embedding=embeddings,
    table_name="LANGCHAIN_DEMO_ADVANCED_FILTER",
)

# Delete already existing documents from the table
db.delete(filter={})
db.add_documents(docs)


# Helper function for printing filter results
def print_filter_result(result):
    if len(result) == 0:
        print("<empty result>")
    for doc in result:
        print(doc.metadata)

Filtering with $ne, $gt, $gte, $lt, $lte

advanced_filter = {"id": {"$ne": 1}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"id": {"$gt": 1}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"id": {"$gte": 1}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"id": {"$lt": 1}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"id": {"$lte": 1}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

Filter: {'id': {'$ne': 1}}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'id': {'$gt': 1}}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'id': {'$gte': 1}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'id': {'$lt': 1}}
<empty result>
Filter: {'id': {'$lte': 1}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}

Filtering with $between, $in, $nin

advanced_filter = {"id": {"$between": (1, 2)}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$in": ["Adam Smith", "Bob Johnson"]}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$nin": ["Adam Smith", "Bob Johnson"]}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

Filter: {'id': {'$between': (1, 2)}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'name': {'$in': ['Adam Smith', 'Bob Johnson']}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'name': {'$nin': ['Adam Smith', 'Bob Johnson']}}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}

Text filtering with $like

advanced_filter = {"name": {"$like": "a%"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$like": "%a%"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

Filter: {'name': {'$like': 'a%'}}
<empty result>
Filter: {'name': {'$like': '%a%'}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}

Text filtering with $contains

advanced_filter = {"name": {"$contains": "bob"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$contains": "bo"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$contains": "Adam Johnson"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"name": {"$contains": "Adam Smith"}}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

Filter: {'name': {'$contains': 'bob'}}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'name': {'$contains': 'bo'}}
<empty result>
Filter: {'name': {'$contains': 'Adam Johnson'}}
<empty result>
Filter: {'name': {'$contains': 'Adam Smith'}}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}

Combined filtering with $and, $or

advanced_filter = {"$or": [{"id": 1}, {"name": "bob"}]}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"$and": [{"id": 1}, {"id": 2}]}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {"$or": [{"id": 1}, {"id": 2}, {"id": 3}]}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

advanced_filter = {
    "$and": [{"name": {"$contains": "bob"}}, {"name": {"$contains": "johnson"}}]
}
print(f"Filter: {advanced_filter}")
print_filter_result(db.similarity_search("just testing", k=5, filter=advanced_filter))

Filter: {'$or': [{'id': 1}, {'name': 'bob'}]}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
Filter: {'$and': [{'id': 1}, {'id': 2}]}
<empty result>
Filter: {'$or': [{'id': 1}, {'id': 2}, {'id': 3}]}
{'name': 'Adam Smith', 'is_active': True, 'id': 1, 'height': 10.0}
{'name': 'Jane Doe', 'is_active': True, 'id': 3, 'height': 2.4}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}
Filter: {'$and': [{'name': {'$contains': 'bob'}}, {'name': {'$contains': 'johnson'}}]}
{'name': 'Bob Johnson', 'is_active': False, 'id': 2, 'height': 5.7}

Usage for retrieval-augmented generation

For guides on how to use this vector store for retrieval-augmented generation (RAG), see the following sections:

Reranking results

Results of a similarity search can be reranked by passing a rerank_config dictionary to the similarity_search methods. The reranking is performed using SAP HANA's CROSS_ENCODE function with Cross Encoding models.

The rerank_config dictionary supports the following fields:

query (str, required) — The query text to use for reranking. If not provided, the original similarity search query will be used.
model_id (str, required) — The model ID for the cross-encoding model to use for reranking.
top_n (int, optional) — The number of top results to return after reranking. Defaults to 3.
rank_fields (list[str], optional) — List of metadata fields to include in reranking. When specified, these fields are concatenated with the content for scoring.

Note: Ensure NLP is enabled in your SAP HANA Cloud instance to use reranking functionality. For more details, see SAP HANA Database Additional Features.

# Prepare sample documents with metadata
docs = [
    Document(
        page_content="Python is a programming language",
        metadata={"category": "programming", "difficulty": "beginner"},
    ),
    Document(
        page_content="Machine learning uses algorithms to learn patterns",
        metadata={"category": "AI", "difficulty": "intermediate"},
    ),
    Document(
        page_content="Neural networks are inspired by the human brain",
        metadata={"category": "AI", "difficulty": "advanced"},
    ),
]

db = HanaDB(
    connection=connection,
    embedding=embeddings,
    table_name="LANGCHAIN_DEMO_RERANKING",
)

# Delete existing entries and add new documents
db.delete(filter={})
db.add_documents(docs)

Basic reranking

Perform a similarity search with reranking using the rerank_config parameter:

# Basic reranking - rerank similarity search results using a cross-encoding model
rerank_config = {
    "model_id": os.environ.get("HANA_DB_RERANK_MODEL_ID"),  # e.g., "SAP_CER.20250701"
    "top_n": 2,
}

results = db.similarity_search(
    "AI technology",
    k=3,
    rerank_config=rerank_config
)

print("Reranked results:")
for doc in results:
    print("-" * 80)
    print(f"Content: {doc.page_content}")
    print(f"Metadata: {doc.metadata}")

Reranked results:
--------------------------------------------------------------------------------
Content: Machine learning uses algorithms to learn patterns
Metadata: {'category': 'AI', 'difficulty': 'intermediate'}
--------------------------------------------------------------------------------
Content: Python is a programming language
Metadata: {'category': 'programming', 'difficulty': 'beginner'}

Reranking with metadata fields

Use rank_fields to include metadata in the reranking process. This is useful when metadata contains relevant information that should influence the ranking:

# Reranking with metadata fields included in the scoring
rerank_config_with_fields = {
    "query": "beginner AI concepts",
    "model_id": os.environ.get("HANA_DB_RERANK_MODEL_ID"),
    "top_n": 2,
    "rank_fields": ["category", "difficulty"],  # Include these metadata fields in reranking
}

results = db.similarity_search(
    "learning algorithms",
    k=3,
    rerank_config=rerank_config_with_fields
)

print("Reranked results with metadata fields:")
for doc in results:
    print("-" * 80)
    print(f"Content: {doc.page_content}")
    print(f"Metadata: {doc.metadata}")

Reranked results with metadata fields:
--------------------------------------------------------------------------------
Content: Neural networks are inspired by the human brain
Metadata: {'category': 'AI', 'difficulty': 'advanced'}
--------------------------------------------------------------------------------
Content: Machine learning uses algorithms to learn patterns
Metadata: {'category': 'AI', 'difficulty': 'intermediate'}

Reranking with scores

Use similarity_search_with_score to get the reranking scores along with the documents:

# Get reranking scores with the results
rerank_config = {
    "model_id": os.environ.get("HANA_DB_RERANK_MODEL_ID"),
    "top_n": 3,
}

results_with_scores = db.similarity_search_with_score(
    "neural network architecture",
    k=3,
    rerank_config=rerank_config
)

print("Reranked results with scores:")
for doc, score in results_with_scores:
    print("-" * 80)
    print(f"Score: {score:.4f}")
    print(f"Content: {doc.page_content}")

Reranked results with scores:
--------------------------------------------------------------------------------
Score: 0.0435
Content: Neural networks are inspired by the human brain
--------------------------------------------------------------------------------
Score: 0.0146
Content: Python is a programming language
--------------------------------------------------------------------------------
Score: 0.0145
Content: Machine learning uses algorithms to learn patterns

Standalone reranking with HanaReranker

For more control over the reranking process, you can use the HanaReranker class directly as a document compressor. This is useful when you want to rerank documents from any source, not just from a similarity search:

from langchain_hana import HanaReranker

# Create a standalone reranker
reranker = HanaReranker(
    connection=connection,
    model_id=os.environ.get("HANA_DB_RERANK_MODEL_ID"),
)

# Documents to rerank (can come from any source)
documents = [
    Document(page_content="Python programming basics"),
    Document(page_content="Advanced machine learning techniques"),
    Document(page_content="Introduction to neural networks"),
    Document(page_content="Deep learning applications"),
    Document(page_content="Reinforcement learning strategies"),
    Document(page_content="Natural language processing techniques"),
]

# Rerank documents based on a query using compress_documents (returns top min(5, len(documents)))
# Reranking scores will be added to the metadata of each document under the key "relevance_score"
reranked_docs = reranker.compress_documents(documents, query="AI and deep learning")

print("Compressed documents:")
for doc in reranked_docs:
    print("-" * 80)
    print(f"Content: {doc.page_content}")
    print(f"Relevance score: {doc.metadata.get('relevance_score'):.4f}")

# Or use the rerank method for more control over top_n
results = reranker.rerank(documents, query="machine learning", top_n=2)
print("\nTop 2 reranked results:")
for idx, score, doc in results:
    print(f"  [{idx}] Score: {score:.4f} - {doc.page_content}")

Compressed documents:
--------------------------------------------------------------------------------
Content: Deep learning applications
Relevance score: 0.2188
--------------------------------------------------------------------------------
Content: Advanced machine learning techniques
Relevance score: 0.0748
--------------------------------------------------------------------------------
Content: Introduction to neural networks
Relevance score: 0.0079
--------------------------------------------------------------------------------
Content: Natural language processing techniques
Relevance score: 0.0040
--------------------------------------------------------------------------------
Content: Reinforcement learning strategies
Relevance score: 0.0036

Top 2 reranked results:
  [1] Score: 0.4351 - Advanced machine learning techniques
  [3] Score: 0.0342 - Deep learning applications

Standard tables vs. "custom" tables with vector data

As default behaviour, the table for the embeddings is created with 3 columns:

A column VEC_TEXT, which contains the text of the Document
A column VEC_META, which contains the metadata of the Document
A column VEC_VECTOR, which contains the embeddings-vector of the Document's text

# Access the vector DB with a new table
db = HanaDB(
    connection=connection, embedding=embeddings, table_name="LANGCHAIN_DEMO_NEW_TABLE"
)

# Delete already existing entries from the table
db.delete(filter={})

# Add a simple document with some metadata
docs = [
    Document(
        page_content="A simple document",
        metadata={"start": 100, "end": 150, "doc_name": "simple.txt"},
    )
]
db.add_documents(docs)

Show the columns in table "LANGCHAIN_DEMO_NEW_TABLE"

cur = connection.cursor()
cur.execute(
    "SELECT COLUMN_NAME, DATA_TYPE_NAME FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'LANGCHAIN_DEMO_NEW_TABLE'"
)
rows = cur.fetchall()
for row in rows:
    print(row)
cur.close()

('VEC_META', 'NCLOB')
('VEC_TEXT', 'NCLOB')
('VEC_VECTOR', 'REAL_VECTOR')

Show the value of the inserted document in the three columns

Since, HANA's dbapi driver outputs the vector columns in fvecs bytes objects by default, we will create a helper function to convert the function into a list of numbers.

import struct
# Helper function to parse fvecs format for REAL_VECTOR
def parseFvecs(fvecs):
    dim = struct.unpack_from("<I", fvecs, 0)[0]
    return list(struct.unpack_from("<%sf" % str(dim), fvecs, 4))

cur = connection.cursor()
cur.execute(
    "SELECT * FROM LANGCHAIN_DEMO_NEW_TABLE LIMIT 1"
)
rows = cur.fetchall()
print(rows[0][0])  # The text
print(rows[0][1])  # The metadata
embedding = parseFvecs(rows[0][2])
print(len(embedding), embedding[:3] + ['...'] + embedding[-3:])  # The vector
cur.close()

A simple document
{"start": 100, "end": 150, "doc_name": "simple.txt"}
768 [-0.01989901065826416, 0.02785174734890461, 0.0020877711940556765, '...', 0.0183248370885849, 0.009469633921980858, 0.04312701150774956]

Custom tables must have at least three columns that match the semantics of a standard table

A column with type NCLOB or NVARCHAR for the text/context of the embeddings
A column with type NCLOB or NVARCHAR for the metadata
A column with type REAL_VECTOR or HALF_VECTOR for the embedding vector

The table can contain additional columns. When new Documents are inserted into the table, these additional columns must allow NULL values.

# Create a new table "MY_OWN_TABLE_ADD" with three "standard" columns and one additional column
my_own_table_name = "MY_OWN_TABLE_ADD"
cur = connection.cursor()
cur.execute(
    (
        f"CREATE TABLE {my_own_table_name} ("
        "SOME_OTHER_COLUMN NVARCHAR(42), "
        "MY_TEXT NVARCHAR(2048), "
        "MY_METADATA NVARCHAR(1024), "
        "MY_VECTOR REAL_VECTOR )"
    )
)

# Create a HanaDB instance with the own table
db = HanaDB(
    connection=connection,
    embedding=embeddings,
    table_name=my_own_table_name,
    content_column="MY_TEXT",
    metadata_column="MY_METADATA",
    vector_column="MY_VECTOR",
)

# Add a simple document with some metadata
docs = [
    Document(
        page_content="Some other text",
        metadata={"start": 400, "end": 450, "doc_name": "other.txt"},
    )
]
db.add_documents(docs)

# Check if data has been inserted into our own table
cur.execute(f"SELECT SOME_OTHER_COLUMN, MY_TEXT, MY_METADATA, TO_NVARCHAR(MY_VECTOR) AS MY_VECTOR FROM {my_own_table_name} LIMIT 1")
rows = cur.fetchall()
print(rows[0][0])  # Value of column "SOME_OTHER_COLUMN". Should be NULL/None
print(rows[0][1])  # The text
print(rows[0][2])  # The metadata
embedding = parseFvecs(rows[0][3])
print(len(embedding), embedding[:3] + ['...'] + embedding[-3:])  # The vector

cur.close()

None
Some other text
{"start": 400, "end": 450, "doc_name": "other.txt"}
768 [0.016170687973499298, -0.01129427831619978, -0.0005921399570070207, '...', 0.017849743366241455, 0.0003932560794055462, -0.00045805066474713385]

Add another document and perform a similarity search on the custom table.

docs = [
    Document(
        page_content="Some more text",
        metadata={"start": 800, "end": 950, "doc_name": "more.txt"},
    )
]
db.add_documents(docs)

query = "What's up?"
docs = db.similarity_search(query, k=2)
for doc in docs:
    print("-" * 80)
    print(doc.page_content)

--------------------------------------------------------------------------------
Some more text
--------------------------------------------------------------------------------
Some other text

Filter performance optimization with custom columns

To allow flexible metadata values, all metadata is stored as JSON in the metadata column by default. If some of the used metadata keys and value types are known, they can be stored in additional columns instead by creating the target table with the key names as column names and passing them to the HanaDB constructor via the specific_metadata_columns list. Metadata keys that match those values are copied into the special column during insert. Filters use the special columns instead of the metadata JSON column for keys in the specific_metadata_columns list.

# Create a new table "PERFORMANT_CUSTOMTEXT_FILTER" with three "standard" columns and one additional column
my_own_table_name = "PERFORMANT_CUSTOMTEXT_FILTER"
cur = connection.cursor()
cur.execute(
    (
        f"CREATE TABLE {my_own_table_name} ("
        "CUSTOMTEXT NVARCHAR(500), "
        "MY_TEXT NVARCHAR(2048), "
        "MY_METADATA NVARCHAR(1024), "
        "MY_VECTOR REAL_VECTOR )"
    )
)

# Create a HanaDB instance with the own table
db = HanaDB(
    connection=connection,
    embedding=embeddings,
    table_name=my_own_table_name,
    content_column="MY_TEXT",
    metadata_column="MY_METADATA",
    vector_column="MY_VECTOR",
    specific_metadata_columns=["CUSTOMTEXT"],
)

# Add a simple document with some metadata
docs = [
    Document(
        page_content="Some other text",
        metadata={
            "start": 400,
            "end": 450,
            "doc_name": "other.txt",
            "CUSTOMTEXT": "Filters on this value are very performant",
        },
    )
]
db.add_documents(docs)

# Check if data has been inserted into our own table
cur.execute(f"SELECT * FROM {my_own_table_name} LIMIT 1")
rows = cur.fetchall()
print(
    rows[0][0]
)  # Value of column "CUSTOMTEXT". Should be "Filters on this value are very performant"
print(rows[0][1])  # The text
print(
    rows[0][2]
)  # The metadata without the "CUSTOMTEXT" data, as this is extracted into a separate column
embedding = parseFvecs(rows[0][3])
print(len(embedding), embedding[:3] + ['...'] + embedding[-3:])  # The vector

cur.close()

Filters on this value are very performant
Some other text
{"start": 400, "end": 450, "doc_name": "other.txt", "CUSTOMTEXT": "Filters on this value are very performant"}
768 [0.016170687973499298, -0.01129427831619978, -0.0005921399570070207, '...', 0.017849743366241455, 0.0003932560794055462, -0.00045805066474713385]

The special columns are completely transparent to the rest of the langchain interface. Everything works as it did before, just more performant.

docs = [
    Document(
        page_content="Some more text",
        metadata={
            "start": 800,
            "end": 950,
            "doc_name": "more.txt",
            "CUSTOMTEXT": "Another customtext value",
        },
    )
]
db.add_documents(docs)

advanced_filter = {"CUSTOMTEXT": {"$like": "%value%"}}
query = "What's up?"
docs = db.similarity_search(query, k=2, filter=advanced_filter)
for doc in docs:
    print("-" * 80)
    print(doc.page_content)

--------------------------------------------------------------------------------
Some more text
--------------------------------------------------------------------------------
Some other text

A simple example

Load the sample document "state_of_the_union.txt" and create chunks from it.

Warning

The langchain-community package is no longer maintained. Examples that import from langchain_community may be outdated or broken. Use with caution.

from langchain_community.document_loaders import TextLoader
from langchain_core.documents import Document
from langchain_openai import OpenAIEmbeddings
from langchain_text_splitters import CharacterTextSplitter

text_documents = TextLoader(
    "../../how_to/state_of_the_union.txt", encoding="UTF-8"
).load()
text_splitter = CharacterTextSplitter(chunk_size=500, chunk_overlap=0)
text_chunks = text_splitter.split_documents(text_documents)
print(f"Number of document chunks: {len(text_chunks)}")

Number of document chunks: 88

Add the loaded document chunks to the table. For this example, we delete any previous content from the table which might exist from previous runs.

# Delete already existing documents from the table
db.delete(filter={})

# add the loaded document chunks
db.add_documents(text_chunks)

Perform a query to get the two best-matching document chunks from the ones that were added in the previous step. By default "Cosine Similarity" is used for the search.

query = "What did the president say about Ketanji Brown Jackson"
docs = db.similarity_search(query, k=2)

for doc in docs:
    print("-" * 80)
    print(doc.page_content)

--------------------------------------------------------------------------------
One of the most serious constitutional responsibilities a President has is nominating someone to serve on the United States Supreme Court.

And I did that 4 days ago, when I nominated Circuit Court of Appeals Judge Ketanji Brown Jackson. One of our nation’s top legal minds, who will continue Justice Breyer’s legacy of excellence.
--------------------------------------------------------------------------------
As I said last year, especially to our younger transgender Americans, I will always have your back as your President, so you can be yourself and reach your God-given potential.

While it often appears that we never agree, that isn’t true. I signed 80 bipartisan bills into law last year. From preventing government shutdowns to protecting Asian-Americans from still-too-common hate crimes to reforming military justice.

Query the same content with "Euclidean Distance". The results should be the same as with "Cosine Similarity".

from langchain_hana.utils import DistanceStrategy

db = HanaDB(
    embedding=embeddings,
    connection=connection,
    distance_strategy=DistanceStrategy.EUCLIDEAN_DISTANCE,
    table_name="STATE_OF_THE_UNION",
)

query = "What did the president say about Ketanji Brown Jackson"
docs = db.similarity_search(query, k=2)
for doc in docs:
    print("-" * 80)
    print(doc.page_content)

--------------------------------------------------------------------------------
One of the most serious constitutional responsibilities a President has is nominating someone to serve on the United States Supreme Court.

And I did that 4 days ago, when I nominated Circuit Court of Appeals Judge Ketanji Brown Jackson. One of our nation’s top legal minds, who will continue Justice Breyer’s legacy of excellence.
--------------------------------------------------------------------------------
As I said last year, especially to our younger transgender Americans, I will always have your back as your President, so you can be yourself and reach your God-given potential.

While it often appears that we never agree, that isn’t true. I signed 80 bipartisan bills into law last year. From preventing government shutdowns to protecting Asian-Americans from still-too-common hate crimes to reforming military justice.

Maximal marginal relevance search (MMR)

Maximal marginal relevance optimizes for similarity to query AND diversity among selected documents. The first 20 (fetch_k) items will be retrieved from the DB. The MMR algorithm will then find the best 2 (k) matches.

docs = db.max_marginal_relevance_search(query, k=2, fetch_k=20)
for doc in docs:
    print("-" * 80)
    print(doc.page_content)

--------------------------------------------------------------------------------
One of the most serious constitutional responsibilities a President has is nominating someone to serve on the United States Supreme Court.

And I did that 4 days ago, when I nominated Circuit Court of Appeals Judge Ketanji Brown Jackson. One of our nation’s top legal minds, who will continue Justice Breyer’s legacy of excellence.
--------------------------------------------------------------------------------
Groups of citizens blocking tanks with their bodies. Everyone from students to retirees teachers turned soldiers defending their homeland.

In this struggle as President Zelenskyy said in his speech to the European Parliament “Light will win over darkness.” The Ukrainian Ambassador to the United States is here tonight.

Let each of us here tonight in this Chamber send an unmistakable signal to Ukraine and to the world.

Creating an HNSW Vector Index