Hive关联DynamoDB外部表执行INSERT OVERWRITE无法删除旧数据

• 核心原因是Hive对接DynamoDB的存储处理器（Storage Handler）对INSERT OVERWRITE的实现逻辑和普通HDFS文件表不同：普通文件表的OVERWRITE会先删除原表关联的所有文件再写入新数据，但DynamoDB作为NoSQL存储，对应的存储处理器没有实现「先清空全表再写入」的语义，只会对SELECT返回的记录执行**upsert（存在则更新、不存在则新增）**操作，不在SELECT结果里的旧记录不会被触及，所以表总条目数不变。
• 你观测到DynamoDB写入容量被消耗，正是因为筛选出的符合条件的记录都执行了upsert操作，和上述逻辑完全吻合。
• 额外提示：你提供的建表语句里reason STRING和time_to_live BIGINT之间缺少逗号，实际建表时需要修正。

可以先单独执行以下查询确认你的筛选条件是否生效，排除过滤逻辑错误的可能：

-- 查询全表总条目数
SELECT COUNT(*) FROM ddb_betaaccounthistory;
-- 查询符合保留条件的条目数
SELECT COUNT(*) FROM ddb_betaaccounthistory WHERE `date` > 1586551523;

如果第二个查询结果小于第一个，说明筛选逻辑没有问题，问题确实出在INSERT OVERWRITE的语义不匹配。

方案1：使用DELETE语句直接删除过期数据 #

如果你使用的是支持DELETE操作的DynamoDB Hive存储处理器（比如AWS官方提供的实现），可以直接执行删除语句：

DELETE FROM ddb_betaaccounthistory WHERE `date` < 1586551523;

注意该操作会扫描全表匹配条件并逐条删除，DynamoDB的读写容量消耗会比较高，建议在业务低峰期执行，也可以通过限制扫描速度避免影响线上业务。

方案2：借助临时表全量替换 #

如果当前使用的存储处理器不支持DELETE操作，可以按以下步骤执行：

1. 新建临时Hive表存储需要保留的数

CREATE TABLE temp_keep_data AS
SELECT * FROM ddb_betaaccounthistory WHERE `date` > 1586551523;

2. 清空原DynamoDB表：数据量大的情况下直接删除DynamoDB表后重建效率最高，也可以选择批量扫描删除所有记录
3. 把临时表的数据写回原外部表：

INSERT INTO ddb_betaaccounthistory SELECT * FROM temp_keep_data;

方案3：补全TTL字段自动过期 #

如果后续也需要定期清理旧数据，可以补全所有记录的time_to_live字段，设置为对应date加18个月的时间戳，开启DynamoDB的TTL功能后，过期数据会被DynamoDB自动清理，不需要手动执行删除操作。

内容的提问来源于stack exchange，提问作者wanderingstu