@Rocky is it possible to generate a vector embeddings table natively based on another starrocks table

Yes, it is possible to generate vector embeddings natively from another StarRocks table. You have two primary ways to do this within the database itself: using recently introduced built-in AI Functions or via Python UDFs. 1. Built-in AI Functions (StarRocks 3.3/3.4+ / Cloud) In the latest versions and specific cloud distributions (like Alibaba Cloud EMR StarRocks), StarRocks has introduced native AI SQL functions. These allow you to call external LLM providers (like OpenAI or local compatible endpoints) directly in your SQL statements. * Function:

ai_embed(model, text)

* Workflow: You can create a new table with a vector column and populate it using an

INSERT INTO ... SELECT

pattern. Example:

sql
-- 1. Create a table to store the results
CREATE TABLE document_embeddings (
    id BIGINT,
    content STRING,
    embedding ARRAY<FLOAT> -- Or VECTOR_FLOAT(1536) in newer versions
) ENGINE=OLAP;

-- 2. Generate and insert embeddings based on an existing 'documents' table
INSERT INTO document_embeddings
SELECT 
    id, 
    content, 
    ai_embed('text-embedding-v3', content) -- Calls the configured embedding model
FROM documents;

Note: This typically requires configuring an "AI Center" or specific BE properties with your API keys and endpoints. 2. Python UDFs (StarRocks v3.4.0+) If your environment doesn't have the built-in AI functions enabled or you want to use a local model (like

sentence-transformers

), you can use Python User-Defined Functions (UDFs). * Capabilities: You can write a Python script that loads an embedding model and returns an array of floats. * Performance: Python UDFs can be vectorized (using

input = "arrow"

) to process batches of data efficiently. Example Workflow: 1. Define the UDF:

sql
    CREATE FUNCTION my_embedding_func(STRING)
    RETURNS ARRAY<FLOAT>
    PROPERTIES (
        "type" = "Python",
        "symbol" = "get_embedding",
        "file" = "<http://your-server/embedding_module.py.zip>"
    );

2. Generate Table:

sql
    CREATE TABLE vector_table AS
    SELECT id, my_embedding_func(text_column) as vector
    FROM source_table;

Summary of Prerequisites * Data Type: StarRocks supports

ARRAY<FLOAT>

for vectors, and more recent versions have introduced a dedicated

VECTOR

type for optimized storage. * Indexing: Once the table is generated, you should add a Vector Index (HNSW or IVFPQ) to the column to enable fast similarity searches:

sql
    ALTER TABLE document_embeddings
    ADD INDEX idx_vector (embedding) USING VECTOR ("index_type" = "hnsw", "metric_type" = "cosine_similarity");

If you are using an older version (pre-3.4), the typical approach is to use an external Python script to fetch the data, generate embeddings, and write them back to StarRocks using a standard

INSERT

or

Routine Load

. References • table_design/indexes/vector_index.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/json_query.md

starrocks 4.1.1

In StarRocks 4.1.1, generating vector embeddings natively from an existing table is possible through the newly introduced AI Functions. However, the availability of specific functions like

ai_embed

depends on your distribution (e.g., standard open-source vs. managed cloud versions like Alibaba Cloud EMR). 1. Standard AI Functions (

ai_query

) In the standard StarRocks 4.1.1 release, the primary interface for AI tasks is the

ai_query

function. This is a general-purpose function that allows you to call external model endpoints (like OpenAI, Azure OpenAI, or other compatible APIs) directly from SQL. * Syntax:

ai_query(prompt, config_json)

* Use Case: While primarily for LLM interactions (summarization, classification), it can be used to retrieve embeddings if the connected model and prompt are configured for embedding tasks. 2. Managed Distribution (

ai_embed

) If you are using Alibaba Cloud EMR StarRocks or specific managed versions, there is a dedicated

ai_embed

function provided by the "AI Center." * Function:

ai_embed(model, text)

* Capability: It converts text into a vector (typically

ARRAY<FLOAT>

) natively within the SQL engine. * Example Workflow:

sql
    -- Generate and store embeddings in a new table
    INSERT INTO target_table
    SELECT
        id,
        content,
        ai_embed('text-embedding-v3', content)
    FROM source_table;

3. Alternative: Python UDFs If the built-in AI functions are not enabled or you want to use a local model without external API calls, StarRocks 4.1.1 supports Python UDFs. You can define a function that utilizes a library like

sentence-transformers

to generate embeddings. Recommended Steps for Table Generation To create a vector table from another table in 4.1.1: 1. Ensure a Vector Index is created: For fast search, define a vector index (HNSW or IVFPQ) on the target column. 2. Use CTAS or Insert-Into:

sql
    CREATE TABLE embedding_table AS
    SELECT
        id,
        ai_embed('your_model', text_col) as vector_col
    FROM source_table;

3. Check Configuration: You must configure the AI Center (for Cloud/EMR) or the necessary BE properties (for

ai_query

) with your API keys and model endpoints before these functions will work. Verification: You can verify available functions in your specific environment by running:

sql
SHOW FUNCTIONS;

References • table_design/indexes/vector_index.md • sql-reference/sql-functions/Python_UDF.md • sql-reference/sql-functions/README.md References • sql-reference/sql-functions/README.md • sql-reference/sql-functions/Window_function.md • sql-reference/sql-functions/hive_bitmap_udf.md • knowledge/usage/best-practice.md • sql-reference/sql-functions/Python_UDF.md • sql-reference/data-types/other-data-types/HLL.md • table_design/indexes/vector_index.md • sql-reference/sql-functions/aggregate-functions/bitmap.md