千万级每日更新JSON数据高效存储与批量增量插入方案咨询

需求背景 #

我有一个包含1000万+条JSON对象的大型压缩文件，每天会收到新的压缩包，其中大部分数据完全相同，但约2%的数据存在属性变更等差异。需要高效存储这些数据，且系统需支持快速读取，应用仅执行读取操作，不写入数据库。

每日接收的新数据大部分重复，仅少量不同，且部分数据顺序不同。核心需求是用新数据更新数据库，但丢弃重复数据——这里的重复指的是除唯一标识address外，其余属性完全一致的条目。

数据示例 #

原始数据示例：

{"address": "123 Main St", "organization": "Real Estate Co", "building": {"type": "House", "leased": false}, "residents": [{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 28}], "facilities": ["Garden"], "location": {"city": "Metropolis", "country": "US"}, "risks": ["Burglary"]}
{"address": "456 Oak St", "organization": "Urban Apartments", "building": {"type": "Apartment", "leased": true}, "residents": [{"name": "Alice Smith", "age": 25}, {"name": "Bob Johnson", "age": 30}], "facilities": ["Fitness Center", "Swimming Pool"], "location": {"city": "Downtownville", "country": "US"}, "risks": ["Noise Complaints", "Fire Hazard"]}

注：实际数据中address字段为唯一标识。

次日收到的更新数据示例：

{"address": "123 Main St", "organization": "Real Estate Co", "building": {"type": "House", "leased": false}, "residents": [{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 28}], "facilities": ["Garden"], "location": {"city": "Metropolis", "country": "US"}, "risks": ["Burglary"]}
{"address": "456 Oak St", "organization": "Urban Apartments", "building": {"type": "Apartment", "leased": false}, "residents": [{"name": "Alice Smith", "age": 25}, {"name": "Bob Johnson", "age": 30}], "facilities": ["Fitness Center", "Swimming Pool", "Burger Shop", "Basketball court"], "location": {"city": "Downtownville", "country": "US"}, "risks": ["Noise Complaints", "Fire Hazard"]}

此时仅需存储第二条JSON对象，因为它的address未变，但其他属性已更改，属于需要保留的更新数据。

优化思路 #

计划将数据存储在单个数据库表中，创建由唯一字段address和自动生成的date组成的复合键，以此区分每日的更新数据，同时避免重复存储完全一致的条目。

MongoDB示例结构 #

{"_id":{"address":"123 Main St","date":"06-01-2024"},"organization":"ABC Inc","as":{"number":12345,"organization":"Internet Services Ltd"},"client":{},"tunnels":[{"operator":"PROXY_OPERATOR","type":"VPN","anonymous":false}],"location":{"city":"Metropolis","country":"US"},"risks":["DATA_LEAK"]}

MySQL示例结构 #

address VARCHAR(40) NOT NULL,
date DATE DEFAULT CURRENT_DATE NOT NULL,
address_data JSON NOT NULL,
PRIMARY KEY (address, date)

该方案的核心逻辑是：检查数据是否唯一（忽略date字段），仅插入与已有数据不同的条目，而非更新原有数据。

已尝试的方案 #

1. MySQL方案 #

使用Docker部署MySQL，创建如下表结构：

address VARCHAR(40) NOT NULL,
date DATE DEFAULT CURRENT_DATE NOT NULL,
address_data JSON NOT NULL,
PRIMARY KEY (address, date)

通过LOAD DATA命令批量插入：

LOAD DATA INFILE '/var/lib/mysql-files/data.json'
INTO TABLE table_name
FIELDS TERMINATED BY '\n'
LINES TERMINATED BY '\n'
(@json)
SET address = JSON_UNQUOTE(JSON_EXTRACT(@json, '$.address')),
    date = CURRENT_DATE,
    address_data = @json;

但遇到问题：部分数据包含ucs2、utf16等MySQL不支持的字符集，或不兼容的Unicode转义字符，导致插入报错：

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Missing a comma or '}' after an object member." at position 86.

2. MongoDB方案 #

改用MongoDB存储JSON数据，先用jq工具预处理数据，生成包含复合_id的条目：

jq -c '. + { "_id": { "address": .address, "date": (now | strftime("%d-%m-%Y")) } }' data.json > formatted_data.json

处理后的数据示例：

{"_id":{"address":"123 Main St","date":"06-01-2024"},"organization": "Real Estate Co", "building": {"type": "House", "leased": false}, "residents": [{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 28}], "facilities": ["Garden"], "location": {"city": "Metropolis", "country": "US"}, "risks": ["Burglary"]}

通过mongoimport成功插入了初始数据，但当前遇到的问题是：mongoimport不支持条件操作，无法直接批量插入仅与已有数据不同的新条目。如果用Python等工具逐条比较数据再插入，耗时太长，效率低下。

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