开工第一弹！Doris & SelectDB for AI 实战：从零搭建非结构化数据智能分析洞察系统

CREATE TABLE forum_questions (
    question_id BIGINT,     --问题ID
    user_id BIGINT,         --提问用户ID
    title STRING,           --标题
    content STRING,         --用户提问和回复的内容
    INDEX idx_content (`content`) USING INVERTED PROPERTIES("lower_case" = "true", "parser" = "chinese", "support_phrase" = "true")
)
UNIQUE KEY(question_id)
DISTRIBUTED BY HASH(question_id) BUCKETS 2
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE forum_question_embeddings (
    user_id        BIGINT NOT NULL,                    -- 原问题所属用户
    question_id    BIGINT NOT NULL,                    -- 原问题ID
    chunk_id       BIGINT NOT NULL AUTO_INCREMENT,     -- 文本切片 ID（每片对应一条向量）
    content_chunk  STRING NOT NULL,                    -- 文本切片内容
    embedding      array<float> NOT NULL,     -- 1024维向量
      INDEX ann_index (embedding) USING ANN PROPERTIES(
      "index_type"="hnsw",
      "metric_type"="inner_product",
      "dim"="1024",
      "quantizer"="flat"
  )
)
DUPLICATE KEY(user_id, question_id, chunk_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 2
PROPERTIES (
    "replication_num" = "1"
);

CREATE TABLE parsed_results (
    id BIGINT NOT NULL AUTO_INCREMENT,        -- 主键ID
    feedback_id BIGINT NOT NULL,     -- 用户反馈ID（外键关联 forum_questions）
    parsed_data VARIANT NOT NULL     -- 解析结果（任意 JSON 数据）
)
UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 2
PROPERTIES (
    "replication_num" = "1"
);

CREATE RESOURCE"qwen-text-embedding-v4"
PROPERTIES (
    'type' = 'ai',
    'ai.provider_type' = 'QWen',
    'ai.endpoint' = 'https://dashscope.aliyuncs.com/compatible-mode/v1',
    'ai.model_name' = 'text-embedding-v4',
    'ai.api_key' = 'xxx',
    'ai.temperature' = '0.7',
    'ai.max_token' = '1024',
    'ai.max_retries' = '3',
    'ai.retry_delay_second' = '1',
    'ai.dimensions' = '1024'
);

CREATE RESOURCE"glm-4-flash-250414"
PROPERTIES (
    'type' = 'ai',
    'ai.provider_type' = 'QWen',
    'ai.endpoint' = 'https://dashscope.aliyuncs.com/compatible-mode/v1',
    'ai.model_name' = 'qwen-flash',
    'ai.api_key' = 'xxx',
    'ai.temperature' = '0.7',
    'ai.max_token' = '-1',
    'ai.max_retries' = '3',
    'ai.retry_delay_second' = '1'
);

WITH raw AS (
    SELECT
        question_id,
        user_id,
        content,
        LENGTH(content) AS content_len
    FROM forum_questions
),
t AS (
    SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
num AS (
    SELECT
        (a.d * 100 + b.d * 10 + c.d + 1) AS n
    FROM t a
    CROSS JOIN t b
    CROSS JOIN t c
),
chunks AS (
    SELECT
        r.user_id,
        r.question_id,
        SUBSTRING(r.content, (n - 1) * 400 + 1, 400) AS content_chunk
    FROM raw r
    JOIN num ON (n - 1) * 400 + 1 <= r.content_len
)
INSERT INTO forum_question_embeddings (user_id, question_id, content_chunk, embedding)
SELECT
    user_id,
    question_id,
    content_chunk,
    EMBED("qwen-text-embedding-v4", content_chunk) AS embedding
FROM chunks;

SET @extract_prompt = "你是一名专业的产品反馈分析助手。现提供一段来自用户的反馈文本（feedback_text），请从中提取与“物化视图（Materialized View, MV）”相关的关键信息，并以严格的 JSON 格式输出。  
如果反馈文本与 MV 无关，或无法识别任何相关信息，则直接返回字符串 NULL。

请从 feedback_text 中提取以下信息并生成 JSON：

{
  \"used_external_source\": \"用户是否提到使用了哪些外部数据源，例如 MySQL、Oracle、Hive、Iceberg 等。如果未提及则为 null。\",
  \"transparent_rewrite\": \"是否使用或提到透明改写（true / false / null）。\",
  \"usage_scenario\": \"用户描述的使用场景。若无则为 null。\",
  \"problems_or_gaps\": \"用户认为 MV 不足或存在的问题。若无则为 null。\",
  \"other_insights\": \"任何与 MV 相关、对产品改进有价值的额外信息。若无则为 null。\"
}

要求：  
1. 输出内容必须是纯 JSON，对象必须以 { 开头、以 } 结尾。
2. 禁止输出任何 Markdown 代码块标记，如 json、、json、``` 等。
3. 禁止输出任何额外文字、描述、解释、前缀或后缀。
4. 不得在 JSON 外输出换行、空格或注释。";

INSERT INTO parsed_results (feedback_id, parsed_data)
SELECT
    question_id,
    AI_GENERATE(
        'glm-4-flash-250414',
         concat(@extract_prompt ,'\nfeedback_text: ```' ,content, '```') 
    ) AS extracted_info
FROM forum_questions where search('content:物化视图');

SELECT 
    lower(CAST(c.used_external_source ASstring)) AS used_external_source, 
    COUNT(*)
FROM (
    SELECT
        a.parsed_data.usage_scenario, 
        a.parsed_data.transparent_rewrite, 
        a.parsed_data.used_external_source AS used_external_source, 
        a.parsed_data.problems_or_gaps, 
        a.parsed_data.other_insights, 
        b.content
    FROM parsed_results a
        JOIN forum_questions b ON a.feedback_id = b.question_id
    WHERE a.parsed_data.used_external_source != ""
        AND a.parsed_data.used_external_source != "null"
) c
GROUP BY lower(CAST(c.used_external_source ASstring));

--向量检索文本
SET @search_text="数据时效性差、 ETL 延迟、物化视图刷新不及时、数据不实时、实时查询不满足业务、报表延迟、同步滞后、时延高、更新慢、不能满足实时监控需求、数据落地慢、数据延时影响决策";

--存储有"数据时效性的痛点或需求"的用户的临时表
CREATE TEMPORARY TABLE retrieved_question_ids (
    question_id BIGINT
)
UNIQUE KEY(question_id)
DISTRIBUTED BY HASH(question_id) BUCKETS 2
PROPERTIES (
    "replication_num" = "1"
);

insert into retrieved_question_ids(question_id)
SELECT question_id                                    --向量检索
FROM forum_question_embeddings 
where inner_product_approximate(                    
        embedding,
        EMBED("qwen-text-embedding-v4", @search_text) 
       ) > 0.5
union ALL
select a.question_id from
(
    SELECT question_id,score() as relevance          --全文检索
    FROM forum_questions
    where content MATCH_ANY '时效性 实时 延迟 延时 更新不及时 刷新慢 滞后'
    ORDER BY relevance DESC
    LIMIT 100
)a;


SELECT
    lower(CAST(c.used_external_source AS STRING)) AS used_external_source,
    COUNT(DISTINCT c.question_id) AS feedback_cnt
FROM (
    SELECT
        a.feedback_id AS question_id,
        a.parsed_data.used_external_source AS used_external_source
    FROM parsed_results a
    JOIN retrieved_question_ids r
        ON a.feedback_id = r.question_id
    WHERE a.parsed_data.used_external_source ISNOTNULL
      AND a.parsed_data.used_external_source != ""
      AND lower(a.parsed_data.used_external_source) != "null"
) c
GROUP BY lower(CAST(c.used_external_source AS STRING))
ORDER BY feedback_cnt DESC;