工业物联网网关断网本地续传:如何用 SQLite 保证时序数据 100% 零丢失? (深度实战篇)
工业物联网网关断网本地续传:如何用 SQLite 保证时序数据 100% 零丢失? (深度实战篇)
一、工业现场真实硬件环境与底层痛点深度剖析
产线现场第一视角:2023年Q3,我司在华东某汽车零部件制造基地部署首批边缘网关,接入200台PLC(三菱Q系列+西门子S7-1200混用),现场工况极为复杂。
1.1 硬件环境真实困境
厂区拓扑:
├─ 生产车间A(3000㎡):数控加工中心80台,485总线长度最长120米
├─ 生产车间B(2000㎡):注塑机40台,RS485/以太网混网
├─ 中控室(距车间200米):工业交换机+4G工业路由器
└─ 边缘网关:NXP i.MX6ULL,eMMC 8GB,工作温度-40~85℃
痛点1:485总线抖动导致报文乱序
485总线在半双工模式下,多节点争用总线时必然出现碰撞。实测中,某注塑机数据采集点以100ms周期上报,但485总线上同时有12台设备轮询,总线占用率峰值达78%。
# 真实抓包日志(截取)
[17:23:01.000] TX: 01 03 00 01 00 0A [CRC] # 读注塑机站号1
[17:23:01.045] RX: ACK + 20 bytes data
[17:23:01.046] TX: 02 03 00 01 00 0A [CRC] # 站号2立即发送(未等总线恢复)
[17:23:01.048] ERR: COLLISION DETECTED # 总线碰撞,无响应
后果:站号2的数据丢失,但网关不知情,继续等待超时后误判为"设备故障",触发错误告警。
痛点2:网络断连与数据乱序
车间B使用4G工业路由器(移动物联卡),信号强度波动-85~-95dBm,日均断连12次,每次持续30秒~5分钟。断网期间采集数据如何存储?直连网络时如何保证上传时序?
# 网络状态监控日志
[2023-09-15 10:00:00] NET: CONNECTED rssi=-82dBm
[2023-09-15 10:03:22] NET: DISCONNECTED (timeout)
[2023-09-15 10:03:52] NET: RECONNECTING...
[2023-09-15 10:04:15] NET: CONNECTED rssi=-88dBm
[2023-09-15 10:04:16] UPLOAD: BACKLOG=342 points, seq=1~342
痛点3:eMMC寿命与写入放大
边缘网关使用eMMC存储采集数据,若以100ms周期写入SQLite,每天约86400次写入,eMMC磨损速度约为2~3年寿命(假设eMMC额定10万次擦写)。必须优化写入策略。
二、码视野高可用边缘系统拓扑架构
2.1 整体架构设计
2.2 核心设计原则
原则1:本地优先,异步上传
采集数据先落盘SQLite,网络恢复后异步上传。保证即使断网2小时,数据零丢失。
原则2:时序号单调递增,断网续传不重不漏
每笔数据分配全局唯一seq_no,断网期间继续递增,恢复后按序上传。云端校验seq_no连续性,自动补传缺失段。
原则3:eMMC寿命优化,批量写入
SQLite使用journal_mode=WAL,每次采集周期批量写入10~50条数据,降低eMMC写入放大。
三、工业级生产环境完整实现代码示例
3.1 SQLite 时序数据表设计
-- 表结构:边缘采集数据表
CREATE TABLE IF NOT EXISTS edge_timeseries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
seq_no INTEGER NOT NULL UNIQUE, -- 全局唯一时序号(单调递增)
device_id TEXT NOT NULL, -- 设备站号(如:注塑机-001)
point_name TEXT NOT NULL, -- 测点名(如:模具温度)
value REAL NOT NULL, -- 采集值
unit TEXT, -- 单位(如:℃)
ts_ms BIGINT NOT NULL, -- 采集时间戳(毫秒)
raw_hex TEXT, -- 原始报文(十六进制)
status INTEGER DEFAULT 0, -- 状态:0=待上传,1=已上传,2=上传失败
created_at TEXT DEFAULT (datetime('now')) -- 本地创建时间
);
-- 索引:优化时序查询与续传
CREATE INDEX idx_seq_status ON edge_timeseries(seq_no, status);
CREATE INDEX idx_device_ts ON edge_timeseries(device_id, ts_ms);
-- WAL模式:减少写入阻塞
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL; -- 平衡性能与安全
PRAGMA temp_store = MEMORY; -- 临时表放内存
3.2 Python 采集驱动与持久化缓存
```python
!/usr/bin/env python3
-- coding: utf-8 --
""" 码视野边缘网关 - 工业采集驱动与本地缓存模块 作者:码视野物联网软件研发团队首席架构师 场景:汽车零部件产线PLC数据采集 """
import sqlite3 import struct import time import threading from datetime import datetime from typing import Optional, List, Dict import logging
logger = logging.getLogger(name)
class EdgeTimeseriesCache: """ 边缘侧时序数据缓存:SQLite持久化 + 断网续传 设计原则: 1. 每笔数据分配全局唯一seq_no(单调递增) 2. 本地优先,网络恢复后异步上传 3. eMMC寿命优化,批量写入 """
def __init__(self, db_path: str = "/data/edge_timeseries.db"):
self.db_path = db_path
self.seq_counter = 0 # 时序号计数器(内存中)
self.lock = threading.Lock() # 线程安全锁
self.conn: Optional[sqlite3.Connection] = None
self._init_db()
self._load_seq_counter()
def _init_db(self):
"""初始化SQLite数据库,创建表与索引"""
self.conn = sqlite3.connect(self.db_path, timeout=30)
self.conn.execute("PRAGMA journal_mode=WAL")
self.conn.execute("PRAGMA synchronous=NORMAL")
self.conn.execute("""
CREATE TABLE IF NOT EXISTS edge_timeseries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
seq_no INTEGER NOT NULL UNIQUE,
device_id TEXT NOT NULL,
point_name TEXT NOT NULL,
value REAL NOT NULL,
unit TEXT,
ts_ms BIGINT NOT NULL,
raw_hex TEXT,
status INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
)
""")
self.conn.execute("CREATE INDEX IF NOT EXISTS idx_seq_status ON edge_timeseries(seq_no, status)")
self.conn.execute("CREATE INDEX IF NOT EXISTS idx_device_ts ON edge_timeseries(device_id, ts_ms)")
self.conn.commit()
logger.info(f"EdgeTimeseriesCache initialized: {self.db_path}")
def _load_seq_counter(self):
"""启动时从DB加载最后seq_no,保证单调递增"""
try:
row = self.conn.execute(
"SELECT MAX(seq_no) FROM edge_timeseries"
).fetchone()
self.seq_counter = row[0] or 0
logger.info(f"Loaded seq_counter={self.seq_counter}")
except Exception as e:
logger.error(f"Failed to load seq_counter: {e}")
self.seq_counter = 0
def _next_seq(self) -> int:
"""获取下一个时序号(线程安全)"""
with self.lock:
self.seq_counter += 1
return self.seq_counter
def save_batch(self, points: List[Dict]) -> int:
"""
批量保存采集数据(优化eMMC寿命)
Args:
points: 采集点列表,每项包含device_id, point_name, value, raw_hex
Returns:
保存的记录数
"""
if not points:
return 0
seq_no = self._next_seq()
ts_ms = int(time.time() * 1000)
rows = []
for p in points:
rows.append((
seq_no + len(rows), # 单调递增
p["device_id"],
p["point_name"],
p["value"],
p.get("unit", ""),
ts_ms + len(rows), # 微秒级时间戳差异
p.get("raw_hex", ""),
0 # status=0(待上传)
))
try:
self.conn.executemany("""
INSERT OR REPLACE INTO edge_timeseries
(seq_no, device_id, point_name, value, unit, ts_ms, raw_hex, status)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""", rows)
self.conn.commit()
logger.debug(f"Saved {len(rows)} points, seq_no range: {seq_no}~{seq_no+len(rows)}")
return len(rows)
except Exception as e:
logger.error(f"Failed to save batch: {e}")
self.conn.rollback()
return 0
def get_pending_upload(self, limit: int = 100) -> List[Dict]:
"""
获取待上传数据(按seq_no升序)
Args:
limit: 每次最多获取记录数
Returns:
待上传数据列表
"""
try:
cursor = self.conn.execute("""
SELECT seq_no, device_id, point_name, value, unit, ts_ms, raw_hex
FROM edge_timeseries
WHERE status = 0
ORDER BY seq_no ASC
LIMIT ?
""", (limit,))
rows = cursor.fetchall()
return [
{
"seq_no": row[0],
"device_id": row[1],
"point_name": row[2],
"value": row[3],
"unit": row[4],
"ts_ms": row[5],
"raw_hex": row[6]
}
for row in rows
]
except Exception as e:
logger.error(f"Failed to get pending upload: {e}")
return []
def mark_uploaded(self, seq_no_list: List[int]) -> int:
"""
标记已上传成功
Args:
seq_no_list: 已上传的seq_no列表
Returns:
更新记录数
"""
if not seq_no_list:
return 0
try:
placeholders = ",".join(["?"] * len(seq_no_list))
cursor = self.conn.execute(f"""
UPDATE edge_timeseries
SET status = 1
WHERE seq_no IN ({placeholders})
""", seq_no_list)
self.conn.commit()
logger.debug(f"Marked {cursor.rowcount} points as uploaded")
return cursor.rowcount
except Exception as e:
logger.error(f"Failed to mark uploaded: {e}")
self.conn.rollback()
return 0
def close(self):
"""关闭数据库连接"""
if self.conn:
self.conn.close()
logger.info("EdgeTimeseriesCache closed")