实时数仓中涉及更新历史数据（如小时、天、月的汇总数据）时，数据库是否支持 UPSERT（存在更新，否则插入）会显著影响解决方案。

在实时数仓中，每5分钟处理增量数据，尤其涉及更新历史数据（如小时、天、月的汇总数据）时，数据库是否支持 UPSERT（存在更新，否则插入）会显著影响解决方案。

1. Couchbase（支持UPSERT）的情况

Couchbase 提供 UPSERT 机制，适合在每5分钟计算时直接更新各层级的汇总数据。

• DWS层汇总更新：基于主键（如 store_id + time_window）进行UPSERT，确保历史汇总数据被及时更新。
• ADS层报表更新：例如“本日数据”或“本月数据”直接基于增量进行汇总更新。

示例： Couchbase 的 UPSERT 操作

UPSERT INTO dws_hourly_order (KEY, VALUE)
VALUES ('store_id_20240612_15', { "store_id": "S01", "hour": "15", "total_amount": 2000 });

2. Hive（不支持UPSERT）的情况

Hive 不直接支持 UPSERT，在这种情况下，增量数据更新到历史层级时可以采用以下几种解决方案：

方案一：增量数据与历史数据合并（Insert Overwrite）

• 思路：每5分钟增量数据先写入临时表，再与历史数据进行合并，最终覆盖目标表。
• 方法：使用 INSERT OVERWRITE 或 MERGE INTO 语句重新生成汇总数据。

示例：合并增量数据到 DWS 表
假设有一个历史天级汇总表 dws_daily_order，每5分钟的增量数据存储在 tmp_incremental_order。

INSERT OVERWRITE TABLE dws_daily_order
SELECT store_id, date, SUM(total_amount) AS total_amount, COUNT(order_id) AS order_count
FROM (
    SELECT store_id, date, total_amount, order_id FROM dws_daily_order
    UNION ALL
    SELECT store_id, date, total_amount, order_id FROM tmp_incremental_order
) t
GROUP BY store_id, date;

• 优点：逻辑简单，重建目标表即可；适用于数据量不特别庞大的场景。
• 缺点：每次重新计算历史数据有一定开销，尤其是大规模数据。

方案二：分区表设计（增量覆盖分区数据）

• 思路：将数据按时间粒度分区（如小时、天、月）。每次增量数据只覆盖对应分区的数据，而不影响其他分区。

示例：按天分区覆盖 DWS 表

ALTER TABLE dws_daily_order DROP IF EXISTS PARTITION (date='2024-06-12');
INSERT INTO TABLE dws_daily_order PARTITION (date='2024-06-12')
SELECT store_id, SUM(total_amount), COUNT(order_id)
FROM tmp_incremental_order
WHERE date = '2024-06-12'
GROUP BY store_id;

• 优点：只更新特定分区的数据，效率高，避免全表重写。
• 缺点：需要合理设计分区字段（如日期、小时等），不适合非时间序列的更新。

方案三：临时表 + 最终表合并（基于 Hive ACID 事务）

Hive 支持 ACID 事务（Hive 2.x 以上），允许在事务表上进行 INSERT、UPDATE、DELETE 操作。

• 思路：将增量数据写入临时表，再基于 ACID 特性进行 MERGE INTO 操作更新目标表。

示例：Hive ACID 事务下的 MERGE INTO 操作

MERGE INTO dws_daily_order t
USING tmp_incremental_order s
ON t.store_id = s.store_id AND t.date = s.date
WHEN MATCHED THEN UPDATE SET t.total_amount = t.total_amount + s.total_amount
WHEN NOT MATCHED THEN INSERT (store_id, date, total_amount) VALUES (s.store_id, s.date, s.total_amount);

• 优点：原生支持增量更新，简化数据更新逻辑。
• 缺点：Hive ACID 需要开启 transactional 表特性，性能较普通表稍低。

方案四：多表分层存储（Lambda架构思路）

• 思路：将数据分为 实时增量表 和 批量汇总表 两部分：
  • 每5分钟的增量数据写入 增量表；
  • 每小时或每天的批量任务将增量数据合并到 汇总表。
• 优势：实时表处理小规模增量数据，批量表定时汇总确保数据准确性。

示例流程：

1. 5分钟任务：写入 tmp_incremental_order。
2. 小时任务：汇总增量数据并覆盖目标分区。

INSERT OVERWRITE TABLE dws_hourly_order PARTITION (hour='2024-06-12-15')
SELECT store_id, SUM(total_amount), COUNT(order_id)
FROM tmp_incremental_order
WHERE hour = '2024-06-12-15'
GROUP BY store_id;

3. 实践建议与总结

• Couchbase 等支持 UPSERT：可以在5分钟任务中直接增量更新历史数据，无需重写整个表。
• Hive 等不支持 UPSERT：
  • 分区覆盖：对按时间分区的数据进行覆盖，减少影响范围；
  • 合并重建：合并增量数据与历史数据，重新生成目标表（适用于小规模数据）；
  • ACID 事务：在 ACID 表上使用 MERGE INTO 实现增量更新。
  • Lambda 架构：实时增量表 + 批量汇总表双轨并行，兼顾实时性和准确性。

针对5分钟窗口的实时数仓建设，选择合适的增量更新策略，结合分区设计、ACID 事务等手段，可以高效地实现数据汇总与更新，避免全表重写带来的性能瓶颈。