OceanBase 三位一体数据库调研

-- 创建包含向量列的表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    embedding VECTOR(1536)  -- 1536 维向量（OpenAI Ada 模型）
);

-- 插入向量数据
INSERT INTO articles (id, title, content, embedding)
VALUES (1, 'AI 技术发展', '...', '[0.1, 0.2, ..., 0.3]');

-- 创建 HNSW 向量索引（OceanBase V4.3.3+ 语法）
CREATE VECTOR INDEX idx_embedding_hnsw ON articles(embedding)
    WITH (distance=l2, type=hnsw, lib=vsag);

-- 创建 IVF 向量索引（OceanBase V4.3.3+ 语法）
CREATE VECTOR INDEX idx_embedding_ivf ON articles(embedding)
    WITH (distance=l2, type=ivf, lib=vsag);

-- 最近邻搜索 (KNN) — 使用 ANN 操作符
SELECT id, title,
       ob_ann_distance(embedding, '[0.1, 0.2, ...]') AS distance
FROM articles
WHERE embedding <-> '[0.1, 0.2, ...]'
ORDER BY distance ASC
LIMIT 10;

-- 带过滤条件的混合搜索
SELECT id, title,
       ob_ann_distance(embedding, '[0.1, 0.2, ...]') AS distance
FROM articles
WHERE category = 'technology'
  AND created_at > '2024-01-01'
  AND embedding <~> '[0.1, 0.2, ...]'  -- 余弦距离 ANN 过滤
ORDER BY distance ASC
LIMIT 10;

-- 单一查询融合三种能力
SELECT
    t.order_id,
    t.amount,
    t.user_id,
    -- AP: 聚合分析
    SUM(h.amount) OVER (PARTITION BY t.user_id
                        ORDER BY t.created_at
                        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as week_total,
    -- Vector: 行为模式匹配
    (t.behavior_vec <-> reference_vec) as anomaly_score
FROM transactions t
JOIN transaction_history h ON t.user_id = h.user_id
WHERE t.created_at > NOW() - INTERVAL '1 hour'
  AND (t.behavior_vec <-> reference_vec) < 0.3  -- 向量相似度过滤
ORDER BY anomaly_score
LIMIT 100;

# 安装
pip install llama-index-vector-stores-oceanbase pyobvector

# 使用示例
from llama_index.vector_stores.oceanbase import OceanBaseVectorStore
from pyobvector import ObVecClient

# 配置 OceanBase 连接（通过 ObVecClient）
client = ObVecClient(
    host="127.0.0.1",
    port=2881,
    user="root@test",
    password="",
    database="test_db"
)

vector_store = OceanBaseVectorStore(
    client=client,
    dim=1536,
    table_name="documents"
)

# 创建索引
index = VectorStoreIndex.from_vector_store(vector_store)

# RAG 查询
query_engine = index.as_query_engine()
response = query_engine.query("什么是 OceanBase?")

# 安装
pip install langchain-oceanbase

# 使用示例
from langchain_oceanbase.vectorstores import OceanBaseVectorStore
from langchain_openai import OpenAIEmbeddings

# 配置
embeddings = OpenAIEmbeddings()
vector_store = OceanBaseVectorStore(
    connection_string="mysql+pymysql://root@test:@127.0.0.1:2881/test_db",
    embedding_function=embeddings,
    table_name="langchain_docs"
)

# 添加文档
vector_store.add_documents(documents)

# 相似性搜索
results = vector_store.similarity_search("查询内容", k=5)

-- 论文元数据表 (TP 场景)
CREATE TABLE papers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(500) NOT NULL,
    abstract TEXT,
    authors JSON,
    publication_date DATE,
    venue VARCHAR(200),
    arxiv_id VARCHAR(50) UNIQUE,
    pdf_url VARCHAR(500),
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_date (publication_date)
);

-- 论文向量表 (Vector DB 场景)
CREATE TABLE paper_embeddings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    paper_id BIGINT NOT NULL,
    chunk_index INT DEFAULT 0,
    chunk_text TEXT,
    embedding VECTOR(1536),  -- OpenAI text-embedding-3-small
    FOREIGN KEY (paper_id) REFERENCES papers(id)
);

-- 创建 HNSW 向量索引（OceanBase V4.3.3+ 语法）
CREATE VECTOR INDEX idx_paper_embedding_hnsw ON paper_embeddings(embedding)
    WITH (distance=l2, type=hnsw, lib=vsag);

-- 引用关系表 (分析场景)
CREATE TABLE citations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    citing_paper_id BIGINT NOT NULL,
    cited_paper_id BIGINT NOT NULL,
    citation_context TEXT,
    FOREIGN KEY (citing_paper_id) REFERENCES papers(id),
    FOREIGN KEY (cited_paper_id) REFERENCES papers(id),
    INDEX idx_citing (citing_paper_id),
    INDEX idx_cited (cited_paper_id)
);

# 拉取 OceanBase 镜像（使用最新 CE 版本）
docker pull oceanbase/oceanbase-ce:latest

# 启动容器（最小配置）
docker run -d \
  --name oceanbase \
  -p 2881:2881 \
  -e MODE=mini \
  -e OB_TENANT_PASSWORD=your_password \
  oceanbase/oceanbase-ce:latest

# 等待启动完成（约 2-5 分钟）
docker logs -f oceanbase

# 当看到 "boot success!" 表示启动成功

# 安装 OBD (OceanBase Deployer)
# 注意：以下 URL 为示例，请从 OceanBase 官方下载页获取最新版本
# https://www.oceanbase.com/softwarecenter
bash -c "$(curl -s https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/latest/oceanbase-all-in-one.sh)"

# 部署单节点集群
obd cluster deploy demo -c mini.yaml
obd cluster start demo

# 使用 MySQL 客户端连接
mysql -h127.0.0.1 -P2881 -uroot@test -p your_password

# 或使用 obclient
obclient -h127.0.0.1 -P2881 -uroot@test -p your_password

pip install llama-index-vector-stores-oceanbase
pip install pyobvector
pip install llama-index
pip install openai
pip install pymysql

import pymysql

# 连接 OceanBase
conn = pymysql.connect(
    host='127.0.0.1',
    port=2881,
    user='root@test',
    password='your_password',
    database='test_db'
)

cursor = conn.cursor()

# 创建论文表
cursor.execute('''
CREATE TABLE IF NOT EXISTS papers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(500) NOT NULL,
    abstract TEXT,
    authors JSON,
    publication_date DATE,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# 创建向量表
cursor.execute('''
CREATE TABLE IF NOT EXISTS paper_embeddings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    paper_id BIGINT NOT NULL,
    chunk_index INT DEFAULT 0,
    chunk_text TEXT,
    embedding VECTOR(1536)
)
''')

# 创建 HNSW 向量索引（OceanBase V4.3.3+ 语法）
cursor.execute('''
CREATE VECTOR INDEX IF NOT EXISTS idx_embedding_hnsw
ON paper_embeddings(embedding)
    WITH (distance=l2, type=hnsw, lib=vsag)
''')

conn.commit()
print("Tables created successfully!")

from llama_index.vector_stores.oceanbase import OceanBaseVectorStore
from llama_index.core import VectorStoreIndex, Document, Settings
from llama_index.embeddings.openai import OpenAIEmbedding
from llama_index.llms.openai import OpenAI
from pyobvector import ObVecClient
import os

# 配置 OpenAI
os.environ["OPENAI_API_KEY"] = "your-api-key"

# 配置 LlamaIndex
Settings.embed_model = OpenAIEmbedding(model="text-embedding-3-small")
Settings.llm = OpenAI(model="gpt-4o-mini")

# 创建 OceanBase 向量存储（通过 ObVecClient 连接）
client = ObVecClient(
    host="127.0.0.1",
    port=2881,
    user="root@test",
    password="your_password",
    database="test_db"
)

vector_store = OceanBaseVectorStore(
    client=client,
    dim=1536,
    table_name="paper_embeddings"
)

# 准备示例文档
documents = [
    Document(
        text="OceanBase 是蚂蚁集团自主研发的企业级分布式关系数据库，具备 HTAP 能力。",
        metadata={"source": "oceanbase_intro", "category": "database"}
    ),
    Document(
        text="LlamaIndex 是一个用于构建 RAG 应用的框架，支持多种向量数据库集成。",
        metadata={"source": "llamaindex_intro", "category": "ai_framework"}
    ),
    Document(
        text="向量搜索通过计算向量之间的相似度来找到语义相关的内容。",
        metadata={"source": "vector_search_intro", "category": "technology"}
    )
]

# 创建索引并添加文档
index = VectorStoreIndex.from_documents(
    documents,
    vector_store=vector_store
)

# 创建查询引擎
query_engine = index.as_query_engine(similarity_top_k=3)

# 执行 RAG 查询
response = query_engine.query("什么是 OceanBase? 它有什么特点?")
print(f"回答: {response}")

# 混合查询示例：结合向量搜索与 SQL 过滤
from llama_index.core.vector_stores import MetadataFilters, ExactMatchFilter

filters = MetadataFilters(
    filters=[ExactMatchFilter(key="category", value="database")]
)

filtered_response = query_engine.query(
    "介绍一下数据库相关技术",
    filters=filters
)
print(f"过滤后回答: {filtered_response}")

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=2881,
    user='root@test',
    password='your_password',
    database='test_db'
)

cursor = conn.cursor()

# 论文分类统计 (AP 场景)
cursor.execute('''
SELECT
    category,
    COUNT(*) as paper_count,
    AVG(LENGTH(abstract)) as avg_abstract_length
FROM papers
GROUP BY category
ORDER BY paper_count DESC
''')

for row in cursor.fetchall():
    print(f"分类: {row[0]}, 论文数: {row[1]}, 平均摘要长度: {row[2]:.0f}")

# 向量相似度搜索 + 分析 (三位一体)
cursor.execute('''
SELECT
    p.title,
    p.category,
    pe.embedding <-> %s AS distance
FROM papers p
JOIN paper_embeddings pe ON p.id = pe.paper_id
WHERE p.publication_date > '2024-01-01'
ORDER BY distance
LIMIT 10
''', (query_vector,))

print("\n最相关的论文:")
for row in cursor.fetchall():
    print(f"  - {row[0]} (分类: {row[1]}, 相似度距离: {row[2]:.4f})")

-- 查看向量索引定义（通过系统视图确认索引存在及配置）
SELECT index_name, index_type, status
FROM oceanbase.DBA_OB_INDEXES
WHERE table_name = 'paper_embeddings'
  AND index_type = 'VECTOR';

-- 查看向量表副本分布（确认数据在集群中的位置）
SELECT table_name, svr_ip, role, replica_type
FROM oceanbase.DBA_OB_TABLE_LOCATIONS
WHERE TABLE_NAME = 'paper_embeddings';

-- 查看查询性能
SELECT
    query_sql,
    elapsed_time,
    queue_time,
    execute_time
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE query_sql LIKE '%embedding%'
ORDER BY elapsed_time DESC
LIMIT 10;

-- 查看资源使用
SELECT
    svr_ip,
    cpu_capacity,
    mem_capacity,
    disk_capacity
FROM oceanbase.GV$OB_SERVERS;