物设备报警情况实时统计
更新时间：2022-12-01
概览
统计每个设备每分钟报警次数。
需求场景
用户拥有1千多台设备，分布在不同城市的多个厂区，每个设备上的传感器大概每5秒采集并上传数据到IoT Hub。
| sensorId | time | status |
|---|---|---|
| 传感器ID | 发送时间 | 是否报警，status值为1代表报警 |
传感器分布在多个设备、多个厂区，用户在RDS还记录如下传感器、设备、厂区维表信息，如下：
| sensorId | sensorType | deviceId | useTime |
|---|---|---|---|
| 传感器ID | 传感器类型 | 设备ID | 使用寿命 |
方案概述
统计每个设备每分钟发生报警的次数，并将统计结果通过输出到下游的RDS，最终展示在可视化报表中。
配置步骤
定义MQTT source表
Plain Text
1CREATE TABLE source_mqtt_table(
2sensorId STRING,
3time STRING,
4status INTEGER
5) WITH(
6type = 'MQTT',
7brokerUrl = 'tcp://duig1nr.mqtt.iot.bj.baidubce.com:1883', --必填
8topic = 'sensor', --必填
9username = 'iotdemo', --必填
10password = 'iotdemo', --必填
11encode = 'JSON',
12connectionTimeout = '30', --非必填，访问超时设置，单位:s
13keepAliveInterval = '60', --非必填，规定时间段内不活动时连接会被断开，单位:s
14maxBatchMessageNum = 'Int.Max', --非必填，每个batch最大数据条数
15maxBatchMessageSize = 'Int.Max' --非必填，每个batch最大消息字节数
16);定义RDS source表
Plain Text
1CREATE TABLE source_rds_table(
2sensorId STRING,
3sensorType STRING,
4deviceId STRING,
5useTime INTEGER
6) WITH(
7type = 'RDS',
8user = 'rdsdemo', --必填，数据库用户名
9password = 'rdsdemo', --必填，数据库访问密码
10url = 'jdbc:mysql://mysql55.rdsmwi1zrjn5ww8.rds.bd.baidubce.com:3306/bsc_rds_test?useUnicode=true&characterEncoding=UTF8', --必填，jdbc访问RDS的url
11dbTable = 'test' --必填，数据表名称
12);定义RDS sink表
Plain Text
1CREATE TABLE sink_rds_table(
2deviceId STRING,
3time TIMESTAMP,
4nums INTEGER
5) WITH(
6type = 'RDS',
7user = 'iotdemo', --必填，数据库用户名
8password = 'iotdemo11', --必填，数据库访问密码
9url = 'jdbc:mysql://mysql55.rdsmwi1zrjn5ww8.rds.bd.baidubce.com:3306/bsc_rds?useUnicode=true&characterEncoding=UTF8', --必填，jdbc访问RDS的url
10dbTable = 'iotdemo' --必填，数据表名称
11);编写数据统计DML语句
统计这一分钟内每个设备的报警次数。由于使用的是滚动窗口，也就意味着数据将在每分钟结束时候产出一份并写入到RDS。
Plain Text
1INSERT INTO
2sink_rds_table outputmode append
3SELECT
4source_rds_table.deviceId,
5CAST(FROM_UNIXTIME(CAST(source_mqtt_table.time AS LONG)) AS TIMESTAMP) AS time,
6count(*) AS nums
7FROM
8source_mqtt_table INNER JOIN source_rds_table ON source_mqtt_table.sensorId = source_rds_table.sensorId
9WHERE
10source_mqtt_table.status = 1
11GROUP BY
12window(time, "1 minute"),
13deviceId