E-MapReduce:Best practices for AI Function

Core capabilities

This tutorial uses the following built-in AI functions:

Key advantages

• Data stays in the engine: All AI processing runs inside StarRocks. Sensitive financial data never leaves the database.

• Pure SQL interface: Analysts work with familiar SQL instead of building Python or Java pipelines.

• Batch processing at scale: AI functions can process millions of text records in batch, with native ETL pipeline integration.

• Seamless analytics integration: AI function results feed directly into GROUP BY, JOIN, window functions, and other analytical operations.

Environment requirements

• EMR Serverless StarRocks 2.1.1 or later.

• AI Function resources enabled in AI Center.

Sample data

This tutorial uses three sample tables that simulate typical financial text data. Create the tables and insert sample records as follows.

CREATE TABLE fin_customer_tickets (
    ticket_id       BIGINT,
    customer_id     VARCHAR(20),
    channel         VARCHAR(10)    COMMENT 'Channel: app/web/phone/branch',
    product_type    VARCHAR(20)    COMMENT 'Product: credit_card/loan/deposit/fund/insurance',
    ticket_text     VARCHAR(65533) COMMENT 'Ticket content',
    created_at      DATETIME
)
DUPLICATE KEY(ticket_id)
DISTRIBUTED BY HASH(ticket_id) BUCKETS 8;

INSERT INTO fin_customer_tickets VALUES
(1001, 'C20240001', 'app', 'credit_card',
 'Your credit card statement is wrong AGAIN! I paid 8000 last month but the bill still shows 5000 outstanding. This is the third time! Fix this or I will escalate to the banking regulator!', '2026-05-01 09:15:00'),
(1002, 'C20240002', 'phone', 'loan',
 'I would like to ask about the process for early mortgage repayment. What documents do I need? Is there a prepayment penalty?', '2026-05-01 10:30:00'),
(1003, 'C20240003', 'web', 'fund',
 'The "conservative" fund you recommended lost 15% in a single month! How is that conservative? I demand compensation! Is the fund manager even doing their job?', '2026-05-01 11:45:00'),
(1004, 'C20240004', 'app', 'deposit',
 'The auto-renewal feature for large CDs is very convenient. The interest rate is also decent, up 0.1 percentage points from before. Overall a good experience.', '2026-05-01 14:00:00'),
(1005, 'C20240005', 'branch', 'insurance',
 'Insurance claim processing is way too slow. I spent 30,000 on a hospital stay. Submitted all documents two months ago and still no result. Called customer service multiple times and they just say it is under review.', '2026-05-01 15:20:00'),
(1006, 'C20240006', 'app', 'credit_card',
 'The new platinum card perks are great. Airport lounge was comfortable, and the points redemption program is a good deal. Already recommended it to friends.', '2026-05-01 16:00:00'),
(1007, 'C20240007', 'phone', 'loan',
 'Can I change my car loan payment date? Currently it debits on the 5th, but my salary arrives on the 15th. It is tight every month.', '2026-05-02 09:00:00'),
(1008, 'C20240008', 'web', 'fund',
 'My index fund DCA has been running for six months. Returns are decent, around 8% annualized. Planning to keep holding.', '2026-05-02 10:15:00');

CREATE TABLE fin_regulatory_news (
    news_id         BIGINT,
    source          VARCHAR(50)    COMMENT 'Issuing authority',
    publish_date    DATE,
    title           VARCHAR(500),
    content         VARCHAR(65533) COMMENT 'Full text'
)
DUPLICATE KEY(news_id)
DISTRIBUTED BY HASH(news_id) BUCKETS 4;

INSERT INTO fin_regulatory_news VALUES
(2001, 'Central Bank', '2026-04-28',
 'Notice on Strengthening Financial Consumer Protection',
 'To further protect financial consumer rights and regulate the conduct of financial institutions, in accordance with the Consumer Protection Law and related regulations, the following requirements are hereby issued: 1. Financial institutions shall establish consumer protection mechanisms with dedicated departments or personnel. 2. Financial institutions shall fully disclose product risks during marketing and sales, and shall not engage in false or misleading advertising. 3. Financial institutions shall maintain accessible complaint channels and resolve complaints within 15 business days.'),
(2002, 'Banking Regulatory Commission', '2026-04-25',
 'Regulatory Requirements on Credit Fund Usage',
 'Recent inspections have identified cases of bank credit funds flowing illegally into real estate and stock markets. Requirements: All banking institutions shall strengthen post-lending management to ensure credit funds are used for their intended purposes. For business loans, institutions shall verify the genuine business needs of borrowers to prevent fund misuse. Violating institutions will face regulatory penalties including but not limited to fines and business scope restrictions.'),
(2003, 'Securities Regulatory Commission', '2026-04-20',
 'Opinions on Further Standardizing Information Disclosure by Listed Companies',
 'To improve the quality of information disclosure by listed companies and protect investor rights, the following opinions are issued: Listed companies shall disclose information truthfully, accurately, completely, and in a timely manner, with no false records, misleading statements, or material omissions. Listed companies are encouraged to voluntarily disclose information relevant to investor decision-making to enhance transparency.'),
(2004, 'Central Bank', '2026-04-15',
 'Q1 2026 Monetary Policy Report',
 'In Q1 2026, prudent monetary policy remained precise and effective. At the end of March, broad money supply M2 grew 8.2% year-over-year. RMB loan balances grew 10.5% YoY, and aggregate financing stock grew 9.8% YoY. Interest rate liberalization continued to advance, with corporate lending rates remaining at low levels. In the next phase, prudent monetary policy will continue to maintain reasonably ample liquidity.');

CREATE TABLE fin_contracts (
    contract_id     VARCHAR(30),
    contract_type   VARCHAR(20)   COMMENT 'Type: loan/guarantee/pledge',
    party_a         VARCHAR(200),
    party_b         VARCHAR(200),
    contract_text   VARCHAR(65533) COMMENT 'Key contract terms'
)
DUPLICATE KEY(contract_id)
DISTRIBUTED BY HASH(contract_id) BUCKETS 4;

INSERT INTO fin_contracts VALUES
('LOAN-2026-0001', 'loan', 'ABC Bank Hangzhou Branch', 'Hangzhou Starcorp Technology Co., Ltd.',
 'Loan amount: RMB 5,000,000.00. Term: January 15, 2026 to January 14, 2027 (12 months). Interest rate: LPR plus 60 basis points, i.e. 4.15% per annum. Repayment: Equal principal and interest, due on the 20th of each month. Default clause: Late payments incur a penalty of 0.05% per day on the overdue amount. Collateral: Property located at XX Road, Xihu District, Hangzhou, owned by the borrower.'),
('LOAN-2026-0002', 'loan', 'ABC Bank Shanghai Branch', 'Shanghai Jinxiu Trading Co., Ltd.',
 'Loan amount: RMB 10,000,000.00. Term: March 1, 2026 to February 28, 2028 (24 months). Interest rate: Fixed at 4.35% per annum. Repayment: Interest-only quarterly, principal due at maturity. Default clause: If the borrower misses three consecutive interest payments or misuses loan funds, the lender may declare the loan immediately due. Guarantee: Joint and several liability guarantee provided by Shanghai Jinxiu Group Co., Ltd.'),
('GUAR-2026-0001', 'guarantee', 'ABC Bank Hangzhou Branch', 'Zhejiang Xinda Industrial Group Co., Ltd.',
 'Guarantee type: Joint and several liability. Scope: Principal of RMB 5,000,000 plus interest, penalties, liquidated damages, and enforcement costs. Period: Two years from the maturity of the principal obligation. Guarantor declaration: The guarantor has full civil capacity and is willing to provide joint and several liability guarantee with all its assets. Contact: Wei Zhang, Phone: 13812345678, ID: 330102198501153456.');

Sentiment analysis: detecting customer emotions

Quickly assess the sentiment of each ticket to prioritize negative feedback:

SELECT
    ticket_id,
    product_type,
    ai_sentiment(ticket_text) AS sentiment,
    LEFT(ticket_text, 40) AS preview
FROM fin_customer_tickets
ORDER BY ticket_id;

Sample output:

Ticket classification: auto-categorizing by business type

Automatically route free-text tickets into predefined business categories:

SELECT
    ticket_id,
    ai_classify(
        ticket_text,
        ARRAY['Billing Dispute', 'General Inquiry', 'Complaint', 'Product Feedback', 'Claims Issue']
    ) AS category,
    ai_sentiment(ticket_text) AS sentiment
FROM fin_customer_tickets;

Sentiment distribution: aggregating by product

Combine AI analysis results with SQL aggregation:

SELECT
    product_type,
    ai_sentiment(ticket_text) AS sentiment,
    COUNT(*) AS ticket_count
FROM fin_customer_tickets
GROUP BY product_type, ai_sentiment(ticket_text)
ORDER BY product_type, ticket_count DESC;

Sample output:

Semantic filtering: screening high-risk tickets

Use ai_filter in a WHERE clause to filter rows by semantic conditions. The following query finds tickets where the customer threatens regulatory escalation:

SELECT
    ticket_id,
    product_type,
    ticket_text,
    ai_sentiment(ticket_text) AS sentiment
FROM fin_customer_tickets
WHERE ai_filter(ticket_text, 'customer threatens to escalate to a regulator or demands compensation');

Notice classification

Classify regulatory notices by regulatory domain:

SELECT
    news_id,
    source,
    title,
    ai_classify(
        content,
        ARRAY['Consumer Protection', 'Credit Regulation', 'Disclosure Requirements', 'Monetary Policy', 'Anti-Money Laundering', 'Market Access']
    ) AS reg_category
FROM fin_regulatory_news;

Notice summarization

Generate concise summaries of lengthy notices for executive review:

SELECT
    news_id,
    title,
    ai_summarize(content) AS summary
FROM fin_regulatory_news
WHERE publish_date >= '2026-04-01';

Compliance impact analysis

Use ai_complete for deeper business analysis, assessing how a notice impacts the bank's operations:

SELECT
    news_id,
    title,
    ai_complete(
        content,
        'You are a bank compliance analyst. Analyze the specific impact of this regulatory notice on commercial bank retail operations. List the key remediation items. Keep your response under 100 words.'
    ) AS compliance_impact
FROM fin_regulatory_news
WHERE ai_filter(content, 'involves banking compliance requirements or penalty measures');

Contract element extraction

Automatically extract structured key terms from contract text:

SELECT
    contract_id,
    contract_type,
    ai_extract(
        contract_text,
        ARRAY['Loan Amount', 'Term', 'Annual Rate', 'Repayment Method', 'Collateral', 'Default Clause']
    ) AS key_elements
FROM fin_contracts
WHERE contract_type = 'loan';

Sample output (JSON format):

{
  "Loan Amount": "RMB 5,000,000.00",
  "Term": "January 15, 2026 to January 14, 2027 (12 months)",
  "Annual Rate": "LPR plus 60 basis points, i.e. 4.15%",
  "Repayment Method": "Equal principal and interest, due on the 20th",
  "Collateral": "Property in Xihu District, Hangzhou",
  "Default Clause": "Late payments incur a 0.05% daily penalty"
}

Contract PII redaction

Redact personally identifiable information from contract text to meet data compliance requirements:

SELECT
    contract_id,
    ai_redact(contract_text) AS redacted_text
FROM fin_contracts
WHERE contract_type = 'guarantee';

Redaction result:

Original: Contact: Wei Zhang, Phone: 13812345678, ID: 330102198501153456
Redacted: Contact: W** Z****, Phone: 138****5678, ID: 330102********3456

Contract risk assessment

Combine ai_extract and ai_complete to build a contract risk assessment pipeline:

WITH contract_elements AS (
    SELECT
        contract_id,
        party_a,
        party_b,
        contract_text,
        ai_extract(
            contract_text,
            ARRAY['Loan Amount', 'Annual Rate', 'Collateral', 'Default Clause']
        ) AS elements
    FROM fin_contracts
    WHERE contract_type = 'loan'
)
SELECT
    contract_id,
    party_b,
    elements,
    ai_complete(
        CONCAT('Contract elements: ', CAST(elements AS VARCHAR), '\n\nFull contract: ', contract_text),
        'You are a credit risk expert. Based on the contract elements, assess the risk level (Low/Medium/High) and identify key risk factors. Keep your response under 80 words.'
    ) AS risk_assessment
FROM contract_elements;

Customer 360 text analytics dashboard

Combine multiple AI functions to build a multi-dimensional analysis view of customer feedback:

SELECT
    ticket_id,
    customer_id,
    product_type,
    channel,
    ai_sentiment(ticket_text)  AS sentiment,
    ai_classify(
        ticket_text,
        ARRAY['Billing Dispute', 'General Inquiry', 'Complaint', 'Product Feedback', 'Claims Issue']
    ) AS category,
    ai_extract(
        ticket_text,
        ARRAY['Amount Involved', 'Customer Request']
    ) AS key_info,
    ai_summarize(ticket_text)  AS summary
FROM fin_customer_tickets
WHERE created_at >= '2026-05-01';

Materialized views for AI results

Persist AI analysis results in a materialized view to avoid redundant computation:

CREATE MATERIALIZED VIEW mv_ticket_analysis AS
SELECT
    ticket_id,
    customer_id,
    product_type,
    channel,
    created_at,
    ai_sentiment(ticket_text) AS sentiment,
    ai_classify(
        ticket_text,
        ARRAY['Billing Dispute', 'General Inquiry', 'Complaint', 'Product Feedback', 'Claims Issue']
    ) AS category
FROM fin_customer_tickets;

Subsequent queries read from the materialized view without re-invoking AI functions:

SELECT
    DATE(created_at) AS dt,
    product_type,
    COUNT(*) AS negative_tickets
FROM mv_ticket_analysis
WHERE sentiment = 'negative'
GROUP BY DATE(created_at), product_type
ORDER BY dt, negative_tickets DESC;

ETL pipeline integration: INSERT INTO SELECT

Write AI processing results to a downstream analytics table to build a complete data processing pipeline:

-- Create the analysis results table
CREATE TABLE fin_ticket_analysis_result (
    ticket_id       BIGINT,
    customer_id     VARCHAR(20),
    product_type    VARCHAR(20),
    sentiment       VARCHAR(20),
    category        JSON,
    key_info        JSON,
    summary         VARCHAR(65533),
    analyzed_at     DATETIME DEFAULT CURRENT_TIMESTAMP
)
DUPLICATE KEY(ticket_id)
DISTRIBUTED BY HASH(ticket_id) BUCKETS 4;

-- Batch AI analysis with results written to the target table
INSERT INTO fin_ticket_analysis_result
    (ticket_id, customer_id, product_type, sentiment, category, key_info, summary)
SELECT
    ticket_id,
    customer_id,
    product_type,
    ai_sentiment(ticket_text),
    ai_classify(ticket_text, ARRAY['Billing Dispute', 'General Inquiry', 'Complaint', 'Product Feedback', 'Claims Issue']),
    ai_extract(ticket_text, ARRAY['Amount Involved', 'Customer Request']),
    ai_summarize(ticket_text)
FROM fin_customer_tickets
WHERE ticket_id NOT IN (SELECT ticket_id FROM fin_ticket_analysis_result);