如何通过Azure Stream Analytics将多台IoT设备数据存储至Azure SQL数据库

方案1：通用元数据表+JSON负载存储（适配现有流处理流程，改动最小） #

这是最适合你当前场景的方案，完全不需要预先定义任何设备上报的业务字段，新增设备也无需调整表结构：

• 仅需在Azure SQL DB中创建一张通用设备遥测表，仅保留所有设备都具备的公共元数据字段，异构业务数据统一存入JSON类型列，表结构参考：

CREATE TABLE IotDeviceTelemetry (
    DeviceId NVARCHAR(128) NOT NULL,
    TelemetryTime DATETIME2 NOT NULL,
    MessageVersion NVARCHAR(32) NULL,
    -- 存储完整的设备上报原始数据
    TelemetryPayload JSON NOT NULL,
    CONSTRAINT PK_IotTelemetry PRIMARY KEY CLUSTERED (DeviceId, TelemetryTime)
)

• 在Azure Stream Analytics的流查询中，不需要做复杂的字段解析，仅提取公共元数据后，将完整报文直接写入JSON列即可，查询示例：

SELECT
    IoTHub.ConnectionDeviceId AS DeviceId,
    System.Timestamp AS TelemetryTime,
    -- 直接保留原始上报的所有字段为JSON格式
    * AS TelemetryPayload
INTO
    YourSqlDbOutputAlias
FROM
    YourIoTHubInputAlias

• 后续需要读取特定设备的业务字段时，直接使用Azure SQL原生的JSON函数查询即可，例如查询Device1的温度数据：

SELECT
    TelemetryTime,
    JSON_VALUE(TelemetryPayload, '$.temperature') AS Temperature,
    JSON_VALUE(TelemetryPayload, '$.humidity') AS Humidity
FROM IotDeviceTelemetry
WHERE DeviceId = 'Device1'

该方案完全兼容你当前使用的Azure SQL DB Stream Analytics预览功能，流处理侧不需要做特殊适配，性能也能满足大多数IoT场景的查询需求。

方案2：动态结构自动建表（适合需要结构化拆分字段的场景） #

如果你需要将不同设备的业务字段拆分为独立的结构化列存储，可以使用流分析调用SQL存储过程的方式实现动态结构生成：

• 首先在SQL DB中编写存储过程，入参为设备ID和完整上报的JSON报文
• 存储过程内部逻辑：先校验对应设备的专属表是否存在，不存在则解析JSON字段动态生成CREATE TABLE语句，再将报文解析后插入对应表
• Stream Analytics输出配置选择“调用存储过程”，将每条消息的设备ID和完整报文作为入参传递即可
• 注意该方案需要做并发控制，避免多设备同时建表产生冲突，适合设备量级在千级以内的场景。

方案3：混合存储模式 #

如果你的设备中部分字段固定、部分字段完全异构，可以拆分两类存储：

• 字段固定的设备单独建结构化表存储，查询性能更高
• 字段未知的异构设备统一存入通用JSON宽表，适配灵活扩展需求

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