奈科斯（NexIoT）

主题

nexiot2025-12-27
1153
5.5m

数据桥接 ​

设备产生的原始数据自动写入指定目标数据库，当前已适配 MySQL、PostgreSQL、SQL Server 2022 三种主流关系型数据库，实现设备数据与业务数据库的实时同步。

核心前提与部署建议

  • 设备绑定要求：设备必须绑定到具体应用，未绑定应用的设备数据不会触发桥接写入逻辑。
  • 部署网络要求：为保障数据桥接的稳定性和系统整体性能，建议数据库与设备数据采集系统部署在同一内网（避免跨公网传输导致的延迟、丢包问题）。
  • 性能评估：提前评估数据库写入性能，避免数据峰值超出数据库处理能力，影响业务稳定性。

pg

核心前提与部署建议 ​

功能说明 ​

  • 当前版本仅支持数据输出（设备数据写入目标数据库），数据输入、双向流转能力待后续迭代支持。
  • 桥接功能支持按产品维度或应用维度配置规则。
  • 兼容任意自定义表结构，可通过编写SQL模板实现灵活的数据映射写入。
  • 支持设备属性的嵌套层级解析（如 properties.temperature）。

桥接 PostgreSQL/MySQL ​

pg

表结构设计 ​

以下为适配大多数物联网场景的通用设备数据记录表结构：

sql
CREATE TABLE "public"."device_data" (
  "id" int8 NOT NULL DEFAULT nextval('device_data_id_seq'::regclass),
  "device_id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "product_key" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "device_name" varchar(128) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "iot_id" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "device_node" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
  "message_type" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "properties" jsonb,
  "raw_data" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "device_data_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "public"."device_data" 
  OWNER TO "user_kYzD57";

-- 创建索引提升查询性能
CREATE INDEX "idx_create_time" ON "public"."device_data" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

CREATE INDEX "idx_device_id" ON "public"."device_data" USING btree (
  "device_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "idx_product_key" ON "public"."device_data" USING btree (
  "product_key" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- 字段注释
COMMENT ON COLUMN "public"."device_data"."id" IS '自增主键';
COMMENT ON COLUMN "public"."device_data"."device_id" IS '设备ID';
COMMENT ON COLUMN "public"."device_data"."product_key" IS '产品密钥';
COMMENT ON COLUMN "public"."device_data"."device_name" IS '设备名称';
COMMENT ON COLUMN "public"."device_data"."iot_id" IS '物联网ID';
COMMENT ON COLUMN "public"."device_data"."device_node" IS '设备节点标识';
COMMENT ON COLUMN "public"."device_data"."message_type" IS '消息类型';
COMMENT ON COLUMN "public"."device_data"."properties" IS '设备属性（JSON格式存储）';
COMMENT ON COLUMN "public"."device_data"."raw_data" IS '原始数据';
COMMENT ON COLUMN "public"."device_data"."create_time" IS '创建时间';
COMMENT ON TABLE "public"."device_data" IS '设备数据记录表';

数据写入模板 ​

通过模板变量实现设备数据与表字段的映射，支持嵌套属性解析，写入 SQL 模板如下：

sql
INSERT INTO device_data(
    device_id, product_key, device_name, iot_id,
    device_node, message_type, properties, raw_data
) VALUES(
    #{deviceId}, #{productKey}, #{deviceName}, #{iotId},
    #{deviceNode}, #{messageType}, #{properties}, 
    #{properties.temperature}
);

pg

桥接 SQL Server 2022 ​

表结构设计 ​

以下为适配 SQL Server 2022 的设备数据记录表结构（推荐配置）：

sql
CREATE TABLE [dbo].[device_data] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [device_id] varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [product_key] varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [device_name] nvarchar(128) COLLATE Chinese_PRC_CI_AS  NULL,
  [iot_id] varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [device_node] varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [message_type] varchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [properties] nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [raw_data] text COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
  [create_time] datetime DEFAULT getdate() NOT NULL,
  CONSTRAINT [PK__device_d__3213E83FC9ABFC0F] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
)  
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[device_data] SET (LOCK_ESCALATION = TABLE)
GO

-- 创建索引提升查询性能
CREATE NONCLUSTERED INDEX [idx_device_id]
ON [dbo].[device_data] (
  [device_id] ASC
)
GO

CREATE NONCLUSTERED INDEX [idx_product_key]
ON [dbo].[device_data] (
  [product_key] ASC
)
GO

CREATE NONCLUSTERED INDEX [idx_create_time]
ON [dbo].[device_data] (
  [create_time] ASC
)
GO

-- 字段注释
EXEC sp_addextendedproperty
'MS_Description', N'自增主键',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'设备ID',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'device_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品密钥',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'product_key'
GO

EXEC sp_addextendedproperty
'MS_Description', N'设备名称',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'device_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'物联网ID',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'iot_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'设备节点标识',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'device_node'
GO

EXEC sp_addextendedproperty
'MS_Description', N'消息类型',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'message_type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'设备属性（JSON格式存储）',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'properties'
GO

EXEC sp_addextendedproperty
'MS_Description', N'原始数据',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'raw_data'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'device_data',
'COLUMN', N'create_time'
GO

EXEC sp_addextendedproperty
'MS_Description', N'设备数据记录表',
'SCHEMA', N'dbo',
'TABLE', N'device_data'
GO

数据写入模板 ​

对应的写入 SQL 模板如下，支持嵌套属性解析：

sql
INSERT INTO device_data(
    device_id, product_key, device_name, iot_id,
    device_node, message_type, properties, raw_data
) VALUES(
    #{deviceId}, #{productKey}, #{deviceName}, #{iotId},
    #{deviceNode}, #{messageType}, #{properties}, 
    #{properties.temperature}
);

pg