变更数据捕获案例
# 变更数据捕获(CDC)案例
# 案例概述
变更数据捕获(Change Data Capture, CDC)是一种用于识别和捕获数据库中数据变更的技术。本案例展示如何使用StreamSQL实现实时CDC处理。
# 案例1：用户信息变更捕获
# 业务场景
电商系统需要实时捕获用户信息变更，同步到数据仓库和推荐系统。
# 数据输入
{
"operation": "UPDATE",
"table": "users",
"timestamp": "2024-01-15T10:30:00Z",
"before": {
"user_id": "U001",
"name": "John Doe",
"email": "john@example.com",
"status": "active",
"last_login": "2024-01-14T15:20:00Z"
},
"after": {
"user_id": "U001",
"name": "John Smith",
"email": "john.smith@example.com",
"status": "active",
"last_login": "2024-01-15T10:25:00Z"
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 期望输出
{
"change_id": "CHG001",
"user_id": "U001",
"changed_fields": ["name", "email", "last_login"],
"change_type": "profile_update",
"timestamp": "2024-01-15T10:30:00Z",
"sync_targets": ["data_warehouse", "recommendation_engine"],
"priority": "medium"
}
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 案例2：订单状态变更跟踪
# 业务场景
跟踪订单状态变更，实时更新库存、通知用户和触发后续流程。
# 数据输入
{
"operation": "UPDATE",
"table": "orders",
"timestamp": "2024-01-15T10:30:00Z",
"before": {
"order_id": "ORD001",
"status": "pending",
"amount": 299.99,
"user_id": "U001"
},
"after": {
"order_id": "ORD001",
"status": "shipped",
"amount": 299.99,
"user_id": "U001",
"tracking_number": "TRK123456"
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 期望输出
{
"order_id": "ORD001",
"status_change": {
"from": "pending",
"to": "shipped"
},
"actions": [
{
"type": "inventory_update",
"priority": "high"
},
{
"type": "user_notification",
"template": "order_shipped",
"data": {
"tracking_number": "TRK123456"
}
}
],
"timestamp": "2024-01-15T10:30:00Z"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 案例3：产品价格变更监控
# 业务场景
监控产品价格变更，触发价格策略调整和竞争分析。
# 数据输入
{
"operation": "UPDATE",
"table": "products",
"timestamp": "2024-01-15T10:30:00Z",
"before": {
"product_id": "P001",
"name": "iPhone 15",
"price": 999.00,
"category": "electronics"
},
"after": {
"product_id": "P001",
"name": "iPhone 15",
"price": 899.00,
"category": "electronics"
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 期望输出
{
"product_id": "P001",
"price_change": {
"old_price": 999.00,
"new_price": 899.00,
"change_percent": -10.01,
"change_amount": -100.00
},
"impact_analysis": {
"competitor_comparison": "below_market",
"demand_prediction": "increase",
"margin_impact": -5.2
},
"recommendations": [
"update_marketing_campaigns",
"adjust_inventory_levels"
],
"timestamp": "2024-01-15T10:30:00Z"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 案例4：库存变更实时同步
# 业务场景
实时同步库存变更到多个系统，确保库存数据一致性。
# 数据输入
{
"operation": "UPDATE",
"table": "inventory",
"timestamp": "2024-01-15T10:30:00Z",
"before": {
"product_id": "P001",
"warehouse_id": "WH001",
"quantity": 100,
"reserved": 10
},
"after": {
"product_id": "P001",
"warehouse_id": "WH001",
"quantity": 95,
"reserved": 15
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 期望输出
{
"product_id": "P001",
"warehouse_id": "WH001",
"inventory_change": {
"quantity_delta": -5,
"reserved_delta": 5,
"available_before": 90,
"available_after": 80
},
"sync_status": {
"ecommerce_platform": "synced",
"pos_system": "synced",
"mobile_app": "pending"
},
"alerts": [
{
"type": "low_stock",
"threshold": 100,
"current": 80
}
],
"timestamp": "2024-01-15T10:30:00Z"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 案例5：用户权限变更审计
# 业务场景
审计用户权限变更，确保系统安全和合规性。
# 数据输入
{
"operation": "UPDATE",
"table": "user_permissions",
"timestamp": "2024-01-15T10:30:00Z",
"before": {
"user_id": "U001",
"role": "user",
"permissions": ["read", "write"]
},
"after": {
"user_id": "U001",
"role": "admin",
"permissions": ["read", "write", "delete", "admin"]
},
"operator": "ADMIN001"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 期望输出
{
"audit_id": "AUD001",
"user_id": "U001",
"permission_change": {
"role_change": {
"from": "user",
"to": "admin"
},
"added_permissions": ["delete", "admin"],
"removed_permissions": []
},
"risk_assessment": {
"risk_level": "high",
"requires_approval": true,
"compliance_check": "required"
},
"operator": "ADMIN001",
"timestamp": "2024-01-15T10:30:00Z"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# CDC特点
# 实时性
- 即时捕获: 数据变更立即被捕获
- 低延迟: 毫秒级的变更传播
- 高可靠: 确保变更不丢失
# 数据完整性
- 变更追踪: 完整记录数据变更历史
- 状态对比: 提供变更前后的完整状态
- 操作类型: 区分INSERT、UPDATE、DELETE操作
# 应用场景
- 数据同步: 实时同步到多个系统
- 审计日志: 记录所有数据变更
- 事件驱动: 基于变更触发业务流程
- 实时分析: 基于变更数据进行实时分析
# 📚 相关文档
在 GitHub 上编辑此页 (opens new window)
上次更新: 2025/07/27, 15:17:27