E-MapReduce:AI 関数のベストプラクティス

コア機能

このチュートリアルでは、以下の組み込み AI 関数を使用します。

主な利点

• データはエンジン内に留まる：すべての AI 処理は StarRocks 内部で実行されます。機密性の高い金融データがデータベースから出ることはありません。

• 純粋な SQL インターフェース：分析担当者は、Python や Java のパイプラインを構築する代わりに、使い慣れた SQL を使用して作業します。

• 大規模なバッチ処理：AI 関数は、ネイティブの ETL パイプライン統合により、数百万件のテキストレコードをバッチで処理できます。

• シームレスな分析統合：AI 関数の結果は、GROUP BY、JOIN、ウィンドウ関数、およびその他の分析操作に直接フィードされます。

環境要件

• EMR Serverless StarRocks 2.1.1 以降。

• AI Center で AI 関数リソースが有効になっていること。

サンプルデータ

このチュートリアルでは、一般的な金融テキストデータをシミュレートした 3 つのサンプルテーブルを使用します。以下のようにテーブルを作成し、サンプルレコードを挿入します。

CREATE TABLE fin_customer_tickets (
    ticket_id       BIGINT,
    customer_id     VARCHAR(20),
    channel         VARCHAR(10)    COMMENT 'チャネル：app/web/phone/branch',
    product_type    VARCHAR(20)    COMMENT '製品：credit_card/loan/deposit/fund/insurance',
    ticket_text     VARCHAR(65533) COMMENT 'チケットの内容',
    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 '発行機関',
    publish_date    DATE,
    title           VARCHAR(500),
    content         VARCHAR(65533) COMMENT '全文'
)
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 '種類：loan/guarantee/pledge',
    party_a         VARCHAR(200),
    party_b         VARCHAR(200),
    contract_text   VARCHAR(65533) COMMENT '主要な契約条件'
)
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.');

感情分析：顧客の感情の検出

各チケットの感情を迅速に評価し、ネガティブなフィードバックを優先的に処理します。

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;

出力例：

チケットの分類：業務タイプ別の自動分類

フリーテキストのチケットを、あらかじめ定義されたビジネスカテゴリに自動的にルーティングします。

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;

感情の分布：製品別の集計

AI の分析結果と SQL の集計を組み合わせます。

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;

出力例：

セマンティックフィルタリング：高リスクチケットのスクリーニング

WHERE 句で ai_filter を使用して、セマンティックな条件で行をフィルタリングします。次のクエリは、顧客が規制当局へのエスカレーションを脅したり、補償を要求したりしているチケットを検索します。

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');

通知の分類

規制通知を規制ドメインごとに分類します。

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;

通知の要約

経営層のレビュー用に、長い通知の簡潔な要約を生成します。

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

コンプライアンス影響分析

ai_complete を使用してより深いビジネス分析を行い、通知が銀行の業務にどのように影響するかを評価します。

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');

契約要素の抽出

契約書のテキストから構造化されたキーフレーズを自動的に抽出します。

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';

出力例 (JSON形式)：

{
  "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"
}

契約書の PII リダクション

データコンプライアンス要件を満たすために、契約書のテキストから個人を特定できる情報をリダクションします。

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

リダクション結果：

原文：Contact: Wei Zhang, Phone: 13812345678, ID: 330102198501153456
リダクション後：Contact: W** Z****, Phone: 138****5678, ID: 330102********3456

契約リスク評価

ai_extract と ai_complete を組み合わせて、契約リスク評価パイプラインを構築します。

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;

顧客360度テキスト分析ダッシュボード

複数の AI 関数を組み合わせて、顧客フィードバックの多次元分析ビューを構築します。

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';

AI 結果のマテリアライズドビュー

AI 分析結果をマテリアライズドビューに永続化することで、冗長な計算を回避します。

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;

後続のクエリは、AI 関数を再呼び出しすることなく、マテリアライズドビューから読み取ります。

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 パイプライン統合：INSERT INTO SELECT

AI 処理結果を下流の分析テーブルに書き込み、完全なデータ処理パイプラインを構築します。

-- 分析結果テーブルの作成
CREATE TABLE fin_ticket_analysis_result (
    ticket_id       BIGINT,
    customer_id     VARCHAR(20),
    product_type    VARCHAR(20),
    sentiment       VARCHAR(20),
    category        VARCHAR(50),
    key_info        JSON,
    summary         VARCHAR(65533),
    analyzed_at     DATETIME DEFAULT CURRENT_TIMESTAMP
)
DUPLICATE KEY(ticket_id)
DISTRIBUTED BY HASH(ticket_id) BUCKETS 4;

-- ターゲットテーブルに結果を書き込むバッチ AI 分析
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);