Server Configuration¶

You must configure an embedding model in the Server configuration. This defines the connection to the LLM or embedding service that the function EMBED_AI() uses during execution.

• When a query requires converting a string into a vector on-the-fly, the Virtual DataPort (VDP) server uses this configuration to call the external API.

Base View Configuration¶

When you introspect a table containing vector data (e.g., a PostgreSQL table with a vector column), you should configure the following in the Design Studio:

1. Source Type Property: Explicitly set which model was used to populate the column.

2. Column Description: Provide a meaningful description that details the information used to generate the vector (e.g., “Embedding generated from the customer review summary”).

To specify the Embedding model source type property of a vector column:

1. Open the Base View and go to the Edit tab.

2. Navigate to the column that holds the vector data.

3. Set the Source Type Property “Embedding Model”.

4. Save the changes.

Benefits of this Configuration

• Automated Vector Generation: It enables the VDP server to automatically identify the required model during query execution. When you run a query comparing text to this column, VDP identifies the model needed to vectorize your input text, eliminating the need to manually specify it in every query.

• Governance and Documentation: It serves as a metadata record, documenting exactly which model and version were used to generate the vectors (e.g., openai-text-embedding-3-small vs cohere-embed-english-v3.0). This prevents version mismatches and ensures developers know how to interact with the data.

• AI-Powered Features: The column descriptions allow the the Denodo Assistant Chatbot for VQL Shell, the Denodo AI SDK, and the Denodo Assistant Chatbot tools to generate more accurate queries by understanding the semantic context of the vector data.

1. Transformation to Vector Search¶

For data sources such as Databricks (available from version 18.1 onwards, when distance functions are supported), Google BigQuery, and Microsoft SQL Server, VDP can detect the appropriate query structure and rewrite the query to delegate it using the specific VECTOR_SEARCH function of the underlying source.

If, for any reason, the transformation to vector search is not applied (for example, using an older version), the standard delegation of distance functions will still function normally without the approximate search benefits.

For VDP to perform this query rewrite, the following requirements must be met:

• Vector Index: An appropriate vector index must exist on the target vector column in the data source. These search indexes are typically imported automatically by VDP. However, if this does not occur, they can be manually declared in the index configuration panel using the Design Studio.

  Additionally, VDP takes into account the distance metric. If the metric used to create the index is known, VDP will verify that it matches the distance function used in the query to decide whether to rewrite it. If the index metric is unknown (which is currently the case for Databricks), VDP ignores the metric check and relies solely on the existence of the index.

• Distance Function: The query must execute a distance function over the vector column.

• Sorting: The query must order the results by the distance function, and it must be the first or only sorting field (ORDER BY).

• Result Limit: A LIMIT clause must be defined to return only the top n results. Returning the entire dataset would invalidate the performance benefits of an approximate search.

• No Filtering: The query cannot contain filtering conditions (WHERE clause).

  Note

  Exception: Google BigQuery does support VECTOR_SEARCH combined with filtering conditions.

2. Native Execution¶

For data sources like PostgreSQL (via pgvector) and Oracle, VDP does not need to explicitly rewrite the query into a specific vector search function.

Instead, when VDP pushes down a standard distance function query that meets the structural criteria (like having an ORDER BY distance and a LIMIT), the underlying database natively recognizes the pattern. If a vector index is present, the database automatically applies its own internal approximate search algorithms to evaluate the distance efficiently.

Basic Semantic Search (Ranking)¶

The following query searches for tickets related to “internet connectivity issues.”

SELECT
    ticket_id,
    ticket_description,
    vector_distance(description_vector, 'My laptop keeps losing connection to the Wi-Fi') AS distance_score
FROM
    support_tickets
ORDER BY
    distance_score
LIMIT 3;

Results:

Notice that the system returns results about “Wireless” and “Network” even though the word “Wi-Fi” might not be present in every row.

Parametrized Approximate Search¶

The ranking scenario from above can be parametrized using a derived view. This encapsulates the logic, allowing client applications to execute an approximate search safely by simply passing a search parameter without needing to write the complex vector functions.

CREATE VIEW ticketsearch AS
    SELECT
        ticket_id,
        ticket_description,
        vector_distance(description_vector, search_param) AS distance_score
    FROM
        support_tickets
    USING PARAMETERS (search_param : text)
    ORDER BY
        distance_score
    LIMIT 3;

To query this parametrized view, the client application simply provides the parameter in the WHERE clause. Virtual DataPort injects this parameter into the distance function without generating a traditional WHERE filter in the underlying database, allowing the approximate search delegation to succeed:

SELECT * FROM ticketsearch
WHERE search_param = 'My laptop keeps losing connection to the Wi-Fi';

Disabling Approximate Search¶

If you need to force the execution of an exact search and prevent VDP from transforming the query into an approximate search, you can disable it at the query level by setting the approximate_vector_search property to OFF in the CONTEXT clause:

SELECT
    ticket_id,
    ticket_description,
    vector_distance(description_vector, 'My laptop keeps losing connection to the Wi-Fi') AS distance_score
FROM
    support_tickets
CONTEXT ('approximate_vector_search' = 'OFF')
ORDER BY
    distance_score
LIMIT 3;

SELECT id
FROM v
FETCH NEXT 2 ROWS ONLY
CONTEXT ('APPROXIMATE_VECTOR_SEARCH'='OFF')

SELECT t0.id FROM schema.v t0 FETCH
EXACT NEXT 2 ROWS ONLY

Hybrid Search (Semantic + Structured Filter)¶

Hybrid search combines semantic understanding with traditional structured filtering. This is useful when you need to find semantically relevant results that are strictly limited to a specific category, date range, or status.

In this example, we search for issues semantically similar to “login failure,” but we strictly filter to show only tickets marked as “Critical” priority.

SELECT
    ticket_id,
    ticket_description,
    priority,
    vector_distance(description_vector, 'Users cannot log into the portal') AS distance_score
FROM
    support_tickets
WHERE
    priority = 'Critical'
    AND vector_distance(description_vector, 'Users cannot log into the portal') < 0.4
ORDER BY
    distance_score;

Results:

The results exclude common password resets (Low priority) and focus only on critical outages, even if the phrasing varies (e.g., “SSO”, “Auth”).

Recommendation System (“More Like This”)¶

You can use semantic search to build a recommendation engine or a “find similar” feature. Instead of comparing a user’s text input to the database, you compare the vector of an existing record to the vectors of all other records using a distance function such as VECTOR_COSINE_DISTANCE().

In this example, we identify a specific ticket (ID 1042) regarding a complex server crash. We then search for other tickets in the system that describe a similar problem to identify if this is a recurring pattern.

SELECT
    t.ticket_id,
    t.ticket_description,
    vector_cosine_distance(t.description_vector, source.description_vector) AS distance_score
FROM
    support_tickets t,
    (SELECT description_vector FROM support_tickets WHERE ticket_id = 1042) source
WHERE
    t.ticket_id <> 1042 -- Exclude the source ticket itself
    AND vector_cosine_distance(t.description_vector, source.description_vector) < 0.2
ORDER BY
    distance_score;

Results:

Assuming Ticket 1042 was “Server crash due to memory leak,” the query returns other tickets describing memory issues.