IoT platform - detailed explanation of the SQL data format of the rule engine

Detailed explanation of the SQL data format of the rule engine

Foreword[](#i0bcvd)
After the device is connected to the IoT platform, the best way to obtain data is to use the rule engine to first process the data using SQL expressions, and then transfer it to DB, MQ, DataHub, FC and other destinations. When configuring data to process SQL, we need to understand the data format corresponding to each topic in order to write correct SQL expressions.

Note: Please do not take the console sql debugging as the standard, unless you understand the sql debugging function

1. Custom Topic
When we use a custom topic, the IoT platform will transparently transmit the Payload data, and the structure will not change in this case.

As shown in the data processing process shown in the figure above, when the original message enters the rule engine and executes the SQL expression, it is still the Payload structure on the end.

{
temperature: 23,
humidity: 63,
}
Therefore, we only need to write SQL by custom payload.


2. Object model system Topic (starting with /sys/)
When we use the object model to transmit data, the data will be processed in the cloud and transformed into the object model JSON format. Here we need to pay attention to the difference of each Payload.


2.1 Reporting of device attributes
Use this topic to obtain the attribute information reported by the device.

Data flow topic: /sys/{productKey}/{deviceName}/thing/event/property/post

Device raw data format:

{
"id": 3536123,
"version": "1.0",
"method": "thing. event. property. post",
"params": {
"Power": "on",
"Position": {
"latitude": 39.9,
"longitude": 116.38
}
}
}

Object model data format: rule engine SQL needs to be written according to this format

{
"productKey": "1234556554",
"deviceName": "deviceName1234",
"gmtCreate": 1510799670074,
"items": {
"Power":{
"value": "on",
"time": 1510799670074
},
"Position": {
"time": 1510292697470,
"value": {
"latitude": 39.9,
"longitude": 116.38
}
}
}
}

2.2 Device event reporting
Get the event information reported by the device through this topic.
Data flow topic: /sys/{productKey}/{deviceName}/thing/event/(tsl.event.identifier}/post

Device raw data format:

{
"id": 3536123,
"version": "1.0",
"method": "thing.event.{tsl.event.identifier}.post",
"params": {
"temperature": 39.9,
"humidity": 87
}
}

Object model data format: rule engine SQL needs to be written according to this format

{
"identifier": "{tsl.event.identifier}",
"type": "info",
"productKey": "X5eCzh6fEH7",
"deviceName":"5gJtxDVeGAkaEztpisjX",
"gmtCreate": 1510799670074,
"value": {
"temperature": 39.9,
"humidity": 87
},
"time": 1510799670074
}

2.3 Device service asynchronous call response
Through this topic, you can obtain the result information returned by the device after the command is sent to the device in an asynchronous manner. If an error occurs during the sending of the command, you can also get the error message of the command sending through this Topic.
Data flow topic: /sys/{productKey}/{deviceName}/thing/downlink/reply/message

Device raw data format:

{
"id": 3536123,
"version": "1.0",
"method": "thing. service. {tsl. service. identifier}",
"params": {
"Power": "on",
"temperature":39.9
}
}
Object model data format: rule engine SQL needs to be written according to this format

{
"gmtCreate":1510292739881,
"productKey": "123xxxx554",
"deviceName": "deviceName1234",
"requestId": 3536123,
"code":200,
"message": "success",
"topic": "/sys/123xxxx554/deviceName1234/thing/service/{tsl.service.identifier}",
"data": {
"status": "on"
}
}

3. Changes in the device itself

3.1 Device online and offline status
Get the online and offline status of the device through this topic.

Data flow Topic: /as/mqtt/status/{productKey}/{deviceName}
Data Format:

{
"status": "online|offline",
"productKey": "12345565569",
"deviceName": "deviceName1234",
"time": "2018-08-31 15:32:28.205",
"utcTime": "2018-08-31T07:32:28.205Z",
"lastTime": "2018-08-31 15:32:28.195",//The last communication time before the state change
"utcLastTime": "2018-08-31T07:32:28.195Z",
"clientIp": "123.123.123.123"
}

3.2 Changes in equipment life cycle
Use this topic to obtain information about device creation, deletion, disabling, and enabling.

Data flow Topic: /sys/{productKey}/{deviceName}/thing/lifecycle
Data Format:

{
"action" : "Possible enumeration values create|delete|enable|disable",
"productKey" : "X5eCxxxxEH7",
"deviceName" : "5gJtxDVeGAkaEztpisjX",
"deviceSecret" : "Device secret, only included when the action is create",
"messageCreateTime": 1510292739881
}