大数据与流处理 / 系统课时

关联商户维度

关联商户维度

本课把只有商户与门店编号的交易事件补充为可分析的业务记录:先用 MySQL CDC 持续维护维度快照,再通过处理时间 Lookup Join 查询商户、门店和区域属性,并控制缓存带来的性能与新鲜度差异。

学习目标

  • 设计适合实时查询的商户门店宽维表
  • 使用 CDC 同步维度,并通过 JDBC Lookup Join 完成补充
  • 处理维度缺失、缓存过期和同步延迟等生产边界

方案与数据流

交易流通常只携带 merchantId、storeId 等稳定编号。为避免连续查询商户、门店、区域三张表,可以先建立以 merchant_id + store_id 为主键的宽维表,一次查询得到全部分析属性。

MySQL CDC 会先读取表快照,再持续消费 binlog 变更;本课将变更写入独立的查询库,交易作业只访问该查询库,避免直接压测业务主库。:contentReference[oaicite:1]{index=1}

CDC 维度同步与交易关联链路

JDBC 连接器可作为 Lookup Join 的右表,并通过部分缓存减少外部数据库请求。缓存由各 TaskManager 独立持有,因此配置过大可能占用较多内存,过期时间过长则会延迟维度更新可见性。:contentReference[oaicite:2]{index=2}

完整 SQL 配置

下面的主示例分为两个 Flink SQL 文件。先提交 sync_dim.sql 并确认初始快照完成,再提交 enrich_trade.sql;不要把两个持续运行的 INSERT 当成一个顺序脚本执行。运行前需安装与 Flink 版本匹配的 MySQL CDC、JDBC、Kafka 和 MySQL 驱动 JAR。

sql
-- 先在 realtime 数据库创建查询表
CREATE TABLE dim_merchant_profile (
    merchant_id   VARCHAR(32)  NOT NULL,
    store_id      VARCHAR(32)  NOT NULL,
    merchant_name VARCHAR(128) NOT NULL,
    store_name    VARCHAR(128) NOT NULL,
    region_code   VARCHAR(32)  NOT NULL,
    region_name   VARCHAR(64)  NOT NULL,
    updated_at    TIMESTAMP(3) NOT NULL,
    PRIMARY KEY (merchant_id, store_id)
);

-- sync_dim.sql:持续把业务维度同步到查询库
SET 'execution.checkpointing.interval' = '10s';

CREATE TABLE merchant_profile_cdc (
    merchant_id STRING,
    store_id STRING,
    merchant_name STRING,
    store_name STRING,
    region_code STRING,
    region_name STRING,
    updated_at TIMESTAMP(3),
    PRIMARY KEY (merchant_id, store_id) NOT ENFORCED
) WITH (
    'connector' = 'mysql-cdc',
    'hostname' = '<DB_HOST>',
    'port' = '3306',
    'username' = '<DB_USER>',
    'password' = '<DB_PASSWORD>',
    'database-name' = 'trade',
    'table-name' = 'merchant_profile',
    'scan.startup.mode' = 'initial'
);

CREATE TABLE merchant_profile_snapshot (
    merchant_id STRING,
    store_id STRING,
    merchant_name STRING,
    store_name STRING,
    region_code STRING,
    region_name STRING,
    updated_at TIMESTAMP(3),
    PRIMARY KEY (merchant_id, store_id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://<DB_HOST>:3306/realtime',
    'table-name' = 'dim_merchant_profile',
    'username' = '<DB_USER>',
    'password' = '<DB_PASSWORD>'
);

INSERT INTO merchant_profile_snapshot
SELECT merchant_id, store_id, merchant_name, store_name,
       region_code, region_name, updated_at
FROM merchant_profile_cdc;

-- enrich_trade.sql:查询维度并输出补充后的交易
CREATE TABLE trade_events (
    transaction_id STRING,
    merchant_id STRING,
    store_id STRING,
    amount DECIMAL(18, 2),
    event_time TIMESTAMP_LTZ(3),
    proc_time AS PROCTIME()
) WITH (
    'connector' = 'kafka',
    'topic' = 'trade-events',
    'properties.bootstrap.servers' = '<KAFKA_HOST>:<PORT>',
    'properties.group.id' = 'trade-dimension-enrichment',
    'scan.startup.mode' = 'latest-offset',
    'format' = 'json',
    'json.fail-on-missing-field' = 'true',
    'json.ignore-parse-errors' = 'false'
);

CREATE TABLE merchant_profile_lookup (
    merchant_id STRING,
    store_id STRING,
    merchant_name STRING,
    store_name STRING,
    region_code STRING,
    region_name STRING,
    updated_at TIMESTAMP(3),
    PRIMARY KEY (merchant_id, store_id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://<DB_HOST>:3306/realtime',
    'table-name' = 'dim_merchant_profile',
    'username' = '<DB_USER>',
    'password' = '<DB_PASSWORD>',
    'lookup.cache' = 'PARTIAL',
    'lookup.partial-cache.max-rows' = '100000',
    'lookup.partial-cache.expire-after-write' = '30s',
    'lookup.partial-cache.cache-missing-key' = 'false',
    'lookup.max-retries' = '3'
);

CREATE TABLE enriched_trade_print (
    transaction_id STRING,
    merchant_id STRING,
    store_id STRING,
    merchant_name STRING,
    store_name STRING,
    region_code STRING,
    region_name STRING,
    amount DECIMAL(18, 2),
    event_time TIMESTAMP_LTZ(3)
) WITH (
    'connector' = 'print'
);

INSERT INTO enriched_trade_print
SELECT
    t.transaction_id,
    t.merchant_id,
    t.store_id,
    COALESCE(d.merchant_name, 'UNKNOWN'),
    COALESCE(d.store_name, 'UNKNOWN'),
    COALESCE(d.region_code, 'UNKNOWN'),
    COALESCE(d.region_name, 'UNKNOWN'),
    t.amount,
    t.event_time
FROM trade_events AS t
LEFT JOIN merchant_profile_lookup
    FOR SYSTEM_TIME AS OF t.proc_time AS d
ON t.merchant_id = d.merchant_id
AND t.store_id = d.store_id;

当维度表存在 M001 + S001,而 M999 + S999 尚不存在时,忽略 print sink 的并行子任务前缀后,可见等价结果:

text
+I[T1001, M001, S001, 华南便利, 南山店, CN-44, 广东, 88.00, <TIME>]
+I[T1002, M999, S999, UNKNOWN, UNKNOWN, UNKNOWN, UNKNOWN, 20.00, <TIME>]

<DB_HOST>、<DB_USER>、<DB_PASSWORD>、<KAFKA_HOST>、<PORT> 和 <TIME> 需要替换为实际值。首个同步作业持续运行,没有“执行完成”日志;应通过查询 dim_merchant_profile 的行数和更新时间确认初始快照已经可用。

一致性与验收

维度新鲜度同时受 CDC 同步延迟和 Lookup 缓存过期时间影响。若商户属性必须秒级生效,应缩短缓存时间或禁用缓存;若数据库压力更重要,可以延长缓存,但要接受短时间旧值。cache-missing-key=false 可避免把暂时不存在的维度长期缓存为空。

使用 LEFT JOIN 能保留维度缺失的交易,并通过 UNKNOWN 暴露数据质量问题。验收时至少检查已有维度、缺失维度、维度更新、维度删除和数据库短暂不可用五种情况;同时监控 Lookup 失败率、缓存命中率、CDC 延迟与查询库连接数。

查询表的复合主键可以使重复写入覆盖同一条记录,但这不等于完整的端到端 exactly-once。生产发布前仍需结合 Checkpoint、故障恢复和目标库事务能力验证重复与丢失边界。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

关联商户维度

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通