iot mysql_IoT Studio服务开发MySQL数据流转示例Demo

概述

阿里云物联网平台的规则引擎功能支持将数据流转到MySQL数据库，但是仅支持同区域(上海--华东二)的数据流转，这里介绍使用IoT Studio服务开发：云数据库MySQL节点，通过MySQL数据库的公网地址，完成跨区域的数据流转。

Step By Step

物联网产品及设备的创建

1、创建产品

2、导入物模型

model.json

{

"schema":"https://iotx-tsl.oss-ap-southeast-1.aliyuncs.com/schema.json",

"profile":{

"productKey":""

},

"properties":[

{

"identifier":"Distance",

"name":"距离",

"accessMode":"rw",

"required":false,

"dataType":{

"type":"double",

"specs":{

"min":"0",

"max":"10000",

"unit":"m",

"step":"1"

}

}

},

{

"identifier":"GeoLocation",

"name":"自定义地理位置",

"accessMode":"rw",

"required":false,

"dataType":{

"type":"struct",

"specs":[

{

"identifier":"Longitude",

"name":"经度",

"dataType":{

"type":"double",

"specs":{

"min":"-180",

"max":"180",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"Latitude",

"name":"纬度",

"dataType":{

"type":"double",

"specs":{

"min":"-90",

"max":"90",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"CoordinateSystem",

"name":"坐标系统",

"dataType":{

"type":"enum",

"specs":{

"1":"WGS_84",

"2":"GCJ_02"

}

}

}

]

}

},

{

"identifier":"CellSignalStrength",

"name":"信号强度",

"accessMode":"r",

"required":false,

"dataType":{

"type":"int",

"specs":{

"min":"-128",

"max":"127",

"unit":"dBm",

"step":"1"

}

}

}

],

"events":[

{

"identifier":"post",

"name":"post",

"type":"info",

"required":true,

"desc":"属性上报",

"method":"thing.event.property.post",

"outputData":[

{

"identifier":"Distance",

"name":"距离",

"dataType":{

"type":"double",

"specs":{

"min":"0",

"max":"10000",

"unit":"m",

"step":"1"

}

}

},

{

"identifier":"GeoLocation",

"name":"自定义地理位置",

"dataType":{

"type":"struct",

"specs":[

{

"identifier":"Longitude",

"name":"经度",

"dataType":{

"type":"double",

"specs":{

"min":"-180",

"max":"180",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"Latitude",

"name":"纬度",

"dataType":{

"type":"double",

"specs":{

"min":"-90",

"max":"90",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"CoordinateSystem",

"name":"坐标系统",

"dataType":{

"type":"enum",

"specs":{

"1":"WGS_84",

"2":"GCJ_02"

}

}

}

]

}

},

{

"identifier":"CellSignalStrength",

"name":"信号强度",

"dataType":{

"type":"int",

"specs":{

"min":"-128",

"max":"127",

"unit":"dBm",

"step":"1"

}

}

}

]

}

],

"services":[

{

"identifier":"set",

"name":"set",

"required":true,

"callType":"async",

"desc":"属性设置",

"method":"thing.service.property.set",

"inputData":[

{

"identifier":"Distance",

"name":"距离",

"dataType":{

"type":"double",

"specs":{

"min":"0",

"max":"10000",

"unit":"m",

"step":"1"

}

}

},

{

"identifier":"GeoLocation",

"name":"自定义地理位置",

"dataType":{

"type":"struct",

"specs":[

{

"identifier":"Longitude",

"name":"经度",

"dataType":{

"type":"double",

"specs":{

"min":"-180",

"max":"180",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"Latitude",

"name":"纬度",

"dataType":{

"type":"double",

"specs":{

"min":"-90",

"max":"90",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"CoordinateSystem",

"name":"坐标系统",

"dataType":{

"type":"enum",

"specs":{

"1":"WGS_84",

"2":"GCJ_02"

}

}

}

]

}

}

],

"outputData":[

]

},

{

"identifier":"get",

"name":"get",

"required":true,

"callType":"async",

"desc":"属性获取",

"method":"thing.service.property.get",

"inputData":[

"Distance",

"GeoLocation",

"CellSignalStrength"

],

"outputData":[

{

"identifier":"Distance",

"name":"距离",

"dataType":{

"type":"double",

"specs":{

"min":"0",

"max":"10000",

"unit":"m",

"step":"1"

}

}

},

{

"identifier":"GeoLocation",

"name":"自定义地理位置",

"dataType":{

"type":"struct",

"specs":[

{

"identifier":"Longitude",

"name":"经度",

"dataType":{

"type":"double",

"specs":{

"min":"-180",

"max":"180",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"Latitude",

"name":"纬度",

"dataType":{

"type":"double",

"specs":{

"min":"-90",

"max":"90",

"unit":"°",

"step":"0.01"

}

}

},

{

"identifier":"CoordinateSystem",

"name":"坐标系统",

"dataType":{

"type":"enum",

"specs":{

"1":"WGS_84",

"2":"GCJ_02"

}

}

}

]

}

},

{

"identifier":"CellSignalStrength",

"name":"信号强度",

"dataType":{

"type":"int",

"specs":{

"min":"-128",

"max":"127",

"unit":"dBm",

"step":"1"

}

}

}

]

}

]

}

注意： 替换自己产品的ProductKey

3、添加设备

IoT Studio绑定产品+设备

1、创建项目

2、项目创建完成后分别关联创建的产品和设备

3、新建业务服务

4、业务流程搭建

5、云数据库MySQL节点配置介绍

5.1 MySQL的版本请使用MySQL5.7或MySQL5.6版本，其它版本兼容性会有问题，可能会出现连接异常。

5.2 参数配置{

"table": "iotdevice1",

"rows": [

{

"CellSignalStrength": "{{query.props.CellSignalStrength.value}}",

"Distance": "{{query.props.Distance.value}}",

"Longitude": "{{query.props.GeoLocation.value.Latitude}}",

"Latitude": "{{query.props.GeoLocation.value.Longitude}}"

}

]

}

也可以是(注意节点Id按照具体节点情况修改)：

{

"table": "iotdevice1",

"rows": [

{

"CellSignalStrength": "{{payload.props.CellSignalStrength.value}}",

"Distance": "{{query.props.Distance.value}}",

"Longitude": "{{query.props.GeoLocation.value.Latitude}}",

"Latitude": "{{node.node_339cdef0.props.GeoLocation.value.Longitude}}"

}

]

}

payload表示上一个节点输出参数；query表示输入节点的参数；node.nodeId表示指定某一节点的输出参数。

5.3 MySQL建表语句/*------- CREATE SQL---------*/

CREATE TABLE `iotdevice1` (

`CellSignalStrength` int(11) DEFAULT NULL,

`Distance` double DEFAULT NULL,

`Longitude` double DEFAULT NULL,

`Latitude` double DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

设备在线模拟测试

1、部署调试

2、数据流转查看

3、流程测试正常后，发布即可

参考链接

来源：oschina

链接：https://my.oschina.net/u/4258260/blog/3209185