OLAP Analytics     → ClickHouse / Doris
Full-text Search   → Elasticsearch
Vector Semantic    → Milvus / Pinecone

┌──────────────────────────────────────┐
│            Doris Segment             │
│  ┌─────────────┬──────────────────┐  │
│  │ Column Data  │ Inverted Index   │  │
│  │ (Columnar)   │ (CLucene)        │  │
│  │              │ ┌──────────────┐ │  │
│  │ tenant_id    │ │ Term Dict    │ │  │
│  │ object_type  │ │ Posting List │ │  │
│  │ display_name │ │ Doc Values   │ │  │
│  │ description  │ │ Norms        │ │  │
│  │ properties   │ └──────────────┘ │  │
│  └─────────────┴──────────────────┘  │
└──────────────────────────────────────┘

CREATE TABLE ontology_object_searchable (
    tenant_id      VARCHAR(64)    NOT NULL COMMENT 'Tenant ID',
    object_type    VARCHAR(128)   NOT NULL COMMENT 'Object type',
    object_rid     VARCHAR(256)   NOT NULL COMMENT 'Object RID',
    display_name   VARCHAR(512)   NOT NULL COMMENT 'Display name',
    description    TEXT           COMMENT 'Description',
    tags           ARRAY<VARCHAR(64)> COMMENT 'Tag array',
    properties_json TEXT          COMMENT 'Properties JSON',
    status         VARCHAR(32)    COMMENT 'Status',
    created_at     DATETIME       COMMENT 'Created at',
    updated_at     DATETIME       COMMENT 'Updated at',

    -- Inverted index definitions
    INDEX idx_display_name (display_name)
        USING INVERTED
        PROPERTIES("parser" = "unicode", "support_phrase" = "true"),

    INDEX idx_description (description)
        USING INVERTED
        PROPERTIES(
            "parser" = "unicode",
            "support_phrase" = "true",
            "lower_case" = "true"
        ),

    INDEX idx_tags (tags)
        USING INVERTED
        PROPERTIES("parser" = "unicode"),

    INDEX idx_properties (properties_json)
        USING INVERTED
        PROPERTIES("parser" = "unicode", "support_phrase" = "true"),

    INDEX idx_status (status)
        USING INVERTED,

    INDEX idx_object_type (object_type)
        USING INVERTED
)
UNIQUE KEY(tenant_id, object_type, object_rid)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true",
    "inverted_index_storage_format" = "V2",
    "store_row_column" = "true"
);

-- Basic full-text search: MATCH_ANY (OR semantics)
SELECT object_rid, display_name, description
FROM ontology_object_searchable
WHERE tenant_id = 'tenant-001'
  AND display_name MATCH_ANY 'supply chain risk'
ORDER BY updated_at DESC
LIMIT 20;

-- Phrase search: MATCH_PHRASE (requires contiguous word order)
SELECT object_rid, display_name, description
FROM ontology_object_searchable
WHERE tenant_id = 'tenant-001'
  AND description MATCH_PHRASE 'supply chain risk assessment'
LIMIT 20;

-- Full match search: MATCH_ALL (AND semantics)
SELECT object_rid, display_name, description
FROM ontology_object_searchable
WHERE tenant_id = 'tenant-001'
  AND description MATCH_ALL 'supply chain risk assessment'
LIMIT 20;

-- Prefix search: MATCH_PHRASE_PREFIX
SELECT object_rid, display_name
FROM ontology_object_searchable
WHERE tenant_id = 'tenant-001'
  AND display_name MATCH_PHRASE_PREFIX 'supply ch'
LIMIT 20;

-- Combined conditions: full-text search + exact filter
SELECT object_rid, display_name, description, status, created_at
FROM ontology_object_searchable
WHERE tenant_id = 'tenant-001'
  AND description MATCH_ANY 'risk assessment alert'
  AND status = 'ACTIVE'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Layer 3:  [A] ────────────────── [F]
           |                      |
Layer 2:  [A] ──── [C] ──── [F] ──── [H]
           |        |        |        |
Layer 1:  [A] ─ [B] ─ [C] ─ [D] ─ [F] ─ [G] ─ [H]
           |    |    |    |    |    |    |    |
Layer 0:  [A]-[B]-[C]-[D]-[E]-[F]-[G]-[H]-[I]-[J]

CREATE TABLE ontology_embeddings (
    tenant_id       VARCHAR(64)    NOT NULL COMMENT 'Tenant ID',
    object_type     VARCHAR(128)   NOT NULL COMMENT 'Object type',
    object_rid      VARCHAR(256)   NOT NULL COMMENT 'Object RID',
    display_name    VARCHAR(512)   COMMENT 'Display name',
    description     TEXT           COMMENT 'Description text',
    embedding       ARRAY<FLOAT>   NOT NULL COMMENT 'Embedding vector (768-dim)',
    model_version   VARCHAR(32)    COMMENT 'Model version',
    created_at      DATETIME       COMMENT 'Created at',

    -- Vector index
    INDEX idx_embedding (embedding)
        USING INVERTED
        PROPERTIES(
            "index_type" = "HNSW",
            "metric_type" = "L2",
            "dim" = "768",
            "M" = "32",
            "efConstruction" = "400"
        ),

    -- Text inverted index (for hybrid queries)
    INDEX idx_display_name (display_name)
        USING INVERTED
        PROPERTIES("parser" = "unicode"),

    INDEX idx_description (description)
        USING INVERTED
        PROPERTIES("parser" = "unicode", "support_phrase" = "true")
)
UNIQUE KEY(tenant_id, object_type, object_rid)
DISTRIBUTED BY HASH(tenant_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",
    "enable_unique_key_merge_on_write" = "true"
);

# intelligence-Layer/embedding_indexer.py
import numpy as np
from typing import List, Dict
from sentence_transformers import SentenceTransformer
from doris_stream_loader import DorisStreamLoader

class OntologyEmbeddingIndexer:
    """Ontology object embedding indexer"""

    def __init__(
        self,
        model_name: str = "BAAI/bge-base-zh-v1.5",
        doris_host: str = "doris-fe-1",
        doris_port: int = 8030
    ):
        self.model = SentenceTransformer(model_name)
        self.loader = DorisStreamLoader(doris_host, doris_port)
        self.model_version = model_name.split("/")[-1]

    def index_objects(
        self,
        tenant_id: str,
        objects: List[Dict]
    ) -> int:
        """Batch index Ontology objects"""
        records = []
        texts = []

        for obj in objects:
            text = f"{obj['display_name']}. {obj.get('description', '')}"
            texts.append(text)

        # Batch encode
        embeddings = self.model.encode(
            texts,
            batch_size=64,
            show_progress_bar=True,
            normalize_embeddings=True
        )

        for obj, embedding in zip(objects, embeddings):
            records.append({
                "tenant_id": tenant_id,
                "object_type": obj["object_type"],
                "object_rid": obj["object_rid"],
                "display_name": obj["display_name"],
                "description": obj.get("description", ""),
                "embedding": embedding.tolist(),
                "model_version": self.model_version,
                "created_at": obj.get("created_at")
            })

        # Bulk import
        result = self.loader.load_json_data(
            database="ontology_db",
            table="ontology_embeddings",
            data=records
        )

        return len(records)

-- Basic vector similarity search (L2 distance)
SELECT
    object_rid,
    display_name,
    L2_DISTANCE(embedding, ARRAY[0.1, 0.2, ...]) as distance
FROM ontology_embeddings
WHERE tenant_id = 'tenant-001'
ORDER BY distance ASC
LIMIT 10;

-- Cosine similarity search
SELECT
    object_rid,
    display_name,
    COSINE_DISTANCE(embedding, ARRAY[0.1, 0.2, ...]) as distance
FROM ontology_embeddings
WHERE tenant_id = 'tenant-001'
ORDER BY distance ASC
LIMIT 10;

-- Inner product search
SELECT
    object_rid,
    display_name,
    INNER_PRODUCT(embedding, ARRAY[0.1, 0.2, ...]) as score
FROM ontology_embeddings
WHERE tenant_id = 'tenant-001'
ORDER BY score DESC
LIMIT 10;

User query: "Find active objects created in the last 30 days
             semantically similar to 'supply chain risk assessment'"
                    │
        ┌───────────┼───────────┐
        ▼           ▼           ▼
   Vector Search  Text Search   OLAP Filter
   (Semantic)     (Keywords)    (Time+Status)
        │           │           │
        └───────────┼───────────┘
                    ▼
           Result Fusion Ranking
           (Weighted Scoring)

-- Hybrid query: vector similarity + full-text match + OLAP filter
SELECT
    e.object_rid,
    e.display_name,
    e.description,
    e.object_type,
    L2_DISTANCE(e.embedding, ARRAY[/* query embedding */]) as vector_distance,
    e.created_at
FROM ontology_embeddings e
WHERE e.tenant_id = 'tenant-001'
  -- OLAP filter
  AND e.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  -- Full-text search
  AND e.description MATCH_ANY 'supply chain risk'
ORDER BY vector_distance ASC
LIMIT 20;

-- Weighted hybrid scoring query
WITH vector_results AS (
    SELECT
        object_rid,
        display_name,
        description,
        L2_DISTANCE(embedding, ARRAY[/* query embedding */]) as v_dist,
        ROW_NUMBER() OVER (ORDER BY L2_DISTANCE(embedding, ARRAY[/* query embedding */])) as v_rank
    FROM ontology_embeddings
    WHERE tenant_id = 'tenant-001'
    ORDER BY v_dist ASC
    LIMIT 100
),
text_results AS (
    SELECT
        object_rid,
        display_name,
        description,
        ROW_NUMBER() OVER (ORDER BY updated_at DESC) as t_rank
    FROM ontology_object_searchable
    WHERE tenant_id = 'tenant-001'
      AND description MATCH_ANY 'supply chain risk assessment'
    LIMIT 100
)
SELECT
    COALESCE(v.object_rid, t.object_rid) as object_rid,
    COALESCE(v.display_name, t.display_name) as display_name,
    -- Weighted score: vector similarity weight 0.6, text match weight 0.4
    (COALESCE(1.0 / v.v_rank, 0) * 0.6 +
     COALESCE(1.0 / t.t_rank, 0) * 0.4) as hybrid_score
FROM vector_results v
FULL OUTER JOIN text_results t ON v.object_rid = t.object_rid
ORDER BY hybrid_score DESC
LIMIT 20;

# intelligence-Layer/search/hybrid_search.py
from dataclasses import dataclass, field
from typing import List, Optional
from enum import Enum

class SearchMode(Enum):
    KEYWORD = "keyword"
    SEMANTIC = "semantic"
    HYBRID = "hybrid"

@dataclass
class SearchRequest:
    tenant_id: str
    query: str
    mode: SearchMode = SearchMode.HYBRID
    object_types: Optional[List[str]] = None
    status_filter: Optional[str] = None
    date_from: Optional[str] = None
    date_to: Optional[str] = None
    top_k: int = 20
    vector_weight: float = 0.6
    text_weight: float = 0.4

@dataclass
class SearchResult:
    object_rid: str
    display_name: str
    description: str
    object_type: str
    score: float
    highlights: List[str] = field(default_factory=list)

class HybridSearchEngine:
    """Hybrid search engine — unified vector + full-text + OLAP"""

    def __init__(self, doris_connection, embedding_model):
        self.conn = doris_connection
        self.model = embedding_model

    async def search(self, request: SearchRequest) -> List[SearchResult]:
        if request.mode == SearchMode.KEYWORD:
            return await self._keyword_search(request)
        elif request.mode == SearchMode.SEMANTIC:
            return await self._semantic_search(request)
        else:
            return await self._hybrid_search(request)

    async def _hybrid_search(self, request: SearchRequest) -> List[SearchResult]:
        # 1. Generate query vector
        query_embedding = self.model.encode(
            request.query,
            normalize_embeddings=True
        )

        # 2. Build hybrid query SQL
        embedding_str = ", ".join(str(x) for x in query_embedding.tolist())

        filters = [f"tenant_id = '{request.tenant_id}'"]
        if request.object_types:
            types_str = ", ".join(f"'{t}'" for t in request.object_types)
            filters.append(f"object_type IN ({types_str})")
        if request.status_filter:
            filters.append(f"status = '{request.status_filter}'")
        if request.date_from:
            filters.append(f"created_at >= '{request.date_from}'")

        where_clause = " AND ".join(filters)

        sql = f"""
        WITH vector_scores AS (
            SELECT object_rid, display_name, description, object_type,
                   1.0 / (1.0 + L2_DISTANCE(embedding, ARRAY[{embedding_str}])) as v_score
            FROM ontology_embeddings
            WHERE {where_clause}
            ORDER BY L2_DISTANCE(embedding, ARRAY[{embedding_str}]) ASC
            LIMIT {request.top_k * 3}
        ),
        text_scores AS (
            SELECT object_rid, display_name, description, object_type,
                   1.0 as t_score
            FROM ontology_object_searchable
            WHERE {where_clause}
              AND (display_name MATCH_ANY '{request.query}'
                   OR description MATCH_ANY '{request.query}')
            LIMIT {request.top_k * 3}
        )
        SELECT
            COALESCE(v.object_rid, t.object_rid) as object_rid,
            COALESCE(v.display_name, t.display_name) as display_name,
            COALESCE(v.description, t.description) as description,
            COALESCE(v.object_type, t.object_type) as object_type,
            (COALESCE(v.v_score, 0) * {request.vector_weight} +
             COALESCE(t.t_score, 0) * {request.text_weight}) as score
        FROM vector_scores v
        FULL OUTER JOIN text_scores t ON v.object_rid = t.object_rid
        ORDER BY score DESC
        LIMIT {request.top_k}
        """

        rows = await self.conn.execute(sql)
        return [
            SearchResult(
                object_rid=row["object_rid"],
                display_name=row["display_name"],
                description=row["description"],
                object_type=row["object_type"],
                score=row["score"]
            )
            for row in rows
        ]

-- Check index build progress
SHOW BUILD INDEX FROM ontology_db;

-- View table index information
SHOW INDEX FROM ontology_embeddings;

-- View segment-level index status
SHOW TABLET FROM ontology_embeddings;

-- Drop and rebuild inverted index
DROP INDEX idx_description ON ontology_object_searchable;

CREATE INDEX idx_description ON ontology_object_searchable(description)
    USING INVERTED
    PROPERTIES("parser" = "unicode", "support_phrase" = "true");

-- Trigger index rebuild
BUILD INDEX idx_description ON ontology_object_searchable;

-- Check index storage usage
SHOW DATA FROM ontology_embeddings;

-- Vector index compression (PQ quantization)
ALTER TABLE ontology_embeddings
MODIFY INDEX idx_embedding
PROPERTIES("index_type" = "HNSW", "pq_enable" = "true", "pq_subvector_num" = "48");

# be.conf — vector search related
enable_vectorized_engine = true
inverted_index_ram_dir_enable = true  # Cache index in memory

# Vector index search threads
vector_index_search_thread_pool_size = 16

# HNSW search parameters (global defaults)
default_hnsw_ef_search = 200

# be.conf — inverted index related
inverted_index_cache_stale_sweep_time_sec = 600
inverted_index_max_buffered_docs = 65536
inverted_index_compaction_enable = true

# Tokenizer cache
inverted_index_searcher_cache_limit = 10%

Total 64GB allocation plan:
├── Query execution engine    30GB (47%)
├── Vector index cache        10GB (16%)
├── Inverted index cache       8GB (12%)
├── Page cache                 8GB (12%)
├── Compaction                 5GB (8%)
└── System reserved            3GB (5%)

-- Use model_version field to isolate vectors from different models
SELECT * FROM ontology_embeddings
WHERE tenant_id = 'tenant-001'
  AND model_version = 'bge-base-zh-v1.5'
ORDER BY L2_DISTANCE(embedding, ARRAY[...]) ASC
LIMIT 10;

-- Limit indexed tokens per document for long text fields
ALTER TABLE ontology_object_searchable
MODIFY INDEX idx_description
PROPERTIES("max_token_per_doc" = "10000");

-- Strategy 1: Filter with scalar predicates first, then vector search
-- Strategy 2: Use partition key + vector index for partition pruning
-- Strategy 3: Two-phase query — coarse screening then fine ranking